Testing Open a PDB as a Hybrid Read Only PDB in 23ai

 Oracle 23ai now allows the DBA to open a PDB in Hybrid Read Only mode.  This mode allows Common Users (e.g. SYS or SYSTEM or others defined as Common Users from the Root CDB) to access a PDB in Read-Write mode while local (i.e. non-Common Users) can access the PDB only in Read-Only mode.

This facilitates live maintenance (e.g. patching or changes to the database / schema) being executed by a DBA or Common User while "normal" local users (eg. Application Accounts) can still query the database.

This is a quick demo :

SQL> -- open the PDB as "normal" Read Write
SQL> connect / as sysdba
Connected.
SQL> -- Version 23ai Free Edition
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL>
SQL> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open;

Pluggable database altered.

SQL>
SQL> -- first demo a normal user in the PDB
SQL> connect hemant/hemant@freepdb1
Connected.
SQL> create table x_test (id number , data varchar2(15));

Table created.

SQL> insert into x_test values (1,'First');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First

SQL>
SQL>
SQL> -- now close and open the PDB in Hybrid Read Only mode
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open hybrid read only ;

Pluggable database altered.

SQL> -- test that SYSTEM (a Common User) can manipulate data -- e.g. INSERT
SQL> connect system/manager@freepdb1
Connected.
SQL> insert into hemant.x_test values(2,'System');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System

SQL> -- even grant DBA to hemant
SQL> grant dba to hemant;

Grant succeeded.

SQL>
SQL> -- test hemant a non-common user
SQL> -- see if the user can execute INSERT and SELECT
SQL> connect hemant/hemant@freepdb1
Connected.
SQL> insert into hemant.x_test values(3,'Third');
insert into hemant.x_test values(3,'Third')
                   *
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/


SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System

SQL> -- so SELECT works, but not INSERT
SQL>
SQL>
SQL> -- reopen PDB as normal "Read Write"
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open;

Pluggable database altered.

SQL> connect hemant/hemant@freepdb1
Connected.
SQL> insert into hemant.x_test values(4,'Fourth');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System
         4 Fourth

SQL>

Thus, when the PDB was first opened in "normal" mode (ie the default OPEN mode is OPEN READ WRITE), the local user  "HEMANT" could execute DDL and DML (create the table and Insert).  

When it was reopened in Hybrid Read Only Mode, the user could not make changes (insert the row with ID=3) but could still query the data (even if the user has been granted "DBA").  However,  the Common User "SYSTEM" was allowed to insert the row with ID=2, DATA='SYSTEM'.

Finally, reopening the PDB in "normal" OPEN READ WRITE mode, the user "HEMANT" could again insert a row (ID=4)