Recreating the Controlfile in RAC

PURPOSE
-------
This article describes how you can recreate your controlfile in RAC.


SCOPE & APPLICATION
-------------------
For DBA's requiring to recreate the controlfile.

WARNING:
--------

You should only need to recreate your control file under very special
circumstances:

- All current copies of the control file have been lost or are corrupted.

- You need to change a "hard" database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,
MAXLOGHISTORY, etc.

- You are restoring a backup in which the control file is corrupted or
missing.

- Oracle Customer Support advises you to do so.

- If you are moving your database to another machine which is
running the same operating system but the location of the datafiles,
logfiles is not the same.


RECREATING THE CONTROLFILE IN RAC
---------------------------------

If are recreating your controlfile from an existing one, use the following
steps to recreate your controlfiles. If you have lost all copies of the
controlfile, a new one will need to be generated using SQL. The syntax
is available in the SQL Reference manual for all versions but consideration
for Step 4 onward must be taken into account.

1. Connected to an open or mounted RAC instance via sqlplus, issue the
following command to dump a trace file that contains a create controlfile
script. The file will be generated in the user_dump_dest on the local
instance (show parameter dump in server manager to find user_dump_dest):

SQL> alter database backup controlfile to trace;

2. Find the trace file using "ls -ltr" in the user_dump_dest, it will
probably be the last or one of the last files listed as it will be very
recent. At this point you may want to move or rename the file to an easy
to remember name. In my example I use the name "create_control.sql".

3. Once the file is opened, remove all of the header information
up to the "STARTUP NOMOUNT" command. In later versions 10+ for RAC
you can remove the "NORESETLOGS" "CREATE CONTROLFILE" statement and keep
the "RESETLOGS" version.

If your redo logs still exist in the correct locations and the create
controlfile statement contains the appropriate "alter database add logfile"
statements, skip to step 5. If your redo logs have been removed or need to
be recreated, continue:

At this point the controlfile should look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "RAC" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ocfs01/rac/redo01.log' SIZE 50M,
GROUP 2 '/ocfs01/rac/redo02.log' SIZE 50M,
GROUP 3 '/ocfs01/rac/redo03.log' SIZE 50M,
GROUP 4 '/ocfs01/rac/redo04.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/ocfs01/rac/system01.dbf',
'/ocfs01/rac/undotbs01.dbf',
'/ocfs01/rac/sysaux01.dbf',
'/ocfs01/rac/undotbs02.dbf',
'/ocfs01/rac/users01.dbf'
CHARACTER SET WE8ISO8859P1
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/ocfs01/rac/temp01.dbf'
SIZE 167772160 REUSE AUTOEXTEND OFF;

4. Now the create controlfile script needs to be altered for RAC.

a. Notice that in the script all of the logfiles are listed together. We
will need to seperate these out by thread. First remove all logfiles listed
that are not from the 1st instance. Once these are removed, after the
create controlfile statement add an "alter database add logfile thread"
statement(s) for each thread of redo to be added (usually 1 per node).

b. Because not all of the logfiles are listed (additional threads added
after the controlfile is created), you will need to use the RESETLOGS
option on the create controlfile statement. This is necessary in RAC
and will reset your scn's back to 0. It is highly recommended to take
a full backup of the database after completing this procedure.

c. We must now set the appropriate recovery commands for the RESETLOGS.
If all datafiles are consistent and no additional recover is required
on the database you can simply place the following commands at the bottom
of the script:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
CANCEL
ALTER DATABASE ADD LOGFILE...
ALTER DATABASE OPEN RESETLOGS;

If additional datafile recovery is needed, you will need to gather information
from the existing controlfile (mount and query V$log and V$logfile) to get the
full path and file name for each online redo log. You will need to manually
run the recovery (take it out of the create controlfile script) and when recovery
prompts for an archive log that does not exist you will need to type in the full
path and file name for the online log that corresponds with the sequence number
requested. When this is finished you should get a "Media Recovery Complete"
message. For example:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
# recovery prompts for non-existant logfile arch_123.log
/u01/redo_log_dest/redo123.log
# Should now get "Media Recovery Complete" message after online logs are applied.
ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
# repeat for other threads if applicable

e. After the alter database open command, add an "alter database enable
public thread #" command(s). Do this for each additional thread to be added.

Now the create controlfile script should look something like the following:

set echo on
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "RAC" RESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ocfs01/rac/redo01.log' SIZE 50M,
GROUP 2 '/ocfs01/rac/redo02.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/ocfs01/rac/system01.dbf',
'/ocfs01/rac/undotbs01.dbf',
'/ocfs01/rac/sysaux01.dbf',
'/ocfs01/rac/undotbs02.dbf',
'/ocfs01/rac/users01.dbf'
CHARACTER SET WE8ISO8859P1
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
CANCEL
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 3 '/ocfs01/rac/redo03.log' SIZE 50M REUSE,
GROUP 4 '/ocfs01/rac/redo04.log' SIZE 50M REUSE;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/ocfs01/rac/temp01.dbf'
SIZE 167772160 REUSE AUTOEXTEND OFF;

5. Shutdown all instances cleanly with shutdown immediate, shutdown
transactional, or shutdown normal.

6. This would be a good time to make backup copies of the current
controlfiles.

7. Verify that you are running from the instance designated for thread 1.
This is because any logfiles designated in the create controlfile statement
will go into thread 1. You will get errors if you are running from another
instance. Make sure that the local init/spfile file states:

instance=1
thread=1

8. Make sure the cluster_database=false parameter is set in the init/spfile
to re-create the controlfile.

9. Now we are ready to run the script. Connect to server manager on Node
1 and as internal or sys and execute the script:

SQL> @create_control.sql

If you get the "Statement processed" message, the database will be
opened with a brand new control file.

10. Make sure the cluster_database=true parameter is set in the init/spfile.

11. Start other instances.

12. At the earliest convenience, take a full backup of the database