Today Roy and I received a question from a colleague about the RELOCATE feature in Multitenant. And I’d like to shed some light and this topic a bit more. Upgrade PDBs with RELOCATE – How does it work?
Photo by Sebastian Pena Lambarri on Unsplash
RELOCATE and UPGRADE?
At first, it looks very promising to me. RELOCATE uses the HOT CLONE functionality of Multitenant but then relocates the PDB from one to another CDB. You need to fulfill a few important requirements, for instance:
- Local Undo must be enabled in both CDBs
- This limits the technique to at least Oracle 12.2.0.1 as a source CDB
- Both CDBs need to operate in ARCHIVELOG mode
- The source needs a lower or equal COMPATIBLE parameter than the destination CDB
- I recommend to keep COMPATIBLE equal among your CDBs on the lowest common setting
If you’d like to learn more about the RELOCATE feature and its options, please see this documentation link:
How does it work [or not]?
Actually I use my previous blog post about patching and relocation as a blueprint example:
So let’s start with my typical Hands-On Lab setup with CDB1 as source in Oracle 12.2.0.1, CDB2 as target in Oracle 19.6.0.
Before I can start, I will need to prepare a few things:
| CDB1 | CDB2 |
Create a common user for the relocate operation and assign necessary privileges:
CREATE USER c##borg IDENTIFIED BY oracle DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp CONTAINER=ALL; GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SYSOPER TO c##borg CONTAINER = ALL; |
|
Create the public database link for the relocate operation:
CREATE public DATABASE link clonemypdb CONNECT TO c##borg IDENTIFIED BY oracle USING 'CDB1'; |
|
|
Initiate the relocation: CREATE PLUGGABLE DATABASE PDB3 FROM PDB3@clonemypdb RELOCATE AVAILABILITY MAX file_name_convert=('CDB1','CDB2');
|
|
CREATE PLUGGABLE DATABASE PDB3 FROM PDB3@clonemypdb RELOCATE AVAILABILITY MAX file_name_convert=('CDB1','CDB2')*ERROR at line 1:ORA-65188: cannot relocate a PDB opened with warnings |
Ouch!
What is going wrong here?
First of all, the error ORA-65188: cannot relocate a PDB opened with warnings does not really tell me what the problem is. In fact, it leads me into the wrong direction. It seems to be the case that the error happens as a result from trying to open the PDB3 in CDB2. But there is no PDB3 in CDB2 visible. And PDB3 in CDB1 is happy and alive, open unrestricted.
MOS may help. It does. I receive exactly one result with the error:
Somebody must have tried this already, and failed. The note gives an understandable explanation:
- AVAILABILITY MAX means that we don’t want customer to get any downtime. Upgrade can be time consuming, and it is a manual action currently.
So AVAILABILITY MAX will still be a downtime for customer application. Hence, we do not allow AVAILABILITY MAX during relocate from lower version to higher version
So my approach was wrong.
How to do it the right way
I repeat my case from above with one important difference.
Then I need to prepare a few things:
| CDB1 | CDB2 |
Create a common user for the relocate operation and assign necessary privileges:
CREATE USER c##borg IDENTIFIED BY oracle DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp CONTAINER=ALL; GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SYSOPER TO c##borg CONTAINER = ALL; |
|
Create the public database link for the relocate operation:
CREATE public DATABASE link clonemypdb CONNECT TO c##borg IDENTIFIED BY oracle USING 'CDB1'; |
|
|
Initiate the relocation: CREATE PLUGGABLE DATABASE PDB3 FROM PDB3@clonemypdb RELOCATE file_name_convert=('CDB1','CDB2');
|
|
|
Open the PDB: ALTER PLUGGABLE DATABASE PDB3 OPEN UPGRADE;
The PDB needs to be opened in upgrade mode as it is a 12.2.0.1 PDB in a 19.6.0 CDB |
|
On the command line simply issue:
$ dbupgrade -c "PDB3" -l /home/oracle This will upgrade now PDB3 to 19.6.0, too. |
Just in case you are interested, this is the output from the database upgrade:
$ dbupgrade -c "PDB3" -l /home/oracle
Argument list for [/u01/app/oracle/product/19/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = PDB3
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.6.0.0.0DBRU_LINUX.X64_191217]
/u01/app/oracle/product/19/rdbms/admin/orahome = [/u01/app/oracle/product/19]
/u01/app/oracle/product/19/bin/orabasehome = [/u01/app/oracle/product/19]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19]
Analyzing file /u01/app/oracle/product/19/rdbms/admin/catupgrd.sql
Log file directory = [/home/oracle]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/catupgrd_catcon_15691.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = CDB2
DataBase Version = 19.0.0.0.0
PDB Parallel SQL Process Count = [2] is higher or equal to CPU Count = [2]
Concurrent PDB Upgrades defaulting to CPU Count [2]
Parallel SQL Process Count (PDB) = 2
Parallel SQL Process Count (CDB$ROOT) = 4
Concurrent PDB Upgrades = 2
Generated PDB Inclusion:[PDB3]
CDB$ROOT Open Mode = [OPEN]
Concurrent PDB Upgrades Reset = 1
Start processing of PDBs (PDB3)
[/u01/app/oracle/product/19/perl/bin/perl /u01/app/oracle/product/19/rdbms/admin/catctl.pl -c 'PDB3' -l /home/oracle -I -i pdb3 -n 2 /u01/app/oracle/product/19/rdbms/admin/catupgrd.sql]
Argument list for [/u01/app/oracle/product/19/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = PDB3
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = pdb3
Child Process I = 1
Log Dir l = /home/oracle
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 2
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.6.0.0.0DBRU_LINUX.X64_191217]
/u01/app/oracle/product/19/rdbms/admin/orahome = [/u01/app/oracle/product/19]
/u01/app/oracle/product/19/bin/orabasehome = [/u01/app/oracle/product/19]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19]
Analyzing file /u01/app/oracle/product/19/rdbms/admin/catupgrd.sql
Log file directory = [/home/oracle]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/catupgrdpdb3_catcon_15957.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrdpdb3*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrdpdb3_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = CDB2
DataBase Version = 19.0.0.0.0
PDB3 Open Mode = [MIGRATE]
Generated PDB Inclusion:[PDB3]
CDB$ROOT Open Mode = [OPEN]
Components in [PDB3]
Installed [CATALOG CATPROC OWM XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM RAC SDO WK XML XOQ]
------------------------------------------------------
Phases [0-107] Start Time:[2020_01_28 22:28:40]
Container Lists Inclusion:[PDB3] Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [PDB3] Files:1 Time: 20s
*************** Catalog Core SQL ***************
Serial Phase #:1 [PDB3] Files:5 Time: 45s
Restart Phase #:2 [PDB3] Files:1 Time: 2s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [PDB3] Files:19 Time: 25s
Restart Phase #:4 [PDB3] Files:1 Time: 2s
************* Catalog Final Scripts ************
Serial Phase #:5 [PDB3] Files:7 Time: 19s
***************** Catproc Start ****************
Serial Phase #:6 [PDB3] Files:1 Time: 14s
***************** Catproc Types ****************
Serial Phase #:7 [PDB3] Files:2 Time: 13s
Restart Phase #:8 [PDB3] Files:1 Time: 2s
**************** Catproc Tables ****************
Parallel Phase #:9 [PDB3] Files:67 Time: 34s
Restart Phase #:10 [PDB3] Files:1 Time: 2s
************* Catproc Package Specs ************
Serial Phase #:11 [PDB3] Files:1 Time: 56s
Restart Phase #:12 [PDB3] Files:1 Time: 2s
************** Catproc Procedures **************
Parallel Phase #:13 [PDB3] Files:94 Time: 11s
Restart Phase #:14 [PDB3] Files:1 Time: 2s
Parallel Phase #:15 [PDB3] Files:121 Time: 15s
Restart Phase #:16 [PDB3] Files:1 Time: 2s
Serial Phase #:17 [PDB3] Files:22 Time: 8s
Restart Phase #:18 [PDB3] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [PDB3] Files:32 Time: 21s
Restart Phase #:20 [PDB3] Files:1 Time: 1s
Serial Phase #:21 [PDB3] Files:3 Time: 15s
Restart Phase #:22 [PDB3] Files:1 Time: 2s
Parallel Phase #:23 [PDB3] Files:25 Time: 187s
Restart Phase #:24 [PDB3] Files:1 Time: 0s
Parallel Phase #:25 [PDB3] Files:12 Time: 113s
Restart Phase #:26 [PDB3] Files:1 Time: 2s
Serial Phase #:27 [PDB3] Files:1 Time: 0s
Serial Phase #:28 [PDB3] Files:3 Time: 8s
Serial Phase #:29 [PDB3] Files:1 Time: 0s
Restart Phase #:30 [PDB3] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [PDB3] Files:1 Time: 7s
Restart Phase #:32 [PDB3] Files:1 Time: 1s
Serial Phase #:34 [PDB3] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [PDB3] Files:294 Time: 20s
Serial Phase #:36 [PDB3] Files:1 Time: 0s
Restart Phase #:37 [PDB3] Files:1 Time: 1s
Serial Phase #:38 [PDB3] Files:6 Time: 8s
Restart Phase #:39 [PDB3] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [PDB3] Files:3 Time: 46s
Restart Phase #:41 [PDB3] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [PDB3] Files:13 Time: 117s
Restart Phase #:43 [PDB3] Files:1 Time: 2s
Parallel Phase #:44 [PDB3] Files:11 Time: 11s
Restart Phase #:45 [PDB3] Files:1 Time: 0s
Parallel Phase #:46 [PDB3] Files:3 Time: 7s
Restart Phase #:47 [PDB3] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [PDB3] Files:1 Time: 14s
Restart Phase #:49 [PDB3] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [PDB3] Files:1 Time: 8s
************ Upgrade Component Start ***********
Serial Phase #:51 [PDB3] Files:1 Time: 7s
Restart Phase #:52 [PDB3] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [PDB3] Files:2 Time: 21s
***************** Upgrading XDB ****************
Restart Phase #:54 [PDB3] Files:1 Time: 0s
Serial Phase #:56 [PDB3] Files:3 Time: 12s
Serial Phase #:57 [PDB3] Files:3 Time: 9s
Parallel Phase #:58 [PDB3] Files:10 Time: 10s
Parallel Phase #:59 [PDB3] Files:25 Time: 11s
Serial Phase #:60 [PDB3] Files:4 Time: 14s
Serial Phase #:61 [PDB3] Files:1 Time: 0s
Serial Phase #:62 [PDB3] Files:32 Time: 10s
Serial Phase #:63 [PDB3] Files:1 Time: 0s
Parallel Phase #:64 [PDB3] Files:6 Time: 12s
Serial Phase #:65 [PDB3] Files:2 Time: 20s
Serial Phase #:66 [PDB3] Files:3 Time: 26s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [PDB3] Files:1 Time: 1s
Serial Phase #:69 [PDB3] Files:1 Time: 8s
Parallel Phase #:70 [PDB3] Files:2 Time: 10s
Restart Phase #:71 [PDB3] Files:1 Time: 1s
Parallel Phase #:72 [PDB3] Files:2 Time: 9s
Serial Phase #:73 [PDB3] Files:2 Time: 9s
***************** Upgrading SDO ****************
Restart Phase #:74 [PDB3] Files:1 Time: 1s
Serial Phase #:76 [PDB3] Files:1 Time: 8s
Serial Phase #:77 [PDB3] Files:2 Time: 9s
Restart Phase #:78 [PDB3] Files:1 Time: 1s
Serial Phase #:79 [PDB3] Files:1 Time: 8s
Restart Phase #:80 [PDB3] Files:1 Time: 2s
Parallel Phase #:81 [PDB3] Files:3 Time: 9s
Restart Phase #:82 [PDB3] Files:1 Time: 2s
Serial Phase #:83 [PDB3] Files:1 Time: 8s
Restart Phase #:84 [PDB3] Files:1 Time: 2s
Serial Phase #:85 [PDB3] Files:1 Time: 8s
Restart Phase #:86 [PDB3] Files:1 Time: 2s
Parallel Phase #:87 [PDB3] Files:4 Time: 10s
Restart Phase #:88 [PDB3] Files:1 Time: 1s
Serial Phase #:89 [PDB3] Files:1 Time: 9s
Restart Phase #:90 [PDB3] Files:1 Time: 0s
Serial Phase #:91 [PDB3] Files:2 Time: 9s
Restart Phase #:92 [PDB3] Files:1 Time: 1s
Serial Phase #:93 [PDB3] Files:1 Time: 7s
Restart Phase #:94 [PDB3] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [PDB3] Files:1 Time: 7s
Restart Phase #:96 [PDB3] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [PDB3] Files:1 Time: 8s
************* Final Upgrade scripts ************
Serial Phase #:98 [PDB3] Files:1 Time: 117s
******************* Migration ******************
Serial Phase #:99 [PDB3] Files:1 Time: 6s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [PDB3] Files:1 Time: 7s
Serial Phase #:101 [PDB3] Files:1 Time: 5s
Serial Phase #:102 [PDB3] Files:1 Time: 5s
***************** Post Upgrade *****************
Serial Phase #:103 [PDB3] Files:1 Time: 16s
**************** Summary report ****************
Serial Phase #:104 [PDB3] Files:1 Time: 7s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [PDB3] Files:1 Time: 6s
Serial Phase #:106 [PDB3] Files:1 Time: 4s
Serial Phase #:107 [PDB3] Files:1 Time: 0s
------------------------------------------------------
Phases [0-107] End Time:[2020_01_28 22:51:09]
Container Lists Inclusion:[PDB3] Exclusion:[NONE]
------------------------------------------------------
Grand Total Time: 1349s [PDB3]
LOG FILES: (/home/oracle/catupgrdpdb3*.log)
Upgrade Summary Report Located in:
/home/oracle/upg_summary.log
Time: 1423s For PDB(s)
Grand Total Time: 1423s
LOG FILES: (/home/oracle/catupgrd*.log)
Grand Total Upgrade Time: [0d:0h:23m:43s]
What happened in the source?
I used the RELOCATE feature. But is this something you should do? And what is the actual benefit of it?
One thing is very straight forward: You don’t deal with an unplug/plug command anymore. The database does it all for you. Not bad actually. I like this idea.
But … well, there is another side to look at – the source CDB1. Let us compare BEFORE/AFTER:
- Before:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB3 READ WRITE NO - After:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO
So my PDB3 has disappeared from CDB1. Of course, it has – as otherwise this wouldn’t be RELOCATE.
Is this good or bad? I’m not 100% convinced yet but in my understanding, the feature used underneath is the CLONE operation. RELOCATE won’t drop the PDB from source as long as it appears in the destination CDB.
But if you have different ideas on how to break it, let me know. I tried several things but either the PDB stayed healthy in the source, or it appeared in the destination.
More Information and Links
- Upgrade Testing – Online Clone to Oracle 19c
- Oracle 19c Documentation – Relocation a Pluggable Database
- Upgrade Blog: Save Patching Downtime with Oracle Multitenant?
- MOS Note:2551092.1 – ORA-65188 on RELOCATE PDB with AVAILABILITY MAX from LOWER version to HIGHER version
–Mike
At what point during the procedure does the source PDB become unavailable?
This is interesting to know to calculate the downtime..
Hi Jan,
as far as I can see from the point where the PDB is fully intact on the destination side. We ship redo in order to age out dirty blocks, then we apply undo in order to rollback uncommited transactions. From this point on, the PDB_DEST is ok – and the PDB_SOURCE becomes unavailable.
But I didn’t dig into the alert.logs to verify the exact point in time.
Cheers,
Mike
Hi Mike,
just testing this feature.
I’ve observed that source PDB will unplugged and their datafiles will not dropped until performing “ALTER PLUGGABLE DATABASE OPEN UPGRADE” on destination.
One question: What happens if the dbupgrade fails and the upgraded PDB keeps in invalid state. The source PDB is dropped and I’m not sure if there is a flashback possible on destination site because of inclomplete upgrade.
The only way to restore is a PITR from regular backup on source.
My feeling says it would be better to clone instead of relocate a PDB for upgrading.
I’m not sure, Mike.
Cheers Peter
Hi Peter,
as you know, the upgrade never fails 🙂 🙂 🙂
Interesting question you have. Let me elaborate this more …
Cheers,
Mike
Hello Mike,
I went to relocate for migration purposes PDB from CDB1@rac1 to cdb2@rac2 (different infrastructures).
The difficulty for me is – both are in ASM and use OMF. The documentation is not very verbose about these ASM and OMF cases.
I tried all combinations of the various file naming parameters (create_file_dest, pdb_file_name_convert, db_create_file_dest und file_name_convert) – no success.
Either it complais about missig source diskgroup on target cluster (ORA 19505) or in complains with ORA 1276 (file is an OMF).
Can you point me to some documentation where this situation is described?
Or is it not possible at all?
Thank you
Matthias
Hi Matthias,
on which release are you right now (exactly please)?
Cheers,
Mike
Hi Mike,
30557433;Database Release Update : 19.6.0.0.200114 (30557433)
Hi Matthias,
I was asking because this used to fail before 19.6.0 – but with 19.6.0 you should be on the safe side.
What exactly is failing – the:
CREATE PLUGGABLE DATABASE PDB3 FROM PDB3@clonemypdb RELOCATE file_name_convert=(‘+DATA/dbname/sales’, ‘+DATA/dbname/depsales’);
Thanks,
Mike
Ciao Mike,
exactly. With and without file_name_convert the procedure complains about non existing Source Diskgroup on target Cluster, which is of course true. Diskgroup names and db_unique_names of source / target cluster and source / target CDB are each different.
Matthias
Hi Matthias,
i’d really need the entire code piece you are using – and the errors you receive. Best option would be to drop this into an SR.
Cheers,
Mike
Good Morning Mike,
as per your advice I was on the way to open SR – first step suggested that note:
create PDB using dblink with encryption enable resulting in ORA-15001 (Doc ID 2542003.1)
and – yes I hit (of course) this bug 🙁
Relocation works like a charm without encryption (Sorry, I forgot to mention that because the error message was in no way suggesting it might be due to TDE enabled in the source PDB).
For us as customers it is sometimes frustrating to stumble over bugs everywhere 🙁
Sorry for that.
Anyway, thanks for your support – as always :-).
Matthias
Hi Matthias,
I recommended already internally to use TDE everywhere to avoid such issues. I’m pretty certain why this is happening. Thanks for sharing the MOS note
Hi Mike,
I tried to relocate a pdb. The clone did happen and I was able to opened the pdb in the target CDB. However, I found that the pdb is still in the source CDB in a MOUNT state. Is that expected behavior? I expect the pdb should be removed or dropped from the source CDB with the relocate command.
Command I used:
CREATE PLUGGABLE DATABASE FOO FROM FOO@cdb1_relocate keystore identified by “” RELOCATE AVAILABILITY MAX;
In my understanding – and this is what I wrote in the blog post as well – it should not be there anymore once the process has been completed successfully. Please check the alert.log of the source whether there had been an issue/error. And then you may need to open an SR please.
Thanks,
Mike