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
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.
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
Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps