Oracle Backup with RMAN take a long time

Question

I have Oracle Database 11g Release 11.2.0.3.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management.

OS is Linux Red Hat 2.6.18-348.12.1.el5.

Database name: dbname

Database size is approx 92 GB.

The execution of backups using RMAN it is taking about 4 hours 45 minutes to complete the task, and damage the correct execution of other processes that interact with the database.

The tables have so many DML (udpate/insert/delete) every moment every day.

I see in rman log two critics moment:

Starting backup at 04-04-2017 04:33:59
channel dbname_backup_disk1: starting incremental level 1 datafile backup set
channel dbname_backup_disk1: specifying datafile(s) in backup set
input datafile file number=00017 name=file_name ....                                                                        
...   "here all files"                                                                         
channel dbname_backup_disk1: starting piece 1 at 04-04-2017 04:34:00
channel dbname_backup_disk1: finished piece 1 at 04-04-2017 07:22:47
piece handle=+RECOVERY/dbname/backupset/2017_04_04/nnndn1_dbname_level_0_0.2614.940394043 tag=DBNAME_LEVEL_0 comment=NONE
channel dbname_backup_disk1: backup set complete, ***elapsed time: 02:48:48***

and:

Starting backup at 04-04-2017 07:46:20
backup will be obsolete on date 04-07-2017 07:46:20
archived logs required to recover from this backup will expire when this 
backup expires
channel dev1: starting compressed full datafile backup set
channel dev1: starting piece 1 at 04-04-2017 07:46:21
channel dev1: finished piece 1 at 04-04-2017 09:22:07
piece handle=/backup/oracle/backup/DBNAME_940405581_6656_1 tag=TAG20170404T074620 comment=NONE
channel dev1: backup set complete, ***elapsed time: 01:35:46***
Finished backup at 04-04-2017 09:22:07

How can I decrease the time taken?

The lines below are a portion of main script called backup.pl. It is executed every day at 02:30am local (UTC -3), from crontab.

RMAN> run {
    show all;
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
    configure controlfile autobackup on;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+RECOVERY/DBTARGET/%F';
    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '+RECOVERY/DBTARGET/%d_%t_%s_%p';
    allocate channel dbname_backup_disk1 device type disk;
    REPORT OBSOLETE;
    DELETE OBSOLETE;
    recover copy of database with tag 'dbname_LEVEL_0' until time 'sysdate-1';
    backup incremental level 1 cumulative copies=1 for recover of copy with tag 'dbname_LEVEL_0' database include current controlfile;
    backup archivelog all not backed up format '+RECOVERY/DBTARGET/%d_%t_s%s_s%p';
    backup current controlfile for standby;
    delete archivelog until time='sysdate-3';
    release channel dbtarget_backup_disk1;
    sql "create pfile=''/backup/oracle/backup/ros1or01-initdbtarget1-20170404.ora'' from spfile";
    }
    allocate channel for maintenance type disk;
    delete noprompt obsolete device type disk;
    CROSSCHECK BACKUP;
    CROSSCHECK DATAFILECOPY ALL;
    CHANGE ARCHIVELOG ALL CROSSCHECK;
    DELETE EXPIRED ARCHIVELOG ALL;
    REPORT OBSOLETE;
    DELETE OBSOLETE;
    release channel;
    run
    {
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/oracle/backup/%F';
    allocate channel dev1 device type disk format '/backup/oracle/backup/%d_%t_%s_%p';
    backup backupset completed after 'sysdate-3/24';
    backup as compressed backupset datafilecopy all noduplicates KEEP UNTIL TIME 'SYSDATE+3' logs;
    release channel dev1;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+RECOVERY/DBTARGET/%F';
     }
     quit
    using target database control file instead of recovery catalog
    RMAN configuration parameters for database with db_unique_name DBTARGET are:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
    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 '+RECOVERY/DBTARGET/%F';
    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 CHANNEL DEVICE TYPE DISK FORMAT  '+RECOVERY/DBTARGET/%d_%t_%s_%p';
    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 '/cots/oracle/11.2.0/dbhome_1/dbs/snapcf_dbtarget1.f'; # default

Answer 1

Could you please tell me what the 92GB express?

• Is it the backup size?
• Is it the real data size?
• Is it the size of all datafiles?

I suspect that it is the size of the backup or the real data size.

I suspect that you have very fragmented tables. This means, many empty blocks are read from disk. The output looks like very little work but in fact there is much more to do (applys to compressed backup).

Besides that your backup script is crazy! I would recommend to first simplify it and then try to improve performance.

Try the following script. It does more or less the same as yours:

RMAN>
#Do all the CONFIGURE... stuff here if you do not want to rely on the current controlfile config.
# Configure the default channel to disk and configure a format so the output goes to the filesystem not to your FRA (ASM).
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/backup/DBTARGET/%d_%t_%s_%p';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

# Place your controlfile autobackup to disk not asm. In case of a recovery szenario you will be glad to have it in your filesystem.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/DBTARGET/%F';

BACKUP DATABASE;
CROSSCHECK ARCHIVELOG ALL;
BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
run {
  allocate channel d1 device type disk;
  recover copy of database with tag 'dbname_LEVEL_0';
  backup incremental level 1 for recover of copy with tag 'dbname_LEVEL_0' database;
}