Skip to Main Content
  • Questions

  • Export Backup Taking too long(Delay in backup by ten hours)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, pal.

Asked: January 18, 2009 - 4:54 am UTC

Last updated: January 25, 2009 - 11:11 am UTC

Version: 9.2.0.8

Viewed 1000+ times

You Asked

Dear Sir,

I have a problem regarding the oracle nightly export backup. earlier it used to take around 9 hours for 400GB database export backup a particular schema. now it takes around 19 to 20 hours. Could you please suggest technique to tune this particular problem in the database.

palkar

Additional Info:
1) Export run by a user with EXP_FULL_DATABASE privilege only.
2) exp $UIDPWD file=PALK.dmp log=PALK.log owner=PALK statistics=none
3) we will be using rman in future. but export extract will be a daily task.

and Tom said...

Well, doing a daily export of a half terabyte of data is not really a useful use of resources. It is not a backup, it probably isn't very useful day to day and it consumes a TON of resources.

It is likely suffering from severe disk contention - probably on the OUTPUT (writing of the dmp file) to the file system.

You could use direct path export to read a bit faster (but if the problem is output related, that won't fix that, it'll make it worse)

You could parallelize your export, do table level exports all at the same time (but again, if output is your problem, this will make it worse, not better)

I would say the only way to 'fix' this is to abandon export (you'll have to in subsequent releases when EXP goes away...) and just use proper backups. Either user managed ones or RMAN managed ones.

The only way to backup a database is to..... back it up.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Export of the entire database

Antonio, January 20, 2009 - 1:32 pm UTC

We inherited a database server from a merger that did nightly exports also and the performance was just as bad. We implemented RMAN and cut the backup down by 25%. And it's a backup, not an export. Fully recoverable. I say invest the time and implement an RMAN backup.

Pal kar, January 21, 2009 - 2:40 am UTC

The output of export is only 22GB. It is very usefull particulary one or more tables are effected and need to be restored from the backup without the downtime.
Tom Kyte
January 21, 2009 - 1:02 pm UTC

backups can do that as well, without downtime. In fact a backup could actually be used to restore something to what it was six hours ago, since you messed it up six hours ago - whereas that less than useful export could restore it to what it was 18 hours ago, since that was when you ran the export.

export != backup


It seems unlikely that it would take 9 hours to export 22gb of information unless it was full of LOB/LONG data (which goes slow by slow). I even more suspect "contention on the output device" as the culprit.

pal kar, January 25, 2009 - 3:45 am UTC

Like to clarify, 22GB is compressed .dmp.Z file.
Tom Kyte
January 25, 2009 - 11:11 am UTC

everything I've said above holds

export is not a backup tool
backups are a backup tool

do proper backups and save a TON on the IO and CPU resources you are burning to do this entirely inferior export.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.