Dataguard failover - what went wrong here??

Printable View

05-24-2005, 06:22 PM
Axr2

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!
05-24-2005, 09:55 PM
kris123

im sure u just missed something:D
thats what i do all the time...and learned from experience...
TRY AND TRY UNTIL YOU SUCCESS
05-24-2005, 11:10 PM
Axr2

Quote:

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

WTF?
05-25-2005, 01:10 AM
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.
05-25-2005, 01:19 AM
Axr2

Quote:

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.
01-18-2008, 05:16 AM
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??
01-18-2008, 05:31 PM
monto1

Quote:

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 .

Quote:

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
01-18-2008, 05:41 PM
hrishy

Quote:

Originally Posted by 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

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

regards
Hrishy
01-19-2008, 03:15 AM
hafeezkalsekar

data guard

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 ??
01-19-2008, 08:52 AM
pando

Quote:

Originally Posted by hafeezkalsekar

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
01-19-2008, 09:26 AM
hrishy

pando
hahaha :-) sorry i couldnt stop laughing out loud..these kind of questions deserve those answers :-D

regards
Hrishy
01-19-2008, 10:05 AM
hafeezkalsekar

hi hirshy ,
there i nothing to laugh in it
i have maximum performance mode so i dont need standby logfile;
i was confused whether to use ....finish skip standby logfile or just recover ... skip standby logfile before giving switchover command,'
as some document recomends 1) alter database recover managed standby finish skip standby log file;
and some recomends 2) alter database recover managed standby database skip standby logfile;
01-19-2008, 01:56 PM
hrishy

Hi

Okay i have stopped laughing...but my 2 cents you should always have a standby logfile even when you are having maximum performance mode.

regards
Hrishy
01-20-2008, 07:26 AM
hafeezkalsekar

in maximum performance mode its not necessary to have standby redo logs.
anyways if any has done failover himself plz comment..
01-21-2008, 11:11 AM
hrishy

Hi

You need to do this as you dont have standby redologs

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH

and then failover using

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

http://download-east.oracle.com/docs...nt.htm#1024703

regards
Hrishy
01-22-2008, 09:04 AM
hafeezkalsekar

sorry hirshy
but as far as i know if u dont have standby redo logs,
u have to fire "alter database recover managed standby database finish skip standby logfile"
01-22-2008, 10:24 AM
hrishy

Hi

I was wrong you indeed need to do

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
SKIP STANDBY LOGFILE

regards
Hrishy