Noarchivelog database recovery on oracle 11g

Oracle Version:- Oracle 11g


Scenario: Database is in nonarchive log mode. And all the datafiles , redologfiles and controlfiles have been lost.Rman backup is present.
And only controlfile is present from multiplexed location.

Solution:-

SQL> select log_mode from V$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> select name from V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/somtbs.dbf

Now let's see what happens when I move all datafiles, redo log and control files to another directory
simulating a disks failure. I assume for this scenario to be able to get back with a consistent copy of at least one multiplexed control files.

[oracle@server1 prim]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ ls -ltrh
total 1.6G
drwxr-xr-x  2 oracle oinstall 4.0K Oct 20 22:15 temp
-rw-r-----  1 oracle oinstall  51M Oct 20 22:23 redo02.log
-rw-r-----  1 oracle oinstall  51M Oct 20 22:23 redo03.log
-rw-r-----  1 oracle oinstall 5.1M Oct 20 22:23 users01.dbf
-rw-r-----  1 oracle oinstall 101M Oct 20 22:23 example01.dbf
-rw-r-----  1 oracle oinstall 101M Oct 20 22:23 somtbs.dbf
-rw-r-----  1 oracle oinstall  21M Oct 20 22:23 temp01.dbf
-rw-r-----  1 oracle oinstall 681M Oct 20 22:29 system01.dbf
-rw-r-----  1 oracle oinstall  56M Oct 20 22:29 undotbs01.dbf
-rw-r-----  1 oracle oinstall 511M Oct 20 22:29 sysaux01.dbf
-rw-r-----  1 oracle oinstall  51M Oct 20 22:30 redo01.log
-rw-r-----. 1 oracle oinstall 9.3M Oct 20 22:30 control01.ctl

[oracle@server1 prim]$ mkdir temp
[oracle@server1 prim]$ mv *.* temp

Now lets try to shutdown the running database:-
SQL> shut immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/prim/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Well it doesn't work. Of course the instance is not able to find and write any SCN on the
current control file. So issue the following command

SQL> shutdown abort; 
ORACLE instance shut down. 
Let's see what happens when an instance is not able to find a control file. The instance is not
able to be open in MOUNT mode

[oracle@localhost old]$ sqlplus / as sysdba 
SQL> startup mount; 
ORACLE instance started. 
 
Total System Global Area  456146944 bytes 
Fixed Size                  1344840 bytes 
Variable Size             352324280 bytes 
Database Buffers           96468992 bytes 
Redo Buffers                6008832 bytes 
ORA-00205: error in identifying control file, check alert log for more info 
 
SQL> shutdown immediate; 
ORA-01507: database not mounted 
 
ORACLE instance shut down. 
SQL> exit 

Since you have lost all your database files except one control file that was wisely multiplexed
on another disk (in my case it's in the flash recovery area path). We can use it to mount the
database, copying it to the original location.

[oracle@server1 prim]$ cd /u01/app/oracle/flash_recovery_area/prim
[oracle@server1 prim]$ ls -ltrh
-rw-r----- 1 oracle oinstall 9.3M Oct 20 22:40 control02.ctl
[oracle@server1 prim]$ cp control02.ctl /u01/app/oracle/oradata/prim/
[oracle@server1 prim]$ mv control02.ctl control01.ctl

Now the instance is able to open in mount mode using the CURRENT control file .
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             314574840 bytes
Database Buffers           92274688 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> select controlfile_type from v$database; 
 
CONTROL 
------- 
CURRENT 

[oracle@server1 prim]$ rman target /
RMAN> restore database;

Also check in OS level that after restore the database all datafiles are back.

Now it's time to open the database using the resetlogs option, but...

SQL> alter database open resetlogs; 
alter database open resetlogs 
* 
ERROR at line 1: 
ORA-01139: RESETLOGS option only valid after an incomplete database recovery 

The following error happens because on the restored datafiles is written an SCN that was valid
at the time the backup was taken and equal to the control file of that moment.
The control file we are using is ahead compared with the SCN written on the datafiles...
You have to perform ("simulate") an incomplete recovery, typing CANCEL when requested

SQL> recover database until cancel;
ORA-00279: change 1214685 generated at 10/20/2014 22:05:35 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2014_10_20/o1_mf_1_34_%u_.arc
ORA-00280: change 1214685 for thread 1 is in sequence #34


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2014_10_20/o1_mf_1_34_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2014_10_20/o1_mf_1_34_%u_.a
rc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
[Here type Cancel]
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select name from V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf

Now the database has been restored to the time when it was backed up.