So this Thursday we were doing some application testing on our physical standby and after the testing was completed, My Backups kicked off while i was flashing back the Database. This caused the Controlfile to have an invalid entry with the ckp_time being null. The problem with an invalid entry int eh control file was that Oracle RMAN was not able to run an implicit resync catalog from the standby. Our Configuration has a primary and a physical standby that is used for backups so the backups were failing which is a problem.
A resync catalog would work for the primary so the issue was isolated to the physical standby. I thought hey that's simple , just get another copy of the control file from the primary using
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stdbycontrol.ctl';
sftp the file
and then run
RMAN> restore controlfile from 'stdbycontrol.ctl;
and all will be good.
While the assumption was right . I was missing important components . Structure,OMF & ASM.
The Standby was originally created from a Filesystem Based Database which was later converted to ASM. Ths meant the structure of the primary and standby were very different. Even after the primary was converted into ASM OMF and standby_file_management=auto meant that OMF would kick in and all files created in the primary will not get the same file name on the standby. This caused loads of headaches for me and i was glad i had a backup.
The Only Good thing was that the File number matched on both primary and standby. This Help me Build a SQL File to
SQL > alter database rename file ' File name of primary database' to ' Files Name of Standby database';
The Way to do this the fastest was to dump the V$datafile from the old controlfile on the standby . Load it into the primary and run a select like this
SQL> select 'alter database rename file ' chr(39)||a.name ||' chr(39)' to ' chr(39)||b.name||chr(39)||';' from v$datafile a stdby_datafile b where a.file#=b.file# order by file#
This generated a quick and dirty datafile renaming script to get me up and running as soon as the standby controlfile was restored.
A painful process but definitely faster than restoring the whole database.
