We do full online backups of all databases each night and have a database that takes 1 hour to back up right after the database is restarted, but over time, the backup goes to 2 hours or more. We use the after image archiver to archive the database logs every half hour. Other databases don't seem to have this issue. Does anyone know of factors that affect the backup speed and what can be done to improve it? Thank you, David Rochford
Contact PSC TS and see if this is the "long redo" problem.
When you restart the database, do you truncate the BI file at the same time? If so, potentially the BI is growing steadily and that is what is taking the extra time to run the backup. Have you compared the logs (where it tells you the number of BI blocks that are going to be dumped)?
When the databases are shutdown, we do truncate the BI file(s). I looked at when the backups were running slower and I see 256 bi blocks for one of the databases in question, however when the backup ran 4 hours quicker (after the databases were restarted), I see the same 256 bi blocks will be dumped.
Truncating the BI is not necessary except for special maintenance functions where the function will tell you that it is required or if some abnormal condition results in excessive BI growth and you want to return to a more reasonable level. When you do a BI truncation, you should use bigrow before restarting so that the users are not paying the penalty of re-extending the BI.
Are you using on-line backup because the database is being put back into production? If so, is this database being heavily used during the backup period?
Recognize that an on-line backup does a snapshot of the BI at the start, which can take a while, depending, but which should be fast if you have just done a stop and restart, and then as it does the backup and other users come along doing work, it needs to capture any area which new user activity is going to impact *before* the user modifies it since the backup is as of the point when the backup started. If you fire off a nighly job stack or some such and beat up on the database, the backup can easily get very busy running around backing up fragments here and there, thus slowing its systematic sequential backup process.
The databases are not heavily used during the backup period, however a few do have some nightly routines. At times some users are working later and require access when the backups start at 7 PM. Can anything be done without taking the databases down to speed up the process?
One customer of mine who has similar issues found that they could bring the system down, do an offline backup to disk, bring the database back up, start night time processing, and roll the disk image out to tape in quite a short period of time, while the online backup took hours. This was back in 9.1, so YMMV. But, you have to figure that if you start work while an online backup is running that there is going to be a lot of competition.