0

After upgrading my DBMS Oracle 10g to 11g, when it will import user files, they are slower than the old version.

A .DMP file in Oracle 10g with the size of 20GB takes almost 1 hour to import, but Oracle 11g version takes almost 3 hours for the same .DMP file.

Export script:

NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss' ; export NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS=',.'; export NLS_NUMERIC_CHARACTERS

exp system/datapar1 file=copy.dmp log=exp.log owner=username \
    buffer=4096000 recordlength =65535 statistics=none \
    feedback=500000 consistent=yes

Import script:

NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'; export NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS=',.'; export NLS_NUMERIC_CHARACTERS

imp system/password file=copy.dmp log=imp.log fromuser=username \
    touser=newusername ignore=yes

Could anyone help me with this?

1

It could be that one of the imp default parameters for Oracle 10G is different in Oracle 11G. I would try adding the

buffer=4096000

parameter to the imp command to see if that helps. That parameter is available on both exp and imp commands.

  • You may also want to check the sizes of the initialization parameters LOG_BUFFER and STREAMS_POOL_SIZE and memory-related SGA values as @Gandolf989 suggested, on the 11G instance. – Mark Stewart Nov 5 '14 at 12:37
1

Do both servers have similar hardware and OS? Is there enough storage space for the 11g database? Have you thought about using data pump instead? With data pump and enterprise edition you can export and import in parallel as well as compress. With 10g you can compress the meta data but not the data. With 11g you can compress the data and meta data. How does the SGA compare between the databases? Are you using AMM on 11g, which isn't available on 10g?

  • 1
    Agree, Data Pump. As Gandolf says, in 11g/12c, you can compress both data and metadata; but beware if you want to compress the data you are required to have Oracle's Advanced Compression pack (not included with Enterprise Edition). – Joshua Huber Nov 4 '14 at 21:23
  • Yes they have similar hardware but not the OS. I'll check this and will try data pump. After doing this I'll give an ok! Thanks! – Thiago Medeiros Nov 5 '14 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.