Enhance Skills Daily

October 03, 2026  ·   · 

Oracle DBA Lesson 48 Part 2 — Make the New PDB Ready for Use

Creating a pluggable database (PDB) leaves it mounted. Making APP_PDB ready for use opens it, checks that the local administrator APP_ADMIN reaches that container, records the open state that should persist, and documents a recovery baseline.

Oracle DBA Lesson 48 Part 2 — Make the New PDB Ready for Use

Open the exact new PDB

Open APP_PDB from CDB$ROOT. The primary CDB is already open read/write. Oracle requires an applicable administrative privilege for this root-issued change: SYSDBA, SYSOPER, SYSBACKUP, or SYSDG, granted commonly or with the documented root-and-target local grants. That privilege is separate from CREATE PLUGGABLE DATABASE.

SHOW CON_NAME
ALTER PLUGGABLE DATABASE app_pdb OPEN READ WRITE;

SELECT pdb_name, status FROM dba_pdbs
WHERE pdb_name = 'APP_PDB';

SELECT name, open_mode, restricted FROM v$pdbs
WHERE name = 'APP_PDB';

The two queries return separate results:

PDB_NAMESTATUS
APP_PDBNORMAL
NAMEOPEN_MODERESTRICTED
APP_PDBREAD WRITENO

The first read/write opening completes integration of this seed-derived PDB into its CDB. Lifecycle status changes from NEW to NORMAL. The first opening has to be read/write. STATUS and OPEN_MODE answer different questions: the lifecycle is integrated, and the current mode permits queries and user transactions. Account privileges, network connectivity, storage capacity, and recoverability each need their own check.

The statement names only APP_PDB. Read the actual target state and any error before using ALL or FORCE. A later mode change, or closing the PDB, can affect sessions. See creating a PDB from scratch, ALTER PLUGGABLE DATABASE, V$PDBS, and DBA_PDBS.

Restrictions and compatibility messages

V$PDBS.RESTRICTED = YES limits connections to users who have RESTRICTED SESSION in the PDB. An ordinary account still needs its own connection test.

From the root, outstanding entries for this PDB are:

SELECT name, type, status, message, action
FROM pdb_plug_in_violations
WHERE name = 'APP_PDB'
  AND status <> 'RESOLVED'
ORDER BY time, line;

TYPE identifies ERROR or WARNING. MESSAGE describes the problem. ACTION describes a corrective action. STATUS can be PENDING, RESOLVED, or IGNORE. Excluding RESOLVED keeps every entry that still needs assessment, including rows already marked IGNORE. Mark RESOLVED when the condition is actually handled, and record that disposition. Investigate errors and a restricted opening before normal use. Assess each warning for the release, the components, and the intended workload. When an opening fails or reports problems, read the alert log and the related diagnostic messages. An empty filtered result covers only the rows this query selected. See PDB_PLUG_IN_VIOLATIONS.

Local account access

Switch to the new PDB and confirm the container before local changes:

ALTER SESSION SET CONTAINER = app_pdb;
SHOW CON_NAME

SELECT username, account_status, default_tablespace, temporary_tablespace
FROM dba_users WHERE username = 'APP_ADMIN';

SELECT granted_role, default_role FROM dba_role_privs
WHERE grantee = 'APP_ADMIN';

SELECT privilege FROM dba_sys_privs
WHERE grantee = 'APP_ADMIN';

Direct system-privilege rows are one part of the effective privileges. Also review each role's contents, which roles are enabled or default, and the direct grants. Part 1 created APP_ADMIN locally and granted PDB_DBA locally. Read the privileges inside that role. The name is only a label.

When CREATE SESSION is missing from the privileges required to log in, grant it in this PDB:

GRANT CREATE SESSION TO app_admin;

CREATE SESSION permits a connection in this container. Other administrative and application privileges stay separate grants. Account lock or expiry, authentication, service mapping, and a restricted open mode still decide whether the login succeeds. Keep the administrative account separate from application workload accounts, and grant each account only the privileges it needs. A failed login is explained by the account status, authentication, service, restriction, and the privilege the login requires. DBA is a wider role than that check. A new grant is additional. The account keeps the privileges it already had.

Service and a fresh client connection

A service's PDB property names the container where a connection starts. From the root:

SELECT name, network_name, pdb FROM all_services
WHERE pdb = 'APP_PDB';

Check the service the intended listener advertises, for example with lsnrctl services for that listener. The client route uses the correct host, port, and the full advertised service name. A dictionary service row, a listener listing, and a completed client login are three observations on the same path. Collect the observations that locate a failure.

PDB_CHECK is a client net-service alias configured for that verified route. For this administrative check it may target the PDB's default service. Oracle creates the default service with the PDB, and that service is for administration. An application uses a user-defined service configured for its requirements. Use SRVCTL when Oracle Restart or Clusterware manages the database. Otherwise create and manage the service with DBMS_SERVICE in the PDB that should own it. A service created through DBMS_SERVICE in the root is associated with the root. Use one management method for a given service, and give any second service its own name.

A separate client session exercises the login route:

sqlplus /nolog
CONNECT app_admin@pdb_check
SHOW CON_NAME

SQL*Plus prompts for the account password, so the connect command leaves the password out.

CON_NAME
APP_PDB

The new login arrives in APP_PDB as APP_ADMIN. A container switch by a common administrator stays inside the existing session and is useful for administration. The fresh CONNECT is the local-account network login. Application grants and workload behavior are checked with the application account and the application service. See administering PDBs and ALL_SERVICES.

Saved open state

After APP_PDB is read/write and unrestricted, return to the root and save that state:

ALTER SESSION SET CONTAINER = CDB$ROOT;
SHOW CON_NAME
ALTER PLUGGABLE DATABASE app_pdb SAVE STATE;

SELECT con_name, instance_name, state, restricted
FROM dba_pdb_saved_states
WHERE con_name = 'APP_PDB';

The root operation requires the ALTER DATABASE privilege. SAVE STATE records the desired PDB open mode for a later CDB restart. The returned row shows the container, the instance, the saved state, and the restriction setting. A backup, a client service, and a successful recovery are separate work. This lesson uses one instance. In RAC, saved state is instance-specific, so each instance needs its own inspection. See DBA_PDB_SAVED_STATES.

When the PDB had no saved state and should stay mounted after a CDB restart, discard the saved state for that PDB:

ALTER PLUGGABLE DATABASE app_pdb DISCARD STATE;

After DISCARD STATE, a CDB restart leaves the PDB mounted. If DBA_PDB_SAVED_STATES already has a row for APP_PDB, that row is the current reopen policy. Read it before changing the policy.

Storage and recovery preparation

Inside the target PDB, read the tablespaces and files the PDB actually has:

ALTER SESSION SET CONTAINER = app_pdb;
SELECT tablespace_name, contents, status
FROM dba_tablespaces ORDER BY tablespace_name;

SELECT tablespace_name, file_name, bytes, autoextensible, maxbytes
FROM dba_data_files ORDER BY tablespace_name, file_id;

SELECT tablespace_name, file_name, bytes, autoextensible, maxbytes
FROM dba_temp_files ORDER BY tablespace_name, file_id;

Permanent data files and temporary files are files on storage. With local undo enabled, a seed-derived PDB includes its own undo tablespace. Find the CONTENTS = 'UNDO' row and the data files that belong to it. Shared undo has different ownership, so read that configuration with the CDB undo design. Also check the application default tablespace, temporary storage, quotas, autoextension limits, and free capacity on the storage underneath. MAXBYTES and autoextension settings are a growth ceiling. Physical free space is measured on the storage itself. See CDB configuration and local undo and DBA_TABLESPACES.

Back up the new PDB from RMAN connected to the root. The PDB-specific command is:

BACKUP PLUGGABLE DATABASE app_pdb;

The command names the backup scope. A backup strategy also includes logging mode, the archived redo recovery needs, retention, control-file and SPFILE metadata, backup destinations, credentials, and any encryption keys. Keep the CDB recovery plan together with this PDB data-file backup. Evidence is a completed backup job and a backup that is available to read. A scheduled job that has not run leaves backup completion pending. RESTORE ... VALIDATE checks that RMAN can read the selected backup resources and leaves the database files in place. An end-to-end restore and recovery test is a further check, and it writes to an isolated destination. See backing up CDBs and PDBs and validating database files and backups.

Recap

The first read/write open changes APP_PDB from NEW to NORMAL. RESTRICTED and unresolved plug-in violations are separate from that open mode. APP_ADMIN reaches the PDB through a verified service and a fresh login. SAVE STATE stores the reopen mode. The tablespace inventory and BACKUP PLUGGABLE DATABASE app_pdb belong to the recovery baseline. A backup job that has only been scheduled still has its completion, validation, and recovery test ahead of it.

Quiz

1. Why is the first opening of a newly seed-created PDB read/write?

2. What does RESTRICTED = YES mean?

3. Which check exercises the new local user's actual client login route?

4. Will SAVE STATE make lost PDB data files recoverable?

5. Which claim is supported by a scheduled backup job that has not yet run?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...