Increase speed of import

Hi there,

Currently, our production database produces 1G export dump. It contains some large table which contains 1 million rows.

We need to import the Production Data into development machine for full testing everyday.

However, I think that the import speed is quite slow.

Our development Platform is SUN E420R with 2G RAM.

Import take nearly 2.5 - 3 hours to completely load all data into DB. It is very time-consuming.

Currently, I already used INDEX=N to prevent user index update during import.

BUFFERS is also increased in IMP parameter.

Any other way to increase the speed of import?

Are you doing a regular conventional export or export direct. Export direct would speed up your import. Also have a good size of RBS and set the COMMIT=N, but COMMIT=Y prevents the RBS growing inordinately large.

Sam

You can try DIRECT=Y option with INDEX=N

If the TABLES are same in both PRDN and DEVL, do not drop the entire schema in DEVL DB. Just truncate all tables so that already allocated extents will be reused.

Increase the REDO LOG FILE SIZE to 100MB in DEVL, ensure checkpoint occurs after the redo fills in.
Or to minimize the redo log, before import alter all tables into NOLOGGING mode. This will definitely speed up your import process.

If I use TRUNCATE TABLE instead of DROP USERNAME CASCADE, will it affect other objects like sequences, trigger, constraints ?

Quote:

---

Originally posted by tamilselvan
You can try DIRECT=Y option with INDEX=N

If the TABLES are same in both PRDN and DEVL, do not drop the entire schema in DEVL DB. Just truncate all tables so that already allocated extents will be reused.

Increase the REDO LOG FILE SIZE to 100MB in DEVL, ensure checkpoint occurs after the redo fills in.
Or to minimize the redo log, before import alter all tables into NOLOGGING mode. This will definitely speed up your import process.

---

If you are going to export the entire data every day, then I would suggest that you look into that of doing incremental export. This would help you to save time a lot.

It didn't strike to me during my previous posting.

Sam

Hi,
I read that the truncate command does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations.

Karthi

It is extract of Oracle8i Utilities Manual.

Important: Incremental, cumulative, and complete Exports are obsolete features
that will be phased out in a subsequent release. You should begin now to migrate to
Oracle’s Backup and Recovery Manager for database backups. See Oracle8i
Operating System Backup and Recovery Guide for more information.
Restrictions:

You can do incremental, cumulative, and complete exports only in full
database mode (FULL=Y). Only users who have the role
EXP_FULL_DATABASE can run incremental, cumulative, and complete
Exports.

I think that incremental export is not suitable to my case. It is because I want several schemas in production database only.

Quote:

---

Originally posted by sambavan
If you are going to export the entire data every day, then I would suggest that you look into that of doing incremental export. This would help you to save time a lot.

It didn't strike to me during my previous posting.

Sam

---

We need to import the Production Data into development machine for full testing everyday.

However, I think that the import speed is quite slow.


As the above posting of yours did not specify the mode of exp/imp, I went ahead and gave the suggestion to do the incremental, asuming that you were doing FULL=Y. This incremental and etc, I'm pretty sure that it would be alive on its 8i env. This feature, I believe got started with 8.0.0 and had propagated to 8i. I'm not sure about its life span on other on coming versions. But currently it is available on 8i version for sure.

Sam

Sambavan,
Just a remark: incremental (cumulative...) mode of export was not introduced in 8i nor in 8.0, it was awailable way back, at least from version 6.0. So if it is awailable for mor than 10 years now, you can bet it will be awailable for at least another 10 years (until the release 17j, I suppose ;)).

edwtsang,
If you are on 8i, why don't you explore the "transportable tablespace" option - I think this feature was made exactly for the cases like yours. In essence, you group all the objects in your production database that you want to transfere occasionally to your dev database in one or more "self-contained" tablespace(s). When you wan't to transfer them, you simply put those tablespace(s) in read-onlymode, copy the files to the dev environment and "plug" them into your developement database.

Check the documentation enad technet for details.

HTH,