Hybrid Read Only in Oracle 23ai

 "Hybrid Read only mode" one of the nicest improvements in Oracle 23ai.

With this , we can have the pluggable databases ( PDB) either as "read/write" / "read only" .

In this "read only" mode , the common users will able to update the tables , as if PDB is "open" and "read write" mode the local (PDB) users will not be able to update / insert any tables; they can "select only" from the pdb objects.

This features enables the DBAs to patch , update the database objects in a pluggable database. 

Here is an example.

There is a new column name 'IS_HYBRID_READ_ONLY' to the v$view ( v$container_topology)

Log on to CDB and review the PDB status.

Session 1:

SQL> show con_name;

CON_NAME 

------------------------------

CDB$ROOT

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

CON_NAME    OPEN_MODE     IS_HYBRID_READ_ONLY    

___________ _____________ ______________________ 

CDB$ROOT    READ WRITE    NO                     

PDB$SEED    READ ONLY     NO                     

FREEPDB1    READ WRITE    NO   

Session 2 :

In this PDB , let us create a user in PDB and give him access to create a table , quota unlimited on "USERS" tablespace. Note that , the user need not created with "LOCAL" as the prefix. 

SQL> show user                    

USER is "LOCAL_MOHIDEEN"

SQL> create table emp as select object_name as emp_name , object_type as dept from all_objects ;

Table created.

SQL> insert into emp values ( 'Zahir' , 'IT') ;

1 row created.

SQL> commit;

Commit complete.

As the PDB is open for "READ WRITE" , the local user was able to create a table and insert a record in to the table. 

Now , let us go to the session 1 and make the PDB as hybrid read only.

Session 1:

In this session , we made the PDB as "hybrid read only" and created a common user ( C##MOHIDEEN) and give him access 

to the table owned by the local user ( LOCAL_MOHIDEEN). Note that common user has to be created with C## as prefix. 

SQL> show con_name;

CON_NAME 

------------------------------

CDB$ROOT

SQL> alter pluggable database freepdb1 close immediate;

Pluggable database FREEPDB1 altered.

SQL> alter pluggable database freepdb1 open hybrid read only;

Pluggable database FREEPDB1 altered.

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

CON_NAME    OPEN_MODE     IS_HYBRID_READ_ONLY    

___________ _____________ ______________________ 

CDB$ROOT    READ WRITE    NO                     

PDB$SEED    READ ONLY     NO                     

FREEPDB1    READ WRITE    YES 

Session 2 :

Now , let us go to the other session and try to insert a record in to the table (EMP) 

SQL> show user;

USER is "LOCAL_MOHIDEEN"

 

SQL> insert into emp values ('John Doe','HR');

insert into emp values ('John Doe','HR')

            *

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> exit

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

Version 23.7.0.25.01

This time , let us log on to the PDB as the common user ( c##mohideen) 

SQL> show user

USER is "C##MOHIDEEN"

SQL> insert into local_mohideen.emp values ( 'John Doe','HR'); 

1 row created.

SQL> commit;

Commit complete.

There we have it . 

With the cdb user , I can do read/write operations, where with the local user the database is read only. 

Hybrid Rocks! :-) 

Comments Welcome!