Oracle Multitenant: PDB Relocation & Cross-CDB Cloning Over Network

Navigating the Modern Oracle Landscape: PDB Relocation and Cross-CDB Cloning Over Network for PeopleSoft Environments

As a senior technology writer at TechNews Venture, I’ve had the privilege of witnessing firsthand the transformative power of Oracle’s Multitenant architecture. For organizations running Oracle PeopleSoft, a critical enterprise application suite, the agility and efficiency offered by Pluggable Databases (PDBs) are nothing short of revolutionary. In this article, we’ll dive deep into two powerful capabilities that leverage this architecture: PDB relocation and cross-CDB cloning over the network. These features are indispensable for modern PeopleSoft environments, enabling rapid provisioning, streamlined development/testing cycles, and robust disaster recovery strategies.

Overview: The Power of Multitenant for PeopleSoft

Oracle's Multitenant architecture, introduced with Oracle Database 12c, fundamentally changed how we manage databases. Instead of the traditional one-database-per-instance model, we now have a Container Database (CDB) that can host multiple independent Pluggable Databases (PDBs). Each PDB behaves like a standalone database to applications, yet shares the CDB's background processes, memory, and common user infrastructure. This separation of concerns significantly simplifies database administration, patching, and upgrades.

For PeopleSoft administrators and developers, the Multitenant architecture translates into unprecedented agility. Imagine needing a new PeopleSoft development environment. Traditionally, this meant a full database restore, often taking hours or even days. With PDBs, you can clone an existing PeopleSoft PDB in minutes. The same applies to refreshing test environments, migrating PeopleSoft instances to new hardware or cloud platforms, or even setting up a rapid disaster recovery site.

The ability to relocate and clone PDBs over the network is a game-changer. It eliminates the need for shared storage or manual file transfers, allowing for seamless movement and duplication of PeopleSoft environments across different servers, data centers, or cloud regions. This is particularly beneficial for distributed teams or hybrid cloud deployments where physical proximity or shared storage is not always feasible.

We'll explore the technical intricacies of:

• PDB Relocation: Moving a PDB from one CDB to another. This is akin to moving a virtual machine from one host to another, where the PDB ceases to exist in the source CDB and becomes part of the target CDB.
• Cross-CDB Cloning: Creating a copy of a PDB from a source CDB into a target CDB. The source PDB remains intact and fully functional, while a new, identical PDB is created on the target. This is perfect for provisioning new development or test environments for PeopleSoft applications.

Prerequisites: Laying the Foundation for Network Operations

Before embarking on PDB relocation or cloning over the network, several prerequisites must be met to ensure a smooth and successful operation. These are fundamental to establishing connectivity and ensuring data integrity.

1. Oracle Database Version

• Oracle Database 12c Release 1 (12.1.0.2) or later: Supports basic PDB relocation and cold cloning.
• Oracle Database 12c Release 2 (12.2.0.1) or later: Introduces support for hot cloning (cloning a PDB while it is open in read-write mode).
• Oracle Database 19c (19.0.0.0) or later: Recommended for production environments due to enhanced stability, performance, and features. All examples in this article will assume a 19c environment.

2. Network Connectivity and Configuration

• TCP/IP Connectivity: The source and target CDB servers must be able to communicate over the network. Firewall rules must allow traffic on the Oracle Listener port (default 1521).
• Oracle Net Services Configuration: Both source and target CDBs must have correctly configured listener.ora and tnsnames.ora files.

Example listener.ora on both Source and Target CDB servers:

# listener.ora for CDB_SOURCE and CDB_TARGET
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cdb-source.example.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = cdb-target.example.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)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = CDB_TARGET)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = CDB_TARGET)
    )
  )

# Ensure the listener is running and registered with the database.
# Check with: lsnrctl status LISTENER
# If not running: lsnrctl start LISTENER
    

Example tnsnames.ora on both Source and Target CDB servers:

# tnsnames.ora for CDB_SOURCE and CDB_TARGET

CDB_SOURCE_SVC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cdb-source.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB_SOURCE)
    )
  )

CDB_TARGET_SVC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cdb-target.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB_TARGET)
    )
  )
    

Ensure that the TNS_ADMIN environment variable is set correctly to point to the directory containing tnsnames.ora.

3. User Privileges

• A common user with the necessary privileges must exist in the root of both the source and target CDBs. This user will be used to create the database link.

  
  -- On both CDB_SOURCE and CDB_TARGET as SYSDBA
  CREATE USER c##db_link_user IDENTIFIED BY YourSecurePassword#123 CONTAINER=ALL;
  GRANT CREATE PLUGGABLE DATABASE TO c##db_link_user CONTAINER=ALL;
  GRANT CREATE SESSION TO c##db_link_user CONTAINER=ALL;
  GRANT SYSDBA TO c##db_link_user CONTAINER=ALL; -- For relocation/cloning purposes, SYSDBA is often used for simplicity and comprehensive access. For production, consider a more granular set like CREATE PLUGGABLE DATABASE, CREATE SESSION, ALTER DATABASE, SELECT ANY DICTIONARY.
          

4. Disk Space

• The target CDB must have sufficient disk space to accommodate the data files of the relocated or cloned PDB. For cloning, this means space equal to or greater than the source PDB's data files.

5. Other Considerations

• Source PDB State: For relocation and cold cloning, the source PDB must be in READ ONLY mode. For hot cloning (12cR2+), the source PDB can remain in READ WRITE mode.
• PDB Compatibility: The source PDB must be compatible with the target CDB. This usually means they are on the same Oracle Database version and patch level.
• Unique PDB Name: The new PDB name on the target CDB must be unique within that CDB.

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

Scenario: Migrating/Cloning a PeopleSoft PDB

Let's assume we have a PeopleSoft PDB named PEOPLE_PDB_PROD on CDB_SOURCE, and we want to either relocate it to CDB_TARGET or clone it to create a new PDB named PEOPLE_PDB_DEV on CDB_TARGET.

Part 1: PDB Relocation Over Network

Relocation moves a PDB from one CDB to another. The source PDB will no longer exist in the source CDB after a successful relocation. This is ideal for migrating a PeopleSoft production instance to new infrastructure or a different CDB.

Step 1: On Source CDB (CDB_SOURCE) - Prepare the PDB

The PDB must be closed and opened in READ ONLY mode for relocation.

sqlplus / as sysdba

-- Connect to the root container of the source CDB
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Check the current state of the PDB
SELECT PDB_NAME, STATUS FROM CDB_PDBS WHERE PDB_NAME = 'PEOPLE_PDB_PROD';

-- Close the PDB
ALTER PLUGGABLE DATABASE PEOPLE_PDB_PROD CLOSE IMMEDIATE;

-- Open the PDB in READ ONLY mode
ALTER PLUGGABLE DATABASE PEOPLE_PDB_PROD OPEN READ ONLY;

-- Verify the state
SELECT PDB_NAME, STATUS FROM CDB_PDBS WHERE PDB_NAME = 'PEOPLE_PDB_PROD';
-- Expected output: PEOPLE_PDB_PROD | READ ONLY

Step 2: On Target CDB (CDB_TARGET) - Create a Database Link

Connect to the root container of the target CDB and create a database link to the source CDB. This link will be used to access the PDB dictionary information from the source.

sqlplus / as sysdba

-- Connect to the root container of the target CDB
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Create the database link
-- Use the common user created in prerequisites
CREATE DATABASE LINK cdb_source_link CONNECT TO c##db_link_user IDENTIFIED BY "YourSecurePassword#123" USING 'CDB_SOURCE_SVC';

-- Verify the database link (optional, but good practice)
SELECT * FROM DUAL@cdb_source_link;

Step 3: On Target CDB (CDB_TARGET) - Relocate the PDB

Execute the `ALTER PLUGGABLE DATABASE ... RELOCATE` command. This command is executed on the target CDB, pulling the PDB from the source.

sqlplus / as sysdba

-- Connect to the root container of the target CDB
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Relocate the PDB. Specify the PDB name from the source and the database link.
-- The PDB will be named PEOPLE_PDB_PROD on the target CDB.
ALTER PLUGGABLE DATABASE PEOPLE_PDB_PROD RELOCATE TO cdb_source_link;

-- This command will take time depending on the size of the PDB and network speed.
-- You can monitor progress in the alert log of both CDBs.

Step 4: On Target CDB (CDB_TARGET) - Open the Relocated PDB

After successful relocation, the PDB will be in `MOUNTED` state on the target. Open it in `READ WRITE` mode.

sqlplus / as sysdba

-- Connect to the root container of the target CDB
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Open the relocated PDB
ALTER PLUGGABLE DATABASE PEOPLE_PDB_PROD OPEN;

-- Verify the state
SELECT PDB_NAME, OPEN_MODE FROM V$PDBS WHERE PDB_NAME = 'PEOPLE_PDB_PROD';
-- Expected output: PEOPLE_PDB_PROD | READ WRITE

Step 5: On Source CDB (CDB_SOURCE) - Verify PDB Removal

The PDB should no longer be listed in the source CDB's `CDB_PDBS` view.

sqlplus / as sysdba

-- Connect to the root container of the source CDB
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Verify that the PDB has been removed
SELECT PDB_NAME, STATUS FROM CDB_PDBS WHERE PDB_NAME = 'PEOPLE_PDB_PROD';
-- Expected output: No rows selected

The data files for PEOPLE_PDB_PROD will also be removed from the source CDB's file system.

Part 2: Cross-CDB Cloning Over Network

Cloning creates a new, independent copy of a PDB. This is exceptionally useful for PeopleSoft environments, allowing you to quickly provision new development, testing, or training instances from a golden master or a production snapshot.

Step 1: On Source CDB (CDB_SOURCE) - Prepare the PDB for Cloning

For a cold clone (PDB in read-only), follow Step 1 from PDB Relocation. For a hot clone (12cR2+), the PDB can remain in `READ WRITE` mode.

Option A: Cold Clone (PDB in READ ONLY)

sqlplus / as sysdba

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE PEOPLE_PDB_PROD CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE PEOPLE_PDB_PROD OPEN READ ONLY;

Option B: Hot Clone (PDB in READ WRITE - requires 12cR2+)

sqlplus / as sysdba

ALTER SESSION SET CONTAINER = CDB$ROOT;
-- No state change needed; PEOPLE_PDB_PROD can remain open READ WRITE
SELECT PDB_NAME, OPEN_MODE FROM V$PDBS WHERE PDB_NAME = 'PEOPLE_PDB_PROD';
-- Expected output: PEOPLE_PDB_PROD | READ WRITE

Step 2: On Target CDB (CDB_TARGET) - Create a Database Link

This is the same as Step 2 for PDB Relocation. If you already created cdb_source_link, you can reuse it.

sqlplus / as sysdba

ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE DATABASE LINK cdb_source_link CONNECT TO c##db_link_user IDENTIFIED BY "YourSecurePassword#123" USING 'CDB_SOURCE_SVC';

Step 3: On Target CDB (CDB_TARGET) - Create the Cloned PDB

Execute the `CREATE PLUGGABLE DATABASE ... FROM ...@DBLINK` command. You'll specify a new unique name for the cloned PDB.

sqlplus / as sysdba

ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Specify the path for the new PDB's data files.
-- Oracle Managed Files (OMF) are highly recommended, where Oracle handles file naming and placement.
-- If not using OMF, you must specify FILE_NAME_CONVERT or CREATE_FILE_DEST.
-- Example with OMF (assuming DB_CREATE_FILE_DEST is set for CDB_TARGET):
CREATE PLUGGABLE DATABASE PEOPLE_PDB_DEV FROM PEOPLE_PDB_PROD@cdb_source_link;

-- If you need to specify a different path or are not using OMF:
-- CREATE PLUGGABLE DATABASE PEOPLE_PDB_DEV FROM PEOPLE_PDB_PROD@cdb_source_link
--   FILE_NAME_CONVERT = ('/u02/oradata/CDB_SOURCE/PEOPLE_PDB_PROD/', '/u02/oradata/CDB_TARGET/PEOPLE_PDB_DEV/');

-- For a hot clone (source PDB was READ WRITE), you must include the NO DATA RECOVERY clause:
-- CREATE PLUGGABLE DATABASE PEOPLE_PDB_DEV FROM PEOPLE_PDB_PROD@cdb_source_link
--   NO DATA RECOVERY;

-- This command will take time depending on the size of the PDB and network speed.
-- You can monitor progress in the alert log of both CDBs.

Step 4: On Target CDB (CDB_TARGET) - Open the Cloned PDB

After successful cloning, the new PDB will be in `MOUNTED` state. Open it in `READ WRITE` mode.

sqlplus / as sysdba

ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Open the new PDB
ALTER PLUGGABLE DATABASE PEOPLE_PDB_DEV OPEN;

-- Verify the state
SELECT PDB_NAME, OPEN_MODE FROM V$PDBS WHERE PDB_NAME = 'PEOPLE_PDB_DEV';
-- Expected output: PEOPLE_PDB_DEV | READ WRITE

Step 5: On Source CDB (CDB_SOURCE) - Reset PDB State (if cold clone)

If you put the source PDB in `READ ONLY` mode for a cold clone, remember to open it back in `READ WRITE` mode.

sqlplus / as sysdba

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE PEOPLE_PDB_PROD OPEN READ WRITE;

Post-Cloning Steps for PeopleSoft Environments

After cloning a PeopleSoft PDB, you'll typically need to perform several application-level adjustments:

• Update Configuration Files: Adjust database connection strings in PeopleSoft's `psappsrv.cfg`, `psprcs.cfg`, and `configuration.properties` files to point to the new PDB service name.
• Change Database Name in PeopleTools: Use PeopleSoft's Database Configuration utility (`pscfg.exe` on Windows or `psconfig.sh` on Unix) to update the database name within the PeopleTools system tables if the PDB service name has changed significantly.
• Refresh Statistics: Although not strictly mandatory for functionality, refreshing optimizer statistics is a good practice for performance.
• Clean up Application Data: For development/test environments, you might need to purge specific transaction data, reset sequences, or obfuscate sensitive information.
• Adjust Listener Configuration: Ensure the target CDB's listener is aware of the new PDB service (usually automatic if using dynamic registration).
• Rebuild Search Indexes: If PeopleSoft Search is used, you might need to rebuild the search indexes for the new environment.

Security Considerations

Implementing PDB relocation and cloning over the network introduces several security vectors that must be addressed rigorously, especially when dealing with sensitive PeopleSoft data.

• Database Link User Privileges: The common user used for the database link (`c##db_link_user` in our example) should follow the principle of least privilege. While SYSDBA simplifies the process, for production or highly secure environments, consider granting only the minimum necessary privileges (e.g., CREATE PLUGGABLE DATABASE, CREATE SESSION, SELECT ANY DICTIONARY, ALTER DATABASE).
• Secure Password Management: Passwords for database link users should be strong, complex, and stored securely. Avoid hardcoding passwords in scripts where possible, or use Oracle Wallet for secure storage.
• Network Encryption: Encrypt network traffic between the source and target CDBs using Oracle Net Services native encryption (SSL/TLS). This protects data in transit from eavesdropping.

  
  # Example sqlnet.ora configuration for encryption (on both source and target)
  SQLNET.ENCRYPTION_SERVER = REQUIRED
  SQLNET.ENCRYPTION_CLIENT = REQUIRED
  SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
  SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
  SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
  SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
  SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)
  SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)
          

• Firewall Rules: Strictly control network access between the CDB servers. Only allow necessary ports (typically 1521 for the listener) between the specific IP addresses of the source and target CDB hosts.
• Auditing: Implement robust auditing on both source and target CDBs to track PDB operations, including who performed them and when. This is crucial for compliance and forensic analysis.
• Data Masking/Redaction: When cloning production PeopleSoft PDBs for development or testing, consider implementing Oracle Data Masking and Subsetting or similar solutions to protect sensitive data in non-production environments.

Best Practices

To maximize the efficiency and reliability of PDB operations, adhere to these best practices:

• Test Thoroughly: Always test relocation and cloning procedures in non-production environments first. Document every step, including pre-checks and post-operations.
• Monitor During Operations: Keep an eye on the alert logs of both source and target CDBs. Use tools like `v$session_longops` or `v$active_session_history` to monitor progress if the operation takes a long time.
• Network Bandwidth: Ensure ample network bandwidth between the source and target servers. Large PeopleSoft PDBs can generate significant network traffic during transfer.
• Disk Space Pre-check: Verify that the target CDB has sufficient disk space for the new PDB's data files. Oracle Managed Files (OMF) simplify this by automatically managing file names and locations, but the underlying storage must still be sufficient.
• TNS_ADMIN Consistency: Ensure the `TNS_ADMIN` environment variable is consistently set on both servers to point to the correct `tnsnames.ora` file.
• Post-Operation Health Checks: After relocation or cloning, always perform comprehensive health checks on the new PDB. This includes:
  • Checking PDB open mode.
  • Running `DBA_REGISTRY` to ensure all components are valid.
  • Checking for invalid objects (`DBA_OBJECTS`).
  • Verifying PeopleSoft application connectivity and basic functionality.
  • Updating PeopleSoft-specific configurations as mentioned earlier.
• Backup Strategy: Ensure your backup and recovery strategy accounts for the new PDB on the target CDB.
• Consider RMAN: For very large PDBs, or scenarios requiring more granular control over recovery, RMAN's `DUPLICATE DATABASE` or `CREATE PLUGGABLE DATABASE FROM` commands (especially with `NO RECOVERY` or `USING BACKUPSET`) can offer more robust options for cloning, including point-in-time recovery. However, the SQL-based network clone is often simpler for direct PDB copies.

FAQ

Q1: What Oracle Database versions fully support PDB relocation and cross-CDB cloning over the network?

Basic PDB relocation and cold cloning (source PDB in READ ONLY mode) are supported from Oracle Database 12c Release 1 (12.1.0.2) onwards. Hot cloning (source PDB can be in READ WRITE mode) was introduced in Oracle Database 12c Release 2 (12.2.0.1). For production environments, Oracle Database 19c is highly recommended due to its stability, long-term support, and performance optimizations, making it the preferred choice for managing PeopleSoft PDBs.

Q2: Can I relocate or clone a PeopleSoft PDB that is currently open in read-write mode?

For PDB relocation, the source PDB must typically be closed and opened in READ ONLY mode to ensure data consistency during the move. However, for cross-CDB cloning, you can perform a "hot clone" if you are using Oracle Database 12c Release 2 (12.2.0.1) or later. In this scenario, the source PDB can remain open in READ WRITE mode, allowing your PeopleSoft application to continue operating without interruption during the cloning process. You must include the NO DATA RECOVERY clause in the CREATE PLUGGABLE DATABASE command for a hot clone.

Q3: What are the performance implications of performing these operations over the network?

The primary performance factors are network bandwidth and latency between the source and target CDB servers, as well as the I/O capabilities of both systems. Large PeopleSoft PDBs with many gigabytes or terabytes of data will naturally take longer to transfer. Ensure your network infrastructure can handle the sustained data transfer rates. High-speed, low-latency network connections are crucial for minimizing downtime during relocation or accelerating the provisioning of cloned environments. Monitoring network utilization and disk I/O during the process will help identify potential bottlenecks.

Conclusion

Oracle Multitenant's PDB relocation and cross-CDB cloning over the network are powerful features that significantly enhance the agility and manageability of Oracle PeopleSoft environments. As Someshwar Thakur at TechNews Venture, I've seen how these capabilities empower organizations to respond faster to business demands, accelerate development cycles, and bolster their disaster recovery strategies. By understanding the prerequisites, mastering the step-by-step implementation, and adhering to robust security and best practices, DBAs and architects can harness these tools to build more resilient, efficient, and scalable PeopleSoft infrastructures. The future of database management is modular, agile, and connected – and Oracle's Multitenant architecture is leading the way.