RMAN FULL database recovery scenerio

Full database recovery :

We will create a scenerio where we will drop all the datafiles and do a full database recovery.

• Database name : ORCL
• Assuming that you have already taken the full database backup (if not please take the full backup before your perform this test/task).
• Database is running in archive-log mode, if not to have a consistent backup you need to shutdown the database open it in Mount mode and take a full backup.

-----------      If you haven't taken backup do so (please see below)   ------

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 7 20:20:55 2014

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

connected to target database: ORCL (DBID=1378116623)

RMAN>  backup database tag"full_backup";

Starting backup at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/datafiles/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/datafiles/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/datafiles/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/datafiles/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/datafiles/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-JUL-14
channel ORA_DISK_1: finished piece 1 at 07-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:51
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-JUL-14
channel ORA_DISK_1: finished piece 1 at 07-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_ncsnf_FULL_BACKUP_9vpkxymd_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUL-14

----  Oracle DB restore process -------

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 20:18:05 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2214456 bytes
Variable Size             369100232 bytes
Database Buffers           92274688 bytes
Redo Buffers                8241152 bytes

lets check name of database:

SQL> select name from v$database;

NAME
---------
ORCL

Check if the database is in archivelog mode;

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oraarch/
Oldest online log sequence     62
Next log sequence to archive   64
Current log sequence           64


list out all the data-files:

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  undotbs01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf        users01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  temp01.dbf

Now lets drop all the dbf files:

[oracle@linux1 orcl]$ rm *.dbf

lets verify that all files have been dropped :

[oracle@linux1 orcl]$ ls -al
total 368564
drwxr-x--- 2 oracle oinstall     4096 Jul  7 20:29 .
drwxr-xr-x 3 oracle oinstall     4096 Jun 15 20:22 ..
-rw-r----- 1 oracle oinstall  9912320 Jul  7 20:29 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:25 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:29 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:20 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo04.log

Most times you will see that server is still open, even tough you see that database has crashed and you can't access datafile.
we will now do a full database recovery. since all the datafiles have been lost.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2214456 bytes
Variable Size             369100232 bytes
Database Buffers           92274688 bytes
Redo Buffers                8241152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'



RMAN> run {
2> restore database;
3> recover database;
4> }

Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 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 00001 to /u01/app/oracle/datafiles/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/datafiles/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/datafiles/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/datafiles/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/datafiles/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 07-JUL-14

Starting recover at 07-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 07-JUL-14

Since the full database recovery has been completed successfully. we need to open the database now

SQL> alter database open;

Database altered.

verify that you can access the database without any errors.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14


14 rows selected.