Toad World

Tricky Oracle data guard situation

Avatar photo
Post author:Written By

Had an interesting situation configuring a Data Guard setup of 7TB Oracle 12c database at one of the clients. After a long and rounds of discussions, considering the constraints and limitations, agreed to use the traditional way of Data Guard setup. Backup the database, replicate the backups to DR site, create a standby controfile, restore the control file on standby host , do DB restore, recovery and configure the synchronization.

 

Restore failed due to unavailability of the data files location (same as production) on the DR host, because, all application tablespaces data files on PROD were not used OMF format, stored under ASM with .dbf extension. Due to this fact, only default tablespaces data files (which actually used OMF format in ASM) were restored. A PROD similar directory structure (using ALTER DISKGROUP ADD DIRECTORY) created and all the data files were restored successfully. The PROD similar directory (+DATA/PROD/DATAFILE)  contained only the soft links and data files were created under the Standby Database SID directory. The entire process took more than 24 hrs, and a subsequent incremental backup was taken to fill the gap and have the data in sync using the roll-forward procedure.

As part of the roll-forward, a new standby controlfile was created on PROD and restored on the DR DB. When CATALOG START WITH command was issued, only few data files (system related) were able to CATALOG, while the soft link data files weren't. This stopped us doing the roll-forward recovery. Tried all possible option, nothing was materialized.

We then cleaned-up the DR database data files, to have a fresh restore. Used the db_convert_file_name parameter and start over the traditional DR configuration procedure. This time, all the data files were restored under standby SID directory, also, soft links for the non-standard OMF data files, were created in the same directory, unlike the first attempt. Once the restore done, successfully performed the roll-forward procedure to make the PROD and DR DBs in sync.

 

Conclusion

If you have non-standard OMF files, with .dbf extension under ASM, ensure you use the db_file_name_convert to avoid the mess.

Well, if you have any alternative, you are most welcome to suggest.

 

Tags:

About the Author

Syed Jaffar Hussain

An Oracle Database Expert for over 15 years from his 20 years of Information Technology (IT) career. Over the past 15 years of Oracle journey, he involved with several local and large scaled international banks where he implemented and managed highly complex cluster and non-cluster environments with over 100’s of business critical databases. Recognizing his efforts and contribution towards the Oracle community, Oracle awarded him the prestigious ‘Best DBA of the year, 2011’ and Oracle ACE Director status. He also acquired industry best Oracle credentials, Oracle Certified Master (OCM), Oracle RAC Expert, OCP DBA 8i,9i,10g & 11g in addition to ITIL Expertise. Syed is an active Oracle speaker, regularly presents technical sessions and webinars on various Oracle database technologies at many Oracle events. You can visit his technical blog, http://jaffardba.blogspot.com where he discuss and writes the workaround/solution about the issues confronted from his day-to-day activities. Apart from being the part of the core Technical Review committee member for a few Oracle technology oriented books, he also co-authored an Oracle 11g R1/R2 Real Application Cluster Essentials and Oracle Expert RAC books. His blog can be found at http://jaffardba.blogspot.com/

Start the discussion at forums.toadworld.com