Oracle 19c Pluggable Database Refreshable Clones – Eclipsys

Intro

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

1. Snapshot carousel

• 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 SNAPSHOT clause or automatically using ‘EVERY interval’.
• There’s a very handy article from my colleague on how to create a snapshot using the carousel technique.

   

• Restrictions:
  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.

2. Refreshable Clone PDBs

• 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

Prerequisites 
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 the 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.

Refresh Considerations

• Refreshable Clone PDB must be closed during the refresh
• It must, also, be kept open Read Only the rest of the time to prevent out-of-sync changes
• Other
  • 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\');

Conclusion

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 as 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.