Oracle DBA Lesson 50C — Open a Copied PDB and Retain Recovery Files

A planned transfer carries an unplugged pluggable database into a destination container database. The practical goal is a target whose files, application access, and recovery arrangements are verified while the preserved source set remains available for a controlled fallback.

Oracle DBA Lesson 50C — Open a Copied PDB and Retain Recovery Files

The workflow is: preserve the unplugged set and test recovery, approve destination compatibility, remove the source registration with KEEP DATAFILES, plug a COPY into separate destination files, open and verify the target, establish its backup, then decide how long to retain the source set. Oracle 19c requires the source PDB to be dropped before plugging it into the same CDB or another CDB. Final target sign-off controls release of the retained files, and it occurs after target commissioning. See ALTER PLUGGABLE DATABASE.

Choose ownership of the files

Choice	Target uses	Effect on preserved source files
COPY	Copies in a distinct destination	Permanent source files remain available.
NOCOPY	Existing files at their current locations	Those files become the target's live files.
MOVE	Files moved to the destination	Originals can disappear; across mounts Oracle may copy and then delete them.

Use COPY for this exercise. The XML describes the source files, and the destination's Oracle Managed Files (OMF) settings determine placement of the target copies. Source-location mapping and target placement answer different questions. The example assumes the XML accurately names the accessible preserved files. If they moved, stop and review SOURCE_FILE_NAME_CONVERT or SOURCE_FILE_DIRECTORY before creation. Neither clause chooses the destination of the new copies. See CREATE PLUGGABLE DATABASE.

Separate paths must resolve to separate physical files. Check aliases, shared storage, and symbolic links with the instructor. Different directory text alone is insufficient. Never run two active PDBs against one physical file set. Preserved files and a cloned or copied application are useful transfer assets. The target also needs a genuine Oracle backup and a tested recovery procedure.

Lab conditions and authority

Use an explicitly designated disposable Oracle Database 19c single-instance Linux lab: two ordinary primary CDBs, the same platform and endianness, instructor-approved release updates, options, and character sets, and an unencrypted source with no TDE keystore requirement. TDE, application containers, RAC, standby integration, cross-platform conversion, and upgrades require their own validated procedures. Stop this simple branch when those conditions do not hold. Verify licensing entitlement and capacity for both databases.

Before the source DROP, the instructor must approve the unplugged XML and the separately preserved permanent files, their completeness and readability, the committed COURSE_OWNER baseline, the destination compatibility report, and a rehearsed source replug or restore route. Retain source backups, necessary redo, control-file and recovery metadata, and any applicable keys outside the tested failure domain. The operation is inside an agreed outage. Ordinary business writes remain stopped through commissioning and sign-off. Record ownership and retention dates. A VM snapshot alone does not establish Oracle recovery capability.

The fictional PDB name on both sides is LABPDB_MOVE. Its CDB names differ. The instructor supplies SRCROOT and DSTROOT root aliases and DSTMOVE for the target service. Verify their actual endpoints. If the default or copied user-defined service could collide across CDBs sharing a listener, resolve the naming and routing before creation. Use separate isolated listeners for this example. A service alias need not equal the PDB name.

Required privileges are separate:

• Target CREATE: common CREATE PLUGGABLE DATABASE in destination CDB$ROOT. The destination CDB must be open READ WRITE.
• OPEN and CLOSE from root: authenticate AS SYSDBA, SYSOPER, SYSBACKUP, or SYSDG with the relevant administrative privilege granted commonly, or locally in root and the affected PDB. This exercise uses a designated common administrator AS SYSDBA.
• Source or target DROP: authenticate AS SYSDBA or SYSOPER from the correct root, with common authority or local authority in both root and the affected PDB. Possession of CREATE PLUGGABLE DATABASE alone is insufficient.
• Parameter containment: appropriate ALTER SYSTEM authority in root and, for the target's own settings, in that PDB. The instructor owns this isolated CDB-wide change and its restoration.
• Read-only evidence: explicitly provisioned access to the named dictionary and dynamic views and container visibility. Application verification uses the existing local COURSE_OWNER account with CREATE SESSION and its own starter tables, not a broad administrative application grant.
• Backup: an RMAN root connection by a common user with SYSBACKUP or SYSDBA and the approved storage and recovery configuration.

See CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE, and DROP PLUGGABLE DATABASE for these privilege contexts.

Replace aliases, identifiers, and paths only through the instructor's checked lab procedure. Capture actual results, not the expected observations described below.

Release the unplugged source registration

Complete the destination identity, name, OMF, and capacity preflight and the containment below before this irreversible source action, then repeat the relevant checks before target CREATE. Use a separate SOURCE root session, authenticated with the administrative authority above. SQL*Plus prompts for the password with an instructor-provisioned account:

sqlplus -L c##course_admin@SRCROOT as sysdba

Before mutating anything:

SHOW USER
SHOW CON_NAME
SELECT db_unique_name, dbid, database_role, open_mode
FROM v$database;

SELECT pdb_name, dbid, RAWTOHEX(guid) AS guid, status
FROM dba_pdbs
WHERE pdb_name = 'LABPDB_MOVE';

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

Compare the source DB_UNIQUE_NAME and DBID, and the LABPDB_MOVE GUID, against the approved source record. The container must be CDB$ROOT, and the intended source PDB must report UNPLUGGED and MOUNTED. Confirm the preserved permanent-file inventory, the XML, and the rehearsed recovery route. Stop on any mismatch, missing row, or unapproved condition. SHOW is a SQL*Plus command. SELECT runs in the database. See V$PDBS.

Only after this source-identity and preservation and compatibility approval:

DROP PLUGGABLE DATABASE labpdb_move KEEP DATAFILES;

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

The postcheck should find no source catalog row. Verify that the permanent source files remain through the instructor's OS and storage inventory. KEEP DATAFILES retains the permanent data files, deletes the tempfile, and removes the source control-file references. It is DDL and cannot be rolled back. Backups and archived logs are retained unless they are separately deleted through their authorized retention procedure. Do not substitute INCLUDING DATAFILES on the source. See DROP PLUGGABLE DATABASE.

The source UNPLUGGED registration is not a working service. Its supported database operation is DROP. Returning it to service requires a controlled replug or restore. The retained set is never discarded merely because the source registration has been removed.

Contain the target before first opening

Move to the separate DESTINATION root session and verify it independently:

sqlplus -L c##course_admin@DSTROOT as sysdba

SHOW USER
SHOW CON_NAME
SELECT db_unique_name, dbid, database_role, open_mode
FROM v$database;
SELECT name, con_id FROM v$pdbs WHERE name = 'LABPDB_MOVE';
SHOW PARAMETER db_create_file_dest
SHOW PARAMETER job_queue_processes
SHOW PARAMETER spfile

Require the approved destination identity and a READ WRITE primary root. LABPDB_MOVE must be absent. Confirm the OMF base and storage capacity, and source-file readability by the destination Oracle account. The destination base is physically distinct from the preserved source set. Record the actual root JOB_QUEUE_PROCESSES and SPFILE settings before changing them.

The instructor must already have blocked outbound application routes and quarantined the default and copied client services. Permit only the designated administrator and test-client routes and approved transfer-file access. Review copied jobs, links, credentials, wallet files, stored secrets, and user-defined service routing while the controls remain active. OPEN RESTRICTED limits user sessions. Job suppression and network quarantine are separate controls.

In this dedicated isolated destination CDB, before CREATE or the first OPEN:

ALTER SYSTEM SET job_queue_processes = 0
  SCOPE=MEMORY CONTAINER=CURRENT;
SHOW PARAMETER job_queue_processes

Verify zero in root and that no relevant work is already running. Root zero suppresses Scheduler and DBMS_JOB jobs in every destination PDB, regardless of its copied override. It also affects Scheduler-based AutoTask and automatic materialized-view refreshes. The instructor must accept this whole-CDB effect. The MEMORY setting lasts until CDB shutdown. A restart is a stop and recontainment condition before opening. The XML can carry PDB parameter overrides, so root containment precedes integration. See JOB_QUEUE_PROCESSES and ALTER SYSTEM.

Create independent destination files

The reviewed descriptor and permanent files are accessible at the XML's recorded source paths. The source registration has already been dropped with KEEP DATAFILES. In the checked destination root:

CREATE PLUGGABLE DATABASE labpdb_move
  USING '/lab/transfer/labpdb_move.xml'
  COPY;

USING identifies the descriptor. COPY preserves the source permanent files and associates separate copies with the destination PDB. With the approved destination OMF settings, Oracle generates its destination file names. Plan new destination temporary files. The source DROP removed the old tempfile. Validate the actual new temporary-file inventory rather than reusing the preserved source path. TEMPFILE REUSE can format an existing tempfile and is outside this preservation-first exercise.

The expected initial state is MOUNTED/NEW:

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

Ordinary unplug and plug carries the PDB identity. Compare its GUID and DBID with the transfer record. CON_ID identifies the PDB inside the destination and can change. COPY is a file-handling choice. AS CLONE is a separate creation choice that generates a new DBID and GUID in Oracle's documented reuse case. It is not added to this ordinary transfer. If creation fails, retain the full errors and the alert-log evidence. A target left UNUSABLE requires its own exact-target DROP before a retry. Do not proceed to application access. See CREATE PLUGGABLE DATABASE for COPY, MOVE, NOCOPY, and AS CLONE.

Complete integration and review health

With root jobs still at zero and the network and client quarantine active:

ALTER PLUGGABLE DATABASE labpdb_move
  OPEN READ WRITE RESTRICTED;

SELECT name, con_id, open_mode, restricted
FROM v$pdbs
WHERE name = 'LABPDB_MOVE';
SELECT pdb_name, status
FROM dba_pdbs
WHERE pdb_name = 'LABPDB_MOVE';
SELECT time, name, type, status, message, action
FROM pdb_plug_in_violations
WHERE name = 'LABPDB_MOVE'
ORDER BY time, line;

The first READ WRITE opening completes integration. After successful integration the status becomes NORMAL. RESTRICTED=YES is expected for this deliberately restricted opening and permits only users holding RESTRICTED SESSION in the target. Preserve the actual errors, review the alert and opening records, and remediate and recheck unresolved integration blockers. Review warnings and their documented action individually. A historical resolved row differs from a current pending finding. Neither NORMAL nor a compatibility result alone completes application acceptance. See plugging in an unplugged PDB, DBA_PDBS, and PDB_PLUG_IN_VIOLATIONS.

Record the destination file paths while the target is still restricted. Use root control-file views with authorized visibility. CDB_DATA_FILES visibility also depends on dictionary access and container visibility, and it may omit restricted or closed PDBs.

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

Compare every target path with the approved preserved-source inventory and the destination base. Confirm that the required files and usable tempfiles exist, that status is appropriate, and that none resolve to preserved source files. See V$DATAFILE and V$TEMPFILE.

Prove the application through its service

Before restoring the root job setting, establish the target's own zero setting while root zero remains active. From the authorized administrative connection in LABPDB_MOVE:

ALTER SESSION SET CONTAINER = labpdb_move;
ALTER SYSTEM SET job_queue_processes = 0
  SCOPE=BOTH CONTAINER=CURRENT;
SHOW PARAMETER job_queue_processes
ALTER SESSION SET CONTAINER = CDB$ROOT;

This requires approved ALTER SYSTEM scope and persistent parameter support. Verify the actual memory and persistent values and the reopen behavior. Keep root zero through the first application checks. Review and disable or replace production-directed jobs and links, using their authorized owners and documented procedures. Changing the PDB's parameter or service spelling does not rewrite embedded application endpoints.

An ordinary COURSE_OWNER login will fail during a RESTRICTED opening unless that account has the required privilege. For this lab, after the health and integration review, the instructor permits only the designated test-client route, keeps jobs at zero and egress quarantine active, and reopens the target without RESTRICTED:

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

Verify READ WRITE, NORMAL, and RESTRICTED=NO before the ordinary service test. This reopening is an agreed lab interruption. CLOSE IMMEDIATE rolls back uncommitted work and disconnects sessions. Do not add broad privileges to the application account merely to bypass an unexpected restricted state.

From the approved test client, use the instructor-provided target alias:

sqlplus -L course_owner@DSTMOVE

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','SERVICE_NAME') AS service_name,
       SYS_CONTEXT('USERENV','SESSION_USER') AS session_user
FROM dual;

SELECT COUNT(*) AS employees_count FROM employees;
SELECT employee_id, employee_name, department_id, salary
FROM employees WHERE employee_id IN (101,102,103) ORDER BY employee_id;
SELECT COUNT(*) AS orders_count FROM orders;
SELECT order_id, customer_id, status, amount
FROM orders WHERE order_id IN (1001,1002,1003) ORDER BY order_id;
SELECT marker_id, marker_text, created_at
FROM recovery_marker ORDER BY marker_id;

Use the committed baseline captured before the source close and unplug, including the named object inventory and the chosen key and value checks. The sample key lists are placeholders for the instructor's actual captured rows. Compare the actual counts, keys, and values. Investigate missing objects, values, or service misrouting. A matching count alone can hide different rows. This exercise's target application checks are read-only, and business writes remain stopped. Record an application acceptance result separately from integration health.

Back up the target and decide retention

Use RMAN connected to the verified destination root as the designated common SYSBACKUP or SYSDBA account. The target CDB's ARCHIVELOG and local-undo configuration, the instructor-approved RMAN channels and storage, the recovery catalog if used, the required archive retention, the control-file and SPFILE metadata, and the keys must be established before an online backup. One example RMAN command is:

BACKUP PLUGGABLE DATABASE labpdb_move TAG 'LABPDB_MOVE_TARGET';

This backs up that PDB's files. An individual PDB backup by itself excludes archived redo logs. The approved recovery procedure must separately capture and retain the required redo and CDB recovery metadata, and it must protect required encryption keys when they apply. Verify backup completion, the pieces and catalog records, restoration responsibility, and a rehearsed recovery route. A failed or missing backup is a stop condition for final handoff. NOARCHIVELOG needs a separately approved consistent shutdown and backup procedure. It is outside this online example. See backing up PDBs.

Restore the exact previously recorded root parameter value only after the target's own persistent zero is verified and the copied integrations are approved. Verify the restore in root and the effective zero in the target. Keep the destination service and network quarantine until the instructor approves application access. Record which approved target jobs may run, and when. The instructor removes temporary containment only according to the agreed handoff, preserving the original configurations where appropriate.

Retain the source XML and permanent set until target application and backup sign-off, rehearsed fallback acceptance, and the retention owner authorize disposal. Source registration removal happened before target CREATE. Retained-file cleanup is a later decision. No retained-source deletion command is part of this exercise.

Fallback and exact-target cleanup

If commissioning fails, quarantine access, stop further target writes, preserve the errors and the actual evidence, and close the target under the authorized destination-root administrator. Use the already rehearsed source replug or restore plan. Replug requires valid XML and files, approved placement, compatibility, fresh tempfiles, and controlled opening. Restore requires the verified backup, redo, metadata, and keys procedure. Keep the failed target inactive before re-establishing the application source. Do not share physical files between active databases.

Returning to the preserved source restores its captured pre-transfer data point. If business writes were allowed at the target, reconcile their consequences and an agreed recovery point before fallback. Simply opening the old source set would lose those later target changes. Preserved source files do not roll back target business transactions.

For instructor-approved removal of only the target created by this exercise, reconnect independently to DSTROOT AS SYSDBA or SYSOPER. Verify the destination DB_UNIQUE_NAME and DBID, the exact LABPDB_MOVE GUID, and its recorded independent target file inventory. Confirm that the source permanent files, XML, and recovery assets remain protected, and obtain the lab owner's reset decision. Never select ALL PDBs or act from SRCROOT.

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

If this exact target is open, close it:

ALTER PLUGGABLE DATABASE labpdb_move CLOSE IMMEDIATE;

Require the exact target to be MOUNTED. Do not blindly rerun CLOSE for an already mounted or unusable target. After the identity, ownership, and retention checks:

DROP PLUGGABLE DATABASE labpdb_move INCLUDING DATAFILES;
SELECT name FROM v$pdbs WHERE name='LABPDB_MOVE';

INCLUDING DATAFILES irreversibly deletes the target permanent files and its tempfile. Verify that the target row and the target files are gone, that the retained source files and recovery assets survive, and that the designated service routes and temporary settings are restored under the lab reset procedure. Backups and archived logs remain subject to their own retention. An UNUSABLE target follows this same exact ownership verification before DROP. Preserve diagnostics before deletion. See DROP PLUGGABLE DATABASE.

Independent practice

Complete the transfer on the disposable lab after preservation and compatibility approval. Record the version and release update, both database identities, the PDB identity, the initial UNPLUGGED state, the source KEEP DATAFILES postchecks, the reviewed XML and source inventory, the destination COPY placement, the actual opening results, the plug-in findings, the data-file and tempfile inventory, the fresh COURSE_OWNER service connection, and the baseline comparison. Capture genuine backup evidence and the named recovery and retention responsibilities.

Pass when the application matches the agreed captured checks, its service reaches the intended destination, the target files are physically independent, current integration blockers are resolved, containment is verified, and the target backup and fallback evidence meets the instructor's acceptance criteria. Rehearse the approved failure route separately on a disposable copy. Finish with either accepted target retention or the exact-target removal above. Never delete the preserved source set as automatic cleanup.

Recap

Preserve the unplugged set, drop the source registration with KEEP DATAFILES, and plug a COPY into separate destination files. Open and verify the target under containment, prove the application through its service, and accept a target backup before deciding how long to retain the source files.

Quiz

1. Why select COPY for this recoverable transfer?

It automatically tests target recovery. It preserves source permanent files and creates separate target files. It makes both active databases share one file set. It deletes the source files before first opening.

2. In the ordinary Oracle 19c unplug and plug workflow, when is the source registration removed?

After deletion of all retained source files. Only when the target has run for a month. After preservation and compatibility approval, and before destination CREATE. Never. UNPLUGGED is a usable service state.

3. What happens to the source tempfile with DROP KEEP DATAFILES?

It is deleted, while permanent source data files are retained. It becomes the target's only backup. It is protected forever by KEEP DATAFILES. It changes into the XML descriptor.

4. Why keep job and network containment during a READ WRITE RESTRICTED first opening?

RESTRICTED rewrites all copied credentials. COPY drops Scheduler jobs automatically. Restricted sessions replace the target backup. RESTRICTED controls user sessions. Copied jobs and integrations need separate controls.

5. Which evidence supports final target handoff?

The existence of its XML file alone. The first successful OPEN alone. Correct target identity, files, and service, plus application baseline checks and completed backup and recovery acceptance. A matching count without the expected keys or service identity.

All SQL and paths in this lesson are teaching examples. No database commands were executed while preparing this lesson.

AIOps Pro

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