Home » RDBMS Server » Backup & Recovery » Archivelogs needed for Incomplete Recovery (Oracle 9.2.0.6.0 on HP-UX)
Archivelogs needed for Incomplete Recovery [message #461308] Thu, 17 June 2010 06:19 Go to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi,

How do I know which are the exact archivelogs needed for incomplete database recovery?

Thanks and Regards,
Chetana
Re: Archivelogs needed for Incomplete Recovery [message #461312 is a reply to message #461308] Thu, 17 June 2010 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle will tell you but if you query v$loghist you will know those you need.

Regards
Michel
Re: Archivelogs needed for Incomplete Recovery [message #461358 is a reply to message #461312] Thu, 17 June 2010 09:03 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hello Michel,

Thanks for the quick reply

I was looking the information in v$archived_log to get timing of archivelog creation

But I am not sure if I can follow the timestamp for incomplete recovery

In following example of database with 4 nodes (and thus 4 threads) we can see that to recover database upto 07:30 we will need following 4 files
Jun 17 08:03 SY2K_0002_0000043995.arc
Jun 17 08:05 SY2K_0004_0000047993.arc
Jun 17 09:12 SY2K_0003_0000043980.arc
Jun 17 09:25 SY2K_0001_0000037944.arc


file on each thread created after 07:30

but I have observed it couple of times that even the following could recovered database till 07:30
(see file of thread 1 is created before 07:30)
Jun 17 06:20 SY2K_0001_0000037943.arc
Jun 17 08:03 SY2K_0002_0000043995.arc
Jun 17 08:05 SY2K_0004_0000047993.arc
Jun 17 09:12 SY2K_0003_0000043980.arc


Could you please help me understand this

As i understand the file SY2K_0001_0000037944.arc shall include all changes from 06:20 till 09:25
then why the recovery till 07:30 was successful with file createc at 06:20 only i.e. SY2K_0001_0000037943?

Can we say SCN recorded in SY2K_0001_0000037943 was the one required for recovery till 07:30?

If this is the case where I could have found that?


Jun 16 21:10 SY2K_0003_0000043974.arc
Jun 16 21:10 SY2K_0004_0000047989.arc
Jun 16 21:10 SY2K_0001_0000037940.arc
Jun 17 01:03 SY2K_0001_0000037941.arc
Jun 17 01:07 SY2K_0002_0000043992.arc
Jun 17 01:10 SY2K_0003_0000043975.arc
Jun 17 01:13 SY2K_0004_0000047990.arc
Jun 17 01:14 SY2K_0004_0000047991.arc
Jun 17 02:35 SY2K_0003_0000043976.arc
Jun 17 02:55 SY2K_0003_0000043977.arc
Jun 17 02:55 SY2K_0001_0000037942.arc
Jun 17 02:55 SY2K_0004_0000047992.arc
Jun 17 02:55 SY2K_0002_0000043993.arc
Jun 17 05:11 SY2K_0003_0000043978.arc
Jun 17 06:09 SY2K_0002_0000043994.arc
Jun 17 06:20 SY2K_0001_0000037943.arc
Jun 17 06:21 SY2K_0003_0000043979.arc
Jun 17 08:03 SY2K_0002_0000043995.arc
Jun 17 08:05 SY2K_0004_0000047993.arc
Jun 17 08:05 SY2K_0002_0000043996.arc
Jun 17 09:12 SY2K_0003_0000043980.arc
Jun 17 09:25 SY2K_0001_0000037944.arc


Thanks and Regards,
Chetana
Re: Archivelogs needed for Incomplete Recovery [message #461368 is a reply to message #461358] Thu, 17 June 2010 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can we say SCN recorded in SY2K_0001_0000037943 was the one required for recovery till 07:30?

Because it does not contain any record useful to recover until this time.
I mean it is not because you say until 7:30 that it is exactly 7:30, it is until the closest time before that allows a consistent open of the database. It depends on the other threads to determine this time.

Quote:
Can we say SCN recorded in SY2K_0001_0000037943 was the one required for recovery till 07:30?

In this case (assuming all what reported is what actually hapenned), yes.

Regards
Michel
Re: Archivelogs needed for Incomplete Recovery [message #461371 is a reply to message #461368] Thu, 17 June 2010 10:21 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Thanks Michel

If we say we needed to recover database up to SCN 123456 to recover till 07:30. Now where I could have found such SCN number? in v$archived_log or v$log_hist?

and how I can co-relate between the archivelogs needed, 'low SCN' and 'Ckp SCN' reported by 'list backup' command

Thanks and Regards,
Chetana
Re: Archivelogs needed for Incomplete Recovery [message #461386 is a reply to message #461371] Thu, 17 June 2010 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can found it nowhere, afaik, I think Oracle get it analyzing and applying the archived logs.
If you could post the restore/recover session you mentionned it should be easier to see on an example.

Regards
Michel
Re: Archivelogs needed for Incomplete Recovery [message #461461 is a reply to message #461386] Fri, 18 June 2010 02:55 Go to previous message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hello Michel,

Thanks for your help

I think my quetsion is not clear enough

I will try to rephrase it

1) I said
Quote:

If we say we needed to recover database up to SCN 123456 to recover till 07:30. Now where I could have found such SCN number? in v$archived_log or v$log_hist?


Please refer following output from v$loghist
select * from v$loghist;

select * from v$loghist;

 THREAD#  SEQUENCE#     FIRST_CHANGE# FIRST_TIME                    SWITCH_CHANGE#
-------- ---------- ----------------- -------------------- -----------------------
       1        809     5767518469198 16-JUN-2010 00:32:46           5767518797452
       1        810     5767518797452 17-JUN-2010 00:30:03           5767518797923
       1        811     5767518797923 17-JUN-2010 00:32:38           5767518988566
       1        812     5767518988566 17-JUN-2010 14:26:21           5767518988931
       1        813     5767518988931 17-JUN-2010 14:27:58           5767518989176
       1        814     5767518989176 17-JUN-2010 14:28:57           5767518990640



Now if I could co-relate the 'ckp scn' or 'low scn' of 'list backup' command with the above 'first_change#' then it would be easy for me to know which are the archivelogs needed for recovery

Thanks and Regards,
Chetana
Previous Topic: Datafile recovery not repairing corrupted block
Next Topic: How to make cold backup
Goto Forum:
  


Current Time: Sat Aug 23 14:59:54 CDT 2014

Total time taken to generate the page: 0.08023 seconds