This is a guest blog by Oracle ACE Pro Deepak Vohra. Deepak is an Oracle Database 10g Administrator Certified Associate (OCA), and has published six containerization (Docker & Kubernetes) related books.
The Oracle Database 23c Free – Developer Release has been available free since April this year. It is a full-featured database that can be used on Oracle Linux and RHEL. If you are using Docker as your platform, or using a Linux distribution that is not listed in downloads, such as Ubuntu, you would still be able to use the new Oracle Database 23c Free with the Docker image for Oracle Database 23c Free.
In this article we shall discuss how to use a new feature in the Oracle Database 23c Free – Developer Release that introduces the new Hybrid Read-Only mode for pluggable databases (PDBs). The article has the following sections:
- What is the Hybrid Read-Only Mode
- Get the Docker Image
- Create your first Oracle Database 23c in a Docker Container
- Start an interactive Bash Shell
- Start a SQL* Plus Session
- The Database is a Container database
- Create a new CDB Common user
- Create a PDB Local User
- Connect as CDB Common User to the PDB that is in READ WRITE Mode
- Connect as the PDB Local User
- Change PDB Open Mode to READ ONLY
- Set PDB Open Mode to HYBRID READ ONLY
- Stop and start container
What is the Hybrid Read-Only Mode
Prior to Oracle Database 23c Free, pluggable databases (PDBs) in a multitenant container database could be opened in one of two modes : Read Write, or Read Only; the default being Read Write. Oracle Database 23c introduces a new mode called the Hybrid Read-Only mode. If the Hybrid Read-Only mode has been set, the effective open mode in a user session is different based on who the user is. The following table lists the effective open mode in a user session for PDB local user & CDB common user corresponding to the three different PDB open mode settings.
| Open Mode |
Effective Open Mode for PDB local user, and Application common user |
Effective Open Mode for CDB common user |
| READ WRITE |
READ WRITE |
READ WRITE |
| READ ONLY |
READ ONLY |
READ ONLY |
| HYBRID READ ONLY |
READ ONLY |
READ WRITE |
Note that in the HYBRID READ ONLY mode the effective open mode is READ WRITE for a CDB common user while it is READ ONLY for a PDB local user. Necessary read, write privileges still need to be granted to the users, CDB common user or PDB local user to be able to perform the read write operations.
What is the benefit of the HYBRID READ ONLY mode? It lets a database administrator perform maintenance and patching on pluggable databases (PDBs) while a PDB is open for read-only queries to local users. As an example, a PDB local user PDB_LOCAL can read but not make content changes to a pluggable database PDB1 while the database is being maintained by a CDB common user C##DBA with a commonly granted DBA role.
The Hybrid Read-Only mode has been implemented by introducing some changes, specifically:
- The ALTER PLUGGABLE DATABASE supports a new clause HYBRID READ ONLY
- The V$CONTAINER_TOPOLOGY dynamic view has a new column called IS_HYBRID_READ_ONLY
The DESCRIBE command on the dynamic view V$CONTAINER_TOPOLOGY lists the new column IS_HYBRID_READ_ONLY:
SQL> DESCRIBE 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)
In the Hybrid Read-Only mode, the open_mode column in the dynamic view V$PDBS still shows READ WRITE to CDB root user.
Get the Docker Image
First, you need to download, or pull in containerization parlance, the Docker image for Oracle Database 23c Free edition with the following docker pull command:
sudo docker pull container-registry.oracle.com/database/free
Using default tag: latest
latest: Pulling from database/free
def8df68f6e5: Pull complete
4f739c567988: Pull complete
579d0ff24aa5: Pull complete
480f5079c729: Pull complete
Digest: sha256:73ecc82314a625122bb283e2cb3d6778899e62d9e21792da03ac4fea74c017b9
Status: Downloaded newer image for container-registry.oracle.com/database/free:latest
container-registry.oracle.com/database/free:latest
List the Docker images with the docker images command, and the downloaded image should be listed.
sudo docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
container-registry.oracle.com/database/free latest 7c64410c08d5 2 months ago 10.3GB
The Docker image has some environment variables pre-configured as listed in table with corresponding values:
| Environment Variable |
Value |
| ORACLE_BASE |
/opt/oracle |
| ORACLE_HOME |
/opt/oracle/product/23c/dbhomeFree |
| RUN_FILE |
runOracle.sh |
| PWD_FILE |
setPassword.sh |
| CREATE_DB_FILE |
createDB.sh |
| USER_SCRIPTS_FILE |
runUserScripts.sh |
| CONF_FILE |
oracle-free-23c.conf |
| CHECK_SPACE_FILE |
checkSpace.sh |
| CHECK_DB_FILE |
checkDBStatus.sh |
| SETUP_LINUX_FILE |
setupLinuxEnv.sh |
| CONFIG_TCPS_FILE |
configTcps.sh |
| INSTALL_DIR |
/install |
| ORACLE_DOCKER_INSTALL |
true |
| CHECKPOINT_FILE_EXTN |
.created |
| ENABLE_ARCHIVELOG |
false |
| ORACLE_SID |
FREE |
| ORACLE_PDB |
FREEPDB1 |
| ORACLE_PWD |
|
| AUTO_MEM_CALCULATION |
false |
The environment variables may be obtained with the command docker image inspect container-registry.oracle.com/database/free.
Create your first Oracle Database 23c in a Docker Container
To use the Oracle Database 23c Free, create a Docker container for it from the Docker image downloaded earlier using the following docker run command that sets a password for the new database instance:
sudo docker run –name orcl \
-p 1521:1521 \
-v /opt/oracle/oradata \
-e ORACLE_SID=FREE \
-e ORACLE_PDB=FREEPDB1 \
-e ORACLE_PWD=ORCLPWD23 \
container-registry.oracle.com/database/free
As the command output lists the Oracle Database instance FREE gets started, and becomes available for use.
Starting Oracle Net Listener.
Oracle Net Listener started.
Starting Oracle Database instance FREE.
Oracle Database instance FREE started.
The Oracle base remains unchanged with value /opt/oracle
SQL*Plus: Release 23.0.0.0.0 – Developer-Release on Wed May 31 17:01:43 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 – Developer-Release
Version 23.2.0.0.0
SQL>
User altered.
SQL>
User altered.
SQL>
Session altered.
SQL>
User altered.
SQL> Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 – Developer-Release
Version 23.2.0.0.0
The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Completed: Pluggable database FREEPDB1 opened read write
Completed: ALTER DATABASE OPEN
2023-05-31T17:01:45.381896+00:00
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
2023-05-31T17:01:51.431835+00:00
FREEPDB1(3):TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P411 (3257) VALUES LESS THAN (TIMESTAMP’ 2023-06-01 00:00:00′)
List the running Docker containers with the docker ps command and the newly created container called orcl gets listed.
sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
27b0465f6cda container-registry.oracle.com/database/free “/bin/sh -c ‘exec $O…” 7 minutes ago Up 5 minutes (healthy) 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp orcl
Start an interactive Bash Shell
To be able to access the database in the container run the following docker exec command to start an interactive bash shell session within the container.
sudo docker exec -it orcl sh
At the sh command prompt, any database related command may be run. First, verify the listener status with the lsnrctl status command:
sh-4.4$ lsnrctl status
LSNRCTL for Linux: Version 23.0.0.0.0 – Developer-Release on 31-MAY-2023 17:07:51
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_FREE)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 23.0.0.0.0 – Developer-Release
Start Date 31-MAY-2023 17:00:36
Uptime 0 days 0 hr. 7 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service FREE
Listener Parameter File /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/27b0465f6cda/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_FREE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Services Summary…
Service “FREE” has 1 instance(s).
Instance “FREE”, status READY, has 1 handler(s) for this service…
Service “FREEXDB” has 1 instance(s).
Instance “FREE”, status READY, has 1 handler(s) for this service…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “f7f6d3d36c1809a9e0536402000a3c5a” has 1 instance(s).
Instance “FREE”, status READY, has 1 handler(s) for this service…
Service “freepdb1” has 1 instance(s).
Instance “FREE”, status READY, has 1 handler(s) for this service…
The command completed successfully
sh-4.4$
The value of the environment variables set in the container may be listed, as an example:
sh-4.4$ echo $ORACLE_SID
FREE
sh-4.4$ echo $ORACLE_PDB
FREEPDB1
Start a SQL* Plus Session
Next, start a SQL* Plus session with the sqlplus command.
sh-4.4$ sqlplus
SQL*Plus: Release 23.0.0.0.0 – Developer-Release on Wed May 31 17:15:37 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
When prompted for user name specify SYS AS SYSDBA.
Enter user-name: SYS AS SYSDBA
When prompted for password specify the password configured when starting the container orcl.
Enter password:
As the output indicates the SYS user gets connected.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 – Developer-Release
Version 23.2.0.0.0
The Database is a Container database
Starting with Oracle Database 21c Oracle Database is available only as a multitenant CDB database, and support for the non-CDB database has been discontinued. A non-CDB database may be upgraded to a CDB as discussed in the upgrade guide. Verify from the CDB column in the v$database view whether the database is a CDB (container database).
SQL>
SELECT CDB from v$database;
SQL> SELECT CDB from v$database;
CDB
—
YES
Display the current container name, and the root container gets listed.
SQL> SHOW CON_NAME
CON_NAME
——————————
CDB$ROOT
List the container id.
SQL> SHOW CON_ID
CON_ID
——————————
1
List the PDBs (pluggable databases) from the v$pdbs view, and the FREEPDB1 should get listed. The other PDB listed PDB$SEED is the seed PDB.
SQL> select con_id, dbid, name from v$pdbs;
CON_ID DBID
———- ———-
NAME
——————————————————————————–
2 871956951
PDB$SEED
3 1511376183
FREEPDB1
SQL>
Verify that the open_mode for the FREEPDB1 is READ WRITE, which is the default and needed to be able to both read and write to the PDB, from the v$pdbs view column open_mode with the following command:
SQL> select name, open_mode from v$pdbs;
NAME
——————————————————————————–
OPEN_MODE
———-
PDB$SEED
READ ONLY
FREEPDB1
READ WRITE
The datafiles for the different container databases may be listed from the v$datafile view.
SQL> select name from v$datafile;
NAME
——————————————————————————–
/opt/oracle/oradata/FREE/system01.dbf
/opt/oracle/oradata/FREE/pdbseed/system01.dbf
/opt/oracle/oradata/FREE/sysaux01.dbf
/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf
/opt/oracle/oradata/FREE/users01.dbf
/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf
/opt/oracle/oradata/FREE/undotbs01.dbf
/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
11 rows selected.
The tempfiles may also be listed from the v$tempfile view.
SQL> select con_id,name from v$tempfile;
CON_ID
———-
NAME
——————————————————————————–
1
/opt/oracle/oradata/FREE/temp01.dbf
2
/opt/oracle/oradata/FREE/pdbseed/temp01.dbf
3
/opt/oracle/oradata/FREE/FREEPDB1/temp01.dbf
By default the IS_HYBRID_READ_ONLY column shows NO for all container databases. The column name is truncated to IS_ in the SELECT output.
SQL> SELECT * from V$CONTAINER_TOPOLOGY;
INSTANCE_NUMBER
—————
CON_NAME
——————————————————————————–
OPEN_MODE CPU_COUNT CON_ID RES IS_
———- ———- ———- — —
1
CDB$ROOT
READ WRITE 2 1 NO NO
1
PDB$SEED
READ ONLY 2 2 NO NO
INSTANCE_NUMBER
—————
CON_NAME
——————————————————————————–
OPEN_MODE CPU_COUNT CON_ID RES IS_
———- ———- ———- — —
1
FREEPDB1
READ WRITE 2 3 NO NO
Create a new CDB Common user
A CDB common user can be used in all the PDBs within a CDB. Create a new CDB common user with the CREATE USER command. Connected to the root CDB, a common user (a user for all the PDBs within the CDB) can be created with the CONTAINER clause set to ALL.
SQL> CREATE USER C##u1 IDENTIFIED BY pwd1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS CONTAINER=ALL;
User created.
Grant CREATE SESSION and CREATE TABLE privileges to the common user. A privilege can be granted commonly by setting CONTAINER=ALL in the GRANT statement. The common user name must start with the value set in the initialization parameter COMMON_USER_PREFIX, C## by default.
SQL> GRANT CREATE SESSION, CREATE TABLE TO C##U1 CONTAINER=ALL;
Grant succeeded.
If the prefix is not included for a CDB common user name, the CREATE USER command fails as follows.
SQL> CREATE USER u1 IDENTIFIED BY p1 CONTAINER=ALL;
CREATE USER u1 IDENTIFIED BY p1 CONTAINER=ALL *
ERROR at line 1:
ORA-65096: common user or role name must start with prefix C##
Create a PDB Local User
To use the PDB called FREEPDB1 run the ALTER SESSION command to set the container to FREEPDB1.
SQL> alter session set container = FREEPDB1;
Session altered.
The session should have changed to the PDB.
SQL> SHOW CON_NAME;
CON_NAME
——————————
FREEPDB1
The system privileges granted to the root user connected to the PDB may be listed from the SESSION_PRIVS table. A subset of the privileges are listed:
SQL> SELECT * FROM SESSION_PRIVS;;
PRIVILEGE
—————————————-
ALTER DATABASE
ALTER ANY ROLE
GRANT ANY ROLE
DROP ANY ROLE
CREATE ROLE
DROP PUBLIC DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREATE DATABASE LINK
SELECT ANY SEQUENCE
DROP ANY SEQUENCE
ALTER ANY SEQUENCE
CREATE ANY SEQUENCE
DROP ANY INDEX
ALTER ANY INDEX
CREATE ANY INDEX
DELETE ANY TABLE
UPDATE ANY TABLE
INSERT ANY TABLE
SELECT ANY TABLE
ALTER ANY TABLE
CREATE ANY TABLE
CREATE TABLE
DROP USER
ALTER USER
BECOME USER
CREATE USER
UNLIMITED TABLESPACE
DROP TABLESPACE
ALTER SESSION
CREATE SESSION
AUDIT SYSTEM
ALTER SYSTEM
…
…
278 rows selected.
Create a PDB local user called orcl_pdb. The CONTAINER clause is set to CURRENT to indicate that the PDB user is a local user for the current container database.
SQL> CREATE USER orcl_pdb
IDENTIFIED BY pwd1
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS CONTAINER=CURRENT;
User created.
Grant CREATE SESSION and CREATE TABLE privileges to the PDB local user with CONTAINER clause set to CURRENT.
SQL> GRANT CREATE SESSION, CREATE TABLE TO orcl_pdb CONTAINER=CURRENT;
Grant succeeded.
Connect as CDB Common User to the PDB that is in READ WRITE Mode
The default open mode being READ WRITE, a CDB common user is able to perform DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), and DCL (Data Control Language) operations
Next, connect to the PDB as a CDB common user. Specify password when prompted.
SQL> CONNECT C##U1@FREEPDB1
Enter password:
Connected.
As the CDB common user, create a new table, and add data to the table.
SQL> CREATE TABLE wlslog(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255));
Table created.
SQL> INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:16-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STANDBY’);
1 row created.
Create a PDB Local User
Next, create a PDB local user while connected as a user that has privileges to create a user.As an example, connect SYS AS SYSDBA.
SQL> connect SYS AS SYSDBA
Enter password:
Connected.
Switch session to the pluggable container database FREEPDB1.
SQL> ALTER SESSION SET CONTAINER=FREEPDB1;
Session altered.
The session name should list the FREEPDB1.
SQL> SHOW CON_NAME;
CON_NAME
——————————
FREEPDB1
Create a new user with the CREATE USER command. Set the CONTAINER clause to CURRENT for the user to be for the current container only.
SQL> CREATE USER orcl_pdb
IDENTIFIED BY pwd1
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS CONTAINER=CURRENT;
User created.
Grant CREATE SESSION and CREATE TABLE privileges to the orcl_pdb user, again with CONTAINER clause set to CURRENT.
SQL> GRANT CREATE SESSION, CREATE TABLE TO orcl_pdb CONTAINER=CURRENT;
Grant succeeded.
Connect as the PDB Local User
Next, connect to the FREEPDB1 as the new local user orcl_pdb.
SQL> CONNECT orcl_pdb@FREEPDB1
Enter password:
Connected.
The default open mode being READ WRITE a PDB local user is able to perform read-write operations. As an example, create a new table and add data to the table.
SQL> CREATE TABLE wlslog(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255));
Table created.
SQL> INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:16-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STANDBY’);
1 row created.
Change PDB Open Mode to READ ONLY
Next, we shall change the PDB open mode to READ ONLY and demonstrate that it affects both a CDB common user and a PDB local user. Connected with SYS AS SYSDBA, the following ALTER PLUGGABLE database command sets the open mode of the FREEPDB1 to mounted.
SQL> CONNECT SYS AS SYSDBA
Enter password:
Connected.
If already connected as a user with the necessary privileges, any uncommitted transactions must be committed before running the ALTER PLUGGABLE statement.
SQL> COMMIT;
Commit complete.
SQL> ALTER PLUGGABLE DATABASE FREEPDB1 CLOSE IMMEDIATE;
Pluggable database altered.
Next, set the open mode of the FREEPDB1 to READ ONLY.
SQL>
SQL> ALTER PLUGGABLE DATABASE FREEPDB1 OPEN READ ONLY;
Pluggable database altered.
In the READ ONLY open mode neither of CDB common users or PDB local users are able to perform any write operations, which includes DDL and DML, but not DQL.
To demonstrate, connect as the CDB common user C##U1 and create a table, or rather try to create a new table.
SQL> CONNECT C##U1@FREEPDB1
Enter password:
Connected.
SQL> CREATE TABLE wlslog_copy(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255));
An error message gets generated, which is expected in the READ ONLY mode.
CREATE TABLE wlslog_copy(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255))
*
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
Try to add some data to an existing table.
SQL> INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:17-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STARTING’);
Again, an error message gets generated, which is expected in the READ ONLY mode.
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:17-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STARTING’)
*
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
The CDB common user is able to perform a read with a SELECT statement.
SQL> SELECT time_stamp || ‘ ‘ || category || ‘ ‘ || type || ‘ ‘ || servername || ‘ ‘ ||code || ‘ ‘ || msg from wlslog;
TIME_STAMP||”||CATEGORY||”||TYPE||”||SERVERNAME||”||CODE||”||MSG
——————————————————————————–
Apr-8-2014-7:06:16-PM-PDT Notice WebLogicServer AdminServer BEA-000365 Server st
ate changed to STANDBY
Next, connect to the PDB that is in READ ONLY mode as a PDB local user and try to perform DDL and DML operations.
SQL> CONNECT orcl_pdb@FREEPDB1
Enter password:
Connected.
Try to create a new table.
SQL> CREATE TABLE wlslog_copy(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255));
An error message gets generated indicating that the CREATE TABLE statement cannot be run because the database is open in read-only mode.
CREATE TABLE wlslog_copy(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255))
*
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
Try to add some data to an existing table.
SQL> INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:17-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STARTING’);
Again, an error message gets generated, as expected.
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:17-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STARTING’)
*
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
A DQL SELECT statement, however, runs without an error.
SQL> SELECT time_stamp || ‘ ‘ || category || ‘ ‘ || type || ‘ ‘ || servername || ‘ ‘ ||code || ‘ ‘ || msg from wlslog;
TIME_STAMP||”||CATEGORY||”||TYPE||”||SERVERNAME||”||CODE||”||MSG
——————————————————————————–
Apr-8-2014-7:06:16-PM-PDT Notice WebLogicServer AdminServer BEA-000365 Server st
ate changed to STANDBY
In the READ ONLY open mode, the IS_HYBRID_READ_ONLY column in the dynamic view V$CONTAINER_TOPOLOGY still shows NO for the FREEPDB1.
SQL> CONNECT SYS AS SYSDBA
Enter password:
Connected.
SQL> SELECT * from V$CONTAINER_TOPOLOGY;
INSTANCE_NUMBER
—————
CON_NAME
——————————————————————————–
OPEN_MODE CPU_COUNT CON_ID RES IS_
———- ———- ———- — —
1
CDB$ROOT
READ WRITE 2 1 NO NO
1
PDB$SEED
READ ONLY 2 2 NO NO
INSTANCE_NUMBER
—————
CON_NAME
——————————————————————————–
OPEN_MODE CPU_COUNT CON_ID RES IS_
———- ———- ———- — —
1
FREEPDB1
READ ONLY 2 3 NO NO
The open_mode in the v$pdbs dynamic view gets listed as READ ONLY.
SQL> select name, open_mode from v$pdbs;
NAME
——————————————————————————–
OPEN_MODE
———-
PDB$SEED
READ ONLY
FREEPDB1
READ ONLY
Set PDB Open Mode to HYBRID READ ONLY
As before, connect as a user with the requisite privileges, such as SYS AS SYSDBA, and close the pluggable database with ALTER PLUGGABLE DATABASE statement.
SQL> CONNECT SYS AS SYSDBA
Enter password:
Connected.
SQL> ALTER PLUGGABLE DATABASE FREEPDB1 CLOSE IMMEDIATE;
Pluggable database altered.
The database state changes to mounted. Open the pluggable database FREEPDB1 in HYBRID READ ONLY mode.
SQL> ALTER PLUGGABLE DATABASE FREEPDB1 OPEN HYBRID READ ONLY;
Pluggable database altered.
The server log lists the details of when the database is opened in HYBRID READ ONLY mode.
ALTER PLUGGABLE DATABASE FREEPDB1 OPEN HYBRID READ ONLY
2023-06-01T00:15:17.430416+00:00
FREEPDB1(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
FREEPDB1(3):Autotune of undo retention is turned on.
FREEPDB1(3):Undo initialization recovery: Parallel FPTR complete: start:9629505 end:9629507 diff:2 ms (0.0 seconds)
FREEPDB1(3):[1571] Successfully onlined Undo Tablespace 2.
2023-06-01T00:15:18.716710+00:00
FREEPDB1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
2023-06-01T00:15:19.772842+00:00
Completed: Pluggable database FREEPDB1 opened read write
Completed: ALTER PLUGGABLE DATABASE FREEPDB1 OPEN HYBRID READ ONLY
In the HYBRID READ ONLY open mode, the IS_HYBRID_READ_ONLY column in the dynamic view V$CONTAINER_TOPOLOGY shows YES for the FREEPDB1.
SQL> SELECT * from V$CONTAINER_TOPOLOGY;
INSTANCE_NUMBER
—————
CON_NAME
——————————————————————————–
OPEN_MODE CPU_COUNT CON_ID RES IS_
———- ———- ———- — —
1
CDB$ROOT
READ WRITE 2 1 NO NO
1
PDB$SEED
READ ONLY 2 2 NO NO
INSTANCE_NUMBER
—————
CON_NAME
——————————————————————————–
OPEN_MODE CPU_COUNT CON_ID RES IS_
———- ———- ———- — —
1
FREEPDB1
READ WRITE 2 3 NO YES
The IS_HYBRID_READ_ONLY column value may be selected separately, and it should be listed as YES for the third row, which is for the FREEPDB1.
SQL> SQL> SELECT IS_HYBRID_READ_ONLY FROM V$CONTAINER_TOPOLOGY;
IS_
—
NO
NO
YES
The open_mode column in the dynamic view v$pdbs shows READ WRITE in the root CDB.
SQL> select name, open_mode from v$pdbs;
NAME
——————————————————————————–
OPEN_MODE
———-
PDB$SEED
READ ONLY
FREEPDB1
READ WRITE
Next, we shall demonstrate that a CDB common user is still able to perform DDL, and DML operations in the PDB that is opened in HYBRID READ ONLY mode, but a PDB local user is not.
Connect as the PDB local user orcl_pdb that we created earlier.
SQL> CONNECT orcl_pdb@FREEPDB1
Enter password:
Connected.
Run a DQL SELECT statement, and it returns the query result.
SQL> SELECT time_stamp || ‘ ‘ || category || ‘ ‘ || type || ‘ ‘ || servername || ‘ ‘ ||code || ‘ ‘ || msg from wlslog;
TIME_STAMP||”||CATEGORY||”||TYPE||”||SERVERNAME||”||CODE||”||MSG
——————————————————————————–
Apr-8-2014-7:06:16-PM-PDT Notice WebLogicServer AdminServer BEA-000365 Server st
ate changed to STANDBY
Try running a DDL statement such as CREATE TABLE.
SQL> CREATE TABLE wlslog_copy(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255));
An error message is returned indicating that the pluggable database is open for read-only access.
CREATE TABLE wlslog_copy(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255))
*
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
Try to run a DML statement such as an INSERT INTO statement.
SQL> INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:17-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STARTING’);
Again, an error message indicates that the database cannot be modified as it is open in read-only access.
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:17-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STARTING’)
*
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
The FREEPDB1 is open for read-only access only for the PDB local user, and not so for a CDB common user. Connect as the CDB common user C##U1.
SQL> CONNECT C##U1@FREEPDB1
Enter password:
Connected.
Try to run a DDL statement, such as a CREATE TABLE and the table gets created.
SQL> CREATE TABLE wlslog_copy(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255));
Table created.
Try to run a DML statement such as INSERT and the data gets added.
SQL> INSERT INTO wlslog_copy(time_stamp,category,type,servername,code,msg) VALUES(‘Apr-8-2014-7:06:17-PM-PDT’,’Notice’,’WebLogicServer’,’AdminServer’,’BEA-000365′,’Server state changed to STARTING’);
1 row created.
Stop and start container
The Docker container for the Oracle Database 23 may be stopped and started as needed. To stop the container run the docker stop command.
~$ sudo docker stop orcl
Orcl
Subsequently, the docker ps command shouldn’t list the container if it has been stopped.
~$ sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
The stopped container may be started again with docker start command.
~$ sudo docker start orcl;
Orcl
The container gets listed as running.
~$ sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
27b0465f6cda container-registry.oracle.com/database/free “/bin/sh -c ‘exec $O…” 2 hours ago Up 7 seconds (health: starting) 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp orcl
In this article we discussed using the new Oracle Database 23c Free edition with Docker. We also discussed a new feature in Oracle Database 23 – the new HYBRID READ ONLY open mode for PDBs.
For further reference: