Introduction
Oracle Multitenant allows you to clone PDBs from one CDB to another easily, even though the source and target CDBs were of different patch levels, e.g. 19.9 and 19.10, and even different editions like moving from SE to EE. The only thing you’ll need is to run datapatch afterwards. Unplug and Plug the PDB will require more downtime needed to move the data files from source to target. Conventional PDB cloning would require stopping the application for the duration needed for the clone operation to complete, which also could take a while for large and busy databases.
Using PDB Refreshable Clones allows you to propagate the changes from source to target manually or automatically and keep the target up to date until “last minute” where you stop your application and perform the last refresh. PDB Refresh was first introduced in Oracle Database Release 12.2 and is available for SE and EE on-premises and in the Cloud. Have a look at the really cool Features and Licensing page. I know, “cool” and “licensing” in one sentence? I’ll make an exception this time.
The source and target CDBs could even be of different release versions, e,g. 18c and 19c. In this case we will need to upgrade the PDB afterwards instead of running datapatch. This will be the topic of the next blog.
The Environment
- Source: EE CDB version 19c and Release Update (RU) 19.9
- Target: EE CDB version 19c and Release Update (RU) 19.10
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__199 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 (19.9):
SQL> create database link DBLINK_TO_19_9 connect to C##SYSOPER identified by SysOperPw__199 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 130.61.33.156)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CDBROOT_fra1rr.sub012345.vcnfra2.oraclevcn.com)))';
Database link created.
Create the refreshable PDB clone with an automatic refresh every minute. You can choose the same PDB name on target database, but for butter demonstration, I’ll choose PDB002 on the target instead of PDB001:
SQL> create pluggable database PDB002 from PDB001@DBLINK_TO_19_9 keystore identified by "TDE_Wallet_Password" refresh mode every 1 minutes parallel 4;
Pluggable database created.
SQL> select name, open_mode, restricted from v$pdbs where name='PDB002';
NAME OPEN_MODE RESTRICTED
-------------------- ---------- --------------------
PDB002 MOUNTED
set lines 300
col pdb_name for a20
col status for a20
col refresh_mode for a20
col refresh_interval for 999
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. The target PDB can be opened in READ ONLY mode if you want. To get the new changes from the source, it must be closed again.
Use the PARALLEL n clause to control the degree of parallelism to copy the PDB. On production systems during high workload, you might want to choose a low degree of parallelism to minimize the impact on the production system.
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.
Open the target PDB:
SQL> alter pluggable database PDB002 refresh mode none;
Pluggable database altered.
SQL> alter pluggable database PDB002 open;
Warning: PDB altered with errors.
OH! Let’s check what is going on:
select type, cause, message from PDB_PLUG_IN_VIOLATIONS where name='PDB002' and status != 'RESOLVED';
This is ok, as we didn’t run datapatch yet.
4. Run Datapatch on the Target DB
Open the PDB in READ WRITE mode.
Run the utlrp.sql script before you invoke datapatch to clean up invalid objects:
SQL> alter session set container=PDB002;
Session altered.
SQL> @?/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.
Now run datapatch:
$ORACLE_HOME/OPatch/datapatch -verbose -pdbs PDB002
Validating logfiles...done
Patch 31465389 rollback (pdb PDB002): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31465389/23823398/31465389_rollback_CDBROOT_PDB002_2021Feb02_21_34_32.log (no errors)
Patch 32218454 apply (pdb PDB002): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_CDBROOT_PDB002_2021Feb02_21_34_33.log (no errors)
Patch 31465389 apply (pdb PDB002): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31465389/23988611/31465389_apply_CDBROOT_PDB002_2021Feb02_21_35_28.log (no errors)
SQL Patching tool complete on Wed Feb 2 21:36:28 2021
To get rid of the errors in PDB_PLUG_IN_VIOLATIONS view, close and open the target PDB again:
SQL> alter pluggable database PDB002 close immediate;
Pluggable database altered.
SQL> alter pluggable database PDB002 open;
Pluggable database altered.
Great! Now the PDB opens without any issues. Check the PDB_PLUG_IN_VIOLATIONS view:
SQL> select type, cause, message from PDB_PLUG_IN_VIOLATIONS where name='PDB002' and status != 'RESOLVED';
As you don’t necessarily need to encrypt SYSTEM and SYSAUX tablespaces, you could ignore these warnings.
Downtime ends. In case something went wrong, you can just open your source PDB again and use it for production.
5. 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 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.
Using the Database while Data Patch is running?
This is possible, too. Check out the great blog post by my colleague Daniel:
Conclusion
Refreshable PDB Clones allow you to reduce the downtime for patching down to very few minutes giving you the flexibility to patch 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. 19.9 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 