After installing 12c you get your container database and a pluggable. But the problem that most people have is that when they restart their server or in my case my VMs the container database gets started but the pluggable database does not automatically start. This means that you have to manually go in an start it. But this is a pain. Surely there is an easy way to get your pluggable databases to start. You would have though that Oracle would have some easy way of doing this. If there is, I haven’t found it yet.
But I have come across how to automatically start your 12c pluggable databases, using a trigger.
CREATE or REPLACE trigger OPEN_ALL_PLUGGABLES
after startup
on database
BEGIN
execute immediate 'alter pluggable database all open';
END open_all_pdbs;
Let us test this out. I’ve started my VirtualBox VM that has 12c installed on Windows 7. Here is the code that I ran to verify that the container has been started and the pluggable is in MOUNTED mode.
C:\Users\oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 17 15:27:35 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, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> select name,DB_UNIQUE_NAME from v$database;
NAME DB_UNIQUE_NAME
--------- ------------------------------
ORCL orcl
SQL> SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
2 FROM v$pdbs v, dba_pdbs d
3 WHERE v.guid = d.guid
4 ORDER BY v.create_scn;
NAME OPEN_MODE RES STATUS
------------------------------ ---------- --- -------------
PDB$SEED READ ONLY NO NORMAL
PDB12C MOUNTED n/a NORMAL
SQL>
Next we will create the procedure (given above).
To test the automatic starting of the pluggables, we need to shut down the container database, by issuing the shutdown command.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> select name,DB_UNIQUE_NAME from v$database;
select name,DB_UNIQUE_NAME from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
This shows us that the container database is shutdown.
Now we can start the container and test to see if the pluggable database is started automatically by the trigger.
SQL> startup
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2408408 bytes
Variable Size 562036776 bytes
Database Buffers 285212672 bytes
Redo Buffers 6324224 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,DB_UNIQUE_NAME from v$database;
NAME DB_UNIQUE_NAME
--------- ------------------------------
ORCL orcl
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
2 FROM v$pdbs v, dba_pdbs d
3 WHERE v.guid = d.guid
4 ORDER BY v.create_scn;
NAME OPEN_MODE RES STATUS
------------------------------ ---------- --- -------------
PDB$SEED READ ONLY NO NORMAL
PDB12C READ WRITE NO NORMAL
SQL>
We can see that the pluggable was started.
