Category Archives: 12c

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

12c, CDB, Multitenant, PDB

Oracle 12c – Use Resource Manager for a CDB and PDB

July 22, 2016 Larry Catt Leave a comment

Resource manager determines how resources are shared amongst multiple competing tasks within oracle. Resource Management in a CDB environment has two components: CDB level – Manage resources amongst multiple PDBs and has the ability to allocate/limit resources to specific PDB. PDB level – Manages resources within a single PDB. Through using the package DBMS_RESOURCE MANGER, the administrator is able to create Plans, Groups, and Allocations of resources.

Resource Allocation:

Create Resource Plans.
Create Consumer Groups.
Assign PDBs to Groups.
Each PDB is allocated a portion of the total system resources in the CDB.
Inside a PDB resources are allocated to individual sessions in the PDB.
Specify which PDBs shares of resources based on their importance.
Limit CPU usage of individual PDB.
Limit number of parallel servers a PDB can use.
Limit resource usage of different sessions in a single PDB.
Monitor resource usage of PDBs.

Larry Catt

OCP

12c, audit

Oracle 12c – Create and enable audit policies

July 18, 2016 Larry Catt Leave a comment

Oracle 12c has implemented the unified auditing which is based off of system and user level policies. This article describes the use of audit policies.

Audit Policy General Aspects.

A unified audit policy is a named group of audit setting that track a particular aspect of user behavior in DB.
The CREATE AUDIT POLICY is used to create unified audit policy.
Multiple audit policy can be active at any given time.
AUDIT and NOAUDIT statements enable and disable.
The AUDIT statement can include or exclude specific users.
AUDIT and NOAUDIT can enable audit application context values.
Activities that can be audited:
1. USER ACCOUNTS, ROLES, and PRIVILEGEs
2. OBJECT ACTION: drop table; running a procedure.
3. APPLICATION CONTEXT VALUES.

You should group auditing into related policies and attempt to minimize the number of policies defined.
Many policies increase overhead in the UGA.
The CREATE AUDIT POLICY statement creates a first class object in the SYS schema not in the schema of the creator.
Once policy is created, it must be enabled with the AUDIT statement with POLICY clause.
CREATE AUDIT POLICY and AUDIT statement do not affect current sessions, user must disconnect and reconnect for auditing to start.
CLAUSES of the AUDIT statement.

BY — Used to apply the unified audit policy to one or more users.

AUDIT POLICY change_table_pol BY ocpuser;

EXCEPT — Used to exclude users from the unified audit policy.

AUDIT POLICY change_table_pol EXCEPT jtkirk, jlpicard;

WHENEVER SUCCESSFUL — Records only successful executions of the audited activity.

AUDIT change_table_pol WHENEVER SUCCESSFUL;

WHENEVER NOT SUCCESSFUL — Records only failed executions of the audited activity.

AUDIT change_table_pol WHENEVER NOT SUCCESSFUL;

Note the following:

WHENEVER — If the WHENEVER clause is omitted, then both failed and successful user activities are written to the audit trail.

BY/ EXCEPT — A unified audit policy can be enabled with either the BY clause or the EXCEPT clause, but not both simultaneously.

AUDIT… BY — If multiple AUDIT statements are executed on the same unified audit policy with different BY users, all of the specified users are audited.

AUDIT… EXCEPT — If multiple AUDIT statements are executed on the same unified audit policy with different EXCEPT users, only the last exception user list is used.

COMMON policies — Common unified audit policies can only be enabled from the root and only for common users.

LOCAL policies — Local audit policies can only be enabled from the PDB to which it applies.

No audit policy is necessary to record RMAN activities. RMAN activities are recorded automatically.

RMAN_SESSION_RECID — Recovery Manager session identifier. Together with the RMAN_SESSION_STAMP column, this column uniquely identifies the Recovery Manager job. RMAN_SESSION_STAMP — Timestamp for the session.

RMAN_OPERATION — The Recovery Manager operation executed by the job.

RMAN_OBJECT_TYPE — Type of objects involved in a Recovery Manager session.

RMAN_DEVICE_TYPE — Device associated with a Recovery Manager session.

Larry Catt

OCP

12c, Multitenant, PDB

Startup Single PDB

July 13, 2016 Larry Catt Leave a comment

In Oracle 12c multitenant databases consist of a container database CDB which contain one or more pluggable databases PDB. Startup of the PDB must be done after the CDB it is contained in has been started. This procedures covers the startup of a single PDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 24 14:55:04 EDT 2016 on pts/1

[oracle@linux2 ~]$

Logon to your CDB where the PDB you wish to start is stored.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 25 09:41:06 2016

Connected to an idle instance.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

View the available PDBs within the CDB with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

SQL>

NOTE: MOUNTED is the closed state for a PDB while it CDB is open.

You have four options for PDBs to choose from:
1. MOUNTED – PDB closed but CDB open.
2. OPEN MIGRATE – PDB is open for upgrade and patching.
3. OPEN READ ONLY – PDB is open for reads but not writes.
4. OPEN READ WRITE – PDB is open for all operations.

All of this modes can be moved to and from with the SQL statement: ‘ALTER PLUGGABLE DATABASE <PDB_NAME> <OPTION>; In this step we will move PDB1 from mounted to open read write. NOTE: read write is optional.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

SQL>

You can close the PDB with close option.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

SQL>

You can actually specify the read write option and it will be accepted.

SQL> alter pluggable database pdb1 open read write;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

SQL>

You cannot move from read write to simply read option, you have to shut down the PDB first.

SQL> alter pluggable database pdb1 open read only;

alter pluggable database pdb1 open read only

ERROR at line 1:

ORA-65019: pluggable database PDB1 already open

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ ONLY

SQL>

The other options are self-evident. This completes startup of single PDB within its CDB

Larry Catt

OCP

Catt Solutions

Category Archives: 12c

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 12c – Use Resource Manager for a CDB and PDB

Oracle 12c – Create and enable audit policies

Startup Single PDB

Oracle tips and tricks.