How to Restore and Recover an Oracle database with RMAN

Let us see how we can restore Oracle database from a RMAN Full Backup set after we remove/destroy it's data files. We see here that we have our database up and running.

[oracle@primary pri]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 14 12:35:40 2014

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


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

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      pri
Let's go and destroy our database :).
SQL> SELECT 'rm -f ' ||file_name FROM dba_data_files;

'RM-F'||FILE_NAME
--------------------------------------------------------------------------------
rm -f /u02/app/oracle/pri/users01.dbf
rm -f /u02/app/oracle/pri/undotbs01.dbf
rm -f /u02/app/oracle/pri/sysaux01.dbf
rm -f /u02/app/oracle/pri/system01.dbf
Run the remove datafile
rm -f /u02/app/oracle/pri/users01.dbf
rm -f /u02/app/oracle/pri/undotbs01.dbf
rm -f /u02/app/oracle/pri/sysaux01.dbf
rm -f /u02/app/oracle/pri/system01.dbf
Try to open your database now
SQL> startup force;
ORACLE instance started.

Total System Global Area  221294592 bytes
Fixed Size                  2212048 bytes
Variable Size             125833008 bytes
Database Buffers           88080384 bytes
Redo Buffers                5169152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u02/app/oracle/pri/system01.dbf'
upsss.. this is bad :) ! Time ot get to work, any show why DBA is so important. Next ask your netbackup admin to restore a copy of you most recent backup.
  • -he will restore into /tmp/backup folder
  • [oracle@primary backup]$ ll
    total 980348
    -rw-r----- 1 oracle oinstall  28461568 Sep 14 10:05 o1_mf_annnn_TAG20140914T100550_b1c4oh90_.bkp
    -rw-r----- 1 oracle oinstall     25088 Sep 14 10:06 o1_mf_annnn_TAG20140914T100654_b1c4qgq4_.bkp
    -rw-r----- 1 oracle oinstall   9830400 Sep 14 10:06 o1_mf_ncsnf_TAG20140914T100554_b1c4qf8d_.bkp
    -rw-r----- 1 oracle oinstall 965550080 Sep 14 10:06 o1_mf_nnndf_TAG20140914T100554_b1c4olv7_.bkp
    Start up your database in mount mode
    SQL> startup force mount;
    ORACLE instance started.
    
    Total System Global Area  221294592 bytes
    Fixed Size                  2212048 bytes
    Variable Size             125833008 bytes
    Database Buffers           88080384 bytes
    Redo Buffers                5169152 bytes
    Database mounted.
    SQL>
    Log in to RMAN tool and catalog the backup set that your backup admin gave you.
    [oracle@primary backup]$ rman target /
    
    Recovery Manager: Release 11.2.0.1.0 - Production on Sun Sep 14 12:46:57 2014
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: PRI (DBID=788900126, not open)
    
    RMAN> catalog backuppiece '/tmp/backup/o1_mf_annnn_TAG20140914T100550_b1c4oh90_.bkp';
    
    using target database control file instead of recovery catalog
    cataloged backup piece
    backup piece handle=/tmp/backup/o1_mf_annnn_TAG20140914T100550_b1c4oh90_.bkp RECID=13 STAMP=858257226
    
    RMAN> catalog backuppiece '/tmp/backup/o1_mf_annnn_TAG20140914T100654_b1c4qgq4_.bkp';
    
    cataloged backup piece
    backup piece handle=/tmp/backup/o1_mf_annnn_TAG20140914T100654_b1c4qgq4_.bkp RECID=14 STAMP=858257232
    
    RMAN> catalog backuppiece '/tmp/backup/o1_mf_ncsnf_TAG20140914T100554_b1c4qf8d_.bkp';
    
    cataloged backup piece
    backup piece handle=/tmp/backup/o1_mf_ncsnf_TAG20140914T100554_b1c4qf8d_.bkp RECID=15 STAMP=858257236
    
    RMAN> catalog backuppiece '/tmp/backup/o1_mf_nnndf_TAG20140914T100554_b1c4olv7_.bkp';
    
    cataloged backup piece
    backup piece handle=/tmp/backup/o1_mf_nnndf_TAG20140914T100554_b1c4olv7_.bkp RECID=16 STAMP=858257240
    Check the content of the backup set.
    RMAN> list backup;
    
    
    List of Backup Sets
    ===================
    
    
    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    13      27.14M     DISK        00:00:00     14-SEP-14
            BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20140914T100550
            Piece Name: /tmp/backup/o1_mf_annnn_TAG20140914T100550_b1c4oh90_.bkp
    
      List of Archived Logs in backup set 13
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    2       948064     14-SEP-14 956801     14-SEP-14
    
    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    14      24.00K     DISK        00:00:00     14-SEP-14
            BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20140914T100654
            Piece Name: /tmp/backup/o1_mf_annnn_TAG20140914T100654_b1c4qgq4_.bkp
    
      List of Archived Logs in backup set 14
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    3       956801     14-SEP-14 956836     14-SEP-14
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    15      Full    9.36M      DISK        00:00:00     14-SEP-14
            BP Key: 15   Status: AVAILABLE  Compressed: NO  Tag: TAG20140914T100554
            Piece Name: /tmp/backup/o1_mf_ncsnf_TAG20140914T100554_b1c4qf8d_.bkp
      SPFILE Included: Modification time: 14-SEP-14
      SPFILE db_unique_name: PRI
      Control File Included: Ckp SCN: 956830       Ckp time: 14-SEP-14
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    16      Full    920.81M    DISK        00:00:00     14-SEP-14
            BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: TAG20140914T100554
            Piece Name: /tmp/backup/o1_mf_nnndf_TAG20140914T100554_b1c4olv7_.bkp
      List of Datafiles in backup set 16
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      1       Full 956811     14-SEP-14 /u02/app/oracle/pri/system01.dbf
      2       Full 956811     14-SEP-14 /u02/app/oracle/pri/sysaux01.dbf
      3       Full 956811     14-SEP-14 /u02/app/oracle/pri/undotbs01.dbf
      4       Full 956811     14-SEP-14 /u02/app/oracle/pri/users01.dbf
    Time to restore the database.
    RMAN> restore database;
    
    Starting restore at 14-SEP-14
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=20 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 /u02/app/oracle/pri/system01.dbf
    channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/pri/sysaux01.dbf
    channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/pri/undotbs01.dbf
    channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/pri/users01.dbf
    channel ORA_DISK_1: reading from backup piece /tmp/backup/o1_mf_nnndf_TAG20140914T100554_b1c4olv7_.bkp
    channel ORA_DISK_1: piece handle=/tmp/backup/o1_mf_nnndf_TAG20140914T100554_b1c4olv7_.bkp tag=TAG20140914T100554
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
    Finished restore at 14-SEP-14
    Recover and Open your database.
    RMAN> recover database ;
    
    Starting recover at 14-SEP-14
    using channel ORA_DISK_1
    
    starting media recovery
    
    archived log for thread 1 with sequence 3 is already on disk as file /u02/app/oracle/flash_recovery_area/PRI/archivelog/2014_09_14/o1_mf_1_3_b1c4qgho_.arc
    archived log for thread 1 with sequence 4 is already on disk as file /u02/app/oracle/flash_recovery_area/PRI/archivelog/2014_09_14/o1_mf_1_4_b1c6vlv6_.arc
    archived log for thread 1 with sequence 5 is already on disk as file /u02/app/oracle/flash_recovery_area/PRI/archivelog/2014_09_14/o1_mf_1_5_b1cf1wf4_.arc
    archived log for thread 1 with sequence 6 is already on disk as file /u02/app/oracle/flash_recovery_area/PRI/archivelog/2014_09_14/o1_mf_1_6_b1cfc51b_.arc
    archived log file name=/u02/app/oracle/flash_recovery_area/PRI/archivelog/2014_09_14/o1_mf_1_3_b1c4qgho_.arc thread=1 sequence=3
    archived log file name=/u02/app/oracle/flash_recovery_area/PRI/archivelog/2014_09_14/o1_mf_1_4_b1c6vlv6_.arc thread=1 sequence=4
    media recovery complete, elapsed time: 00:00:04
    Finished recover at 14-SEP-14
    
    
    RMAN> alter database open;
    
    database opened
    Done !