Skip to main content

How to Improve Oracle Data Pump Performance - IMPDP

 How to Improve Oracle Data Pump Performance:-

  1. Use Parallelism:
    • Set the PARALLEL parameter to at least 2 * number of CPUs. This allows multiple worker processes to perform tasks simultaneously, speeding up both export and import processes.
    • Example: PARALLEL=4 for a system with 2 CPUs.
  1. Perform Import Using NETWORK_LINK:
    • Use the NETWORK_LINK parameter to import data directly from the source database. This method is particularly helpful when space is constrained, as it streams the data without needing to generate dump files on the source.
    • It can also reduce time compared to exporting and then importing, as data is streamed directly from the source to the target database.
  1. Disable Archivelog Mode (Standalone Databases):
    • For standalone databases, temporarily disable archive logging during the import process. Import operations can generate a lot of redo logs, slowing down the import. Disabling archive logging will prevent unnecessary log generation.
  1. Increase UNDO_RETENTION:
    • Increase the UNDO_RETENTION parameter to avoid the ORA-01555 (Snapshot Too Old) error during large imports. A longer retention period ensures that long-running transactions can be handled properly.
  1. Exclude Statistics and Indexes:
    • Excluding statistics and indexes during the import will speed up the process significantly. Statistics and indexes can be recreated after the import is completed.
    • Use the EXCLUDE=STATISTICS and EXCLUDE=INDEX parameters in impdp.
  1. Separate LOB Columns for Import:
    • LOB (Large Object) columns do not support parallelism, and can slow down the import process. For better performance, consider exporting and importing LOB columns separately using a different process.
    • Use the DATA_OPTIONS=LOB_STORAGE parameter to optimize LOB handling

  2. Increase Buffer Size:
    • Increasing the BUFFER size can help reduce the number of I/O operations and speed up the process. This memory comes from the PGA, and each parallel process allocates its own buffer. Make sure the system has enough memory.
    • Example: BUFFER=50000000 (50 MB).
  3. Flashback for Consistent Backups:
    • Use the FLASHBACK_SCN or FLASHBACK_TIME parameters during export to ensure a consistent snapshot of the data. This ensures that the data exported is consistent, even if changes are being made in the source database during the export process.
    • Example: FLASHBACK_SCN=123456789 or FLASHBACK_TIME=SYSTIMESTAMP.
  1. Disable Foreign Key Constraints During Import:
    • If applicable, consider disabling foreign key constraints during the import process. Once the import is complete, you can re-enable them to save time and avoid constraint-related delays.

 

Comments

Post a Comment

Popular posts from this blog

CHANGING DBID FOR ORACLE DATABASE 11G

C:\Users\computer>sqlplus SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 10 21:03:33 2013 Copyright (c) 1982, 2008, Oracle.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select dbid from v$database;       DBID ---------- 2188161033 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Users\computer>nid DBNEWID: Release 11.1.0.7.0 - Production on Thu Sep 10 21:04:44 2013 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Keyword     Description                    (Default) ---------------------------------------------------- TARGET ...
Post a Comment
Read more

Difference between AWR, ADDM and ASH reports

AWR: Automatic Workload Repository gathers, processes and maintains performance stats used for problem detection and self-tuning the databases. Different Components that uses AWR are: Automatic Database Diagnostic Monitor Undo Advisor SQL Tuning Advisor Segment Advisor Different types of AWR Reports for different purposes: For Single Instance Environment: @$ORACLE_HOME/rdbms/admin/awrrpt.sql For Oracle RAC Environment : @$ORACLE_HOME/rdbms/admin/awrgrpt.sql For a particular SQL Statement : @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql For Comparing the reports : @$ORACLE_HOME/rdbms/admin/awrddrpt.sql ADDM: Automatic Database Diagnostic Monitoring Report analyzes the AWR data on a regular basis, to give you overview of the root cause of the problem which is affecting your database’s performance. It also provides suggestions or recommendations for rectifying any problem identified and lists the areas which are having no issues. ADDM recommends multiple solutions for the DBA to choose from which...
Post a Comment
Read more

ORA-28007: THE PASSWORD CANNOT NE REUSED

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. ...
Post a Comment
Read more