DATA MOVEMENT: CHALLENGE OR STRENGTH ?

 

Organizations in highly regulated industries go through many business events that trigger the need for a data migration. The typical “lift and shift” approach to data migration, however, results in a considerable amount of time and resource allocation—without any concrete business value to show for it. Adlib goes beyond just automating the process of moving your data between repositories. We add value to your content before, during, and after migration, for enriched content that’s easier to find and easier to use.

Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

In order to use Data Pump, the database administrator must create a directory object and grant privileges to the user on that directory object. You would login to SQL*Plus as system and enter the following SQL command to create a directory. 

1. SQL> CREATE DIRECTORY dpump_dir1 AS ‘/usr/apps/datafiles’; 
2. SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir1 TO scott; 

Once the directory access is granted, the user scott can export his database objects with command arguments: 

3. Run the command

> expdp username/password DIRECTORY=dpump_dir1 dumpfile=scott.dmp 

Data Pump offers much greater metadata filtering than Original Export and Import. The INCLUDE parameter allows you to specify which object (and its dependent objects) you want to keep in the export job. The EXCLUDE parameter allows you to specify which object (and its dependent objects) you want to keep out of the export job. You cannot mix the two parameters in one job. Both parameters work with Data Pump Import as well, and you can use different INCLUDE and EXCLUDE options for different operations on the same dump file.

expdp username/password FULL=y INCLUDE=GRANT INCLUDE= INDEX DIRECTORY=dpump_dir1 DUMPFILE=dba.dmp CONTENT=ALL

 

Moving data between versions 

The Data Pump method for moving data between different database versions is different from the method used by original Export and Import. With original Export, you had to run an older version of Export to produce a dump file that was compatible with an older database version. With Data Pump, you use the current Export version and simply use the VERSION parameter to specify the target database version. You cannot specify versions earlier than Oracle Database 10g (since Data Pump did not exist before 10g). 

Example: > expdp username/password TABLES=hr.employees VERSION=10.1 DIRECTORY=dpump_dir1 DUMPFILE=emp.dmp 

Data Pump Import can always read dump file sets created by older versions of Data Pump Export. 
Note: that Data Pump Import cannot read dump files produced by original Export.

 

MAXIMIZING THE POWER OF ORACLE DATA PUMP
Once you are comfortable with DataPump, there are new capabilities that you will want to explore to improve the performance.

PARALLELISM :
Data Pump Export and Import operations are processed in the database as a Data Pump job, which is much more efficient that the client-side execution of original Export and Import. Now Data Pump operations can take advantage of the server’s parallel processes to read or write multiple data streams simultaneously (PARALLEL is only available in the Enterprise Edition of Oracle Database 10g and only applies to loading table data, not metadata.) 

The number of parallel processes can be changed on the fly using Data Pump’s interactive command-line mode. You may have a certain number of processes running during the day and decide to change that number if more system resources become available at night (or vice versa). For best performance, you should do the following: 

Example:expdp username/password DIRECTORY=dpump_dir1 JOB_NAME=hr DUMPFILE=par_exp%u.dmp PARALLEL=4 

REMAP:
• REMAP_TABLESPACE – This allows you to easily import a table into a different tablespace from which it was originally exported. The databases have to be 10.1 or later. 
• REMAP_DATAFILES – This is a very useful feature when you move databases between platforms that have different file naming conventions. This parameter changes the source datafile name to the target datafile name in all SQL statements where the source datafile is referenced. Because the REMAP_DATAFILE value uses quotation marks, it’s best to specify the parameter within a parameter file. 

 

EVEN MORE ADVANCED FEATURES OF ORACLE DATA PUMP

NETWORK MODE :
Data Pump gives you the ability to pass data between two databases over a network (via a database link), without creating a dump file on disk. This is very useful if you’re moving data between databases, like data marts to data warehouses, and disk space is not readily available. 
Network export creates the dump file set on the instance where the Data Pump job is running and extracts the metadata and data from the remote instance. 

GENERATING SQLFILES :
With Data Pump, it is a lot easier to get a workable DDL script. When you run Data Pump Import and specify the SQLFILE parameter, a text file is generated that has the necessary DDL (Data Definition Language) in it to recreate all object types, not just tables and indexes. Although this output file is ready for execution, the DDL statements are not actually executed, so the target system will not be changed. 
impdp username/password DIRECTORY=dpumpdir1 DUMPFILE=expfull.dmp SQLFILE=dpump_dir2:expfull.sql INCLUDE=TABLE,INDEX 

FREQUENTLY ASKED QUESTIONS

1. Why are directory objects needed? 
They are needed to ensure data security and integrity. Otherwise, users would be able to read data that they should not have access to and perform unwarranted operations on the server. 
2. What makes Data Pump faster than original Export and Import? 
There are three main reasons that Data Pump is faster than original Export and Import. First, the Direct Path data access method (which permits the server to bypass SQL and go right to the data blocks on disk) has been rewritten to be much more efficient and now supports Data Pump Import and Export. Second, because Data Pump does its processing on the server rather than in the client, much less data has to be moved between client and server. Finally, Data Pump was designed from the ground up to take advantage of modern hardware and operating system architectures in ways that original Export/ and Import cannot. These factors combine to produce significant performance improvements for Data Pump over original Export and Import 
3. How much faster is Data Pump than the original Export and Import utilities? 
For a single stream, Data Pump Export is approximately 2 times faster than original Export and Data Pump Import is approximately 15 to 40 times faster than original Import. These performance improvements are for data movement only, not metadata. Speed can be dramatically improved using the PARALLEL parameter. 
4. Why is Data Pump slower on small jobs? 
Data Pump was designed for big jobs with lots of data. Each Data Pump job has a master table that has all the information about the job and is needed for restartability. The overhead of creating this master table makes small jobs take longer, but the speed in processing large amounts of data gives Data Pump a significant advantage in medium and larger jobs. 
5. Are original Export and Import going away? 
Original Export is being deprecated with the Oracle Database 11g release. Original Import will always be supported so that dump files from earlier releases (release 5.0 and later) will be able to be imported. Original and Data Pump dump file formats are not compatible. 
6. Are Data Pump dump files and original Export and Import dump files compatible? 
No, the dump files are not compatible or interchangeable. If you have original Export dump files, you must use original Import to load them. 

CONCLUSION
Data Pump is fast and flexible. It replaces original Export and Import starting in Oracle Database 10g and 11g. Moving to Data Pump is easy, and opens up a world of new options and features.