HOW TO RELOCATE PDB WITH NEAR ZERO DOWNTIME IN ORACLE MULTITENANT

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.