fbpx

How to Check PDB Status in Oracle

Sometimes a PDB is in a wrong status, like the MIGRATE status if you have just made an upgrade.

The best way to check the status is to execute this query from the CDB

set lines 200
column HOST_NAME format a50

select INST_ID, INSTANCE_NUMBER, INSTANCE_NAME, 
HOST_NAME, DATABASE_STATUS, con_id  
from gv$instance;

   INST_ID INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                          DATABASE_STATUS       CON_ID
---------- --------------- ---------------- -------------------------------------------------- ----------------- ----------
         1               1 YD001NFR1        con01db01.mydomain.com                             ACTIVE                     0
         2               2 YD001NFR2        con01db02.mydomain.com                             ACTIVE                     0

set lines 200
column name format a30

select INST_ID, CON_ID, NAME, OPEN_MODE, RESTRICTED 
from gv$pdbs;

   INST_ID     CON_ID NAME                           OPEN_MODE  RES
---------- ---------- ------------------------------ ---------- ---
         1          2 PDB$SEED                       READ ONLY  NO
         1          3 CLML01NFRPLUG                  READ WRITE NO
         2          2 PDB$SEED                       READ ONLY  NO
         2          3 CLML01NFRPLUG                  READ WRITE NO

alter pluggable database CLML01NFRPLUG close instances=('YD001NFR1');

select INST_ID, CON_ID, NAME, OPEN_MODE, RESTRICTED
from gv$pdbs;  

   INST_ID     CON_ID NAME                           OPEN_MODE  RES
---------- ---------- ------------------------------ ---------- ---
         1          2 PDB$SEED                       READ ONLY  NO
         1          3 CLML01NFRPLUG                  MOUNTED
         2          2 PDB$SEED                       READ ONLY  NO
         2          3 CLML01NFRPLUG                  READ WRITE NO

alter pluggable database CLML01NFRPLUG open instances=('YD001NFR1');

set lines 200
column name format a30

select INST_ID, CON_ID, NAME, OPEN_MODE, RESTRICTED 
from gv$pdbs;

   INST_ID     CON_ID NAME                           OPEN_MODE  RES
---------- ---------- ------------------------------ ---------- ---
         1          2 PDB$SEED                       READ ONLY  NO
         1          3 CLML01NFRPLUG                  READ WRITE NO
         2          2 PDB$SEED                       READ ONLY  NO
         2          3 CLML01NFRPLUG                  READ WRITE NO

Some more tests by closing one instance and then opening all instances again.

alter pluggable database CLML01NFRPLUG close instances=('YD001NFR1');

alter pluggable database CLML01NFRPLUG open instances=all;

set lines 200
column name format a30

select INST_ID, CON_ID, NAME, OPEN_MODE, RESTRICTED 
from gv$pdbs;

   INST_ID     CON_ID NAME                           OPEN_MODE  RES
---------- ---------- ------------------------------ ---------- ---
         1          2 PDB$SEED                       READ ONLY  NO
         1          3 CLML01NFRPLUG                  READ WRITE NO
         2          2 PDB$SEED                       READ ONLY  NO
         2          3 CLML01NFRPLUG                  READ WRITE NO

I hope this was helpful.

Leave a Reply

Your email address will not be published.