DROP PLUGGABLE DATABASE ... KEEP DATAFILES releases an unplugged source's registration while retaining its permanent data files. The retained set supports the planned transfer or the agreed fallback. Its storage needs a named owner, an exact inventory, and a recorded condition for eventual release.
Oracle DBA Lesson 55A — Keep Transfer Files After Dropping
This lesson starts after successful unplugging. It focuses on the source registration postcheck and retained-file reconciliation. Follow the earlier transfer procedure for closing, unplugging, compatibility checking, and plugging.
What the command changes
| Asset | Result of the source drop with KEEP DATAFILES |
|---|---|
| Source PDB registration | Removed from the source CDB |
| Control-file references to that PDB and its data files | Removed |
| Permanent data files | Retained at their existing locations |
| Source tempfiles | Deleted |
| Existing archived redo and RMAN backup pieces | Retained; governed by separate policy |
KEEP DATAFILES is Oracle's default, but writing the clause makes the intended file disposition explicit. Oracle requires the source PDB to be unplugged. A PDB that is merely MOUNTED has not necessarily met this requirement. See DROP PLUGGABLE DATABASE for the syntax, privileges, file behavior, and snapshot-copy restriction, and removing a PDB for the closed and unplugged lifecycle.
The operation commits a structural change. SQL ROLLBACK cannot restore the source registration. Retaining files supports a separately rehearsed replug or recovery procedure.
Conditions for the worked example
The examples use Oracle Database 19c, a single-instance Linux CDB, and SQL*Plus. Record the actual Release Update, edition, storage type, and lab identity. These are unexecuted teaching examples, not captured database output. Use only the instructor's disposable transfer source LABPDB_MOVE.
Before the example, the instructor must establish all of these conditions:
LABPDB_MOVEwas the intended traditional, full-copy PDB. It has been closed and successfully unplugged into the approved XML descriptor.- The database unique name, PDB name, immutable GUID, PDB ID, original file locations, and permanent and tempfile classifications have been recorded. Keep the pre-unplug inventory and the file manifest and checksums captured after unplugging completed. Unplugging changes file headers.
- The XML descriptor and every required permanent file are available and protected together. The reviewed transfer plan and target compatibility checks have been accepted. Final application acceptance of a target created from this set is a later retention-release condition.
- No application session, service route, job, or business dependency relies on reopening this source. Preserve the service and job routing plan and the agreed fallback before removing registration.
- A compatible replug or dropped-PDB recovery drill has succeeded in an isolated environment. Required backups, archived redo, control-file and root recovery dependencies and, when applicable, encryption keys remain accessible outside the failure domain being tested.
- The retained set has a custodian, protected location, capacity allocation, release condition, and escalation contact. A directory name alone does not establish independent recovery storage.
Snapshot-copy PDBs and source PDBs with dependent snapshot copies are excluded. Oracle requires INCLUDING DATAFILES for a snapshot-copy PDB. Snapshot-copy dependencies also restrict unplugging and dropping the source. Application roots, seeds, proxies, refreshable PDBs, RAC, Data Guard, and encrypted-transfer procedures are outside this simple lab. If encryption exists, use the release-specific approved key and keystore procedure before proceeding. A descriptor and files alone are insufficient for an encrypted fallback.
The traditional PDB drop must be issued in CDB$ROOT, authenticated AS SYSDBA or AS SYSOPER. The administrative privilege must be commonly granted, or locally granted in both the root and the target PDB. The examples use the instructor's designated AS SYSDBA lab administrator. The documented SYSOPER permission to drop does not by itself establish permission to read every inventory view. Provide an appropriately authorized reader for these checks when using that alternative. Query errors or incomplete visibility are stop conditions.
Verify the exact source
In the established source-root SQL*Plus connection:
SHOW USER
SHOW CON_NAME
SELECT db_unique_name FROM v$database;
SELECT pdb_name, pdb_id, status, RAWTOHEX(guid) AS guid
FROM dba_pdbs
WHERE pdb_name = 'LABPDB_MOVE';
SELECT name, con_id, open_mode, RAWTOHEX(guid) AS guid
FROM v$pdbs
WHERE name = 'LABPDB_MOVE';SHOW is a SQL*Plus command. The queries read Oracle metadata. Match the returned database unique name to the transfer record, and match the PDB's name and GUID to the recorded source. RAWTOHEX presents the raw GUID as hexadecimal text for comparison. A matching name alone is insufficient if a previous PDB with that name was replaced.
DBA_PDBS.STATUS must be UNPLUGGED. V$PDBS.OPEN_MODE should be MOUNTED following unplugging. These fields answer different questions: the first records the completed lifecycle step, and the second reports the current instance's open mode. An absent row, an unexpected GUID, the wrong database or root, another status, or a failed query means the precondition has not been verified. Do not issue the drop under those conditions. See DBA_PDBS, V$PDBS, and V$DATABASE.
Keep the file evidence available after registration ends
The authoritative comparison set is the previously captured, reviewed manifest and the completed-unplug descriptor and file set. Do not depend on querying CDB_DATA_FILES for a complete inventory of a closed or unplugged PDB. If additional control-file crosschecks are needed before the drop, the designated root administrator can use:
SELECT f.con_id, f.file#, f.name
FROM v$datafile f
JOIN v$pdbs p ON p.con_id = f.con_id
WHERE p.name = 'LABPDB_MOVE'
ORDER BY f.file#;
SELECT t.con_id, t.file#, t.name
FROM v$tempfile t
JOIN v$pdbs p ON p.con_id = t.con_id
WHERE p.name = 'LABPDB_MOVE'
ORDER BY t.file#;V$DATAFILE describes data files from the control file. V$TEMPFILE describes tempfiles. CON_ID associates each file with its container. Reconcile these metadata lists with the earlier inventory, descriptor, and storage-level evidence. If any item is unresolved, investigate before the drop. These queries report registered names. They do not validate file contents or prove that a physical copy is usable. The root account must have the required view access and visibility. See V$DATAFILE and V$TEMPFILE.
Keep permanent and temporary records separate. For permanent files, record the exact path or ASM file identity, size, completed-unplug checksum where supported, protected copy, and storage owner. Keep the descriptor's location and its mapping to those files. Record actual tempfile names for the expected removal check. Only the storage custodian uses the instructor-approved filesystem or ASM inspection tools. This exercise supplies no OS deletion command.
Release source registration and verify the result
After the identity, file evidence, and fallback conditions above are satisfied, the source-root administrator's scoped command is:
DROP PLUGGABLE DATABASE labpdb_move KEEP DATAFILES;The name selects the recorded source. KEEP DATAFILES retains its permanent files, while the source tempfiles are removed. The source entry and its control-file references are removed. For a traditional transfer, dropping the source is part of completing removal before replugging. Follow the accepted transfer workflow for the destination.
Immediately recheck the same source database and root:
SHOW CON_NAME
SELECT db_unique_name FROM v$database;
SELECT pdb_name FROM dba_pdbs
WHERE pdb_name = 'LABPDB_MOVE';
SELECT name FROM v$pdbs
WHERE name = 'LABPDB_MOVE';Both filtered target inventories should be empty after a successful drop. That is the expected postcondition, not an observation from this authored example. Preserve the actual command result and query outputs in the lab record. An unexpected target row requires investigation.
Then reconcile physical storage against the saved manifest:
| Check | Evidence to retain |
|---|---|
| Each permanent file | Exact recorded file exists; size and checksum match the completed-unplug baseline where available |
| Descriptor | Protected, readable, and associated with the same completed file set |
| Source tempfiles | Expected removal checked for the exact recorded names |
| Retained-set ownership | Named custodian and protected location remain assigned |
| Other PDBs | Previously recorded identities remain present; agreed health and client checks succeed |
| Recovery dependencies | Required independent copies, keys, archived redo, and backups remain accessible |
Absence from the source dictionary describes registration. The retained physical files are still assigned to the transfer and fallback record. If a file is missing, an unexpected tempfile remains, or another PDB's checks change, stop and escalate with the captured evidence. Do not infer that unregistered files can be reused, overwritten, recursively deleted, or removed to make a postcheck look correct.
Decide when the retained set can be released
Write a retention record before the source drop and update it after the checks:
| Field | Example decision to resolve in the lab |
|---|---|
| Owner | Named transfer custodian, with an escalation contact |
| Identity | Source database unique name, PDB name and GUID, and the completed file manifest |
| Required use | Preserved source for transfer or compatible fallback |
| Release condition | Final target application acceptance plus a verified target backup and agreed recovery evidence |
| Review point | Date and time for reviewing the condition and unresolved issues |
| Fallback | Rehearsed compatible replug or the specific dropped-PDB recovery runbook |
The review date prompts a decision. It is not automatic permission to erase the set. Keep the permanent files protected while their transfer or fallback use is required. If the destination uses NOCOPY, those existing files become its live files. If it uses MOVE, file locations change. Update ownership and inventory accordingly. A protected independent source set requires the planned COPY workflow and verified separate locations, as described in CREATE PLUGGABLE DATABASE.
Archived redo and existing RMAN backups survive this drop. Their retention and eventual removal are separate recovery-policy operations. This lab performs no backup or archived-log deletion. Record dependencies of other PDBs and the CDB before any later retention maintenance.
Practice, pass criterion, and cleanup
In the instructor's isolated transfer lab, have a second person review the exact source database, name, and GUID, the completed-unplug evidence, the manifest, and the tested fallback. Perform the scoped source drop only after that review. Capture the actual source inventory postchecks and reconcile every retained permanent file and removed tempfile. Record the owner and release condition using the table above.
Pass when the intended source registration is absent, every retained permanent file and descriptor is accounted for, tempfile removal is explained, other PDBs' agreed checks pass, and the transfer and fallback evidence remains available. Mark the final target acceptance items pending until they are actually observed.
Cleanup for this part is evidence handoff and continued protection of the retained set. A later instructor-approved disposition follows the recorded release condition and current ownership. There is no broad directory cleanup.
For cancellation, follow the already rehearsed compatible replug procedure using the preserved descriptor and complete file set in an appropriate CDB, then verify integration, services, application checks, and recovery readiness. Replugging has its own compatibility, privileges, capacity, file-handling, and key requirements. Preserve an independent copy when the recovery plan requires one.
If the agreed fallback is RMAN recovery of a dropped PDB, use that specific tested procedure. Oracle's 19c guide requires suitable backups of the control file, root, and dropped PDB, a chosen recovery point, the required redo and keys, and an auxiliary location as applicable. A root common-user connection uses SYSDBA or SYSBACKUP. Recovery and OPEN RESETLOGS introduce consequences that the existing drill must address. See recovering a dropped PDB. The structural drop is not reversed with SQL ROLLBACK.
Recap
After a successful unplug, drop the source registration with KEEP DATAFILES only when the identity, file evidence, and fallback are already satisfied. The drop removes the source catalog entry and its tempfiles, and it keeps the permanent data files. Reconcile those files and the descriptor with the saved manifest, name a custodian and a release condition, and reverse the drop only with the rehearsed replug or the specific dropped-PDB recovery procedure.
Quiz
1. Can KEEP DATAFILES generally drop a PDB that is still open?
2. Which source files does Oracle remove when the verified unplugged PDB is dropped with KEEP DATAFILES?
3. The source target row is absent after the drop. What is the useful next action?
4. Which condition supports releasing the protected fallback set in this example?
5. How should the administrator plan a reversal after source registration is dropped?
Names, commands, and expected results in this lesson are unexecuted teaching examples. Run the practice on the instructor's disposable source LABPDB_MOVE in the designated Oracle Database 19c lab, confirm the exact release update, and record what you actually observe.
No comments:
Post a Comment