Controlfile recreation with backup

We recreate controlfile when

1. Control file is corrupted

2. Rename a database

3. change the number of maxlogfiles

Take a trace of the current control file

SQL>alter database backup controlfile to trace;

The trace file of the control file is saved in the background_dump_destination in Oracle 11g.

[ To know the background_dump_destination just type

SQL>show parameter background; ]

Now in a separate terminal view the trace at the background_dump_destination

copy the code to create control file into a sql file [ preferably in the instance directory /home/app/oracle/oradata/TEST ] for example

[oracle@localhost trace]$ vi control_file.sql

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/home/app/oracle/oradata/TEST/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/home/app/oracle/oradata/TEST/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/home/app/oracle/oradata/TEST/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/home/app/oracle/oradata/TEST/system01.dbf',

'/home/app/oracle/oradata/TEST/sysaux01.dbf',

'/home/app/oracle/oradata/TEST/undotbs01.dbf',

'/home/app/oracle/oradata/TEST/users01.dbf',

'/home/app/oracle/oradata/TEST/example01.dbf',

'/home/app/oracle/oradata/TEST/big_ts.dbf',

'/home/app/oracle/oradata/TEST/auto_ext.dbf'

CHARACTER SET WE8MSWIN1252

;

save and exit

gracefully shutdown the database [ in the case of deleting the control file while the database is up and running, shutdown abort is the only option ]

Now start the databse in nomount state and

run the script

SQL>@/home/app/oracle/oradata/TEST/create_control_file;

startup nomount

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1337720 bytes

Variable Size 251659912 bytes

Database Buffers 276824064 bytes

Redo Buffers 5840896 bytes

SQL> @/home/app/oracle/oradata/TEST/create_control_file.sql

ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.