Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Introduction:

A question was asked several times these days in the OTN forum;
“How to restore a datafile using RMAN when we backup on tape and how to then apply archive logs?”. In this article, I am going to detail the steps.
But, before going through coding, I am going to explain the backup strategy used in my environment.

Backup strategy:

Daily backup: Take incremental updated backup to disk. Backup the backup set to tape. Backup the archivelogs to tape.
Weekly backup: Take incremental updated backup to disk. Backup the Fast Recovery Area to tape.
Use the tape retention policy, to synchronize the tape retention to RMAN retention.

Backup strategy scripts:

Daily script:

CONNECT TARGET /
run{
allocate channel t1_disk_backup device type disk;
# Incrementally updated backup.
RECOVER COPY OF DATABASE WITH TAG 'LVL0_MERGE_INCR';
BACKUP CHECK LOGICAL INCREMENTAL LEVEL 1 CUMULATIVE COPIES=1 FOR RECOVER OF COPY WITH TAG 'LVL0_MERGE_INCR' DATABASE;
release channel t1_disk_backup;
allocate channel ch00 device type 'SBT_TAPE' PARMS="SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/home/oracle/tape_daily_mon2)";
backup backupset all NOT BACKED UP since time 'SYSDATE-1';
# Switch current logfile to archive
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
#Backup archive logs directly to tape and leave on disk
backup archivelog all not backed up since time 'SYSDATE-1';
release channel ch00;
}
exit;

Weekly script:

CONNECT TARGET /
run{
allocate channel t1_disk_backup device type disk;
# Incrementally updated backup.
#RECOVER COPY OF DATABASE WITH TAG 'LVL0_MERGE_INCR' UNTIL TIME 'SYSDATE-7';
RECOVER COPY OF DATABASE WITH TAG 'LVL0_MERGE_INCR';
BACKUP CHECK LOGICAL INCREMENTAL LEVEL 1 CUMULATIVE COPIES=1 FOR RECOVER OF COPY WITH TAG 'LVL0_MERGE_INCR' DATABASE;
release channel t1_disk_backup;
allocate channel ch00 device type 'SBT_TAPE' PARMS="SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/home/oracle/tape_weekly_sun)";
backup recovery area;
crosscheck backupset;
delete noprompt expired backupset;
crosscheck archivelog all;
delete noprompt expired archivelog all;
release channel ch00;
}
exit; 

Crons jobs:

05 13 * * 1-6 /home/oracle/dbbackup_scripts/rmanincrupdbackup.sh ORAWISSE > /home/oracle/dbbackup_scripts/logs/cron_d
05 13 * * 0 /home/oracle/dbbackup_scripts/rmanincrupdbackup_w.sh ORAWISSE > /home/oracle/dbbackup_scripts/logs/cron_w
Jobs executed from Monday 5th of September to Monday 12th of September.

RMAN Configurations:

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORAWISSE are:
CONFIGURE RETENTION POLICY TO NONE;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_ORAWISSE.f'; # default
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
RMAN>

Daily backups have been performed for the following days:

1- On Monday 5 September 2011.
2- On Tuesday 6 September 2011.
3- On Wednesday 7 September 2011:
4- On Thursday 8 September 2011:
5- On Friday 9 September 2011:
6- On Saturday 10 September 2011:

On Sunday 11 September 2011: As a part of the weekly backup, the Fast Recovery Area (FRA) backup has been done.

Again, a daily backup was done on Monday 12 September 2011.
On Tuesday 13 September 2011: We simulate a Failure and a loss of a datafile, before backup Tuesday.

SQL> select FILE_NAME from DBA_DATA_FILES;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ORAWISSE/datafile/o1_mf_users_74l74rmb_.dbf
/home/oracle/app/oracle/oradata/ORAWISSE/datafile/o1_mf_undotbs1_74l74rjp_.dbf
/home/oracle/app/oracle/oradata/ORAWISSE/datafile/o1_mf_sysaux_74l74rgj_.dbf
/home/oracle/app/oracle/oradata/ORAWISSE/datafile/o1_mf_system_74l74rb0_.dbf
/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que2.dbf
/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf
6 rows selected.
SQL>
SQL> create table TEST_FAILURE (P_DATE DATE) TABLESPACE TBS_TST_QUE;
Table created.
SQL>
SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> insert into test_failure values(sysdate);
1 row created.
SQL> select * from test_failure;
P_DATE
-------------------
13.09.2011 13:14:43
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> !mv /home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf /home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf.OLD
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 759172952 bytes
Database Buffers 301989888 bytes
Redo Buffers 5554176 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6:
'/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf'
SQL>

Use RMAN to Restore and Recover the missing file:

We will use the tape backup taken on Sunday to restore the missing datafile. We will use the daily backups plus archive logs and online redo logs to recover the datafile.

RMAN> run{
2> allocate channel ch00 device type 'SBT_TAPE' PARMS="SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/home/oracle/tape_weekly_sun)";
3> restore datafile 6;
4> release channel ch00;
5> }
using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=10 device type=SBT_TAPE
channel ch00: WARNING: Oracle Test Disk API
Starting restore at 13-SEP-11
channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00006 to /home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf
channel ch00: reading from backup piece 2cmm9f97_1_1
channel ch00: piece handle=2cmm9f97_1_1 tag=TAG20110911T130543
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:03
Finished restore at 13-SEP-11
released channel: ch00
RMAN> recover datafile 6;
Starting recover at 13-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORAWISSE/backupset/2011_09_11/o1_mf_nnnd1_LVL0_MERGE_INCR_76s5hskw_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORAWISSE/backupset/2011_09_11/o1_mf_nnnd1_LVL0_MERGE_INCR_76s5hskw_.bkp tag=LVL0_MERGE_INCR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORAWISSE/backupset/2011_09_12/o1_mf_nnnd1_LVL0_MERGE_INCR_76vsvokt_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORAWISSE/backupset/2011_09_12/o1_mf_nnnd1_LVL0_MERGE_INCR_76vsvokt_.bkp tag=LVL0_MERGE_INCR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORAWISSE/backupset/2011_09_12/o1_mf_nnnd1_LVL0_MERGE_INCR_76vt1mtx_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORAWISSE/backupset/2011_09_12/o1_mf_nnnd1_LVL0_MERGE_INCR_76vt1mtx_.bkp tag=LVL0_MERGE_INCR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-11
RMAN> alter database open;
database opened
RMAN>
[oracle@wissem dbbackup_scripts]$ rlsqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 13:21:59 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
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> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_failure;
P_DATE
-------------------
13.09.2011 13:14:43
SQL>

Hope it helps!
Wissem