Oracle DBA Tips and Techniques

By Arjun Raja

ON STARTUP TRIGGER TO OPEN ALL PLUGGABLE DATABASES

Posted by Arjun Raja on June 11, 2014

ON STARTUP TRIGGER TO OPEN ALL PLUGGABLE DATABASES

Create trigger to open all PDB’s on restart of CDB.

Container database called CDB1D1

Pluggable database called PDB1D1

Shutdown CDB

cdbd1:/u01/oracle> sqlas

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 13 13:39:17 2014

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, OLAP, Advanced Analytics and Real Application Testing options

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

Try connecting to PDB

cdbd1:/u01/oracle> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 13 13:40:07 2014

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

Now startup CDB

cdbd1:/u01/oracle> sqlas

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 13 13:40:31 2014

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2360696 bytes
Variable Size 956304008 bytes
Database Buffers 637534208 bytes
Redo Buffers 7213056 bytes
Database mounted.
Database opened.
SQL>

Check status of PDB.

cdbd1:/u01/oracle> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 13 13:41:20 2014

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, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE
—————————— ———-
PDB1D1 MOUNTED

SQL>

SQL> select file_name from dba_data_files;
select file_name from dba_data_files
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

SQL>

Therefore if PDB is shutdown when a CDB is shutdown, on restart of CDB, the PDB has to be opened manually.

cdbd1:/u01/oracle> sqlas

SQL> alter pluggable database pdb1d1 open read write;

Pluggable database altered.

SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE
—————————— ———-
PDB1D1 READ WRITE

To avoid this – a trigger can be enabled which starts all PDB’s once a CDB is restarted.

CREATE TRIGGER open_all_PDBs
AFTER STARTUP ON DATABASE
begin
execute immediate ‘alter pluggable database all open’;
end open_all_PDBs;
/

Trigger created.

SQL>

Now shutdown and restart CDB – the PDB should also be opened by the trigger.

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2360696 bytes
Variable Size 956304008 bytes
Database Buffers 637534208 bytes
Redo Buffers 7213056 bytes
Database mounted.
Database opened.
SQL>

Check if PDB is opened.

cdbd1:/u01/oracle> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE
—————————— ———-
PDB1D1 READ WRITE

SQL>

OPENED !

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: