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 implementation of a trigger to startup all PDBs in a CDB.

 

1. Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

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

[oracle@linux2 ~]$

2. 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 Oct 25 09:41:06 2016

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

 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>

 

3. 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.

 

4. Create a trigger which executes after the CDB starts with the following create trigger statement.

create trigger PDB_OPEN

after startup on database

begin

execute immediate ‘alter pluggable database all open’;

end;

/SQL>   2    3    4    5    6

 Trigger created.

 SQL>

5. Now shutdown the CDB with the command: shutdown immediate.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

6. Restart the CDB with command: startup open.

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>

7. Check the status of PDBs with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

 PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           READ WRITE

 SQL>

8. Note: All PDBs are now open after restart of CDB without the need to manually issue a command from SQL prompt.
9. This completes startup of all PDB within its CDB automatically.

Larry Catt

OCP