Introduction
Oracle Multitenant architecture makes it possible to effortlessly move PDBs from one CDB to another, even though the source and target CDBs were of different release versions, e.g. 18c and 19c, and even of different editions like moving from SE to EE. In the previous blog we discussed how patching can be done with minimal downtime by creating refreshable PDBs across CDBs with different patch levels. Similarly, in this blog post we are going to create the refreshable PDB across CDBs with different release versions, keeping them in sync until “last minute”, and finally upgrade the PDB on the target CDB to match the CDB’s release version.
The Environment
- Source: EE CDB version 18c and Release Update (RU) 18.11
- Target: EE CDB version 19c and Release Update (RU) 19.10
You can use the same procedure to upgrade to other versions, e.g. to 21c.
0. Prepare the Source CDB
Prepare the C##SYSOPER user In the source CDB:
SQL> grant create session, sysoper to C##SYSOPER identified by SysOperPw__1811 container=all;
Grant succeeded.
1. Create the Refreshable PDB on the Target CDB
Create a database link on the target CDB (19.10) pointing to the source CDB (18.11):
SQL> create database link DBLINK_TO_18_11 connect to C##SYSOPER identified by SysOperPw__1811 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 130.61.117.238)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CDBROOT_fra1sr.sub012345.vcnfra2.oraclevcn.com)))';
Database link created.
Create the refreshable PDB clone with automatic refresh every one minute. You can choose the same PDB name on target, but for butter demonstration, I’ll choose PDB002 on target instead of PDB001:
SQL> create pluggable database PDB002 from PDB001@DBLINK_TO_18_11 keystore identified by "TDE_Wallet_Password" refresh mode every 1 minutes;
Pluggable database created.
set lines 300
col name for a10
col open_mode for a30
SQL> select name, open_mode, restricted from v$pdbs where name='PDB002';
NAME OPEN_MODE RESTRICTED
-------------------- -------------------- --------------------
PDB002 MOUNTED
col pdb_name for a10
col refresh_mode for a20
SQL> select pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='PDB002';
PDB_NAME STATUS REFRESH_MODE REFRESH_INTERVAL
-------------------- ---------- -------------------- ----------------
PDB002 REFRESHING AUTO 1
One minute is the minimal time that can be specified for automatic refresh.
The PDB is now in MOUNTED mode, REFRESHING state, and will be refreshed with changes from the source every one minute.
2. Close the Source PDB
Stop your application and close the source PDB to prevent any further changes:
SQL> alter pluggable database PDB001 close immediate;
Pluggable database altered.
The downtime begins.
3. Refresh the Target PDB
Now bring your target PDB up to date by executing a manual refresh to get the last changes from the source PDB:
SQL> alter pluggable database PDB002 refresh;
Pluggable database altered.
4. Upgrade the PDB
Open the target PDB for upgrade:
SQL> alter pluggable database PDB002 refresh mode none;
Pluggable database altered.
SQL> alter pluggable database PDB002 open upgrade;
Pluggable database altered.
SQL> select name, open_mode, restricted from v$pdbs where name='PDB002';
NAME OPEN_MODE RESTRICTED
-------------------- -------------------- --------------------
PDB002 MIGRATE YES
Upgrade the PDB:
$ORACLE_HOME/bin/dbupgrade -c "PDB002" -l /tmp
Grand Total Upgrade Time: [0d:0h:21m:53s]
After the upgrades succeeds, the PDB is left in MOUNTED mode. Open the PDB:
SQL> alter pluggable database PDB002 open;
Pluggable database altered.
-- check the PDB_PLUG_IN_VIOLATIONS view for unresolved issues
SQL> set lines 300
SQL> col type for a15
SQL> col cause for a30
SQL> col message for a170
SQL> select type, cause, message
from PDB_PLUG_IN_VIOLATIONS
where name='PDB002' and status != 'RESOLVED';
5. Post Upgrade Tasks
Recompile invalid objects using the utlrp.sql script:
SQL> alter session set container=PDB002;
Session altered.
SQL> @?/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.
Run the postupgrade_fixups.sql script:
SQL> @/u01/app/oracle/cfgtoollogs/$ORACLE_SID/preupgrade/postupgrade_fixups.sql
Downtime ends. Check the DBA_REGISTRY_SQLPATCH view:
SQL> alter session set container=PDB002;
Session altered.
SQL> select patch_id, patch_type, status, description, action_time from dba_registry_sqlpatch order by action_time desc;
The PDB is now at release 19c and patch level 19.10.
In case something went wrong, you can just open your source PDB again and use it for production.
6. Additional Tasks
Re-key the new created PDB so that it has its own TDE master encryption key:
SQL> alter session set container=PDB002;
Session altered.
SQL> administer key management set key using tag 'new own key' force keystore identified by "TDE_Wallet_Password" with backup;
keystore altered.
Gather schema statistics:
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'schema_name', estimate_percent=>dbms_stats.auto_sample_size, cascade=>true, degree=>4);
PL/SQL procedure successfully completed.
Gather fixed object statistics, but rather one week after upgrade. Fixed objects are not really tables, but memory structures. As the database is restarted multiple times during upgrade, the buffer cache is empty. so gathering fixed object statistics right after the upgrade will get you statistics of an empty buffer cache. Not a good idea. Not to forget this task, it is good practice to schedule it one time:
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END;',
start_date => SYSDATE+7,
auto_drop => TRUE,
comments => 'Gather fixed objects stats after upgrade - one time'
);
DBMS_SCHEDULER.ENABLE (
name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"'
);
END;
/
PL/SQL procedure successfully completed.
Conclusion
Refreshable PDB Clones provide flexibility to upgrade individual PDBs one by one with minimal downtime. As the source PDB remains open, you can test this scenario first without impacting your business continuity. The source PDB remains available for a fallback scenario if needed. The Source PDB could also be of different edition, e.g. 18.11 SE, while the target being of version 19.10 EE. You can use the same procedure to migrate a PDB from one platform to another, e.g. from on-premises to the cloud, to Oracle Cloud of course.
Database Heartbeat 