8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Hybrid Read-Only Mode for PDBs in Oracle Database 23ai
Oracle 23ai 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.
Related articles.
- Multitenant : Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : All Articles
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;
For more information see:
- Opening a Pluggable Database in Hybrid Read-Only Mode
- Multitenant : Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : All Articles
Hope this helps. Regards Tim...