RMAN-Recover database after only lose of all controlfiles

Recover database after only lose of all controlfiles

This example is based on,
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.

In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and you don't have any backup of your controlfile.

Let's start by deleting controlfile of my running database.

1.In order to know the controlfiles of my database issue,
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 controlfile. 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*

3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info

As it can't read controlfile so ORA-00205 arises.

4.From this state is your situation what you need to do. 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 you have to remember the name of datafile and online redo log file.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" NORESETLOGS 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> @/oradata2/ctl.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.

7.Open the database after recovery completes.
SQL> alter database open;
Database altered.

8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/arjudba/arjudba/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;