28 | 03 | 2024
Latest Articles
Popular Articles

RMAN

RMAN Backup scenario

User Rating:  / 4
PoorBest 

Backup Scenario

 

For database backups I often use the below scenario

1. Archivelogs are generated in the flash recovery area

2. I use RMAN 10G compression for my backupsets

3. I weekly take a full backup ( level 0 )

4. I daily take an incremental backup ( level 1 )

5. I use block change tracking in order to speed up the incremental backups

6. I often backup first to disk with RMAN afterwards the backupsets are put on a tape device.

7. I often backup towards the flash recovery area, unless the flash recovery area is on a ASM diskgroup. If ASM is in use I use RMAN to backup out of ASM towards a traditional logical volume. Afterwards the backupsets are put on a tape device.

With the below commands I show the archivelogs are generated towards the flash recovery area which is on an ASM diskgroup and that there are 189 archived redo logs in the flash recovery area. Furthermore we notice block change tracking has been enabled and that the block change tracking file is located in ASM diskgroup DG5

 

Update Nov 2012 :

Note that this is a scenario I implemented about 6 years ago. In between I have together with the team I am working with rolled out several similar but slightly different scenario' s. If you think to find something useful here then the advise remains that you should always test your backups and recovery (skills) out to verify if it really fits your needs and if it is what you were looking for and this for your environment.

 

See also Why RMAN

 

 
SQL> show parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION="USE_DB_RECOVERY_FILE
                                                 _DEST", valid_for=(ONLINE_LOGF
                                                 ILE,ALL_ROLES)
log_archive_dest_10                  string

SQL> show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_recovery_file_dest                string      +DG1
db_recovery_file_dest_size           big integer 140G

SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                 53.1                       2.1             189
BACKUPPIECE                 .87                         0              22
IMAGECOPY                     0                         0               0
FLASHBACKLOG               6.53                         0             585
SQL> select * from v$block_change_tracking;
STATUS
----------
FILENAME
----------------------------------------------------
     BYTES
----------
ENABLED
+DG5/mydb/changetracking/ctf.330.639306495
  44138496

 

The RMAN persistent settings show us that

1. I backup to disk towards the logical volume /opt/oradata/MYDB/backup

2. The maximum backup piece size is 20G

3. The defined retention period is 1

4. Archivelogs are not automatically purged once backed up

 

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/opt/oradata/MYDB/backup/%U';
CONFIGURE MAXSETSIZE TO 20 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/ORA_HOME/dbs/snapcf_MYDB1.f'; # default

Here I show you the backup script I use once a week for the full backup of the database. We notice

1. I redefine parallellism and the maximum backuppiece size, in order to override what could have been changed "by accident"

2. I crosscheck to verify whether there is not a mismatch between what physically exist and what logically is supposed to exist.

3. If there is a mismatch I delete the logical entries with the delete expired command

4. I use rman' s backup compression

5. I do not backup read only tablespaces

6. I backup the archivelogs but I do not delete them ( they are however automatically flagged as purgable )

7. I delete old backup sets

8. I backup current controlfile and current spfile

Important is that in the beginning of the script I put the auto controlfile backup feature off and only at the very end of the script once I backed the current controlfile out of ASM I put the auto controlfile backup feature back to on. this is done in order to be sure that the controlfile copy I make during the backup Is the last one and won' t as such never be deleted by the automatically purge triggered by the command "delete noprompt obsolete"

run {
configure controlfile autobackup off;
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE MAXSETSIZE TO 20G;
crosscheck backupset;
crosscheck archivelog all;
delete expired backupset;
delete expired archivelog all;
backup incremental level 0 device type disk as compressed backupset database skip readonly;
backup device type disk as compressed backupset archivelog all;
delete noprompt obsolete device type disk;
backup current controlfile;
backup spfile;
configure controlfile autobackup on;
}

The script for the daily incremental backups does not differ a lot.

run {
configure controlfile autobackup off;
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE MAXSETSIZE TO 20G;
crosscheck backupset;
crosscheck archivelog all;
delete expired backupset;
delete expired archivelog all;
backup incremental level 1 device type disk as compressed backupset database skip readonly;
backup device type disk as compressed backupset archivelog all;
delete noprompt obsolete device type disk;
backup current controlfile;
backup spfile;
configure controlfile autobackup on;
}

And here is the script I have scheduled twice a day to backup the archived redo logs. Note I never delete archived redo logs, I have them automatically deleted by Oracle ( feature only available if archivelogs are generated in the flash recovery area ) The archivelogs are automatically deleted with respect to the defined archivelog deletion policy. One of my favourite 10G features is the wonderful configure archivelog deletion policy to applied on standby. More about that later.

run {
configure controlfile autobackup off;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
crosscheck archivelog all;
delete expired archivelog all;
backup device type disk as compressed backupset archivelog all;
delete noprompt obsolete device type disk;
backup current controlfile;
backup spfile;
configure controlfile autobackup on;
}