What is the difference between RESETLOGS and NORESETLOGS when you create a control file ?
This question was posed by Sandeep today. One of the good responses to this question is in orafaq forums:
Oracle is forcing you to make a choice:
Use RESETLOGS after incomplete recovery (when the entire redo stream wasn't applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.
Use NORESETLOGS when doing complete recovery (when the entire redo stream was applied). Oracle will continue using the existing (valid) log files.
Metalink 16530.1 has this information:
After recover database operation, open the database with: ALTER DATABASE OPEN [NO]RESETLOGS
NORESETLOGS
The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.
RESETLOGS
CAUTION: Never use RESETLOGS unless necessary.
Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!
Before using the RESETLOGS option take an offline backup of the database.
The RESETLOGS option clears all the online redo logs and modifies all the online data files to indicate no recovery is needed. After resetting the redo logs none of the existing log files or data file backups can be used. In the control file, the log sequence number is modified, which is very important for recovery purposes. The recovery will be applied only to the log files whose sequence number is greater than log sequence number in the control file. One has to be very cautious when using RESETLOGS option. It is important to remember that all datafiles must be online otherwise they will become useless once the database is up
Subscribe to:
Post Comments (Atom)
Posts
10 comments:
question:
an oracle systems has been running for a long time and there are many archivelog files. We want to do full offline database backup by shutting down oracle and backup whole files. If we don't want to backup those archivelogs, should we then use resetlogs. We don't need those archivelogs as we don't need to do rollback.
mohdoryza.ananda@yahoo.com
Hi,
There is no need to resetlogs in case of a cold backup.
1. Shutdown the DB .
2. Take datafile + controlfile + redo backup.
When you shutdown the DB for the Cold backup, it will fire a check point and freeze the header of all datafile and sync with controlfile.
So, when you start it after the backup is over, it will not ask for any recovery.
- Vikram
if database is up and running,if any one of the user delete one of the datafile,whether database will be consistent
Hi Sunel,
If user deletes one of the datafile from a running database, the database will not be consistent and crash. This is a recovery scenario. I know this because it happened to us once where a newbie DBA compressed the datafiles of a live database to create space.
- Vikram
I have a 11g db runing. I have a full bacckup of the database using expdp. Then a week later i put the db on archive log mode and did a level 0 backup with rman. Now the system01.dbf is corrupted and the archives log file are missing, the db is not working. is it possible to restore the full backup from the datapump, if yes how?
you should have valid backup including archivelog and redo logs to make db as it was before crash..
if one of the (after o backup) archivelog, redo log then there is only the way to do incomplete recovery database..
Hi
explain what will hapen internally in database when i issue this statement
"BACKP DATABASE"
Ramesh
I have a doubt on complete recovery,
Please help me .
After creating a controlfile manually without any backup how the database goes to a consistent state? Because the newly created controlfile does not have the scn details as well as checkpoint information that the deleted controlfile had.
Also after restoring the controlfile is it needed to open the database in resetlogs mode? If not why?
After creating a controlfile manually without any backup how the database goes to a consistent state? Because the newly created controlfile does not have the scn details as well as checkpoint information that the deleted controlfile had.
Also after restoring the controlfile is it needed to open the database in resetlogs mode? If not why?
After creating a controlfile manually without any backup how the database goes to a consistent state? Because the newly created controlfile does not have the scn details as well as checkpoint information that the deleted controlfile had.
Also after restoring the controlfile is it needed to open the database in resetlogs mode? If not why?
Good question
I too was very focusing on this one when I restore controlfile and open the database with resetlogs and I try to open with noresetlogs but no luck.
With this point I clear my doubt and according to my understanding if you recreated controlfile or restore from autobackup the scn and ckpt information is different.
Anyway a control file restored from a backup has an SCN taken at that "remote" time, different compared with those currently available in the datafiles and redo logs and so they have to be resynchronized.
To resynchornized controlfile with redologs and datafiles we need to do resetlogs and you know resetlogs: "RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database."
Post a Comment