Lengthening backup

From: Terry Sutton <terrysutton_at_usa.net>
Date: Fri, 7 Jul 2006 11:05:14 -0700
Message-ID: <1f8101c6a1f0$3c564eb0$6500a8c0@TerrySutton>

I'm having some issues with a client's RMAN backup. They're on Oracle 8.1.7.4, Solaris 8. It's a weekly full backup, and they're backing up ~1.5TB to disk, and the longer the instance is up, the longer the backup takes. A recent progression has been:

Week 1: 15 hours
Week 2: 16 hours
Week 3: 19 hours
Week 4: 21 hours
Week 5: 25 hours

...
Week 15: 48 hours

But when we bounce the database, the length of time to backup goes back to ~15 hours. The database size fluctuates a bit, with a bit of an upward trend, but not with a significant change in size.

One time, when the backup had been running for 24.5 hours, I checked the session stats for the session on the database which is performing the backup, and these were the stats:

 Sess                                          Total   Total Time (hs) Avg (hs)
   ID Wait Event                               Waits Timouts    Waited     Wait


----- ------------------------------------- -------- ------- --------- --------

  436 CPU time                                                 4157966
  436 SQL*Net message from client               9165       0    471612       51
  436 enqueue                                   1226     238     89874       73
  436 rdbms ipc reply                           4767     219     52585       11
  436 db file sequential read                   2782       0      5621        2
  436 latch free                                  95      75      3837       40
  436 control file parallel write               6519       0      1865        0
  436 control file sequential read            114550       0      1366        0
  436 db file single write                       915       0       242        0
  436 file open                                10104       0       201        0
  436 file identify                             1830       0        30        0
  436 SQL*Net message to client                 9165       0         2        0

I also checked the stats for the session on the RMAN catalog which is connected from the main database for the backup:
 Sess                                          Total   Total Time (hs) Avg (hs)
   ID Wait Event                               Waits Timouts    Waited     Wait


----- ------------------------------------- -------- ------- --------- --------

   11 SQL*Net message from client               3334       0      4281        1
   11 CPU time                                                     429
   11 db file sequential read                    172       0        82        0
   11 db file scattered read                      69       0        53        1
   11 free buffer waits                            1       0        21       21
   11 SQL*Net message to client                 3335       0         1        0
   11 latch free                                   1       1         1        1
   11 log file sync                                1       0         1        1
   11 SQL*Net break/reset to client                2       0         0        0
   11 SQL*Net more data from client                1       0         0        0
   11 SQL*Net more data to client                 16       0         0        0
   11 file open                                    2       0         0        0

The backup script is:
run {
allocate channel d1 type disk format='/sharevol3/orabkup/DBNAME/db_t%t_s%s_p%p' ;

backup
  tag full_us2
filesperset 1
database include current controlfile;
release channel d1;
}

I don't have experience tuning RMAN backups, so there may be settings which are not optimal, but it seems pretty strange that the time would increase the longer the instance has been up.

Any comments or ideas would be welcome, other than 1) get better hardware or 2) upgrade to new Oracle version. :-)

--Terry

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 07 2006 - 13:05:14 CDT