Oracle DBA Lesson 49B — Keep a Remote Clone Safe for Testing

A remote clone places a source application's database contents in another container database. This is useful when an application team needs a test copy on a separate database system. The new files belong to the target, while copied application identities and integration settings can still refer to external systems. The practical task is to establish the remote source, contain the target before its first opening, and approve each integration before application access.

Oracle DBA Lesson 49B — Keep a Remote Clone Safe for Testing

The example uses Oracle Database 19c, SQL*Plus, two separate single-instance Linux CDBs, source LABPDB, target LABPDB_REMOTE, and a private destination-root database link named COURSE_SRC. These are teaching examples reviewed against documentation; no command or result in these notes represents an executed lab. Record actual output in the designated disposable lab.

Where the link belongs

The clone command runs in the destination CDB$ROOT. COURSE_SRC belongs to the destination account that issues the command and connects towards the source. This lesson's link connects directly to source LABPDB as an instructor-provisioned local account, COURSE_CLONE_SOURCE.

Identity and scope	Required authority for this example
Destination cloning account, destination root	Commonly granted CREATE PLUGGABLE DATABASE; ownership of the private link; separately granted inspection and containment privileges
COURSE_CLONE_SOURCE, source LABPDB	CREATE SESSION and locally granted CREATE PLUGGABLE DATABASE on that PDB
Destination containment administrator	Commonly granted ALTER SYSTEM for destination-root parameter changes; target-PDB ALTER SYSTEM, required container access and inspection authority
Designated destination lifecycle administrator	For root OPEN/CLOSE, authenticated AS SYSDBA, AS SYSOPER, AS SYSBACKUP or AS SYSDG, with that privilege commonly granted or locally granted in both root and target; DROP requires AS SYSDBA or AS SYSOPER and the documented root/target grant scope

Oracle also permits a link connected as a common user to the source root, or as a common or local user directly to the source PDB. The documented source authority is CREATE PLUGGABLE DATABASE, granted commonly or locally on the source PDB, or SYSOPER. A source-root account must be common and have the appropriate connection and source-PDB authority. The local direct-PDB account used here avoids granting SYSOPER for this example. An account with the CREATE PLUGGABLE DATABASE source privilege has significant authority; make the grant temporary and narrowly scoped. See Remote clone prerequisites.

The instructor provisions the link through approved protected credential handling. Link creation requires CREATE DATABASE LINK in the destination root for its owner. Real passwords, wallet contents and production endpoints stay out of recordings and learner evidence. The private-link owner can inspect its definition and make a small connectivity check:

SHOW USER
SHOW CON_NAME

SELECT db_link, username, host
FROM user_db_links;

SELECT 1 AS link_response FROM dual@course_src;

The first two commands establish the destination account and root. HOST describes the Oracle Net connect string, which the instructor must resolve to the approved source service. A response to the final query shows that this connection can perform that query. Confirm source database, container and account separately in a source connection using the exact provisioned endpoint, and retain the instructor's link-to-endpoint mapping. A successful simple query checks connectivity; cloning also requires the documented source authority and compatibility. See Database-link views and CREATE DATABASE LINK.

Eligibility and compatibility

Before changing either lab, record its full 19c release/RU, edition, instance and database identity, the source PDB identity, and available entitlements. Confirm the target name is unused, there is an available PDB slot, and verified OMF storage has capacity for the complete copy and temporary/growth requirements. The 19c licensing table permits up to three user-created PDBs per CDB without the Oracle Multitenant option; check the actual offering, total PDB count and relevant installed features before the exercise. An installed option is inventory evidence, while entitlement comes from the applicable licence. See 19c licensing information.

Use two compatible 19c CDBs for this exercise, preferably the same approved RU. Review release and COMPATIBLE requirements and unresolved plug-in messages. The platforms must have the same endianness. Source database options must be the same as, or a subset of, destination options. If the destination database character set is not AL32UTF8, source and target character sets and national character sets must be compatible. The documented AL32UTF8 destination exception applies to ordinary PDBs; application-container character-set/name/version rules have additional requirements and are outside this standard-PDB example. See Remote clone compatibility.

The source remains open. A hot READ WRITE source requires source-CDB ARCHIVELOG and local undo. Keep required archived redo generated throughout cloning in an enabled, available archive destination until completion. If either mode requirement is absent, use the agreed source READ ONLY window from Lesson 49A. Source mode changes require their own authorised application window; this remote exercise does not prescribe a new mode-changing procedure. Network and storage throughput determine elapsed time, so measure this lab rather than assuming a universal duration.

Encryption keys and application secrets have different purposes

The simple SQL below is eligible only when the instructor verifies that the source has no TDE encrypted data and no configured keystore requiring a keystore clause. Otherwise the instructor must replace it with the documented procedure for the actual source/destination keystore modes and types, using protected secret handling. An auto-login keystore does not waive all clone keystore requirements.

For the documented united-mode remote clone between CDBs, Oracle transports the source master encryption keys to the clone. The Advanced Security Guide identifies the clone's KEYSTORE IDENTIFIED BY value as the destination CDB keystore password, requires the relevant root keystore to be open, and recommends matching keystore types to avoid errors. It also directs creation of a unique master key for the new clone with backup. Isolated-mode support and handling depend on the 19c RU and configuration; resolve that separate procedure before execution. Preserve keys and key backups required to read encrypted data or recover the clone. See United-mode remote clone and rekeying and Keystore clauses.

Application passwords, external-job credentials, API tokens, outbound-link credentials and client-authentication wallets require a separate test-use decision. Some are stored in copied database objects; others are external files or external secret-store entries referenced by copied configuration. A database clone is not an automatic sanitisation process and does not automatically copy every external file. Inventory both the copied definitions and the external dependencies, replace application secrets with test-only values, and retain required encryption/recovery keys under the authorised key-management policy.

Containment before CREATE and the first OPEN

Use a dedicated isolated destination CDB for the following root-wide job control. Before cloning, establish a verified network policy that permits only the controlled clone connection to source LABPDB and the required administration traffic. Block outbound application access to source/production databases, HTTP/email/file endpoints and external agents. Limit destination client access to the authorised reviewer, including access to the default service that Oracle will create. Network controls must cover every relevant route, including local/shared paths and hosts that can execute external work.

Confirm no destination jobs are already running, capture the original destination-root value, and apply the temporary job gate:

-- Dedicated destination CDB$ROOT; authorised containment administrator.
SHOW CON_NAME
SELECT name, value FROM v$parameter
WHERE name = 'job_queue_processes';

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

SELECT name, value FROM v$parameter
WHERE name = 'job_queue_processes';

Record the actual original value before changing it; do not assume a default. The recheck must show zero. In CDB root, zero prevents DBMS_JOB and Oracle Scheduler jobs from running in root and all its PDBs, regardless of PDB-level values. Materialized-view automatic refresh, AutoTask and other Scheduler/legacy-job features are also affected. This is why the destination must be dedicated and the whole-CDB interruption agreed. CONTAINER=CURRENT selects the root parameter scope; the parameter's root-zero rule supplies the whole-CDB job block. See JOB_QUEUE_PROCESSES.

SCOPE=MEMORY takes effect immediately and lasts until instance shutdown. It leaves the startup configuration unchanged. Keep the lab running through the review; if it restarts, retain network/client quarantine and reapply and verify the root gate before any clone reopening. Also inspect startup mechanisms and saved PDB states. A persistent change needs a separately agreed SPFILE/pfile and restoration procedure. Root zero is a gate for the documented job frameworks, while egress/client controls and review must address other application execution paths. An OPEN RESTRICTED limits user sessions to identities with RESTRICTED SESSION; use the parameter gate to control scheduled job execution. See ALTER SYSTEM scope and privileges and Restricted PDB opening.

Remote CREATE and controlled first opening

With containment active, the private link verified, source consistency conditions met, and OMF configured, issue this as the private-link owner in destination root:

CREATE PLUGGABLE DATABASE labpdb_remote
  FROM labpdb@course_src;

LABPDB_REMOTE is the target name. labpdb is the source PDB. @course_src resolves the source through this account's private link. The ordinary full clone copies source files into destination-managed target files. It carries local application users, their local grants, schema objects and data. Successful creation leaves the target mounted with catalog status NEW. The source remains in place; a hot source can keep operating.

If the source contains the user-defined service COURSE_APP, the instructor can instead use the reviewed mapping at creation time:

CREATE PLUGGABLE DATABASE labpdb_remote
  FROM labpdb@course_src
  SERVICE_NAME_CONVERT = ('COURSE_APP', 'COURSE_APP_TEST');

This is an alternative CREATE for that source-service condition. Execute one reviewed CREATE, and use the source service's actual name. SERVICE_NAME_CONVERT renames user-defined services. Oracle creates the default service with the new PDB's name, LABPDB_REMOTE; this clause cannot rename that default service. Verify names are collision-free across the relevant listener reach and that application aliases route only to the test target. See Service-name conversion.

Keep root jobs at zero, network containment active and application access quarantined. The designated lifecycle administrator, authenticated in destination root with the documented OPEN authority, performs the controlled first opening. The creation privilege and private-link ownership authorise creation; OPEN/CLOSE require the separate administrative authentication and grant scope in the table above. See ALTER PLUGGABLE DATABASE prerequisites.

ALTER PLUGGABLE DATABASE labpdb_remote OPEN READ WRITE RESTRICTED;

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

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

The first READ WRITE open completes integration. The intended controlled review state is OPEN_MODE=READ WRITE, RESTRICTED=YES, catalog STATUS=NORMAL. These are expected conditions to verify, not observed results. Review unresolved PDB_PLUG_IN_VIOLATIONS for this target, resolve errors and record a decision for each warning. If creation fails, inspect actual state and the alert log; an UNUSABLE target requires the documented removal procedure before reusing its name. Preserve containment while investigating. See Remote clone basic steps.

Review every copied integration

Inside the target, keep a PDB-level job gate before any restoration of the root gate:

ALTER SESSION SET CONTAINER = labpdb_remote;
SHOW CON_NAME
ALTER SYSTEM SET job_queue_processes = 0
  SCOPE = BOTH CONTAINER = CURRENT;
SELECT name, value FROM v$parameter
WHERE name = 'job_queue_processes';

This target-local setting is modifiable in a PDB. Confirm the approved database configuration supports the persistent setting and verify it after any controlled reopen/restart. Keep root zero until that check and the integration review are complete. The clone's inherited Scheduler job ENABLED attributes can remain TRUE while the global gate is zero; inventory those definitions rather than treating quiet execution as completed review.

SELECT owner, job_name, enabled, job_type,
       program_owner, program_name, job_action
FROM dba_scheduler_jobs
ORDER BY owner, job_name;

SELECT job, schema_user, broken, what
FROM dba_jobs;

SELECT owner, db_link, username, host
FROM dba_db_links;

SELECT owner, credential_name, username, enabled
FROM dba_credentials;

SELECT name, network_name, pdb
FROM dba_services;

Collect these reports only with authorised dictionary visibility and protect actions, destinations and credential usernames in private lab records. DBA_CREDENTIALS is the recommended 19c view; the older DBA_SCHEDULER_CREDENTIALS is deprecated. See Scheduler job columns, Credential inventory, and Service inventory.

For each item, record its owner, actual external dependencies, chosen action, reviewer and verification evidence:

Item	Concrete review and disposition
Scheduler and legacy jobs	Inspect job actions, referenced programs/chains, arguments, destinations, external agents and credentials. Disable production actions or replace them with approved test actions. Legacy DBMS_JOB definitions need their own owner-authorised disposition.
Outbound database links	Resolve connect strings to actual endpoints; remove source/production routes or replace them under approved test-only accounts. Review private and public links in the target and root separately.
Credentials, wallets and secrets	Inventory database credentials, application configuration and external secret files/stores. Disable or replace external credentials and client authentication; preserve encryption keys and recovery dependencies.
Services and clients	Confirm unique service/network names, listener advertisement and intended test alias. Quarantine the default and user-defined services until approval; release only approved application access.

For example, if the instructor's cloned fixture contains COURSE_OWNER.COURSE_EXPORT, the job owner or authorised administrator can disable that specific job inside LABPDB_REMOTE:

BEGIN
  DBMS_SCHEDULER.DISABLE('COURSE_OWNER.COURSE_EXPORT');
END;
/

SELECT owner, job_name, enabled
FROM dba_scheduler_jobs
WHERE owner = 'COURSE_OWNER' AND job_name = 'COURSE_EXPORT';

Verify the row reports ENABLED=FALSE. If the fixture is absent, record its absence and choose an actual instructor-approved copied job rather than claiming execution. Review every integration; one disabled job is one item in that review. Object ownership, object ALTER grants or authorised Scheduler administration determine who can disable a job. Avoid issuing a broad disable/drop loop across Oracle-maintained jobs or unrelated objects. See DBMS_SCHEDULER.DISABLE.

Common accounts synchronise with destination root

Local application identities and grants travel with their database objects. User-created common accounts have destination-root reconciliation rules: an account matching a destination common account receives that destination account's common grants; a source common account absent from destination root is dropped during synchronisation if it owns no PDB objects, or locked if it owns objects. Locally granted roles/privileges remain relevant. Review affected ownership, account state and privileges before application access. Keep unused accounts locked or follow a separately approved ownership-migration procedure; do not create matching common users solely to bypass the review. See After cloning a remote PDB.

Evidence, handoff and cleanup

Record destination database/instance identity and target name, container ID and GUID. Inventory target data files and temporary files, their actual locations, size and growth limits. Confirm the full clone's target files are independent from source files and reside in the approved target storage. In the target, compare application keys and row counts with the agreed quiet-point source baseline from Lesson 49A. Preserve the baseline's time and source-write conditions; an actively changing source after cloning is a different comparison point. Retain a completed target backup and its required redo, metadata and keys.

Examples of targeted checks, run in their labelled scope:

-- Destination root: target identity.
SELECT con_id, name, guid FROM v$pdbs
WHERE name = 'LABPDB_REMOTE';

-- Target PDB, authorised file observer.
SELECT file_id, tablespace_name, file_name FROM dba_data_files;
SELECT file_id, tablespace_name, file_name FROM dba_temp_files;

-- Target PDB, authorised application observer.
SELECT COUNT(*) AS employee_count FROM course_owner.employees;
SELECT employee_id, department_id FROM course_owner.employees
WHERE employee_id IN (101, 102);

Use actual existing keys from the instructor baseline when different from these example keys. File names identify the files being inventoried; correlate them with storage placement and ownership evidence. A filename directory alone is insufficient evidence of physical failure-domain independence. The reviewers must approve each outbound integration's disposition, the target-local job policy, service route and application evidence before removing client quarantine. Keep source/production egress blocked for the test environment.

When this ordinary full clone no longer needs its creation route, the private-link owner in destination root removes it:

DROP DATABASE LINK course_src;
SELECT db_link FROM user_db_links;

The source administrator then revokes only the temporary grants added for this clone and locks/drops the dedicated temporary source account when authorised and unused. Removing COURSE_SRC does not remove copied application links inside the target; their inventory and disposition remain separate. See DROP DATABASE LINK.

Return to destination root only after the target-local gate is verified and the root-wide hold can be released. Restore the recorded original root memory value, verify it, and retain the approved target-local zero while unreviewed jobs remain. There is no universal restoration number. Preserve network containment across any restart, and verify root/PDB parameter values and service access again. If the lab is finished, use the target-only removal procedure below; preserve source LABPDB, its files, grants unrelated to this exercise and recovery evidence. Restore the agreed dedicated-destination operating policy.

Remove only the disposable remote target

Dropping the target is irreversible DDL and deletes its target data files when INCLUDING DATAFILES is specified. Agree the lab teardown window and retain required evidence/backups first. Keep target clients stopped and network containment active. The designated lifecycle administrator connects to the verified destination root authenticated AS SYSDBA or AS SYSOPER, with that privilege either commonly granted or locally granted in both root and target. Confirm this is the disposable target using the original destination database identity, PDB GUID and recorded file inventory:

SHOW USER
SHOW CON_NAME
SELECT dbid, name, db_unique_name FROM v$database;
SELECT con_id, name, guid, open_mode FROM v$pdbs
WHERE name = 'LABPDB_REMOTE';

Stop if the destination or target differs from the recorded creation evidence. Agree the effect of disconnecting any remaining target sessions; CLOSE IMMEDIATE terminates them and rolls back unfinished transactions. Then run these operations against this named target only:

ALTER PLUGGABLE DATABASE labpdb_remote CLOSE IMMEDIATE;
SELECT name, open_mode FROM v$pdbs
WHERE name = 'LABPDB_REMOTE';
-- Proceed only after confirming MOUNTED and the exact target identity.
DROP PLUGGABLE DATABASE labpdb_remote INCLUDING DATAFILES;
SELECT name FROM v$pdbs WHERE name = 'LABPDB_REMOTE';

An already mounted target needs no additional CLOSE. The post-drop query should return no target row. Verify recorded target files were removed, retire only its test service/client configuration and exercise-created external assets under their owners' procedures, and record the exact root-setting restoration. Recheck source LABPDB in its own CDB: identity, agreed open mode, application marker/data and recovery evidence remain intact. Do not delete source files or shared keystore/backups as target cleanup. The INCLUDING DATAFILES path is appropriate for this disposable full clone; keep required backups separately. See DROP PLUGGABLE DATABASE prerequisites and file deletion and PDB CLOSE.

Oracle 19c DBCA provides a remote clone in silent mode. It is an alternative interface with its own documented assumptions and provisioning requirements; retain the same source, compatibility, capacity, containment and validation decisions. Use approved secrets handling instead of copying documentation's password-bearing example arguments. See DBCA remote clone example.

Independent practice

1. Obtain two authorised disposable 19c CDBs, exact identity/RU/edition records, a recovery/reset plan and a sufficient target slot/storage allocation. Have the instructor provision and document the private direct-PDB link and temporary source authority.
2. Write the source-consistency/redo-retention decision and compatibility evidence. Arrange an application quiet point when key/count comparison requires the fixed source baseline.
3. Establish and test the destination egress/client containment policy. Capture the original root job setting, confirm destination jobs are idle, set root zero and verify it before CREATE or first OPEN.
4. Execute one condition-appropriate CREATE, then perform the controlled READ WRITE RESTRICTED opening with containment active. Resolve target plug-in errors and record warnings.
5. Establish the persistent target-local job gate. Inventory jobs, links, credentials, secrets/wallet dependencies and services. Record and verify an approved disposition for every outbound integration before application access.
6. Capture target identity, independent-file and application key/count evidence; complete the target backup baseline. Test an approved test service route and verify the fresh connection's database, account and container.
7. Drop the private creation link, retire temporary source authority, restore the exact agreed destination-root setting when eligible, and verify the target policy. Complete the authorised target-only cleanup and source-preservation checks.

Pass when a reviewer can trace source and destination identities, link direction and authority, before-open containment evidence, independent target files, expected application data, a completed backup, and every integration's verified disposition. Record unavailable privileges/features or tests as pending. Application access remains quarantined until the pass conditions are satisfied.

Recap

Establish the remote source, contain the target before its first opening, and approve each integration before application access. A reviewer should be able to trace source and destination identities, link direction and authority, before-open containment evidence, independent target files, expected application data, a completed backup, and every integration's verified disposition. Application access remains quarantined until those pass conditions are satisfied.

Quiz

1. Where is COURSE_SRC created for the remote CREATE in this lesson?

In the target account's destination CDB$ROOT, connecting towards source LABPDB Only inside the source PDB, connecting back to destination root In the source operating-system shell In the new PDB after its first open

2. Which source-account setup supports this direct-PDB example?

CREATE SESSION alone A local account in source LABPDB with CREATE SESSION and CREATE PLUGGABLE DATABASE on that PDB A local account connected to source root Any account with an application table grant

3. Does copying a PDB automatically remove stored application secrets and scheduled jobs?

Yes, because it has a new PDB name Yes, when OMF creates different files No; review copied configuration and its external dependencies under containment Only if source is read write

4. What is the effect of destination-root JOB_QUEUE_PROCESSES=0?

It blocks only root jobs and leaves PDB Scheduler jobs active It prevents Scheduler and DBMS_JOB jobs across that CDB, regardless of PDB values It deletes copied jobs It changes the source CDB's job setting

5. What does SERVICE_NAME_CONVERT=('COURSE_APP','COURSE_APP_TEST') rename?

Every source database link The new PDB's default service The specified user-defined service in the clone All source application accounts

Names, paths, and expected results in this lesson are teaching examples reviewed against documentation. No command or result in these notes represents an executed lab. Record actual output in the designated disposable lab.

AIOps Pro

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