Oracle 12c: Managing PDBs using Triggers (12.1.0.1)

July 11th, 2013 | Posted in 12c, Blog | 2 Comments

Introduction:

UPDATE: 01 August 2014: With the new Oracle Database 12c Release 12.1.0.2 ; We don’t need start up triggers anymore because this version comes with the “SAVE STATE” option for pluggable databases;
See: 12.1.0.2

The following sections apply to Oracle Database 12c (12.1.0.1)
Triggers are procedures written in PL/SQL, Java, or C that run (fire) implicitly whenever user actions, operations on database objects or database system actions occur. Oracle database 12c is not an exception. With the new Oracle 12c, it is possible to manage Pluggable Databases (PDBs) using triggers. The new version of the database comes also with new trigger events to manage the PDBs.
In this post, we are going to show some examples using triggers to manage PDBs.

1- AFTER STARTUP

AFTER STARTUP trigger can be created ON DATABASE to change PDBs opening mode.
In the following example, we create a trigger called “TRG_OPEN_ALL_PDBS” to open automatically all the PDBs after Container Database (CDB) startup. By default, after opeing CDB all PDBs will be in mount state.

Note from the output below, “ORAWISS12C” PDB is in Open write mode.

sandbox1(orawiss):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 04:46:17 2013

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


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

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061740350 PDB$SEED                       READ ONLY
         3 1574282659 ORAWISS12C                     READ WRITE

SQL>

Let’s restart the CDB, note from the output below, ORAWISS12C is in mount state:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          339738624 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.
SQL>  select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061740350 PDB$SEED                       READ ONLY
         3 1574282659 ORAWISS12C                     MOUNTED

We create the trigger;

SQL> CREATE OR REPLACE TRIGGER TRG_OPEN_ALL_PDBS AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END;  
/

Trigger created.

Restart the CDB and check the PDB status:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          339738624 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061740350 PDB$SEED                       READ ONLY
         3 1574282659 ORAWISS12C                     READ WRITE

SQL>

Drop the trigger if you want:

SQL> DROP TRIGGER TRG_OPEN_ALL_PDBS;

Trigger dropped.

SQL>

2- AFTER CLONE:

AFTER CLONE trigger fires after clone of a PDB. You must specify the ON PLUGGABLE DATABASE clause during AFTER CLONE trigger creation. Note that if the AFTER CLONE trigger fails for any reason ALL the clone operation will fail. The AFTER CLONE trigger must be created within a PDB, if you try to create it within a CDB you get the following error;

sandbox1(orawiss):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 04:46:17 2013

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


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

SQL> CREATE TRIGGER TRG_TEST_PDB AFTER CLONE ON PLUGGABLE DATABASE
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO  WISSEM.TEST_REC VALUES (1)';
END;  2    3    4
  5  /
CREATE TRIGGER TRG_TEST_PDB AFTER CLONE ON PLUGGABLE DATABASE
                                        *
ERROR at line 1:
ORA-65072: user must be connected to a pluggable database on which a trigger is
being created

Let’s create now a AFTER CLONE trigger, we want the new cloned PDB to have one more row in WISSEM.TEST_REC table.


SQL> alter session set container=ORAWISS12C;

Session altered.

SQL>  select * from wissem.test_rec;

no rows selected

SQL>

SQL> CREATE OR REPLACE TRIGGER TRG_TEST_PDB AFTER CLONE ON PLUGGABLE DATABASE
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO  WISSEM.TEST_REC VALUES (1)';
END; 
 /

Trigger created.


SQL>

Note WISSEM.TEST_REC table has no data in “ORAWISS12C” (PDB source).

Let’s bring the source PDB called “ORAWISS12C” to open read only mode prior to clone operation

SQL>  select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         3 1574282659 ORAWISS12C                     READ WRITE

SQL>
SQL> ALTER PLUGGABLE DATABASE ORAWISS12C CLOSE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE ORAWISS12C OPEN READ ONLY;

Pluggable database altered.

SQL>  select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         3 1574282659 ORAWISS12C                     READ ONLY

SQL> 

Let’s use SQL Developer to clone the PDB, alternatively, you can also use SQL*Plus by running the simple following command:

CREATE PLUGGABLE DATABASE PDB2 FROM ORAWISS12C;

Click on Clone Pluggable Database :

Specify the name of the new cloned PDB: (PDB2 in our example) and leave all to default:

NOTE: More details about Managing PDBs using Sql Developer can be found here: Manage PDBs using Sql Developer

The new PDB called “PDB2″ is in mount state:

sandbox1(orawiss):/home/oracle/PDB2/DATAFILE>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 05:15:54 2013

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


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

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061740350 PDB$SEED                       READ ONLY
         3 1574282659 ORAWISS12C                     READ ONLY
         4 3886702940 PDB2                           MOUNTED

Let’s open the new PDB:

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061740350 PDB$SEED                       READ ONLY
         3 1574282659 ORAWISS12C                     READ ONLY
         4 3886702940 PDB2                           READ WRITE

Switch the session to PDB2 and check the table:

SQL> alter session set container=PDB2;

Session altered.

SQL>  select * from wissem.test_rec;

        ID
----------
         1

I have noticed the AFTER CLONE trigger is deleted after the clone operation.

sandbox1(orawiss):/home/oracle/PDB2/DATAFILE>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 05:29:39 2013

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


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

SQL>  alter session set container=ORAWISS12C;

Session altered.

SQL> SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIGGER_NAME='TRG_TEST_PDB';

no rows selected

SQL>
SQL> alter session set container=PDB2;

Session altered.

SQL> SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIGGER_NAME='TRG_TEST_PDB';

no rows selected

SQL>

3- BEFORE UNPLUG:

BEFORE UNPLUG trigger fires before unplug of a PDB. You must specify the ON PLUGGABLE DATABASE clause during BEFORE UNPLUG trigger creation. Note that if the BEFORE UNPLUG trigger fails for any reason ALL the unplug operation will fail. The BEFORE UNPLUG trigger must be created within a PDB.

Let’s create a BEFORE UNPLUG Trigger and cause the unplug operation to fail: we know that “ALTER PLUGGABLE DATABASE ALL OPEN” clause is not allowed within PDB.

SQL> alter session set container=PDB2;

Session altered.

SQL> CREATE OR REPLACE TRIGGER TRG_PDB_2 BEFORE UNPLUG ON PLUGGABLE DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END;  
/

Trigger created.

SQL>

SQL>  alter pluggable database PDB2 close immediate;

Pluggable database altered.

SQL>  alter pluggable database PDB2 unplug into '/tmp/pdb2.xml';
 alter pluggable database PDB2 unplug into '/tmp/pdb2.xml'
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


Note the “ORA-65040: operation not allowed from within a pluggable database” error duing the UNPLUG operation.
I have noticed the BEFORE UNPLUG trigger is deleted after the unplug operation:

Drop the new cloned PDB2.

sandbox1(orawiss):/home/oracle/PDB2/DATAFILE>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 06:10:13 2013

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


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

SQL>  drop pluggable database PDB2 including datafiles;

Pluggable database dropped.

SQL>

Cheers,
Wissem

References ; Doc 12cR1



2 Comments to “Oracle 12c: Managing PDBs using Triggers (12.1.0.1)”

  1. Hans Forbrich says:

    Very nice.


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs
  • ron: unable to connect to Cluster Synchronization Service
  • KevinB: Please post or attach the response file
  • nnarimanov: I’d like to clarify the reason why rman restores all datafiles into +DATA. It’s because of...
  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!