Table of Contents
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
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