Home » Server Options » Data Guard » some archived logs not applied on standby but looks ok (rhel 5.3 oracle 10g)
some archived logs not applied on standby but looks ok [message #539149] Wed, 11 January 2012 11:13 Go to next message
n2fontenay
Messages: 15
Registered: December 2011
Location: San Diego, CA
Junior Member
Hi,

I got a database on dataguard and my primary (db1) is shipping files to my standby (db2) with no problems.

However, when I query:
select sequence#, status, applied from v$archived_log;

I see this:
SEQUENCE# S APP
---------- - ---
4 A YES
5 A YES
6 A YES
7 A YES
8 A YES
9 A YES
10 A YES
11 A YES
12 A YES
13 A YES
14 A YES

SEQUENCE# S APP
---------- - ---
14 A YES
15 A YES
15 A YES
16 A NO
16 A YES
17 A NO
17 A YES
18 A YES
18 A NO
19 A YES
20 A YES

SEQUENCE# S APP
---------- - ---
21 A YES
24 A YES
22 A YES
23 A YES
25 A YES
26 A YES

28 rows selected.

So I did an alter system switch logfile on db1

then looked again and I can see new archived logs being applied.

Now the question: I thought all archived logs had to be applied on the standby since this is the very foundation of the standby database.

Am I going to run in trouble later if I have a failover (unsynchronized database)

dgmgrl show SUCCESS.
Re: some archived logs not applied on standby but looks ok [message #539259 is a reply to message #539149] Thu, 12 January 2012 10:12 Go to previous messageGo to next message
n2fontenay
Messages: 15
Registered: December 2011
Location: San Diego, CA
Junior Member
Never mind. I've found the reason.

17 A NO
17 A YES

It did apply. I just didn't notice.
Re: some archived logs not applied on standby but looks ok [message #539288 is a reply to message #539259] Fri, 13 January 2012 01:16 Go to previous messageGo to next message
himabija
Messages: 33
Registered: December 2011
Location: San Francisco
Member
But the question is why oracle is showing like this?

SEQUENCE# S APP
---------- - ---
..
..

16 A NO
16 A YES
17 A NO
17 A YES
18 A YES
18 A NO
Why we are getting some 'NO' even if it is being applied?What is the reson for this dual response for single SEQUENCE#
Re: some archived logs not applied on standby but looks ok [message #539289 is a reply to message #539288] Fri, 13 January 2012 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add dest_id, thread# and standby_dest to your query result.

Regards
Michel
Re: some archived logs not applied on standby but looks ok [message #539370 is a reply to message #539289] Sat, 14 January 2012 01:06 Go to previous messageGo to next message
himabija
Messages: 33
Registered: December 2011
Location: San Francisco
Member
So far I have only noticed 'YES' and 'NO' applied-response for a single SEQENCE# ('NO'for primary 'YES' for standby) for primary database only. Whenever I had checked my standby I have only found output like below(Single applied-response for a specific sequence# ).


SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       334 YES                1 NO           1
       335 YES                1 NO           1
       336 YES                1 NO           1
       337 YES                1 NO           1
       338 YES                1 NO           1


So I was just confused how 'YES' and 'NO' both applied response can arise in secondary database (As in 'n2fontenay's post). Then I realize that he might have done 'SWITCHOVER' operation and that might cause this scenario. To be sure I have done below experiment.



standby>select name ,db_unique_name,database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
TEST      DGTEST                         PHYSICAL STANDBY

standby>select  SEQUENCE#,applied,DEST_ID,STANDBY_DEST,THREAD# from v$archived_log where SEQUENCE# >333;

standby>select  SEQUENCE#,applied,DEST_ID,STANDBY_DEST,THREAD# from v$archived_log where SEQUENCE# >333;

 SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       334 YES                1 NO           1
       335 YES                1 NO           1
       336 YES                1 NO           1
       337 YES                1 NO           1
       338 YES                1 NO           1
       339 YES                1 NO           1
       340 YES                1 NO           1
       341 YES                1 NO           1

8 rows selected.

standby>



Then I performed switchover

standby>
DGMGRL for Linux: Version 11.2.0.1.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@test
Password:
Connected.
DGMGRL> show configuration

Configuration - DGSOLUTION

  Protection Mode: MaxProtection
  Databases:
    TEST   - Primary database
    DGTEST - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to 'DGTEST';

--
--
[oracle@primary trace]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@test
Password:
Connected.
DGMGRL> show configuration

Configuration - DGSOLUTION

  Protection Mode: MaxProtection
  Databases:
    DGTEST - Primary database
    TEST   - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> 





Content of alert log in primary database during switchover operation

----------------------------------------------------------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (TEST)
Sat Jan 14 11:50:22 2012
MRP0 started with pid=36, OS id=5158
MRP0: Background Managed Standby Recovery process started (TEST)
 started logmerger process
Sat Jan 14 11:50:29 2012
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/test/datafile/TEST/redoTEST1.log
Clearing online log 1 of thread 1 sequence number 344
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Sat Jan 14 11:50:43 2012
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/test/datafile/TEST/redoTEST2.log
Clearing online log 2 of thread 1 sequence number 342
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/test/datafile/TEST/redoTEST3.log
Clearing online log 3 of thread 1 sequence number 343
Sat Jan 14 11:51:24 2012
Clearing online redo logfile 3 complete
Sat Jan 14 11:51:24 2012
Media Recovery Waiting for thread 1 sequence 345
---------------------------------------------------------------



Now my original Secondary database will become 'Primary' and now I check v$archived_log and got expected result.

standby> select name ,db_unique_name,database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
TEST      DGTEST                         PRIMARY

standby> select  SEQUENCE#,applied,DEST_ID,STANDBY_DEST,THREAD# from v$archived_log where SEQUENCE# >333;

 SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       334 YES                1 NO           1
       335 YES                1 NO           1
       336 YES                1 NO           1
       337 YES                1 NO           1
       338 YES                1 NO           1
       339 YES                1 NO           1
       340 YES                1 NO           1
       341 YES                1 NO           1
       342 YES                1 NO           1
       343 YES                1 NO           1
       344 YES                2 NO           1

 SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       345 NO                 1 NO           1
       345 YES                2 YES          1
       346 YES                2 YES          1
       346 NO                 1 NO           1
       347 YES                2 YES          1
       347 NO                 1 NO           1
       348 YES                2 YES          1
       348 NO                 1 NO           1
       349 YES                2 YES          1
       349 NO                 1 NO           1
       350 YES                2 YES          1

 SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       350 NO                 1 NO           1
       351 NO                 2 YES          1
       351 NO                 1 NO           1

25 rows selected.

standby>



Also I checked new standby database (which was "Primary" before switchover) . I found that after switchover (starting from sequence# 345) there is only one response for a single sequence#

primary> select name ,db_unique_name,database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
TEST      TEST                           PHYSICAL STANDBY

primary> select  SEQUENCE#,applied,DEST_ID,STANDBY_DEST,THREAD# from v$archived_log where SEQUENCE# >333;

 SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       334 YES                2 YES          1
       334 YES                1 NO           1
       335 YES                2 YES          1
       335 YES                1 NO           1
       336 YES                2 YES          1
       336 YES                1 NO           1
       334 YES                1 NO           1
       335 YES                1 NO           1
       337 YES                1 NO           1
       337 YES                2 YES          1
       338 YES                2 YES          1

 SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       338 YES                1 NO           1
       339 YES                2 YES          1
       339 YES                1 NO           1
       340 YES                1 NO           1
       340 YES                2 YES          1
       341 YES                2 YES          1
       341 YES                1 NO           1
       342 YES                2 YES          1
       342 YES                1 NO           1
       343 YES                2 YES          1
       343 YES                1 NO           1

 SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       344 YES                1 NO           1
       344 NO                 2 YES          1
       345 YES                1 NO           1
       346 YES                1 NO           1
       347 YES                1 NO           1
       348 YES                1 NO           1
       349 YES                1 NO           1
       350 YES                1 NO           1
       351 YES                1 NO           1
       352 YES                1 NO           1
       353 YES                1 NO           1

 SEQUENCE# APPLIED      DEST_ID STA    THREAD#
---------- --------- ---------- --- ----------
       354 IN-MEMORY          1 NO           1

34 rows selected.

primary>


[Updated on: Sat, 14 January 2012 01:14]

Report message to a moderator

Re: some archived logs not applied on standby but looks ok [message #539372 is a reply to message #539370] Sat, 14 January 2012 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback and demonstration.

Regards
Michel
Re: some archived logs not applied on standby but looks ok [message #539374 is a reply to message #539370] Sat, 14 January 2012 02:28 Go to previous messageGo to next message
John Watson
Messages: 4091
Registered: January 2010
Location: Global Village
Senior Member
What a brilliant piece of research! I didn't reply to the original question, because I couldn't understand what would produce that result. Thank you.
Re: some archived logs not applied on standby but looks ok [message #539390 is a reply to message #539374] Sat, 14 January 2012 11:26 Go to previous message
n2fontenay
Messages: 15
Registered: December 2011
Location: San Diego, CA
Junior Member
This is brilliant!

Thanks a lot for the deep research and the clarity Smile
Previous Topic: Backups on Physical standby database
Next Topic: Copy BFILE
Goto Forum:
  


Current Time: Sun Apr 20 21:58:27 CDT 2014

Total time taken to generate the page: 0.08655 seconds