Oracle 19c Pluggable Database Refreshable Clones
Besides the long-term release benefits, upgrading to a multitenant 19c Database will completely ease the way test environments are refreshed from production servers. Say goodbye to complex & lengthy RMAN (Recovery Manager) duplicate scripts and hello to remote cloning. Even better with refreshable clones, production data is now automatically updated. Many blogs were written about it like the one from Tim Hall, Franck Pachot(Poor man’s standby), or Oracle ACE peer Diana Robete. But I still found something to add, as I used Oracle scheduler to automate the refreshes unlike the default available option (EVERY X Minutes).
Options to refresh Test PDB from prod
A PDB snapshot is a point-in-time copy of a PDB (pluggable database). Source PDB can be open read-only or read/write during snapshot creation. Copies can be sparse or full depending on whether the storage system supports sparse clones or not.
Snapshots are created manually via
SNAPSHOTclause or automatically using ‘
There’s a very handy article from my colleague on how to create a snapshot using the carousel technique.
Snapshot carousel requires ASM (Automatic Storage Management) and ACFS (Automatic Cluster File System) file systems which, in my case, are not present so I’ll pass.
This is a valid approach for non-ASM DBs and doesn’t require additional prerequisites besides a CDB (container database).
The below graphic from Tim Hall’s blog explains simply how the refresh mechanism works:
The underlying filesystem structure doesn’t need to be similar
Similar to those of a hot, remote clone, here are the most common (19c) prerequisites:
The user in the target database must have CREATE PLUGGABLE DATABASE privilege in the root container.
Remote CDB uses local undo mode. Otherwise, remote PDB must be opened in read-only mode.
Remote DB is in archive log mode. Otherwise, remote PDB must be opened in read-only mode.
If the remote database is a PDB, the database link can point to the remote CDB using a common user, the PDB using a local or common user.
The user in the remote database that the database link connects to must have the CREATE PLUGGABLE DATABASE privilege.
Target & remote databases must have the same endianness.
Target & remote databases either have the same options installed, or the remote database must have a subset of those of the target database.
If the character set of the target CDB is AL32UTF8, remote DB can be any character set. If target CDB does not use AL32UTF8, the character sets of remote/local databases must match.
If the remote database uses TDE, the target CDB must be configured appropriately before attempting the clone. If not, the new PDB will only open in restricted mode.
Refreshable Clone PDB must be closed during refresh
It must, also, be kept open Read Only the rest of the time to prevent out-of-sync changes
A refreshable PDB can be changed to a non-refreshable PDB, but not vice versa.
If source PDB is not available over the DB link, archived redo logs can be read from the optional REMOTE_RECOVERY_FILE_DEST location parameter.
New data files added to the source PDB are automatically created on the destination PDB. PDB_FILE_NAME_CONVERT parameter must be specified for the conversion to happen.
Refreshable PDB Demo
This scenario assumes DB file parameters are not defined in the target environment. OS: Windows
NAME VALUE -------------------- -------------------- db_file_name_convert pdb_file_name_convert db_create_file_dest
1- Create a common user in the source CDB with the required privileges where source PDB is “PSPROD”
SQL> Create user c##psprod_clone identified by welcome1 container=all; SQL> Grant create session, create pluggable database to c##psprod_clone container=all;
2- Create a database link at the Target CDB using source TNS alias ‘SourceCDB’
SQL> create database link psprod_clone_link connect to c##psprod_clone identified by welcome1 using 'SourceCDB';
3- Create a refreshable PDB clone using a manual refresh, plus a few automatic refresh syntax examples.
-- Manual refresh mode. CREATE PLUGGABLE DATABASE PSPDB_RO FROM PSPROD@psprod_clone_link file_name_convert=('D:\ORACLE\DATA\','K:\ORACLE\CDB\PSPDB_RO\DATA\', 'H:\ORACLE\TEMP\','K:\ORACLE\PSPDB_RO\TEMP\') REFRESH MODE MANUAL; -- Automatically refresh every 60 minutes. CREATE PLUGGABLE DATABASE PSPDB_RO FROM PSPROD@psprod_clone_link REFRESH MODE EVERY 60 MINUTES; -- Non-refreshable PDB. -- Both below commands are functionally equivalent. CREATE PLUGGABLE DATABASE PSPDB_RO FROM PSPROD@psprod_clone_link REFRESH MODE NONE; CREATE PLUGGABLE DATABASE PSPDB_RO FROM PSPROD@psprod_clone_link;
4- Perform a manual refresh. The syntax works for Post 12.2 from the root container
SQL> CONN / AS SYSDBA ALTER PLUGGABLE DATABASE PSPDB_ro CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE PSPDB_ro REFRESH; ALTER PLUGGABLE DATABASE PSPDB_ro OPEN READ ONLY;
5- Check the last refresh time and SCN
SQL> SELECT last_refresh_scn, cast(scn_to_timestamp(last_refresh_scn) as date) refresh_Time FROM dba_pdbs WHERE pdb_name = 'PSPDB_RO' ; LAST_REFRESH_SCN REFRESH_TIME ---------------- -------------------- 17635452014 17-Dec-2021 16:48:17
Schedule a nightly refresh (4 AM)
1- Create The refresh procedure
CREATE OR REPLACE PROCEDURE sys.psprod_refresh AS BEGIN EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE PSPDB_RO CLOSE IMMEDIATE'; EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE PSPDB_RO REFRESH'; EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE PSPDB_RO OPEN READ ONLY'; END; /
2- Create the refresh JOB
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'PSPROD_REFRESH_JOB', job_type => 'STORED_PROCEDURE', job_action => 'sys.psprod_refresh', repeat_interval => 'freq=daily;byday=mon,tue,wed,thu,fri;BYHOUR=4; BYMINUTE=1', auto_drop => FALSE, enabled => TRUE, comments => 'Automatic refresh of the prod PDB clone'); END; / SQL>@check_job PSPROD_REFRESH_JOB JOB_NAME REPEAT_INTERVAL ------------------ -------------------------------------------------------- PSPROD_REFRESH_JOB freq=daily;byday=mon,tue,wed,thu,fri;BYHOUR=4; BYMINUTE
CREATE A CLONE FROM A REFRESHABLE PDB
You can see that File_name_convert works the same when creating a clone from a refreshable one
SQL> CREATE PLUGGABLE DATABASE PSDEV01 FROM PSPDB_RO File_Name_convert=('K:\ORACLE\CDB\PSPDB_RO\DATA\','I:\ORACLE\CDB\PSDEV01\DATA\', 'K:\ORACLE\CDB\PSPDB_RO\TEMP\','I:\ORACLE\CDB\PSDEV01\TEMP\');
This was an example of how to automate the refresh of a PDB clone using a DBMS scheduler job that gives better control of the frequency, instead of relying on the minutely refresh. Handy if you don’t like keeping an automatic refresh every X minutes and rather have it run once a day like a backup to avoid any unexpected maintenance.
Note: If you have the ORA-65345 error, “cannot refresh pluggable database”. That means remote archive logs have been wiped from the source server and refresh should be adjusted to match the source backup retention.