Re: rman backup slow
Date: Sun, 1 Feb 2009 13:51:58 -0800
I did with both catalog and no catalog mode and in both the case it shows 30,28 minute for the 8G datafile . However when I ran it with optimizer_mode=rule . it did the same file in 24 minutes. but today there is hardly any load on the server so It might not hold true on week day backup.
Yes I did with default parallelism(=1) and somehow I could not see any gain . by the way when you say to try to 2 separate disk . do you mean 2 different san mount point . please let me know and I can surely give it a try.
On Sat, Jan 31, 2009 at 10:51 PM, Robert Freeman <robertgfreeman_at_yahoo.com>wrote:
> If ASYNC IO is not supported then your direction to multiple slaves would
> be correct.
> Did you try the backup of the datafile both connect to the catalog and not
> connected? I'm looking to compare the two.
> Have you tried without parallelism? If your disk can't handle the IO then
> the two channels may just not be enough. Do you have two separate physical
> disks that you could write to at the same time? You may be saturating your
> bandwidth with the two channels?
> Robert G. Freeman
> OCP: Oracle Database 11g Administrator Certified Professional Study Guide
> Oracle Database 11g New Features (Oracle Press)
> Portable DBA: Oracle (Oracle Press)
> Oracle Database 10g New Features (Oracle Press)
> Oracle9i RMAN Backup and Recovery (Oracle Press)
> Oracle9i New Features (Oracle Press)
> Other various titles out of print now...
> Blog: http://robertgfreeman.blogspot.com
> The LDS Church is looking for DBA's. You do have to be a Church member in
> good standing. A lot of kind people write me, concerned I may be breaking
> the law by saying you have to be a Church member. It's legal I promise! :-)
> *From:* Prasad <p4cldba_at_gmail.com>
> *To:* Robert Freeman <robertgfreeman_at_yahoo.com>
> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
> *Sent:* Saturday, January 31, 2009 11:02:31 PM
> *Subject:* Re: rman backup slow
> Hi Robert,
> I did as you suggested with no catalog . it took 30 minutes for a 8GB
> datafile .which
> still doesnot look that good ?
> There is no major db size change in last few quarters . it is just that
> recently it is
> overlapping with the business hours once in a while.
> The backup goes to one disk device . we are using 2 channel . I have yet
> to find out the disk io rate.so far I have been trying from Oracle side to
> see if any improvement can be done.
> The current current vxfs fs does not support async_io . as the way for us
> to make it use
> async io is to use ODM or quick i/o and both are not easy option at this
> time for this 9i database so hoping enabling multiple slave will reduce
> async io wait.
> Pleae let me know if I miss anything.
> On Sat, Jan 31, 2009 at 4:42 PM, Robert Freeman <robertgfreeman_at_yahoo.com>wrote:
>> Do a backup without the catalog. Does it go faster? You can start by just
>> testing the backup of a specific datafile with and without the catalog,
>> rather than the whole database. I'd do one that is big enough to give you a
>> decent backup run time.
>> Has this database gotten bigger over time or is it's size static? Is the
>> increased run time of the backup related to any increase in database size?
>> Are you just backing up to one disk device? Are you using one channel or
>> multiple channels? What is the disk IO rate for writes in ms? Are you
>> getting the throughput you need on your disks.
>> I would not change the parameters you are talking about just yet. It would
>> be much smarter to figure out for sure what is causing your problem before
>> you change lots of parameters and potentially make the problem even worse.
>> I assume that your system does ASYNC IO? Correct? I would not go the SYNC
>> IO path as you are suggesting by setting disk_asynch_io to false.
>> Robert G. Freeman
>> OCP: Oracle Database 11g Administrator Certified Professional Study Guide
>> Oracle Database 11g New Features (Oracle Press)
>> Portable DBA: Oracle (Oracle Press)
>> Oracle Database 10g New Features (Oracle Press)
>> Oracle9i RMAN Backup and Recovery (Oracle Press)
>> Oracle9i New Features (Oracle Press)
>> Other various titles out of print now...
>> Blog: http://robertgfreeman.blogspot.com
>> The LDS Church is looking for DBA's. You do have to be a Church member in
>> good standing. A lot of kind people write me, concerned I may be breaking
>> the law by saying you have to be a Church member. It's legal I promise!
>> *From:* Prasad <p4cldba_at_gmail.com>
>> *To:* ORACLE-L <oracle-l_at_freelists.org>
>> *Sent:* Friday, January 30, 2009 9:45:46 PM
>> *Subject:* Re: rman backup slow
>> my apologies for the late response. To answer this . not it is not a
>> new database . it has been in production since last couple of years. it
>> hosts a application which has lot of LONG RAW data . it is right now 170GB .
>> out of which the LONG RAW table only occupies 80GB. The RMAN backup is
>> taken connected to recover catalog . The backup goes to disk . right now
>> the cursor_sharing is set to similar . The most significant wait I see is
>> disk async i/o . This is a solaris server with vxfs and it do not support
>> async i/o .
>> The puzzling factor is the select on x$dual which just sits silently for
>> hours. and the entire backup duration goes beyond 10 hour .
>> This is what we are planning to do increase the db_writer_process from 1
>> to 4 and set the disk_asynch_io to false ( i gues the default is TRUE) .
>> would appreciate your thoughts on this change .
>> because of the sensitivity of the application ( it is a highly visible
>> production database) it is a bit hard to find time to take a no catalog
>> backup and also do any tracing experiment . but i guess it seems
>> unavoidable .
>> hopefully I didnt miss to provide any information asked. please feel free
>> to let me know if any further information will assist finding solution.
>> Thanks again .
>>> On Jan 30, 2009, at 1:10, Prasad <p4cldba_at_gmail.com> wrote:
>>> we have a 170G db on 18.104.22.168(solaris 9) . and I see that it currently is
>>> taking nearly 10 hour+ to do a rman backup . when I see this in grid
>>> control I see the rman session keeps waiting on this sql .
>>> TO_CHAR(SYSDATE,:"SYS_B_4",:"SYS_B_5") FROM X$DUAL
>>> anyone has any similar situation . appreciate any suggestion.
>>> Thanks in advance