v$archived_log in standby database

Printable View

09-12-2004, 11:40 PM
marist89

v$archived_log in standby database

I have a simple 2 node standby setup. On my primary, I query v$archived_log for my secondary destination and it correctly shows the logs that have been archived and applied on the standby. However, the applied flag is not set for some logs, but the alert.log on the standby says it was applied.

Code:

1 select sequence#, dest_id, archived, applied, completion_time 2 from v$archived_log 3 where dest_id = 2 4 and sequence# > 36925 5* order by sequence# [email protected]> / SEQUENCE# DEST_ID ARC APP COMPLETION_TIME ---------- ---------- --- --- ------------------- 36926 2 YES YES 09/12/2004 20:17:46 36927 2 YES YES 09/12/2004 20:18:09 36928 2 YES YES 09/12/2004 20:18:36 36929 2 YES YES 09/12/2004 20:18:59 36930 2 YES NO 09/12/2004 20:25:49 36931 2 YES YES 09/12/2004 20:25:51 36932 2 YES YES 09/12/2004 20:56:02 36933 2 YES YES 09/12/2004 21:26:29 36934 2 YES YES 09/12/2004 21:56:55 36935 2 YES YES 09/12/2004 22:27:21 36936 2 YES YES 09/12/2004 22:57:47 36937 2 YES YES 09/12/2004 23:28:12 12 rows selected. Elapsed: 00:00:00.21 Media Recovery Waiting for thread 1 seq# 36922 Media Recovery Log /u07/oraarch/xxx/1_36922.dbf Media Recovery Waiting for thread 1 seq# 36923 Media Recovery Log /u07/oraarch/xxx/1_36923.dbf Media Recovery Waiting for thread 1 seq# 36924 Media Recovery Log /u07/oraarch/xxx/1_36924.dbf Media Recovery Waiting for thread 1 seq# 36925 Media Recovery Log /u07/oraarch/xxx/1_36925.dbf Media Recovery Log /u07/oraarch/xxx/1_36926.dbf Media Recovery Log /u07/oraarch/xxx/1_36927.dbf Media Recovery Log /u07/oraarch/xxx/1_36928.dbf Media Recovery Log /u07/oraarch/xxx/1_36929.dbf Media Recovery Log /u07/oraarch/xxx/1_36930.dbf Media Recovery Log /u07/oraarch/xxx/1_36931.dbf Media Recovery Waiting for thread 1 seq# 36932 Media Recovery Log /u07/oraarch/xxx/1_36932.dbf Media Recovery Waiting for thread 1 seq# 36933 Media Recovery Log /u07/oraarch/xxx/1_36933.dbf Media Recovery Waiting for thread 1 seq# 36934 Media Recovery Log /u07/oraarch/xxx/1_36934.dbf Media Recovery Waiting for thread 1 seq# 36935 Media Recovery Log /u07/oraarch/xxx/1_36935.dbf Media Recovery Waiting for thread 1 seq# 36936 Media Recovery Log /u07/oraarch/xxx/1_36936.dbf Media Recovery Waiting for thread 1 seq# 36937 Media Recovery Log /u07/oraarch/xxx/1_36937.dbf Media Recovery Waiting for thread 1 seq# 36938 $ r ls ls -l /u07/oraarch/xxx/1_36930.dbf -rw-r----- 1 oracle dba 32668160 Sep 12 20:25 /u07/oraarch/xxx1/1_36930.dbf

Can anybody enlighten me or point me to some documentation that explains this?
09-13-2004, 04:52 AM
xiaomao

When the sequence no, in your case is 36930, was not able to send to standby due to whatever reason, it will be sent later on by an ARCH process as per FAL request. In this case, V$ARCHIVED_LOG.APPLIED column of the primary node won't be updated thus it still shows '
NO' status but actually it already applied in standby db.
09-13-2004, 03:22 PM
marist89

How can I track that this was satisfied by FAL request?
09-13-2004, 03:59 PM
stmontgo

I've seen this if I interrupt the automatic recovery process , manually apply logs and then resume sustained recovery. Not sure if that applies in your case.

Steve
09-13-2004, 04:09 PM
marist89

Quote:

Originally posted by stmontgo
I've seen this if I interrupt the automatic recovery process , manually apply logs and then resume sustained recovery. Not sure if that applies in your case.

Steve

Both the primary and standby were up and down during this period, but I never manually recovered anything.
09-13-2004, 04:36 PM
Axr2

Are you using data guard?
09-13-2004, 04:40 PM
Axr2

Quote:

Originally posted by marist89
How can I track that this was satisfied by FAL request?

Not sure I understand the above. In your first post you said -

Quote:

On my primary, I query v$archived_log for my secondary destination and it correctly shows the logs that have been archived and applied on the standby.

That pretty much means it was satisfied by the FAL right?

In any case, here are some queries. See if they help your cause.

COLUMN destination FORMAT A35 WRAP
column process format a7
column archiver format a8
column ID format 99
select dest_id "ID",destination,status,target,
archiver,schedule,process,mountid
from v$archive_dest;
select dest_id,process,transmit_mode,async_blocks,
net_timeout,delay_mins,reopen_secs,register,binding
from v$archive_dest;
column error format a55 tru
select dest_id,status,error from v$archive_dest;
column message format a80
select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;
select ads.dest_id,max(sequence#) "Current Sequence",max(log_sequence) "Last Archived",
max(applied_seq#) "Last Sequence Applied"
from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
where ad.dest_id=al.dest_id
and al.dest_id=ads.dest_id
group by ads.dest_id;
column SRL_Count format 99
column SRL_Active format 99
select dest_id,database_mode,recovery_mode,
protection_mode,standby_logfile_count "SRL_COUNT",standby_logfile_active "SRL_ACTIVE",
archived_seq#, applied_seq#
from v$archive_dest_status;
select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;
select group#,sequence#,bytes from v$standby_log;
select group#,thread#,sequence#,bytes,archived,status from v$log;
09-13-2004, 05:04 PM
marist89

Quote:

Originally posted by Axr2
Are you using data guard?

no
09-13-2004, 05:10 PM
marist89

Quote:

Originally posted by Axr2
Not sure I understand the above. In your first post you said -

I'm sure it was applied, but not sure how it was applied. The alert.log of the standby shows it was applied, but v$archived_log doesn't.

I guess my big question is how can I verify all my logs have been applied without looking at the alert.log on the standby.
09-13-2004, 05:15 PM
Axr2

Quote:

Originally posted by marist89

I guess my big question is how can I verify all my logs have been applied without looking at the alert.log on the standby. [/B]

See first sql in

http://www.dbasupport.com/forums/sho...threadid=44415
09-13-2004, 05:27 PM
marist89

Quote:

Originally posted by Axr2

select ads.dest_id,max(sequence#) "Current Sequence",max(log_sequence) "Last Archived",
max(applied_seq#) "Last Sequence Applied"
from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
where ad.dest_id=al.dest_id
and al.dest_id=ads.dest_id
group by ads.dest_id;

I don't think this query is accurate. Right now, by alert.log says:

Code:

Media Recovery Log /u07/oraarch/tstore1/1_37032.dbf Mon Sep 13 16:51:25 2004 Media Recovery Waiting for thread 1 seq# 37033 Media Recovery Log /u07/oraarch/tstore1/1_37033.dbf Mon Sep 13 17:21:22 2004 Media Recovery Waiting for thread 1 seq# 37034

and this query shows:

Code:

[email protected]> l 1 select ads.dest_id,max(al.sequence#) "Current Sequence",max(ad.log_sequence) "Last Archived", 2 max(ads.applied_seq#) "Last Sequence Applied" 3 from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads 4 where ad.dest_id=al.dest_id 5 and al.dest_id=ads.dest_id 6* group by ads.dest_id [email protected]> / DEST_ID Current Sequence Last Archived Last Sequence Applied ---------- ---------------- ------------- --------------------- 1 37033 37033 0 2 37033 37033 37032

The query is telling me 37032 was the last applied and the alert.log is telling me 37033 was applied.
09-13-2004, 06:14 PM
Axr2

Issue
select max(sequence#) from v$archived_log where applied='YES';
on the standby then.

Am still not sure about the descrepancy in sequence #s in the query ouput you listed. I would have to test some more.

Switch some logfiles manually, then issue the below on the standby.

select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;

It'll give you an exact picture of what's happening on the standby.
09-13-2004, 09:18 PM
stmontgo

what about V$ARCHIVE_GAP?

That might help. Also consider that FAL does work so that view
may give you false alarms

for instance

log 1 shippied and applied to standby
log 2 starts to ship and network hiccups, log does not get sent
when log 3 is generated it will detect the gap and send log 3 and log two

In that instance it's possible that log three gets received first and hence a gap may be detected.
09-13-2004, 11:16 PM
xiaomao

Quote:

How can I track that this was satisfied by FAL request?

1.Do you set FAL parameters in your standby init.ora? ('FAL_SERVER' & 'FAL_CLIENT')
2.FAL is running on primary server. Check you primary database alert.log, you should be able to see some entries like: 'ARC0: Begin FAL archive','ARC0: Complete FAL archive...
3.If no FAL parameters set, ARCH also can automatically send missing archived logs to standby db.

Quote:

Both the primary and standby were up and down during this period, but I never manually recovered anything.

Most of time, you don’t have to manually recovered/sync standby db as Oracle automatically resolves the gap issues. (oracle 9i)

Quote:

I'm sure it was applied, but not sure how it was applied. The alert.log of the standby shows it was applied, but v$archived_log doesn't.

Again, it comes back to your original question. You may refer to oracle article '263994.1' for the answer.

Quote:

I guess my big question is how can I verify all my logs have been applied without looking at the alert.log on the standby.

For me, I always query the max(sequence#) from V$archived_log in primary db to get the latest logs applied. Sometime I also need to verify it in alert.log if any doubt. As noted in oracle article 150214.1, you can’t trust V$archived_log 100%.

In article 150214.1---
'Note: the sql statements give additional information (confirmation)of information of alert files. If these sql statements return rows it doesn't necessarily mean ther's an actual gap! Always check the alert files as in 1b.'