Oracle DBA Lesson 50A — Preserve a PDB for Transfer

Unplugging prepares a pluggable database (PDB) for a planned transfer or archive. The practical question is what remains usable, what files must survive, and how service can be restored if the transfer is cancelled. Closing, unplugging, and dropping have separate effects.

Oracle DBA Lesson 50A — Preserve a PDB for Transfer

Action	Application access	Source catalog	Files and recovery
CLOSE IMMEDIATE	Existing sessions end; uncommitted work is rolled back	PDB remains registered; open mode becomes MOUNTED	Database files remain; a closed, normally registered PDB can be opened using the appropriate privileges
UNPLUG INTO ...xml	PDB stays unavailable	Source entry remains with status UNPLUGGED	Oracle writes a descriptor and updates data-file headers; permanent files are kept separately
UNPLUG INTO ...pdb	PDB stays unavailable	Source entry remains with status UNPLUGGED	A compressed archive contains the descriptor and associated files
A separate DROP ... KEEP DATAFILES	Source PDB is removed	Source references are removed	Permanent data files remain; the source tempfile is deleted

An unplugged PDB supports DROP PLUGGABLE DATABASE as its source operation. Returning it to service uses a documented replug or tested restore procedure. A normal OPEN is not a reversal of successful unplugging. See removing a PDB and DBA_PDBS, which explain these states.

Conditions before starting

Use an instructor-designated, disposable Oracle Database 19c, single-instance, primary CDB on Linux. Record its exact release update, edition, platform, and licensed features. The only PDB named by this exercise is LABPDB_MOVE: an ordinary PDB that has already been opened at least once and whose disposable ownership is verified. It is separate from the starter LABPDB. Snapshot copies, application containers, RAC, standby databases, and cross-platform conversion are outside this exercise.

The main example assumes no TDE-encrypted data, no TDE master keys to transport, and no required PDB keystore. Have the designated administrator establish that eligibility; an application query or an empty encrypted-tablespace query alone is not a complete key inventory. If encryption or a required wallet is discovered, stop this simple branch and use the applicable key procedure below.

Before any change, agree the application outage, stop dependent sessions and jobs, capture an application baseline, and rehearse the fallback in the disposable lab. Keep a usable pre-change backup, required control-file and recovery metadata, redo, and keys where applicable, with evidence from an actual restore drill. A filesystem copy, descriptor, or checksum is evidence about a file set; recovery readiness also needs the rehearsed recovery procedure.

The authorized common administrator connects to the source CDB$ROOT using AS SYSDBA or AS SYSOPER. For this ordinary root exercise, the applicable administrative privilege must be granted commonly, or locally in both root and LABPDB_MOVE. CLOSE can use other administrative connection modes, but those do not automatically confer unplug and drop authority. Dictionary queries below require the corresponding authorized view access. See ALTER PLUGGABLE DATABASE prerequisites.

The instructor must supply a protected database-server directory such as /lab/transfer, writable by the database software owner and with enough verified capacity. The pathname in UNPLUG INTO is on the server; it is unrelated to the SQL*Plus client's working directory. Allocate additional space for protected file copies and, for the alternative archive workflow, compression, extraction, and target files. Protect the descriptor, file copies, archive, hashes, and recovery records from modification and unauthorized access. Use unused output names and retention locations approved for the exact exercise.

Capture identity and an inventory while the PDB is still available

Connect using the instructor-provided source-root service and approved authentication process. Verify the connection before each mutating step:

SHOW USER
SHOW CON_NAME

SELECT name, db_unique_name, dbid, database_role
FROM v$database;

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

SHOW is a SQL*Plus command. The queries identify the database and PDB; the database unique name distinguishes the source from a later destination. Save the actual source DB_UNIQUE_NAME, DBID, PDB name, CON_ID, GUID, and initial open mode. Continue only when they match the approved source identity. A missing or mismatched PDB row is a stop condition. See V$DATABASE and V$PDBS.

Inventory the permanent and temporary files separately:

SELECT con_id, file#, name, bytes
FROM v$datafile
WHERE con_id =
  (SELECT con_id FROM v$pdbs WHERE name = 'LABPDB_MOVE')
ORDER BY file#;

SELECT con_id, file#, name, bytes
FROM v$tempfile
WHERE con_id =
  (SELECT con_id FROM v$pdbs WHERE name = 'LABPDB_MOVE')
ORDER BY file#;

These control-file and dynamic views support the inventory even when later PDB dictionary visibility is limited. Save exact names, sizes, and storage type using the lab's actual results. Compare this inventory with the descriptor after unplugging. ASM or OMF files need the instructor's supported storage-copy method; shell-copy examples for ordinary filesystem files are not a substitute for it. See V$DATAFILE and V$TEMPFILE.

Capture committed application checks through LABPDB_MOVE's service before closing, after writes have been stopped for the agreed comparison interval. With the course schema provisioned in this disposable PDB, useful read-only checks include:

SELECT COUNT(*) AS employee_count
FROM course_owner.employees;

SELECT employee_id, employee_name, department_id
FROM course_owner.employees
WHERE employee_id IN (101, 102)
ORDER BY employee_id;

SELECT COUNT(*) AS order_count
FROM course_owner.orders;

Use actual committed keys present in the lab; no particular counts or values are asserted here. Retain the baseline for later application validation. Confirm services, job disposition, and restart and saved-state controls with the administrator so automatic service or job startup cannot undermine the outage or fallback plan.

Close, then write the XML descriptor

From the verified source root, using the stated administrative connection:

ALTER PLUGGABLE DATABASE labpdb_move CLOSE IMMEDIATE;

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

Successful closing leaves the PDB MOUNTED. IMMEDIATE disconnects its users and rolls back uncommitted work. It may wait for that rollback to finish. The CDB instance and other PDBs continue operating; this statement names just LABPDB_MOVE. The application outage begins with closing and lasts until service is deliberately restored through the chosen workflow. Review the actual result before proceeding. See SQL*Plus SHUTDOWN and immediate shutdown behavior.

ALTER PLUGGABLE DATABASE labpdb_move
  UNPLUG INTO '/lab/transfer/labpdb_move.xml';

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

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

The expected documented result after successful unplugging is a retained DBA_PDBS row with STATUS = 'UNPLUGGED', while V$PDBS.OPEN_MODE remains MOUNTED. These are expected conditions to verify, not output captured from an execution. Save actual messages and errors. If unplugging fails, inspect the actual state and diagnostic evidence before selecting recovery; do not assume either completion or safe reopening.

The XML file carries identity and file metadata for the plug operation. The application data resides in the permanent database files. Copying the XML by itself leaves the transfer incomplete. Oracle also modifies file headers during unplugging, so preserve the successfully unplugged file set rather than an earlier pre-unplug copy.

Preserve the completed transfer set

After successful unplugging, retain the exact XML and every permanent file it describes. Use the instructor-approved storage method to create protected, independent copies while the source remains unplugged and cannot change those files through normal application access. Record original-to-preserved paths, file sizes, completion time, and ownership and access rights. Compare the copies against checksums captured from the completed unplugged originals.

For a confirmed ordinary filesystem file, a read-only integrity example is:

sha256sum /lab/source/labpdb_move/users01.dbf
sha256sum /lab/retained/labpdb_move/users01.dbf
sha256sum /lab/transfer/labpdb_move.xml

Replace these fictional names with independently verified inventory paths. Run these outside SQL*Plus using an approved account with read access; record both actual outputs. Repeat for every permanent file and compare like-for-like copies. Matching checksums support byte integrity at that time; the chosen replug or restore drill provides operational recovery evidence. Copies on the same failing storage do not supply an independent recovery location.

Keep temporary-file names and sizes in a separate inventory. The subsequent source DROP KEEP DATAFILES deletes the source tempfile. Plan suitable target and recovery temporary-file creation and reuse with the documented plug procedure; retain its capacity and collision checks. The permanent-file preservation criterion applies to the descriptor-listed permanent files, rather than treating a tempfile as durable application data.

Keep source identity, baseline, descriptor, post-unplug hashes, copy locations, actual status evidence, and recovery and retention responsibility together in protected storage. This stage stops with the source catalog entry retained and status UNPLUGGED. Destination compatibility and target creation and opening have separate acceptance checks.

The .pdb archive alternative and encryption

As an alternative from an eligible, closed, normally registered PDB, choose an archive output instead of XML:

ALTER PLUGGABLE DATABASE labpdb_move
  UNPLUG INTO '/lab/transfer/labpdb_move.pdb';

Choose one unplug format for the exercise. Once XML unplugging has succeeded, the source is already UNPLUGGED; the archive statement is not an additional supported step on that same source entry. Rehearse an alternative format in a separate disposable run.

The compressed .pdb archive packages the descriptor and PDB files, including the applicable wallet file where documented. Validate and protect the archive, and retain independent recovery evidence. When it is used to plug in a PDB, Oracle extracts its files into the archive's directory, so plan permissions, capacity, and file-name collisions. A filename extension does not establish confidentiality or compatibility. See SQL unplug clauses and plugging an unplugged PDB.

For an encrypted transfer, select the exact documented keystore mode, RU, and platform procedure before either unplug format:

• United mode: the documented ENCRYPT USING transport_secret workflow requires the source root keystore to be open, exports this PDB's master keys encrypted with the transport secret, and needs the relevant key-management privilege, including SYSKM for the unplug encryption clause. The destination uses the corresponding DECRYPT USING procedure and available target keystore, then completes the documented key, open, rekey, and keystore-backup steps. The transport secret protects exported keys; treat data files and archives as sensitive files throughout. Older separate export and import procedures have additional requirements and are a different branch.
• Isolated mode: use the PDB's own keystore/wallet handling. The documented isolated workflow does not require the same ENCRYPT USING clause; XML transfer requires the associated wallet handling, while the archive can carry the wallet file. The target still requires correct wallet access, credentials, mode-specific integration, and key checks.
• External keystores: arrange documented key availability and transfer with the designated key administrator; a local archive alone cannot establish external-key access.

Do not paste a real key password or transport secret into these notes. The encrypted branches require a specific approved procedure; the unencrypted example above does not execute or validate them. See united mode unplug/plug and isolated mode unplug/plug.

Source metadata removal and cancellation

The later source metadata operation is:

-- Separate operation, after identity, preservation and compatibility review:
DROP PLUGGABLE DATABASE labpdb_move KEEP DATAFILES;

This line explains the operation; it is not part of this preservation-stage exercise. The exact source connection, identity recheck, drop execution, and destination COPY procedure belong to the transfer commissioning stage. KEEP DATAFILES requires an unplugged PDB, retains permanent files, deletes its tempfile, and removes source references. DROP cannot be rolled back. Archived redo and existing backups are retained until separately maintained. See DROP PLUGGABLE DATABASE, which states these consequences.

For the documented transfer taught here, verify the protected post-unplug set and recovery plan, review destination compatibility, then separately remove the unplugged source entry with KEEP DATAFILES before destination creation. Oracle's SQL unplug reference requires dropping before plugging into the same or another CDB. Retain the original permanent files and immutable copies through target validation and the agreed retention and fallback period. Target sign-off governs final file retirement; it is not a reason to teach source DROP after target creation in this workflow.

If the transfer is cancelled before unplugging and the PDB is simply closed in its normal registered state, the authorized administrator can restore its prior open and service state according to the outage plan. If unplugging has succeeded, use the rehearsed source replug or restore plan: it includes verified identity, removal of the unplugged catalog entry where required, protected file copies, compatibility, and correctly scoped creation and opening. Restoration needs the correct backup metadata, redo, and keys for the selected recovery point. Preserve untouched fallback copies; replug and open change the working files.

Never run two active PDBs against the same physical file set. Do not remove a validated destination, replace shared CDB files, or improvise an OS deletion as cancellation cleanup. If restoration is unavailable, keep the PDB unavailable, preserve evidence, and escalate to the instructor responsible for the lab.

Independent practice and pass criterion

Using the approved disposable run, record the actual version and RU, source identity, original open mode, privilege scope, file inventory, application baseline, and tested recovery reference. Perform the approved close and XML-unplug procedure. Explain why application access stopped, why the source still has a row, and why the descriptor needs permanent files.

Pass this part when the actual source status is UNPLUGGED, the protected descriptor and every descriptor-listed permanent file are accounted for, post-unplug checksums match the preserved copies, and the responsible instructor has accepted the rehearsed fallback and retention plan. List tempfile handling separately. These are practical acceptance conditions to establish; no such lab execution is claimed here.

For this stage's cleanup, leave the preserved transfer set protected and the source entry unplugged. No OS file deletion, source DROP, destination creation, or reopening is required by this practice. If the instructor cancels the exercise, carry out the pre-agreed replug, restore, or reset procedure and record the actual identity, service, data, and tempfile postchecks. Remove only exercise-owned copies after recovery and retention responsibility are signed off.

Recap

UNPLUG leaves the source dictionary entry. The practical sequence is an agreed outage, successful unplugging, a protected completed file set, verified recovery evidence, and a separate metadata-removal decision.

Quiz

1. What happens to uncommitted work during a successful CLOSE IMMEDIATE?

It is committed automatically It is rolled back while existing sessions are disconnected It is stored inside the XML descriptor It becomes a new backup

2. What must accompany an XML descriptor for this transfer?

Only the SQL*Plus history An empty directory with the same name The descriptor-listed permanent file set, with required keys where applicable The destination's alert log instead of data files

3. Which source state is expected after successful unplugging?

No source catalog row exists UNPLUGGED status with open mode MOUNTED NORMAL status with the application still writing All source files have been deleted

4. When should hashes for the preserved unplugged permanent files be captured?

Only before unplugging While normal application writes continue From the completed unplugged originals, then compared with protected copies Only after deleting the originals

5. What does later DROP ... KEEP DATAFILES retain and remove?

It retains permanent files while removing source metadata and the source tempfile It retains the source row while deleting every permanent file It restores the source to READ WRITE It rolls back unplugging without another procedure

These are Oracle 19c teaching examples checked against official documentation. Paths, names, and SQL are examples rather than captured execution. No live database was connected or changed while preparing these notes. Use the designated lab's actual release, privileges, output, and tested recovery procedure for evidence.

AIOps Pro

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