New Resource Manager options on Oracle 12c R2 Muntitenant

Since its introductions Oracle Resource Manager has been a key tool to guarantee performance stability and predictability, regulating the access to the database resources.

In the era of extreme consolidation, the role of Oracle Resource Manager is more important then ever, and Oracle Multitenant has extended its functionalities.

In Oracle 12.1 the resource manager was capable to handle:

  • Parallel server processes
  • CPU

The version 12.2 the following options have been introduced:

  • I/O rate limits for PDBs (Not supported on Exadata enviroment where I/O Resource Manager exists)
  • Memory  Management
  • Performance Profiles
  • Resource Monitoring

 

How to limit a PDB I/O consumption

--Limiting the number of I/O per second.
Alter System set MAX_IOPS = 9500 scope = BOTH;

--Limiting the amount of MB per second.
Alter System set MAX_MBPS = 70 scope = BOTH;

 

Memory  Management

Optionally starting from Oracle 12.2 it is possible to limit the PDB memory utilization using the following parameters:

  • SGA_Target                       – Max SGA size for PDB
  • SGA_Min_Size                   – Amount of guaranteed SGA size for PDB.
  • DB_Cache_Size                  – Amount of guaranteed Buffer Cache size for PDB.
  • DB_Shared_Pool_Size      – Amount of guaranteed Shared Pool size for PDB.
  • PGA_Aggregate_Limit      – Max PGA size for PDB.
  • PGA_Aggregate_Target    – Target PGA size for PDB.

 

Performance Profiles

The Profiles allows to create standard and common  Resouce Profiles, based for example on the different SLA levels (platinum, gold, silver), to grant directly to the PDBs.

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
 plan => 'PDB_Silver', 
 profile => 'silver', 
 shares => 2, 
 utilization_limit => 30, 
 parallel_server_limit => 20,
 memory_limit=> 30
);

 

Resource Monitoring

The new system view  V$RSRCPDBMETRIC can be used to monitor  chargeback the I/O and memory consumpion.

SELECT r.CON_ID, p.PDB_NAME, r.IOPS,r.SGA_BYTES, r.SHARED_POOL_BYTES
FROM V$RSRCPDBMETRIC r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID;

 


 

New Oracle version (12.2.0.1) old BUG!

 

In June 2016 I posted the following BUG: Bug on Oracle 12c Multitenant & PDB Clone as Snapshot Copypromising to post an update once the version 12cR2 is available, because in the service request, originally opened with the version 12.1.0.2 Oracle stated that the bug would be fixed in 12cR2.

I was so impatient, that just few hours after the general availability of the Oracle Database 12c Release 2  I created a new cluster and tested the resolution.

 

For the record, it states that the resolution of this bug is important for one of my clients, where we have implemented the snapshot PDB on the application development lifecycle.

 

So let’s see if the bug has been fixed!

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 1 21:06:54 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> CREATE PLUGGABLE DATABASE PDBACFS1_SNAP1 from PDBACFS1 SNAPSHOT COPY;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE PDBACFS1_SNAP1 OPEN instances=all;

Pluggable database altered.

SQL> select CON_ID, NAME, OPEN_MODE, SNAPSHOT_PARENT_CON_ID from v$pdbs where NAME in ('PDBACFS1','PDBACFS1_SNAP1');

 CON_ID     NAME               OPEN_MODE  SNAPSHOT_PARENT_CON_ID
---------- ------------------- ---------- ----------------------
 5          PDBACFS1            READ WRITE
 6          PDBACFS1_SNAP1      READ WRITE               <-- This should be 5 but is NULL

2 rows selected.

 

To a certain point of view progress has been made, in version 12.1.0.2 the column SNAPSHOT_PARENT_CON_ID was always zero (0) now is null!

I’m sorry for my customer, I’ll keep testing hoping …

 

 

Oracle 12c – Unified Audit Trail

 

Oracle 12c introduces “Unified Audit Trail” a faster, easier to access and more secure audit system.

It optionally allows to stage the audit records in a dedicated memory buffer (UNIFIED_AUDIT_SGA_QUEUE_SIZE), where they are temporarily grouped before being written into the audit table via batch transactions.

This new audit configuration substantially reduces the transactional overhead generated by the auditing.

 

Important improvements have also done to simplify the utilization:

– One single audit trail for any audit data, in fact  UNIFIED_AUDIT_TRAIL view replaces SYS.AUD$/DBA_AUDIT_TRAIL, SYS.FGA_LOGS$/DBA_FGA_AUDIT_TRAIL, DVSYS.AUDIT_TRAIL$, V$XML_AUDIT_TRAIL and the OS audit files in adump.

– All audit data stored in Oracle secure files.

– Role segregation between:

  • DBA responsible to maintain free space and backup.
  •  AUDIT_ADMIN responsible to manage the audit policies and define the data retention.
  • AUDIT_VIEWER in charge of the reports.

 

Unified Audit Trail introduces also new security options important to mention:

– It is activated with a kernel relink and it doesn’t require additional steps or parameters.

– The new AUDSYS table has a Read-Only Protection for all users. Even the DBA privilege can’t manipulate the audit records!

 

How to activate Unified Audit Trail

--Stop all Oracle processes: databases, listener and Enterprise Manager agent.

--Relink Oracle with the uniaud_on option.
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

--Restart all Oracle processes: databases, listener and Enterprise Manager agent.

--Check is Unified Audit Trail is active
SQL> select * from v$option where PARAMETER='Unified Auditing';

INST_ID PARAMETER                       VALUE              CON_ID
------- ------------------------------ ------------------- ----------
 1      Unified Auditing                TRUE                0

 

Optional, but strongly recommended it is possible to relocate the AUDIT segments  from SYSAUX Tablespace to a dedicated one.

SQL> Create tablespace TBS_AUDIT datafile SIZE 2G AUTOEXTEND ON;

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
 audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
 audit_trail_location_value => 'TBS_AUDIT');
END;
/


SQL> select OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, BYTES from dba_segments where TABLESPACE_NAME='TBS_AUDIT';

OWNER           SEGMENT_NAME                    PARTITION_NAME                SEGMENT_TYPE       BYTES
--------------- ------------------------------ ------------------------------ ------------------ ----------
AUDSYS           CLI_SWP$1b2a49f1$1$1           HIGH_PART                      TABLE PARTITION   65536
AUDSYS           CLI_SWP$1b2a49f1$1$1           PART_2                         TABLE PARTITION   65536
AUDSYS           CLI_LOB$1b2a49f1$1$1           HIGH_PART                      INDEX PARTITION   65536
AUDSYS           CLI_TIME$1b2a49f1$1$1          HIGH_PART                      INDEX PARTITION   65536
AUDSYS           CLI_LOB$1b2a49f1$1$1           PART_2                         INDEX PARTITION   65536
AUDSYS           CLI_TIME$1b2a49f1$1$1          PART_2                         INDEX PARTITION   65536
AUDSYS           CLI_SCN$1b2a49f1$1$1           PART_2                         INDEX PARTITION   65536
AUDSYS           SYS_IL0000091784C00014$$       SYS_IL_P241                    INDEX PARTITION   65536
AUDSYS           CLI_SCN$1b2a49f1$1$1           HIGH_PART                      INDEX PARTITION   65536
AUDSYS           SYS_IL0000091784C00014$$       SYS_IL_P246                    INDEX PARTITION   65536
AUDSYS           SYS_LOB0000091784C00014$$      SYS_LOB_P244                   LOB PARTITION     131072
AUDSYS           SYS_LOB0000091784C00014$$      SYS_LOB_P239                   LOB PARTITION     131072

12 rows selected.

 

The introduction of Audit Policies have brought flexibility and granularity on what it is possible to audit, here an example using Oracle sys_context function.

CREATE AUDIT POLICY hr_employees
 PRIVILEGES CREATE TABLE
 ACTIONS UPDATE ON HR.EMPLOYEES
 WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''HR_ADMIN'''
 EVALUATE PER STATEMENT;

AUDIT POLICY hr_employees;

 

 

 

Oracle DB stored on ASM vs ACFS

Nowadays a new Oracle database environment with Grid Infrastructure has three main storage options:

  1. Third party clustered file system
  2. ASM Disk Groups
  3. ACFS File System

While the first option was not in scope, this blog compares the result of the tests between ASM and ACFS, highlighting when to use one or the other to store 12c NON-CDB or CDB Databases.

The tests conducted on different environments using Oracle version 12.1.0.2 July PSU have shown controversial results compared to what Oracle  is promoting for the Oracle Database Appliance (ODA) in the following paper: “Frequently Asked Questions Storing Database Files in ACFS on Oracle Database Appliance

 

Outcome of the tests

ASM remains the preferred option to achieve the best I/O performance, while ACFS introduces interesting features like DB snapshot to quickly and space efficiently provision new databases.

The performance gap between the two solutions is not negligible as reported below by the  AWR – TOP Timed Events sections of two PDBs, sharing the same infrastructure, executing the same workload but respectively using ASM and ACFS storage:

  • PDBASM: Pluggable Database stored on  ASM Disk Group
  • PDBACFS:Pluggable Database stored on ACFS File System

 

 

PDBASM AWR – TOP Timed Events and Other Stats

topevents_asm

fg_asm

 

 

PDBACFS AWR – TOP Timed Events and Other Stats

TopEvents_ACFS.png

fg_acfs

 

Due to the different characteristics and results when ASM or ACFS is in use, it is not possible to give a generic recommendation. But case by case the choise should be driven by business needs like maximum performance versus fast and efficient database clone.

 

 

 

 

New to Oracle Multitenant?

Multitenant is the biggest architectural change of Oracle 12c and the enabler of many new database options in the years to come. Therefore I have decided to write over the time, few blog posts with basic examples of what should be done and not in a multitenant database environment.

 

Rule #1   – What should not be done

If you are a CDB DBA, always pay attention to which container you are connected to and remember that application data should be stored on Application PDB only!

Unfortunately this golden rule is not-enforced by the RDBMS, but it is left in our responsibility as shown on the example below:

oracle@lxoel7n01:~/ [CDB_TEST] sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 21 18:28:23 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

CDB$ROOT SQL>
CDB$ROOT SQL> show user
USER is "SYS"
CDB$ROOT SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

 

Once connected to the ROOT container let see if I can mistakenly create an application table:

CDB$ROOT SQL> CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB); 2 3 4 5 6

Table created.

CDB$ROOT SQL> desc emp_1
 Name                                Null?    Type
 ----------------------------------- -------- ----------------------------
 EMP_ID                                        NUMBER
 EMP_NAME                                      VARCHAR2(25)
 START_DATE                                    DATE
 EMP_STATUS                                    VARCHAR2(10)
 RESUME                                        CLOB


CDB$ROOT SQL> insert into emp_1 values (1, 'Emiliano', sysdate, 'active', ' ');

1 row created.

CDB$ROOT SQL> commit;

Commit complete.


CDB$ROOT SQL> select * from emp_1;

EMP_ID     EMP_NAME                  START_DAT EMP_STATUS RESUME
---------- ------------------------- --------- ---------- ----------------
 1          Emiliano                  21-SEP-16 active

CDB$ROOT SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

The answer is “YES” and the consequences can be devastating…

 

Rule #2   – Overview of Local and Common Entities

Non-schema entities can be created as local or common.  Local entities exist only in one PDB similar to a non-CDB architecture, while Common entities exist in every current and future container.

List of possible Local / Common entities in a Multitenant database:

  • Users
  • Roles
  • Profiles
  • Audit Policies

All Local entities are created from the local PDB and all Common entities are created from the CDB$ROOT container.

Common-user-defined Users, Roles and Profiles require a standard database prefix, defined by the spfile parameter COMMON_USER_PREFIX:

SQL> show parameter common_user_prefix

NAME                              TYPE        VALUE
--------------------------------- ----------- -----------------
common_user_prefix                string      C##

 

Example of Common User creation:

SQL> CREATE USER C##CDB_DBA1 IDENTIFIED BY PWD CONTAINER=ALL;

User created.


SQL> SELECT con_id, username, user_id, common

  2  FROM cdb_users where username='C##CDB_DBA1'  ORDER BY con_id;

    CON_ID USERNAME                USER_ID COMMON
---------- -------------------- ---------- ------
         1 C##CDB_DBA1               102    YES
         2 C##CDB_DBA1               101    YES
         3 C##CDB_DBA1               107    YES
         4 C##CDB_DBA1               105    YES
         5 C##CDB_DBA1               109    YES
         ...

 

Example of Local user creation:

SQL> show con_name

CON_NAME
------------------------------
MYPDB

SQL> CREATE USER application IDENTIFIED BY pwd CONTAINER=CURRENT;

User created.

If we try to create a Local User from the CDB$ROOT container the following error occurs: ORA-65049: creation of local user or role is not allowed in CDB$ROOT

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> CREATE USER application IDENTIFIED BY pwd   CONTAINER=CURRENT;

CREATE USER application IDENTIFIED BY pwd   CONTAINER=CURRENT

                                      *

ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

 

 

Rule #3  – Application should connect through user-defined database services only

We have been avoiding to create user-defined database services for many years, sometimes even for RAC databases. But in Multitenet or Singletenant architecture the importance of user-defined database service is even greater. For each CDB and PDB Oracle is still automatically creating a default service, but as in the past the default services should never be exposed to the applications.

 

To create user-defined database service in stand-alone environment use the package DBMS_SERVICE while connected to the corresponding PDB:

BEGIN
 DBMS_SERVICE.CREATE_SERVICE(
     SERVICE_NAME     => 'mypdb_app.emilianofusaglia.net',
     NETWORK_NAME     => 'mypdb_app.emilianofusaglia.net',
     FAILOVER_METHOD  =>
     ...
      );
 DBMS_SERVICE.START_SERVICE('mypdp_app.emilianofusaglia.net ');
END;
/

The database services will not start automatically after opening a PDB!  Create a database trigger for this purpose.

 

To create user-defined database service in clustered environment use the srvctl utility from the corresponding RDBMS ORACLE_HOME:

oracle@oel7n01:~/ [EFU1] srvctl add service -db EFU \
> -pdb MYPDB -service mypdb_app.emilianofusaglia.net \
> -failovertype SELECT -failovermethod BASIC \
> -failoverdelay 2 -failoverretry 90

 

List all CDB database services order by Container ID:

SQL> SELECT con_id, name, pdb FROM v$services ORDER BY con_id;

    CON_ID NAME                                     PDB
---------- --------------------------------------- -----------------

         1 EFUXDB                                   CDB$ROOT   <-- CDB Default Service 
         1 SYS$BACKGROUND                           CDB$ROOT   <-- CDB Default Service 
         1 SYS$USERS                                CDB$ROOT   <-- CDB Default Service 
         1 EFU.emilianofusaglia.net                 CDB$ROOT   <-- CDB Default Service 
         1 EFU_ADMIN.emilianofusaglia.net           CDB$ROOT   <-- CDB User-defined Service  
         3 mypdb.emilianofusaglia.net               MYPDB      <-- PDB Default Service 
         3 mypdb_app.emilianofusaglia.net           MYPDB      <-- PDB User-defined Service  

7 rows selected.

 

EZCONNECT to a PDB using the user-defined service:

sqlplus <username>/<password>@<host_name>:<local-listener-port>/<service-name>
sqlplus application/pwd@oel7c-scan:1522/mypdb_app.emilianofusaglia.net

 

 

Rule #4  –  Backup/Recovery strategy in Multitenant

As database administrator one of the first responsibility to fulfil is the “Backup/Recovery” strategy. The migration to multitenant database, due to the high level of consolidation density requires to review existing procedures. Few infrastructure operations, like creating a Data Guard or executing a backup, have been shifted from per-database to per-container consolidating the number of tasks.

RMAN in 12c covers all CDB, PDB backup/restore combinations, even though the best practice suggests to run the daily backup at CDB level, and in case of restore needed, the granularity goes down to the single block of one PDB.  Below are reported few basic backup/restore operations in Multitenant environment.

 

Backup a full CDB:

RMAN> connect target /;
RMAN> backup database plus archivelog;

 

Backup a list of PDBs:

RMAN> connect target /;
RMAN> backup pluggable database mypdb, hrpdb plus archivelog;

 

Backup one PDB directly connecting to it:

RMAN> connect target sys/manager@mypdb.emilianofusaglia.net;
RMAN> backup incremental level 0 database;

 

Backup a PDB tablespace:

RMAN> connect target /;
RMAN> backup tablespace mypdb:system;

 

Generate RMAN report:

RMAN> report need backup pluggable database mypdb;

 

Complete PDB Restore

RMAN> connect target /;
RMAN> alter pluggable database mypdb close;
RMAN> restore pluggable database mypdb;
RMAN> recover pluggable database mypdb;
RMAN> alter pluggable database mypdb open;

 

 

Rule #5  –  Before moving to Multitenant

Oracle Multitenant has introduced many architectural changes that force the DBA to evolve how databases are administered. My last golden rule suggests to thoroughly study the multitenant/singletenant architecture before starting any implementation.

During my experiences implementing multitenant/singletenant architectures, I found great dependencies with the following database areas:

  • Provisioning/Decommissioning
  • Patching and Upgrade
  • Backup/recovery
  • Capacity Planning and Management
  • Tuning
  • Separation of duties between CDB and PDB

 

 

Oracle Datapatch on Multitenant environment

The example below shows how to patch a Pluggable Database (PDB) migrated to a Container Database (CDB) whith a different patch level.

 

List the PDB violations

col message for a150
col action for a60
select * FROM pdb_plug_in_violations WHERE STATUS <>'RESOLVED';

TIME NAME CAUSE TYPE ERROR_NUMBER LINE
--------------------------------------------------------------------------- ------------------------------ ---------------------------------------------------------------- --------- ------------ ----------
MESSAGE STATUS ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------ --------- ------------------------------------------------------------
15-07-16 11:33:26.022539 CUSPPO SQL Patch ERROR 0 1
PSU bundle patch 160419 (Database Patch Set Update : 12.1.0.2.160419 (22291127)): Installed in the CDB but not in the PDB. ERROR Call datapatch to install in the PDB or the CDB


1 row selected.

 

Datapatch help

[oracle@zlo6ka1n1 OPatch]$ ./datapatch -h
SQL Patching tool version 12.1.0.2.0 on Wed Jun 15 10:53:36 2016
Copyright (c) 2015, Oracle. All rights reserved.

sqlpatch usage:
All arguments are optional, if there are no arguments sqlpatch
will automatically determine which SQL scripts need to be run in
order to complete the installation of any SQL patches.

Optional arguments:
-db <db name>
 Use the specified database rather than $ORACLE_SID
-bundle_series <bundle_series>
 Specify if the patch is a bundle patch
 Should also be accompanied by -force option
 if -bundle_series option is specified,only 1 patch will
 be considered by the -force command
-apply <patch1,patch2,...,patchn>
 Only consider the specified patch list for apply operations
-rollback <patch1,patch2,...,patchn>
 Only consider the specified patch list for rollback operations
-upgrade_mode_only
 Only consider patches that require upgrade mode
-force
 Run the apply and/or rollback scripts even if not necessary
 per the SQL registry
-pdbs <pdb1,pdb2,...,pdbn>
 Only consider the specified list of PDBs for patching. All
 other PDBs will not be patched
-prereq
 Run prerequisite checks only, do not actually run any scripts
-oh <oracle_home value>
 Use the specified directory to check for installed patches
-verbose
 Output additional information used for debugging
-help
 Output usage information and exit
-version
 Output build information and exit

SQL Patching tool complete on Wed Jul 15 10:53:36 2016

 

Apply the patch to the PDB

[oracle@zlo6ka1n0 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Wed Jul 15 11:36:19 2016
Copyright (c) 2015, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_59195_2016_07_15_11_36_19/sqlpatch_invocation.log

Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
 that are in an open state, no patches will be applied to closed PDBs.
 Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
 (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
 ID 160419 in the binary registry and ID 160419 in PDB CDB$ROOT, ID 160419 in PDB PDB$SEED

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED
 Nothing to roll back
 Nothing to apply
 For the following PDBs: CUSPPO
 Nothing to roll back
 The following patches will be applied:
 22291127 (Database Patch Set Update : 12.1.0.2.160419 (22291127))

Installing patches...
Patch installation complete. Total patches installed: 1

Validating logfiles...
Patch 22291127 apply (pdb CUSPPO): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_CLGBTE_CUSPPO_2016Jul15_11_37_25.log (no errors)
SQL Patching tool complete on Wed Jul 15 11:37:36 2016
[oracle@zlo6ka1n0 OPatch]$

 

 

Bug on Oracle 12c Multitenant & PDB Clone as Snapshot Copy

While automating the refresh of the test databases on Oracle 12c Multitenant environment with ACFS and PDB snapshot copy, I encountered the following BUG:

The column SNAPSHOT_PARENT_CON_ID of the view V$PDBS shows 0 (zero) in case of PDBs created as Snapshot Copy.

This bug prevents to identify the parent-child relationship between a PDB and its own Snapshots Copies.

The test case below explains the problem:

SQL> CREATE PLUGGABLE DATABASE LARTE3SEFU from LARTE3 SNAPSHOT COPY; 
 
 Pluggable database created. 
 
 SQL> select CON_ID, NAME, OPEN_MODE, SNAPSHOT_PARENT_CON_ID from v$pdbs where NAME in ('LARTE3SEFU','LARTE3'); 
 
 CON_ID      NAME          OPEN_MODE  SNAPSHOT_PARENT_CON_ID 
 ---------- -------------- ---------- ---------------------- 
 5          LARTE3         READ ONLY  0 
 16         LARTE3SEFU     MOUNTED    0  <-- This should be 5
 
 2 rows selected. 

A Service Request to Oracle has been opened, I’ll update this post once I have the official answer.

Update from the Service Request: BUG Fixed on version 12.2