How to setup efficient rman backup in Oracle database 11g

Jephe Wu - http://linuxtechres.blogspot.com

Environment:  Oracle database 11.2.0.3 Enterprise edition 64bit.
Objective: create rman backup script to backup database plus archivelog and keep 3 days.


Steps/scripts:

1. Environment variables
[oracle@db01 fast_recovery_area]$ more /usr/local/bin/oraset 
export PATH=$PATH:/u01/app/oracle/product/11.2.0/db_1/bin:/home/oracle/bin
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' 
export ORACLE_SID=orcl

ORAENV_ASK=NO
. /usr/local/bin/oraenv
unset ORAENV_ASK

2. cronjobs 
[root@db01 bin]# more /etc/cron.d/rman 
#!/bin/sh
MAILTO=jephe.wu@domain.com
0 23 * * * oracle /home/oracle/bin/rman.sh

3. scripts
[root@db01 bin]# more /home/oracle/bin/rman.sh

#!/bin/bash
. /usr/local/bin/oraset
MAIL_LIST='jwu@domain.com'

export TIMESTAMP=`date +%Y%m%d%H%M`
export BACKUP_DIR=/u01/orarman
export LOG=$BACKUP_DIR/rman_full_${TIMESTAMP}.log
export week=`date +%w`

find $BACKUP_DIR/rman_full*.log -type f -mtime +7 -exec rm -f {} \;

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 # this is important, otherwise the next line $ORACLE_HOME/bin/rman will be invalid.

$ORACLE_HOME/bin/rman log=${LOG} <<EOF

connect target /
set echo on;
show all;
report unrecoverable;

# refer to http://docs.oracle.com/cd/B14117_01/server.101/b10734/rcmconc3.htm for explanation
# Run CROSSCHECK to change the status of these files to EXPIRED and then run DELETE EXPIRED to delete the records from the RMAN repository
# To ensure that data about backups in the recovery catalog or control file is synchronized with actual files on disk or in the media management catalog, perform a crosscheck. 
# The CROSSCHECK command operates only on files that are recorded in the RMAN repository.
# Update outdated information about backups that disappeared from disk or tape or became corrupted, maybe due to umount a partition or something.
# Update the repository if you delete archived redo logs or other files with operating system commands
# crosscheck command itself doesn't delete record, you must use delete command to remove the record for the expired file from the repository
crosscheck backup;
delete force noprompt expired backup;

crosscheck copy;
delete force noprompt expired copy;

crosscheck archivelog all;
delete noprompt expired archivelog all;

use CONTROLFILE_RECORD_KEEP_TIME=7 by default to record those backup history 

# You can specify that DELETE should remove backups that are EXPIRED or OBSOLETE. 
# if you run DELETE EXPIRED on a backup that exists, RMAN issues a warning and does not delete the backup. You can override this behavior and delete the backup by running DELETE FORCE.

# Configure environment, redundant to run each time, but ensures set correctly.
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE BACKUP OPTIMIZATION CLEAR;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT='/u01/orarman/%n_%U.bu' MAXPIECESIZE 20G;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
# Note: The Retention Policy applies to Backups. It does not apply to ArchiveLogs.
The ArchiveLog Deletion Policy applies to ArchiveLogs. It does not apply to Backups.
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/orarman/%n_%F';

# Backup datafiles, archive redo logs, and control file
backup as compressed backupset incremental level=0 database plus archivelog;
or
backup database plus archivelog;

# for backup ... delete [all] input if any
# If you specify DELETE INPUT (without ALL), then RMAN deletes only the specific files that it backs up. 
# If you specify ALL INPUT, then RMAN deletes all copies of the files recorded in the RMAN repository.

# Always use DELETE command within RMAN to remove RMAN backups, rather than an operating system or media manager utility or command

# Otherwise, the RMAN repository can contain records of backups that are no longer available for use in restore operations.

delete force noprompt obsolete;   # only delete obsolete backups according to retention policy, not archivelogs.

delete noprompt archivelog all backed up 1 times to device type disk;   # only for archivelog, not for backups.

# backup controlfile to trace

sql 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE';
or
sql "ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ''/u01/orarman/controlfile.backup.${week}.sql'' reuse";
sql "CREATE PFILE=''/u01/orarman/spfile.backup'' FROM SPFILE";

# now do a trial restore to see if all is ok
restore database validate check logical;
restore controlfile to '/tmp/' validate check logical;
restore spfile validate check logical;
restore archivelog from time 'sysdate-5' validate check logical;

Note: since we used the recovery windows as 5 days in the beginninig, so we use use 'sysdate-5' here.

# now do a checkup for running database
validate database include current controlfile plus archivelog;
----------
For 10g, use below to validate, 10g doesn't support 'validate database' command to check running database.

restore database validate check logical;
restore archivelog from time 'sysdate-2' validate;
restore controlfile validate;
restore spfile validate;
-----------------------------------

# report 

report unrecoverable;

Note: for datawarehouse, it's critical there's no nolog operation during rman backup period.

#report schema;

#list backup summary;

#list backupset;

#list backup of controlfile;

exit;

EOF

if [ $? -ne "0" ]; then
   mailx -s "rman full backup for `hostname` failed" $MAIL_LIST < $LOG
else
   mailx -s "rman full backup for `hostname` successful" $MAIL_LIST < $LOG
fi

2. archivelog retention policy and auto deletion

Based on page http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmconfb.htm  for archivelog retention.

Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Only logs in the flash recovery area can be deleted automatically by the database. For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs when additional disk space is required. You can manually delete eligible logs from any location, whether inside or outside the flash recovery area, when you issue BACKUP ... DELETE INPUT or DELETE ARCHIVELOG.

The archived redo log deletion policy is configured to NONE by default. In this case, RMAN considers archived redo log files in the recovery area as eligible for deletion if they meet both of the following conditions:

You can use the CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP integer TIMES TO DEVICE TYPE command to enable an archived log deletion policy. This configuration specifies that archived logs are eligible for deletion only when the specified number of archived log backups exist on the specified device type.

Refer to http://www.fatihacar.com/blog/delete-archivelog-using-rman-in-oracle/ as follows:

Archivelog List Commands

RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10′;
RMAN>list copy of archivelog from time ‘SYSDATE-10′
RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

Archivelog Delete Commands

RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10′;
RMAN>delete archivelog from time ‘SYSDATE-10′
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;

Note : Also, you can use noprompt statement for do not yes-no question.
RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

RMAN>delete noprompt archivelog all backed up 1 times to device type disk;

3. how to use rman to restore archive log

RMAN> run {
set archivelog destination to ‘/tmp’;
restore archivelog from logseq=6 until logseq=7;
}

#######
How to check datafile block corruption?

rman> backup validate check logical datafile 3;
rman> backup validate check logical database;

The CHECK LOGICAL option checks for both PHYSICAL and LOGICAL Block corruptions

sql> select * from v$database_block_corruption;

References:
1. How to identify all the Corrupted Objects in the Database with RMAN [ID 472231.1]
2. Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]
3. How to Check Archivelogs for Corruption using RMAN [ID 377146.1]

4. 

Oracle10g / 11g - Getting Started with Recovery Manager (RMAN) [ID 360416.1]

If you simply want to check for corruption but not actually create a backuppiece then use the 'validate' option:

RMAN>backup validate archivelog all;

4. How To Check (Validate) If RMAN Backup(s) Are Good [ID 338607.1]

Example: Refer to Oracle support:

11.2 RMAN Block recovery syntax
=========================

In 11.2 you can use RMAN to:

1) validate all database files and archived redo log files for physical and logical corruption:

BACKUP VALIDATE CHECK LOGICAL
  DATABASE ARCHIVELOG ALL;

2) to check individual data blocks, as shown in the following example:

VALIDATE DATAFILE 4 BLOCK 10 TO 13;

3) validate backup sets:

VALIDATE BACKUPSET 3;

You specify backup sets by primary key, which is shown in the output of the LIST BACKUP command.


The following RMAN command recovers the corrupted blocks:


1) recover all corrupted blocks reported in v$database_block_corruption

RMAN> RECOVER CORRUPTION LIST;

2) recover individual blocks, see eg:

RMAN> RECOVER DATAFILE 1 BLOCK 233, 235 DATAFILE 2 BLOCK 100 TO 200;