delay in backing up using RMAN

[Noel Daniel]

I have an RMAN related problem, where all of a sudden all threads backing up take more than an hour to backup a simple file, no larger than a KBs. I have copied an extract from the report which shows that at a certain arbitrary point, the backup takes so much time on all availble threads. During the remainder of the backup, the speeds are okay.

Any experience with a similar issue ?

[Yogeeraj_1]

hi,

are you doing your backup directly to tape or disk?

did the problem repeat itself for several days?

It could be related to your database performance.

what version of Oracle are you using?

revert

kind regards
yogeeraj

[Noel Daniel]

1- Backing up is done to tape.

2- The issues are of an intermittent nature, but very frequent.

3- Oracle 9i

Thanks

[Eric Antunes]

Hi Noel,

Is the CONTENT size of "/finsys/fdata/data/igwd01.dbf" similar to the "/finsys/fdata/data/iscx01.dbf" CONTENT size?

Eric

[Noel Daniel]

Hello,
The answer to your question is:

Status Name Tablespace Size (M) Free (MB) Free %
ONLINE /finsys/fdata/data/igwd01.dbf IGWD 10.000 4.922 49.22
ONLINE /finsys/fdata/data/iscx01.dbf ISCX 10.000 3.398 33.98

Thank you for our interest,

[Eric Antunes]

Hi Noel,

So the difference between those 2 datafiles are 45% and channel dev_2 takes more 30% time than dev_1. I dont't see any problems here...

What I see is that you seem to be using a channel for each datafile which is bad for performance. Can you post the result of "show all" in rman?

Best Regards,

Eric Antunes

[Noel Daniel]

I have attached the full session report.

I have also highlighted whenever a certain file has taken an unaccoutably long time for backing up. There are 2 instances of 50 odd minutes, & 1, 27 minute instance. And when there is a delay all handles which handle backups are delayed. I have marked the fore-mentioned instances in Red.

Thank you,

[Eric Antunes]

Hi Noel,

Are you using the DP default RMAN scripts? I'm asking because those scripts are based on RMAN 8i and don't take full advantage of RMAN 9i...

Best Regards,

Eric Antunes

[Eric Antunes]

Noel,

What I want to say is that you should have several datafiles being backed up in each channel instead of 1 datafile set you have.

See my example:

channel dev_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/disc1/oradata/TST/apps_undots1_01.dbf
input datafile fno=00085 name=/disc1/oradata/TST/transaction_table.dbf
input datafile fno=00093 name=/disc1/oradata/TST/transaction_index_2.dbf
input datafile fno=00107 name=/disc1/oradata/TST/APPS_TS_TX_DATA04.dbf
input datafile fno=00126 name=/disc1/oradata/TST/APPS_TS_INTERFACE02.dbf
input datafile fno=00130 name=/disc1/oradata/TST/APPS_TS_ARCHIVE02.dbf
input datafile fno=00132 name=/disc1/oradata/TST/APPS_TS_MEDIA02.dbf
input datafile fno=00112 name=/disc1/oradata/TST/APPS_TS_TX_DATA09.dbf
input datafile fno=00114 name=/disc1/oradata/TST/APPS_TS_TX_DATA11.dbf
input datafile fno=00004 name=/disc1/oradata/TST/sysaux01.dbf
input datafile fno=00120 name=/disc1/oradata/TST/APPS_TS_TX_IDX10.dbf
input datafile fno=00086 name=/disc1/oradata/TST/eul01.dbf
input datafile fno=00084 name=/disc1/oradata/TST/olap01.dbf
channel dev_1: starting piece 1 at 03/16/2007 [16:53:35]
...
channel dev_1: finished piece 1 at 03/16/2007 [17:10:22]
piece handle=Database & Controlfiles<617388815:1>.dbf comment=API Version 2.0,MMS Version 65.5.50.74
channel dev_1: backup set complete, elapsed time: 00:16:47



So the question is: how did you define the backup specification? What template did you used (if any)?


Cheers,

Eric

[Noel Daniel]

Sorry for the long lay off.

I have copied the rman scripts for the omniback ignited backup, below.

run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PROD,OB2BARLIST=Full_PROD_FRI-1)';
allocate channel 'dev_1' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PROD,OB2BARLIST=Full_PROD_FRI-1)';
allocate channel 'dev_2' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PROD,OB2BARLIST=Full_PROD_FRI-1)';
allocate channel 'dev_3' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=PROD,OB2BARLIST=Full_PROD_FRI-1)';
crosscheck archivelog all ;
backup
incremental level
filesperset 1
format 'Full_PROD_FRI-1.dbf'
database
include current controlfile ;
sql 'alter system switch logfile';
backup
filesperset 1
format 'Full_PROD_FRI-1.dbf'
archivelog all ;
}


Any suggestions as to what we tend to be doing wrong ?

[Eric Antunes]

Hi Noel,

Remove the "filesperset 1" part...

Best Regards,

Eric

[Noel Daniel]

What does the "filesperset" do ?

thanks n' rgds.,

noel

[Yogeeraj_1]

Hi noel,

Filesperset restricts the number of datafiles/archivelogs that should be contained in one backup piece. This is for ease of restore.

Below an important note that you may consider to review before making the changes.

Below a quote from metalink note: 388422.1 - Top 10 Backup and Recovery best practices.

6. Have each datafile in a single backup piece
REASON: When doing a partial restore RMAN must read through the entire piece to get the datafile/archivelog requested. The smaller the backup piece the quicker the restore can complete. This is especially relevent with tape backups of large databases or where the restore is only on individual / few files.

RMAN> backup database filesperset 1 plus archivelog delete input;


hope this helps too!

kind regards
yogeeraj

[Yogeeraj_1]

Hi noel,

Filesperset restricts the number of datafiles/archivelogs that should be contained in one backup piece. This is for ease of restore.

Below an important note that you may consider to review before making the changes.

Below a quote from metalink note: 388422.1 - Top 10 Backup and Recovery best practices.

...
6. Have each datafile in a single backup piece
REASON: When doing a partial restore RMAN must read through the entire piece to get the datafile/archivelog requested. The smaller the backup piece the quicker the restore can complete. This is especially relevent with tape backups of large databases or where the restore is only on individual / few files.

RMAN> backup database filesperset 1 plus archivelog delete input;
...


hope this helps too!

kind regards
yogeeraj

[Eric Antunes]

Hi Noel,

filesperset tells rman how many files (datafiles AND ARCHIVELOGS) will make one backup piece.

PS: thanks Yogeeraj but Noel seems more concerned about backup than restore performance and his full DP session report has 565 pages because of "filesperset 1" with a few archivelogs backed up... ;)

Best Regards,

Eric Antunes