Refresh database from Data Guard

My client asked me for help. He needs to update the DEV environment every month

The current process (using export-import) takes a few days.

To reduce the time, an alternative approach was implemented

• Replicate constantly all production changes to a standby database.   This is achieved using Data Guard
• For the refresh
  • Stop synchronization
  • Drop old DEV databases
  • Create new DEV databases by cloning from the standby database
  • Restart synchronization

The new procedure should considerably reduce the refresh time considerably to a few hours.

1         Pre-Requirements

• Oracle version and Patch Level on Primary and Secondary should be same.
• Db_unique_name on standby and primary database should be different in order to used in DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.

2         Preparation

Standby preparation

Check if standby is in sync with primary

****** To check archivelog apply lag

SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM
(select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change#
FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;

Convert standby in snapshot standby

shutdown immediate
startup mount
alter database convert to snapshot standby;
alter database open;

 

Create DB links between Standby and DEV

---- Standby

alter pluggable database ALL open read only;
CREATE USER c##r_clone identified by Oracle23 CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SYSOPER, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO  c##r_clone CONTAINER=ALL;
GRANT DBA to c##r_clone;

CREATE DATABASE LINK refresh_link CONNECT TO c##r_clone identified by Oracle23 using 'DUTSD'; 

----- Dev Database

CREATE DATABASE LINK refresh_link CONNECT TO c##r_clone identified by Oracle23 using 'DUTSST';

alter pluggable database ALL open read only;
CREATE USER c##r_clone identified by Oracle23 CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SYSOPER, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO  c##r_clone CONTAINER=ALL;
GRANT DBA to c##r_clone;

Cleanup old PDB’s in DUTSD

####### DROP OLD PDB - DUTSD
Show pdbs; 

ALTER PLUGGABLE DATABASE <PDBNAME> CLOSE;
DROP PLUGGABLE DATABASE <PDBNAME> INCLUDING DATAFILES;

3 Clone PDBs

The files of standby were created using OMF convention.   For this reason the database files need to be converted from

+DATA01/DUTSD/<GUID>/DATAFILE/<file>.<id>  to

+DATA01/DUTSST/DATAFILE/<file>.dbf’

Clone DUTSD

CREATE PLUGGABLE DATABASE DUTSD FROM DUTSD@refresh_link storage unlimited tempfile reuse 
file_name_convert=('+DATA01/DUTSD/CCA899613E3D0713E0531114C30A2022/DATAFILE/duts01.474.1147957609','+DATA01/DUTSST/DATAFILE/duts01.dbf',
'+DATA01/DUTSD/CCA899613E3D0713E0531114C30A2022/DATAFILE/duts02.269.1147957617','+DATA01/DUTSST/ /DATAFILE/duts02.dbf') 
keystore identified by "Duts23" parallel 6;

4 Finalize the refresh

. oraenv
sqlplus / as sysdba
shutdown immediate

startup mount

Restore datafiles permissions

exec dbms_dnfs.restore_datafile_permissions('<CDBNAME>');

Convert the snapshot back to physical standby

show pdbs
alter database convert to physical standby;
shutdown immediate
startup

See you soon in other articles.

Stay tuned by following us on Twitter @aontalba and Linkedin.

Disclaimer: “The postings on this site are my own and do not necessarily represent my current employer positions, strategies, or opinions. The information here was edited to be useful for general purpose, specific data, and identifications was removed to allow reaching a generic audience and to be useful.”