Hybrid Read-Only Mode for Pluggable Databases in Oracle Database 23c

/ Multitenant 23c, Oracle DB 23c New Features / By

Oracle Database 23c introduces a new mode for pluggable databases (PDBs) called Hybrid Read-Only mode. This mode offers increased flexibility for managing maintenance and user access within multitenant environments.

Key Features:

  • Selective Read-Write Access: In Hybrid Read-Only mode, a PDB operates in either read-only or read-write mode depending on the user type:
    • Common users (CDB-level users) have full read-write access.
    • Local users (PDB-specific users) and common application users are restricted to read-only access.
  • Enhanced Maintenance Flexibility: This allows database administrators (DBAs) to perform maintenance tasks on a PDB without completely disrupting user access.
  • Simplified Patching and Updates: Common users can apply patches and updates while local users continue to query data without interference.
  • Database Forensics: Hybrid Read-Only mode can be useful for investigative purposes, allowing DBAs to analyze data while preventing modifications.

Enabling Hybrid Read-Only Mode

To enable Hybrid Read-Only mode for a PDB, use the following SQL statement:

ALTER PLUGGABLE DATABASE <pdb_name> OPEN HYBRID READ ONLY;

-- Example:
ALTER PLUGGABLE DATABASE mypdb OPEN HYBRID READ ONLY;

Scenario

A PDB named freepdb1 is in Hybrid Read-Only mode.
Two users exist:
– c##common_user: A common user with read-write privileges on the employees table.
– user1: A local user with read-only privileges on the employees table.:

Let’s create the common user:

CONN SYS/ORACLE AS SYSDBA
ALTER SESSION SET CONTAINER=CDB$ROOT;
CREATE USER C##COMMON_USER IDENTIFIED BY Abc##1234 CONTAINER=ALL;
GRANT DBA TO C##COMMON_USER CONTAINER=ALL;

Verify that the local user exists and employees table exists:

conn  user1/oracle@localhost:1521/freepdb1

desc employees
Name           Null?    Type
______________ ________ _______________
EMPLOYEE_ID             NUMBER
FIRST_NAME              VARCHAR2(15)
LAST_NAME               VARCHAR2(15)

Verify that the user is able to insert into the table:

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME) VALUES (100, 'Ahmed', 'Baraka');
COMMIT;

We start the PDB in hybrid read-only mode:

CONN SYS/ORACLE AS SYSDBA
ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER PLUGGABLE DATABASE FREEPDB1 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE FREEPDB1 OPEN HYBRID READ ONLY;

Let’s verify that the common user can perform DML on employees table:

CONN C##COMMON_USER/Abc##1234@localhost:1521/freepdb1

SQL> UPDATE USER1.EMPLOYEES SET FIRST_NAME='AHMED', LAST_NAME='BARAKA' WHERE EMPLOYEE_ID=100;
1 row updated.

SQL> COMMIT;
Commit complete.

Verify that the local user is unable to modify the employees table or create a new object:

SQL> conn user1/oracle@localhost:1521/freepdb1
Connected.

SQL> SQL> UPDATE USER1.EMPLOYEES SET FIRST_NAME='Ahmed', LAST_NAME='Baraka' WHERE EMPLOYEE_ID=100;
SQL Error: ORA-16000: Attempting to modify database or pluggable database that is open for read-only access.

SQL> create table test ( a number);
SQL Error: ORA-16000: Attempting to modify database or pluggable database that is open for read-only access.

Checking the Mode

To check the current mode of a PDB, query the V$PDBS view. The OPEN_MODE column will display READ WRITE for common users and READ ONLY for local users.

SQL> conn  c##common_user/Abc##1234@localhost:1521/freepdb1
Connected.
SQL> column name format a10
SQL> select name, open_mode from v$pdbs;

NAME        OPEN_MODE
___________ _____________
FREEPDB1    READ WRITE

# grant access on the v$ views to user1:
conn  sys/oracle as sysdba
SQL> grant select on v_$pdbs to user1;
Grant succeeded.
SQL> grant select on v_$container_topology to user1;
Grant succeeded.

# connect as a local user and check the open mode of pdb:
SQL> conn user1/oracle@localhost:1521/freepdb1
Connected.

SQL> column name format a10
SQL> select name, open_mode from v$pdbs;

NAME        OPEN_MODE
___________ ____________
FREEPDB1    READ ONLY

The V$CONTAINER_TOPOLOGY view displays the status differently. We can tell if the PDB is opened in Hybrid Read-Only mode.

SQL> conn  c##common_user/Abc##1234@localhost:1521/freepdb1
Connected.
SQL> column con_name format a10
SQL> column is_hybrid_read_only format a20
SQL> select con_name, open_mode, is_hybrid_read_only
  2* from   v$container_topology;

CON_NAME    OPEN_MODE     IS_HYBRID_READ_ONLY
___________ _____________ ______________________
FREEPDB1    READ WRITE    YES

SQL> conn user1/oracle@localhost:1521/freepdb1
Connected.
SQL> select con_name, open_mode, is_hybrid_read_only
  2* from   v$container_topology;

CON_NAME    OPEN_MODE     IS_HYBRID_READ_ONLY  
___________ _____________ ______________________
FREEPDB1    READ WRITE    YES

Additional Considerations
User Privileges: Users still require appropriate privileges (read or read-write) to perform operations, even in Hybrid Read-Only mode.
Application Common Users: These users are treated as local users in this context, having read-only access.
Disabling the Mode: To disable Hybrid Read-Only mode, use ALTER PLUGGABLE DATABASE OPEN READ WRITE;

Conclusion

Hybrid Read-Only mode for pluggable databases in Oracle Database 23c offers significant advantages for database administrators and application managers. It enables more efficient maintenance, patching, and investigative activities while maintaining controlled user access. This feature contributes to enhanced flexibility and control within multitenant environments.

Notes:
The code was tested on Oracle Database 23c Free, Release 23.0.0.0.0 – Developer-Release.
The output in the code examples was truncated for readability.

Scroll to Top