Log applied datetime [message #601840] |
Tue, 26 November 2013 05:42 |
|
Michel Cadot
Messages: 68694 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 #601936 is a reply to message #601864] |
Wed, 27 November 2013 10:06 |
John Watson
Messages: 8951 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 #601950 is a reply to message #601941] |
Wed, 27 November 2013 12:33 |
John Watson
Messages: 8951 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 |
|
Michel Cadot
Messages: 68694 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...
[Updated on: Wed, 27 November 2013 13:12] Report message to a moderator
|
|
|
|