Migrate & Clone Oracle PDBs: Network Relocation & Cross-CDB Guide

Deep dive into Oracle Multitenant PDB relocation & cross-CDB cloning over network. Optimize your database mobility and replication.

Overview: Agility and Resilience with Oracle Multitenant PDB Operations

In the dynamic landscape of enterprise IT, the ability to rapidly provision, consolidate, and manage database environments is paramount. Oracle's Multitenant architecture, introduced in Oracle Database 12c, revolutionized database management by enabling a single Container Database (CDB) to host multiple Pluggable Databases (PDBs). This architecture offers significant benefits in terms of resource utilization, patching, upgrades, and overall operational efficiency. Among the most powerful capabilities within the Multitenant framework are PDB relocation and cross-CDB cloning, especially when performed over the network.

PDB relocation involves moving a PDB from one CDB to another, effectively unplugging it from the source and plugging it into the target, without requiring shared storage. Cross-CDB cloning, on the other hand, creates a copy of an existing PDB in a different CDB, leaving the original PDB intact. Both operations leverage Oracle's robust network capabilities, allowing for direct data transfer between source and target database servers. This eliminates the complexities and overhead associated with traditional methods like RMAN backups and restores or manual data export/import, particularly in environments where shared storage is not feasible or desired.

The "over network" aspect is a game-changer for organizations seeking unparalleled agility. Imagine quickly moving a production PDB to a more powerful server, or provisioning a new development or testing environment by cloning a subset of your production data into a different CDB, all without downtime for the source PDB (in the case of cloning) or complex storage configurations. This article, penned from the perspective of a seasoned Oracle expert, will delve deep into the technical intricacies, practical implementations, security considerations, and best practices for performing PDB relocation and cross-CDB cloning over the network.

"The Oracle Multitenant architecture is not just a feature; it's a fundamental shift in how we manage databases. PDB relocation and cloning over the network are the power tools that unlock its true potential for consolidation, agility, and disaster recovery."
— Someshwar Thakur, Senior Technology Writer, TechNews Venture

Prerequisites: Laying the Foundation for Seamless Operations

Before embarking on PDB relocation or cross-CDB cloning over the network, several critical prerequisites must be met to ensure a smooth and successful operation. Neglecting any of these can lead to errors, delays, or even data inconsistencies.

Oracle Database Version Compatibility: Both the source and target CDBs should ideally be running the same Oracle Database version. While it is possible to relocate/clone from an older version to a newer version (e.g., 19c to 21c), this requires careful consideration of compatibility and potential upgrade implications for the PDB itself. Relocation/cloning from a newer to an older version is generally not supported.
CDB Open Mode: The source PDB must be open in READ WRITE mode for cloning (or READ ONLY if using the NO DATA clause). For relocation, the source PDB must be closed before the operation and then opened on the target. The target CDB must be open in READ WRITE mode.
Listener Configuration: A properly configured and running Oracle Net Listener is required on both the source and target database servers. The listener must be configured to accept connections for the respective CDBs.
TNS Connectivity: The target CDB must be able to resolve and connect to the source CDB via Oracle Net Services. This typically involves configuring tnsnames.ora entries on the target server (for cloning) or on the source server (for relocation) that point to the respective CDBs.
User Privileges: The user performing the operation (connecting to the source/target CDB) must have sufficient privileges. Typically, SYSDBA is used, but PDB_DBA can also be granted for more granular control over PDB operations within a CDB.
Sufficient Disk Space: The target server must have adequate disk space to accommodate the datafiles of the PDB being relocated or cloned.
Character Set Compatibility: For cloning, the character set of the source PDB must be compatible with the character set of the target CDB. Ideally, they should be identical. Mismatches can lead to data corruption or errors.
Timezone File Version: Ensure that the timezone file version in the target CDB is equal to or greater than that of the source PDB. You can check this using SELECT VERSION FROM V$TIMEZONE_FILE;.
COMPATIBLE Parameter: The COMPATIBLE initialization parameter of the target CDB must be set to a value that supports the PDB's compatibility level.
Network Connectivity and Firewall Rules: Ensure that network firewalls are configured to allow communication on the listener ports between the source and target database servers.

Let's illustrate with typical listener.ora and tnsnames.ora configurations:

Source Server (e.g., 192.168.1.10 hosting CDB_PROD):


# listener.ora on 192.168.1.10
LISTENER_PROD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))

SID_LIST_LISTENER_PROD =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cdb_prod)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = cdb_prod)
    )
  )

ADR_BASE_LISTENER_PROD = /u01/app/oracle

Target Server (e.g., 192.168.1.20 hosting CDB_DEV):


# listener.ora on 192.168.1.20
LISTENER_DEV =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))

SID_LIST_LISTENER_DEV =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cdb_dev)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = cdb_dev)
    )
  )

ADR_BASE_LISTENER_DEV = /u01/app/oracle

tnsnames.ora on Source Server (for PDB Relocation, connecting to target):


# tnsnames.ora on 192.168.1.10
CDB_DEV_TARGET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb_dev)
    )
  )

tnsnames.ora on Target Server (for Cross-CDB Cloning, connecting to source):


# tnsnames.ora on 192.168.1.20
CDB_PROD_SOURCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb_prod)
    )
  )

Always verify connectivity before starting the operation:


# On Source server (192.168.1.10)
$ tnsping CDB_DEV_TARGET

# On Target server (192.168.1.20)
$ tnsping CDB_PROD_SOURCE

Step-by-Step Implementation: Mastering PDB Relocation and Cross-CDB Cloning

Let's walk through the practical steps for both PDB relocation and cross-CDB cloning over the network. We'll use a hypothetical sales_pdb in CDB_PROD on 192.168.1.10, and target CDB_DEV on 192.168.1.20.

Scenario 1: PDB Relocation Over the Network

PDB relocation moves a PDB from one CDB to another. The original PDB is no longer available in the source CDB after a successful relocation. This is ideal for scenarios like moving a PDB to a different server for performance, consolidation, or maintenance.

Verify Source PDB Status:

Connect to the source CDB (CDB_PROD) and check the status of the PDB you intend to relocate.


$ sqlplus sys/oracle@cdb_prod as sysdba

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES_PDB                      READ WRITE NO
         4 HR_PDB                         READ WRITE NO

Close the Source PDB:

The PDB must be closed in the source CDB before relocation. This ensures data consistency during the move.


SQL> ALTER PLUGGABLE DATABASE SALES_PDB CLOSE IMMEDIATE;

Pluggable database altered.

Verify its status:


SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES_PDB                      MOUNTED
         4 HR_PDB                         READ WRITE NO

Execute the Relocation Command on Source CDB:
Connect to the source CDB and issue the ALTER PLUGGABLE DATABASE ... RELOCATE command. This command orchestrates the transfer of datafiles and metadata over the network to the target CDB.
```
SQL> ALTER PLUGGABLE DATABASE SALES_PDB RELOCATE TO CDB_DEV_TARGET;

Pluggable database altered.
        
```
The CDB_DEV_TARGET here refers to the TNS alias configured on the source server, pointing to the target CDB (CDB_DEV).

Monitor and Verify Relocation:

The relocation process can take time depending on the size of the PDB and network bandwidth. You can monitor its progress in V$PDBS on the source or target CDB. After the command completes, the PDB will no longer be listed in the source CDB.


-- On Source CDB (CDB_PROD)
SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 HR_PDB                         READ WRITE NO

Now, connect to the target CDB (CDB_DEV) and verify the presence of the relocated PDB:


$ sqlplus sys/oracle@cdb_dev as sysdba

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES_PDB                      MOUNTED

Open the Relocated PDB on Target:

The PDB will be in MOUNTED state on the target. You need to open it.


SQL> ALTER PLUGGABLE DATABASE SALES_PDB OPEN;

Pluggable database altered.

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES_PDB                      READ WRITE NO

Scenario 2: Cross-CDB Cloning Over the Network

Cross-CDB cloning creates a new PDB in a target CDB, using an existing PDB from a source CDB as its template. The source PDB remains operational and unaffected. This is excellent for creating development, testing, or reporting environments.

Verify Source PDB Status:

Connect to the source CDB (CDB_PROD) and ensure the PDB is open in READ WRITE mode.


$ sqlplus sys/oracle@cdb_prod as sysdba

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES_PDB                      READ WRITE NO

Execute the Cloning Command on Target CDB:
Connect to the target CDB (CDB_DEV) and issue the CREATE PLUGGABLE DATABASE ... FROM ... command. This command initiates the cloning process, pulling datafiles and metadata from the source PDB over the network.

It's crucial to use the FILE_NAME_CONVERT clause or ensure OMF (Oracle Managed Files) is enabled on the target to manage the new PDB's datafiles. If you omit FILE_NAME_CONVERT and OMF is not configured, you'll need to specify the full path for each datafile.
```
$ sqlplus sys/oracle@cdb_dev as sysdba

SQL> CREATE PLUGGABLE DATABASE SALES_PDB_CLONE FROM SALES_PDB@CDB_PROD_SOURCE
  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB_PROD/SALES_PDB/', '/u01/app/oracle/oradata/CDB_DEV/SALES_PDB_CLONE/');

Pluggable database created.
        
```
Here, SALES_PDB@CDB_PROD_SOURCE specifies the source PDB and the TNS alias pointing to its CDB. The FILE_NAME_CONVERT clause tells Oracle how to rename the datafiles as they are copied to the target's file system.

If you have OMF enabled on the target CDB, you can simplify the command:
```
SQL> CREATE PLUGGABLE DATABASE SALES_PDB_CLONE FROM SALES_PDB@CDB_PROD_SOURCE;

Pluggable database created.
        
```

Monitor and Verify Cloning:

The cloning process will create the new PDB in a MOUNTED state. You can monitor its progress and verify its creation:


-- On Target CDB (CDB_DEV)
SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES_PDB_CLONE                MOUNTED

Open the Cloned PDB on Target:

Once the PDB is created and mounted, open it for use.


SQL> ALTER PLUGGABLE DATABASE SALES_PDB_CLONE OPEN;

Pluggable database altered.

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES_PDB_CLONE                READ WRITE NO

Managing TDE-Enabled PDBs During Cloning/Relocation

If your source PDB is encrypted with Transparent Data Encryption (TDE), special considerations apply. The master key for the PDB resides in its keystore. When relocating or cloning, this key needs to be accessible by the target CDB.

For Relocation: If the target CDB already has a keystore, the PDB's master key is typically transferred and merged into the target CDB's keystore during the relocate operation. You might need to open the target CDB's keystore after the relocate.
For Cloning: When cloning an encrypted PDB, you need to provide the master key password (or the keystore location if using a file-based keystore) so the target CDB can decrypt and re-encrypt the data with its own keystore.

Example for cloning a TDE-enabled PDB:


-- On Target CDB (CDB_DEV)
-- Ensure the target CDB's keystore is open:
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "CDBDevKeystorePassword";

CREATE PLUGGABLE DATABASE ENCRYPTED_SALES_PDB_CLONE FROM ENCRYPTED_SALES_PDB@CDB_PROD_SOURCE
  KEYSTORE IDENTIFIED BY "SourcePDBMasterKeyPassword"
  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB_PROD/ENCRYPTED_SALES_PDB/', '/u01/app/oracle/oradata/CDB_DEV/ENCRYPTED_SALES_PDB_CLONE/');

-- After cloning, the new PDB's master key will be generated and stored in CDB_DEV's keystore.
-- You might need to open the PDB with its new master key.
ALTER PLUGGABLE DATABASE ENCRYPTED_SALES_PDB_CLONE OPEN;

It's critical to have the correct master key password for the source PDB when cloning, as Oracle needs to decrypt the data during the transfer. The new cloned PDB will then be encrypted using the target CDB's keystore.

Security Considerations: Protecting Your Data in Motion and at Rest

While the convenience of PDB operations over the network is significant, neglecting security can expose your critical data. Here are key considerations:

Network Security:
- Firewalls: Restrict listener port access (typically 1521) between database servers to only necessary hosts using firewall rules.
- VPN/Private Network: For sensitive data, perform PDB operations over a Virtual Private Network (VPN) or a dedicated private network segment to prevent eavesdropping and unauthorized access.
- Oracle Net Encryption: Configure Oracle Net Services to encrypt data in transit using SSL/TLS. This can be done via sqlnet.ora parameters (e.g., SQLNET.ENCRYPTION_SERVER=REQUIRED).
Database User Privileges:
- Least Privilege: While SYSDBA is often used for simplicity in examples, in production environments, consider creating a dedicated user with only the necessary PDB_DBA privileges or other specific grants required for PDB operations.
- Password Security: Use strong, unique passwords for database users involved in these operations. Avoid hardcoding passwords in scripts.
Listener Security:
- Password Protect Listener: Implement listener password protection using LSNRCTL SET PASSWORD to prevent unauthorized listener administration.
- Restrict Listener Access: Use VALID_NODE_CHECKING_REGISTRATION and REMOTE_REGISTRATION_ADDRESSES in listener.ora to limit which hosts can register services with the listener.
Transparent Data Encryption (TDE):
- If the PDB contains sensitive data, ensure TDE is enabled on the source PDB. When cloning or relocating, manage the master keys securely as discussed in the implementation section.
- After relocation/cloning, verify that the PDB on the target is correctly encrypted with the target CDB's keystore.
Auditing:
- Enable database auditing to track PDB creation, relocation, and open/close operations. This provides an audit trail for compliance and security monitoring.
- Audit commands like CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE ... RELOCATE, and ALTER PLUGGABLE DATABASE ... OPEN.

Best Practices: Optimizing Performance and Ensuring Reliability

Adhering to best practices can significantly improve the success rate, performance, and maintainability of your PDB relocation and cloning operations.

Test in Non-Production Environments: Always perform a dry run in a development or staging environment that closely mirrors your production setup. This helps identify potential issues related to connectivity, space, or compatibility before impacting live systems.
Monitor Network Bandwidth: PDB operations over the network are I/O intensive. Ensure sufficient network bandwidth between the source and target servers. Large PDBs can take a considerable amount of time and saturate network links.
Use OMF or FILE_NAME_CONVERT:
- Oracle Managed Files (OMF): If possible, configure OMF on your target CDB. This simplifies datafile management significantly, as Oracle automatically handles file naming and location, eliminating the need for FILE_NAME_CONVERT.
- FILE_NAME_CONVERT: If not using OMF, always use the FILE_NAME_CONVERT clause to specify how datafiles should be named and located on the target. This prevents errors due to conflicting file paths and ensures proper organization.
Check Target Disk Space: Before initiating any operation, verify that the target storage location has enough free space to accommodate all datafiles of the PDB. This includes space for redo logs, undo tablespaces, and temp files if they are also being copied/relocated.
Backup Before Critical Operations: Although PDB relocation and cloning are generally safe, always perform a full backup of the source CDB (or at least the source PDB) before undertaking these operations, especially in production environments. This provides a recovery point in case of unexpected failures.
Understand NO DATA Clause: For cloning, the NO DATA clause creates a PDB with only the schema objects, without any table data. This is useful for creating empty development or testing environments quickly, but be aware it will be an empty PDB.
Consider STANDBY Clause for Minimal Downtime: For critical production PDB migrations, consider using the STANDBY clause with CREATE PLUGGABLE DATABASE ... FROM .... This allows the PDB to be created as a standby, continuously applying changes from the source until a switchover is performed,

Migrate & Clone Oracle PDBs: Network Relocation & Cross-CDB Guide