Use implicit Trace parameters to diagnose Oracle Data Pump faults

The Data Pump was launched by Oracle from 10 GB and is used to replace the Data backup and restoration component of the traditional exp/imp tool. After the evolution and modification of several versions, Data Pump has become very mature and is gradually accepted by more and more DBAs and O & M personnel.

Compared with the traditional exp/imp, Data Pump has many advantages and becomes more complex. One of the most notable features of the data pump is the Server-Side operation. Exp/Imp is a small tool running on the client. Although it is easy to use, it is necessary to handle the compatibility problem between the data source and the target server and the client. This is why many friends on the Internet are struggling with how to deal with Exp/Imp version differences. In addition, the Exp/Imp running on the client is greatly affected by the network. Once the network is unstable for a long time, the operation may end with a failure. At the same time, exp/imp still has many shortcomings in performance, stability, and feature support.

The Data Pump runs on the server, directly reducing the possibility of version problems. The version parameter can be effectively controlled even if a version problem exists. In addition, you can run a separate job to avoid unexpected interruptions.

Even so, we often encounter Data Pump faults and problems. In many cases, we cannot diagnose Data Pump completely with the help of prompt information. At this time, we can consider using the hidden Trace parameter of Data Pump to generate a Trace file and gradually troubleshoot the error.

View Cursor from 10046 Trace RAW File

How do I diagnose the ORA-00060 Deadlock Type through Trace?

ORA-01157: cannot identify/lock data file 6-see DBWR Trace file ORA-01110: Solution

Solve the problem that autoTrace does not display Predicate Information

Oracle 11g new SQL Trace 10046 Method

Oracle SQL Trace and 10046 events

1. Data Pump Working Principle and environment preparation

Data Pump has two features: Job Scheduling and multi-process collaboration. In Oracle, Data Pump is processed as a specific Job. It can start, stop, and pause a Job, more importantly, the Dump job is independent of external users. That is to say, the user does not need to "stare at" The interface like Exp/Imp, and does not need to use nohup & background job to implement automatic background operations.

In work, Data Pump is a multi-process combination. We can see from the workday log that when each Data Pump job is created, a job table is automatically created, which records the operation process. There are two types of Process Work when a Job is working. One is the master control process, which is responsible for overall Process coordination, Work Process pool management, and task allocation. The actual import and export process is the Work Process. If the parallel parameter is set, multiple Work processes will Work on the data.

The diagnosis of Data Pump is essentially the tracking of various Process behaviors. Oracle provides an implicit Trace parameter to help us achieve this goal.

First, prepare the Data Pump working environment. Prepare the Directory object.

[Root @ SimpleLinux/] # ls-l | grep dumpdata

Drwxr-xr-x 2 root 4096 Sep 11 09: 01 dumpdata

[Root @ SimpleLinux/] # chown-R oracle: oinstall dumpdata/

[Root @ SimpleLinux/] # ls-l | grep dumpdata

Drwxr-xr-x 2 oracle oinstall 4096 Sep 11 dumpdata

-- Create a directory object

SQL> select * from v $ version where rownum <2;

BANNER

-----------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Producti

SQL> create directory dumpdir as '/dumpdata ';

Directory created

2. Implicit parameter Trace

The Trace parameter is an implicit internal parameter of Data Pump. The usage method is the same as that of other data pump parameters, but the usage value requires some attention. The following is the Trace command of our experiment.

[Oracle @ SimpleLinux dumpdata] $ expdp \ "/as sysdba \" directory = dumpdir schemas = scott dumpfile = scott_dump.dmp parallel = 2 trace = 480300

Export: Release 11.2.0.3.0-Production on Wed Sep 11 09:45:07 2013

The Trace is not the same as other Trace processes. Use the y/n parameter to enable or disable the Trace. The Trace parameter of Data Pump is a 7-digit hexadecimal numeric string. Different numeric strings indicate different trace object methods. The 7-digit hexadecimal number is divided into two parts. The first three digits indicate the specific data Pump component, and the last four digits can use 0300.

According to the information provided in Oracle MOS, the Trace characters follow the following setting rules:
 
 
 
Ü do not enter more than 7 characters;
 
Ü you do not need to use 0X to specify hexadecimal characters;
 
Ü hexadecimal characters cannot be converted to numeric values;
 
Ü if the 7th character starts with 0, it can be omitted;
 
Ü the input characters are case insensitive;
 
Each component is represented by a different three-digit hexadecimal number. See the following snippet:
 
 
-- Summary of Data Pump trace levels:
-- ========================================

Trace dm dw ora Lines
Level trc in
(Hex) file trace Purpose
------------------------------------------------------------------------
10300 x SHDW: To trace the Shadow process (API) (expdp/impdp)
20300 x KUPV: To trace Fixed table
40300 x 'div 'To trace Process services
80300 x KUPM: To trace Master Control Process (MCP) (DM)
100300 x KUPF: To trace File Manager
200300 x KUPC: To trace Queue services
400300 x KUPW: To trace Worker process (es) (DW)
800300 x KUPD: To trace Data Package
1000300 x META. To trace Metadata Package
--- +
1FF0300 x 'all' To trace all components (full tracing)
 
 
To track multiple components at the same time, you need to accumulate the hex Value of the target component. The 300 values of the last four digits are the same.

The Trace file generated by the target Dump job is essentially different from other Trace files. The default value is in the BACKGROUP_DUMP_DEST directory. However, note that the Data Pump Trace Process generates multiple Trace files and locates the Process ID information of dm and dw.

One recommended method is to temporarily Save the Trc and trm files in the directory to other places if the system service is not very busy. Then perform the Trace job. The generated file clearly shows what it is.

For Trace values of a trail, we recommend that Oracle use 480300 to handle most of the situations. 480300 the Master Control Process (MCP) and Work Process of the Oracle Dump job will be tracked. As the process of initializing the trail, 480300 is enough.

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page