Dataguard failover - what went wrong here??

[Axr2]

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!

[kris123]

im sure u just missed something
thats what i do all the time...and learned from experience...
TRY AND TRY UNTIL YOU SUCCESS

[Axr2]

Originally posted by kris123
im sure u just missed something
thats what i do all the time...and learned from experience...
TRY AND TRY UNTIL YOU SUCCESS

WTF?

[gandolf989]

Do you need to do log switches on the primary and or force the secondary to sync up before making the secondary database active? Also, do you need to change the control file before you activate the server.

fyi, It has been awhile since I have mucked with standby servers.

[Axr2]

Originally posted by gandolf989
Do you need to do log switches on the primary and or force the secondary to sync up before making the secondary database active? Also, do you need to change the control file before you activate the server.

fyi, It has been awhile since I have mucked with standby servers.

If the logXptMode were ARCH (max availability mode), yes you would need to switch archivelogs..and everything in online redologs is history.

With max performance and LGWR mode, you don't need to switch logs..coz it uses SRLs (standy redologs). When LGWR Xpt gets hosed it automatically switches to max availability mode.

As for controlfile stuff..it is taken care of when you activate the standby.

In my case, the failover worked -- it eventually did what I asked it to (i blew my online redologs..but thats okay by me). But I don't get why it complained the first couple of times..and worked the 3rd time.

PS : I've performed real failovers / switchovers many times in past..never faced this before.

[hafeezkalsekar]

hi gurus of dataguard ,
i have 2 query plz help

i have data guard configured in maximum protection mode and arch and
no standby logfiles, for
failover what should i follow
1) alter database recover managed standby database skip standby log files;
or
alter database recover managed standby finish skip standby log file;
if i follow option a) do i need to do option b) as well ?

2) is it to be done at mount or nomount stage??

[monto1]

i have data guard configured in maximum protection mode and arch and
no standby logfiles, for
failover what should i follow
1) alter database recover managed standby database skip standby log files;
or
alter database recover managed standby finish skip standby log file;
if i follow option a) do i need to do option b) as well ?

sql>alter database recover managed standby database skip standby log files;

does gracefull failover even without standby redologs .

if i follow option a) do i need to do option b) as well ?

nope you dont have too,
aftre that just do sql>alter database commit to switchover to primary;

2)should be mounted.

Regards

[hrishy]

hi gurus of dataguard ,
i have 2 query plz help

i have data guard configured in maximum protection mode and arch and
no standby logfiles, for
failover what should i follow

AFAIK you need to have standby logs if you want to use maximum protection mode


regards
Hrishy

[hafeezkalsekar]

hi thnk for reply

so monto these is what i suppose to do ??

1) alter database recover managed standby skip standby logfiles;

2) alter database commit to switchover to primary ;

but what about the finish command is it not neccessary ??

[pando]

1) alter database recover managed standby database skip standby log files;
or
alter database recover managed standby finish skip standby log file;
if i follow option a) do i need to do option b) as well ?

2) is it to be done at mount or nomount stage??

that is BS because you need standby logfiles to use maximum protection