PDB - Hybrid read only mode

 Hybrid Read-Only Mode, introduced in Oracle Database 23ai, solves the classic "Catch-22" where you need to perform maintenance (requiring Read-Write access) but want to prevent application users from changing data (requiring Read-Only access).

For years, DBAs have faced a frustrating trade-off during maintenance windows. If you open a Pluggable Database (PDB) in READ WRITE mode, your application users (and their potentially messy DML) can interfere with your patching or schema upgrades. If you open it in READ ONLY mode, you can’t perform the very maintenance you planned.

What is Hybrid Read-Only Mode?

In this mode, the PDB's accessibility is determined by the user type:

• Common Users (CDB Level): Experience the PDB in READ WRITE mode. They can perform DDL, DML, and maintenance tasks.

• Local Users (PDB Level): Experience the PDB in READ ONLY mode. Even if they have the DBA role, they are restricted from making any changes.

Implementing and Testing Hybrid Read-Only Mode in Oracle 26ai

First, confirm that FREEPDB1 is in its default READ WRITE state. This ensures the PDB is accessible for standard operations.

By default the IS_HYBRID_READ_ONLY column shows NO for all container databases. The column name is truncated to IS_ in the SELECT output.

To manage a multi-tenant environment effectively, you'll need a Common User. These users are defined in the CDB Root and are recognized across all PDBs within that container. To create one, ensure you are connected to the CDB$ROOT and use the CONTAINER=ALL clause:

Attempting to create a common user while connected directly to a PDB will trigger ORA-65050: common user creation not allowed in a pluggable database. Remember: common users are global entities and must be defined at the CDB Root level.

Create a PDB Local User

To use the PDB called FREEPDB1 run the ALTER SESSION command to set the container to FREEPDB1.

Create a PDB local user called nani_local. The CONTAINER clause is set to CURRENT to indicate that the PDB user is a local user for the current container database.

Connect as CDB Common User to the PDB that is in READ WRITE Mode

The default open mode being READ WRITE,  a CDB common user is able to perform DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), and DCL (Data Control Language)  operations

Next, connect to the PDB as a CDB common user. Specify password when prompted.

Connect as PDB Local User to the PDB that is in READ WRITE Mode

The default open mode being READ WRITE,  local user nani_local is able to perform DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), and DCL (Data Control Language)  operations

Change PDB - FREEPDB1 Open Mode to READ ONLY

Next, we shall change the PDB open mode to READ ONLY and demonstrate that it affects both a CDB common user and a PDB local user.  Connected with SYS AS SYSDBA, the following ALTER PLUGGABLE database command sets the open mode of the FREEPDB1 to mounted. 

In the READ ONLY open mode neither of CDB common users or PDB local users are able to perform any write operations, which includes DDL and DML, but not DQL.

To demonstrate, connect as the CDB common user C##NANI and create a table, or rather try to create a new table.

The CDB common user is able to perform a read with a SELECT statement.

Next, connect to the PDB that is in READ ONLY mode as a PDB local user and try to perform DDL and DML operations.

Try to create a new table.

An error message gets generated indicating that the CREATE TABLE statement cannot be run because the database is open in read-only  mode

In the READ ONLY open mode, the IS_HYBRID_READ_ONLY column in the dynamic view V$CONTAINER_TOPOLOGY still shows NO for the FREEPDB1.

The open_mode in the v$pdbs dynamic view gets listed as READ ONLY.

Configure PDB Open Mode to HYBRID READ ONLY

As before, connect as a user with the requisite privileges, such as SYS AS SYSDBA, and close the pluggable database with ALTER PLUGGABLE DATABASE statement and open with HYBRID READ ONLY option.

The open_mode column in the dynamic view v$pdbs shows READ WRITE in the root CDB.

Next, we shall demonstrate that a CDB common user is still able to perform DDL, and DML operations in the PDB that is opened in HYBRID READ ONLY mode, but a PDB local user is not.

Connect as the PDB local user nani_local  that we created earlier.