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