Work on jobs for expdp an impdp

                                  Work on jobs for expdp an impdp

While using the expdp use job_name clause to create a user defined job.

[oracle@myserver01 ABC11TST]$ cat par_test.par

directory=DATA_PUMP_DIR

dumpfile=ABC11TST_testing.dmp

logfile=ABC11TST_testing.log

JOB_NAME=test123

full=y

run the expdp and starts play with jobs

 [oracle@myserver01 ABC11TST]$ expdp parfile=par_test.par

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:34:30

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

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

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

Starting "SYS"."TEST123":  /******** AS SYSDBA parfile=par_test.par

Estimate in progress using BLOCKS method...

Once expdp is running:

To Stop/Start/Kill the jobs check the job status and use below to stop/kill/start

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME                OPERATION      JOB_MODE        STATE

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

SYS        TEST123                   EXPORT         FULL           EXECUTING

[oracle@myserver01 ~]$ ps -ef |grep exp

root      3789     1  0 Feb02 ?        00:00:24 gpm -m /dev/input/mice -t exps2

oracle    5477  8147  0 22:34 pts/2    00:00:00 expdp

oracle    5535  1552  0 22:34 pts/1    00:00:00 grep exp

its internal process is DW01 [n]

myserver01:GEM11TST\sys> !ps -ef |grep -i DW01

oracle    5492     1 84 22:34 ?        00:00:55 ora_dw01_ABC11TST

oracle    5672  5652  0 22:35 pts/1    00:00:00 /bin/bash -c ps -ef |grep -i DW01

To Stop the job:

Use attach clause to get into the job like below:

[oracle@myserver01~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:43:06

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

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

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

Job: TEST123

  Owner: SYS

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: FD94D4B483EF4A7BE040140A2613156C

  Start Time: Sunday, 06 July, 2014 22:38:04

  Mode: FULL

  Instance: ABC11TST

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        /******** AS SYSDBA parfile=par_test.par

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

    bytes written: 4,755,456

Worker 1 Status:

  Process Name: DW01

  State: EXECUTING

  Object Schema: TEST_SOAINFRA

  Object Name: REFERENCE_INSTANCE

  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 879

  Completed Rows: 98,326

  Worker Parallelism: 1

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([yes]/no): yes

[oracle@myserver01 ~]$

Now again check the status

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME         OPERATION      JOB_MODE        STATE

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

SYS        TEST123            EXPORT          FULL         NOT RUNNING

[oracle@myserver01 ~]$ ps -ef |grep -i expdp

oracle    5840  5759  0 22:37 pts/1    00:00:00 grep -i expdp

[oracle@myserver01 ~]$

[oracle@myserver01 ~]$ ps -ef |grep -i 5492

oracle    5847  5759  0 22:37 pts/1    00:00:00 grep -i 5492

[oracle@myserver01 ~]$ ps -ef |grep -i 5477

oracle    5854  5759  0 22:37 pts/1    00:00:00 grep -i 5477

[oracle@myserver01 ~]$

Now start the Job:

[oracle@myserver01~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:37:59

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

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

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

Job: TEST123

  Owner: SYS

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: FD94D4B483EF4A7BE040140A2613156C

  Start Time: Sunday, 06 July, 2014 22:38:04

  Mode: FULL

  Instance: ABC11TST

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        /******** AS SYSDBA parfile=par_test.par

  State: IDLING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

    bytes written: 4,751,360

Worker 1 Status:

  Process Name: DW01

  State: UNDEFINED

  Object Schema: TEST_SOAINFRA

  Object Name: REFERENCE_INSTANCE

  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 879

  Completed Rows: 9,076

  Worker Parallelism: 1

Export> START_JOB

Export> status

Job: TEST123

  Operation: EXPORT

  Mode: FULL

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

    bytes written: 4,755,456

Worker 1 Status:

  Process Name: DW01

  State: EXECUTING

  Object Schema: TEST_SOAINFRA

  Object Name: REFERENCE_INSTANCE

  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 879

  Completed Rows: 88,962

  Worker Parallelism: 1

Export> exit

[oracle@myserver01~]$ ps -ef |grep -i DW01

oracle    5952     1  9 22:38 ?        00:00:16 ora_dw01_ABC11TST

oracle    6257  5877  0 22:41 pts/1    00:00:00 grep -i dw01

[oracle@myserver01~]$ ps -ef |grep -i expdp

oracle    6299  5877  0 22:41 pts/1    00:00:00 grep -i expdp

[oracle@myserver01~]$

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME                OPERATION      JOB_MODE        STATE

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

SYS        TEST123                   EXPORT         FULL           EXECUTING

To Kill the Jobs:

Check job  status

OWNER_NAME      JOB_NAME        OPERATION            JOB_MODE                 STATE

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

SYS             TEST123         EXPORT               FULL                  EXECUTING

[oracle@myserver01 ~]$ ps -ef |grep expdp

oracle   12703  8147  0 00:05 pts/2    00:00:00 expdp

oracle   15100 15053  0 00:36 pts/1    00:00:00 grep expdp

[oracle@myserver01 ~]$ ps -ef |grep DW01

oracle   15102 15053  0 00:36 pts/1    00:00:00 grep DW01

[oracle@myserver01 ~]$

Attach to job

[oracle@myserver01 ~]$ expdp ATTACH=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 0:36:14

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

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

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

Job: TEST123

  Owner: SYS

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: FD96194AF4EA35C6E040140A261331A8

  Start Time: Monday, 07 July, 2014 0:05:25

  Mode: FULL

  Instance: ABC11TST

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        /******** AS SYSDBA parfile=par_test.par

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

    bytes written: 4,751,360

Worker 1 Status:

  Process Name: DW01

  State: EXECUTING

  Object Schema: TEST_SOAINFRA

  Object Name: REFERENCE_INSTANCE

  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 879

  Completed Rows: 241,615

  Worker Parallelism: 1

Export> exit

Export> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): yes

[oracle@myserver01 ~]$

After killed the job everything will gone.

[oracle@myserver01 ~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 0:46:24

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

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

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

ORA-31626: job does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.KUPV$FT", line 386

ORA-31638: cannot attach to job TEST123 for user SYS

ORA-31632: master table "SYS.TEST123" not found, invalid, or inaccessible

ORA-00942: table or view does not exist

[oracle@myserver01 ~]$ ps -ef |grep expdp

oracle   15543 15053  0 00:41 pts/1    00:00:00 grep expdp

[oracle@myserver01 ~]$ ps -ef |grep DW01

oracle   15548 15053  0 00:41 pts/1    00:00:00 grep DW01

[oracle@myserver01 ~]$

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

no rows selected

Issue Faced during Job stop and start:

ORA-39095

Job "SYS"."TEST123" stopped due to fatal error at 22:09:39

Job TEST123 has been reopened at Sunday, 06 July, 2014 22:09

Restarting "SYS"."TEST123":  /******** AS SYSDBA parfile=par_test.par

ORA-39095: Dump file space has been exhausted: Unable to allocate 0 bytes

Job "SYS"."TEST123" stopped due to fatal error at 22:10:19

These kind of error comes for couple of reason.

1)    If you use parallel option while exporting through expdp

2)    Another reason if you are using compression method in expdp.

Both reason will cause the same error.

Error comes while starting the job, job will stop and it won’t be able to start. So avoid using compression and parallel in such scenario.