Day 4 High Speed Export / Import: Oracle Data Pump
It has been greatly improved with Oracle Database 10g utility data movement.
To date, the export / import toolset is still the preferred utility that is the least labor intensity of labor intensity across multiple platforms, although people often complain that it is too slow. Import just reads each record from the export dump file, and then insert it into the target table using the common insert into command, so import may be a very slow process, which is not surprised.
Enter Oracle Data Pump, Oracle Database 10g's Export / Import Toolkit Update Faster Similar Tools, which is designed to accelerate this process.
Data Pump reflects a thorough innovation of the entire export / import process. It is not using a common SQL command, but applies a dedicated API to load and uninstall data faster. In my test, I see that the export performance has increased by 10-15 times in direct mode, and the import process performance has increased by 5 times. In addition, unlike the use of export utility, it can only take only specific types of objects (such as the process).
Data Pump Export
This new utility is called EXPDP, which is distinguished from the original export EXP. In this example, we will use Data Pump to export a large table CASES, the size is about 3GB. Data Pump uses file processing in server-side to create and read files; therefore, the directory is used as location. In this case, we will use file system / u02 / dpdata1 to save dump files.
Create Directory DPDATA1 AS '/ U02 / DPDATA1';
Grant Read, Write on Directory DPData1 to Ananda;
Next, we will export data:
Expdp Ananda / Abc123 Tables = CASES DIRECTORY = DPDATA1
DUMPFILE = EXPCASES.DMP JOB_NAME = CASES_EXPORT
Let us analyze the various parts of the command. The meaning of the user ID / password combination, the table, and dump file parameters is obvious. Unlike the original export, the file is created on the server (not the client). The location is specified by the directory parameter value DPDATA1, which points to previously created / u02 / dpdata1. This process also creates a log file in the location specified by the directory parameter (on the server). By default, this process uses a directory named DPUMP_DIR; so you can create it instead of DPDATA1.
Note that the above parameters job_name, this is a special parameter, not in the original export. All Data Pump works through the job. Data Pump Jobs - Different from the DBMS job - just server processes, represents the main process to process data. The main process (called the main control process) coordinates this work by advanced queue (AQ); it achieves this by a special table created during the run. In our example, if you check the user ananda mode when you run the EXPDP, you will notice the presence of a table CASES_EXPORT (corresponding parameter job_name). This table is discarded when EXPDP ends.
Export monitoring
When the Data Pump Export (DPE) is run, press Control-C; it will block the message on the screen, but do not stop exporting the process itself. Instead, it will display the DPE prompt (as shown below). The process is now considered to be "interactive" mode: export>
This method allows you to enter several commands on this DPE job. To view the summary, use the status command at the prompt:
Export> Status
Job: Cases_export
Operation: export
Mode: Table
State: Executing
Degree: 1
Job Error Count: 0
Dump file: /u02/dpdata1/expcases.dmp
BYTES WRITTEN = 2048
Worker 1 status:
State: Executing
Object Schema: dwowner
Object Name: Cases
Object Type: Table_Export / TBL_TABLE_DATA / TABLE / TABLE_DATA
Completed Objects: 1
Total Objects: 1
Completed Rows: 4687818
Remember, this is just the status display. Export work in the background. To continue to view messages on the screen, use the command amount_client from the Export> prompt.
Parallel operation
You can use the Parallel parameter to export using more than one thread to significantly accelerate your job. Each thread creates a separate dump file, so the parameter dumpfile should have an item as much as possible. You can specify a wildcard as a file name instead of explicitly enter each file name, for example:
Expdp Ananda / Abc123 Tables = CASES DIRECTORY = DPDATA1
DUMPFILE = EXPCASES_% u.dmp Parallel = 4 JOB_NAME = CASES_EXPORT
Note that the DumpFile parameter has a wildcard% U, which indicates that the file will be created as needed, and the format will be expcases_nn.dmp, where NN starts from 01, then increases up as needed.
In parallel mode, the status screen will display four working processes. (In the default mode, only one process is visible.) All working processes are simultaneously removed and their progress is displayed on the status screen.
It is important to separate access / output channels for the access data file and dump directory file system. Otherwise, the overhead associated with maintaining the Data Pump can exceed the efficiency of the parallel thread and therefore reduces performance. Parallelism only is effective when the number is more than parallel value and the table is large.
Database monitoring
You can also get more information about running Data Pump jobs from the database view. The main view of the monitoring job is DBA_DataPump_jobs, which will tell you how many working processes (column degree) are working in the job. Another important view is DBA_DATAPUMP_SESSIONS, which will give a session SID for the main front process when it combines with the above view and V $ session.
Select Sid, Serial #
From v $ session s, dba_datapump_sessions d
WHERE S.SADDR = D.SADDR;
This instruction displays the session of the front desk process. More useful information can be obtained from the alert log. When the process starts, the MCP and working processes are shown in the alert log:
Kupprdp: Master Process DM00 Started with PID = 23, OS ID = 20530 To EXECUTE -SYS.KUPM $ mcp.main ('Cases_export', 'Ananda');
Kupprdp: worker process dw01 started with worker id = 1, pid = 24, OS ID = 20532 to execute -
Sys.kupw $ worker.main ('Cases_export', 'Ananda');
Kupprdp: worker process dw03 started with worker id = 2, pid = 25, OS ID = 20534 to Execute -
Sys.kupw $ worker.main ('Cases_export', 'Ananda');
It shows the PID of the session that is started as a data pump operation. You can find the actual SID with the following query:
SELECT SID, Program from V $ Session WHERE PADDR in
(SELECT Addr from V $ Process Where PID in (23, 24, 25));
The Program column will display the process DM (main process) or DW (for working process) in the name of the name of the alarm log file. If a work process uses a parallel query, such as SID 23, you can see it in the view v $ px_session and find it. It will display all parallel query sessions running from the work process represented by SID 23:
SELECT SID FROM V $ PX_SESSION WHERE QCSID = 23;
Other useful information can be obtained from view v $ session_longops to predict the time that the completion of the job will cost.
Select Sid, Serial #, Sofar, Totalwork
From v $ session_longops
Where opname = 'carated_export'
And Sofar! = TotalWork;
Column TotalWork displays the total workload, the number of Sofar's Sofar is added to the current time - so you can use it to estimate how long it takes.
Data Pump Import
However, the data import performance is the truly beautiful place of Data Pump. To import the previously exported data, we will use
IMPDP Ananda / Abc123 Directory = DPDATA1 DUMPFILE = EXPCASES.DMP JOB_NAME = CASES_IMPORT
The default behavior of the import process is to create a table and all related objects and then generate an error when the table already exists. If you want to add data to an existing table, you can use Table_exists_Action = append in the above command line.
As in the process of importing with Data Pump, press Control-C in the process to enter the interactive mode of Date Pump Import (DPI); Similarly, the prompt is import>.
Handle specific objects
Have you ever only export specific procedures from one user to recreate these processes in a different database or user? Different from traditional export utilities, Data Pump allows you to export only specific types of objects. For example, the following command allows you to export only anything else - do not export tables, views, and even functions:
Expdp Ananda / Iclaim Directory = DPDATA1 DUMPFILE = EXPPROCS.DMP INCLUDE = Procedure To export some specific objects - For example, functions FUNC1 and process Proc1 - you can use
Expdp Ananda / Iclaim Directory = DPDATA1 DUMPFILE = EXPPROCS.DMP
INCLUDE = procedure: / "= / 'proc1 /' /", function: / "= / 'func1 /' /"
This dump file acts as a backup of the source object. You can even use it to create a DDL script for later use. A special parameter called SQLFile allows you to create a DDL script file.
IMPDP Ananda / Iclaim Directory = DPDATA1 DUMPFILE = EXPPROCS.DMP SQLFILE = procs.sql
This instruction creates a file named procs.sql in the directory specified by DPDATA1, and contains scripts of the object in export dump files. This method helps you create a source object in another mode.
Use Parameter Include to allow you to define objects to include or exclude from dump files. You can use clauses include = Table: "Like 'Tab%'" to export only those names that are starting with TAB. Similarly, you can use Structure Include = Table: "NOT LIKE 'TAB%'" to exclude all names starting with TAB. As another option, you can use the Exclude parameter to exclude specific objects.
Through the external table, Data PUMP can also be used to transmit table space; it is very powerful and can instantly define parallel mode, add more tables to an existing process (this is beyond the scope of this article; For more details, please refer to Oracle Database Utilities 10g Release 1 10.1). The following command displays a list of all parameters provided by the Data Pump Export utility:
Expdp help = y
Similarly, IMPDP Help = Y will display all parameters in the DPI.
When the Data Pump job is running, you can suspend them by issuing STOP_JOB at the DPE or DPI prompt, then use start_job to restart them. This feature is very convenient in your space and wants to modify it before you continue to perform.
For more information, please read the Part 1 of the Oracle Database Utilities 10g Release 1 10.1 Guide.
