Pluggable Database (PDB) Migration & Upgrade from 12c to 19c using Remote Clone

In a recent blog, we demonstrated Pluggable Database (PDB) Migration & Upgrade from 12c to 19c using Full Transportable Tablespace Export-Import Click here to read more. In this blog, we will see steps for How we can migrate PDB from Window OS into Linux CDB and upgrade from 12C to 19C using Remote Clone.

Below are the high-level steps we will follow to perform Migration & Upgrade
1. Create common user in Source CDB
2. Create a DBLink in Target CDB
3. Initiate PDB Clone Command in Target CDB
4. Initiate migrated PDB upgrade to 19C
5. Run utlrp.sql to compile invalid objects
6. Verify upgrade

Below are setup details and the same will be used in this demonstration.

Sr. No.	OS	Hostname	IP	CDB@PDB	DB Version
1	Window	test-machine01	192.168.114.177	CDB1@MSGUSER	12.1.0.2.0
2	Oracle Linux	test-machine02	192.168.114.176	CDB2@MSGUSER	19.3.0.0.0

Step 1. Create common user in Source CDB: Create common user c##dblink for remote clone operation and assign required privileges along with identify tablespace to assign default tablespace to common users.

C:\Users\jkhan>
C:\Users\jkhan>sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 13 09:56:11 2022

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  select instance_name, con_id, version from v$instance;

INSTANCE_NAME        CON_ID VERSION
---------------- ---------- -----------------
cdb1                   0 12.1.0.2.0

SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MSGUSER                        READ WRITE NO

SQL>
SQL> CREATE USER c##dblink IDENTIFIED BY oracle DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp CONTAINER=ALL;
CREATE USER c##dblink IDENTIFIED BY oracle DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp CONTAINER=ALL
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database MSGUSER
ORA-00959: tablespace 'USERS' does not exist
SQL> show user
USER is "SYS"
SQL>
SQL> alter session set container=MSGUSER;

Session altered.

SQL>
SQL> show con_name

CON_NAME
------------------------------
MSGUSER
SQL>
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
MSGUSER

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
H:\CDB1\ORADATA_2\UNDOTBS01.DBF
G:\CDB1\ORADATA_2\MSGUSER\SYSTEM01.DBF
G:\CDB1\ORADATA_2\MSGUSER\SYSAUX01.DBF
H:\CDB1\ORADATA_2\MSGUSER\MSGUSER_01.DBF
H:\CDB1\ORADATA_2\MSGUSER\MSGUSER_02.DBF

SQL> create tablespace USERS datafile 'G:\CDB1\ORADATA_2\MSGUSER\USERS1.DBF' size 100M autoextend on maxsize unlimited;

Tablespace created.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
MSGUSER
USERS

SQL>


SQL> alter session set container=CDB$ROOT;

Session altered.
SQL>
SQL> CREATE USER c##dblink IDENTIFIED BY oracle DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp CONTAINER=ALL;

User created.

SQL>

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SYSOPER TO c##dblink CONTAINER = ALL;

Grant succeeded.

SQL>

Step 2. Create DBLink in Target CDB: Add below PDB_MIG tnsname of source CDB in tnsnames.ora file. Use same tnsname to create dblink.

[oracle@test-machine02 admin]$ pwd
/u01/app/oracle/product/19.0.0/db_1/network/admin
[oracle@test-machine02 admin]$ vi tnsnames.ora
PDB_MIG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )

:wq!

[oracle@test-machine02 admin]$
[oracle@test-machine02 admin]$ tnsping PDB_MIG

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2022 14:04:28

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

Used parameter files:
/u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1)))
OK (40 msec)
[oracle@test-machine02 admin]$
[oracle@test-machine02 ~]$
[oracle@test-machine02 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 6 14:04:42 2022
Version 19.3.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> show con_name

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

SQL>
SQL> CREATE public DATABASE link PDB_MIG CONNECT TO c##dblink IDENTIFIED BY oracle USING 'PDB_MIG';

Database link created.

SQL> select sysdate from dual@PDB_MIG;

SYSDATE
---------
06-FEB-22

SQL>

Step 3. Initiate PDB Clone Command in Target CDB : Create directory /u01/oradata_2/msguser/ in target server. We will use the same directory to store all datafiles. Use command to initiate clone, we are using file_name_convert parameter to change Window directory location of datafiles in Linux directory location to /u01/oradata_2/msguser/. Please note clone command failed due to “ORA-65346: The PDB version is lower and components (APEX) are missing in CDB“. This is because APEX component is missing in Target CDB. Launch DBCA in Target Server and add the missing component in Target CDB as per the below screenshots. Once the missing component is added execute CREATE PLUGGABLE DATABASE command once again.

[oracle@test-machine02 ~]$
[oracle@test-machine02 ~]$ mkdir -p /u01/oradata_2/msguser/
[oracle@test-machine02 ~]$
[oracle@test-machine02 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 6 14:14:04 2022
Version 19.3.0.0.0

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

Enter password:

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

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> CREATE PLUGGABLE DATABASE MSGUSER FROM MSGUSER@PDB_MIG  file_name_convert=('H:\CDB1\ORADATA_2\','/u01/oradata_2/msguser/','G:\CDB1\ORADATA_2\MSGUSER\','/u01/oradata_2/msguser/','H:\CDB1\ORADATA_2\MSGUSER\','/u01/oradata_2/msguser/');
CREATE PLUGGABLE DATABASE MSGUSER FROM MSGUSER@PDB_MIG  file_name_convert=('H:\CDB1\ORADATA_2\','/u01/oradata_2/msguser/','G:\CDB1\ORADATA_2\MSGUSER\','/u01/oradata_2/msguser/','H:\CDB1\ORADATA_2\MSGUSER\','/u01/oradata_2/msguser/')
*
ERROR at line 1:
ORA-65346: The PDB version is lower and components (APEX) are missing in CDB.
SQL>

Source CDB Components

Target CDB Components

SQL>
SQL> CREATE PLUGGABLE DATABASE MSGUSER FROM MSGUSER@PDB_MIG  file_name_convert=('H:\CDB1\ORADATA_2\','/u01/oradata_2/msguser/','G:\CDB1\ORADATA_2\MSGUSER\','/u01/oradata_2/msguser/','H:\CDB1\ORADATA_2\MSGUSER\','/u01/oradata_2/msguser/');
Pluggable database created.

SQL>
SQL>

SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MSGUSER                        MOUNTED
SQL>
SQL>

Step 4. Initiate migrated PDB upgrade to 19C: Open PDB in upgrade mode and use the below command to initiate an upgrade to 19C. It took 30 mins to complete the upgrade.

SQL>
SQL> ALTER PLUGGABLE DATABASE MSGUSER OPEN UPGRADE;
Pluggable database altered.

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MSGUSER                        MIGRATE    YES
SQL>

[oracle@test-machine02 DB_PUMP]$
[oracle@test-machine02 DB_PUMP]$ dbupgrade -c "MSGUSER" -l /u01/DB_PUMP

Argument list for [/u01/app/oracle/product/19.3.0/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = MSGUSER
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 = /u01/DB_PUMP
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.3.0.0.0DBRU_LINUX.X64_190417]


/u01/app/oracle/product/19.3.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.3.0/db_1]
/u01/app/oracle/product/19.3.0/db_1/bin/orabasehome = [/u01/app/oracle/product/19.3.0/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.3.0/db_1]

Analyzing file /u01/app/oracle/product/19.3.0/db_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/DB_PUMP]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/DB_PUMP/catupgrd_catcon_9833.lst]

catcon::set_log_file_base_path: catcon: See [/u01/DB_PUMP/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/DB_PUMP/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 2
Database Name         = cdb1
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:[MSGUSER]
CDB$ROOT  Open Mode = [OPEN]
Concurrent PDB Upgrades Reset           = 1

Start processing of PDBs (MSGUSER)
[/u01/app/oracle/product/19.3.0/db_1/perl/bin/perl /u01/app/oracle/product/19.3.0/db_1/rdbms/admin/catctl.pl -c 'MSGUSER' -l /u01/DB_PUMP -I -i msguser -n 2 /u01/app/oracle/product/19.3.0/db_1/rdbms/admin/catupgrd.sql]

Argument list for [/u01/app/oracle/product/19.3.0/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = MSGUSER
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = msguser
Child Process                I = 1
Log Dir                      l = /u01/DB_PUMP
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.3.0.0.0DBRU_LINUX.X64_190417]


/u01/app/oracle/product/19.3.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.3.0/db_1]
/u01/app/oracle/product/19.3.0/db_1/bin/orabasehome = [/u01/app/oracle/product/19.3.0/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.3.0/db_1]

Analyzing file /u01/app/oracle/product/19.3.0/db_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/DB_PUMP]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/DB_PUMP/catupgrdmsguser_catcon_10107.lst]

catcon::set_log_file_base_path: catcon: See [/u01/DB_PUMP/catupgrdmsguser*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/DB_PUMP/catupgrdmsguser_*.lst] files for spool files, if any


Number of Cpus        = 2
Database Name         = cdb1
DataBase Version      = 19.0.0.0.0
MSGUSER Open Mode = [MIGRATE]
Generated PDB Inclusion:[MSGUSER]
CDB$ROOT  Open Mode = [OPEN]
Components in [MSGUSER]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-107]         Start Time:[2022_02_06 16:32:42]
Container Lists Inclusion:[MSGUSER] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [MSGUSER] Files:1    Time: 44s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [MSGUSER] Files:5    Time: 43s
Restart  Phase #:2    [MSGUSER] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [MSGUSER] Files:19   Time: 22s
Restart  Phase #:4    [MSGUSER] Files:1    Time: 2s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [MSGUSER] Files:7    Time: 19s
*****************   Catproc Start   ****************
Serial   Phase #:6    [MSGUSER] Files:1    Time: 15s
*****************   Catproc Types   ****************
Serial   Phase #:7    [MSGUSER] Files:2    Time: 13s
Restart  Phase #:8    [MSGUSER] Files:1    Time: 2s
****************   Catproc Tables   ****************
Parallel Phase #:9    [MSGUSER] Files:67   Time: 27s
Restart  Phase #:10   [MSGUSER] Files:1    Time: 2s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [MSGUSER] Files:1    Time: 49s
Restart  Phase #:12   [MSGUSER] Files:1    Time: 2s
**************   Catproc Procedures   **************
Parallel Phase #:13   [MSGUSER] Files:94   Time: 11s
Restart  Phase #:14   [MSGUSER] Files:1    Time: 2s
Parallel Phase #:15   [MSGUSER] Files:120  Time: 15s
Restart  Phase #:16   [MSGUSER] Files:1    Time: 1s
Serial   Phase #:17   [MSGUSER] Files:22   Time: 7s
Restart  Phase #:18   [MSGUSER] Files:1    Time: 2s
*****************   Catproc Views   ****************
Parallel Phase #:19   [MSGUSER] Files:32   Time: 18s
------------------------------------------------------

Grand Total Upgrade Time:    [0d:0h:29m:2s]
[oracle@test-machine02 DB_PUMP]$

[oracle@test-machine02 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 6 17:03:55 2022
Version 19.3.0.0.0

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

Enter password:

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

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL>
SQL> show pdbs

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

SQL>  alter pluggable database MSGUSER open;

Pluggable database altered.
SQL>  
SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MSGUSER                        READ WRITE NO
SQL>

Step 5. Execute utlrp.sql to compile invalid objects: Compile invalid objects using the below command.

[oracle@test-machine02 DB_PUMP]$
[oracle@test-machine02 DB_PUMP]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'MSGUSER' -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/DB_PUMP/utlrp_catcon_19232.lst]

catcon::set_log_file_base_path: catcon: See [/u01/DB_PUMP/utlrp*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/DB_PUMP/utlrp_*.lst] files for spool files, if any


catcon.pl: completed successfully
[oracle@test-machine02 DB_PUMP]$

Step 6. Verify upgrade: As timezone is in the same version. No need to upgrade the timezone.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT 
SQL>
SQL> col VALUE$ for a15
SQL> select value$, con_id from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by 2;

VALUE$              CON_ID
--------------- ----------
32                       1
32                       2
32                       3

SQL>

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MSGUSER                        READ WRITE NO
SQL>
SQL> alter session set container=MSGUSER;

Session altered.

SQL> set lin 1000
SQL> col action form a12
SQL> col version  form a40
SQL> col description form a85
SQL> col action_date form a20
SQL> select description, action, to_char(action_time,'DD/MM/RR HH24:MI:SS') action_date, ' ' version from dba_registry_sqlpatch;

DESCRIPTION                                                                           ACTION       ACTION_DATE          VERSION
------------------------------------------------------------------------------------- ------------ -------------------- ----------------------------------------
Database Release Update : 19.3.0.0.190416 (29517242)                                  APPLY        06/02/22 16:58:25

SQL>
SQL>
SQL> set lines 300
SQL> set pages 300
SQL> COL comp_id for a15
SQL> COL status for a10
SQL> COL version for a15
SQL> COL comp_name for a40
SQL> SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10) version, SUBSTR(comp_name,1,40) comp_name FROM dba_registry;

COMP_ID         STATUS     VERSION         COMP_NAME
--------------- ---------- --------------- ----------------------------------------
CATALOG         VALID      19.0.0.0.0      Oracle Database Catalog Views
CATPROC         VALID    19.0.0.0.0      Oracle Database Packages and Types
JAVAVM          VALID      12.1.0.2.0      JServer JAVA Virtual Machine
XML             VALID      19.0.0.0.0      Oracle XDK
CATJAVA         VALID    19.0.0.0.0      Oracle Database Java Packages
APS             VALID      19.0.0.0.0      OLAP Analytic Workspace
RAC             OPTION OFF 19.0.0.0.0      Oracle Real Application Clusters
XDB             VALID      19.0.0.0.0      Oracle XML Database
OWM             VALID      19.0.0.0.0      Oracle Workspace Manager
CONTEXT         VALID      19.0.0.0.0      Oracle Text
ORDIM           VALID      19.0.0.0.0      Oracle Multimedia
SDO             VALID      19.0.0.0.0      Spatial
XOQ             VALID    19.0.0.0.0      Oracle OLAP API
OLS             VALID      19.0.0.0.0      Oracle Label Security
APEX            VALID      4.2.5.00.0      Oracle Application Express
DV              VALID      19.0.0.0.0      Oracle Database Vault

16 rows selected.

SQL>

Reference : Upgrade Testing – Online Clone a PDB to 19c : https://mikedietrichde.com/2020/03/06/upgrade-testing-online-clone-a-pdb-to-19c/

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DBsGuru