How to set Oracle Pluggable Database Automatic startup

As you know the pluggable database concept introduce in oracle 12c and when we startup the CDB database the PDBs (Pluggable Database) will be mount stage. So that time we need to open PDBs manually using alter command. Here we are going to learn how to Oracle Pluggable Database Automatic startup step by step.

To make the PDBs open automatically we need to use the saved state option and that option (feature) is available from 12.1.0.2 onwards.

Make Pluggable Database Automatic Startup

Using below steps you can make your all pluggable database automatic startup.

Step 1. Start the Container database.

$sqlplus / as sysdba
SQL>startup

SQL>show con_name
CON_NAME
------------
CDB$ROOT

Step 2. Check the status of PDBS

SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

The above command will show you the current status of all pdbs and you can see all pdbs are in mounted mode.

Step 3. Open the PDBS using below command.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

Check again the stage of pdbs.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

Step 4. Make the pdbs startup automatically using save the pdbs current state. the save state command will help us to save the states, if you take cdb restart the pdbs will be open in read write mode automatically.

SQL> alter pluggable database pdb1 save state;

Pluggable database altered.

SQL> alter pluggable database pdb2 save state;

Pluggable database altered.

Check the pdbs saved state using dba_pdb_saved_states views.

SQL> select con_name,state from dba_pdb_saved_states

CON_NAME             STATE
-------------------- --------------
PDB1                 OPEN
PDB2                 OPEN

Step 5. Bounce the cdb and after restart check the pdbs state.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.
Total System Global Area 1493172224 bytes
Fixed Size                  2924592 bytes
Variable Size             486543312 bytes
Database Buffers          989855744 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

So, these are the complete steps to make the pdbs startup automatically. Now if you want to discard the above changes, if cdb restart next time the pdbs will be mount state only.

Discard the pdbs saved state

Step 1. How to discard the pdbs saved state. Here we only discard the state of pdb1.

SQL> alter pluggable database pdb1 discard state;

Pluggable database altered.


SQL> select con_name,state from dba_pdb_saved_states;

CON_NAME             STATE
-------------------- --------------
PDB2                 OPEN

Step 2. Now bounce the cdb to check the pdbs state discarded or not.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1493172224 bytes
Fixed Size                  2924592 bytes
Variable Size             486543312 bytes
Database Buffers          989855744 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           READ WRITE NO

Now you can see only pdb1 started in mounted stage, because we only discard the state only for pdb1.

How to Restore RMAN Backup with a Different Database Name