Re: rman backup slow

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Sun, 1 Feb 2009 16:47:39 -0800 (PST)
Message-ID: <931610.87833.qm_at_web38901.mail.mud.yahoo.com>



Have you opened an SR with Oracle? Your wait issue might associated with a known bug, so you should check that possibility...

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_at_gmail.com>
To: Robert Freeman <robertgfreeman_at_yahoo.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Sent: Sunday, February 1, 2009 2:51:58 PM Subject: Re: rman backup slow

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_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?

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_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.

thanks
Pk

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.

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_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 .

thanks
Prasad  

On Jan 30, 2009, at 1:10, Prasad <p4cldba_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Feb 01 2009 - 18:47:39 CST

Original text of this message