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;
####### 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’
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
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.”
