Saturday, 1 February 2025

Relocate a PDB from SOURCE to TARGET CDB

How to relocate a pluggable database (PDB) from 1 Container database (CDB) to another CDB?

This article talk about PDB relocation i.e. simply migrating and attaching a PDB from 1 CDB to another CDB. The process is almost similar to a normal PDB clone but since we are migrating a DB here so there are variations as well.

For this exercise, I am using Oracle 19c, with 2 CDBs named CDB1, running on 2 separate VMs. Here is the simple picture,

  • CDB1 (oravm19c) - TARGET CDB
  • CDB1 (oravm19c-2) - SOURCE CDB
  • PDB1 - PDB to be relocated mapped to CDB1 of oravm19c-2


Let's begin,

1) Create a common user in SOURCE CDB.

[oracle@oravm19c-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 1 10:22:00 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         5 PDB2                           READ WRITE NO

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> create user C##remoteuser identified by remote_user;

User created.

SQL> grant create session, sysoper, create pluggable database to C##remoteuser container=all;

Grant succeeded.

2) Checking if SOURCE CDB is on LOCAL UNDO mode & whether ARCHIVE MODE is ENABLED or not.

SQL> set lines 200 pages 200
SQL> col property_name for a40;
SQL> col property_value for a40;
SQL> select property_name, property_value from database_properties where property_name= 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                            PROPERTY_VALUE
---------------------------------------- ----------------------------------------
LOCAL_UNDO_ENABLED                       TRUE


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
Oldest online log sequence     16
Current log sequence           18

So database is in NO ARCHIVE mode, which means we'll have to switch PDB to READ ONLY mode. With LOCAL_UNDO_ENABLED and ARCHIVE LOG ENABLED, we can relocate PDB in READ WRITE mode.


3) Open PDB in read-only mode.

SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> alter pluggable database PDB1 open READ ONLY;

Pluggable database altered.

4) Make TNS entries on TARGET server for DB link creation.

In TNSNAMES.ORA

CDB12 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oravm19c-2.practice.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1)
    )
  )

LISTENER_PDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oravm19c.practice.com)(PORT = 1521))


In LISTENER.ORA

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cdb1)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = cdb1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pdb2)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = pdb2.practice.com)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pdb1)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = pdb1.practice.com)
    )
  )

5) Create Database Link in TARGET CDB.

SQL> set lines 200 pages 200;
SQL> select owner, db_link, username, host, created from dba_db_links;

OWNER                                    DB_LINK                        USERNAME                       HOST                           CREATED
---------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------
SYS                                      SYS_HUB                                                       SEEDDATA                       17-APR-19
SYS                                      DBLINK_PDB_CLONE.PRACTICE.COM  CL_USER                        pdb1                           12-JAN-25

SQL> drop database link DBLINK_PDB_CLONE;

Database link dropped.

SQL> create public database link DBLINK_PDB_CLONE connect to C##remoteuser identified by remote_user using 'cdb12';

Database link created.

6) You can now relocate/migrate your PDB.

Note: I am not using Oracle Managed Files, hence I need to specify FILE_NAME_CONVERT parameter. If you are using OMF, you don't need to use this parameter.

SQL> create pluggable database PDB1 from PDB1@DBLINK_PDB_CLONE relocate file_name_convert=('/u01/app/oracle/oradata/CDB1','/u01/app/oracle/oradata/CDB1/PDB');

Pluggable database created.

7) Verify your relocated PDB and see if it is opening properly.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         5 PDB2                           READ WRITE NO

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         5 PDB2                           READ WRITE NO

8) At this point, PDB1 is also removed from SOURCE CDB, which indicates the successful relocation of PDB1.

[oracle@oravm19c-2 CDB1]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 1 11:06:22 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDB2                           MOUNTED

I hope this helps !!

No comments:

Post a Comment