Oracle Multitenant: How to Relocate & Clone PDBs Cross-CDB Network

Navigating Oracle Multitenant: Seamless PDB Relocation and Cross-CDB Cloning Over the Network

As a senior technology writer at TechNews Venture, I've witnessed the transformative power of Oracle's Multitenant architecture. It's not just a feature; it's a fundamental shift in how we manage databases, offering unprecedented agility, consolidation, and resource isolation. For organizations running critical applications like Oracle PeopleSoft, the ability to rapidly provision, relocate, and clone Pluggable Databases (PDBs) is a game-changer. This article delves into the intricacies of PDB relocation and cross-CDB cloning over the network, a powerful capability that eliminates shared storage dependencies and unlocks new paradigms for development, testing, disaster recovery, and production migrations.

The Oracle Multitenant architecture, introduced in Oracle Database 12c, allows a single Container Database (CDB) to host multiple PDBs. Each PDB is a fully functional database that appears to applications as a standalone database, yet it shares the CPU, memory, and background processes of the CDB. This consolidation drastically reduces administrative overhead, hardware footprint, and licensing costs. However, the true power emerges when you need to move or duplicate these PDBs. Traditional methods often involve complex RMAN backups, file transfers, and manual recovery steps, especially when moving between different hosts or storage systems. Oracle's network-based PDB operations simplify this immensely, allowing PDBs to be relocated or cloned directly from a source CDB to a target CDB across the network, without requiring shared storage or intricate file system manipulations.

Imagine a scenario in a PeopleSoft environment: you need to refresh a development PDB (PDB_HR_DEV) with the latest data from a production PDB (PDB_HR_PROD) residing on a different server. Or perhaps you're migrating a PeopleSoft HCM PDB from an on-premises CDB to a cloud-based CDB, or even consolidating multiple PeopleSoft departmental PDBs onto a new, more powerful CDB. These operations, traditionally resource-intensive and time-consuming, become remarkably streamlined with network-based PDB relocation and cloning. This approach leverages Oracle's internal mechanisms to stream data directly over SQL*Net, minimizing manual intervention and accelerating your operational workflows.

Prerequisites for Network-Based PDB Operations

Before embarking on PDB relocation or cloning over the network, several critical prerequisites must be met to ensure a smooth and successful operation. Ignoring these can lead to frustrating errors and delays.

• Source and Target CDBs: Both the source Container Database (CDB) and the target CDB must be up and running. They should ideally be of compatible Oracle Database versions, though Oracle does support some level of cross-version compatibility for PDB operations within certain limits (e.g., upgrading a PDB during plug-in).
• Network Connectivity: Direct network connectivity must exist between the source and target database servers. This means ensuring that firewalls are configured to allow communication on the Listener ports (typically 1521) and any ephemeral ports used for data transfer.
• TNS Connectivity: Proper TNS (Transparent Network Substrate) entries must be configured on both the source and target hosts for both the source and target CDBs. This allows the databases to resolve each other's service names.
• Listener Configuration: The Oracle Net Listeners on both source and target hosts must be running and correctly configured to register the respective CDB services.
• User Privileges: The user performing the operation (typically connected as SYSDBA) must have sufficient privileges in both the source and target CDBs.
• Disk Space: The target CDB's storage location must have ample free space to accommodate the relocated or cloned PDB's data files.
• Source PDB State:
  • For cloning, the source PDB must be open in READ WRITE mode. This allows Oracle to create a consistent copy of the PDB's data.
  • For relocation, the source PDB can be open in READ WRITE mode (for online relocation) or closed (for offline relocation).
• Compatibility: Ensure the source PDB is compatible with the target CDB. You can check this using SELECT COMPATIBLE FROM V$DATABASE; and SHOW PDBs; and checking the STATUS column for the PDB. Incompatible PDBs will show NEW or MIGRATE status after unplugging.
• Archivelog Mode: For online relocation or hot cloning, both the source and target CDBs should be in ARCHIVELOG mode.

Let's look at a typical listener.ora and tnsnames.ora configuration for our example scenario:

Source Host (dbhost01.techventure.com) - listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost01.techventure.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cdb_source)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = cdb_source)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Target Host (dbhost02.techventure.com) - listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost02.techventure.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cdb_target)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = cdb_target)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Source Host (dbhost01.techventure.com) - tnsnames.ora:

CDB_SOURCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost01.techventure.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb_source)
    )
  )

CDB_TARGET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost02.techventure.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb_target)
    )
  )

Target Host (dbhost02.techventure.com) - tnsnames.ora:

CDB_SOURCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost01.techventure.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb_source)
    )
  )

CDB_TARGET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost02.techventure.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb_target)
    )
  )

Verify listener status and service registration on both hosts:

[oracle@dbhost01 ~]$ lsnrctl status LISTENER

[oracle@dbhost02 ~]$ lsnrctl status LISTENER

Test TNS connectivity from source to target and vice-versa:

[oracle@dbhost01 ~]$ tnsping CDB_TARGET

[oracle@dbhost02 ~]$ tnsping CDB_SOURCE

Step-by-Step Implementation

Let's walk through the implementation of both PDB relocation and cross-CDB cloning over the network, focusing on practical PeopleSoft-related scenarios.

Scenario 1: PDB Relocation (Moving a PeopleSoft PDB)

PDB relocation is essentially a move operation. It detaches a PDB from its current CDB and attaches it to a different CDB, streaming the data files over the network. This is incredibly useful for balancing workloads, migrating a PeopleSoft HR production PDB to a new, more powerful server, or moving a PDB as part of a consolidation strategy.

In this example, we'll relocate PDB_HR_PROD from CDB_SOURCE on dbhost01 to CDB_TARGET on dbhost02. Oracle 12.2 and later versions support online PDB relocation, minimizing downtime.

1. Connect to the Source CDB and Verify PDB Status

[oracle@dbhost01 ~]$ sqlplus / as sysdba

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.

SQL> SHOW PDBS;

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

2. Initiate Online PDB Relocation

The RELOCATE clause directly streams the PDB from the source to the target CDB. The source PDB remains open for the majority of the operation, with only a brief outage during the final cutover.

SQL> ALTER PLUGGABLE DATABASE PDB_HR_PROD RELOCATE TO CDB_TARGET SERVICE_NAME='CDB_TARGET';

Explanation:

• PDB_HR_PROD: The name of the PDB to be relocated.
• CDB_TARGET: The TNS alias of the target CDB, as defined in tnsnames.ora. This tells Oracle where to stream the PDB data.
• SERVICE_NAME='CDB_TARGET': Specifies the service name of the target CDB, which is crucial for the network connection.

This command executes on the source CDB. Oracle then coordinates with the target CDB to create the new PDB, stream its data files, and perform the necessary metadata updates. The source PDB will transition to MIGRATE state during the cutover and then be dropped automatically.

3. Monitor Progress (Optional, but Recommended)

You can monitor the relocation progress from the target CDB's alert log or by querying V$SESSION_LONGOPS if the operation is large.

-- On Target CDB (dbhost02)
[oracle@dbhost02 ~]$ sqlplus / as sysdba

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.

SQL> SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS, START_TIME, LAST_UPDATE_TIME, ELAPSED_SECONDS, TIME_REMAINING
     FROM V$SESSION_LONGOPS
     WHERE OPNAME LIKE 'PDB Relocate%';

4. Verify PDB on Target CDB

Once the command completes on the source, connect to the target CDB and verify the PDB's presence and status.

-- On Target CDB (dbhost02)
[oracle@dbhost02 ~]$ sqlplus / as sysdba

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.

SQL> SHOW PDBS;

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

The PDB will initially be in MOUNTED state. You need to open it.

SQL> ALTER PLUGGABLE DATABASE PDB_HR_PROD OPEN;
Pluggable database altered.

SQL> SHOW PDBS;

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

The PDB has now been successfully relocated. The source PDB PDB_HR_PROD on CDB_SOURCE will have been automatically dropped.

Scenario 2: Cross-CDB Cloning (Creating a PeopleSoft Dev/Test Environment)

Cross-CDB cloning over the network allows you to create a duplicate of an existing PDB in a different CDB. This is invaluable for PeopleSoft development, testing, and training environments, enabling rapid provisioning of fresh copies of production data without impacting the source. We'll clone PDB_HR_PROD from CDB_SOURCE to create PDB_HR_DEV in CDB_TARGET.

1. Connect to the Target CDB and Create the Clone

The cloning operation is initiated from the target CDB. The source PDB must be open in READ WRITE mode.

[oracle@dbhost02 ~]$ sqlplus / as sysdba

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.

SQL> CREATE PLUGGABLE DATABASE PDB_HR_DEV
     FROM PDB_HR_PROD@CDB_SOURCE
     FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb_source/pdb_hr_prod/', '/u01/app/oracle/oradata/cdb_target/pdb_hr_dev/');

Explanation:

• PDB_HR_DEV: The name of the new PDB to be created on the target CDB.
• FROM PDB_HR_PROD@CDB_SOURCE: Specifies the source PDB (PDB_HR_PROD) and the TNS alias of its CDB (CDB_SOURCE). This tells Oracle where to pull the data from.
• FILE_NAME_CONVERT: This clause is crucial. It tells Oracle how to rename the data files and where to store them on the target. Without it, you might run into conflicts if the directory structures are identical or if you want a different naming convention. The example assumes standard Oracle Flexible Architecture (OFA) paths. Adjust as per your environment.

2. Open the Cloned PDB

After the cloning command completes, the new PDB will be in MOUNTED state. You need to open it.

SQL> ALTER PLUGGABLE DATABASE PDB_HR_DEV OPEN;
Pluggable database altered.

SQL> SHOW PDBS;

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

3. Verify the Cloned PDB

You can now connect to PDB_HR_DEV and verify its contents. For a PeopleSoft PDB, you might check the PS_HOME, database configuration, or run a simple query against a PeopleTools table.

SQL> ALTER SESSION SET CONTAINER = PDB_HR_DEV;
Session altered.

SQL> SELECT NAME, OPEN_MODE FROM V$DATABASE;

NAME       OPEN_MODE
---------- ----------
PDB_HR_DEV READ WRITE

SQL> SELECT COUNT(*) FROM PSLOCK; -- Example PeopleSoft table

Advanced Cloning with RMAN Duplicate PDB (for more control)

While the SQL CREATE PLUGGABLE DATABASE ... FROM ...@DBLINK command is convenient, RMAN offers more robust options, especially for complex scenarios, point-in-time recovery, or when you need to clone from a backup. The DUPLICATE PLUGGABLE DATABASE command in RMAN allows you to clone a PDB from an active database over the network.

Let's clone PDB_HR_PROD from CDB_SOURCE to PDB_HR_TEST in CDB_TARGET using RMAN.

1. Connect RMAN to Both Source and Target CDBs

You'll need to establish connections to both the source (target) and destination (auxiliary) CDBs. The `TNSNAMES.ORA` entries are vital here.

[oracle@dbhost02 ~]$ rman TARGET sys@CDB_SOURCE AUXILIARY sys@CDB_TARGET

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 29 10:30:00 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: CDB_SOURCE (DBID=1234567890)
auxiliary database Password:
connected to auxiliary database: CDB_TARGET (DBID=0987654321)

2. Execute the RMAN Duplicate Command

This command will duplicate the PDB from the active source database to the auxiliary target CDB.

RMAN> DUPLICATE PLUGGABLE DATABASE PDB_HR_PROD TO PDB_HR_TEST
      FROM ACTIVE DATABASE
      NOFILENAMECHECK;

Explanation:

• DUPLICATE PLUGGABLE DATABASE PDB_HR_PROD TO PDB_HR_TEST: Specifies the source PDB and the desired name for the new PDB.
• FROM ACTIVE DATABASE: Instructs RMAN to clone directly from the running source database, streaming data over the network.
• NOFILENAMECHECK: Important when cloning to a different host or directory structure, as RMAN will automatically create new file names for the data files. If you have specific file paths in mind, you can use SET NEWNAME FOR DATAFILE ... or configure OMF on the target.

RMAN will handle the creation of the PDB, streaming of data files, and recovery on the target. The auxiliary instance (CDB_TARGET) will automatically open the new PDB in MOUNTED mode after the duplication is complete.

3. Open the Cloned PDB on Target

RMAN> SQL 'ALTER PLUGGABLE DATABASE PDB_HR_TEST OPEN';

Or, exit RMAN and use SQL*Plus:

[oracle@dbhost02 ~]$ sqlplus / as sysdba

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.

SQL> ALTER PLUGGABLE DATABASE PDB_HR_TEST OPEN;
Pluggable database altered.

SQL> SHOW PDBS;

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

Note on File System Layout: When cloning or relocating, Oracle attempts to maintain the relative path structure of data files within the PDB. If the target CDB's data file location is different from the source, the FILE_NAME_CONVERT clause (for SQL cloning) or ensuring OMF (Oracle Managed Files) is configured on the target CDB (for RMAN) is highly recommended. OMF simplifies file management by letting Oracle manage the names and locations of data files, redo logs, and control files.

Security Considerations

Performing PDB relocation and cloning over the network involves moving sensitive data, especially in PeopleSoft environments. Robust security measures are paramount.

• Network Security:
  • Firewalls: Restrict Listener port access (typically 1521) to only trusted hosts or IP ranges.
  • VPN/Private Network: For operations across different network segments or cloud boundaries, ensure communication occurs over a secure VPN tunnel or a private network link (e.g., Oracle Cloud Infrastructure FastConnect, AWS Direct Connect, Azure ExpressRoute).
  • TLS/SSL: Configure Oracle Net Services to use TLS/SSL for encrypting data in transit. This is crucial for protecting PeopleSoft data during network transfers.
• Database User Privileges:
  • The SYSDBA privilege used for these operations grants extensive control. Ensure that only authorized personnel have access to these credentials.
  • Implement strong password policies and multi-factor authentication where possible.
• Listener Security:
  • Regularly patch your Oracle Database and Listener software.
  • Restrict access to lsnrctl commands to authorized OS users.
  • Consider using Listener Valid Node Checking (VNC) to restrict connections to specific IP addresses.
• Auditing:
  • Enable database auditing for critical actions, especially those related to PDB creation, modification, and deletion. Track who performed the operation, when, and from where.
  • Audit trails provide accountability and can help detect unauthorized activities.
• Data Masking/Redaction: When cloning production PeopleSoft PDBs for development or testing, consider implementing data masking or redaction techniques to protect sensitive information in non-production environments. Oracle Data Masking and Subsetting Pack can be used for this purpose.

Best Practices

To maximize efficiency, reliability, and security during PDB relocation and cloning, adhere to these best practices:

• Thorough Testing: Always test relocation and cloning procedures in non-production environments that mimic your production setup before attempting them on live PeopleSoft systems. Document the steps and expected outcomes.
• Performance Monitoring: Monitor CPU, I/O, and network utilization on both source and target servers during the operation. Large PDBs, like those housing PeopleSoft applications, can generate significant network traffic and I/O load.
• Plan for Downtime (Even for Online Operations): While online relocation minimizes downtime, there's a brief cutover period. Plan this window carefully to avoid impacting critical PeopleSoft users. For cloning, the source PDB remains fully available, but the target CDB will experience resource utilization.
• Backup Strategy: Ensure you have a robust backup and recovery strategy for both the source PDB (before relocation/cloning) and the target CDB (after the operation). RMAN full CDB backups, including all PDBs, are crucial.
• Resource Management: Be mindful of resource consumption on the target CDB. Cloning a large PeopleSoft PDB will consume significant disk space and may temporarily increase CPU/memory usage during the cloning process. Ensure the target CDB has adequate resources.
• Version Compatibility: While Oracle generally supports PDBs across minor versions within a major release, always verify compatibility. For example, a PDB from 19c can be plugged into another 19c CDB. Upgrading a PDB during a plug-in operation (e.g., 12.2 PDB into 19c CDB) is also supported but requires careful planning.
• Pre-Checks: Develop and run pre-check scripts to verify prerequisites: network connectivity, TNS entries, listener status, disk space, and PDB compatibility.
• Post-Operations: After cloning a PeopleSoft PDB, remember to update application-specific configurations (e.g., PeopleSoft configuration files, web server settings, batch server configurations) to point to the new PDB service name and ensure all PeopleSoft components are correctly configured for the new environment. This includes updating environment variables and database connection strings.
• Automate: For repetitive tasks like refreshing PeopleSoft development environments, consider scripting these operations using shell scripts combined with SQL*Plus or RMAN.

Frequently Asked Questions (FAQ)

Q1: Can I relocate/clone a PDB between different OS platforms (e.g., Linux to Windows)?

A1: No, PDB relocation and cloning over the network (using ALTER PLUGGABLE DATABASE ... RELOCATE or CREATE PLUGGABLE DATABASE ... FROM ...@DBLINK) requires the source and target CDBs to be on the same endian format. This generally means the same operating system platform and architecture (e.g., Linux x64 to Linux x64). Cross-platform PDB migrations typically involve an unplug/plug process with data pump export/import or RMAN cross-platform transportable tablespace operations, which are more complex.

Q2: What happens if the network connection drops during a PDB relocation or cloning operation?

A2: If the network connection drops during a PDB relocation, the operation will likely fail. In the case of online relocation, the source PDB might remain in a MIGRATE state or revert to its original state, and the target PDB creation might be incomplete. For cloning, the target PDB creation will fail. You would typically need to clean up any partially created PDB on the target CDB (DROP PLUGGABLE DATABASE PDB_NAME INCLUDING DATAFILES;) and retry the operation. It's crucial to have a stable, high-bandwidth network for these operations, especially with large PeopleSoft PDBs.

Q3: Is it possible to rename a PDB during relocation or cloning?

A3: Yes, you can rename a PDB during a cloning operation. When using the CREATE PLUGGABLE DATABASE ... FROM ...@DBLINK syntax, the name you specify after CREATE PLUGGABLE DATABASE is the new name for the cloned PDB. For example, CREATE PLUGGABLE DATABASE PDB_HR_DEV FROM PDB_HR_PROD@CDB_SOURCE creates a new PDB named PDB_HR_DEV. For relocation, the ALTER PLUGGABLE DATABASE PDB_NAME RELOCATE command moves the PDB with its existing name. If