Home » RDBMS Server » Backup & Recovery » Old archive logs after open resetlogs (Windows,10.2.0.3)
Old archive logs after open resetlogs [message #471946] Wed, 18 August 2010 00:42 Go to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
Hi suppose I restore my database from my backup and perform a incomplete recovery and open the database in RESETLOGS mode.Can I use the old archive logs again for performing recovery
Re: Old archive logs after open resetlogs [message #471948 is a reply to message #471946] Wed, 18 August 2010 00:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can I use the old archive logs again for performing recovery
I give up.
Can you?
Re: Old archive logs after open resetlogs [message #471953 is a reply to message #471946] Wed, 18 August 2010 00:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Yes, this was a major change with 10g. Prior to 10g, ALTER DATABASE OPEN RESETLOGS invalidated all previous archives, but from 10g on it is possible to recover through resetlogs. You'd better test it though, to prove that you really can.

(In fact, it always was possible to recover through a resetlogs, but you had to to incomplete recovery up to the SCN when it was opened, then manually copy in the post-open controlfile, then continue the recovery. So it wasn't supported.)
Re: Old archive logs after open resetlogs [message #471961 is a reply to message #471946] Wed, 18 August 2010 01:20 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
So consider I have a backup taken yesterday at 10 am and today I perform a incomplete recovery and open the database with resetlogs.After opening the database it resets the sequence and create 5 more archive logs.Now tomorrow my database crashes again and the only backup I have is yesterday's backup(Taken before resetlogs) So from this backup can I apply the logs created before the first crash as well as the archive logs created after the incomplete recovery to bring my database to most recent possible scn??
Re: Old archive logs after open resetlogs [message #471965 is a reply to message #471961] Wed, 18 August 2010 01:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
You'd better test it though, to prove that you really can.
Re: Old archive logs after open resetlogs [message #471967 is a reply to message #471961] Wed, 18 August 2010 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Old archive logs after open resetlogs [message #471969 is a reply to message #471967] Wed, 18 August 2010 01:42 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
Can you tel me If i am correct on what scenarios we must use open RESETLOGS to recover database

1.When we use backup copies of data files and have current copy of control file

2.When the contents of online redo log files are not accessible and we have current data files

When we have accessible redo log files and current datafiles we can perform noresetlogs by using create control file with noresetlogs
Re: Old archive logs after open resetlogs [message #471972 is a reply to message #471969] Wed, 18 August 2010 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. No unless you make an until time recovery
2. Yes unless online redo logs are not necessary to make a complete recovery.
3. Why do you create a control file?

Regards
Michel
Re: Old archive logs after open resetlogs [message #471974 is a reply to message #471972] Wed, 18 August 2010 02:03 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
I user create control file when I lost all my control files but I have datafiles as well as online logfiles unharmed so instead of using recover database using backup controlfile which forces resetlogs I will use create controlfile which I backed up to trace with noresetlogs.

Re: Old archive logs after open resetlogs [message #471975 is a reply to message #471974] Wed, 18 August 2010 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK.

The best way to know what you can do and have is to test it by yourself.
When you'll do it, post the results.


Regards
Michel
Re: Old archive logs after open resetlogs [message #472016 is a reply to message #471975] Wed, 18 August 2010 05:46 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
Hi Michel,

The best way to know what you can do and have is to test it by yourself.


I did test it and I was able to recover the database by using backup taken from previous incarnation and using the archive logs generated from the previous as well as the current incarnation.


But I have a new doubt suppose I insert two rows into a table and commit the transaction.Then I shut down the database and before the I open the database next time the current redo log group corrupts.So when I open the database next time I clear all the redo log groups including the unarchived current group.In this case will the two rows that I inserted at last and was present in current online redo log group(before I cleared it) will be present in the database or not after opening it?
Re: Old archive logs after open resetlogs [message #472020 is a reply to message #472016] Wed, 18 August 2010 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I did test it and I was able to recover the database by using backup taken from previous incarnation and using the archive logs generated from the previous as well as the current incarnation.

Great! Now to help others can you post the steps you executed.

Quote:
But I have a new doubt suppose I insert two rows into a table and commit the transaction.Then I shut down the database and before the I open the database next time the current redo log group corrupts. So when I open the database next time I clear all the redo log groups including the unarchived current group.

If the shutdown was not abort then redo logs are emptied so no problem to restart.
I advice you to take a full backup.

Quote:
In this case will the two rows that I inserted at last and was present in current online redo log group(before I cleared it) will be present in the database or not after opening it?

As I said, if the shutdown was not abort, the database is in a clear and consistent state, so your rows are there.
If the shutdown was abort then you have to recover to a time of the last archived log you have and open reset logs to .

Regards
Michel

[Updated on: Wed, 18 August 2010 06:23]

Report message to a moderator

Re: Old archive logs after open resetlogs [message #472038 is a reply to message #472020] Wed, 18 August 2010 08:51 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
Sure Michel This is what I did

1.Create a database in archive log mode
2.Create a backup of the database
3.create a table and insert some rows in the table and manually switch the log files(I did it twice)
4.shut down database
5.Create a scenario in which you are forced to open the database in resetlogs mode(I deleted the current redo log file)
6.Solve the problem and open the database with resetlogs option
7.Insert some more rows into the table and manually switch the logfiles again
8.shut down the database
9.Restore the database using the backup taken in the 2nd step
10.specify the logfiles generated from the previous incarnation as well as the current incarnation when recovery asks for it.
11.Now the database will open and the table will contain all the inserted rows.
Re: Old archive logs after open resetlogs [message #472046 is a reply to message #472038] Wed, 18 August 2010 09:40 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks.

Regards
Michel


Previous Topic: backup and recovery
Next Topic: Recovery of a Noncritical Data File in Archivelog Mode
Goto Forum:
  


Current Time: Thu Mar 28 19:15:29 CDT 2024