Home » RDBMS Server » Backup & Recovery » Oracle Backup with RMAN take a long time (Oracle 11g RMAN)
icon14.gif  Oracle Backup with RMAN take a long time [message #661896] Wed, 05 April 2017 12:48 Go to next message
carlino70
Messages: 7
Registered: April 2012
Junior Member

Experts, 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

Please help me to find the key to decreasing the time taken. Thanks

The lines bellow, are a portion of main script called backup.pl. 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

Thanks in advanced
Re: Oracle Backup with RMAN take a long time [message #661897 is a reply to message #661896] Wed, 05 April 2017 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If RMAN competes with DML for the disk IO you should first investigate in IO load/bandwidth.
And you should obviously not backup in the same disks (and even same controller) than the database disks.

Re: Oracle Backup with RMAN take a long time [message #661898 is a reply to message #661896] Wed, 05 April 2017 12:57 Go to previous messageGo to next message
John Watson
Messages: 7184
Registered: January 2010
Location: Global Village
Senior Member
02:48:48 to backup a 92GB database is terrible. You need to determine if the time is in reading the datafiles or writing the backup set. If you can run BACKUP VALIDATE DATABASE that will tell you how long it takes to do the read, so subtraction will you how long it takes to do the write.

As a quick fix, if you have Enterprise Edition licences you could enable block change tracking.


Re: Oracle Backup with RMAN take a long time [message #661899 is a reply to message #661897] Wed, 05 April 2017 13:28 Go to previous messageGo to next message
carlino70
Messages: 7
Registered: April 2012
Junior Member

John Watson:
The license is for "Standard Edition". I'll try run the "BACKUP VALIDATE DATABASE" option, when I retry a new test.



Michel Cadot:
Unfortunately, a storage MSA2000 is shared among Database Files and Backup/Rman files. But in different partitions and shadow files.

Re: Oracle Backup with RMAN take a long time [message #661900 is a reply to message #661899] Wed, 05 April 2017 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Check each disk load at OS level and, if possible, at disk array level (see with your storage admin).

Re: Oracle Backup with RMAN take a long time [message #661901 is a reply to message #661899] Wed, 05 April 2017 13:38 Go to previous messageGo to next message
carlino70
Messages: 7
Registered: April 2012
Junior Member

Sorry by the omition, thanks both.
Re: Oracle Backup with RMAN take a long time [message #661929 is a reply to message #661899] Thu, 06 April 2017 06:40 Go to previous message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
carlino70 wrote on Wed, 05 April 2017 13:28



Michel Cadot:
Unfortunately, a storage MSA2000 is shared among Database Files and Backup/Rman files. But in different partitions and shadow files.

So certain hardware failures put you at risk of losing both the database AND the backup at the same time. And that is exacerbated by the fact that you set your recovery window to only one day.
Previous Topic: Recovery after incremental backup
Next Topic: Error ORA - 19870 error while restoring backup piece ...
Goto Forum:
  


Current Time: Thu Dec 14 08:00:06 CST 2017

Total time taken to generate the page: 0.01691 seconds