Oracle 12c – Apply and review patches

April 14, 2016 Larry Catt Leave a comment

In Oracle 12c, Patching is performed to correct discovered bugs and close security vulnerabilities. Oracle provides four types of Patches:

Interim Patches – single/collection of bug fixes or one-offs.

Diagnostic Patches – Diagnose or verify a fix.

Patch Set Updates (PSU) – Collection of high impact, low risk, proven fixes for products and components

Critical Patch Updates (CPU) – Collection of patches for security vulnerabilities.

Patching of the RDBMS can be done with two methods: Manually or EM Cloud Control Patch Wizard. Additionally, the patching processes have added: Online Patching and Queryable Inventories. This article will give a brief overview of these Features.

Manual Patching

Step in manually patching are:

Logon to oracle support https://support.oracle.com
Click Patches & Updates tab.
Enter the patch number you are looking for on Patches % Updates page.
Click download on patch for your OS
Click download Patch Metadata.
Review the documentation for specific installation instructions.
Use the OPatch utility to install all patches.

EM Cloud Control Patch Wizard

The EM Cloud Control Patch Wizard has the following benefits over Manual Patching:

Integrate Patch work with Oracle support and provides single view to determine patch needs.
End to End workflow of patching, resolving patch conflicts.
Easy determination of Patch needs for single system.
Patch plans can be saved as templates.
Out of place patching is possible for support of RAC and GRID infrastructure.
EM Cloud allows you create a patch plan to apply patches to one or more targets. A patch can be added to plan only if it’s for same release and OS.

EM Cloud can apply patches with two methods:

Online Mode – EM connects to Oracle Support, searches for necessary patches, resolves any conflicts and adds them to patch plan.
Offline Mode – Used when EM cannot connect to Oracle Support. EM searches for patches manually uploaded to software library and adds them to patch plan.

Online Patches

Before 11g all patches require relink of RDBMS libraries and a shutdown of system. Now a large number of Patches can be done online, they contain .so files and do not require relink of RDBMS.

Benefit of Online Patches:

No downtime.
persistent across shutdown
Allow rolling patches in RAC
Fast install.

Downside of Online Patches:

Require more memory
Online patches not available for all OS
Not all patches are hot patches.

Queryable Patch Inventory

Queryable Patch inventory is new 12c, implemented with DBMS_QOPATCH package and provides view of patches installed just as OPATCH lsinventory command. DBMS_QOPATCH queries the OUI Oracle Universal Installer inventory to gather this metadata.

It provides the following:

Query patches installed from SQL prompt.
Creates reports and provides validation across many installs.
Checks patches on RAC from single node.

DBMS_QOPATCH components:

GET_OPTACH_BUGS – Provides list of bug fixes per patch number.

GET_OPATCH_COUNT – Provides total number of patches installed.

GET_OPATCH_DATA – Provides top level patch info – number, creation date.

GET_OPATCH_FILES – Provides list of files updated by patch number.

GET_OPATCH_INSTALL_INFO – Provides info such as ORACLE_HOME, patch, inventory_location

GET_OPATCH_LIST – List of Patches installed.

GET_OPATCH_LSINVENTORY – Whole patch inventory

GET_OPATCH_OLAYS –Overlay for specific patch

GET_OPATCH_PREQS – Provides prerequisites for a patch

GET_OPATCH_XSLT – Provides style sheet for opatch XML inventory

GET_PENDING_ACTIVITY – Provides SQL patches applied to single inventory from binaries.

GET_SQLPATCH_STATUS – Patch status from SQL patch registry.

IS_PATCH_INSTALLED – Patches applied info from XML inventory

PATCH_CONFLICT_DETETION – Patch in conflict for give file.

SET_CURRENT_OPINST – inventory information for specific node in RAC.

Larry Catt

OCP

12c, audit

Oracle 12c – Enable and configure Unified Audit Data Trail

April 13, 2016 Larry Catt Leave a comment

In 12c the Unified Audit Trail consolidates audit information form multiple sources and makes this information available in a standard format in the UNIFIED_AUDIT_TRAIL dictionary view.
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 following:
Unified audit policies and AUDIT settings

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, SQL

Oracle 12c – Adaptive Execution Plan

April 10, 2016 Larry Catt Leave a comment

In Oracle 12c Adaptive Execution Plans allows optimizer to make Janision on SQL at runtime and in mid execution. It will actually make sub-plans and collect statistics to make final Janision on execution. Currently release only supports adaptive execution plans for join and parallel operations.

This article will demonstrate the use of Adaptive Execution Plan against a set test tables.

Logon server as the oracle software owner and logon to SQLPLUS as the user scott.

[root@linux2 ~]# su – oracle

Last login: Thu Jan 15 13:05:52 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 schema]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 23 08:12:04 2016

Last Successful login time: Fri Jan 23 2016 08:10:57 -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 the user test with dba privileges.

SQL> create user test identified by test

default tablespace users quota unlimited on users; 2

User created.

SQL> grant dba to test;

Grant succeeded.

Create a test table test_tab1 and 200 insert values.

SQL> create table test.test_tab1(

col_a number,

col_b varchar2(10),

constraint tab1_pk primary key(col_a)); 2 3 4

Table created.

SQL> Janlare

2 val_a number:=1;

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

5 begin

7 while val_a<200

8 loop

9 insert into test.test_tab1(col_a, col_b)

10 values(val_a, val_b);

12 val_a:=val_a+1;

13 if val_b=’z’

14 then

15 val_b:=’a’;

16 else

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

18 end if;

19 end loop;

20 commit;

21 end;

22 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

Create test table test_tab2 with foreign key back to test_tab1. NOTE: Adaptive Execution only work with join operations.

SQL> create table test.test_tab2(

tab1_col_a number,

col_a number,

col_b varchar2(10),

constraint tab2_tab1_fk foreign key (tab1_col_a) references test.test_tab1(col_a)); 2 3 4 5

Table created.

SQL>

Populate test table test_tab2.

SQL> insert into test.test_tab2(tab1_col_a, col_a, col_b)

select col_a, col_a+1, ‘testing’

from test.test_tab1 where 1=mod(col_a,2); 2 3

100 rows created.

SQL> commit;

Commit complete.

SQL>

Perform an explain for a select join operation on the two tables.

SQL> explain plan for select * from test.test_tab2 b, test.test_tab1 a

where a.col_a=tab1_col_a 2

3 ;

Explained.

SQL>

Now examine the execution plan and not the adaptive remark at the bottom.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1368181317

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

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

| 0 | SELECT STATEMENT | | 100 | 5300 | 6 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 100 | 5300 | 6 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| TEST_TAB2 | 82 | 2706 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| TEST_TAB1 | 82 | 1640 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 – access(“A”.”COL_A”=”TAB1_COL_A”)

Note

—–

– dynamic statistics used: dynamic sampling (level=AUTO)

– this is an adaptive plan

20 rows selected.

SQL>

Now use the format => adaptive reference in the dbms_xplan.display function to get the adaptive plan.

SQL> SET LINESIZE 200 PAGESIZE 100

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => ‘adaptive’));SQL>

PLAN_TABLE_OUTPUT

SQL_ID ff5spha9pjuyu, child number 0

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

select * from table(dbms_xplan.display)

Plan hash value: 2137789089

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

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

| 0 | SELECT STATEMENT | | | | 29 (100)| |

| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |

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

13 rows selected.

SQL>

This completes testing of adaptive execution plan.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup all PDB in a CDB

April 8, 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 and shutdown of all PDBs in a CDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Mar 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 Mar 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

PDB2 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 all <OPTION>; In this step we will move all the PDBs from mounted to open read write. NOTE: read write is optional.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

You can close all the PDB with close option.

SQL> alter pluggable database all close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

SQL>

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

SQL> alter pluggable database all open read write;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

You cannot move from read write to simply read option with all PDB and you cannot move all PDB into read only mode. You can only open all PDBs in read write mode and close all PDBs with all option.

SQL> alter pluggable database all open read;

alter pluggable database all open read

ERROR at line 1:

ORA-00922: missing or invalid option

SQL> alter pluggable database all close;

Pluggable database altered.

SQL> alter pluggable database all open read;

alter pluggable database all open read

ERROR at line 1:

ORA-00922: missing or invalid option

SQL>

There are no other options to move all PDBs open mode. This completes startup and shutdown of all PDB within its CDB.

Larry Catt

OCP

12c, listener, network, PDB

Configure listener.ora for PDB

April 6, 2016 Larry Catt Leave a comment

With Oracle 12c multitenant databases connection is still normally performed through the Oracle networking files of listener.ora, tnsnames.ora, and sqlnet.ora. PDBs are contained within a specific CDBs thus no configuration of the listener is needed. The listener process will pick up all active PDBs on startup if the PDB is started within its CDB. However, you can test the connection to a PDB through the listener process as shown below.

Logon to your Oracle Server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Feb 24 09:57:56 EDT 2016 on pts/0

[oracle@linux2 ~]$

Now start the listener with the command: lsnrctl start

[oracle@linux2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 10:58:06

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=10.30.15.75)(PORT=1521)))

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-FEB-2016 10:58:06

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=10.30.15.75)(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 ~]$

List all serviced SIDs and Services with the command: lsnrctl status.

[oracle@linux2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 11:08:45

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-FEB-2016 10:58:06

Uptime 0 days 0 hr. 10 min. 39 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 admin]$

NOTE: The Service pdb1 is being handled by the Instance cdb1. The SID you will see at the OS layer will be cdb1 and you will not see any SID named pdb1. PDB1 is a service which is handled by the SID cdb1.

You can test the listener configuration by suppling the tnsping utility with the <machine_IP>:<Port>/<service_name> as below:

[oracle@linux2 admin]$ tnsping 10.30.15.75:1521/pdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 11:38:30

Used parameter files:

Used HOSTNAME adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

OK (10 msec)

[oracle@linux2 admin]$

This completes configuration of listener.ora file for PDB.

Larry Catt

OCP

12c, Performance

Oracle 12c – Manage memory

April 4, 2016 Larry Catt Leave a comment

Oracle 12c provides a single parameter allowing the database instance to automatically manage all memory pools called MEMORY_TARGET. When MEMORY_TARGET is set to a given value, oracle process grabs that much memory and allocates it to the various memory pools. MEMORY_TARGET parameter is dynamic and can be adjusted with the database online. MEMORY_MAX_TARGET parameter is an optional method to specify the maximum amount of memory for oracle to use. MEMORY_MAX_TARGET is not dynamic and cannot be changed without restarting database. MEMORY_MAX_TARGET serves as an upper limit to MEMORY_TARGET so the parameter is not set too high.

AMM (Automatic memory management) is the database instance configuration which manages and distributes memory allocations between SGA and PGA automatically and redistributes based on changing requirements. Databases created by DBCA will have AMM enabled by default.

Automatic Shared Memory Management ASMM was the version before Oracle 12c and AMM. In ASMM the total amount of memory for SGA is specified by SGA_TARGET parameter, oracle will automatically distribute the memory specified by SGA_TARGET to the pools:

LOG_BUFFER, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and DB_nK_CACHE_SIZE.

Any memory allocated by parameters to above 4 pools will be subtracted from SGA_TARGET allocation and the rest is distributed amongst: SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, STREAMS_POOL_SIZE. BOTH ASMM and AMM use database statistics and cannot be used if the parameter STATISTICS_LEVEL is set to BASIC.

Setup AMM by changing the following initialization parameters:

ALTER SYSTEM SET MEMORY_TARGET = nM;

ALTER SYSTEM SET SGA_TARGET = 0;

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;

Optionally, you can also set the MEMORY_MAX_TARGET parameter:

ALTER SYSTEM SET MEMORY_MAX_TARGET = nM scope = SPFILE;

Restart the database.

Oracle Initialization Parameters concerning Memory Management:

MEMORY_TARGET – When set the Oracle grabs the memory at startup and gives 60% to SGA and 40% to PGA by default. It redistribute memory as needed. IF not set AMM is not in use, even if a value is set for MEMORY_MAX_TARGET.

MEMORY_MAX_TARGET – Sets the maximum amount of memory that Oracle will grab. If not set, it default to MEMORY_TARGET.

SGA_TARGET – Parameter is not needed if using AMM, if it is defined the value becomes the minimum amount of memory allocated by AMM.

PGA_AGGREGATE_TARGET – Parameter is not needed if using AMM, if it is defined the value becomes the minimum amount of memory allocated by AMM.

Larry Catt

OCP

Oracle

Oracle 12c – Use Online operation enhancements

April 2, 2016 Larry Catt Leave a comment

Online table redefinition allows to restructure table with little affect to table availability. The table is open for queries and DML for most of the redefine procedure, but is locked in exclusive mode for a short time in process. Redefinition is available in EM Cloud and through DBMS_REDEFINITION package.

Three enhancements in 12c for redefine:

Multiple Partitions – you can create multiple partitions in single redefinition execution. This reduces time for re-partitioning but requires more space, because an interim table is built for each partition creation.
Tables with VPD Policies – You can now redefine tables with Virtual Private Database policies. The copy_vpd_opt parameter of START_REDEF_TABLE procedure must set to handle VPD policies during process. Parameter can be set to:
1. CONS_VPD_NONE – This is the default, for use when VPD not in user, error will occur on a VPD table.
2. CONS_VPD_AUTO – VPD policies will be copied to new table during redefinition process.
3. CONS_VPD_MANUAL – VPD policies must be manually moved to new table, done when structural changes are made that affect VPD policies.
Lock Timeout for FINISH_REDEF_TABLE – You can specify a number of seconds that a redefine process will hold an exclusive lock for swapping interim table. If it is exceeded redefine will exit.
REDEF_TABLE – New procedure in the DBMS_REDEFINITION package, which allows for table redefinition in a single step to change the following:
1. Change the tablespace for a table, partition, index or LOB storage.
2. Compression changes including table, partition, index or LOBs
3. LOB column changes for SECUREFILE or BASICFILE storage.

Larry Catt

OCP

Catt Solutions

Monthly Archives: April 2016

Oracle 12c – Apply and review patches

Oracle 12c – Enable and configure Unified Audit Data Trail

Oracle 12c – Adaptive Execution Plan

Startup all PDB in a CDB

Configure listener.ora for PDB

Oracle 12c – Manage memory

Oracle 12c – Use Online operation enhancements

Oracle tips and tricks.