Pluggable database: limitations of the open mode of the CDB imposed on the open mode of PDBs

Tweet

The open mode of the container database imposes limitations on the open mode of PDBs.
For example, the root must be open before any PDBs can be open. Therefore, you might need to change the open mode of the root before changing the open mode of a PDB. Let's start.

Verify you are connected to the root container.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

Verify the open mode from the V$DATABASE view.

SQL> select name, con_id, open_mode from v$database;

NAME       CON_ID OPEN_MODE
--------- ---------- --------------------
CDB001     0 READ WRITE

I've two pluggable databases in the current container: I want to close the pluggable database called PDB001.

SQL> alter pluggable database PDB001 close immediate;

Pluggable database altered.

Verify the open mode from the V$PDBS view.
One of my pluggable databases (PDB002) is available in READ WRITE mode, the second (PDB001) is in MOUNTED mode. The seed (PDB$SEED) is a template that you can use to create new PDBs and is always in READ ONLY mode.

SQL> select name, con_id, open_mode from v$pdbs;

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

What does it happen to pluggable databases when you shutdown the root container and open it again in MOUNT mode ?

SQL> shutdown immediate;    
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size      2291472 bytes
Variable Size    473958640 bytes
Database Buffers   146800640 bytes
Redo Buffers      3276800 bytes
Database mounted.

The root container is in MOUNTED mode as expected.

SQL> select name, con_id, open_mode from v$database;

NAME       CON_ID OPEN_MODE
--------- ---------- --------------------
CDB001     0 MOUNTED

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

The three pluggable databases are all in MOUNTED mode too.

SQL> select name, con_id, open_mode from v$pdbs;

NAME       CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED    2 MOUNTED
PDB001     3 MOUNTED
PDB002     4 MOUNTED

You cannot open a pluggable database if the container is not open.

SQL> alter pluggable database PDB001 open read write;
alter pluggable database PDB001 open read write
*
ERROR at line 1:
ORA-01109: database not open

Open first the CDB instance containing the root, the seed and its pluggable databases.

SQL> alter database open;

Database altered.

SQL> select name, con_id, open_mode from v$database;

NAME       CON_ID OPEN_MODE
--------- ---------- --------------------
CDB001     0 READ WRITE

The seed database is now put in READ ONLY mode; the other two pluggable databases are still in MOUNTED mode. So they don't start automatically and you have to take care of this.

SQL> select name, con_id, open_mode from v$pdbs;    

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

You can now open pluggable databases as you want. I'm going to open a pluggable database, PDB001, in READ ONLY mode and then PDB002 in READ WRITE mode.

SQL> alter pluggable database pdb001 open read only;

Pluggable database altered.

SQL> alter pluggable database pdb002 open;          

Pluggable database altered.

SQL> select name, con_id, open_mode from v$pdbs;

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

As you can see I didn't specify to open the pluggable database PDB002 in READ WRITE mode: as usual when you don't specify a valid option the READ WRITE is the default mode.

To open also the PDB001 pluggable database in READ WRITE mode you can use the following command and the FORCE option.

SQL> alter pluggable database PDB001 open read write force;

Pluggable database altered.

SQL> select name, con_id, open_mode from v$pdbs;

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

That's all.