Oracle DBA Lesson 55B — Retire a Disposable PDB

Retiring a completed test copy is a file-ownership decision. You identify the exact pluggable database, confirm that its live files can be released, and check the effects of the drop. A separate recovery and retention decision governs backups and archived redo.

Oracle DBA Lesson 55B — Retire a Disposable PDB

The worked example uses LABPDB_DISCARD, a separately provisioned disposable full copy. LABPDB is the source and stays available. All commands and expected observations here are unexecuted teaching examples. They are not captured database output.

What INCLUDING DATAFILES removes

DROP PLUGGABLE DATABASE ... INCLUDING DATAFILES removes the target's registration and its associated permanent data files and tempfiles. Oracle updates the CDB control file to remove the target references. The target must be mounted or unplugged. The statement cannot be reversed by SQL ROLLBACK.

The earlier transfer workflow uses KEEP DATAFILES for an unplugged source whose permanent files remain owned by a transfer or fallback plan. Here, the verified disposable target's live file set is being retired. Backups and archived redo remain subject to separate retention and deletion operations. See DROP PLUGGABLE DATABASE and removing a PDB. Those topics define these effects.

Conditions for the destructive exercise

Use an instructor-designated Oracle Database 19c, single-instance Linux CDB, and SQL*Plus. Record the actual release and RU, edition, storage arrangement, and administrative identity. Complete a successful recovery rehearsal before performing deletion. A VM snapshot alone is insufficient proof that the Oracle recovery dependencies are usable.

The instructor must establish all of these conditions:

• LABPDB_DISCARD was created solely for this exercise as a full copy with its own complete live file set. Its provisioning record, database identity, PDB name, GUID, and exact files agree. It is a traditional PDB, not the seed, an application root, a proxy, or a refreshable clone.
• LABPDB remains separately identified. Target and source file paths have been inventoried and checked against their actual storage ownership. Separate directory names alone do not establish independent recovery storage.
• No snapshot-copy descendants or other clone or snapshot dependencies rely on the target. Creation and history evidence, together with storage-owner evidence, establish its full-copy provenance. Snapshot-copy workflows are excluded. A source with snapshot-copy clones has restrictions on dropping, and sparse clones have additional storage dependencies. See CREATE PLUGGABLE DATABASE for snapshot copy and cloning a PDB for those separate cases.
• Application services, connection pools, jobs, monitoring, database links, backup jobs, and external consumers have been reviewed. The scratch target has no business users or unresolved dependencies. Even a full copy may inherit jobs or outbound references. The lab isolates those routes before opening it for inspection.
• A tested recovery plan has usable independent artifacts, required root and control-file information, required redo, and any encryption keys. The plan records the recovery target and the expected restored data. It can survive loss of the target live files.
• A second person checks the database unique name, target name and GUID, the exact permanent and tempfile manifest, the source distinction, the dependency review, and the actual recovery proof. Agree the isolated outage and the retention owner and endpoint before execution.

For a traditional PDB drop, connect in CDB$ROOT, authenticated AS SYSDBA or AS SYSOPER. The administrative privilege must be granted commonly, or locally in both the root and the target. The same root and target grant scope applies to this root-issued close operation. The worked inspection assumes an authorized common SYSDBA administrator. SYSOPER authentication alone does not promise access to every dictionary view. The instructor supplies any additional read privileges and container visibility needed for the inventory.

These are lab eligibility requirements. Teaching examples do not authorize connecting to a production database or performing deletion.

Identify the target before its outage

Run the read-only identity checks from the intended root:

SHOW USER
SHOW CON_NAME

SELECT db_unique_name FROM v$database;

SELECT pdb_id, pdb_name, RAWTOHEX(guid) AS pdb_guid, status
FROM dba_pdbs
WHERE pdb_name = 'LABPDB_DISCARD';

SELECT con_id, name, RAWTOHEX(guid) AS pdb_guid,
       open_mode, restricted
FROM v$pdbs
WHERE name IN ('LABPDB', 'LABPDB_DISCARD')
ORDER BY name;

SHOW commands belong to SQL*Plus. The SQL queries inspect database metadata. DB_UNIQUE_NAME identifies the CDB. PDB_NAME identifies the target within it. The GUID is a globally unique immutable identifier assigned at creation. RAWTOHEX prints the raw value as readable hexadecimal. Match it to the recorded creation identity rather than accepting a familiar name alone. PDB_ID and CON_ID link the current target to its file rows. Keep that value with the name and GUID, because a later container could reuse an identifier.

Stop if the root, database unique name, target row, GUID, state, or expected source differs. A missing or unreadable row requires investigation before any mutation. The source and target should appear as separate identities. See DBA_PDBS, V$PDBS, and V$DATABASE. Those views document these fields.

Capture permanent and temporary file ownership

Capture the manifest before closing the target, while the controlled target is open in a mode and under an account that provide complete dictionary visibility. From root, CDB_* queries return data according to privileges and CONTAINER_DATA, and they normally report open, unrestricted PDBs. A closed or restricted target can make an incomplete result misleading. Resolve any gap using the instructor's validated inventory procedure. Do not proceed with an empty or unexplained manifest.

These two queries associate files with the target identity:

SELECT p.pdb_name, RAWTOHEX(p.guid) AS pdb_guid,
       f.con_id, f.file_id, f.tablespace_name, f.file_name
FROM cdb_data_files f
JOIN dba_pdbs p ON p.pdb_id = f.con_id
WHERE p.pdb_name = 'LABPDB_DISCARD'
ORDER BY f.file_id;

SELECT p.pdb_name, RAWTOHEX(p.guid) AS pdb_guid,
       f.con_id, f.file_id, f.tablespace_name, f.file_name
FROM cdb_temp_files f
JOIN dba_pdbs p ON p.pdb_id = f.con_id
WHERE p.pdb_name = 'LABPDB_DISCARD'
ORDER BY f.file_id;

The first query inventories permanent data files, including the target's system and application tablespaces. The second inventories its temporary files. Record every actual complete FILE_NAME with file type, file ID, tablespace, container ID, and GUID. Verify that the physical storage owner can inspect these exact locations, whether filesystem or ASM. Encrypted, offline, or inaccessible files can require additional authorized preparation. Establish complete coverage rather than silently omitting them.

Capture the same manifests for LABPDB and the intended unaffected PDBs by changing the name filter in these read-only queries only. Compare complete path sets. Confirm that none of the disposable target's recorded files is adopted by an active PDB or a retained transfer set.

See CDB_* views, DBA_DATA_FILES, and DBA_TEMP_FILES. Those topics document the view rules and fields. The CDB versions add CON_ID to their DBA counterparts.

Review dependencies and retain a baseline

The second-person review includes the approved creation record and storage dependency history, not just one query. Obtain service-owner and job-owner inventories, including external connection aliases, pools, scheduled scripts, and monitoring. Resolve any remaining target references before retirement.

For a root service inventory under the appropriate catalog access:

SELECT name, network_name, pdb
FROM dba_services
WHERE pdb = 'LABPDB_DISCARD'
ORDER BY name;

This reports documented service associations. It does not list every external client or prove that a service is actively used. Review configuration owned by the listener or service manager as well. An automatically associated default service is accounted for in retirement. This lab requires no application consumers or unresolved custom service dependencies. See DBA_SERVICES, which has the columns of ALL_SERVICES.

In an authorized connection to the disposable target, a job-owner review can inspect:

SHOW CON_NAME

SELECT owner, job_name, enabled, state
FROM dba_scheduler_jobs
ORDER BY owner, job_name;

Include DBMS_JOB and external schedulers if they are present in the actual lab. A dictionary list supplements the owner review. Resolve copied jobs and outbound actions through the instructor's containment plan. Return to the reviewed root connection and repeat the identity checks before any close or drop command. See DBA_SCHEDULER_JOBS for the administrative scope. The field definitions are in ALL_SCHEDULER_JOBS.

Record the source's initial open and restricted state, service access, and a small read-only data baseline. The later availability test compares against that actual baseline. Stop if target identification, dependency ownership, the complete inventory, or tested recovery remains uncertain.

Close, recheck, then drop

The following statements are separated by an intentional review point. Execute them only after the approved lab conditions above are met. This is not a batch script to paste into an arbitrary environment.

-- Reviewed root connection and exact target identity:
ALTER PLUGGABLE DATABASE labpdb_discard CLOSE IMMEDIATE;

CLOSE IMMEDIATE disconnects target sessions, ends their executing work, and rolls back uncommitted transactions. It places the target in MOUNTED mode. A long rollback can take time. It names one target and leaves the other PDBs outside that close command. See ALTER PLUGGABLE DATABASE, which equates this operation to PDB shutdown immediate, and starting up and shutting down, which explains its transaction and session effects.

Recheck before proceeding:

SHOW CON_NAME
SELECT db_unique_name FROM v$database;

SELECT name, RAWTOHEX(guid) AS pdb_guid, open_mode
FROM v$pdbs
WHERE name = 'LABPDB_DISCARD';

Require the same recorded CDB, target, and GUID, and MOUNTED open mode. If the instructor initially supplied an already mounted target, establish its complete manifest through the separately validated inspection process. Omit an unnecessary close command rather than claiming to close an open target. The worked sequence assumes the target was open for inventory. Do not reopen solely to repair a failed drop without investigating the failure.

After the review point:

DROP PLUGGABLE DATABASE labpdb_discard INCLUDING DATAFILES;

The named target's registration and associated live files are removed. Preserve the actual statement result, alert messages, time, and identity. If an error or a partial observation occurs, stop and investigate the exact error and inventory before attempting additional cleanup. SQL ROLLBACK does not recreate the dropped target or its files.

Verify the retirement independently

From the same reviewed root:

SELECT name, RAWTOHEX(guid) AS pdb_guid, open_mode
FROM v$pdbs
WHERE name = 'LABPDB_DISCARD';

SELECT pdb_name, RAWTOHEX(guid) AS pdb_guid
FROM dba_pdbs
WHERE pdb_name = 'LABPDB_DISCARD';

SELECT name, RAWTOHEX(guid) AS pdb_guid, open_mode, restricted
FROM v$pdbs
WHERE name = 'LABPDB'
ORDER BY name;

After a successful drop, the first two filtered inventories should return no target row. Use the saved manifest to check removal of each exact permanent and temporary path with authorized filesystem or ASM inspection. Check through the correct storage access method. A missing mount or insufficient permissions can otherwise look like file absence. A new empty CDB file-view result alone is insufficient physical evidence.

Compare LABPDB and the other intended PDB identities, states, and exact files with the captured baseline. Test the intended client services using the authorized baseline accounts, confirm their connected container, and perform the same read-only data check. An inventory row by itself leaves application availability untested.

If any target file remains, any unexpected file disappears, or another PDB loses expected access, record the discrepancy and stop. Never use recursive directory deletion, filename wildcards, or removal of a whole ASM location to force the expected result. The evidence is an exact-target comparison, not a claim that everything in a parent directory was disposable.

Recovery and retention

The retention record names its owner, protected artifact locations, usable recovery target, last successful rehearsal, and release condition. Preserve the exact target GUID and name, the CDB identity, the file manifest, the actual removal evidence, and the proved recovery procedure. Backups, keys, and control-file records must remain accessible outside the target file set and failure domain.

A dropped-PDB recovery is a specifically prepared operation. See recovering a dropped PDB. It requires backups of the control file, root, and dropped PDB, an earlier recovery target, appropriate root RMAN privileges, and applicable redo and keystore prerequisites. It uses RECOVER PLUGGABLE DATABASE with the chosen recovery endpoint, followed by OPEN RESETLOGS. Auxiliary storage may be required. Local and shared undo, and the actual encryption configuration, affect the rehearsed dependency set. Recovery abandons changes after its selected endpoint. A compatible, independently preserved full set can instead support a specifically rehearsed replug or reset plan. Preserve only artifacts proven sufficient for the selected method.

These notes do not provide an untested restore command to use after deletion. Follow the instructor's successful, recorded recovery runbook, and validate recovered data and client access before calling recovery complete. If no such rehearsal exists, do the non-destructive planning exercise below and leave deletion unexecuted.

The agreed retention endpoint is a business and recovery acceptance decision. It is not an automatic backup purge triggered by the PDB drop. Review the required recovery window and any consumers of archived logs with the recovery owner. RMAN and FRA policies may later make retained material eligible for deletion, so ensure they support the promised endpoint. See RMAN backup concepts and maintaining RMAN backups. Those topics describe the separate retention and deletion rules. Do not change retention or delete backup pieces as cleanup for this exercise.

Independent practice and pass criteria

First produce a retirement proposal using an instructor-provided actual scratch-target inventory. Fill in the database identity, name and GUID, permanent and tempfile manifest, affected consumers, source baseline, required privileges, tested recovery evidence, outage, and retention endpoint. Explain the expected close and drop effects, and distinguish logical, physical, and recovery-retention evidence. Have a second person challenge the selected target and the recovery proof.

For the separately authorized destructive lab, follow the reviewed close, recheck, and drop sequence. Record the actual results, compare every manifest path, and test the source and the other intended services. A practical pass requires all of the following observations:

1. The intended disposable target's metadata is absent after a successful drop.
2. Every recorded target permanent and tempfile path has the expected removal status under valid storage access.
3. LABPDB and the other intended PDBs retain their identities, files, and agreed client availability.
4. Required independent recovery artifacts remain accessible and are covered by the recorded retention plan.
5. The report includes the actual version and RU, administrator and container, command result, postchecks, discrepancies, and cleanup status. No unexecuted check is marked passed.

Cleanup ends with the retired target and an archived evidence record. Keep recovery artifacts to the agreed endpoint. An instructor reset can provision a new independent scratch copy for another exercise. Record its new identity and manifest. Recovery of the old target uses the rehearsed independent method. Stop on any mismatch and hand the exact evidence to the instructor.

Recap

Identify the disposable target by CDB identity, name, GUID, and exact file manifest. Close only that target, then drop it with INCLUDING DATAFILES so its registration, permanent data files, and tempfiles are removed. Confirm that the metadata row is gone and that every recorded path is absent under valid storage access. Leave LABPDB and the other intended PDBs available, and keep backups, archived redo, and the rehearsed recovery plan under a separate retention decision.

Quiz

1. Which evidence most precisely identifies the disposable target before deletion?

Its display name alone. CDB database unique name, target name and GUID, and linked exact file manifest. A parent directory containing several PDBs. The number of open application sessions.

2. What does CLOSE IMMEDIATE do in the worked target outage?

Wait for every user to voluntarily disconnect while leaving work active. Delete all backups. Disconnect target sessions, roll back uncommitted work, and leave it mounted. Drop every PDB in the CDB.

3. Which file sets does INCLUDING DATAFILES delete for the verified target?

Only temporary files. Only permanent files. Every file in the storage parent directory. Its associated permanent data files and tempfiles.

4. Does INCLUDING DATAFILES also erase all RMAN backup pieces?

No; backup and archived-redo retention are separate operations. Yes; it purges every recovery artifact. Yes, when the target was a full copy. Only after a SQL ROLLBACK.

5. The target inventory row is absent but an exact recorded file appears to remain. What should happen next?

Remove its entire parent directory. Mark the exercise passed because the SQL row is gone. Stop and investigate the exact file, ownership, storage access, and drop evidence. Run ROLLBACK to recover the file set.

Names, paths, commands, and expected observations in this lesson are unexecuted teaching examples. They are not captured database output. Run the practice on the instructor's disposable target LABPDB_DISCARD in the designated Oracle Database 19c lab, confirm the exact release and RU, and record what you actually observe.

AIOps Pro

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