Oracle DBA Lesson 54A — Why PDB Logins Fail

An application login failure can involve the PDB's availability, its connection restriction, or the service the client uses. Read these facts before deciding to change the PDB's state. A read-only PDB can support a successful query connection while rejecting user changes later in the application workflow.

Oracle DBA Lesson 54A — Why PDB Logins Fail

Read mode and restriction together

From the intended CDB's root, inspect only the disposable target:

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

Fact	Interpretation for routine application availability
OPEN_MODE = MOUNTED	The PDB is closed to ordinary application work. Administrative connection paths support the permitted management tasks.
OPEN_MODE = READ ONLY	Queries are allowed; user changes are blocked.
OPEN_MODE = READ WRITE	Queries and user changes are allowed, subject to the account's privileges.
RESTRICTED = YES	Connections require RESTRICTED SESSION in this PDB as well as ordinary login authorization.
RESTRICTED = NO	The PDB connection restriction is disabled. Account, service, and network conditions still determine whether a client connects.
RESTRICTED = NULL	Preserve the returned value and interpret it with the PDB's state. Treat it as its own reported value.

RESTRICTED is a separate column from OPEN_MODE. READ WRITE with RESTRICTED=YES permits appropriately authorized work. The three modes above cover routine availability. Oracle also documents MIGRATE for upgrade work, which is outside this exercise. See V$PDBS.

Conditions for the state-changing lab

The baseline is Oracle Database 19c on Linux, a single-instance primary CDB open read/write, and SQL*Plus. Record the exact edition, RU, and instance. Use the instructor's existing disposable LABPDB_STATE, separate from the shared LABPDB. The exercise changes only this target. No statement using ALL, and no CDB shutdown, upgrade mode, or standby operation, belongs in this lab.

The instructor supplies a common administrative identity authenticated AS SYSDBA, with the required privilege commonly granted, or granted in both the root and the target, as documented. Other documented administrative connection privileges may be used through an explicitly reviewed runbook. Changing PDB state requires exercising the administrative privilege at connect time. Saving or discarding state also needs ALTER DATABASE in the root. Inspection requires access to the referenced views and visibility of the target container. See ALTER PLUGGABLE DATABASE prerequisites.

Before the outage, the instructor and learner must record:

• The exact CDB, instance, root connection, target name, CON_ID, DBID and GUID, and the current mode and restriction.
• The existing saved-state row for this instance, or its verified absence. Record the intended restart mode and restriction separately from the current mode.
• Current target sessions, application traffic and jobs, service configuration, and active-service state. Agree who drains traffic and who checks that uncommitted work may be rolled back.
• A tested, isolated recovery or reset procedure and its protected backup or snapshot. A snapshot's existence alone is insufficient evidence of a successful Oracle restore.
• An approved downtime window and the exact restoration commands. Stop if identity, privilege, current or saved policy, session visibility, or recovery is uncertain.

The worked exercise assumes the recorded original current state is READ WRITE / NO. It leaves the saved policy unchanged unless the separate optional policy practice is authorized. For another original state, prepare the matching restoration branch below before changing it.

All commands, names, and expected observations here are documentation-based teaching examples. They have not been executed against a database. Capture actual output from the designated lab. Do not report the expected state as an observed result.

Capture the starting evidence

Authenticate through the instructor's approved root connection, using a credential prompt or a wallet. Confirm the connected identity and container:

SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV','DB_NAME') AS db_name,
       SYS_CONTEXT('USERENV','CON_NAME') AS container_name,
       SYS_CONTEXT('USERENV','SESSION_USER') AS session_user
FROM dual;

SELECT instance_name, status
FROM v$instance;

SELECT name, con_id, dbid, RAWTOHEX(guid) AS pdb_guid,
       open_mode, restricted
FROM v$pdbs
WHERE name = 'LABPDB_STATE';

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

SHOW is a SQL*Plus command. SELECT runs in the database. Require CDB$ROOT and the approved target identity. Store non-secret evidence with the observation time. The saved-state view uses STATE, rather than OPEN_MODE. Retain its actual value and the instructor-validated corresponding restart intent. Its INSTANCE_NAME identifies which instance the saved policy belongs to. See DBA_PDB_SAVED_STATES.

Bind the observed target container ID and inspect its current sessions from the root:

VARIABLE target_con_id NUMBER

BEGIN
  SELECT con_id INTO :target_con_id
  FROM v$pdbs
  WHERE name = 'LABPDB_STATE';
END;
/

PRINT target_con_id

SELECT con_id, sid, serial#, username, status
FROM v$session
WHERE con_id = :target_con_id;

The PL/SQL block reads the target's ID into a client bind. The slash executes that block. Stop on any lookup error. Session rows identify current connections visible to this observer. SID and SERIAL# distinguish session identities. Include administrative sessions in the review. An idle session can retain uncommitted work. Session activity status alone is an insufficient outage decision. See V$SESSION.

Close and reopen the target

After the isolated-target preflight and the traffic-draining decision, remain in the root:

ALTER PLUGGABLE DATABASE labpdb_state CLOSE IMMEDIATE;

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

ALTER PLUGGABLE DATABASE labpdb_state OPEN READ WRITE RESTRICTED;

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

CLOSE IMMEDIATE closes this PDB and disconnects its sessions. Uncommitted work is rolled back, which can take time. Committed work remains subject to normal durability and recovery. The expected intermediate mode is MOUNTED. The CDB instance continues running. Reopening with READ WRITE RESTRICTED is expected to report READ WRITE / YES. Compare those predictions with actual output before proceeding. See PDB CLOSE and OPEN semantics, immediate shutdown behavior, and SQL*Plus PDB shutdown scope.

If closing or opening fails, retain the full error and the current state. The instructor follows the prepared recovery or reset plan. For an unexpected restricted open, review the alert and the relevant plug-in violations, and follow their documented actions before restoring unrestricted access. See PDB compatibility checks on open.

Check who may connect and work

Predict the result for two pre-provisioned identities: an ordinary target-local account with CREATE SESSION alone, and an authorized maintenance connection with the applicable restricted access. The instructor supplies the actual usernames, service aliases, and permitted connection methods. Preserve their existing grants. Granting the application extra maintenance privileges is outside this exercise.

Try the designated ordinary account through its existing lab service using a prompt, for example this fictional alias and account:

CONNECT state_reader@LAB_STATE_APP

During restriction, the account lacks the PDB privilege needed to connect. Record the actual error, then distinguish account rejection from any service or listener rejection using the evidence already collected.

For the permitted administrative test, use a separate authorized root session authenticated AS SYSDBA and switch only to the target:

ALTER SESSION SET CONTAINER = LABPDB_STATE;
SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV','CON_NAME') AS connected_container
FROM dual;

An open restricted PDB admits the relevant authorized connections. Users connected normally need CREATE SESSION and RESTRICTED SESSION in the PDB. Authenticated administrative access follows its documented privilege path. Permitted sessions can still perform work allowed by the mode and the privileges. Inspect target sessions again from the root before maintenance. Restriction is an access condition. Maintenance readiness also requires the planned session and transaction check. See connecting to a PDB and restricted access.

Current state, restart intent, and client readiness

V$PDBS describes the current instance state. SAVE STATE preserves the PDB's mode for a CDB restart, and its saved metadata includes restriction. DISCARD STATE removes the saved choice, and a normal CDB startup leaves the PDB mounted. These operations leave current availability to the open and close workflow.

The following are alternatives for a separately approved, target-only policy practice:

ALTER PLUGGABLE DATABASE labpdb_state SAVE STATE;

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

ALTER PLUGGABLE DATABASE labpdb_state DISCARD STATE;

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

Inspect the actual saved mode and restriction, and restore the recorded original policy. A CDB restart is unnecessary for this short practice and requires a separate whole-instance window. Existing startup triggers, service management, or automation may reopen the PDB after startup. Record those controls when predicting availability. In particular, Oracle Restart or SRVCTL can open a closed PDB when starting its service. See preserving and discarding open mode, and service management.

Check the existing intended service's PDB association and active state from the authorized observer:

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

SELECT name, network_name, con_name, blocked
FROM v$active_services
WHERE con_name = 'LABPDB_STATE';

ALL_SERVICES describes configuration. V$ACTIVE_SERVICES identifies active services and whether they are blocked from new connections. Then have the service owner verify listener registration and test the actual client path. Check the connected container, the account, and the required application operation. Applications use user-defined services associated with the intended PDB. The PDB's default service is for administrative use. Service and configuration changes are outside this exercise. See ALL_SERVICES, V$ACTIVE_SERVICES, and PDB services.

Restore the original condition

End only the disposable test connections, return the administrative session to the verified root, and restore the recorded current state. For the worked starting condition READ WRITE / NO:

ALTER SESSION SET CONTAINER = CDB$ROOT;
SHOW CON_NAME

ALTER PLUGGABLE DATABASE labpdb_state CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE labpdb_state OPEN READ WRITE;

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

If the target is already mounted, omit the redundant close. Use this table to prepare the reopening command for a different recorded original state:

Original current condition	After target-only close
MOUNTED	Leave the target mounted.
READ ONLY / NO	ALTER PLUGGABLE DATABASE labpdb_state OPEN READ ONLY;
READ ONLY / YES	ALTER PLUGGABLE DATABASE labpdb_state OPEN READ ONLY RESTRICTED;
READ WRITE / NO	ALTER PLUGGABLE DATABASE labpdb_state OPEN READ WRITE;
READ WRITE / YES	ALTER PLUGGABLE DATABASE labpdb_state OPEN READ WRITE RESTRICTED;

Core practice does not change the saved policy. Compare the final saved record with the starting evidence. If policy practice changed it, restore both the saved policy and the current state:

1. With no original saved row for this instance, issue target-only DISCARD STATE and verify its absence.
2. With an original saved policy, place the target in its recorded restart mode and restriction using the corresponding branch above, then issue target-only SAVE STATE. Verify the resulting saved row against the recorded policy. For a deliberately saved mounted policy, keep it mounted while saving. Resolve the meaning of STATE with the instructor before choosing a command.
3. If that saved policy differs from the original current state, close and reopen to the recorded current state without saving again. This preserves the restored policy while returning current availability to its starting condition.

Repeat the state, policy, and service and client checks. The service owner restores the recorded intended service availability using the appropriate existing management procedure if needed. Capture the final actual results, the cleanup outcome, and any unresolved errors. State changes and rollback effects require explicit restoration. SQL ROLLBACK cannot restore a previous open mode, reconnect sessions, or recover deliberately lost uncommitted work.

Independent practice

Before changing anything, explain a hypothetical READ WRITE / YES result: which account may log in, which work its privileges permit, and which evidence would identify current sessions. Then complete the approved disposable-target workflow and the two connection predictions using actual lab results. Verify restoration of the current state, the saved policy, and client readiness.

Pass when you correctly identify the mode, the restriction, and the current sessions, explain the disruption and rollback, distinguish restart intent from present availability, and record successful cleanup. If a required connection or recovery lab is unavailable, complete the prediction exercise and record the unexecuted practical work.

Recap

Read OPEN_MODE and RESTRICTED together before changing the disposable PDB. Close and reopen only that target, then test which accounts can connect. Keep the current open state separate from the saved restart policy, confirm the intended service and client path, and restore the recorded state, policy, and readiness.

Quiz

1. Is RESTRICTED a fourth OPEN_MODE equivalent to READ ONLY?

Yes, both report the same state. No; restriction controls connection eligibility, while read-only mode controls user changes. Yes, but only in the root. No; restriction controls redo-log size.

2. What is a consequence of target-only CLOSE IMMEDIATE?

All PDBs are renamed. The CDB instance always stops. Target sessions disconnect and uncommitted work rolls back. Every transaction is committed automatically.

3. A target is READ WRITE / YES. Which normal connection needs the appropriate additional privilege?

A user with CREATE SESSION needs RESTRICTED SESSION in the target PDB. A user needs only CREATE TABLE in the root. Every client connects automatically. Changing the client alias grants restricted access.

4. Which view answers what restart policy is saved for a PDB and instance?

V$ACTIVE_SERVICES V$SESSION ALL_SERVICES DBA_PDB_SAVED_STATES

5. The PDB reports READ WRITE / NO. What completes application-readiness checking?

Assume every client reaches it. Verify the intended service and listener, and test a client connection to the correct container. Save state for every PDB. Grant the application administrative access.

All commands, names, and expected observations in this lesson are documentation-based teaching examples. They have not been executed against a database. Capture actual output from the designated lab, and do not report the expected state as an observed result.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro