A container database contains the root, the seed, and user pluggable databases. Each user PDB holds an application's schemas and data. The CDB can be open while that PDB is still mounted. Its open mode decides what ordinary users can do.
Oracle DBA Lesson 27A — Opening and Closing a PDB
After the CDB starts, check the PDB the application uses. A running CDB can hold PDBs in different modes. The question is the current mode of this application PDB.
Current modes and restricted access
| Setting | Meaning for ordinary application work |
|---|---|
MOUNTED |
The PDB is closed to ordinary application sessions. |
READ WRITE |
Users can query and change data, subject to their privileges. |
READ ONLY |
Users can query existing data. Use this mode when the work only reads data. |
RESTRICTED = YES |
A connection also requires the RESTRICTED SESSION privilege in that PDB. |
OPEN_MODE and RESTRICTED are two settings. A PDB can be READ WRITE with restricted access, or READ ONLY with restricted access. The first setting controls the type of work. The second controls who can connect. Oracle V$PDBS reference.
PDB$SEED supplies the starting content for new PDBs. Leave it unchanged in these open and close examples. LABPDB is the named user PDB in the commands below.
Check from the root
Use an authorized administrative SQL*Plus session in CDB$ROOT:
SHOW CON_NAME
SELECT name, open_mode, restricted
FROM v$pdbs
ORDER BY con_id;SHOW CON_NAME identifies the current container. The query reports the mode and connection restriction of each visible PDB. Locate the application PDB by NAME.
For a focused check:
SELECT name, open_mode, restricted
FROM v$pdbs
WHERE name = 'LABPDB';If LABPDB is mounted, an ordinary application account needs it opened before connecting. If it is already open, use that mode to decide whether a change is needed. RESTRICTED can be YES, NO, or NULL. Check it again after you open the PDB. Oracle V$PDBS reference.
Open the named PDB
Starting condition: LABPDB is MOUNTED, and the workload needs read/write access.
ALTER PLUGGABLE DATABASE LABPDB OPEN;On the primary database used here, OPEN defaults to READ WRITE. The explicit form is:
ALTER PLUGGABLE DATABASE LABPDB OPEN READ WRITE;Choose one form. The statement targets LABPDB. Other PDBs keep their current modes. A user still needs the account and object privileges to query or change data. Oracle ALTER PLUGGABLE DATABASE reference.
Check the result:
SELECT name, open_mode, restricted
FROM v$pdbs
WHERE name = 'LABPDB';For unrestricted read/write use, the intended values are READ WRITE and NO.
Change to read-only
Read-only supports queries. To switch an open PDB to read-only, close it, then open it in that mode.
ALTER PLUGGABLE DATABASE LABPDB CLOSE IMMEDIATE;
SELECT name, open_mode
FROM v$pdbs
WHERE name = 'LABPDB';
ALTER PLUGGABLE DATABASE LABPDB OPEN READ ONLY;
SELECT name, open_mode, restricted
FROM v$pdbs
WHERE name = 'LABPDB';After the close, expect MOUNTED. After the open, expect READ ONLY. Oracle also supports a FORCE mode change. This exercise uses the explicit close and open. Oracle ALTER PLUGGABLE DATABASE reference.
CLOSE IMMEDIATE ends work in the target PDB. Executing statements stop, users are disconnected, and uncommitted changes roll back. Wait for the close to finish before you open the PDB again. A large rollback takes time. Oracle PDB administration guide. Oracle shutdown guide.
An application may write during a report, for example by recording the report in a log table. Check the actual workload before you choose READ ONLY. Oracle read-only database guidance.
Restrict connections for maintenance
From an open PDB, close it, then open it restricted:
ALTER PLUGGABLE DATABASE LABPDB CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE LABPDB OPEN READ WRITE RESTRICTED;The intended result is OPEN_MODE = READ WRITE and RESTRICTED = YES. A connection to this PDB needs RESTRICTED SESSION in the PDB. Restricted access can be combined with read/write or read-only. From the mounted state, a read-only maintenance window uses OPEN READ ONLY RESTRICTED. Oracle PDB open modes.
After the work, restore the recorded baseline. When the baseline is unrestricted read/write:
ALTER PLUGGABLE DATABASE LABPDB CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE LABPDB OPEN READ WRITE;
SELECT name, open_mode, restricted
FROM v$pdbs
WHERE name = 'LABPDB';If the baseline was a different mode or restriction, reopen with those recorded settings.
Confirm application access
After the mode and restriction match the intended values, connect through the application's service. Here APP_SERVICE is an existing client alias for that service:
sqlplus -L course_owner@APP_SERVICEIn that session:
SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM dual;Expect LABPDB, then run a small application query. The service's PDB association decides the destination container. Use the service managed for the application. Service configuration is a separate topic. Oracle PDB connections and services.
Practice
These examples assume Oracle Database 19c, a single-instance primary CDB, and SQL*Plus. Use a designated disposable PDB. Coordinate the disconnects before a close. The root session needs the administrative privilege in the root and in the target PDB. This course uses AS SYSDBA. Record the release, the database and PDB identity, the original open mode, the restriction, and the service. Oracle statement prerequisites.
Complete one close and open cycle on LABPDB. Record the intermediate and final modes, restore the baseline, and test the application connection. Leave every other PDB, including PDB$SEED, as you found it.
No comments:
Post a Comment