Home » Server Options » Data Guard » Log applied datetime (10.2.* and 11.2.*)
Log applied datetime [message #601840] Tue, 26 November 2013 05:42 Go to next message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is there anyone knowing if it is possible to know and where I can find when each (archived) log was applied from the standby database?

Regards
Michel
Re: Log applied datetime [message #601860 is a reply to message #601840] Tue, 26 November 2013 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 23035
Registered: January 2009
Senior Member
It should be recorded in the alert_SID.log file
Re: Log applied datetime [message #601864 is a reply to message #601860] Tue, 26 November 2013 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know, I have nothing in diag/alert or diag/trace directory and "find" does not return any alert.log for this standby database/instance.

Anyway it would be great if I could get it from SQL (I didn't specify it in the question, my bad) and, as the database is not open, I can't use external tables (and so V$DIAG_ALERT_EXT is not available).

Re: Log applied datetime [message #601936 is a reply to message #601864] Wed, 27 November 2013 10:06 Go to previous messageGo to next message
John Watson
Messages: 4797
Registered: January 2010
Location: Global Village
Senior Member
I saw this only just now, is it fixed? I could set up data guard this evening (I'm in CET) on Windows or Linux and try to find out, it won't take long. And I'll be sitting in a hotel with nothing else to do.
Re: Log applied datetime [message #601941 is a reply to message #601936] Wed, 27 November 2013 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the answer, no it is not.
I don't know why I have no alert.log file but I don't care for the moment, I will investigate this in a less loaded time (but if you have an answer or a clue it is welcome Smile ); my OS is Linux.
My issue is to get an history of the log application from the standby database.

Regards
Michel
Re: Log applied datetime [message #601950 is a reply to message #601941] Wed, 27 November 2013 12:33 Go to previous messageGo to next message
John Watson
Messages: 4797
Registered: January 2010
Location: Global Village
Senior Member
It took me 40 minutes to get Data Guard working. Bad! You need to do better than that for OCM. I must be out of practice. Anyway, the alert log probably doesn't tell you what you need to know. The situation seems to be complicated by the use of standby logs and real-time apply. Here is an extract from the physical standby. The primary is LONDON, the standby is BERLIN:
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Assigned to RFS process 8070
RFS[3]: Selected log 4 for thread 1 sequence 14 dbid -1126843666 branch 832613104
Wed Nov 27 18:07:48 2013
Media Recovery Log /u01/app/oracle/fast_recovery_area/BERLIN/archivelog/2013_11_27/o1_mf_1_11_99df7lmw_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/BERLIN/archivelog/2013_11_27/o1_mf_1_12_99df7ln2_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/BERLIN/archivelog/2013_11_27/o1_mf_1_13_99df7n5d_.arc
Media Recovery Waiting for thread 1 sequence 14 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 14 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/BERLIN/onlinelog/o1_mf_4_99df2r7k_.log
  Mem# 1: /u01/app/oracle/fast_recovery_area/BERLIN/onlinelog/o1_mf_4_99df2rb9_.log
Wed Nov 27 18:08:29 2013
Media Recovery Waiting for thread 1 sequence 15 (in transit)
Wed Nov 27 18:08:29 2013
Archived Log entry 6 added for thread 1 sequence 14 ID 0xbcd530ee dest 1:
Wed Nov 27 18:08:29 2013
RFS[3]: Selected log 5 for thread 1 sequence 15 dbid -1126843666 branch 832613104
Recovery of Online Redo Log: Thread 1 Group 5 Seq 15 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/BERLIN/onlinelog/o1_mf_5_99df2wxk_.log
  Mem# 1: /u01/app/oracle/fast_recovery_area/BERLIN/onlinelog/o1_mf_5_99df2x0w_.log
Wed Nov 27 18:10:33 2013
Media Recovery Waiting for thread 1 sequence 16 (in transit)
Wed Nov 27 18:10:33 2013
Archived Log entry 7 added for thread 1 sequence 15 ID 0xbcd530ee dest 1:
Wed Nov 27 18:10:33 2013
RFS[3]: Selected log 4 for thread 1 sequence 16 dbid -1126843666 branch 832613104
Recovery of Online Redo Log: Thread 1 Group 4 Seq 16 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/BERLIN/onlinelog/o1_mf_4_99df2r7k_.log
  Mem# 1: /u01/app/oracle/fast_recovery_area/BERLIN/onlinelog/o1_mf_4_99df2rb9_.log
it shows applying sequences 11, 12, and 13. These will have been the logs generated while I was getting things wrong, after making the backup of the controlfile and before starting managed recovery.
Then you see 14 being recovered from a standby log group 4, which is archived by the standby at 18:08:29.
At 18:08:29, it starts receiving sequence 15 into standby log group 5, which gets applied and is archived at 18:10:33.

This query,
berlin> select sequence#,applied,completion_time,next_time from v$archived_log order by 1;

 SEQUENCE# APPLIED   COMPLETION_TIME     NEXT_TIME
---------- --------- ------------------- -------------------
         6 YES       2013-11-27 18:04:57 2013-11-27 17:49:50
         7 YES       2013-11-27 18:04:57 2013-11-27 17:51:46
        11 YES       2013-11-27 18:07:47 2013-11-27 18:03:35
        12 YES       2013-11-27 18:07:46 2013-11-27 18:06:57
        13 YES       2013-11-27 18:07:48 2013-11-27 18:07:48
        14 YES       2013-11-27 18:08:29 2013-11-27 18:08:29
        15 IN-MEMORY 2013-11-27 18:10:33 2013-11-27 18:10:32

7 rows selected.

berlin>
does seem to show when the log was applied COMPLETION_TIME), as well as when it was created. At the time of running, redo was being received for sequence 16. Is it any good?
Hope it helps.

--update: I've just read the docs, and COMPLETION_TIME must be when the standby log was archived. So that does NOT mean that it has been applied. Unless, of course, APPLIED=YES or IN-MEMORY.
Sorry, that was all I could come up with.

[Updated on: Wed, 27 November 2013 12:55]

Report message to a moderator

Re: Log applied datetime [message #601954 is a reply to message #601950] Wed, 27 November 2013 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the test, we also use MAXIMUM PERFORMANCES and real time apply.

I will check with our databases (but I only will be at this office next Monday) but documentation says that completion_time is the time when "Time when the archiving completed" which may not match when it is applied (especially when you have a gap, for instance when the database is opened in read only mode, not Active Data Guard). In your output, completion_time for sequence time 12 is less than the one for sequence 11 (which is not strange when multiple files are transferred at the same time, for instance after a network break or when resolving some gaps) but I doubt 12 were applied before 11 (and your listing showed them in the expected order). So I wonder if completion_time is not when the archive/standby logs is completely transferred/filled.

Anyway, this is a way I can investigate, tests will be not so hard to do to understand what is actually completion_time.

Thanks
Michel

Update: I only see your update after posting my answer; so we are in sync... Smile

[Updated on: Wed, 27 November 2013 13:12]

Report message to a moderator

Re: Log applied datetime [message #602111 is a reply to message #601840] Fri, 29 November 2013 10:08 Go to previous message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For information, I also opened a topic there.

Previous Topic: Data Guard
Next Topic: Error with shipping redo log from primary to standby
Goto Forum:
  


Current Time: Mon Nov 24 09:11:00 CST 2014

Total time taken to generate the page: 0.09398 seconds