Recover database after missing online redo logs and all controlfiles.

This example is based on,
You have lost all your current and backup of controlfiles.
You have avaiable your current data files.
You have lost all your online active , current redo log files.
You have lost your spfile and pfile.

In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost.

1.
Let's start by deleting online redo log files and controlfile of my running database.

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/redo02.log
/oradata2/arjudba/arjudba/redo01.log
/oradata2/arjudba/arjudba/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl

2.Delete all copies of controlfiles and online redo log files. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*

SQL> !rm /oradata2/arjudba/arjudba/redo0*

3.Now it is task to recover my database. This procedure will begin by creating a new controlfile. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below.
Note that as you have lost your online redo log files you have to specify RESETLOGS option.

-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> shutdown abort
ORACLE instance shut down.

6. The recovery state I performed is to create an pfile.
-bash-3.00$ vi /oradata2/1.pfile
compatible=10.2.0.1.0
db_name=arjudba
_allow_resetlogs_corruption = true
sga_target=400M
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'

7.The next step is to create a controlfile.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT pfile='/oradata2/1.pfile'
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

8.Create controlfile and mount the database.
SQL> @/oradata2/ctl.ctl
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes

Control file created.
--Recover database if necessary.This might necessary if you restore backup from a previous. Then recover database using backup controlfile until cancel and then --cancel

9.Normal Shutdown the database.
SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

10.Start the database with the pfile.
SQL> startup pfile='/oradata2/1.pfile';
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

11. Open the database with the resetlogs option.
SQL> alter database open resetlogs;
Database altered.

Note that once the database is opened, then we must immediately rebuild the database. Database rebuild means

1) perform a full-database export,
2) create a brand new and separate database, and finally
3) import the export dump.
Before you try this option, ensure that you have a good and valid backup of the current database.

This is necessary to rebuild because after _allow_resetlogs_corruption the dictionary information may be corrupted.

Related Documents
Recover database after only lose of all controlfiles