Oracle: Save PDB state for all instances in RAC environment

oracleoracle-12cracsqlplus

I found out the following two contradicting statements in the Oracle DBA guide.

For an Oracle RAC CDB, you can use the instances clause in the pdb_save_or_discard_state clause to specify the instances on which a PDB's open mode is preserved in the following ways: …

Then it goes on to say this :

For a PDB in an Oracle RAC CDB, SAVE STATE and DISCARD STATE only affect the mode of the current instance. They do not affect the mode of other instances, even if more than one instance is specified in the instances clause.

How do I save the PDB state in all instances in a RAC environment without connecting to all of them seperately?

Does the srvctl utlity does this automatically when I am using it to bounce the CDB as below:

srvctl stop db -d cdb_name
srvctl start db -d cdb_name

Best Answer

Depends on where you use the command.

It works on all instances from the root container:

srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         2          3 PDB1       ENABLED  MOUNTED
         1          3 PDB1       ENABLED  MOUNTED

SQL> alter pluggable database pdb1 open instances=all;

Pluggable database altered.

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         1          3 PDB1       ENABLED  READ WRITE
         2          3 PDB1       ENABLED  READ WRITE

SQL> alter pluggable database pdb1 save state instances=all;

Pluggable database altered.

srvctl stop database -db wagon; srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         1          3 PDB1       ENABLED  READ WRITE
         2          3 PDB1       ENABLED  READ WRITE

But it does not work on all instances when issued from the PDB itself:

SQL> alter session set container=pdb1;

Session altered.

SQL> alter pluggable database pdb1 close instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb1 save state instances=all;

Pluggable database altered.

srvctl stop database -db wagon; srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         2          3 PDB1       ENABLED  READ WRITE
         1          3 PDB1       ENABLED  MOUNTED
Related Question