Saturday, January 16, 2010

Failed Switchover - Oracle11g

I recently had a situation where we had a failed switchover process. Was able to switch the Primary to Standby successfully, but encountered some issues while switching over standby to primary - therefore ended up having two standbys! The easiest alternative to get out of the situation would be to ACTIVATE one of the standbys and then rebuild the primary - but it's not always desirable (size, efforts, unprotected primary etc..).

We even attempted the oracle provided command to cancel the switchover, but encountered the following (On original primary):

SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
ALTER DATABASE PREPARE TO SWITCHOVER CANCEL
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [adbdrv_switch], [384], [], [], [],
[], [], [], [], [], [], []

Here is I ended up doing:

All of these have been performed on original primary:
1. Startup Mount;
SQL> startup force nomount;
ORACLE instance started.

Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 150998612 bytes
Database Buffers 83886080 bytes
Redo Buffers

2. Recreate the controlfile using the trace (got from standby)
SQL> @scripts/ccf.sql

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL>
3. That's it…that did it!

SQL> Select open_mode, database_role, switchover_status from V$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE PRIMARY TO STANDBY

SQL>

4. Here are the entries from standby - didn't have to do anything - it continued through RESETLOGS!

RFS[18]: New Archival REDO Branch(resetlogs_id): 708469623 Prior: 708454293
RFS[18]: Archival Activation ID: 0x6fece72a Current: 0x0
RFS[18]: Effect of primary database OPEN RESETLOGS
RFS[18]: Incarnation entry added for Branch(resetlogs_id): 708469623 (ETSI01)
Sat Jan 16 21:08:09 2010
Setting recovery target incarnation to 4
Sat Jan 16 21:08:09 2010
Archived Log entry 33 added for thread 1 sequence 1 ID 0x6fece72a dest 1:
Sat Jan 16 21:08:09 2010
RFS[19]: Assigned to RFS process 5022