Full Import Taking Lot of Time

[pareshjavkar]

Dear All,

O.S.: Sun Solaris 5.9
Oracle: 9.2.0.6.0
DBSize (Logical): 27GB
Export Dump Size (FULL=Y): 12GB

Export command:

exp username/password file='/pathname/filename.dmp' log='/pathname/filename.log' full=Y statistics=none compress=n buffer=10000000 feedback=100000

Now, export Backup takes 1 hour or maximum 1.5 hours to complete.

Import command:

imp username/password FILE='/pathname/filename.dmp' LOG='/pathname/imp_filename.log' FULL=y IGNORE=y COMMIT=y BUFFER=20000000 FEEDBACK=100000 statistics=none

Import takes more than 12 hours to complete.

Now, some of the parameters which might put some more light:

SGA size: 1GB
Buffer cache size: 500M
Shared Pool: 300M
Redo Log size: 5 groups (100M each)

Please advise, as to why does importing takes such a longtime ?

Note:
Datafiles/Redo Log Files and export Dump file are all placed on three different partitions.

[davey23uk]

1) dont use exp / imp for backups - you will end up losing data
2) if you must, use indexes=no then create the indexes in parallel

12 hours isnt a long time for 27gb worth of data, seems fine as it is

[ixion]

1. Use IxUnload to extract the data.
2. Use exp/import for structure.

3. Use SQLLDR direct path option and data extracted from step 1.

This will be orders of magnitude faster than export/import.

Regards