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 !

    Copyright 2020. All Rights Reserved