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 )