Oracle 23c : New Feature- Hybrid Read only mode for PDB

In General, DBA's open pluggable database or PDB either in Read Write Mode or Read-only, what if the database behaves in read-only, and read-write mode based on the connected user. One such feature is introduced in Oracle 23c. As we know there are two types of users in multitenant database starting from Oracle12c, local user and common user, Local users are local to the pluggable database and common users are common to all pluggable and container databases. Common users can connect to a container and several pluggable databases. 

As the Oracle database 23c release date was announced, everybody was excited about new features for DBA's in Oracle 23c. So here is one feature with many benefits: Hybrid Read-only mode for a pluggable database. Hybrid Read only mode enables the pluggable database to operate either in read-only or read-write mode.

For common users- a pluggable database or PDB will be in both read-write and read-only mode

For Local users/application users - a pluggable database or PDB will be restricted to read-only mode

To enable Hybrid read-only mode, alter pluggable database command needs to be executed against PDB.

we will not be able to see hybrid read-only mode  through v$database or v$pdbs, it can be viewed through v$container_topology with new column IS_HYBRID_READ _ONLY

SQL> desc v$container_topology

 Name                    Null?    Type

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

 INSTANCE_NUMBER                     NUMBER

 CON_NAME                            VARCHAR2(128)

 OPEN_MODE                           VARCHAR2(10)

 CPU_COUNT                           NUMBER

 CON_ID                              NUMBER

 RESTRICTED                                VARCHAR2(3)

 IS_HYBRID_READ_ONLY                       VARCHAR2(3)

 

While we can see some different output in v$pdbs as well, for local users it will be read-only for common users it will be read-write mode

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

 

CON_NAME        OPEN_MODE  IS_HYBRID_READ_ONLY

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

FREEPDB1        READ WRITE NO

 

common users

 

SQL> show user

USER is "SYS"

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

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

FREEPDB1        READ WRITE

As local user 

SQL> show user

USER is "LOCAL_USR"

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

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

FREEPDB1        READ WRITE

 

SQL> 

Now change the mode of the pluggable database to Hybrid read-only mode. If the database is in open mode, close it using the close immediate command and open the pluggable database in hybrid read-only by using the below steps

 

SQL> alter pluggable database close immediate;

 

Pluggable database altered.

 

SQL> alter pluggable database open hybrid read-only;

 

Pluggable database altered.

 

SQL> show con_name

 

CON_NAME

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

FREEPDB1

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

 

CON_NAME        OPEN_MODE                    IS_HYBRID_READ_ONLY

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

FREEPDB1        READ WRITE             YES

 

SQL> show user

USER is "SYS"

SQL> 

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

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

FREEPDB1        READ WRITE

 

SQL> conn local_usr/Test123@freepdb1

Connected.

SQL> 

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

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

FREEPDB1        READ ONLY

 

SQL> 

In the above output, we can see, with common user SYS, it is READ WRITE mode, while with the local user it is READ ONLY mode

 

Reverting to the original state (Read Write):  follow simple steps to revert Oracle pluggable database to read-only mode

 

SQL> conn sys@freepdb1 as sysdba

Enter password: 

Connected.

SQL> alter pluggable database close immediate;

 

Pluggable database altered.

 

SQL> alter pluggable database open;

 

Pluggable database altered.

 

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

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

FREEPDB1        READ WRITE

In this way, we simply tested Oracle database 23c new feature of hybrid read-only mode, looks impressive feature, we will discuss more such features associated with oracle 23c.