Hidden Parameter Datapump expdp/impdp

 Oracle Undocumented Parameters


1. METRICS

You can use the parameter METRICS=Y to include additional logging information about the number of objects and the time it took to process them in the log file. With METRICS, additional information can be obtained about the number of objects that were processed and the time it took for processing them.

Note that the METRICS parameter does not create any additional trace files. Instead, the extra details are logged in the logfile of the Export Data Pump or Import Data Pump job.

Here is an example:

 expdp \'/ as sysdba\' DUMPFILE=data_pump_dir:sysman_%U.dat SCHEMAS=sysman_apm JOB_NAME=sysman_pump METRICS=Y

[oracle@zurich Desktop]$ expdp \'/ as sysdba\' DUMPFILE=data_pump_dir:sysman_%U.dat SCHEMAS=sysman_apm JOB_NAME=sysman_pump METRICS=Y

  

Export: Release 11.2.0.3.0 - Production on Tue Dec 20 10:46:08 2011

  

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

  

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYSMAN_PUMP":  "/******** AS SYSDBA" DUMPFILE=data_pump_dir:sysman_%U.dat SCHEMAS=sysman_apm JOB_NAME=sysman_pump METRICS=Y 

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 320 KB

Processing object type SCHEMA_EXPORT/USER

     Completed 1 USER objects in 0 seconds

...

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

     Completed 1 DEFAULT_ROLE objects in 2 seconds

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

     Completed 1 TABLESPACE_QUOTA objects in 8 seconds

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

     Completed 1 PROCACT_SCHEMA objects in 27 seconds

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

...

. . exported "SYSMAN_APM"."PATCH_RUN"                        0 KB       0 rows

Master table "SYS"."SYSMAN_PUMP" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYSMAN_PUMP is:

  /u01/app/oracle/admin/CLOUD/dpdump/sysman_01.dat

Job "SYS"."SYSMAN_PUMP" successfully completed at 10:49:19


2. ACCESS_METHOD


In some situations, the undocumented parameter ACCESS_METHOD can be used to enforce a specific method to unload or load the data.

If the parameter is not specified, then Data Pump will automatically choose the best method to load or unload the data.

The parameter can only be specified when the Data Pump job is initially started i.e., the parameter cannot be specified when the job is restarted.

If the parameter is specified, the method of loading or unloading the data is enforced on all tables that need to be loaded or unloaded with the job.

Enforcing a specific method may result in a slower performance of the overall Data Pump job.

Here is an example:


expdp \'/ as sysdba\'  ... ACCESS_METHOD=DIRECT_PATH  

expdp \'/ as sysdba\'  ... ACCESS_METHOD=EXTERNAL_TABLE 

  

impdp \'/ as sysdba\'  ... ACCESS_METHOD=DIRECT_PATH  

impdp \'/ as sysdba\'  ... ACCESS_METHOD=EXTERNAL_TABLE

 

3. TRACE


Tracing can be enabled by specifying an 7 digit hexadecimal mask in the TRACE parameter of expdp or impdp. The first three digits enable tracing for a specific DataPump component, while the last four digits are usually: 0300. Any leading zero’s can be omitted, and the value specified for the TRACE parameter is not case sensitive. TRACE does not add anything to the output of DataPump, it creates additional trace files.

Here is a summary of the Data Pump trace levels:


   10300 SHDW: To trace the Shadow process 

  20300 KUPV: To trace Fixed table

  40300 'div' To trace Process services

  80300 KUPM: To trace Master Control Process 

 100300 KUPF: To trace File Manager

 200300 KUPC: To trace Queue services

 400300 KUPW: To trace Worker process(es)         

 800300 KUPD: To trace Data Package

1000300 META: To trace Metadata Package

1FF0300 'all' To trace all components, full tracing


As you see, in order to trace all Data Pump components, level 1FF0300 can be specified, here is an example:

impdp \'/ as sysdba\' NETWORK_LINK=edw_p SCHEMAS=CLOUD PARALLEL=8 JOB_NAME=net_pump EXCLUDE=statistics TRACE=1FF0300 

SHDW: *** GET_STATUS_VERSION call ***

SHDW:     Version = 9999

KUPP:10:58:22.050: Input trace/debug flags: 01FF0300 = 33489664

KUPP:10:58:22.050: Current trace/debug flags: 01FF0300 = 33489664

SHDW:10:58:22.050: Current user = SYS

SHDW:10:58:22.050: Current schema = SYS

SHDW:10:58:22.050: Current language = AMERICAN_AMERICA.AL32UTF8

SHDW:10:58:22.052: Current session address = 0000000077F7AFA0

SHDW:10:58:22.052: *** OPEN call ***

SHDW:10:58:22.052:     operation       = IMPORT

SHDW:10:58:22.052:     job_mode        = schema

SHDW:10:58:22.052:     remote_link     = edw_p

SHDW:10:58:22.052:     version         = 

SHDW:10:58:22.052:     compression     = 2

KUPV:10:58:22.052: Open request for job: SYS.NET_PUMP

KUPV:10:58:22.058: Master Table create statement: CREATE TABLE "SYS"."SYSMAN_PUMP" (process_order NUMBER, duplicate NUMBER, dump_fileid NUMBER, dump_position NUMBER, dump_length NUMBER, dump_orig_length NUMBER 


The 3 types of trace files can be found in $ADR_HOME/trace:

- Data Pump Master Control Processes files have format: SID_dm_process_id.trc

- Data Pump Worker Process trace files have format: SID_dw_process_id.trc

- Data Pump Shadow Process trace files have format: SID_ora_process_id.trc


4. KEEP_MASTER


If the undocumented DataPump parameter KEEP_MASTER=Y is used, then the Master Table is not deleted after the job completes. The drop of the master table does not lead to any data dictionary corruption and if you keep the master table after the job completes, then a drop of the master table afterwards will not cause any corruption either.

You might use this parameter if you create transportable tablespaces where the source and the destination are both ASM based.

Here is an example:

 expdp \'/ as sysdba\' directory=DUMPDIR DUMPFILE=skat_full.dmp LOGFILE=skat_full.log FULL=Y KEEP_MASTER=Y

Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more