Re: rman backup slow
- From: Prasad <p4cldba@xxxxxxxxx>
- To: Robert Freeman <robertgfreeman@xxxxxxxxx>
- Date: Sun, 1 Feb 2009 13:51:58 -0800
Robert,
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.
Thanks
Pk
On Sat, Jan 31, 2009 at 10:51 PM, Robert Freeman
<robertgfreeman@xxxxxxxxx>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?
>
> RF
>
> Robert G. Freeman
> Author:
> OCP: Oracle Database 11g Administrator Certified Professional Study Guide
> (Sybex)
> 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@xxxxxxxxx>
> *To:* Robert Freeman <robertgfreeman@xxxxxxxxx>
> *Cc:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
> *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.
>
> thanks
> Pk
>
> On Sat, Jan 31, 2009 at 4:42 PM, Robert Freeman
> <robertgfreeman@xxxxxxxxx>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.
>>
>>
>> RF
>>
>> Robert G. Freeman
>> Author:
>> OCP: Oracle Database 11g Administrator Certified Professional Study Guide
>> (Sybex)
>> 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@xxxxxxxxx>
>> *To:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
>> *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 .
>>
>> thanks
>> Prasad
>>
>>
>>>
>>>
>>> On Jan 30, 2009, at 1:10, Prasad <p4cldba@xxxxxxxxx> wrote:
>>>
>>> All,
>>>
>>> we have a 170G db on 9.2.0.7(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 .
>>>
>>> SELECT
>>> TO_CHAR(SYSDATE,:"SYS_B_0",:"SYS_B_1"),TO_CHAR(SYSDATE,:"SYS_B_2",:"SYS_B_3"),
>>> TO_CHAR(SYSDATE,:"SYS_B_4",:"SYS_B_5") FROM X$DUAL
>>>
>>> anyone has any similar situation . appreciate any suggestion.
>>>
>>> Thanks in advance
>>>
>>> thanks
>>> Prasad
>>>
>>>
>>>
>>>
>>
>>
>
Other related posts:
