HOW TO RELOCATE PDB WITH NEAR ZERO DOWNTIME IN ORACLE MULTITENANT
Local – staxdb ; Remote – orcl
PDB relocate is one of the efficient way to migrate a PDB without any hassle from one CDB to another. This method acts like a switchover mechanism in a dataguard.
Advantages:
- Zero or minimal downtime
- Faster than other methods
Conditions:
- Source PDB can be accessed by the application during the activity.
- Local and remote cannot be active at the same time. Either of these should be active.
- Existing client connections are redirected to the newly relocated PDB in remote CDB
- Both local and remote PDB should be of same version and release. Eg: l – 19.15.0.0..0 & r – 19.15.0.0.0 is valid and l – 19.3.0.0.0 & r – 19.15.0.0.0 is not valid
- Source should be local undo enabled

Working:
All the database blocks are copied with undo and redo change vectors from local to remote pdb which is why it can also be called block by block relocation
Local PDB:
First most important one to verify is the local undo management and archive log enabled for the source PDB to be read write mode. If source PDB is not ready for a downtime, then archive log should be enabled. If archive log’s are disabled, then source PDB should be in read only mode during relocation.
Local undo is mandatory for cloning or moving PDB’s, These are the activity which require local undo in general,
- PDB relocate
- Proxy PDB’s
- PDB Hot clone
- Refreshable PDB clone
Steps:
Check the database status and open mode for the PDB to be relocated. Also make sure that, remote CDB has max_pdbs parameter with a high value to n + 1 if there are n PDBS in remote CDB. Eg: if max_pdbs = 5 and there are 5 PDB’s in the remote CDB, then a new PDB cannot be relocated because of limit. Default value is 254.
kIsH@STAX<>select name,open_mode from v$pdbs;
NAME OPEN_MODE
-------------------- --------------------
PDB$SEED READ ONLY
PDB_PROXY2 READ WRITE
PDBSTX1 READ WRITE <== PDB to be relocated
PROX1 MOUNTED
kIsH@STAX<>col pdb_name for a20
kIsH@STAX<>col status for a20
kIsH@STAX<>select pdb_name,status from dba_pdbs;
PDB_NAME STATUS
-------------------- --------------------
PROX1 NORMAL
PDB$SEED NORMAL
PDB_PROXY2 NORMAL
PDBSTX1 NORMAL
kIsH@STAX<>show parameter max_pdb
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_pdbs integer 254
Ensure that local undo is enabled and archive log are generated in staxdb
kIsH@STAX<>select property_name,property_value from database_properties where property_name like '%UNDO%';
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE
kIsH@STAX<>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
check the source PDB’s in staxdb
kIsH@STAX<>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBSTX READ WRITE NO
4 PROX1 MOUNTED
Save the states and services of all the PDB’s so that once the relocation is complete, all the services are automatically up without manual intervention in staxdb
--Standalone
kIsH@STAX<>alter pluggable database all save state;
Pluggable database altered.
--RAC
kIsH@STAX<>alter pluggable database all save state instances=all;
Pluggable database altered.
Check if all the below grants are in place for the user from which the PDB is relocated in staxdb
kIsH@STAX<>create user c##proxy identified by password default tablespace users temporary tablespace temp quota unlimited on users account unlock profile default;
User created.
kIsH@STAX<>grant create pluggable database, sysoper, create session to c##proxy container=all;
Grant succeeded.
Create a database link from remote CDB(orcl) to local CDB(staxdb)
kIsH@STAX<>create database link relpdb connect to c##proxy identified by password using 'staxdb';
Execute a sample sql to test if the sessions are relocated or failed
kIsH@STAX<>conn sys/password@pdbstx as sysdba
Connected.
--Session 1
kIsH@STAX<>select * from dba_source
--Session 2
kIsH@STAX<>select sid,status,sql_id,command from v$session where command=3 and sid=78;
SID STATUS SQL_ID COMMAND
---------- -------- ------------- ----------
78 ACTIVE 2u4hgbgzxc9td 3
kIsH@STAX<>select sql_text from v$sql where sql_id='g0aywfbqsj573';
SQL_TEXT
--------------------------------------------------------------------------------
select * from dba_source
From remote CDB, create the PDB with RELOCATE clause. FILE_NAME_CONVERT clause is not required if OMF is enabled. All the services from source PDB are cleaned up in local PDB and redirected to remote PDB.
kIsH@STAX<>show parameter db_create_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/ORCL/
kIsH@STAX<>create pluggable database pdbstx1 from pdbstx@relpdb relocate;
Pluggable database created.
In background, oracle does the following,
- Change the endian to little for staxdb
- New PDB is created as unusable in orcl
- Perform media recovery by applying the redo changes
- Pseudo open the PDB
- Drains all the active sessions from local PDB
- Undo initialization in remote PDB
- Delete old datafiles and add new datafiles
- Create PDB in remote CDB orcl in mount state
- Delete the local PDB from staxdb after remote PDB is opened
create pluggable database pdbstx1 from pdbstx@relpdb relocate
2022-10-09T13:07:20.437032+05:30
PDBSTX1(3):Endian type of dictionary set to little
****************************************************************
Pluggable Database PDBSTX1 with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
Applying media recovery for pdb-4099 from SCN 119082993 to SCN 119083009
Remote log information: count-1
thr-1,seq-34,logfile-/apps01/RECO/STAXDB/foreign_archivelog/PDBSTX/2022_10_09/o1_mf_1_34_kn4ykk5k_.arc,los-119047889,nxs-18446744073709551615,maxblks-234273
PDBSTX1(3):Media Recovery Start
2022-10-09T13:07:21.354400+05:30
PDBSTX1(3):Serial Media Recovery started
PDBSTX1(3):max_pdb is 9
2022-10-09T13:07:21.378106+05:30
PDBSTX1(3):Media Recovery Log /apps01/RECO/STAXDB/foreign_archivelog/PDBSTX/2022_10_09/o1_mf_1_34_kn4ykk5k_.arc
2022-10-09T13:07:21.567209+05:30
PDBSTX1(3):Incomplete Recovery applied until change 119083009 time 10/09/2022 13:07:20
2022-10-09T13:07:21.575712+05:30
PDBSTX1(3):Media Recovery Complete (orcl)
Completed: create pluggable database pdbstx1 from pdbstx@relpdb relocate
2022-10-09T13:08:16.999349+05:30
alter pluggable database PDBSTX1 open
2022-10-09T13:08:19.240224+05:30
Applying media recovery for pdb-4099 from SCN 119083009 to SCN 119084362
Remote log information: count-1
thr-1,seq-34,logfile-/apps01/RECO/STAXDB/foreign_archivelog/PDBSTX/2022_10_09/o1_mf_1_34_kn4ymbxm_.arc,los-119047889,nxs-18446744073709551615,maxblks-235720
PDBSTX1(3):Media Recovery Start
2022-10-09T13:08:19.240586+05:30
PDBSTX1(3):Serial Media Recovery started
PDBSTX1(3):max_pdb is 9
2022-10-09T13:08:19.321041+05:30
PDBSTX1(3):Media Recovery Log /apps01/RECO/STAXDB/foreign_archivelog/PDBSTX/2022_10_09/o1_mf_1_34_kn4ymbxm_.arc
2022-10-09T13:08:19.839894+05:30
PDBSTX1(3):Incomplete Recovery applied until change 119084362 time 10/09/2022 13:08:17
2022-10-09T13:08:19.863573+05:30
PDBSTX1(3):Media Recovery Complete (orcl)
PDBSTX1(3):Pluggable database PDBSTX1 opening in read only
2022-10-09T13:08:20.482718+05:30
PDBSTX1(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDBSTX1(3):Autotune of undo retention is turned on.
PDBSTX1(3):Undo initialization finished serial:0 start:15362996 end:15362996 diff:0 ms (0.0 seconds)
PDBSTX1(3):Database Characterset for PDBSTX1 is AL32UTF8
2022-10-09T13:08:21.389189+05:30
PDBSTX1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2022-10-09T13:08:21.806990+05:30
PDBSTX1(3):Opening pdb with no Resource Manager plan active
2022-10-09T13:08:30.221921+05:30
PDBSTX1(3):Pluggable database PDBSTX1 closing
PDBSTX1(3):JIT: pid 17516 requesting stop
PDBSTX1(3):Buffer Cache flush started: 3
PDBSTX1(3):Buffer Cache flush finished: 3
2022-10-09T13:08:34.350778+05:30
Applying media recovery for pdb-4099 from SCN 119084362 to SCN 119084497
Remote log information: count-1
thr-1,seq-34,logfile-/apps01/RECO/STAXDB/foreign_archivelog/PDBSTX/2022_10_09/o1_mf_1_34_kn4ymt42_.arc,los-119047889,nxs-18446744073709551615,maxblks-235992
PDBSTX1(3):Media Recovery Start
2022-10-09T13:08:34.351170+05:30
PDBSTX1(3):Serial Media Recovery started
PDBSTX1(3):max_pdb is 9
2022-10-09T13:08:34.419099+05:30
PDBSTX1(3):Media Recovery Log /apps01/RECO/STAXDB/foreign_archivelog/PDBSTX/2022_10_09/o1_mf_1_34_kn4ymt42_.arc
2022-10-09T13:08:34.766382+05:30
PDBSTX1(3):Incomplete Recovery applied until change 119084497 time 10/09/2022 13:08:31
2022-10-09T13:08:34.794694+05:30
PDBSTX1(3):Media Recovery Complete (orcl)
PDBSTX1(3):Pluggable database PDBSTX1 pseudo opening
PDBSTX1(3):Undo initialization recovery: Parallel FPTR complete: start:15377356 end:15377357 diff:1 ms (0.0 seconds)
PDBSTX1(3):Undo initialization recovery: err:0 start: 15377356 end: 15377357 diff: 1 ms (0.0 seconds)
PDBSTX1(3):[17516] Successfully onlined Undo Tablespace 2.
PDBSTX1(3):Undo initialization online undo segments: err:0 start: 15377357 end: 15377378 diff: 21 ms (0.0 seconds)
PDBSTX1(3):Undo initialization finished serial:0 start:15377356 end:15377384 diff:28 ms (0.0 seconds)
PDBSTX1(3):Database Characterset for PDBSTX1 is AL32UTF8
PDBSTX1(3):Pluggable database PDBSTX1 pseudo closing
PDBSTX1(3):Closing sequence subsystem (15377430677).
PDBSTX1(3):Buffer Cache flush started: 3
PDBSTX1(3):Buffer Cache flush finished: 3
2022-10-09T13:08:35.415790+05:30
PDBSTX1(3):Pluggable database PDBSTX1 opening in read write
PDBSTX1(3):Undo initialization recovery: Parallel FPTR complete: start:15377871 end:15377872 diff:1 ms (0.0 seconds)
PDBSTX1(3):Undo initialization recovery: err:0 start: 15377871 end: 15377873 diff: 2 ms (0.0 seconds)
PDBSTX1(3):[17516] Successfully onlined Undo Tablespace 2.
PDBSTX1(3):Undo initialization online undo segments: err:0 start: 15377873 end: 15377922 diff: 49 ms (0.0 seconds)
PDBSTX1(3):Undo initialization finished serial:0 start:15377871 end:15377923 diff:52 ms (0.1 seconds)
PDBSTX1(3):Deleting old file#25 from file$
PDBSTX1(3):Deleting old file#26 from file$
PDBSTX1(3):Deleting old file#27 from file$
PDBSTX1(3):Adding new file#90 to file$(old file#25). fopr-1, newblks-48640, oldblks-19200
PDBSTX1(3):Adding new file#91 to file$(old file#26). fopr-1, newblks-58880, oldblks-15360
PDBSTX1(3):Adding new file#92 to file$(old file#27). fopr-1, newblks-29440, oldblks-12800
PDBSTX1(3):Successfully created internal service PDBSTX1 at open
****************************************************************
Post plug operations are now complete.
Pluggable database PDBSTX1 with pdb id - 3 is now marked as NEW.
****************************************************************
PDBSTX1(3):Pluggable database PDBSTX1 dictionary check beginning
2022-10-09T13:08:35.868945+05:30
PDBSTX1(3):Pluggable Database PDBSTX1 Dictionary check complete
PDBSTX1(3):Database Characterset for PDBSTX1 is AL32UTF8
PDBSTX1(3):JIT: pid 17516 requesting full stop
2022-10-09T13:08:37.055843+05:30
Violations: Type: 2, Count: 3
PDBSTX1(3):***************************************************************
PDBSTX1(3):WARNING: Pluggable Database PDBSTX1 with pdb id - 3 is
PDBSTX1(3): altered with errors or warnings. Please look into
PDBSTX1(3): PDB_PLUG_IN_VIOLATIONS view for more details.
PDBSTX1(3):***************************************************************
PDBSTX1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x0, new 0x0 (no suplog)
PDBSTX1(3):Opening pdb with no Resource Manager plan active
PDBSTX1(3):joxcsys_required_dirobj_exists: directory object exists with required path /u01/app/oracle/product/19.0.0/dbhome_1/javavm/admin/, pid 17516 cid 3
2022-10-09T13:08:38.972159+05:30
Pluggable database PDBSTX1 opened read write
Completed: alter pluggable database PDBSTX1 open
After relocation of the PDB, if another PDB is tried to be created, then it fails due to existing GUID
kIsH@STAX<>create pluggable database pdbstx from pdbstx@relpdb relocate;
create pluggable database pdbstx from pdbstx@relpdb relocate
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing
container.
Open the remote relocated PDB for normal application operations. The moment the remote PDB is opened, local PDB is removed.
kIsH@STAX<>alter pluggable database PDBSTX1 open;
Pluggable database altered.
kIsH@STAX<>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB_PROXY2 READ WRITE NO
6 PDBSTX1 READ WRITE NO
ORACLE_SID = [orcl] ? staxdb <== local CDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@staxhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 7 19:17:46 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
kIsH@STAX<>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PROX1 MOUNTED
*** local PDB PDBSTX is dropped
Transactions which were active during the relocation stage are failed in local PDB
ERROR:
ORA-01089: immediate shutdown or close in progress - no operations are
permitted
Process ID: 18895
Session ID: 81 Serial number: 32847
152955 rows selected.