Oracle 12c – Block Change Tracking

August 20, 2016 Larry Catt Leave a comment

Oracle 12c provides block change tracking to increase performance of incremental backups by recording change blocks for each datafile, thus stopping RMAN from scanning each datafile to determine changes. This feature must be enabled by default it is disabled in the database. Tracking information on each changed block is stored in a file located in the directory defined by parameter DB_CREATE_FILE_DEST and is only used by level 1 backups. Additionally, the tracking must be enabled before the level 0 backup is performed in order for level 1 backups to use it. Use ALTER DATABASE command to turn on Block Change Tracking:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

You can also specify the location of the tracking file:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/linux1/rman_change_tracking.file’ REUSE;

Disable Block tracking with:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Larry Catt

OCP

12c, Performance

Oracle 12c – Explain Index enhancements for partitioned tables

August 18, 2016 Larry Catt Leave a comment

Asynchronous Global Index Maintenance

In Oracle 12c the commands DROP PARTITION and TRUNCATE PARTITION only affects metadata. The index maintenance is asynchronous now, thus it does not invalidate indexes on the partitions. You then use UPDATE INDEXES commands to synch indexes during maintenance hours. Limitations of asynchronous global index maintenance are:

Only for heap tables.
Object types in table not supported.
Domain indexes not supported.
Cannot be user SYS

SYS.PMO_DEFERRED_GIDX_MAINT_JOB –

Oracle 12c provides this Automatic scheduled job to execute at 0200 and it updates any indexes impacted by DROP PARTITION and TRUNCATE PARTITION commands. This job can be executed anytime by DBMS_SCHEDULER.RUN_JOB.

Partial Indexes –

In Oracle 12c you can now create local and global indexes on subsets of partitions. Partial indexes do not supported unique indexes and cannot enforce unique constraints. By default, indexes are created as FULL indexes. You create partial indexing using the INDEXING clause at the partition and sub-partition level.

ONLINE Move Partition –

In Oracle 12c, the ALTER TABLE … MOVE PARTITION allows for DML operations to take place while executing. During MOVE operation, global indexes are maintained and rebuilt later by scheduler job.

Larry Catt

OCP

12c, Administration

Oracle 12c – Implement real-time database operation monitoring

August 17, 2016 Larry Catt Leave a comment

This procedure outlines the steps of performing real-time database operation monitoring via the sqlplus console using the package DBMS_SQL_MONITOR.

Start SQLPLUS and connect as a user with the appropriate privileges.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 20 08:00:16 2016

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 user test and table test_tab, than insert 10 miliion records into test.test_tab for monitoring test.

create user test identified by test

default tablespace users quota unlimited on users;

grant dba to test;

create table test.test_tab(col_a number,

col_b varchar2(10));

Jullare

val_a number:=1;

val_b varchar2(10):=’a’;

begin

while val_a<20000001

loop

insert into test.test_tab(col_a, col_b)

values(val_a, val_b);

val_a:=val_a+1;

if val_b=’z’

then

val_b:=’a’;

else

val_b:=chr(ascii(val_b) + 1);

end if;

end loop;

commit;

end;

Execution:

SQL> create user test identified by test

default tablespace users quota unlimited on users;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> create table test.test_tab(col_a number,

col_b varchar2(10));

Table created.

SQL> Jullare

val_a number:=1;

2 3 val_b varchar2(10):=’a’;

4 begin

5 while val_a<20000001

6 loop

7 insert into test.test_tab(col_a, col_b)

8 values(val_a, val_b);

9 val_a:=val_a+1;

10 if val_b=’z’

11 then

12 val_b:=’a’;

13 else

14 val_b:=chr(ascii(val_b) + 1);

15 end if;

16 end loop;

17 commit;

18 end;

19 /

PL/SQL procedure successfully completed.

SQL>

Define a variable to hold the execution ID.

var opid NUMBER

Begin the database operation.

EXEC :opid := DBMS_SQL_MONITOR.BEGIN_OPERATION(‘op_test’);

Run the queries in the operation.

SELECT col_a FROM test.test_tab order by col_a;

SELECT col_b FROM test.test_tab order by col_a;

End the database operation.

EXEC DBMS_SQL_MONITOR.END_OPERATION(‘op_test’, :opid);

Confirm that the database operation completed.

SQL> SELECT dbop_name, status FROM v$sql_monitor WHERE dbop_name = ‘op_test’;

DBOP_NAME STATUS

—————————— ——————-

rtsm_op DONE

SQL>

This completes manual execution of real_time database operation monitoring with DBMS_SQL_MONITOR.

Larry Catt

OCP

12c, Administration, Performance

Oracle 12c – Use Resource Manager to manage resources

August 13, 2016 Larry Catt Leave a comment

Oracle Database Resource Manager is designed to optimize resource allocation among concurrent database sessions. It prevents from the OS making resource decisions during high overhead periods without awareness of database needs. Resource Manager gives the database more control over resource allocation and gives the ability to place sessions into groups and allocated resources to those groups, through resource plans. Oracle 12c comes with the following Resource Plans by default:

DEFAULT_MAINTENANCE_PLAN – default plan for maintenance windows.
DEFAULT_PLAN – Default gives priority to SYS_GROUP and gives minimal resources to maintenance and diagnostics operations.
DSS_PLAN – for data warehouse gives priority to DDS queries and less to non-DDS and ETL operations.
ETL_CRITICAL_PLAN – for data warehouse gives priority to ETL over DDS.
INTERNAL_QUIESCE – quiescing the database, must be done manually with QUIESCE command.
MIXED_WORKLOAD_PLAN – prioritized interactive processes over batch.

Three elements of Resource Management are:

Resource Consumer Group – group of sessions based on resource needs.
Resource Plan – directives that detail how resources are allocated to group.
Resource Plan Directive – Associates resource consumer group with a particular plan and how resources are allocated to consumer group.

Resource plan directives can limit CPU and I/O for sessions in the group. This is done by a processes call switching, which specifies an action to take when a call exceeds the limit. Resource plan directive attribute SWITCH_GROUP determines which action to take. If the attribute is a consumer group name action 1 will be taken, if the attribute is KILL_SESSION action 2, and if attribute is CANCEL_SQL action 3.

The possible actions are:

Session is switched to a consumer group with lower resource allocation.
Session is killed.
Sessions current SQL is aborted.

Attributes which control I/O and CPU are as follow: NOTE: unlimited means no limit.

SWITCH_TIME – CPU time in seconds before switch
SWITCH_IO_MEGABYTES – amount of I/O read and writes before switch.
SWITCH_IO_REQS – number of I/O requests before switch.

Two attributes that can modify behavior of resource plan switching:

SWITCH_ESTIMATE – If TRUE – database estimate execution time of each call. if it exceeds SWITCH_TIME attribute, the session is moved to Default is FALSE.
SWITCH_FOR_CALL – If TRUE – session switched because of exceeding resource is returned to original group once completed. Default is NULL.

Larry Catt

OCP

12c, alert / logs, Performance

Oracle 12c – Monitor database alerts

August 11, 2016 Larry Catt Leave a comment

Alert Logs and Trace Files:

All Oracle background process have their own trace files where information about errors are dumped to when detected. Alert logs also contain information about database errors, with less detail and normally point towards specific trace file. Message in alert log include:

Init parameter with non-default values or other notations.
all errors: internal ORA-600, block corruption ORA-1578 and deadlock ORA-60
Admin DDL CREATE, ALTER, and DROP and startup and shutdown.

Alert logs are maintained in both XML and plain text. The ADRCI utility is used to view XML alert logs. Both Alert and trace files are stored under the Automatic Diagnostic Repository directory structure. The init parameter MAX_DUMP_FILE_SIZE limits the size of trace files to a set number of OS blocks. You cannot limit size of alert logs, but it is a good practice to periodically rename the alert file to reduce file size oracle is using. The database instance will automatically create a new alert log with appropriate name and this renaming process can be done with the database online.

Statistics:

Statistics are generated by the Oracle 12c database to help increase level of performance and to all the optimizer to decide on the best execution plans of SQL statements. If the SQL_TRACE init parameter is set to TRUE, performance statistics will be generated for SQL statements. SQL tracing can be set at session level by ‘ALTER SESSION SET SQL_TRACE’ command. Trace files are written to Automatic Diagnostic Repository directory structure. The DBMS_SESSION and DBMS_MONITOR packages can be used to control SQL tracing for a session.

Adaptive Thresholds:

You can monitor database continuously with adaptive thresholds. By setting warning and critical alerts for system metrics. This can be done using moving window metrics. Two types of adaptive thresholds:

Percentage of maximum – Defined as the max of data/resource in moving window. like 80% of observed.

Significance level – Defined to a unusual value of threshold, set to one of the following values

High (.95)
Very High (.99)
Severe (.999)
Extreme (.9999)

Larry Catt

OCP

12c, PDB

Connection to PDB

August 6, 2016 Larry Catt Leave a comment

In Oracle 12c multitenant databases CDBs are containers databases which hold oracle internals, supplied accounts and processes and client PDBs pluggable databases for applications. PDBs are services within a CDB and thus are connected to through the CDB they are contained inside of or through oracle networking. This procedure demonstrates the connection through both processes.

Connection via CDB

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

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 24 11:43:05 EDT 2016 on pts/1

[oracle@linux2 ~]$

Verify that you have environment has the appropriate CDB SID set with command echo $ORACLE_SID. If it is not the appropriate SID change which the export ORACLE_SID= statement.

[oracle@linux2 ~]$ echo $ORACLE_SID

cdb1

[oracle@linux2 ~]$ export ORACLE_SID=cdb1

[oracle@linux2 ~]$ echo $ORACLE_SID

cdb1

[oracle@linux2 ~]$

Now you can connect directly into the CDB referenced by the environmental parameter ORACLE_SID with the command sqlplus / as sysdba. NOTE: You are connecting with the administrative account

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 24 12:26:00 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>

Once you are connected to the CDB, you can now change your session to connect to the PDB of interest. In this example we will connect to pdb1. You do this with the command alter session set container=<PDB_name>l

SQL> show con_name;

CON_NAME

——————————

CDB$ROOT

SQL> alter session set container = pdb1;

Session altered.

SQL> show con_name

CON_NAME

——————————

PDB1

SQL>

This completes connecting the PDB from CDB.

Connection via Oracle networking

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

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 24 11:43:05 EDT 2016 on pts/1

[oracle@linux2 ~]$

View the database connection available in the listener process with command lsnrctl status.

[oracle@linux2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-JUN-2016 14:03:44

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

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 24-JUN-2016 14:02:17

Uptime 0 days 0 hr. 1 min. 26 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=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 2 instance(s).

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

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

Service “cdb1XDB” has 1 instance(s).

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

Service “pdb1” has 1 instance(s).

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

The command completed successfully

[oracle@linux2 ~]$

See the PDB pdb1 in the container cdb1. Connect directly to the PDB via oracle listener by referencing this listener server.

[oracle@linux2 ~]$ sqlplus system/password@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 24 14:57:43 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>SQL> show con_name

CON_NAME

——————————

PDB1

SQL>

This completes connection to PDB via oracle networking.

Larry Catt

OCP

12c, Administration, ILM

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

Catt Solutions

Monthly Archives: August 2016

Oracle 12c – Block Change Tracking

Oracle 12c – Explain Index enhancements for partitioned tables

Oracle 12c – Implement real-time database operation monitoring

Oracle 12c – Use Resource Manager to manage resources

Oracle 12c – Monitor database alerts

Connection to PDB

Use ILM feature: Automatic Database Optimization (ADO)

Oracle tips and tricks.