Step by Step: Deploy RMAN Backup Strategy for Oracle Database 11g

Portable RMAN Scripts for Oracle Database 11g



Hello Guys,

 So here I am again, I would like to share this deployment guide which I created in 2012! This deployment guide is cool as you can use and re-use the scripts and deploy it from one server to another by just changing the parameter values and creating the required directories.

 For RAC deployments, it is just the same, just use your ASM diskgroup (i.e. +FRA) as you backup storage location.

 By the way, if you want to use multiple channels to make backup faster, you can use:

 RUN
{
  ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/disk1/%U'; 
  ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/disk2/%U'; 
  BACKUP DATABASE PLUS ARCHIVELOG;

}

 If you have questions, clarifications or additional information, please feel free to comment.

 Let's share and make others love Oracle, too.

What is the purpose of Backup and Recovery?

As a backup administrator, your principal duty is to devise, implement, and manage a backup and recovery strategy. In general, the purpose of a backup and recovery strategy is to protect the database against data loss and reconstruct the database after data loss. Typically, backup administration tasks include the following:
·         Planning and testing responses to different kinds of failures
·         Configuring the database environment for backup and recovery
·         Setting up a backup schedule
·         Monitoring the backup and recovery environment
·         Troubleshooting backup problems
·         Recovering from data loss if the need arises
As a backup administrator, you may also be asked to perform other duties that are related to backup and recovery:
·         Data preservation, which involves creating a database copy for long-term storage
·         Data transfer, which involves moving data from one database or one host to another
The purpose of this manual is to explain how to perform the preceding tasks.


Using RMAN Incremental Backups

The primary reasons for making incremental backups parts of your strategy are:
·         Faster daily backups if block change tracking is enabled (see "Using Block Change Tracking to Improve Incremental Backup Performance")
·         Ability to roll forward datafile image copies, thereby reducing recovery time and avoiding repeated full backups.
·         Less bandwidth consumption when backing up over a network.
·         Improved performance when the aggregate tape bandwidth for tape write I/Os is much less than the aggregate disk bandwidth for disk read I/Os.
·         Possibility of recovering changes to objects created with the NOLOGGING option.
For example, direct load inserts do not create redo log entries, so their changes cannot be reproduced with media recovery. Direct load inserts do change data blocks, however, and these blocks are captured by incremental backups.
·         Synchronize a physical standby database with the primary database. You can use the RMAN BACKUP INCREMENTAL FROM SCN command to create a backup on the primary database that starts at the current SCN of the standby database, which you can then use to roll forward the standby database.


Multilevel Incremental Backups

RMAN can create multilevel incremental backups. Each incremental level is denoted by a value of 0 or 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data. You can create a level 0 database backup as backup sets or image copies.
The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy. Thus, an incremental level 0 backup is a full backup that happens to be the parent of incremental backups whose level is greater than 0.
A level 1 incremental backup can be either of the following types:
Incremental backups are differential by default.

  1. Create the necessary directories for the RMAN backup files in a strategic location

The recommended location for the RMAN backup files is : /oracle/flash_recovery_area/SID

As the ORACLE user, create the following directories inside this location using the mkdir command. Follow the block diagram below as a reference:



                RMAN Backup files in each location:

                archivelog              location of the archive logs generated by the database instance
                autobackup           location of the controlfile autobackup generated by RMAN
                backupsets            contains sub-directories level0 and level1 folders
                level0                     holds the incremental RMAN  level 0 backup pieces
                level1                     holds the incremental RMAN  level 1 backup pieces
                trace                       contains sub-directories controltrc and pfile folders
controltrc            holds the controlfile SQL trace file
pfiletrc                 holds the textfile snapshot of the currecnt spfile
logs                         location of the RMAN logs generated every backup execution



Sample Deployment:

[oracle@gabserv01]$ mkdir /oracle/flash_recovery_area/ORADB
[oracle@gabserv01]$ cd /oracle/flash_recovery_area/ORADB
[oracle@gabserv01 /oracle/flash_recovery_area/ORADB]$ mkdir archivelog  autobackup  backupsets  logs  trace
[oracle@gabserv01]$ cd backupsets
[oracle@gabserv01 backupsets]$ mkdir level0 level1
[oracle@gabserv01 /oracle/flash_recovery_area/ORADB]$ cd trace
[oracle@gabserv01 backupsets]$ mkdir pfiletrc controltrc


  1. Change the database parameters required for RMAN backups

Estimate the database size. It is recommended that the recovery area size is three (3) times the size of the database segment.

SQL> select sum(bytes/1024/1024/1024) “DB Size in GB” from dba_segments;

Check if the database is using an spfile or a pfile, as SYSDBA:

SQL> show parameter spfile;

If the database uses an spfile, a value will be returned, otherwise, the server uses a pfile. Altering the database parameter can be done dynamically whe spfile is being used. It is always recommended to use an spfile over pfile but if the management requires to keep using a pfile, then adjust the required parameters in the pfile using text editor.

If the server users an spfile:

SQL> alter system set db_recovery_file_dest = ’/oracle/flash_recovery_area‘ scope=spfile;
SQL> alter system set db_recovery_file_dest_size = (3x the value of the database size)G scope=spfile;
SQL> alter system set log_archive_format = %t_%s_%r.arch
SQL> alter system set log_archive_dest_1=’location=/oracle/flash_recovery_area/SID/archivelog’
          scope=spfile;
           
            Restart the database:

                SQL> shutdown immediate;
                SQL> startup;

If the server users a pfile:

Locate the initSID.ora, usually under $ORACLE_HOME/dbs and add the following lines in the file:

*. db_recovery_file_dest = ’/oracle/flash_recovery_area‘
*. db_recovery_file_dest_size = (3x the value of the database size in bytes)
*. set log_archive_format = %t_%s_%r.arch
*. log_archive_dest_1=’location=/oracle/flash_recovery_area/SID/archivelog’

Restart the database:

SQL> shutdown immediate;
SQL> startup pfile=’$ORACLE_HOME/dbs/initSID.ora’;

Connect to RMAN and change the following attributes as shown below:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
                ‘/oracle/flash_recovery_area/SID/autobackup/%F’;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

  1. Switch the database to archiving mode

Follow the steps below to change the database to archivelog mode

(Single Instance Database)

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Verify the change:

SQL> select NAME, LOG_MODE from v$database;

NAME      LOG_MODE
--------- ------------
SID       ARCHIVELOG

(RAC Database 11g and newer versions)

[oracle$] srvctl stop database -d DB_NAME

From any instance:
[oracle@gabserv01-node01] sqlplus / as sysdba
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> SHUTDOWN IMMEDIATE;

[oracle$] srvctl start database -d DB_NAME

NOTE: No need to set the parameter cluster_database to false then revert it to true since this is 11g.



  1. Deploy the following scripts in the location: /oracle/flash_recovery_area/scripts

rman-level0.sh

#!/bin/sh
### Load the environment variable of Oracle Database to be Backed-Up
. ~/.bash_profile ##--> this value should be changed depending on the shell

ORACLE_SID=$1
export ORACLE_SID

### Set the FLASH_RECOVERY_AREA variable
### Note: Change this value if this script will be copied to another machine
FRA_HOME=/oracle/flash_recovery_area
export FRA_HOME

echo -e "\033[1mThe environment for $1 has been loaded \033[0m"
echo -e "\033[1mPerform RMAN Incremental Level 0 Database Backup for $1 \033[0m"
echo -e "\033[1mConnecting to SQL*Plus... \033[0m"
echo -e "\033[1mConnected to database instance: $1 \033[0m"


### Uncomment the “cp” command below for instances that are not using SPFILE.
### cp $ORACLE_HOME/dbs/init$1.ora $FRA_HOME/$1/trace/pfiletrc/init$1-$(date +%Y%m%d-%H%M).ora


$ORACLE_HOME/bin/sqlplus "/ as sysdba" @$FRA_HOME/scripts/trace-ctl-spfile.sql

### Connect to RMAN
$ORACLE_HOME/bin/rman <<EOF
connect target

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$FRA_HOME/$1/backupsets/level0/level0-%d-%T-%U';


SHOW ALL;
REPORT SCHEMA;
LIST BACKUP OF DATABASE;
REPORT NEED BACKUP;
REPORT UNRECOVERABLE;
LIST EXPIRED BACKUP BY FILE;
LIST ARCHIVELOG ALL;
REPORT OBSOLETE;
CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK COPY OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK COMPLETED BEFORE 'SYSDATE-14';

RUN {
sql "alter system archive log current";
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
RESTORE VALIDATE CHECK LOGICAL DATABASE;
}

EOF
exit


rman-level1.sh

#!/bin/sh
### Load the environment variable of Oracle Database to be Backed-Up
. ~/.bash_profile ##--> this value should be changed depending on the shell

ORACLE_SID=$1
export ORACLE_SID

### Set the FLASH_RECOVERY_AREA variable
### Note: Change this value if this script will be copied to another machine
FRA_HOME=/oracle/flash_recovery_area
export FRA_HOME

echo -e "\033[1mThe environment for $1 has been loaded \033[0m"
echo -e "\033[1mPerform RMAN Incremental Level 1 Database Backup for $1 \033[0m"
echo -e "\033[1mConnecting to SQL*Plus... \033[0m"
echo -e "\033[1mConnected to database instance: $1 \033[0m"

### Uncomment the “cp” command below for instances that are not using SPFILE.
### cp $ORACLE_HOME/dbs/init$1.ora $FRA_HOME/$1/trace/pfiletrc/init$1-$(date +%Y%m%d-%H%M).ora


$ORACLE_HOME/bin/sqlplus "/ as sysdba" @$FRA_HOME/scripts/trace-ctl-spfile.sql

### Connect to RMAN
$ORACLE_HOME/bin/rman <<EOF
connect target

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$FRA_HOME/$1/backupsets/level1/level1-%d-%T-%U';


SHOW ALL;
REPORT SCHEMA;
LIST BACKUP OF DATABASE;
REPORT NEED BACKUP;
REPORT UNRECOVERABLE;
LIST EXPIRED BACKUP BY FILE;
LIST ARCHIVELOG ALL;
REPORT OBSOLETE;
CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK COPY OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK COMPLETED BEFORE 'SYSDATE-14';

RUN {
sql "alter system archive log current";
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;
RESTORE VALIDATE CHECK LOGICAL DATABASE;
}

EOF
Exit



trace-ctl-spfile.sql

--##This SQL*PLus script captures the current CONTROLFILE and SPFILE of the Database##--
set lines 500
spool $FRA_HOME/$ORACLE_SID/trace/backup_CTL-SPFILE.sql
select
        'create pfile=''$FRA_HOME/$ORACLE_SID/trace/pfiletrc/'||NAME||'-'||RESETLOGS_CHANGE#||'-'||CURRENT_SCN||'.oratrc'||''''|| ' from spfile;'
from v$database;

select
'alter database backup controlfile to trace as ''$FRA_HOME/$ORACLE_SID/trace/controltrc/'||NAME||'-'||RESETLOGS_CHANGE#||'-'||CURRENT_SCN||'.ctltrc'||''''||
';'
from v$database;

spool off;
@$FRA_HOME/$ORACLE_SID/trace/backup_CTL-SPFILE.sql
exit;


  1.  Make the shell scripts executable and schedule the backup job

As ROOT user:
[root@gabserv01]# chmod +x /oracle/flash_recovery_area/scripts/rman-level0.sh rman-level1.sh

As ORACLE user:
[oracle@gabserv01]$ crontab –e

Then add the following lines:

###------SID-------###

#PERFORM RMAN INCREMENTAL LEVEL 0 BACKUP FOR EAMPRD01 AT 12:00AM EVERY SUNDAY
#00 00 * * 0 /oracle/flash_recovery_area/SCRIPTS/rman-level0.sh sid >
/oracle/flash_recovery_area/SID/logs/SID-LVL0-$(date +%Y%m%d-%H%M).log

#PERFORM RMAN INCREMENTAL LEVEL 1 BACKUP FOR EAMPRD01 AT 12:00AM FROM MONDAY TO SATURDAY
#00 00 * * 1-6 /oracle/flash_recovery_area/SCRIPTS/rman-level1.sh sid >
/oracle/flash_recovery_area/SID/logs/SID-LVL1-$(date +%Y%m%d-%H%M).log





  1.  Basic RMAN monitoring and administration

Checking and interpreting logs generated by RMAN

RMAN backup logs are located inside: $FRA_HOME/SID/logs directory. The RMAN log files are named according to the incremental backup job and the localhost timestamp.
Level 0 backups are named according to the pattern SID-LVL0-YYYYMMDD-HH24MI.log while Level 1 backups are named SID-LVL0-YYYYMMDD-HH24MI.log.

Example:
Level 0 RMAN log file:        ORADB-LVL0-20120205-0145.log
Level 1 RMAN log file:        ORADB-LVL1-20120206-0130.log
To ensure that each backup job completes successfully, check the log file for significant messages and errors. Use the following methods:

[oracle@gabserv01] $ cat ORADB-LVL0-20120205-0145.log | grep –i “ORA-“
[oracle@gabserv01] $ cat ORADB-LVL0-20120205-0145.log | grep –i “RMAN-“

The command should proceed to next prompt and should not have any output in the screen. Otherwise, investigate the log more and check the details of the error.

The following messages must be present at the end of each RMAN log file:

…
…
Finished backup at “DATE”
…
…
Finished Control File and SPFILE autobackup at “DATE”
…
…
Finished Restore at “DATE”
…
…
Recovery Manager Complete.




  1.  Troubleshooting

The usual errors that operators may encounter in incremental backups are the following:

    1. Disk Space shortage in the recovery area
    2. Unable to backup datafiles due to block corruption

Disk space shortage is easier to address: either add more storage in the recovery area or minimize the time in the recovery window. The most common issue in incremental backups is the exhaustion of the disk space in the recovery area because of the backup pieces, which are outside the recovery window/retention policy, are not automatically deleted by the RMAN backup job. This is caused by failed Incremental Level 0 backups.

If disk space is at risk, refer to the table as reference:




Current Date is: Day 22 
Current Space Available: 1GB
Retention Policy: 14 Days
Backups in RED: Obsolete
                Backup in BLUE: Failed Backup

1.       Check the current retention policy (which is set to 14 days)
2.       Check the last two Level 0 backup logs, make sure that there is no error and the backup pieces are available.
3.       If the last two Level 0 backups are complete and reliable, check the Level 1 backup logs at least from the present date until the most recent Level 0 backup or at most until the Level 0 of the past week
4.       Once verified that the database still has a good backup (either one or two copies of Level 0 with complete Level 1 backup), obsolete backups can be deleted using OS commands.
5.       Once deleted, run the following commands inside RMAN:

SHOW ALL;
REPORT SCHEMA;
LIST BACKUP OF DATABASE;
REPORT NEED BACKUP;
REPORT UNRECOVERABLE;
LIST EXPIRED BACKUP BY FILE;
LIST ARCHIVELOG ALL;
REPORT OBSOLETE;
CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK COPY OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK COMPLETED BEFORE 'SYSDATE-14';




            Example:

                As shown in the block diagram above and scenario above:

                Here is the best solution:
               
    1. Check the Day 15 Level 0 backup and make sure it is available and good.
    2. Check all the Level 1 backups from Day 16 to Day 21. Make sure they are available and good.
    3. Make sure all archivelogs are available between Day 21 Level 1 backup and Day 22 Level 0 pending backup.
    4. Delete all backups from Day 1 to Day 14 (either by RMAN (recommended) or OS commands)
    5. Perform Day 22 Level 0 backup.



Comments

Post a Comment

Popular posts from this blog

RMAN Restoration to New Server with Different Directory and New Database Name

In this case, I have an existing production database with RMAN Level 0 backup: CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\flash_recovery_area\galaxy\backupsets\autobackup\SAMSUNG-ctl_%F'; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\flash_recovery_area\galaxy\backupsets\level0\level0-%d-%T-%U' MAXPIECESIZE 4G; DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 7'; SHOW ALL; REPORT SCHEMA; LIST BACKUP OF DATABASE; REPORT NEED BACKUP; REPORT UNRECOVERABLE; LIST EXPIRED BACKUP BY FILE; LIST ARCHIVELOG ALL; REPORT OBSOLETE; CROSSCHECK BACKUP DEVICE TYPE DISK; CROSSCHECK COPY OF ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK; DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK; RUN { sql 'alter system archive log current'; BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT; } LIST BACKUP
Read more

[Script] Tablespace Usage Alert

Hi, This is another variation of the previous scripts presented in this blog site. The building blocks of this script is the same as the Disk Space and ASM Diskgroup Space Monitoring scripts. This will alert you via email if a certain tablespace  reaches the set threshold. Let's share and make others love Oracle, too. For tablespace monitoring alert scripts. We have the following: Tablespace Monitoring Script You will notice that there is a pattern in the scripts creation presented in this blog.  Part 1: Report Generation : Here it is the tablespace report Part 2: Report output string process : Scripts are created to process further report into columns. Conditional statement was created to test the values in the report. Part 3: Check if the file generates alert : Email is sent if entities met the threshold. # # # # # #----- Start of Script -----###### #!/bin/bash  # Report tablespace usage  sqlplus "/ as sysdba" << EOF SET LINES 700 SET
Read more

[Script] ASM Diskgroup Space Usage Alert

Hi Again, Another important task that a DBA should do is to monitor the ASM diskgroup usage. We do not just add space to tablespaces, create more tablespaces or resize datafiles to increase storage without checking if there is space to our ASM diskgroup. Also, when we perform RMAN backups stored in ASM or Datapump Exports stored in ASM, we have to monitor and get alerts if we are running out of space. This is a simple monitoring script. I hope this could help you. Let's share and make others love Oracle, too. ASM Diskgroup  Space Monitoring ######--- Start of Script -----###### #!/bin/bash  # Report ASM Diskgroup space usage  #NOTES:  # 1. There is really a slash on the v$asm_diskgroup so Unix interpreter will treat dollar sign as a literal $ # 2. Set your tablespace threshold by changing the value of the AND clause  " >= 80" sqlplus "/ as sysdba" << EOF SET LINES 700 SET FEEDBACK OFF SET TRIMSPOOL ON SET ECHO OFF SPOOL /tmp/ASMs
Read more