Configure Fast Recovery Area and RMAN

Configure Fast Recovery Area and RMAN

1. Enabling the Fast Recovery Area

This is used to be called Flash Recovery Area, but since it is ofen confused with the Flashback feature. It is now called Fast Recovery Area.

It is enabled by setting two initializatoin parameters. Set DB_RECOVERY_FILE_DEST first.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/oradata/TESTDB/archivelog' SCOPE=BOTH;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=520G SCOPE=BOTH;

The size needs to be larger than at least following files

Size of datafiles: 162 gb

Size of redo logs: 250mb/grp*16 groups = 4 gb

Size of 1 day of archived redo logs: 300 log switches * 250 mb/log = 73 gb

size of backupset: 20 gb/backupset * 4= 80 gb

size of control file 12 mb

After getting fast recovery area almost full in the alert log, did this

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=800G SCOPE=BOTH;

2. Configure the Backup Retention Policy

Specifies how many full or level 0 backups of each datafil and control file that RMAN should keep. if the file exceeds the setting, then it is considered obselete.

RMAN>CONFIGURE RETENTION POLICY TO REDUNDANCY 4;

3. Auto Backup Control File and SPfile when it changes.

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON

4. Archive Log Deletion Policy

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

This policy makes sure the archive logs are not deleted until it is shipped and applied on standby

5. Configure Compression

RMAN>CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;

--The default OPTIMIZE FOR LOAD TRUE ensure RMAN avoid using addtional CPU resouces to perform precompression Block Processing.

6. Configure Parallelism

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;

7. DBID of primary TESTDBand standby TESTDB: DBID=299787364

To perform a disaster recovery, you must have the following:

Backups of all datafiles

All archived redo logs generated after the creation time of the oldest backup that you intened to restore

At lesast one control file autobackup

a record of DBID of the database

Setting Up Scripts

The backup scripts are stored in /home/oracle/bin/dba_scripts on both primary and standby

chmod 775 if the script you created were not executable at begining.

On Primary

primarydb_backup.rman -- this script perform a full database backup and then delete the archivelog files every night

On Standby

standby_archive_clean.sh -- this script deletes archive logs every night

primarydb_backup.rman

CONNECT TARGET /
SET COMMAND ID TO 'cron_rman_full_bk_&1';
backup device type disk tag 'primary_full_daily' database;
backup device type disk tag 'primary_full_daily' archivelog all not backed up delete all input;
run {
allocate channel oem_backup_disk1 type disk maxpiecesize 1000 G;
backup tag 'primary_full_daily' current controlfile;
release channel oem_backup_disk1;
}
allocate channel for maintenance type disk;
crosscheck backupset;
crosscheck archivelog all;
delete noprompt obsolete device type disk;
release channel;
EXIT;

primarydb_back.sh
#!/bin/bash
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE; ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_HOME;ORACLE_SID=TESTDB
export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
PATH=$PATH:$HOME/bin
export PATH
CURRENT_DATE=`date +%Y%m%d`
logfile="/home/oracle/bin/dba_scripts/primarydb_backup$CURRENT_DATE.log"
/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman @ '/home/oracle/bin/dba_scripts/primarydb_backup.rman' USING $CURRENT_DATE log /home/oracle/bin/dba_scripts/primarydb_backup$CURRENT_DATE.log
status=$?
if [ $status -gt 0 ]; then
mailx -s "BACKUP FAILED: TESTDB" dba_email_here<< !
`cat $logfile`
!
else
mailx -s "BACKUP SUCCESSFUL: TESTDB" dba_email_here << !
`cat $logfile`
!
fi

rm_empty_backup_dir.sh
this script remove empty directories

for folder in $(find /u01/app/oracle/oradata/TESTDB/archivelog/TESTDB_PRIMARY/backupset -type d); do
if [ "`ls $folder | wc -l`" -eq 0 ]; then
rmdir $folder
fi
done


standby_archive_clean.sh
#!/bin/bash
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE; ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_HOME;ORACLE_SID=TESTDB
export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
PATH=$PATH:$HOME/bin
export PATH
CURRENT_DATE=`date +%Y%m%d`
logfile="/u01/app/oracle/admin/TESTDB/dba_scripts/standby_archive_clean$CURRENT_DATE.log"
/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman log /u01/app/oracle/admin/TESTDB/dba_scripts/standby_archive_clean$CURRENT_DATE.log << EOF
CONNECT TARGET /
SET COMMAND ID TO 'DELETE STANDBY ARCHIVE';
DELETE NOPROMPT ARCHIVELOG ALL;
CROSSCHECK ARCHIVELOG ALL;
EXIT;
EOF
status=$?
if [$status -gt 0]; then
mailx -s "STANDBY ARCHIVE CLEAN FAILED: TESTDB" umdoitdwdba@missouri.edu << !
`cat $logfile`
!
else
mailx -s "SUCCESSFUL STANDBY ARCHIVE CLEAN: TESTDB" umdoitdwdba@missouri.edu << !
`cat $logfile`
!
fi