RMAN Restore Database in Oracle

Mehmet Salih Deveci

3 years ago

I will explain RMAN Restore Database in Oracle in this post.

RMAN Restore Database

Especially Big companies like banking and insurance sector, historical backups are stored for up to 10 years. In fact, sometimes historical backups may be required. As a result of a lawsuit, you may need to restore a backup near that time to know what the data of the years ago was

Here, in this article, I will restore a backup belongs to 2014 which was taken to EMC Data domain. We will perform point in time Restore and Recovery RMAN backup from Tape.

RMAN Restore database steps are as follows.

Oracle database home should be installed
Set Bash Profile and Parameter files
Startup nomount mode and Restore controlfile
Restore database
Restore Archivelog and Recover database
Open database with resetlogs mode.

Step-1: Oracle database home should be installed

If you restore this backup on new server, you should install Oracle database software firstly. You can use following article to install Oracle database software.

Oracle Database 19c on Linux 7.6 and RHEL Step by Step Installation -1

Once Oracle database software is installed, perform following steps.

Step-2: Set Bash Profile and Parameter files

Set Oracle Home

Set Oracle Home for new database .

export ORACLE_BASE=/oracle/app

export ORACLE_HOME=/oracle/app/product/11.2.0.3/db

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9

export ORACLE_SID=ORCL

export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$PATH

Parameter File settings

Create pfile under $ORACLE_HOME/dbs directory to start up Oracle Software in nomount mode.

oracle:deveci01:/oracle/app/product/11.2.0.3/db/dbs:>cat initORCL.ora 

db_name='ORCL'

db_unique_name='ORCLYENI'

memory_target=12G

processes = 1500

audit_file_dest='/oracle/app/admin/ORCLYENI'

audit_trail ='db'

db_block_size=8192

db_domain=''

diagnostic_dest='/oracle/app'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=500

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files='+DATA/ORCLYENI/CONTROLFILE/current.256.849957761'

compatible='11.2.0.0.0'

Step-3: Startup nomount mode and Restore controlfile

Open Oracle Instance with nomount mode like following.

SQL> startup nomount pfile=’ initORCL.ora’;

ORACLE instance started.
Total System Global Area 1.7364E+10 bytes
Fixed Size                  1359424 bytes
Variable Size            1254834624 bytes
Database Buffers         1.6106E+10 bytes
Redo Buffers                2097152 bytes

Connect RMAN

Connect RMAN Catalog database and query for proper Controlfile backup like following.

SELECT session_recid,TO_CHAR (start_time, 'DD-MM-YYYY  HH24:MI:SS') start_time,         input_type,         status,         ROUND (elapsed_seconds / 3600, 1) time_hr,INPUT_BYTES/1024/1024/1024 IN_GB,OUTPUT_BYTES/1024/1024/1024  OUT_GB ,OUTPUT_DEVICE_TYPE FROM

v$rman_backup_job_details   WHERE START_TIME > SYSDATE - 5 ORDER BY start_time desc;

 

select * from v$rman_output where session_recid=729;   

 

 

oracle:deveci01:/home/users/oracle:>rman

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jun 11 16:42:06 2014

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

RMAN> connect catalog rman/rman123@CAT

set DBID=2402765690

connect target /connected to recovery catalog database

RMAN> 

executing command: SET DBID

database name is "ORCL" and DBID is 2402765690

RMAN> 

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

 

RMAN> list backup of controlfile completed between "to_date('11.06.2014','dd.mm.yyyy hh24')" and "to_date('12.06.2014','dd.mm.yyyy hh24')";

 

List of Backup Sets

===================

          .

          .

         .

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

1576770235 Full    24.25M     SBT_TAPE    00:00:04     11-JUN-14      

        BP Key: 1576770276   Status: AVAILABLE  Compressed: NO  Tag: TAG20140611T110055

        Handle: c-2402765690-20140611-04   Media: V10DBBackup.002

  Control File Included: Ckp SCN: 68825716813   Ckp time: 11-JUN-14

Restore Controlfile from Tape

If you find related Controlfile backup information, restore this controlfile from Tape like following.

Use EMC DataDomain Tape information like NSR Server and NSR Client like following.

run

{

ALLOCATE CHANNEL CH1 DEVICE TYPE 'SBT_TAPE';

send 'NSR_ENV=(NSR_CLIENT=deveci01.localdomain,NSR_SERVER=TAPE_SERVER.localdomain) ';

restore controlfile from 'c-2402765690-20140611-04';

RELEASE CHANNEL CH1;

}

Once Controlfile is restored from DataDomain Tape, you can start restoring database.

If Directories and Mount points are different on new server, you can use ” set newname for datafile ” in restore database script.

select file_name,file_id from dba_data_files;
set head off pages 0 feed off echo off verify off
set lines 200
select 'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' || '+DATA/ORCLYENI/DATAFILE/' || substr(name,instr(name,'/',-1)+1) || ''';' from v$datafile;

Datafile convert example is like following.

set newname for datafile 102 to '+DATA/ORCLYENI/DATAFILE/harita04.dba';

set newname for datafile 99 to '+DATA/ORCLYENI/DATAFILE/users14.dbf';

set newname for datafile 98 to '+DATA/ORCLYENI/DATAFILE/users13.dbf';

set newname for datafile 97 to '+DATA/ORCLYENI/DATAFILE/users12.dbf';

Step-4: Restore database

Restore Backup from Tape

You can use the Restore database command to restore database.

Mount points are different in my case, so i have used ” set newname for datafile “ for modifying datafiles location and my Restore database script and Tape informations has been like following.

oracle:deveci01:/home/users/oracle:>cat RestoreORCL.rman

connect catalog rman/rman123@CAT

set DBID=2402765690

connect target /

run

{

ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH5 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH6 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH7 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH8 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH9 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH10 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH11 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH12 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH13 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH14 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH15 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH16 TYPE 'SBT_TAPE';

send 'NSR_ENV=(NSR_CLIENT=deveci01.localdomain,NSR_SERVER=TAPE_SERVER.localdomain) ';

set newname for datafile 102 to '+DATA/ORCLYENI/DATAFILE/harita04.dba';

set newname for datafile 99 to '+DATA/ORCLYENI/DATAFILE/users14.dbf';

set newname for datafile 98 to '+DATA/ORCLYENI/DATAFILE/users13.dbf';

set newname for datafile 97 to '+DATA/ORCLYENI/DATAFILE/users12.dbf';

set newname for datafile 96 to '+DATA/ORCLYENI/DATAFILE/tsevam01.dba';

set newname for datafile 93 to '+DATA/ORCLYENI/DATAFILE/users11.dbf';

set newname for datafile 92 to '+DATA/ORCLYENI/DATAFILE/users10.dbf';

set newname for datafile 91 to '+DATA/ORCLYENI/DATAFILE/users09.dbf';

set newname for datafile 90 to '+DATA/ORCLYENI/DATAFILE/users08.dbf';

set newname for datafile 87 to '+DATA/ORCLYENI/DATAFILE/users07.dbf';

set newname for datafile 86 to '+DATA/ORCLYENI/DATAFILE/users06.dbf';

set newname for datafile 85 to '+DATA/ORCLYENI/DATAFILE/users05.dbf';

set newname for datafile 84 to '+DATA/ORCLYENI/DATAFILE/users04.dbf';

set newname for datafile 83 to '+DATA/ORCLYENI/DATAFILE/harita03.dba';

set newname for datafile 82 to '+DATA/ORCLYENI/DATAFILE/iapro_harita18.dba';

set newname for datafile 81 to '+DATA/ORCLYENI/DATAFILE/iapro_harita19.dba';

set newname for datafile 80 to '+DATA/ORCLYENI/DATAFILE/iapro_harita20.dba';

set newname for datafile 79 to '+DATA/ORCLYENI/DATAFILE/system02.dbf';

set newname for datafile 78 to '+DATA/ORCLYENI/DATAFILE/deveci_41.dba';

set newname for datafile 77 to '+DATA/ORCLYENI/DATAFILE/deveci_40.dba';

set newname for datafile 76 to '+DATA/ORCLYENI/DATAFILE/deveci_39.dba';

set newname for datafile 75 to '+DATA/ORCLYENI/DATAFILE/undotbs03.dbf';

set newname for datafile 74 to '+DATA/ORCLYENI/DATAFILE/undotbs02.dbf';

set newname for datafile 73 to '+DATA/ORCLYENI/DATAFILE/users03.dbf';

set newname for datafile 72 to '+DATA/ORCLYENI/DATAFILE/users02.dbf';

set newname for datafile 71 to '+DATA/ORCLYENI/DATAFILE/iapro_harita17.dba';

set newname for datafile 70 to '+DATA/ORCLYENI/DATAFILE/deveci_37.dba';

set newname for datafile 69 to '+DATA/ORCLYENI/DATAFILE/deveci_38.dba';

set newname for datafile 68 to '+DATA/ORCLYENI/DATAFILE/iapro_harita15.dba';

set newname for datafile 67 to '+DATA/ORCLYENI/DATAFILE/iapro_harita16.dba';

set newname for datafile 66 to '+DATA/ORCLYENI/DATAFILE/iapro_deveci03.dba';

set newname for datafile 65 to '+DATA/ORCLYENI/DATAFILE/iapro_deveci04.dba';

set newname for datafile 64 to '+DATA/ORCLYENI/DATAFILE/iapro_harita13.dba';

set newname for datafile 63 to '+DATA/ORCLYENI/DATAFILE/iapro_harita14.dba';

set newname for datafile 62 to '+DATA/ORCLYENI/DATAFILE/iapro_harita11.dba';

set newname for datafile 61 to '+DATA/ORCLYENI/DATAFILE/iapro_harita12.dba';

set newname for datafile 60 to '+DATA/ORCLYENI/DATAFILE/iapro_harita10.dba';

set newname for datafile 59 to '+DATA/ORCLYENI/DATAFILE/iapro_harita08.dba';

set newname for datafile 58 to '+DATA/ORCLYENI/DATAFILE/iapro_harita09.dba';

set newname for datafile 57 to '+DATA/ORCLYENI/DATAFILE/iapro_harita07.dba';

set newname for datafile 56 to '+DATA/ORCLYENI/DATAFILE/iapro_harita06.dba';

set newname for datafile 55 to '+DATA/ORCLYENI/DATAFILE/iapro_harita05.dba';

set newname for datafile 54 to '+DATA/ORCLYENI/DATAFILE/iapro_harita04.dba';

set newname for datafile 53 to '+DATA/ORCLYENI/DATAFILE/iapro_harita03.dba';

set newname for datafile 52 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_05.dbf';

set newname for datafile 51 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_04.dbf';

set newname for datafile 50 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_03.dbf';

set newname for datafile 49 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_02.dbf';

set newname for datafile 48 to '+DATA/ORCLYENI/DATAFILE/deveci_36.dba';

set newname for datafile 47 to '+DATA/ORCLYENI/DATAFILE/deveci_35.dba';

set newname for datafile 46 to '+DATA/ORCLYENI/DATAFILE/deveci_34.dba';

set newname for datafile 45 to '+DATA/ORCLYENI/DATAFILE/deveci_33.dba';

set newname for datafile 44 to '+DATA/ORCLYENI/DATAFILE/deveci_32.dba';

set newname for datafile 43 to '+DATA/ORCLYENI/DATAFILE/deveci_31.dba';

set newname for datafile 42 to '+DATA/ORCLYENI/DATAFILE/deveci_30.dba';

set newname for datafile 41 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_01.dbf';

set newname for datafile 40 to '+DATA/ORCLYENI/DATAFILE/iapro_harita02.dba';

set newname for datafile 39 to '+DATA/ORCLYENI/DATAFILE/iapro_harita01.dba';

set newname for datafile 38 to '+DATA/ORCLYENI/DATAFILE/iapro_deveci02.dba';

set newname for datafile 37 to '+DATA/ORCLYENI/DATAFILE/iapro_deveci01.dba';

set newname for datafile 36 to '+DATA/ORCLYENI/DATAFILE/harita02.dba';

set newname for datafile 35 to '+DATA/ORCLYENI/DATAFILE/harita01.dba';

set newname for datafile 34 to '+DATA/ORCLYENI/DATAFILE/pmdata01.dba';

set newname for datafile 33 to '+DATA/ORCLYENI/DATAFILE/deveci_29.dba';

set newname for datafile 32 to '+DATA/ORCLYENI/DATAFILE/deveci_28.dba';

set newname for datafile 31 to '+DATA/ORCLYENI/DATAFILE/deveci_27.dba';

set newname for datafile 30 to '+DATA/ORCLYENI/DATAFILE/deveci_26.dba';

set newname for datafile 29 to '+DATA/ORCLYENI/DATAFILE/deveci_25.dba';

set newname for datafile 28 to '+DATA/ORCLYENI/DATAFILE/deveci_24.dba';

set newname for datafile 27 to '+DATA/ORCLYENI/DATAFILE/deveci_23.dba';

set newname for datafile 26 to '+DATA/ORCLYENI/DATAFILE/deveci_22.dba';

set newname for datafile 25 to '+DATA/ORCLYENI/DATAFILE/deveci_21.dba';

set newname for datafile 24 to '+DATA/ORCLYENI/DATAFILE/deveci_20.dba';

set newname for datafile 23 to '+DATA/ORCLYENI/DATAFILE/deveci_19.dba';

set newname for datafile 22 to '+DATA/ORCLYENI/DATAFILE/deveci_18.dba';

set newname for datafile 21 to '+DATA/ORCLYENI/DATAFILE/deveci_17.dba';

set newname for datafile 20 to '+DATA/ORCLYENI/DATAFILE/deveci_16.dba';

set newname for datafile 19 to '+DATA/ORCLYENI/DATAFILE/deveci_15.dba';

set newname for datafile 18 to '+DATA/ORCLYENI/DATAFILE/deveci_14.dba';

set newname for datafile 17 to '+DATA/ORCLYENI/DATAFILE/deveci_13.dba';

set newname for datafile 16 to '+DATA/ORCLYENI/DATAFILE/deveci_12.dba';

set newname for datafile 15 to '+DATA/ORCLYENI/DATAFILE/deveci_11.dba';

set newname for datafile 14 to '+DATA/ORCLYENI/DATAFILE/deveci_10.dba';

set newname for datafile 13 to '+DATA/ORCLYENI/DATAFILE/deveci_09.dba';

set newname for datafile 12 to '+DATA/ORCLYENI/DATAFILE/deveci_08.dba';

set newname for datafile 11 to '+DATA/ORCLYENI/DATAFILE/deveci_07.dba';

set newname for datafile 10 to '+DATA/ORCLYENI/DATAFILE/deveci_06.dba';

set newname for datafile 9 to '+DATA/ORCLYENI/DATAFILE/deveci_05.dba';

set newname for datafile 8 to '+DATA/ORCLYENI/DATAFILE/deveci_04.dba';

set newname for datafile 7 to '+DATA/ORCLYENI/DATAFILE/deveci_03.dba';

set newname for datafile 6 to '+DATA/ORCLYENI/DATAFILE/deveci_02.dba';

set newname for datafile 5 to '+DATA/ORCLYENI/DATAFILE/deveci.dba';

set newname for datafile 4 to '+DATA/ORCLYENI/DATAFILE/users01.dbf';

set newname for datafile 3 to '+DATA/ORCLYENI/DATAFILE/undotbs01.dbf';

set newname for datafile 2 to '+DATA/ORCLYENI/DATAFILE/sysaux01.dbf';

set newname for datafile 1 to '+DATA/ORCLYENI/DATAFILE/system01.dbf';

restore database;

switch datafile all;

RELEASE CHANNEL CH1;

RELEASE CHANNEL CH2;

RELEASE CHANNEL CH3;

RELEASE CHANNEL CH4;

RELEASE CHANNEL CH5;

RELEASE CHANNEL CH6;

RELEASE CHANNEL CH7;

RELEASE CHANNEL CH8;

RELEASE CHANNEL CH9;

RELEASE CHANNEL CH10;

RELEASE CHANNEL CH11;

RELEASE CHANNEL CH12;

RELEASE CHANNEL CH13;

RELEASE CHANNEL CH14;

RELEASE CHANNEL CH15;

RELEASE CHANNEL CH16;

}

oracle:deveci01:/home/users/oracle:>

This process is taking long time, so you can run Restore process with Nohup like following.

nohup rman cmdfile RestoreORCL.rman log RestoreORCL.rman.log &

Restore Backup from Tape

Restore is started, you can look and analyze log files like following.

oracle:deveci01:/home/users/oracle:>vi RestoreORCL.rman.log 

"RestoreORCL.rman.log" 979 lines, 49631 characters 

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jun 11 14:22:43 2014

allocated channel: CH16

channel CH16: SID=1565 device type=SBT_TAPE

channel CH16: NMDA Oracle v1.6.0

sent command to channel: CH16

executing command: SET NEWNAME

Starting restore at 11-JUN-14

 

channel CH1: starting datafile backup set restore

channel CH1: specifying datafile(s) to restore from backup set

channel CH1: restoring datafile 00004 to +DATA/ORCLYENI/DATAFILE/users01.dbf

channel CH1: reading from backup piece rman_ORCL_inc0_1795_1_849916841

channel CH2: starting datafile backup set restore

channel CH2: specifying datafile(s) to restore from backup set

channel CH2: restoring datafile 00072 to +DATA/ORCLYENI/DATAFILE/users02.dbf

channel CH2: reading from backup piece rman_ORCL_inc0_1796_1_849916841

channel CH3: starting datafile backup set restore

channel CH3: specifying datafile(s) to restore from backup set

channel CH3: restoring datafile 00003 to +DATA/ORCLYENI/DATAFILE/undotbs01.dbf

channel CH3: reading from backup piece rman_ORCL_inc0_1794_1_849916841

channel CH4: starting datafile backup set restore

channel CH4: specifying datafile(s) to restore from backup set

channel CH4: restoring datafile 00086 to +DATA/ORCLYENI/DATAFILE/users06.dbf

channel CH4: reading from backup piece rman_ORCL_inc0_1792_1_849916841

channel CH5: starting datafile backup set restore

channel CH5: specifying datafile(s) to restore from backup set

channel CH5: restoring datafile 00075 to +DATA/ORCLYENI/DATAFILE/undotbs03.dbf

channel CH5: reading from backup piece rman_ORCL_inc0_1791_1_849916841

channel CH6: starting datafile backup set restore

channel CH6: specifying datafile(s) to restore from backup set

channel CH6: restoring datafile 00085 to +DATA/ORCLYENI/DATAFILE/users05.dbf

channel CH6: reading from backup piece rman_ORCL_inc0_1793_1_849916841

channel CH7: starting datafile backup set restore

channel CH7: specifying datafile(s) to restore from backup set

channel CH7: restoring datafile 00074 to +DATA/ORCLYENI/DATAFILE/undotbs02.dbf

channel CH7: reading from backup piece rman_ORCL_inc0_1798_1_849920088

channel CH8: starting datafile backup set restore

channel CH8: specifying datafile(s) to restore from backup set

channel CH8: restoring datafile 00073 to +DATA/ORCLYENI/DATAFILE/users03.dbf

channel CH8: reading from backup piece rman_ORCL_inc0_1797_1_849920088

channel CH9: starting datafile backup set restore

channel CH9: specifying datafile(s) to restore from backup set

channel CH9: restoring datafile 00063 to +DATA/ORCLYENI/DATAFILE/iapro_harita14.dba

channel CH9: reading from backup piece rman_ORCL_inc0_1801_1_849920345

channel CH10: starting datafile backup set restore

channel CH10: specifying datafile(s) to restore from backup set

channel CH10: restoring datafile 00084 to +DATA/ORCLYENI/DATAFILE/users04.dbf

channel CH10: reading from backup piece rman_ORCL_inc0_1799_1_849920124

channel CH11: starting datafile backup set restore

channel CH11: specifying datafile(s) to restore from backup set

channel CH11: restoring datafile 00064 to +DATA/ORCLYENI/DATAFILE/iapro_harita13.dba

channel CH11: reading from backup piece rman_ORCL_inc0_1802_1_849920352

channel CH12: starting datafile backup set restore

channel CH12: specifying datafile(s) to restore from backup set

channel CH12: restoring datafile 00061 to +DATA/ORCLYENI/DATAFILE/iapro_harita12.dba

channel CH12: reading from backup piece rman_ORCL_inc0_1800_1_849920330

channel CH13: starting datafile backup set restore

channel CH13: specifying datafile(s) to restore from backup set

channel CH13: restoring datafile 00036 to +DATA/ORCLYENI/DATAFILE/harita02.dba

channel CH13: reading from backup piece rman_ORCL_inc0_1807_1_849923187

channel CH14: starting datafile backup set restore

channel CH14: specifying datafile(s) to restore from backup set

channel CH14: restoring datafile 00035 to +DATA/ORCLYENI/DATAFILE/harita01.dba

channel CH14: reading from backup piece rman_ORCL_inc0_1806_1_849923180

channel CH15: starting datafile backup set restore

channel CH15: specifying datafile(s) to restore from backup set

channel CH15: restoring datafile 00039 to +DATA/ORCLYENI/DATAFILE/iapro_harita01.dba

channel CH15: reading from backup piece rman_ORCL_inc0_1808_1_849923195

channel CH16: starting datafile backup set restore

channel CH16: specifying datafile(s) to restore from backup set

channel CH16: restoring datafile 00067 to +DATA/ORCLYENI/DATAFILE/iapro_harita16.dba

channel CH16: reading from backup piece rman_ORCL_inc0_1803_1_849923083

channel CH15: piece handle=rman_ORCL_inc0_1808_1_849923195 tag=ORCL_INC0_BACKUP

channel CH15: restored backup piece 1

channel CH15: restore complete, elapsed time: 00:21:37

input datafile copy RECID=416 STAMP=849978593 file name=+DATA/ORCLYENI/datafile/users14.dbf

datafile 102 switched to datafile copy

input datafile copy RECID=417 STAMP=849978593 file name=+DATA/ORCLYENI/datafile/harita04.dba

released channel: CH1

released channel: CH16

 

Recovery Manager complete.

Step-5: Restore Archivelog and Recover database

Once Restore process is done, start Recovery process and recover database. Firstly find related Archivelog sequence or point in time you want to recover.

RMAN> 

database name is "ORCL" and DBID is 2402765690

 

RMAN> connect target /

 

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

 

RMAN> list backup of archivelog from time 'sysdate-776';

 

 

List of Backup Sets

===================

        .

        .

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

1580180174 505.25M    SBT_TAPE    00:02:48     11-JUN-14

        BP Key: 1580180177   Status: AVAILABLE  Compressed: NO  Tag: ORCL_ARC_BACKUP

        Handle: rman_ORCL_arc_1907_1_849985279   Media: V10DBBackup.003

 

  List of Archived Logs in backup set 1580180174

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    123     68829021957 11-JUN-14 68829259069 11-JUN-14

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

1581845193 436.00M    SBT_TAPE    00:00:23     11-JUN-14

        BP Key: 1581845306   Status: AVAILABLE  Compressed: NO  Tag: ORCL_ARC_BACKUP

        Handle: rman_ORCL_arc_1909_1_849999650   Media: V10DBBackup.003

 

  List of Archived Logs in backup set 1581845193

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    124     68829259069 11-JUN-14 68829533952 11-JUN-14

Recover database from Tape Backup

11. If you specify related Archivelog sequence, you can start Recovery process. Recovery script will be like following.

oracle:deveci01:/home/users/oracle:>more RecoverORCL.rman

connect catalog rman/rman123@CAT

set DBID=2402765690

connect target /

run

{

ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH5 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH6 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH7 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH8 TYPE 'SBT_TAPE';

send 'NSR_ENV=(NSR_CLIENT=deveci01.localdomain,NSR_SERVER=TAPE_SERVER.localdomain) ';

set until sequence 124 thread 1;

recover database;

RELEASE CHANNEL CH1;

RELEASE CHANNEL CH2;

RELEASE CHANNEL CH3;

RELEASE CHANNEL CH4;

RELEASE CHANNEL CH5;

RELEASE CHANNEL CH6;

RELEASE CHANNEL CH7;

RELEASE CHANNEL CH8;

}

12. You can start Recovery process with Nohup.

nohup rman cmdfile RecoverORCL.rman log RecoverORCL.rman.log &

13. You can track logfile to see errors or warnings.

oracle:deveci01:/home/users/oracle:>cat RecoverORCL.rman.log 

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 12 09:37:37 2014

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

connected to recovery catalog database

executing command: SET DBID

database name is "ORCL" and DBID is 2402765690

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

allocated channel: CH8

channel CH8: SID=3 device type=SBT_TAPE

channel CH8: NMDA Oracle v1.6.0

 

sent command to channel: CH1

executing command: SET until clause

Starting recover at 12-JUN-14

starting media recovery

channel CH2: restored backup piece 1

channel CH2: restore complete, elapsed time: 00:00:15

channel CH1: piece handle=rman_ORCL_arc_1821_1_849927663 tag=ORCL_ARC_BACKUP

channel CH1: restored backup piece 1

channel CH1: restore complete, elapsed time: 00:00:25

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_119_848780439.dbf thread=1 sequence=119

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_120_848780439.dbf thread=1 sequence=120

channel CH3: piece handle=rman_ORCL_arc_1903_1_849956438 tag=ORCL_ARC_BACKUP

channel CH3: restored backup piece 1

channel CH3: restore complete, elapsed time: 00:00:30

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_121_848780439.dbf thread=1 sequence=121

channel CH4: piece handle=rman_ORCL_arc_1905_1_849970843 tag=ORCL_ARC_BACKUP

channel CH4: restored backup piece 1

channel CH4: restore complete, elapsed time: 00:00:33

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_122_848780439.dbf thread=1 sequence=122

channel CH5: piece handle=rman_ORCL_arc_1907_1_849985279 tag=ORCL_ARC_BACKUP

channel CH5: restored backup piece 1

channel CH5: restore complete, elapsed time: 00:00:36

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_123_848780439.dbf thread=1 sequence=123

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

Finished recover at 12-JUN-14

released channel: CH1

Recovery Manager complete.

Step-6:

Restore and Recovery are done, Before open database with resetlogs, modify logfile path like datafile ” set newname for datafile”, if you don’t modify logfile path, you will get an error. Change logfile path with alter database rename file command.

SQL> select member from v$logfile;

 

MEMBER

--------------------------------------------------------------------------------

+DATA/ORCLYENI/onlinelog/redo0101.log

+DATA/ORCLYENI/onlinelog/redo0102.log

+DATA/ORCLYENI/onlinelog/redo0201.log

+DATA/ORCLYENI/onlinelog/redo0202.log

+DATA/ORCLYENI/onlinelog/redo0301.log

/gecici/data/oradata/ORCL/redo0302.log

/gecici/data/oradata/ORCL/redo0303.log

/gecici/data/oradata/ORCL/redo0304.log

/gecici/data/oradata/ORCL/redo0305.log

/gecici/data/oradata/ORCL/redo0306.log

/gecici/data/oradata/ORCL/redo0307.log

 

MEMBER

--------------------------------------------------------------------------------

+DATA/ORCLYENI/onlinelog/redo0302.log

/gecici/data/oradata/ORCL/redo0308.log

 

13 rows selected.

 

SQL>

 

 

SQL> ALTER DATABASE RENAME FILE  '/gecici/data/oradata/ORCL/redo0302.log' to '+DATA/ORCLYENI/onlinelog/redo0303.log';

 

Database altered.

SQL> ALTER DATABASE RENAME FILE  '/gecici/data/oradata/ORCL/redo0303.log' to '+DATA/ORCLYENI/onlinelog/redo0304.log';

Database altered.

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0304.log' to '+DATA/ORCLYENI/ONLINELOG/redo0305.log';

Database altered.

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0305.log' to '+DATA/ORCLYENI/ONLINELOG/redo0306.log';

Database altered.

 

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0306.log' to '+DATA/ORCLYENI/ONLINELOG/redo0307.log';

Database altered.

 

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0307.log' to '+DATA/ORCLYENI/ONLINELOG/redo0308.log';

Database altered.

 

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0308.log' to '+DATA/ORCLYENI/ONLINELOG/redo0309.log';

Database altered.

14- Check all logfiles path if they exist or not.

 

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

+DATA/ORCLYENI/onlinelog/redo0101.log

+DATA/ORCLYENI/onlinelog/redo0102.log

+DATA/ORCLYENI/onlinelog/redo0201.log

+DATA/ORCLYENI/onlinelog/redo0202.log

+DATA/ORCLYENI/onlinelog/redo0301.log

+DATA/ORCLYENI/onlinelog/redo0303.log

+DATA/ORCLYENI/onlinelog/redo0304.log

+DATA/ORCLYENI/onlinelog/redo0305.log

+DATA/ORCLYENI/onlinelog/redo0306.log

+DATA/ORCLYENI/onlinelog/redo0307.log

+DATA/ORCLYENI/onlinelog/redo0308.log

 

MEMBER

--------------------------------------------------------------------------------

+DATA/ORCLYENI/onlinelog/redo0302.log

+DATA/ORCLYENI/onlinelog/redo0309.log

13 rows selected.

14. When i open database with resetlogs option, i got following logfile error. You should clear this logfile to solve this error like following.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00392: log 5 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 5 thread 1: '+DATA/ORCLYENI/onlinelog/redo0304.log'



SQL> alter database clear unarchived logfile group 5;

Database altered.

15. Now you can open database with resetlogs option.

SQL> alter database open resetlogs;

Database altered.

16. You should add new tempfile.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/ORCLYENI/DATAFILE/temp01.dbf' SIZE 33554416K AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED;

Tablespace altered.

Do you want to learn more details about RMAN, then read the following articles.

RMAN Tutorial | Backup, Restore and Recovery Tutorials For Beginner Oracle DBA

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )