# Cloning a Pluggable Database (PDB) Across CDBs via Database Link

**Applies to:** Oracle Database 19c (Standard/Enterprise Edition), multitenant architecture **Status:** Draft / Verified in test

* * *

## Overview

This runbook documents cloning a source PDB into a new target PDB using a remote clone over a database link. The source PDB remains online and read-write throughout — no downtime on the source is required. Destination datafiles are placed on a dedicated target filesystem/mount.

This method works because Oracle 12.2+ supports cloning a PDB that is open read-write; Oracle reconstructs a consistent copy using the source's redo stream, similar to `RMAN DUPLICATE ... FROM ACTIVE DATABASE`.

**Placeholders used in this document:**

| Placeholder | Description |
| --- | --- |
| `<SOURCE_CDB>` | TNS alias / service name of the source CDB |
| `<SOURCE_HOST>` | Hostname of the source database server |
| `<SOURCE_PDB>` | Name of the PDB being cloned |
| `<TARGET_PDB>` | Name of the new PDB being created |
| `<DEST_CDB_NAME>` | Name of the destination CDB |
| `<DEST_MOUNT>` | Target filesystem/mount point for datafiles (e.g. a dedicated data volume) |
| `<LINK_USER>` | Common user created on the source CDB for DB link authentication |
| `<LINK_PASSWORD>` | Password for `<LINK_USER>` |
| `<DB_LINK_NAME>` | Name of the database link created on the destination CDB |

* * *

## Prerequisites

*   Source and destination CDBs reachable over SQL\*Net.
    
*   Same Oracle version, same platform/endianness, matching character sets between CDBs.
    
*   Sufficient free space on `<DEST_MOUNT>` for the cloned datafiles.
    
*   `LOCAL_UNDO_ENABLED = TRUE` on the source CDB (mandatory since 12.2).
    
*   Common user on the source CDB with `CREATE PLUGGABLE DATABASE` privilege, used solely to authenticate the DB link.
    

* * *

## Procedure

### 1\. Add a TNS alias for the source CDB (on the destination host)

Add to `tnsnames.ora` on the destination host:

```plaintext
<SOURCE_CDB> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <SOURCE_HOST>)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = <source_cdb_service_name>))
  )
```

Verify connectivity:

```bash
tnsping <SOURCE_CDB>
```

### 2\. Create a common user on the source CDB

Run on the **source** CDB as SYS:

```sql
CREATE USER <LINK_USER> IDENTIFIED BY <LINK_PASSWORD> CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO <LINK_USER> CONTAINER=ALL;
```

### 3\. Confirm LOCAL\_UNDO is enabled on the source

```sql
SELECT property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';
```

Expected: `TRUE`. The source PDB stays open read-write for the entire operation — **no shutdown required**.

### 4\. Create the database link (on the destination CDB)

```sql
ALTER SESSION SET CONTAINER = CDB$ROOT;

CREATE DATABASE LINK <DB_LINK_NAME>
  CONNECT TO <LINK_USER> IDENTIFIED BY <LINK_PASSWORD>
  USING '<SOURCE_CDB>';
```

Test:

```sql
SELECT * FROM dual@<DB_LINK_NAME>;
```

### 5\. Confirm the destination filesystem is ready

```bash
ls -ld <DEST_MOUNT>
df -h <DEST_MOUNT>
```

### 6\. Check and set `db_create_file_dest` before cloning

> **⚠️ Critical step — see Lessons Learned below.**

```sql
SHOW PARAMETER db_create_file_dest;
```

Set it to the intended destination:

```sql
ALTER SYSTEM SET db_create_file_dest = '<DEST_MOUNT>' SCOPE=BOTH;
```

Do **not** reset this parameter to NULL — an unset `db_create_file_dest` combined with `PATH_PREFIX` produces `ORA-65016: FILE_NAME_CONVERT must be specified` on a remote (DB link) clone.

### 7\. Run the clone

```sql
ALTER SESSION SET CONTAINER = CDB$ROOT;

CREATE PLUGGABLE DATABASE <TARGET_PDB>
  FROM <SOURCE_PDB>@<DB_LINK_NAME>;
```

Files are created under `<DEST_MOUNT>/<DEST_CDB_NAME>/<GUID>/datafile/...` (standard OMF naming, rooted at `db_create_file_dest`).

**If a specific subdirectory is required** (e.g. `<DEST_MOUNT>/<TARGET_PDB>/`), do not rely on `PATH_PREFIX` for a remote/DB-link clone — it did not override `db_create_file_dest` in testing. Use `FILE_NAME_CONVERT` instead, after retrieving the source's actual file paths:

```sql
SELECT file_name FROM cdb_data_files@<DB_LINK_NAME>
WHERE con_id = (SELECT con_id FROM v$pdbs@<DB_LINK_NAME> WHERE name = '<SOURCE_PDB>');
```

```sql
CREATE PLUGGABLE DATABASE <TARGET_PDB>
  FROM <SOURCE_PDB>@<DB_LINK_NAME>
  FILE_NAME_CONVERT = ('<source_path_prefix>', '<DEST_MOUNT>/<DEST_CDB_NAME>/<TARGET_PDB>/');
```

### 8\. Open the new PDB

```sql
ALTER PLUGGABLE DATABASE <TARGET_PDB> OPEN;
```

### 9\. Save state (auto-open on CDB restart)

```sql
ALTER PLUGGABLE DATABASE <TARGET_PDB> SAVE STATE;
```

### 10\. Verify

```sql
SELECT name, open_mode, restricted FROM v$pdbs WHERE name = '<TARGET_PDB>';

SELECT file_name FROM cdb_data_files
WHERE con_id = (SELECT con_id FROM v$pdbs WHERE name = '<TARGET_PDB>');
```

Confirm every datafile path starts with `<DEST_MOUNT>/...`. Spot-check row counts against the source PDB to confirm data consistency.

### 11\. Cleanup

```sql
DROP DATABASE LINK <DB_LINK_NAME>;
DROP USER <LINK_USER> CASCADE;
```

Only run this if the link/user are one-time use; skip if they'll be reused for future clones.

* * *

## Lessons Learned / Troubleshooting

| Issue | Cause | Resolution |
| --- | --- | --- |
| Datafiles created on the wrong mount despite `PATH_PREFIX` being specified | `db_create_file_dest` was already set to a different path on the destination instance and took precedence over `PATH_PREFIX` | Check `SHOW PARAMETER db_create_file_dest` **before** running `CREATE PLUGGABLE DATABASE`, and set it explicitly to the desired target path |
| `ORA-65016: FILE_NAME_CONVERT must be specified` when using `PATH_PREFIX` | `db_create_file_dest` was reset to NULL; for a remote (DB link) clone, `PATH_PREFIX` alone did not enable OMF-style file creation | Keep `db_create_file_dest` set to a real path; don't rely on `PATH_PREFIX` overriding it for DB-link clones — use `FILE_NAME_CONVERT` if a custom path is required |
| Wrong-location clone already created | N/A | If no data had been written yet, cleanest fix is `ALTER PLUGGABLE DATABASE <TARGET_PDB> CLOSE IMMEDIATE;` then `DROP PLUGGABLE DATABASE <TARGET_PDB> INCLUDING DATAFILES;` and re-run the clone with corrected parameters |

**Key takeaway:** For remote PDB clones over a DB link in 19c, `db_create_file_dest` is the authoritative control for OMF file placement. Set it explicitly before cloning rather than depending on `PATH_PREFIX`.

* * *

## Rollback

To remove the target PDB entirely:

```sql
ALTER PLUGGABLE DATABASE <TARGET_PDB> CLOSE IMMEDIATE;
DROP PLUGGABLE DATABASE <TARGET_PDB> INCLUDING DATAFILES;
```

The source PDB is never altered during this procedure, so no source-side rollback is needed.