Recover database without control files and redolog files.

Scenario:- We are deleting controlfiles from database and would recover database after dropping them.

Database version :- Oracle 11g R2
OS: Rhel 6
Database sid: orcl
Archivelog mode: enabled

Step1:-
$cd /u01/app/oracle/oradata/orcl
$rm -rf *.ctl

$sqlplus / as sysdba
SQL>startup
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             402655224 bytes
Database Buffers            4194304 bytes
Redo Buffers                4308992 bytes
ORA-00205: error in identifying control file, check alert log for more info

Step2:-Start the database in nomount stage
startup nomount
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1248140 bytes
Variable Size              75498612 bytes
Database Buffers          130023424 bytes
Redo Buffers                2945024 bytes

Step3:-Recreate the control file:-

CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/orcl/REDO01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/orcl/REDO02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/orcl/REDO03.log’  SIZE 50M
DATAFILE
‘/u01/app/oracle/oradata/orcl/system01.dbf’,
‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’,
‘/u01/app/oracle/oradata/orcl/users01.dbf’,
‘/u01/app/oracle/oradata/orcl/example01.dbf’,
‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’
CHARACTER SET WE8MSWIN1252;

Now after creating the control file, the database has been mounted.

Step 4.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/prim/system01.dbf’

So in this case we have to do the recovery  using the online redolog files.
SQL>  select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
1          1          0   52428800        512          1 YES UNUSED                       0                      0
3          1          0   52428800        512          1 YES CURRENT                      0                      0
2          1          0   52428800        512          1 YES UNUSED                       0                      0

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1030644 generated at 01/08/2015 22:25:57 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_1_%u_.arc
ORA-00280: change 1030644 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_1_%u_.arc
ORA-00308: cannot open archived log
‘/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_1_%u_.ar
c’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE