Recovering database after loss of current controlfile

Recovering database after loss of current controlfile

In this scenario we will be recovering the database after loss of current control file. We assume that database is in archive log mode and all the datafile, online redo log files and archive log files are intact.
Same steps holds good even if the database is in no archive log mode.
1) Database is in archive log mode here. But archivelog mode does not matter. These steps holds good even if the database is not in archive log mode.

bash-2.05$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 12 05:51:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dy/oracle/product/db10g/archive/htmldb
Oldest online log sequence 71
Next log sequence to archive 73
Current log sequence 73
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
2) Remove the control files from file system.
bash-2.05$ ls
htmldb redo02.log system01.dbf test_control2
htmldb01.dbf redo03.log temp01.dbf undotbs01.dbf
redo01.log sysaux01.dbf test_control1
bash-2.05$ rm test_control*
After doing this database will crash after some time, when ever any background process needs to update the control file, it wont find the same and DB will crash.
3) Create a “create control file script”. This can be obtained using “Alter database backup controlfile to trace” command if used some time back. Its a good practice to backup the control file to trace regularly when ever the structure if changed (Example when ever a datafile is added).
Create control file command is stored in crdb.sql
bash-2.05$ cat crdb.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “HTMLDB” NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/dy/oracle/product/db10g/dbf/redo01.log’ SIZE 100M,
GROUP 2 ‘/dy/oracle/product/db10g/dbf/redo02.log’ SIZE 100M,
GROUP 3 ‘/dy/oracle/product/db10g/dbf/redo03.log’ SIZE 100M
— STANDBY LOGFILE
DATAFILE
‘/dy/oracle/product/db10g/dbf/system01.dbf’,
‘/dy/oracle/product/db10g/dbf/undotbs01.dbf’,
‘/dy/oracle/product/db10g/dbf/sysaux01.dbf’,
‘/dy/oracle/product/db10g/dbf/htmldb01.dbf’
CHARACTER SET US7ASCII
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMPTS1 ADD TEMPFILE ‘/dy/oracle/product/db10g/dbf/temp01.dbf’
SIZE 20971520 REUSE AUTOEXTEND OFF;
4) Run the “Create control file” script crdb.sql.
bash-2.05$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 12 05:55:43 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @crdb.sql
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 243276104 bytes
Database Buffers 822083584 bytes
Redo Buffers 6397952 bytes
Control file created.
Media recovery complete.
System altered.
Database altered.
Tablespace altered.
SQL>
We are done. Database is now up and running. Note that this is a complete recovery. There is no loss of data, because all the data files and redo log files were intact.