Auto-Starting your pluggables in 12c

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.