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
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