Sunday, July 28, 2013

How to change the open mode of listed PDBs

The clauses of the ALTER PLUGGABLE DATABASE statement that modify the mode of a PDB are:
- OPEN READ WRITE [RESTRICTED] [FORCE]: it opens the PDB in read/write mode;
- OPEN READ ONLY [RESTRICTED] [FORCE]: it opens the PDB in read-only mode;
- OPEN UPGRADE [RESTRICTED]: it opens the PDB in migrate mode;
- CLOSE [IMMEDIATE]: it places the PDB in mounted mode and it is the PDB equivalent of the SQL*Plus SHUTDOWN command;

When using the RESTRICTED option, only users with RESTRICTED SESSION privilege can log into the PDB: all other sessions connected to the PDB without RESTRICTED SESSION privilege are terminated and their transactions rolled back.
When FORCE is specified, the statement opens a PDB that is currently closed and changes the open mode as specified.

To modify the open mode of one or more PDBs you have to ensure that the current container is the root:
SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
Let's get information about current pluggable databases querying the V$PDBS view:
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4
I want to "shutdown" the pluggable database identified as PDB001: all current connected sessions will be terminated and their transaction rolled back.
You need to use the alter pluggable database command:
SQL> alter pluggable database pdb001 close immediate;

Pluggable database altered.
The specified pluggable database is put in MOUNTED mode:
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          READ WRITE    4
If you want to open the pluggable database PDB001 in READ ONLY mode you can use again the alter pluggable database command:
SQL> alter pluggable database pdb001 open read only;

Pluggable database altered.
It's now open in READ ONLY mode.
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          READ WRITE    4
What does it happen when you try to open it in READ WRITE mode ?
SQL> alter pluggable database pdb001 open read write;
alter pluggable database pdb001 open read write
*
ERROR at line 1:
ORA-65019: pluggable database PDB001 already open
Oracle throws the following exception:
[oracle@localhost trace]$ oerr ora 65019
65019, 00000, "pluggable database %s already open"
// *Cause:  An attempt was made to open a pluggable database that was already
//          opened.
// *Action: Check V$PLUGGABLE_DATABASE.STATE.
If you want to open in READ WRITE mode your pluggable database, currently in READ ONLY mode, you have to add the FORCE option to the previous command:
SQL> alter pluggable database pdb001 open read write force;

Pluggable database altered.
The pluggable database is now open in READ WRITE mode again:
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

In the next post I will decribe the limitations of the open mode of the root imposed on the open mode of PDBs.

That's all.

No comments: