| some archived logs not applied on standby but looks ok [message #539149] |
Wed, 11 January 2012 11:13  |
 |
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 #539370 is a reply to message #539289] |
Sat, 14 January 2012 01:06  |
 |
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 |
|
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
