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.
