Automate Pluggable Database opening during Container instance startup
1) List the PDBs and respective Modes
sqlplus / as sysdba
SQL> show pdbs
2) Check the Saved State for all PDB’s from DBA view
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;
3) Check the Saved State for all PDB’s from CDB view
SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;
4) Change Save State of PDB by usig below statement
alter pluggable database PDB1 save state;
5) To discard any saved state of a pluggable database using below statement
alter pluggable database PDB1 discard state;
logs:
=====
[root@oracleprod ~]# su - oracle
Last login: Wed Jun 21 01:20:29 +08 2023
[oracle@oracleprod ~]$
[oracle@oracleprod ~]$ ps -ef|grep smon
oracle 17433 16853 0 01:45 pts/0 00:00:00 grep --color=auto smon
oracle 29594 1 0 Jun19 ? 00:00:02 ora_smon_PRODCDB
[oracle@oracleprod ~]$ . oraenv
ORACLE_SID = [PRODCDB] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracleprod ~]$
[oracle@oracleprod ~]$ env |grep ORA
ORACLE_SID=PRODCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oracleprod ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:47:00 2023
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
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
6 ORAODB1 READ WRITE NO
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@oracleprod ~]$
[oracle@oracleprod ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:47:57 2023
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@oracleprod ~]$ ps -ef|grep smon
oracle 18270 16853 0 01:48 pts/0 00:00:00 grep --color=auto smon
[oracle@oracleprod ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:48:53 2023
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 3707763824 bytes
Fixed Size 9170032 bytes
Variable Size 1912602624 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7606272 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 MOUNTED
6 ORAODB1 MOUNTED
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
6 ORAODB1 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
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
6 ORAODB1 READ WRITE NO
SQL> alter pluggable database ORAODB1 close;
Pluggable database altered.
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
6 ORAODB1 MOUNTED
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;
no rows selected
SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;
no rows selected
SQL> alter pluggable database PDB1 save state;
Pluggable database altered.
SQL> col CON_NAME for a20;
SQL> col INSTANCE_NAME for a20;
SQL> col state for a20;
SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------------
PDB1 PRODCDB OPEN
SQL> col name for a20;
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;
NAME STATE
-------------------- --------------------
PDB1 OPEN
SQL> alter pluggable database PDB2 save state;
Pluggable database altered.
SQL> col name for a20;
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;
NAME STATE
-------------------- --------------------
PDB1 OPEN
PDB2 OPEN
SQL> alter pluggable database ORAODB1 save state;
Pluggable database altered.
SQL> col name for a20;
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;
NAME STATE
-------------------- --------------------
PDB1 OPEN
PDB2 OPEN
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 3707763824 bytes
Fixed Size 9170032 bytes
Variable Size 1912602624 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7606272 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
6 ORAODB1 MOUNTED
SQL> col name for a20;
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;
NAME STATE
-------------------- --------------------
PDB1 OPEN
PDB2 OPEN
SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------------
PDB1 PRODCDB OPEN
PDB2 PRODCDB OPEN
SQL> alter pluggable database PDB1 discard state;
Pluggable database altered.
SQL> col CON_NAME for a20;
SQL> col INSTANCE_NAME for a20;
SQL> col state for a20;
SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------------
PDB2 PRODCDB OPEN
SQL> alter pluggable database PDB2 discard state;
Pluggable database altered.
SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;
no rows selected
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@oracleprod ~]$
Regards,
Mallikarjun Ramadurg
WhatsApp: +91 9880616848
gmail: mallikarjun.ramadurg@gmail.com
