Snapshot standby gone bad....almost

I was recently involved in DR testing involving two standby databases.  For the testing, I simply converted the 2 standby databases from physical standbys to snapshot standbys.  

After the testing had ended from an application perspective, I started to convert the standby databases from snapshot standby back to physical standby.  In the process of doing so, I received the following ORA-0600 error.


SQL > ALTER DATABASE CONVERT TO PHYSICAL STANDBY
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [krhahws_02], [], [], [], [], [],
[], [], [], [], [], []

Oh no!  Never fear, I thought...  I went to Oracle Support to research this issue and found the following resolution:

 1. set disk_asynch_io=FALSE in the init.ora (this setting is just
    going to be temporary for repeating the "flashback database").
 2. shutdown then "startup mount" the database, to pick up the new
    parameter setting for disk_asynch_io
 3. verify the disk_asynch_io parameter is now FALSE via:
     select name,value from v$parameter where name='disk_asynch_io';
 4. repeat the same "flashback database" command that just gave the
    ORA-00600:[krhahws_02], this time the operation is mostly a
    no-op as the datafile contents have already been flashed back
    to the target scn, but this time the final step, which clears
    the v$datafile.first_nonlogged_scn if needed, should complete
    correctly with no errors.
 5. remove "disk_asynch_io=FALSE" (added in #1 above) from the
    init.ora
 6. shutdown and "startup mount" the database to pick up the
    original parameter setting (of TRUE) for disk_asynch_io
 7. verify the disk_asynch_io parameter is now TRUE via
     select name,value from v$parameter where name='disk_asynch_io';
 8. open resetlogs, as usual following a "flashback database"

I implemented the workaround per Metalink note ID; however, I still couldn't convert the databases back to physical standby successfully.  I received the following error:

SQL > alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01122: database file 108 failed verification check
ORA-01110: data file 108:
'+DATA/testdb/datafile/tbspname2008_01.755.796547639'
ORA-01207: file is more recent than control file - old control file

I tried to manually flashback to the GRP that is created as part of the conversion to snapshot standby.  No dice!

I was beginning to think that the database was hosed until I decided to try to something else.  I decided to open the database and start over again with converting the database back to physical standby with the disk_async_io being set to FALSE.  So here's what I did.

SQL> alter database open resetlogs;

Database altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size            2432697528 bytes

Database Buffers         1828716544 bytes

Redo Buffers               12132352 bytes

Database mounted.

SQL > alter database convert to physical standby;

Database altered.

Well, what do you know.... It worked!  After the conversion back to physical standby worked, I shutdown the database, did a startup mount of the database, and placed it back in auto recovery mode.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size            2432697528 bytes

Database Buffers         1828716544 bytes

Redo Buffers               12132352 bytes

Database mounted.

SQL > alter database recover managed standby database using current logfile disconnect;

Database altered.

I was back in business!   I set disk_async_io parameter back to TRUE, bounced the standby, and restarted the auto recovery.

Just when I thought I had to rebuild 2 monstrous standby databases.  I hope this helps others from having to rebuild!