[26ai] Hybrid Read-Only Mode PDB

If you prefer to read in Spanish Spanish version.

Today, let's talk about a new Hybrid Read-Only open-mode in 26ai.

When we set up this mode, our PDB is read-only mode for local users and read-write for common users.

So, let's get down to business!

First of all, let's create a new PDB:

SQL> CREATE PLUGGABLE DATABASE PDB_HYBRID ADMIN USER admin IDENTIFIED BY admin keystore identified by external store;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE  PDB_HYBRID OPEN;
Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE  PDB_HYBRID SAVE STATE;
Pluggable database altered.

Let's create both users:

SQL> CREATE USER c##hybrid IDENTIFIED BY "XXXXXXX";
User created.
SQL> GRANT DBA TO c##hybrid CONTAINER=ALL;
Grant succeeded.

SQL> alter session set container=pdb_hybrid;
SQL> create smallfile tablespace tb1 
      datafile size 1m autoextend on maxsize 10m;

Tablespace created.

SQL> CREATE USER hybrid IDENTIFIED BY "XXXXXXX"
  DEFAULT TABLESPACE tb1
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON tb1;  

User created.

SQL> GRANT DBA TO hybrid;

Grant succeeded.

All right, we have just created both users, on one hand, the common user is c##hybrid. On the other hand, the local user is hybrid .

Before opening it with the new state, let's connect as local user in order to create a table with some information:

SQL> conn hybrid/"XXXXX"@X.X.X.X:1521/pdb_hybrid.XXXX.XXXX.XXXX.com
Connected.
SQL>      
SQL> 
 Create table T_TEST AS 
  SELECT OBJECT_NAME FROM ALL_OBJECTS;

Table created.

Let's connect to CDB$Root in order to open the PDB in hybrid read-only mode.

SQL> alter pluggable database PDB_HYBRID close;

Pluggable database altered.

SQL> alter pluggable database PDB_HYBRID open hybrid read only;

Pluggable database altered.

SQL> r
    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
PDB_HYBRID           READ WRITE YES

Let's connect as local user in order to execute DML (delete) and DDL (truncate).

SQL>  conn hybrid/"XXXXX"@X.X.X.X:1521/pdb_hybrid.XXXX.XXXX.XXXX.com
Connected.
SQL> set linesize 1000
SQL> col name format a20
SQL> select name, open_mode from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB_HYBRID           READ ONLY

SQL> delete T_TEST;
delete T_TEST
       *
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> truncate table T_TEST;
truncate table T_TEST
*
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/

Both operations failed with the following message "ORA-16000: Attempting to modify database or pluggable database that is open for read-only access"

Let's connect as the common user in order to performn the same operations.

SQL>  conn c##hybrid/"FJA_2026_Oracle19c" 
Connected.

SQL>  alter session set container=PDB_HYBRID;
Session altered.

SQL>  select name, open_mode from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB_HYBRID           READ WRITE

SQL> delete hybrid.T_TEST;

52165 rows deleted.

SQL> truncate table hybrid.T_TEST;

Table truncated.

Both operations completed sucessfully.

Before calling it a day, when we check v$pdbs as local user, the value in OPEN_MODE is READ ONLY, whereas, the value for common user is READ WRITE.

Looking forward to seeing you in the next article :)