Dataguard failover - what went wrong here??
I have the below DG configuration replicating to a physical standby in a Maximum performance mode (using LGWR as the Xptmode).
I had to perform/simulate a failover to the standby in this case. So I "DEFER"ed the appropriate log_archive_dest_state_n on the primary (I didn't disable the DG configuration)..and then went about activating the standby database. Here's a cut n paste of what I did..in exact sequence (the commands were typed one after the other rapidly). Why in the world did the SWITCHOVER (failover rather) throw errors the first 2 times..and then work in the end? Yes, it's on windows..
Thanks.
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
C:\Documents and Settings\Administrator>set ORACLE_SID=PRODDB
C:\Documents and Settings\Administrator>sqlplus "sys as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Tue May 24 14:56:10 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
---->>>> NICE!
SQL>
SQL>
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required
---->>>>> WHAT THE HECK??!!
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE ACTIVATE STANDBY DATABASE
*
ERROR at line 1:
ORA-00261: log 5 of thread 1 is being archived or modified
ORA-00312: online log 5 thread 1: 'D:\ORACLE\ORADATA\PRODDB\PRODDB_SRL1.F'
--->>> WHATEVER..
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE SKIP STANDBY LOGFILE;
ALTER DATABASE ACTIVATE STANDBY DATABASE SKIP STANDBY LOGFILE
*
ERROR at line 1:
ORA-00261: log 5 of thread 1 is being archived or modified
ORA-00312: online log 5 thread 1: 'D:\ORACLE\ORADATA\PRODDB\PRODDB_SRL1.F'
---->>>>> WHAT?? WHY IS IT BEING MODIFIED?!! I DEFER'ED the log_arch_dest_state on the primary..SHOULDN'T THAT TAKE CARE OF THIS? NOTE : THE "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;" was successful. YET, IT THROWS THIS ERROR!
SQL> alter database recover managed standby database finish skip wait;
alter database recover managed standby database finish skip wait
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-00261: log 5 of thread 1 is being archived or modified
ORA-00312: online log 5 thread 1: 'D:\ORACLE\ORADATA\PRODDB\PRODDB_SRL1.F'
----->>>> OKAY...
SQL> shutdown immediate;
ORA-01109: database not open
--->>> AM SHUTTING DOWN STANDBY AS A LAST RESORT TO KILL / STOP THAT SRL THREAD FROM THE PRIMARY
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1242638604 bytes
Fixed Size 456972 bytes
Variable Size 528482304 bytes
Database Buffers 713031680 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> RECOVER MANAGED STANDBY DATABASE FINISH;
Media recovery complete.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE ACTIVATE STANDBY DATABASE
*
ERROR at line 1:
ORA-16132: An error occurred during activation of the standby.
---->>>>>>>>>> AAAAAHHHHH
SQL> shutdown immediate
ORA-01109: database not open
--->>>>> HERE I GO AGAIN
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1242638604 bytes
Fixed Size 456972 bytes
Variable Size 528482304 bytes
Database Buffers 713031680 bytes
Redo Buffers 667648 bytes
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active
-->>> I SEE..(I ENTERED THIS COZ THE DATABASE AUTOMATICALLY GETS MOUNTED AND SET IN A MANAGED RECOVERY MODE once I startup "NOMOUNT" even..)
SQL> recover standby database;
ORA-00283: recovery session canceled due to errors
ORA-16157: media recovery not allowed following successful FINISH recovery
--->>>> JUST CHECKING IF IT'LL ALLOW ME TO MANUALLY RECOVER. AS YOU CAN SEE, THE DATABASE IS ALREADY MOUNTED.
SQL> recover managed standby database;
ORA-00283: recovery session canceled due to errors
ORA-16157: media recovery not allowed following successful FINISH recovery
--->>> OKAY, I CANNOT ISSUE RECOVER, ONCE "FINISH"ed..got it.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO primary;
Database altered.
--->>>>> HOLY COW! WHY DID IT WORK THIS TIME, BUT NOT THE LAST 2 TIMES?!!
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1242638604 bytes
Fixed Size 456972 bytes
Variable Size 528482304 bytes
Database Buffers 713031680 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
---->>>> SWEET!