World of Oracle DBA

In many cases you sometimes stop data pump job or in case of an abnormal end of the Data Pump job (the orphaned job) or using undocumented parameter KEEP_MASTER=Y, the master table remain in the database. Though this topic is related to cleanup orphaned datapump jobs. But it is good to know several things before doing cleanup jobs. 1) You can check the orphaned data pump from the state column of the view dba_datapump_jobs and DBA_DATAPUMP_JOBS is based on gv$datapump_job, obj$, com$, and user$. Orphaned Data Pump jobs do not have an impact on new Data Pump jobs. If a new Data Pump job is started, a new entry will be created, which has no relation to the old Data Pump jobs. 2) For a new data pump job without any job name it is used a system generated name. From the dba_datapump_jobs it is checked for existing data pump jobs and then obtain a unique new system generated jobname. 3) Data pump jobs are different from DBMS_JOBS and they are maintained differently. Jobs created with DBMS_JOB...

Problem Description E:\>impdp directory=test dumpfile=testexp_07_03_09.dmp userid=shaik/a Import: Release 10.2.0.1.0 - Production on Thursday, 07 May, 2009 10:07:00 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39143: dump file "E:\oracle\Test\testexp_07_03_09.dmp" may be an original export dump file Cause of the problem The above problem happened whenever you try to use the Import Data Pump client (impdp) to import a dumpfile that was created with the original Export client (exp). Though not related but similar error occured. Like whenever you try to import from an empty file, E:\>impdp directory=test dumpfile=testexp_07_03_09.dmp userid=shaik/a Import: Release 10.2.0.1.0 - Production on Thursday, 07 May, 2009 10:16:52 Copyright (c) 2003, 2005, Orac...

Problem Description Connecting to local 10.2.01 database whenever I want to take data pump export into remote 11g database machine using NETWORK_LINK parameter data pump export fails with ORA-39001, ORA-39169 as below. [oracle@localhost bin]$ ./expdp system/a NETWORK_LINK=maestro.net schemas=maximsg VERSION=10.2 Export: Release 10.2.0.1.0 - Production on Wednesday, 04 March, 2009 5:27:39 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-39001: invalid argument value ORA-39169: Local version of 10.2.0.1.0 cannot work with remote version of 11.1.0.6.0. Similarly, connecting to local 10.1.0.* database whenever I want to take data pump export into remote 11g database machine using NETWORK_LINK parameter data pump export fails with ORA-39006: internal error ORA-39022: Database version 11.1.0.6.0 is not supported. Doing expdp/impdp connected to a ...

We can get the number of objects processed and timing information needed to process object types in data pump jobs. We can achieve this goal by using the undocumented parameter METRICS. By setting parameter METRICS to y we can get timing details. An example is given below. E:\Documents and Settings\ shaik >expdp schemas=shaik userid= shaik /a dumpfile= shaik _30_04.dmp logfile= shaik _20_04.log metrics=y Export: Release 10.2.0.1.0 - Production on Friday, 01 May, 2009 7:24:07 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting "SHAIK"."SYS_EXPORT_SCHEMA_03": schemas= shaik userid= shaik /******** dumpfile= shaik _30_04.dmp logfile= shaik _20_04.lo g metrics=y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1024 KB Processing obj...

Data Pump jobs use a master table, a master process, and worker processes to perform the work and keep track of the progress. For every Data Pump Export and Data Pump Import job, a master process is created. The master process controls the entire job, including communicating with the clients, creating and controlling a pool of worker processes, and performing logging operations. Let's see an example. 1)Starting data pump jobs in one session. expdp schemas=shaik userid=shaik/a dumpfile=shaik_30_04_2009.dmp logfile=arju_20_04_09.log 2)Query from dba_datapump_session to know the data pump job status. set lines 150 pages 100 col program for a20 col username for a5 col spid for a7 col job_name for a25 select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr; DATE PROGRAM ...

Whenever you issue, impdp help=y or expdp help=y you can see a list of parameters that can be used for oracle data pump export/import jobs. From there you don't see any parameter by which you can trace data pump jobs. But tracing datapump job is an important issue in case of diagnosing incorrect behavior and/or troubleshooting Data Pump errors. The undocumented parameter TRACE is really useful to troubleshoot data pump jobs. The tracing of data pump is done by TRACE parameter. This parameter takes value as 7 digit hexadecimal number. Specifying the parameter value follow some rules. Out of 7 digit hexadecimal number, - first 3 digits are responsible to enable tracing for a specific data pump component. - Rest 4 digits are usually 0300 - Specifying more than 7 hexadecimal number is not allowed. Doing so will result, UDE-00014: invalid value for parameter, 'trace'. - Specifying leading 0x (hexadecimal specification characters) is not allowed. - Value to be specified in hexade...

With uniq utility you can remove duplicate line if they are within successive line. For example you have successive identical line in the file, then with uniq you can discard all but one of successive identical lines from the file. Consider you have following lines within with files. # vi student_data.txt Roll number is 024401 His Name is SHAIK His Name is SHAIK He is 24 years old. Roll number is 12345 Then using use uniq utility as below will yield following result. # uniq student_data.txt Roll number is 12345 His Name is SHAIK He is 24 years old. Note that within file there was two duplicate lines. One is, "Roll number is 12345" and another is "His Name is SHAIK". Using the "uniq" output only "His Name is SHAIK" line is omitted because they are successive identical lines. However "Roll number is 12345" text line is not removed because they are not successive though they are identical. So uniq utility is used to remove adjacent identic...