Oracle 23ai: Hybrid Read-Only Mode for Pluggable Databases

 

- Overview:

• Oracle 23ai database introduces a new feature to open Pluggable database in a new mode called hybrid read-only.
• Hybrid read-only mode enables a PDB to operate as either read-write or read-only, depending on the user who is connected to the PDB.
• For Oracle Multitenant database common users (like SYSTEM, or C##<USER>), the PDB will be in read-write mode.
• For Pluggable database local users (like HR, or SCOTT), the PDB will be restricted to read-only mode.
• Hybrid read-only mode enables you to patch and maintain an application in a safe mode for open PDBs without the risk of local users, including higher privileged common users, interfering with the ongoing maintenance operation of the PDB.

In this blog, I'll demonstrate opening a PDB in a hybrid read-only mode, then test a DML command from  common and local users.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• A pluggable database with HR database schema.

Step #1: Open a PDB in Hybrid Read-Only Mode

- Connect to the root container as SYS, close the PDB, then open the PDB in hybrid read-only mode.

SQL> alter pluggable database &PDB_NAME close immediate instances=all;

SQL> alter pluggable database &PDB_NAME OPEN HYBRID READ ONLY instances=all;

Note: The pluggable database open mode will be READ WRITE even after opening in hybrid read-only mode.

Step #2: Testing a DML from a PDB Database Local User

- Connect to the PDB as HR and run a DML statement.

- You will get below error message

SQL Error: ORA-16000: Attempting to modify database or pluggable database that is open for read-only access.

Step #3: Testing a DML from a Common Database User

- Connect to the PDB as system HR and run a DML statement.

- A DML statement will be executed without error.