Oracle 12c New Features

Online Oracle Training

LESSON 13

How to create AFTER STARTUP trigger to open all PDBs except the mypdb2_clone pluggable database

Login to your mycdb CDB and create AFTER STARTUP trigger.

# cd ~

# . ./.profile

# sqlplus /nolog

SQL> connect / as sysdba

--Just to make sure where you are

Get the container name

SQL> show con_name

List or query the V$DATABASE view.

SQL> SELECT name, cdb, con_id FROM v$database;

SQL> CREATE TRIGGER open_my_pdbs

   2   AFTER STARTUP ON DATABASE

   3   BEGIN

   4      EXECUTE IMMEDIATE "ALTER PLUGGABLE DATABASE all EXCEPT mypdb2 OPEN;

   5   END open_my_pdbs;

   6   /

Trigger created.

Shutdown the CDB database

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

Start up again.

SQL> STARTUP;

Get the list of all PDBs open mode.

SQL> SELECT con_id, name, open_mode, dbid FROM v$pdbs;

Notice that the mypdb2 open mode is "MOUNTED."

To open it

SQL> ALTER PLUGGABLE DATABAE mypdb2 OPEN;

Get the list of all PDBs open mode.

SQL> SELECT con_id, name, open_mode, dbid FROM v$pdbs;

Notice that all PDBs open mode are "READ WRITE."

You can also change a pluggable database to a restricted mode.

SQL> ALTER PLUGGABLE DATABAE mypdb2 CLOSE IMMEDIATE;

SQL> ALTER PLUGGABLE DATABAE mypdb2 OPEN RESTRICTED;

Get the list of all PDBs open mode.

SQL> SELECT con_id, name, open_mode, restricted FROM v$pdbs WHERE name = "MYPDB2";

Notice that the restricted column value is "YES."

Good Luck!