Category Archives: Administration

Use ILM feature: Automatic Database Optimization (ADO)

August 2, 2016 Larry Catt Leave a comment

Oracle Information Lifecycle Management (ILM) is the processes by which we can manage data from creation to deletion. It allows us a method to increase speed of access and to acquire metadata about use. The two features which support ILM in Oracle 12c are Heat Maps and Automatic Data Optimization. This procedure will cover the use of Automatic Database Optimization (ADO) within a 12c RDBMS. ADO allows us to create policies for compression and data movement within database by use of metadata at the row and segment level.

Logon to your Oracle server as the Oracle software owner.

[root@linux2 ~]# su – oracle

Last login: Tue Jul 13 09:01:45 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$

Logon to SQLPLUS with sysdba permissions.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 14 06:55:06 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Using the table test.test_hm, add a compression policy for table if data is not modified in 1 day.

SQL> ALTER TABLE test.test_hm ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;

Table altered.

SQL>

You must delete a policy if you already have a policy on an object. Example

SQL> ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS;

ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS

ERROR at line 1:

ORA-38323: policy conflicts with policy 1

SQL> alter table test.test_hm ilm delete policy p1;

Table altered.

SQL> ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS;

Table altered.

SQL>

Compression options for ADO policies are:

COMPRESS ADVANCED – on a heap table maps to standard compression for indexes and LOW for LOB segments.
COMPRESS FOR QUERY LOW/ QUERY HIGH – on a heap table maps to standard compression for indexes and MEDIUM for LOB segments.
COMPRESS FOR ARCHIVE LOW/ ARCHIVE HIGH – on a heap table maps to standard compression for indexes and HIGH for LOB segments.

This completes our coverage of ADO in Oracle 12c

Larry Catt

OCP

12c, Administration

Oracle 12c – Use Oracle Database Migration Assistant for Unicode

June 21, 2016 Larry Catt Leave a comment

Oracle 12c now comes with the Database Migration Assistant for Unicode (DMU) for migration of Unicode character sets. This replaces DB Character Set Scanner (CSSCAN) and CASALTER utility which are both de-supported. DMU guides DBA through entire migration and contains a verification mode for existing Unicode databases. Validation method identifies problems with Unicode existing and gives solutions.

Database must meet the following to support DMU.

DB version must be 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.1 or later.
Character set must be ASCII based.
DBMS_DUMA_INTERNAL must be installed.
Oracle Database Vault must be disabled to migrate.
Cannot be 12c PDB.
DB must be in read/write mode.

Feature of DMU:

Selective Conversion – DMU has the ability to only convert data needed at table and column levels.
Monitoring – GUI used to track progress.
Inline Conversion – Support inline conversion of database content.
Scheduling – Removal of old data can be scheduled for future maintenance period.

Before conversion process, DMU preforms an analysis of varchar2, char, long, and CLOB to determine if anything will corrupt the data during Unicode conversion, it checks for the following:

If results are changed from original value.
Conversion fits in length limit of column.
Conversion results fit in data type of column.
Each character if valid in the source character code.

Larry Catt

OCP

12c, Administration, Performance

Oracle 12c – Use Index enhancements

March 29, 2016 Larry Catt Leave a comment

You can create multiple indexes of different types on the same set of columns now. But only one index will be visible at any given time. Multiple indexes can exist when one of the following is true

B-tree vs bitmap
Different partitioning sets exist.
Unique vs non-unique.

Example: Creating two indexes on single column:

Logon to SQLPLUS.

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 29 13:47:09 2016

Last Successful login time: Tue Jan 27 2016 09:57:58 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Create bitmap index on table scott.emp(job).

SQL> create bitmap index bm_scott_emp_job on scott.emp(job);

Index created.

SQL>

View the new index from user_constraints.

SQL> column index_name format a20

SQL> column index_type format a10

SQL> select index_name, index_type, visibility from user_indexes where table_name=’EMP’;

INDEX_NAME INDEX_TYPE VISIBILIT

——————– ———- ———

BM_SCOTT_EMP_JOB BITMAP VISIBLE

PK_EMP NORMAL VISIBLE

SQL>

Make first index invisible.

alter index bm_scott_emp_job invisible;

SQL> alter index bm_scott_emp_job invisible;

Index altered.

SQL>

Now create second index on table scott.emp(job) but this one a b-tree.

create index bt_scott_emp_job on scott.emp(job);

SQL> create index bt_scott_emp_job on scott.emp(job);

Index created.

SQL>

NOTE: the first index must be invisible before you can create the second index. Now see the indexes on table EMP.

SQL> column index_name format a20

column index_type format a10

select index_name, index_type, visibility from user_indexes where table_name=’EMP’;SQL> SQL>

INDEX_NAME INDEX_TYPE VISIBILIT

——————– ———- ———

BM_SCOTT_EMP_JOB BITMAP INVISIBLE

BT_SCOTT_EMP_JOB NORMAL VISIBLE

PK_EMP NORMAL VISIBLE

SQL>

This completes index enhancements in Oracle 12c

Larry Catt

OCP

12c, Administration, Performance

Oracle 12c – Use Flash Cache

February 27, 2016 Larry Catt Leave a comment

The Database Smart Flash Cache allows an instance to access multiple flash devices without requiring a volume manager. This feature only works on Solaris and Linux OS.

Enabling Smart Flash Cache is a benefit if:

Buffer Pool Advisor of AWR or STATSPACK says to double size of buffer cache.
DB FILE SEQUENTIAL READ is top wait event.
System has spare CPU cycles.

Two initialization parameters used to configure Database Smart Flash Cache.

DB_FLASH_CACHE_FILE – list of Paths and files for Database Smart Flash Cache which can be ASM or OS files but must be on a flash device. If not specified, oracle will create on startup. Support max of 16 files.

DB_FLASH_CACHE_SIZE – Specifies size of each file, if number of sizes does not match number of files, an error will occur. Size is specified by nG (ie 10G). You disable FLASH device by specifying zero for size. But this is not a dynamic change and restart is required.

V$FLASHFILESTAT view can determine latency and read counts for each file.

Larry Catt

OCP

12c, Administration, backup/recovery, RMAN

Oracle 12c New Feature – RMAN

February 27, 2016 Larry Catt Leave a comment

Table Level Recovery from backup – 12c now has the ability to recover a single table from an RMAN backup with the user of RECOVER TABLE option.

RMAN Command-Line SQL – RMAN utility now allows for the execution of SQL commands against target database without using SQL key word. RMAN also now support the SQLPLUS DESCRIBE command for tables and views.

Active Database Duplication – RMAN supports the ability to clone a source database to an auxiliary database over the network. Retrieving all backup sets from source database over network and allows for use of block compression during the duplication process.

NOOPEN option for Duplicate database creation, which allows for recovered database to not be open with resetlogs before administrative tasks can be performed.

duplicate target database

to newdatabase

from active database

noopen;

SECTION SIZE option with Image Copies – RMAN now allows you to divide data files into subsections and split upon multiple recovery channels.

backup

as copy

section size 500M

database;

Incremental Backups can include the SECTION SIZE option which allows for parallel channels to be used, thus reducing the time for backup.

backup

incremental level 1

section size 500m

Datafile ‘/u01/oradata/orcl/data01.dbf’;

SNAPSHOT with the use of BACKUP mode, 12c allows for the use of VM snapshots or storage snapshot without placing database in backup mode. The snapshot requires 3 things for use:

Database is consistent at time of snapshot.
Write order is maintained for snapshot.
Snapshot stores time of snapshot.

SNAPSHOT TIME option has become part of RECOVER command which allows for recovery from OS snapshot to a consistent point.

Larry Catt

OCP

11g, 12c, Administration, storage

Oracle 12c – Use Direct NFS

January 22, 2016 Larry Catt Leave a comment

In Oracle 11g a native NFS client has been placed in Oracle Database kernel. It improves performance when Network File Systems are in use. Oracle NFS kernel optimizes and eliminates overhead of NFS, eliminating the need to tune NFS parameters at OS layer.

To use Direct NFS, the NFS file system must be mount and available by OS kernel . The Oracle Direct NFS Client manages Oracles access to drives, by a configuration file call orafstab or mount tab file /etc/mtab on linux. The file $ORACLE_HOME/dbs/orafstab would specify the direct NFS client settings for a single database. Oracle looks for settings in /etc/oranfstab then Oracle reads /etc/mtab to identify available NFS mounts.

To use Direct NFS mount setting you have to replace standard Oracle Disk Manager (ODM) library with one supporting Direct NFS Client.

Example

[oracle@linux2 ~]$ $ORACLE_HOME/dbs/orafstab

server: MyNFSServer1

path: 192.168.1.1

path: 192.168.1.2

path: 192.168.1.3

path: 192.168.1.4

export: /vol/ oradata1

mount: /mnt/ oradata1

Once the file exists, the following commands will enable the Direct NFS Client ODM Library

[oracle@linux2 ~]$ cd $ ORACLE_HOME/ lib

[oracle@linux2 ~]$ cp libodm11. so libodm11. so_stub

[oracle@linux2 ~]$ ln –s libnfsodm11. so libodm11. so

Larry Catt

OCP

12c, Administration, storage

Oracle 12c Moving or renaming Datafile online

January 5, 2016 Larry Catt Leave a comment

In oracle 12c you can now move a datafile while it is online. This procedure will list the rules of moving or renaming a datafile online and show some examples.

Rules

If a file of the same name already exists the ALTER DATABASE statement will fail with an error.
If the REUSE clause is used with the ALTER DATABASE statement and the file already exists, the existing file will be overwritten.
When the ALTER DATABASE MOVE DATAFILE statement is used a copy of the file is made during the process and adequate space must be available for both the original and the copy for operation to succeed.
At the end of the operation, the original copy is deleted. Unless the KEEP clause is used.

Examples

Logon as the oracle software owner and logon to sqlplus.

Display all datafiles with command: select file_name from dba_data_files;

SQL> select file_name from dba_data_files;

FILE_NAME

——————————————————————————–

/u01/oradata/orcl/orcl/system01.dbf

/u01/oradata/orcl/orcl/sysaux01.dbf

/u01/oradata/orcl/orcl/users01.dbf

/u01/oradata/orcl/orcl/undotbs01.dbf

SQL>

Move the sysaux tablespace datafile from sysaux01.dbf to sysaux01_test.dbf.

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’; 2

Database altered.

SQL>

Display all datafiles with command: select file_name from dba_data_files; NOTE: dbf has been replaced with sysaux01_test.dbf.

SQL> select file_name from dba_data_files;

FILE_NAME

——————————————————————————–

/u01/oradata/orcl/orcl/system01.dbf

/u01/oradata/orcl/orcl/sysaux01_test.dbf

/u01/oradata/orcl/orcl/users01.dbf

/u01/oradata/orcl/orcl/undotbs01.dbf

SQL>

To overwrite an existing file, add the REUSE clause.

ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’ REUSE;

To keep the old file, add the KEEP clause.

ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’ KEEP;

This completes movment of online datafiles.

Larry Catt

OCP

12c, Administration, alert / logs, audit

Oracle 12c – Unified Audit Data Trail features

December 14, 2015 Larry Catt Leave a comment

In 12c the Unified Audit Trail consolidates audit information from multiple sources and makes this information available in a standard format in the UNIFIED_AUDIT_TRAIL dictionary view. This article describes this new feature and gives a brief example of enabling it.

Unified Audit Data Trail features.

The Unified Audit Trail is stored in a read-only table in the AUDSYS schema in SYSAUX tablespace
Data is available to SYS and users with AUDIT_ADMIN and AUDIT_VIEWER roles.
AUDIT_ADMIN can view data and create audit policies.
AUDIT_VIEWER can only view data.
The Unified Audit Data Trail combines the audit data from:

Fine-grained audit records from DBMS_FGA
Oracle Database Real Application Security
Oracle Recovery Manager
Oracle Database Vault
Oracle Label Security
Oracle Data Mining
Oracle Data Pump
Oracle SQL* Loader Direct Load

Once configured the unified auditing is always enabled and does not depend on initialization parameters.
If the database is open in READ-ONLY mode, audit records are written to new OS files in $ORACLE_BASE/audit/$ORACLE_SID directory.
The V$OPTION view can be queried to determine if DB is using unified auditing.

Select value from v$option where parameter=’Unified Auditing’;

Parameter Value

—————– —————–

Unified Auditing TRUE

New 12c database mixed mode auditing is enabled by default by predefined policy
Mixed-mode auditing enables both Traditional pre-12c auditing and unified auditing.
Traditional auditing is controlled by AUDIT_TRAIL init parameters.
When AUDIT_TRAIL init parameter is parameter other than NONE, the traditional audit trail will be populated as well as the Unified Audit trail.
Auditing can be set to individual PDBs or CDB as a whole.
In Multi-tenant DB each PDB and root CDB has its own audit trail.
When upgrading to 12c, you have to manually migrate to Unified Auditing to enable.
Once unified auditing is enabled, traditional is disabled.
To start using Unified, at least one unified policy must be enabled.
To stop using Unified, disable all unified policies.
Predefined policy ORA_SECURECONFIG is initially enabled on all new 12c databases.

Larry Catt

OCP

12c, Administration, alert / logs, listener, tnsnames

Oracle 12c – Troubleshoot network issues

November 15, 2015 Larry Catt Leave a comment

Troubleshooting of Oracle networking issues can be a bit more complicated than a general database issue because it involves the client configuration, network, and server configuration. On the client side we have determine the method used for connection and that all the parameter used to resolve the server side connection are correct. Ensuring the network connection from the client machine to the server is also critical and will normally include network administrative support. Finally, on the server side, the listener process must be properly configured to accept client connection requests and pass them to the appropriate server process. The entire process of determining the solution to an Oracle networking issue can be very convoluted. It helps to eliminate first what is not the problem. This article outlines steps to address networking issues in a systematic process. It addresses the three areas of network issues separately and if all work should make for a fluid network connection between oracle client and oracle database server. It is assumed that the network connection has worked in the past and all appropriate files have been configured.

Server side network troubleshooting:

Logon to your oracle database server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Thu Jan 5 07:25:33 EST 2015 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$

Verify that the listener process is running with the command lsnrctl status.

[oracle@linux2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:46:48

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.102)(PORT=1521)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

[oracle@linux2 ~]$

The listener process is not running start the process with lsnrctl start.

[oracle@linux2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:47:01

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.102)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.102)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 05-JAN-2015 10:47:01

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.102)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 ~]$

Once listener has been started attempt to use the listener with tnsping utility.

[oracle@linux2 ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:48:42

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (10 msec)

[oracle@linux2 ~]$

This shows that the listener is accepting connection, but there may be issues in the database such as dispatcher or server processes that are beyond this instruction set.

Network troubleshooting between client and server:

From the client machine open a command prompt, ensure that you can successfully ping the client machine.

C:\Users\MYWINDOWS>ping 192.168.0.102

Pinging 192.168.0.102 with 32 bytes of data:

Reply from 192.168.0.102: bytes=32 time<1ms TTL=64

Ping statistics for 192.168.0.102:

Packets: Sent = 3, Received = 3, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

Minimum = 0ms, Maximum = 0ms, Average = 0ms

Control-C

C:\Users\MYWINDOWS>

Success full connection to server machine. If this connection fails, contact your network administrator.

Client side:

Client side network troubleshooting:

Logon to your oracle client machine with normal user account.
Open a command prompt and attempt a tnsping to your alias machine

C:\Users\MYWINDOWS> tnsping orcl

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:48:42

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (10 msec)

C:\Users\MYWINDOWS>

If this fails verify that the values for POTOCAL, HOST, PORT, SERVICE_NAME are correct for the server and database service you are seeking to connect to.

NOTE: You can trace oracle net communications at the server level and client level this gives very detail logs of the communications occurring and very helpful in troubleshooting network connectivity issues.

Larry Catt

OCP

12c, Administration, alert / logs, testing, troubleshooting

Oracle 12c – Troubleshoot database issues

October 8, 2015 Larry Catt Leave a comment

Oracle uses the ADR (Automatic Diagnostic Repository) directory structure to store all log files concerning the health of the database. This directory structure is stored under the $ORACLE_BASE/diag directory. Each background process of the Oracle RDBMS maintains its own logs in addition to recording information to the central alert log. Two versions of oracle logs are maintained by the ADR: XML and plain text. Background processes may place more detailed information into trace files referenced in the log file messages. General steps for troubleshooting database issues.

Logon to SQLPLUS with sysdba privileges.
Determine the location of each log and trace file type by quering the view V$DIAG_INFO as shown below:

SQL> set linesize 200

SQL> column name format a30

SQL> column value format a150

SQL> select name, value from v$diag_info;

NAME VALUE

—————————— ——————————————————————————————————————————————————

Diag Enabled TRUE

ADR Base /opt/app/oracle/diag

ADR Home /opt/app/oracle/diag/diag/rdbms/orcl/ORCL

Diag Trace /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/trace

Diag Alert /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/alert

Diag Incident /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/incident

Diag Cdump /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/cdump

Health Monitor /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/hm

Default Trace File /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/trace/ORCL_ora_15788.trc

Examine the log files starting with the alert log in directory: $ORACLE_BASE/diag/rdbms/<DB_NAME>/<SID>/trace directory.

This alert log will contain the following:

Internal errors (ORA-00600)
Block Corruption errors (ORA-01578)
Deadlock errors (ORA-00060)
All DDL statements: CREATE, ALTER, and DROP.
All SHUTDOWN, STARTUP, and ARCHIVELOG statements.
Errors with shared processes, server process, and dispatcher processes.
Errors from automatic refresh operations in materialized views.
Values of non-default initialization parameters.
Background and Archiving processes.

Diagnostics is based on a problem or incident:

PROBLEM – critical error in the database, internal error such ORA- error.

INCIDENT – is a single occurrence of a problem and is tracked by a specific number.

Each INCIDENT records the following:

Entry is made to alert log.
Incident alert is sent to EM.
Diagnostic info is sent to dump file in the ADR subdirectory for that incident type.

Take corrective action based on information received from INCIDENT referenced in logs and trace files.
Re-execute process causing original issue and repeat troubleshooting if necessary.
Additional steps of SQL TRACING and getting assistance from ORACLE SUPPORT may be required in some situations.

Larry Catt

OCP

Catt Solutions

Category Archives: Administration

Use ILM feature: Automatic Database Optimization (ADO)

Oracle 12c – Use Oracle Database Migration Assistant for Unicode

Oracle 12c – Use Index enhancements

Oracle 12c – Use Flash Cache

Oracle 12c New Feature – RMAN

Oracle 12c – Use Direct NFS

Oracle 12c Moving or renaming Datafile online

Oracle 12c – Unified Audit Data Trail features

Oracle 12c – Troubleshoot network issues

Oracle 12c – Troubleshoot database issues

Oracle tips and tricks.