A new feature of Oracle Database 23ai allows us to configure Pluggable Databases to operate in a new mode called hybrid read-only mode.
Hybrid read-only mode enables the Pluggable Database to operate as either read-write or read-only mode – but this depends on which user is connected to the PDB!
For common users, the PDB will operate in both read-only as well as read-write mode. For local users, the PDB will be operating in exclusively read-only mode.
A new clause HYBRID READ ONLY is added to the ALTER PLUGGABLE DATABASE statement.
The V$CONTAINER_TOPOLOGY dynamic view has a new column, IS_HYBRID_READ_ONLY
The V$PDBS dynamic view OPEN_MODE column for local users shows READ ONLY – for common users, this column shows READ WRITE.
This hybrid read-only mode feature has a benefit where it enables database and application administrators to patch and maintain a PDB without the risk of local users including higher privileged ones with say a DBA role interfering or affecting maintenance operations currently in progress for a PDB.
Shutdown the Pluggable Database in order to convert it to Hybrid Read Only mode.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
SQL> alter pluggable database FREEPDB1 close immediate;
Pluggable database altered.
Open the PDB in Hybrid Read Only mode.
The SHOW PDBS command still shows the PDB in READ WRITE mode because we are connected as a common user. However we can see from the view V$CONTAINER_TOPOLOGY column IS_HYBRID_READ_ONLY column is showing the value of YES.
SQL> alter pluggable database FREEPDB1 open hybrid read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 FREEPDB1 READ WRITE NO
SQL> col is_hybrid_read_only format a30
SQL> col con_name format a30
SQL> select con_name, is_hybrid_read_only from v$container_topology;
CON_NAME IS_HYBRID_READ_ONLY
------------------------------ ------------------------------
FREEPDB1 YES
Connect as a local user and we can see that an error is returned when any DML type of operation is attempted.
SQL> conn hr/oracle@freepdb1
Connected.
SQL> truncate table jobs;
truncate table jobs
*
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
Help: https://docs.oracle.com/error-help/db/ora-16000/
The SYSTEM user is a Common User and is able to perform DML activity even though the PDB is operating in Hybrid Read Only mode.
SQL> select common from dba_users where username='SYSTEM';
COM
---
YES
SQL> update hr.jobs set MAX_SALARY=10000 where job_id='HR_REP';
1 row updated.
SQL> rollback;
Rollback complete.
Even if the DBA role is granted to a local user, DML activity can still not be performed. To perform any kind of DML activity the user needs to be a Common User!
SQL> grant dba to hr;
Grant succeeded.
SQL> conn hr/oracle@freepdb1
Connected.
SQL> delete hr.jobs;
delete hr.jobs
*
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
Help: https://docs.oracle.com/error-help/db/ora-16000/
We can convert the PDB back to its original Read Write default operating mode – without having to shutdown the database by using the FORCE clause of the ALTER PLUGGABLE DATABASE statement.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter pluggable database freepdb1 open read write force;
Pluggable database altered.
SQL> col is_hybrid_read_only format a30
SQL> col con_name format a30
SQL> select con_name, is_hybrid_read_only from v$container_topology;
CON_NAME IS_HYBRID_READ_ONLY
------------------------------ ------------------------------
FREEPDB1 NO
DML operations can now be performed by a local user like HR.
SQL> conn hr/oracle@freepdb1
Connected.
SQL> update hr.jobs set MAX_SALARY=10000 where job_id='HR_REP';
1 row updated.
SQL> rollback;
Rollback complete.
