Tricky Oracle data guard situation

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.

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.

No comments: