Thursday, 19 April 2012

Why do have 2 sets of sql statements to create control file in a trace file?


When there is a need to perform a database clone or create a new control file and open the database, we do:

Alter database backup controlfile to trace ; (In the source system.)

This generates a trace file in the udump. We copy the appropriate set of statements from the trace into a script file, perform edit as necessary, and execute it when there is a need to re-create the control file.


Have we noticed that there are 2 sets of sql statements in the trace file to create a new control file?

Why do we have it?

-    The First set of sql statement  is for NORESETLOGS Case:

Example:
                             STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE


This opens the database with the NORESETLOGS option and should be used only if the current versions of all online logs are available.


- The second set of sql statement is for RESETLOGS CASE
Example:
                                                                   STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE


This will create a new control file and will open the database with the RESETLOGS option. The contents of online logs will be lost and all backups will be invalidated. This is used ONLY if online logs are damaged.

No comments:

Post a Comment