Monthly Archives: July 2021

How to change PDB open mode without disconnecting existing sessions: the ambiguous FORCE

This entry was posted in Multitenant Oracle Oracle 19c on by

When the database is in READ ONLY mode, the way to change it to READ WRITE is by issuing

SQL> ALTER DATABASE OPEN READ WRITE;
Database altered.

However, when working on a PDB, the similar syntax does not work:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database pdb1 already open

This is strange, and to my surprise, the correct syntax to change a PDB from READ ONLY to READ WRITE is:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE FORCE;
Pluggable database altered.

I would expect that the “FORCE” would do the same as on the STARTUP command here:

SQL> STARTUP FORCE OPEN READ WRITE;
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size             872415232 bytes
Database Buffers         1258291200 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

On the normal STARTUP command, the FORCE keyword performs a shutdown abort, before starting back the database. It is correctly described in the documentation:

If the database is open, then FORCE shuts down the database with a SHUTDOWN ABORT statement before re-opening it.

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/starting-up-and-shutting-down.html#GUID-CCA52747-05CA-4ED3-BE6D-E2E684C4D87D

The ALTER PLUGGABLE DATABASE explains the different behaviour of the FORCE keyword in this case:

Specify this keyword [FORCE] to change the open mode of a PDB from READ WRITE to READ ONLY, or from READ ONLY to READ WRITE. The FORCE keyword allows users to remain connected to the PDB while the open mode is changed.

When you specify FORCE to change the open mode of a PDB from READ WRITE to READ ONLY, any READ WRITE transaction that is open when you change the open mode will not be allowed to perform any more DML operations or to COMMIT.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-PLUGGABLE-DATABASE.html#GUID-A29491AD-8F0F-4E52-9D94-57FC3FF8FBC7

So, at the end it is good news. It is possible to change the PDB open mode without killing the users’ sessions. Just that “FORCE” keyword seems a bit too strong for my taste.