Hybrid Read-Only Mode for PDBs in Oracle Database 23c

Oracle 23c allows us to open PDBs in hybrid read-only mode. This allows common users to work in read-write mode, while local users and common application users are restricted to read-only mode.

Why use hybrid read-only mode? It allows application administrators connected as common users to patch applications without risk of local users blocking the maintenance.

◉ Enable Hybrid Read-Only Mode

We connect to the root container and start our PDB in hybrid read-only mode.

conn / as sysdba

alter pluggable database freepdb1 close immediate;

alter pluggable database freepdb1 open hybrid read only;

The open mode displayed will depend on the type of user issuing the query. We create a common and local user to test this. A common application user would act like a local user in this context.

conn / as sysdba

create user c##testdba identified by testdba container=all;

grant dba to c##testdba container=all;

alter session set container=freepdb1;

create user testdba identified by testdba;

grant dba to testdba;

We connect using the common user and we see the following output from the V$PDBS and V$CONTAINER_TOPOLOGY views. The PDB is showing an open mode of read-write in the V$PDBS view.

conn c##testdba/testdba@//localhost:1521/freepdb1

column name format a10

select name, open_mode from v$pdbs;

NAME       OPEN_MODE

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

FREEPDB1   READ WRITE

SQL>

column con_name format a10

column is_hybrid_read_only format a20

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>

This time we connect to the local user and repeat the queries. This time the V$PDBS view displays an open mode of read-only.

conn testdba/testdba@//localhost:1521/freepdb1

column name format a10

select name, open_mode from v$pdbs;

NAME       OPEN_MODE

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

FREEPDB1   READ ONLY

SQL>

column con_name format a10

column is_hybrid_read_only format a20

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>

Notice the V$CONTAINER_TOPOLOGY view output remains unchanged.

◉ Test Common and Local Users

We've seen how the different types of users affect the perceived open mode of the database. Let's see how this affects normal use.

We connect to the PDB with the common user and perform some DDL and DML. All operations succeed as we would expect for a read-write database.

SQL> conn c##testdba/testdba@//localhost:1521/freepdb1

Connected.

SQL> drop user if exists testuser2 cascade;

User dropped.

SQL> create user testuser2 identified by testuser2 quota unlimited on users;

User created.

SQL> grant db_developer_role to testuser2;

Grant succeeded.

SQL>create table testuser2.t1 (id  number);

Table created.

SQL> insert into testuser2.t1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>

We connect to the PDB with the local user and perform some DDL and DML. All operations fail except read-only operations.

SQL> conn testdba/testdba@//localhost:1521/freepdb1

Connected.

SQL> drop user if exists testuser2 cascade;

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> create user testuser2 identified by testuser2 quota unlimited on users;

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> grant db_developer_role to testuser2;

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> create table testuser2.t1 (id  number);

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> insert into testuser2.t1 values (1);

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> select * from testuser2.t1;

        ID

----------

         1

SQL>

◉ Enable Read-Write Mode

We can switch back to read-write or read-only mode at any time.

conn / as sysdba

alter pluggable database freepdb1 close immediate;

alter pluggable database freepdb1 open read write;

Source: oracle.com