Loss of all datafiles when the database is in archive log mode?

In live Environments ,we will schedule full/Incremental/Cumulative backup's and it depends on the orginization or business needs.

Let's see how to resolve this issue when we lost all the datafiles and we have full backup of the database.



SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/d02/oracle/oradata/orcl/users01.dbf
/d02/oracle/oradata/orcl/undotbs01.dbf
/d02/oracle/oradata/orcl/sysaux01.dbf
/d02/oracle/oradata/orcl/system01.dbf
/d02/oracle/oradata/orcl/users02.dbf
/d02/oracle/oradata/orcl/users06

SQL> select open_mode from v$database;

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

SQL>


[oracle@wasiq orcl]$ pwd
/d02/oracle/oradata/orcl
[oracle@wasiq orcl]$ ls -tlrh
total 1.6G
-rw-r----- 1 oracle dba  21M Jan  1 09:28 temp01.dbf
-rw-r----- 1 oracle dba  51M Jan  1 09:41 redo03.log
-rw-r----- 1 oracle dba  51M Jan  1 09:41 redo02.log
-rw-r----- 1 oracle dba 101M Jan  1 09:41 users06
-rw-r----- 1 oracle dba 101M Jan  1 10:42 users02.dbf
-rw-r----- 1 oracle dba 5.1M Jan  1 10:42 users01.dbf
-rw-r----- 1 oracle dba 671M Jan  1 12:11 system01.dbf
-rw-r----- 1 oracle dba 481M Jan  1 12:13 sysaux01.dbf
-rw-r----- 1 oracle dba  51M Jan  1 12:15 undotbs01.dbf
-rw-r----- 1 oracle dba  51M Jan  1 12:15 redo01.log
-rw-r----- 1 oracle dba 9.3M Jan  1 12:16 control01.ctl


++++++++++++++++++++++++++++++++++++++++++
Due to human error all the datafiles are lost
+++++++++++++++++++++++++++++++++++++++++
/d02/oracle/oradata/orcl
[oracle@wasiq orcl]$ ls -tlrh
total 1.6G
-rw-r----- 1 oracle dba  21M Jan  1 09:28 temp01.dbf
-rw-r----- 1 oracle dba  51M Jan  1 09:41 redo03.log
-rw-r----- 1 oracle dba  51M Jan  1 09:41 redo02.log
-rw-r----- 1 oracle dba 101M Jan  1 09:41 users06
-rw-r----- 1 oracle dba 101M Jan  1 10:42 users02.dbf
-rw-r----- 1 oracle dba 5.1M Jan  1 10:42 users01.dbf
-rw-r----- 1 oracle dba 671M Jan  1 12:16 system01.dbf
-rw-r----- 1 oracle dba  51M Jan  1 12:17 undotbs01.dbf
-rw-r----- 1 oracle dba 481M Jan  1 12:17 sysaux01.dbf
-rw-r----- 1 oracle dba  51M Jan  1 12:17 redo01.log
-rw-r----- 1 oracle dba 9.3M Jan  1 12:17 control01.ctl
[oracle@wasiq orcl]$ rm *.dbf
[oracle@wasiq orcl]$ ls -tlrh
total 260M
-rw-r----- 1 oracle dba  51M Jan  1 09:41 redo03.log
-rw-r----- 1 oracle dba  51M Jan  1 09:41 redo02.log
-rw-r----- 1 oracle dba 101M Jan  1 09:41 users06
-rw-r----- 1 oracle dba  51M Jan  1 12:17 redo01.log
-rw-r----- 1 oracle dba 9.3M Jan  1 12:17 control01.ctl
[oracle@wasiq orcl]$


++++++++++++++++++++++++++++++++++
shutdown the database with abort option
++++++++++++++++++++++++++++++++++
SQL> shutdown abort;
ORACLE instance shut down.
SQL>


++++++++++++++++++++++++++
mount the database
++++++++++++++++++++++
SQL> startup mount ;
ORACLE instance started.

Total System Global Area 1372651520 bytes
Fixed Size                  1336428 bytes
Variable Size             385878932 bytes
Database Buffers          973078528 bytes
Redo Buffers               12357632 bytes
Database mounted.

[oracle@wasiq orcl]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jan 1 12:21:24 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /;

connected to target database: ORCL (DBID=1362912542, not open)

RMAN> restore database;

Starting restore at 01-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=129 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=192 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=6 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /d02/oracle/oradata/orcl/users02.dbf
channel ORA_DISK_1: restoring datafile 00006 to /d02/oracle/oradata/orcl/users06
channel ORA_DISK_1: reading from backup piece /backup/rman/orcl/28dec2013/ORCL_db_u0tosm0kr_s29_p1_t835388059_db
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00002 to /d02/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_2: restoring datafile 00003 to /d02/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /backup/rman/orcl/28dec2013/ORCL_db_u0sosm0kr_s28_p1_t835388059_db
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00004 to /d02/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_3: reading from backup piece /backup/rman/orcl/28dec2013/ORCL_db_u0rosm0kr_s27_p1_t835388059_db
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00001 to /d02/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_4: reading from backup piece /d02/oracle/flash_recovery_area/ORCL/backupset/2014_01_01/o1_mf_nnndf_TAG20140101T121123_9d7g4n8d_.bkp
channel ORA_DISK_3: piece handle=/backup/rman/orcl/28dec2013/ORCL_db_u0rosm0kr_s27_p1_t835388059_db tag=TAG20131228T201419
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: piece handle=/backup/rman/orcl/28dec2013/ORCL_db_u0tosm0kr_s29_p1_t835388059_db tag=TAG20131228T201419
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_2: piece handle=/backup/rman/orcl/28dec2013/ORCL_db_u0sosm0kr_s28_p1_t835388059_db tag=TAG20131228T201419
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:36
channel ORA_DISK_4: piece handle=/d02/oracle/flash_recovery_area/ORCL/backupset/2014_01_01/o1_mf_nnndf_TAG20140101T121123_9d7g4n8d_.bkp tag=TAG20140101T121123
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:36
Finished restore at 01-JAN-14
RMAN> recover database;
Starting recover at 01-JAN-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-JAN-14

RMAN> sql 'alter database open';
sql statement: alter database open
RMAN> exit

Recovery Manager complete.
[oracle@wasiq orcl]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 1 12:23:29 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

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

SQL>