DBA Hub

📋Steps in this guide1/3

Hybrid Read-Only Mode for PDBs in Oracle Database 23ai/26ai

Oracle 23ai/26ai 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.

oracle 23configurationintermediate
by OracleDba
17 views
1

Enable Hybrid Read-Only Mode

We connect to the root container and start our PDB in hybrid read-only mode. 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. We connect using the common user and we see the following output from the and views. The PDB is showing an open mode of read-write in the view. This time we connect to the local user and repeat the queries. This time the view displays an open mode of read-only. Notice the view output remains unchanged.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
conn / as sysdba

alter pluggable database freepdb1 close immediate;
alter pluggable database freepdb1 open hybrid read only;

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;

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>

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>
2

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. We connect to the PDB with the local user and perform some DDL and DML. All operations fail except read-only operations.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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>

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>
3

Enable Read-Write Mode

We can switch back to read-write or read-only mode at any time. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
conn / as sysdba

alter pluggable database freepdb1 close immediate;
alter pluggable database freepdb1 open read write;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!