How to open pluggable database automatically

There are different startup phases of Oracle database. In multitenant architecture, startup and shutdown commands of container database are same as the regular database.

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]

SHUTDOWN [IMMEDIATE | ABORT]

After opening the container database, pluggable database has to be opened manually as shown below.

Last login: Fri Jan  1 14:06:57 2021 from 172.22.36.1
[root@TFOL01 ~]# su - oracle
Last login: Fri Jan  1 14:09:19 +03 2021 on pts/0
[oracle@TFOL01 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 2 16:08:09 2021
Version 19.3.0.0.0

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

Enter user-name: sys as sysdba
Enter password:

Ba?lant?:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
SQL>
SQL> shutdown immediate
Veritaban? kapat?ld?.
Veritaban? kullan?ma kapat?ld?.
ORACLE an? kapat?ld?.
SQL>
SQL> startup
ORACLE an? baslat?ld?.

Total System Global Area 3,5702E+10 bytes
Fixed Size                  9144112 bytes
Variable Size            4697620480 bytes
Database Buffers         3,0870E+10 bytes
Redo Buffers              125071360 bytes
Veritaban? kullan?ma ac?ld?.
Veritaban? ac?ld?.
SQL>
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TAMPDB                         MOUNTED
         6 TAMPDB2                        MOUNTED
SQL>
SQL>

So, as it can be seen both PDBs are in mounted mode. They have to be opened manually.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Eklenebilir veritaban? de?istirildi.

SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TAMPDB                         READ WRITE NO
         6 TAMPDB2                        READ WRITE NO
SQL>
SQL>

Once you open the CDB you can save the state of a PDB. Also, if you have several PDBs and you want them all to open on restart, you can set them with the following command.

--Save the state of a spesific PDB 
ALTER PLUGGABLE DATABASE PDB_NAME SAVE STATE;

--Save all PDBs state
ALTER PLUGGABLE DATABASE ALL SAVE STATE;

--Furthermore, If you have multiple PDBs, you can exclude some of them from this.
ALTER PLUGGABLE DATABASE ALL EXCEPT PDB_NAME1, PDB_NAME2 SAVE STATE;

In our example, let’s save only the state of TAMPDB and restart the CDB again.

SQL> ALTER PLUGGABLE DATABASE TAMPDB SAVE STATE;

Eklenebilir veritaban? de?istirildi.

SQL>
SQL> shutdown immediate
Veritaban? kapat?ld?.
Veritaban? kullan?ma kapat?ld?.
ORACLE an? kapat?ld?.
SQL>
SQL> startup
ORACLE an? baslat?ld?.

Total System Global Area 3,5702E+10 bytes
Fixed Size                  9144112 bytes
Variable Size            4697620480 bytes
Database Buffers         3,0870E+10 bytes
Redo Buffers              125071360 bytes
Veritaban? kullan?ma ac?ld?.
Veritaban? ac?ld?.
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TAMPDB                         READ WRITE NO
         6 TAMPDB2                        MOUNTED
SQL>

So, as you can see from the above result. Only TAMPDB has been opened automatically after the restart. You can check the DBA_PDB_SAVED_STATES view for information about the saved state of containers.

SQL> COLUMN CON_NAME FORMAT A20
SQL> COLUMN INSTANCE_NAME FORMAT A20
SQL> COLUMN STATE FORMAT A10
SQL>
SQL> SELECT CON_NAME, INSTANCE_NAME, STATE FROM DBA_PDB_SAVED_STATES;

CON_NAME             INSTANCE_NAME        STATE
-------------------- -------------------- ----------
TAMPDB               tamtestc             OPEN

SQL>

Note that saving state is only available from 12.1.0.2. If you use 12.1.0.1 then you can use the after startup trigger like below.

CREATE OR REPLACE TRIGGER TRI_OPEN_PDBS 
  AFTER STARTUP ON DATABASE 
BEGIN 
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN'; 
END TRI_OPEN_PDBS;
/

Leave a Reply

Your email address will not be published. Required fields are marked *