RE: export import Speed up

Folks

We need to move a Big Production Database of 600 GB from Oracle 8.1.7 on Solaris 5.6 TO Oracle 9.2 on IBM-AIX using export/import with MIN Downtime.

How can expdp/impdp be enabled/made available for the above versions?

Max/Optimal RECORDLENGTH value to be used in exp/imp? Is it 64K for Unix?

Max/Optimal BUFFER value to be used in exp/imp on Unix Flavours?

STATISTICS value in 8i during "exp"?

Any Other Tips?

Any Docs, Links, best practices on the same?

Great info below too

Thanks indeed

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Thursday, June 09, 2005 1:25 AM
Subject: Re: ** import tuning

There is so called "DP" option to imp/exp. By using impdp/expdp you can

double your pleasure and double your fun. It is a bit version specific, but other then that,

it does use PQO and parallel DML, which means that it is much, much faster then without the "turbo". The

fun part is that export file with turbo option is in XML format, so you can do many things with it.

These "DP" options are priceless, so you don't even have to use your MasterCard(TM)

Mladen Gogala

Oracle DBA

Ext. 121

From: [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Marquez, Chris

Sent: Thursday, July 21, 2005 8:47 PM

Subject: RE: Huge import takes a long time

...

...

Faster IMPORTS

Set IMP parameter COMMIT = Y.

Set IMP parameter RECORDLENGTH >= EXP RECORDLENGTH value.

Set IMP parameter BUFFER = MB (Set in the MB range not KB range)

Set IMP parameter STATISTICS = NONE (9i, n/a 8i)

Set IMP parameter RECALCULATE_STATISTICS = N. (8i, n/a 9i)

Set IMP parameter ANALYZE = N. (8i, n/a 9i)

Set IMP parameter INDEXES = N (Import them later...separately)

Set init.ora parameter LOG_ARCHIVE_START = FALSE *&* alter database noarchivelog;

Set init.ora parameter _disable_logging = TRUE (Warning, this could be dangerous and unrecoverable in failure. Backup immediately after import - * Redo records (to disk) will NOT be generated (redo WAS generated in the log buffer.) [Even with _disable_logging you still "switch" logs when they get "full"])

Set init.ora parameter _wait_for_sync = FALSE (Warning, this could be dangerous and unrecoverable in failure. Backup immediately after import - _wait_for_sync: Wait_for_sync is an oracle generic parameter which, when set to false, will allow the system to complete commits without waiting for the redo-log buffer flushes to complete.)

Make redo logs enormous; 500MB, 1GB, etc.

Use Locally Managed Tablespaces on target database.

Chris Marquez

Oracle DBA

C-(703)507-1421

cmarquez_at_capwiz.com

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** -- http://www.freelists.org/webpage/oracle-l