19c PDB Refreshable Clones: Simplifying Test Environment Refreshes

 Upgrading to Oracle's 19c multitenant database brings more than just long-term release benefits. It revolutionizes how you refresh test environments from production servers, saying goodbye to complex and lengthy RMAN duplicate scripts and welcoming the simplicity of remote cloning. With refreshable clones, your production data gets automatically updated, making your life a whole lot easier.

 

While many experts like Tim Hall, Franck Pachot, and Diana Robete have delved into this topic, I have a unique twist to share—using Oracle Scheduler to automate refreshes, bypassing the default "EVERY X Minutes" option.

 

Options to Refresh Test PDB from Production

1. Snapshot Carousel

A PDB snapshot is a point-in-time copy of a PDB, which can be created while the source PDB is open in read-only or read/write mode. These snapshots can be sparse or full, depending on your storage system. You can create them manually using the SNAPSHOT clause or automatically at specified intervals.

However, the snapshot carousel requires ASM and ACFS filesystems, which weren't available in my setup, so I opted out of this method.

 

2. Refreshable Clone PDBs

For non-ASM databases, refreshable clone PDBs are the way to go. They don't require additional prerequisites beyond having a CDB.

Here's a simplified explanation of the refresh mechanism

Prerequisites: Ensure the user in the target database has CREATE PLUGGABLE DATABASE privilege in the root container. The remote CDB should use local undo mode, be in archivelog mode, and the remote PDB should be opened in read-only mode if these conditions are not met.

User Privileges: The user in the remote database must have the CREATE PLUGGABLE DATABASE privilege.

Compatibility: The target and remote databases must have the same endianness and either the same options installed or a subset of those in the target database. If the target CDB uses AL32UTF8, the remote DB can have any character set; otherwise, the character sets must match.

TDE Configuration: If the remote database uses TDE, the target CDB must be configured appropriately before attempting the clone.

Refresh Considerations

Close During Refresh: The refreshable clone PDB must be closed during the refresh process.

Read-Only Mode: Keep the PDB open in read-only mode the rest of the time to avoid out-of-sync changes.

Other Tips: A refreshable PDB can be changed to a non-refreshable PDB, but not vice versa. If the source PDB isn't available over a DB link, archived redo logs can be read from the optional REMOTE_RECOVERY_FILE_DEST location parameter. New datafiles added to the source PDB are automatically created on the destination PDB, with the PDB_FILE_NAME_CONVERT parameter handling the conversion.

Refreshable PDB Demo

Here's a quick demo on automating the refresh of a PDB clone using a DBMS scheduler job for better control over the refresh frequency. This is particularly handy if you prefer a daily refresh like a backup rather than an automatic refresh every few minutes, avoiding unexpected maintenance.

 

In the realm of databases, where parameters are unknown:

SQL> show parameter name

NAME                     VALUE 
-------------------- -------------------- 
db_file_name_convert 
pdb_file_name_convert 

1. Create common user in the source CDB, named "STGPROD". 

SQL> create user c##oraprod_clone identified by oradbahelp container=all;

SQL> grant create session, create pluggable database to c##oraprod_clone container=all;

2. Forge a database link in the Target CDB using the TNS alias (Here, it is 'Source_CDB_Name'). 

SQL> create database link oraprod_clone_db_link connect to c##oraprod_clone 
identified by oradbahelp using 'Source_CDB_NAME';

3. Create a refreshable PDB clone ( Given below, both manual and automatic refresh options). 

CREATE PLUGGABLE DATABASE ORADBCPY FROM STGPROD@praprod_clone_db_link   
file_name_convert=('+DATA1','+DATA2')
  REFRESH MODE MANUAL;
// Using Manual refresh mode.


CREATE PLUGGABLE DATABASE ORADBCPY FROM STGPROD@praprod_clone_db_link
  REFRESH MODE EVERY 60 MINUTES;
// Using Automatically refresh every 60 minutes method



CREATE PLUGGABLE DATABASE ORADBCPY FROM STGPROD@praprod_clone_db_link
  REFRESH MODE NONE;

CREATE PLUGGABLE DATABASE ORADBCPY FROM STGPROD@praprod_clone_db_link;
// A Non-refreshable PDB

4. Perform a manual refresh as part of testing. 

$sqlplus / as sysdba
ALTER PLUGGABLE DATABASE ORADBCPY CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ORADBCPY REFRESH;
ALTER PLUGGABLE DATABASE ORADBCPY OPEN READ ONLY;

5. Check the last refresh time and SCN to ensure the consistency. 

SQL> SELECT last_refresh_scn, cast(scn_to_timestamp(last_refresh_scn) as date) refresh_time 
FROM dba_pdbs WHERE pdb_name = 'ORADBCPY' ;

LAST_REFRESH_SCN REFRESH_TIME
---------------- --------------------
     14263917664 26-Jan-2024 19:15:09

6. With a dash of syntax and a sprinkle of logic, we shall craft - the sacred refresh procedure.

CREATE OR REPLACE PROCEDURE sys.DB_COPY_refresh
AS BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ORADBCPY CLOSE IMMEDIATE';
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ORADBCPY REFRESH';
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ORADBCPY OPEN READ ONLY';
END;
/

7. With a whispered command, we create the refresh JOB, entrusting it to keep the data flowing effortlessly. 

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DB_COPY_refresh_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'sys.DB_COPY_refresh',
repeat_interval => 'freq=daily;byday=mon,tue,wed,thu,fri;BYHOUR=2; BYMINUTE=1',
auto_drop => FALSE,
enabled => TRUE,
comments => 'Automatic refresh of the prod PDB clone');
END;
/

SQL>@validate_job DB_COPY_refresh_JOB
JOB_NAME     		 REPEAT_INTERVAL      
------------------   --------------------------------------------------------
DB_COPY_REFRESH_JOB  freq=daily;byday=mon,tue,wed,thu,fri;BYHOUR=2; BYMINUTE 

Notice how File_name_convert operates seamlessly, exhibiting consistent behavior whether you're crafting a clone or refreshing an existing one.

SQL> CREATE PLUGGABLE DATABASE ORADEV1 FROM ORADBCPY 
file_Name_convert=('+DATA1','+DATA2');

Conclusion

Automating PDB clone refreshes with Oracle Scheduler gives you better control over the refresh frequency, making your maintenance routine more predictable and less intrusive. If you encounter the ORA-65345 error ("cannot refresh pluggable database"), it indicates that remote archive logs have been wiped from the source server. Adjust your refresh schedule to align with the source backup retention to resolve this issue.

 

And there you have it, a tale of database wizardry spun with a touch of whimsy and a sprinkle of enchantment!

Thank you for reading! If you have any questions or comments, feel free to reach out. Happy cloning!