Re: rman backup slow
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: Prasad <p4cldba@xxxxxxxxx>
- Date: Sun, 1 Feb 2009 16:40:57 -0800 (PST)
By two different disks I mean two different physical devices. If you are using
a SAN with a large number of disks, then this may not be an issue. Just want to
distribute the physical IO across more disks.
You can look at the V$BACKUP_SYNC_IO or V$BACKUP_ASYNC_IO views (I just checked
to see if they were available in 9r2, they are) can give you information on the
IO throughput, etc...
Might check those views and see what your IO looks like.
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: 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@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:
