Overview Backup and Recovery for RAC systems

Overview

  • Unlike pre-11gR2 we don’t need to off/on cluster_database parameter
  • archive log and Fast Recovery settings must remain consistent across all instances
  • put archive logs and redo logs on shared devices to allow all RAC instances to access logs in case of recovery or restore
  • Use a FRA diskgroup to speed up recovery

 

Define a Fast Recovery Destination

Create a ASM diskgroup and verify that all instances have mounted this diskgroup
$  asmcmd lsdg FRA
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    204797   204659                0          204659              0             N  FRA/

Enable Fast Recovery Area at database level :
SQL> alter system set db_recovery_file_dest_size=180g scope=both SID='*';
System altered.
SQL> alter system set db_recovery_file_dest='+FRA' scope=both SID='*';
System altered.

 

Stop RAC database and enable archive mode

SQL> archive log list
Database log mode             No Archive Mode
Automatic archival            Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     368
Current log sequence           369

$ srvctl stop database -d grac4 -o immediate
$ srvctl status  database -d grac4 
Instance grac41 is not running on node grac41
Instance grac42 is not running on node grac42
Instance grac43 is not running on node grac43

SQL> startup mount
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size            2253024 bytes
Variable Size          486543136 bytes
Database Buffers      838860800 bytes
Redo Buffers            8519680 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open
Database altered.

Verfiy new settings:
SQL>  select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list
Database log mode             Archive Mode
Automatic archival            Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     369
Next log sequence to archive   370
Current log sequence           370

 

Display current RMAN settings

$ rman target=/
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 30 17:36:16 2013
connected to target database: GRAC4 (DBID=794109772)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name GRAC4 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
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 '/u01/app/oracle/product/11204/racdb/dbs/snapcf_grac41.f'; # default

 

Create snapshot control file on shared device

RMAN>  CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/snapcf_grac4.';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/snapcf_grac4.';
new RMAN configuration parameters are successfully stored

RMAN> show SNAPSHOT CONTROLFILE NAME;
RMAN configuration parameters for database with db_unique_name GRAC4 are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/snapcf_grac4.';

Enable Block Change Tracking

SQL> alter database enable block change tracking using file '+FRA/bct.dbf';
Database altered.
SQL>  select * from v$block_change_tracking;
STATUS       FILENAME                 BYTES
---------- -------------------------------- ----------
ENABLED    +FRA/bct.dbf               11599872

Configure RMAN 
RMAN> configure retention policy to redundancy 2;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Shell script running a level 0 backup from any of the available instances

#!/bin/bash
logloc=`date '+rman_backup_lvl0_grac4_%d%m%y%H%M%S'`.log
$ORACLE_HOME/bin/rman target=/ log=$logloc << EOF
run
{
allocate channel ch1 type Disk connect sys/sys@grac41;
allocate channel ch2 type Disk connect sys/sys@grac42;
allocate channel ch3 type Disk connect sys/sys@grac43;
backup  incremental level=0 database plus archivelog delete input;
release channel ch1;
release channel ch2;
release channel ch3;
}
delete noprompt obsolete;
EOF
cat $logloc

 

Monitor RMAN progress and load balancing

SQL>  @check_rman
SQL> SELECT s.inst_id, s.SID, p.SPID, s.CLIENT_INFO FROM GV$PROCESS p, GV$SESSION s WHERE p.ADDR = s.PADDR AND CLIENT_INFO LIKE 'rman%' 
     and p.inst_id =s.inst_id   order by s.inst_id;
   INST_ID  SID SPID             CLIENT_INFO
---------- ---- ------------------------ ------------------------------
     1  195 10879             rman channel=ch1
     2   59  6827             rman channel=ch2
     3   55 17027             rman channel=ch3
--> All 3 instances are used for the database backup

SQL> SELECT inst_id, SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM GV$SESSION_LONGOPS
      WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK order by inst_id;

   INST_ID  SID    SERIAL#    CONTEXT       SOFAR  TOTALWORK %_COMPLETE
---------- ---- ---------- ---------- ---------- ---------- ----------
     1     195      12425        1      164094     597760     27.45
     2      59       1107        1      204926     302080     67.84
     3      55         69        1       22398      33280     67.3

 

Display the current backup status

RMAN>  list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
14      B  A  A DISK        31-dec-2013 1150:22 1       1       NO         TAG20131231T115014
15      B  A  A DISK        31-dec-2013 1150:17 1       1       NO         TAG20131231T115014
16      B  A  A DISK        31-dec-2013 1150:22 1       1       NO         TAG20131231T115014
17      B  0  A DISK        31-dec-2013 1155:31 1       1       NO         TAG20131231T115025
18      B  0  A DISK        31-dec-2013 1202:42 1       1       NO         TAG20131231T115025
19      B  0  A DISK        31-dec-2013 1209:50 1       1       NO         TAG20131231T115025
20      B  A  A DISK        31-dec-2013 1209:59 1       1       NO         TAG20131231T120957
21      B  A  A DISK        31-dec-2013 1210:04 1       1       NO         TAG20131231T120957
22      B  A  A DISK        31-dec-2013 1209:59 1       1       NO         TAG20131231T120957
23      B  F  A DISK        31-dec-2013 1210:15 1       1       NO         TAG20131231T121004
24      B  A  A DISK        31-dec-2013 1226:19 1       1       NO         TAG20131231T122617
25      B  A  A DISK        31-dec-2013 1226:23 1       1       NO         TAG20131231T122617
26      B  A  A DISK        31-dec-2013 1226:18 1       1       NO         TAG20131231T122617
27      B  1  A DISK        31-dec-2013 1226:29 1       1       NO         TAG20131231T122621
28      B  1  A DISK        31-dec-2013 1226:30 1       1       NO         TAG20131231T122621
29      B  1  A DISK        31-dec-2013 1226:45 1       1       NO         TAG20131231T122621
30      B  A  A DISK        31-dec-2013 1226:55 1       1       NO         TAG20131231T122653
31      B  A  A DISK        31-dec-2013 1226:59 1       1       NO         TAG20131231T122653
32      B  A  A DISK        31-dec-2013 1226:54 1       1       NO         TAG20131231T122653
33      B  F  A DISK        31-dec-2013 1227:05 1       1       NO         TAG20131231T122658

RMAN> list backup by file;
List of Datafile Backups
========================
File Key     TY LV S Ckp SCN    Ckp Time            #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- ------------------- ------- ------- ---------- ---
1    28      B  1  A 24498346   31-dec-2013 1226:25 1       1       NO         TAG20131231T122621
     17      B  0  A 24493443   31-dec-2013 1150:28 1       1       NO         TAG20131231T115025
2    29      B  1  A 24498343   31-dec-2013 1226:30 1       1       NO         TAG20131231T122621
     18      B  0  A 24493441   31-dec-2013 1150:33 1       1       NO         TAG20131231T115025
3    28      B  1  A 24498346   31-dec-2013 1226:25 1       1       NO         TAG20131231T122621
     17      B  0  A 24493443   31-dec-2013 1150:28 1       1       NO         TAG20131231T115025
4    27      B  1  A 24498339   31-dec-2013 1226:25 1       1       NO         TAG20131231T122621
     19      B  0  A 24493437   31-dec-2013 1150:28 1       1       NO         TAG20131231T115025
5    29      B  1  A 24498343   31-dec-2013 1226:30 1       1       NO         TAG20131231T122621
     18      B  0  A 24493441   31-dec-2013 1150:33 1       1       NO         TAG20131231T115025
6    28      B  1  A 24498346   31-dec-2013 1226:25 1       1       NO         TAG20131231T122621
     17      B  0  A 24493443   31-dec-2013 1150:28 1       1       NO         TAG20131231T115025
7    29      B  1  A 24498343   31-dec-2013 1226:30 1       1       NO         TAG20131231T122621
     18      B  0  A 24493441   31-dec-2013 1150:33 1       1       NO         TAG20131231T115025

List of Archived Log Backups
============================
Thrd Seq     Low SCN    Low Time            BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- ------------------- ------- - ------- ------- ---------- ---
1    374     24482948   31-dec-2013 1012:03 14      A 1       1       NO         TAG20131231T115014
1    375     24485001   31-dec-2013 1035:50 15      A 1       1       NO         TAG20131231T115014
1    376     24487039   31-dec-2013 1056:37 15      A 1       1       NO         TAG20131231T115014
1    377     24493368   31-dec-2013 1150:05 20      A 1       1       NO         TAG20131231T120957
1    378     24496633   31-dec-2013 1209:55 26      A 1       1       NO         TAG20131231T122617
1    379     24498308   31-dec-2013 1226:06 31      A 1       1       NO         TAG20131231T122653
2    260     24484992   31-dec-2013 1035:51 14      A 1       1       NO         TAG20131231T115014
2    261     24487043   31-dec-2013 1056:42 15      A 1       1       NO         TAG20131231T115014
2    262     24493372   31-dec-2013 1150:10 21      A 1       1       NO         TAG20131231T120957
2    263     24496626   31-dec-2013 1209:58 24      A 1       1       NO         TAG20131231T122617
2    264     24498312   31-dec-2013 1226:13 32      A 1       1       NO         TAG20131231T122653
3    304     24482952   31-dec-2013 1012:03 14      A 1       1       NO         TAG20131231T115014
3    305     24484996   31-dec-2013 1035:48 14      A 1       1       NO         TAG20131231T115014
3    306     24487047   31-dec-2013 1056:38 16      A 1       1       NO         TAG20131231T115014
3    307     24493376   31-dec-2013 1150:06 22      A 1       1       NO         TAG20131231T120957
3    308     24496630   31-dec-2013 1209:54 25      A 1       1       NO         TAG20131231T122617
3    309     24498305   31-dec-2013 1226:05 30      A 1       1       NO         TAG20131231T122653

List of Control File Backups
============================
CF Ckp SCN Ckp Time            BS Key  S #Pieces #Copies Compressed Tag
---------- ------------------- ------- - ------- ------- ---------- ---
24498447   31-dec-2013 1226:58 33      A 1       1       NO         TAG20131231T122658
24496654   31-dec-2013 1210:04 23      A 1       1       NO         TAG20131231T121004
List of SPFILE Backups
======================
Modification Time   BS Key  S #Pieces #Copies Compressed Tag
------------------- ------- - ------- ------- ---------- ---
30-dec-2013 1814:43 33      A 1       1       NO         TAG20131231T122658
30-dec-2013 1814:43 23      A 1       1       NO         TAG20131231T121004

 

Test that we can restore from last full backup having all needed archive logs:

RMAN>  restore archivelog from time='sysdate - 1' validate;
Starting restore at 01-jan-2014 1044:50
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/annnf0_tag20131231t115014_0.283.835617017
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/annnf0_tag20131231t115014_0.283.835617017 tag=TAG20131231T115014
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/annnf0_tag20131231t115014_0.284.835617021
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/annnf0_tag20131231t115014_0.284.835617021 tag=TAG20131231T115014
channel ORA_DISK_1: restored backup piece 1
...
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/annnf0_tag20131231t122653_0.285.835619215
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/annnf0_tag20131231t122653_0.285.835619215 tag=TAG20131231T122653
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/annnf0_tag20131231t122653_0.276.835619219
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/annnf0_tag20131231t122653_0.276.835619219 tag=TAG20131231T122653
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 01-jan-2014 1045:03

 

Create a recovery scenario

SQL> select FILE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/grac4/datafile/users.259.826111427
+DATA/grac4/datafile/undotbs1.258.826111425
+DATA/grac4/datafile/sysaux.257.826111425
+DATA/grac4/datafile/system.256.826111425
+DATA/grac4/datafile/undotbs2.264.826111755
+DATA/grac4/datafile/undotbs3.268.826220083
/u01/oradata/grac4_dnfs_ts.dbf
--> Shutdown RAC cluster and remove DNFS datafile : /u01/oradata/grac4_dnfs_ts.dbf

Instance startup fails: 
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/oradata/grac4_dnfs_ts.dbf'

Restore and Recover Database

( Note in the above case a datafile recovery is the correct action ) 
RMAN> restore database;
Starting restore at 01-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 instance=grac41 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 +DATA/grac4/datafile/system.256.826111425
channel ORA_DISK_1: restoring datafile 00003 to +DATA/grac4/datafile/undotbs1.258.826111425
channel ORA_DISK_1: restoring datafile 00006 to +DATA/grac4/datafile/undotbs3.268.826220083
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/nnndn0_tag20131231t115025_0.263.835617031
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/nnndn0_tag20131231t115025_0.263.835617031 tag=TAG20131231T115025
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:36
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 00002 to +DATA/grac4/datafile/sysaux.257.826111425
channel ORA_DISK_1: restoring datafile 00005 to +DATA/grac4/datafile/undotbs2.264.826111755
channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/grac4_dnfs_ts.dbf
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/nnndn0_tag20131231t115025_0.270.835617037
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/nnndn0_tag20131231t115025_0.270.835617037 tag=TAG20131231T115025
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:08:06
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 00004 to +DATA/grac4/datafile/users.259.826111427
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/nnndn0_tag20131231t115025_0.271.835617031
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/nnndn0_tag20131231t115025_0.271.835617031 tag=TAG20131231T115025
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:15:17
Finished restore at 01-JAN-14

RMAN> recover database;
Starting recover at 01-JAN-14
using channel ORA_DISK_1
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 00004: +DATA/grac4/datafile/users.259.826111427
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/nnndn1_tag20131231t122621_0.261.835619187
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/nnndn1_tag20131231t122621_0.261.835619187 tag=TAG20131231T122621
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 00001: +DATA/grac4/datafile/system.256.826111425
destination for restore of datafile 00003: +DATA/grac4/datafile/undotbs1.258.826111425
destination for restore of datafile 00006: +DATA/grac4/datafile/undotbs3.268.826220083
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/nnndn1_tag20131231t122621_0.269.835619187
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/nnndn1_tag20131231t122621_0.269.835619187 tag=TAG20131231T122621
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
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 00002: +DATA/grac4/datafile/sysaux.257.826111425
destination for restore of datafile 00005: +DATA/grac4/datafile/undotbs2.264.826111755
destination for restore of datafile 00007: /u01/oradata/grac4_dnfs_ts.dbf
channel ORA_DISK_1: reading from backup piece +FRA/grac4/backupset/2013_12_31/nnndn1_tag20131231t122621_0.256.835619193
channel ORA_DISK_1: piece handle=+FRA/grac4/backupset/2013_12_31/nnndn1_tag20131231t122621_0.256.835619193 tag=TAG20131231T122621
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
starting media recovery
media recovery complete, elapsed time: 00:01:45
Finished recover at 01-JAN-14

 

References

One thought on “Overview Backup and Recovery for RAC systems”

  1. Hi Helmut,

    Thanks for this tutorial. It could not be more clearer for some of us the new DBA without the proper training.

    I am testing the script after customizing and will update you on the outcome.

    Once again I appreciate.

    Regards,

    Eric

Leave a Reply to Eric Cancel reply

Your email address will not be published. Required fields are marked *