OGG-01755 or OGG-01759

2015-03-29 01:55:00  ERROR   OGG-01755  Oracle GoldenGate Command Interpreter for Oracle:  Cannot register or unregister EXTRACT E0000019 because of the following SQL error: OCI Error 26,665. See Extract user privileges in the Oracle GoldenGate for Oracle Installation and Setup Guide.

2015-03-29 00:03:53  ERROR   OGG-01759  Oracle GoldenGate Command Interpreter for Oracle:  Cannot unregister EXTRACT E0000019 from database because this Extract is not currently registered

Problem

We are not able to clean up or remove the Integrated Exact from the Golden Gate after removing it manually.

Mainly check the below object

select owner,object_name,object_type,status from dba_objects where object_name in ('DDLREPLICATION','DBMS_CAPTURE_ADM_INTERNAL');

Solution

1)     Check whether all the sys object are valid.

2)     Check whether all the goldengate user objects are valid; you need to re-create but check with oracle support or oracle documentation.

Couple of useful queries

To check the current capturing extract in your environment.

select capture_name,status from dba_capture;

To stop the Extract

SQL> exec dbms_capture_adm.stop_capture('OGG$CAP_E0000019',true);

PL/SQL procedure successfully completed.

To Drop the Extract from the Registry

SQL> exec dbms_capture_adm.drop_capture('OGG$CAP_E0000019',true);

PL/SQL procedure successfully completed.

Note: Check with oracle support, if you want to make some changes in the production environment.

OGG-00868 Error code 1291, error message: ORA-01291: missing logfile

OGG-00868 Error code 1291, error message: ORA-01291: missing logfile

When you are working on GoldenGate with INTEGRATED CAPTURE and you are missing some of the old archive logs; you can’t start the extract like classic capture; INTEGRATED CAPTURE will work differently; it will try to start from the registered SCN number or if it is running then you will provide all the old archive logs till the latest one;

Note: if you are missing some old archive logs then you have to rebuild the entire GG setup

You need to do the below activity

GGSCI> STOP EXTRACT E0000032

GGSCI> UNREGISTER EXTRACT E0000032 DATABASE

GGSCI> REGISTER EXTRACT E0000032 DATABASE

GGSCI> START EXTRACT E0000032

GGSCI> INFO E0000032

You should see the extract changing from STARTING to RUNNING STATE;

if you feel still it is not getting started; then you have to follow like below(wait for atleast 10 min, some time INTEGRATED CAPTURE would take time depending on your database load)

GGSCI> STOP EXTRACT E0000032

GGSCI> UNREGISTER EXTRACT E0000032 DATABASE

GGSCI> delete EXTRACT E0000032

GGSCI> REGISTER EXTRACT E0000032 DATABASE

GGSCI> ADD EXTRACT E0000032, INTEGRATED TRANLOG, BEGIN NOW, Description "Some details about extract"

GGSCI> ADD EXTTRAIL ./dirdat/app/ap, EXTRACT E0000032

GGSCI> START EXTRACT E0000032

GGSCI> INFO E0000032

If you have any issue, you can see the ggserr.log or view report <extract_name> would give more details about the error message.

Happy Reading :)

How to set the NLS_LANG in GoldenGate

You need to set NLS_LANG when you run GoldenGate on the super-set character set for the database

you can see the NLS_LANG setting on the OS level

echo $NLS_LANG 
American_America.AL32UTF8

On the DB Side

it is the combination of below parameter, which will get it from NLS_DATABASE_PARAMETERS vuew
NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET

You can use the following commands to check the database character sets. (Each Database might have different setting; check your DB before you set this value)

col VALUE format a30
SQL> SELECT * from NLS_DATABASE_PARAMETERS; 

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0


20 rows selected.

Note:

1) if you set a wrong value; your data might be corrupted;

2) it might abended the Replicate process on the target.

ERROR OGG-00403 There can be only one DDL filtering statement.

When i have to include multiple schema name for the DDL filtering, i got the below error message

2014-12-25 07:58:48  ERROR   OGG-00403  There can be only one DDL filtering statement. If DDL filter is long, use ampersand (&) sign to continue it on another line.
2014-12-25 07:58:48  ERROR   OGG-01668  PROCESS ABENDING.

then i have modified like below. Below are the different example on how to include multiple schema and how to restrict the filtering in the EXTRACT for the Golden Gate.

Ex#1 - Including all the schema with all type of objects; 

DDL INCLUDE MAPPED OBJNAME DISCOVER.* INCLUDE MAPPED OBJNAME RESEARCH.* INCLUDE MAPPED OBJNAME LOAD.* INCLUDE MAPPED OBJNAME TEST.* INCLUDE MAPPED OBJNAME EVENT.* INCLUDE MAPPED OBJNAME FAST.*;

-- All the schema DDLReplication for TABLES ONLY

DDL INCLUDE MAPPED OBJNAME DISCOVER.* INCLUDE MAPPED OBJNAME RESEARCH.* INCLUDE MAPPED OBJNAME LOAD.* INCLUDE MAPPED OBJNAME TEST.* INCLUDE MAPPED OBJNAME EVENT.* INCLUDE MAPPED OBJNAME FAST.*,OBJTYPE 'TABLE';

EX#2 - Including all the object, Except the exclusion specified below.

DDL INCLUDE MAPPED &

   EXCLUDE INSTR 'drop partition' &

   EXCLUDE OBJTYPE 'TRIGGER' &

   EXCLUDE OBJTYPE 'INDEX' &

   EXCLUDE OBJTYPE 'CONSTRAINT' &

   EXCLUDE INSTRWORDS 'ALTER TABLE "DROP"' &

   EXCLUDE INSTR 'DROP TABLE'

EX#3 - Excluding the DDL for MV's & MV LOG's

DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'SNAPSHOT', EXCLUDE OBJTYPE 'SNAPSHOT LOG' 

Replacing Pump on Golden Gate V11.2 with same Name

We had a Golden Gate replication with EXTRACT, PUMP & REPLICAT format. For some reason Pump was hanging in the same place; we plan to replace the PUMP with the new one with the same name and same details.

Note: we couldn’t stop the PUMP using normal stop. So we have used the kill

1 (/ggsbin/11.2)(SOURCE)

We tried to stop pump process (P0000037), but it cannot be stopped as before with timeout message, so we killed it:

GGSCI 1> KILL P0000037

2.  Then we got detailed information about this process (I’ve highlighted information we used later to recreate the process): (SOURCE)

GGSCI 2> info P0000037, showch, detail

EXTRACT    P0000037  Last Started 2014-12-03 15:36   Status ABENDED

Checkpoint Lag       00:00:00 (updated 171:44:59 ago)

Log Read Checkpoint  File /ggsdata/dirdat/bhu/hr/tr000046

                     2014-11-26 11:57:03.000000  RBA 37973983

Current Checkpoint Detail:

Read Checkpoint #1

  GGS Log Trail

  Startup Checkpoint (starting position in the data source):

    Sequence #: 46

    RBA: 37973983

    Timestamp: 2014-11-26 11:57:03.000000

    Extract Trail: /ggsdata/dirdat/bhu/hr/tr

  Current Checkpoint (position of last record read in the data source):

    Sequence #: 46

    RBA: 37973983

    Timestamp: 2014-11-26 11:57:03.000000

    Extract Trail: /ggsdata/dirdat/bhu/hr/tr

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):

    Sequence #: 46

    RBA: 65832552

    Timestamp: 2014-11-26 11:57:08.027390

    Extract Trail: /ggsdata/dirdat/test/tr

CSN state information:

  CRC: F1-F1-2F-27

  Latest CSN: 2156609198684

  Latest TXN: 10.18.1315688

  Latest CSN of finished TXNs: 2156609198684

  Completed TXNs: 10.18.1315688

Header:

  Version = 2

  Record Source = A

  Type = 1

  # Input Checkpoints = 1

  # Output Checkpoints = 1

File Information:

  Block Size = 2048

  Max Blocks = 100

  Record Length = 2048

  Current Offset = 0

Configuration:

  Data Source = 0

  Transaction Integrity = 1

  Task Type = 0

Status:

  Start Time = 2014-12-03 15:36:47

  Last Update Time = 2014-11-26 11:57:08

  Stop Status = A

  Last Result = 400

 

   Target Extract Trails:

  Remote Trail Name                                Seqno        RBA     Max MB

  /ggsdata/dirdat/test/tr               46   65832552        100

  Extract Source                          Begin             End             

  /ggsdata/dirdat/bhu/hr/tr000046  2014-11-26 11:57  2014-11-26 11:57

  /ggsdata/dirdat/bhu/hr/tr000046  2014-11-26 11:57  2014-11-26 11:57

  /ggsdata/dirdat/bhu/hr/tr000046  2014-11-26 11:57  2014-11-26 11:57

  /ggsdata/dirdat/bhu/hr/tr000046  * Initialized *   2014-11-26 11:57

  /ggsdata/dirdat/bhu/hr/tr000034  * Initialized *   First Record   

  /ggsdata/dirdat/bhu/hr/tr000030  2014-10-29 16:00  2014-10-29 16:00

  /ggsdata/dirdat/bhu/hr/tr000030  2014-10-29 16:00  2014-10-29 16:00

  /ggsdata/dirdat/bhu/hr/tr000030  2014-10-29 16:00  2014-10-29 16:00

  /ggsdata/dirdat/bhu/hr/tr000030  2014-10-29 16:00  2014-10-29 16:00

  /ggsdata/dirdat/bhu/hr/tr000030  2014-10-25 11:56  2014-10-29 16:00

  /ggsdata/dirdat/bhu/hr/tr000028  2014-10-24 18:00  2014-10-25 11:56

  /ggsdata/dirdat/bhu/hr/tr000028  * Initialized *   2014-10-24 18:00

  /ggsdata/dirdat/bhu/hr/tr000020  * Initialized *   First Record   

  /ggsdata/dirdat/bhu/hr/tr000020  2014-10-11 12:57  2014-10-11 12:57

  /ggsdata/dirdat/bhu/hr/tr000020  2014-10-11 12:57  2014-10-11 12:57

  /ggsdata/dirdat/bhu/hr/tr000020  2014-10-11 12:57  2014-10-11 12:57

  /ggsdata/dirdat/bhu/hr/tr000020  2014-10-11 12:57  2014-10-11 12:57

  /ggsdata/dirdat/bhu/hr/tr000020  2014-10-11 12:57  2014-10-11 12:57

  /ggsdata/dirdat/bhu/hr/tr000020  2014-10-11 12:57  2014-10-11 12:57

  /ggsdata/dirdat/bhu/hr/tr000020  2014-10-11 12:57  2014-10-11 12:57

  /ggsdata/dirdat/bhu/hr/tr000020  2014-09-27 06:00  2014-10-11 12:57

  /ggsdata/dirdat/bhu/hr/tr000012  2014-09-27 06:00  2014-09-27 06:00

  /ggsdata/dirdat/bhu/hr/tr000012  2014-09-27 06:00  2014-09-27 06:00

  /ggsdata/dirdat/bhu/hr/tr000012  2014-09-27 06:00  2014-09-27 06:00

Current directory    /ggsbin/11.2

Report file          /ggsbin/11.2/dirrpt/P0000037.rpt

Parameter file       /ggsbin/11.2/dirprm/p0000037.prm

Checkpoint file      /ggsbin/11.2/dirchk/P0000037.cpe

Process file         /ggsbin/11.2/dirpcs/P0000037.pce

Stdout file          /ggsbin/11.2/dirout/P0000037.out

Error log            /ggsbin/11.2/ggserr.log

3.  We checked what number of trail sequence was delivered to Replicat side: (TARGET)

GGSCI> info R0000037, detail

REPLICAT   R0000037  Last Started 2014-12-02 14:13   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint  File /ggsdata/dirdat/test/tr000046

                     First Record  RBA 66056184

4.  We backed up param, process (which remained even after kill of the process) and checkpoint files just in case: (SOURCE)

# mkdir /ggsbin/11.2/dirprm/bak

# cp /ggsbin/11.2/dirprm/p0000037.prm /ggsbin/11.2/dirprm/bak/

# mv /ggsbin/11.2/dirchk/P0000037.cpe /ggsbin/11.2/dirchk/P0000037.cpe.bak

# mv /ggsbin/11.2/dirpcs/P0000037.pce /ggsbin/11.2/dirpcs/P0000037.pce.bak

Note:

1) We have moved the checkpoint file & process file to a different name

2) We have checked the process ID from the process file, whether process is exist in the server by using ps –ef|grep <process_Id>

5.  Then pump process has been deleted (SOURCE)

GGSCI 1> DBLOGIN USERID ggs@bhu, PASSWORD AADAAAAAAAAAAJNBOGKGNHZBOALCFBNHEERHZIQFQDUJCHKF AES128, ENCRYPTKEY PROD

Successfully logged into database.

GGSCI 2> DELETE EXTRACT P0000037

Deleted EXTRACT P0000037.

6.  Then we checked that process file and checkpoint files are gone

7.  Creating of pump process back (please, look at highlighted values used from p.2 and 3 outputs). Sequence 47 – is <last sequence of trails on REPLICAT side, which was 46 > + 1: (SOURCE)

GGSCI 1> DBLOGIN USERID ggs@bhu, PASSWORD AADAAAAAAAAAAJNBOGKGNHZBOALCFBNHEERHZIQFQDUJCHKF AES128, ENCRYPTKEY PROD

Successfully logged into database.

GGSCI 2> ADD EXTRACT P0000037, EXTTRAILSOURCE /ggsdata/dirdat/bhu/hr/tr

EXTRACT added.

GGSCI 8> ALTER EXTRACT P0000037, EXTSEQNO 46, EXTRBA 37973983

EXTRACT altered.

GGSCI 10> ADD RMTTRAIL /ggsdata/dirdat/test/tr, SEQNO 47, EXTRACT P0000037

RMTTRAIL added.

GGSCI 11> info RMTTRAIL /ggsdata/dirdat/test/tr

       Extract Trail: /ggsdata/dirdat/test/tr

             Extract: P0000037

               Seqno: 47

                 RBA: 0

           File Size: 100M

GGSCI 12> info P0000037, showch  (SOURCE)

EXTRACT    P0000037  Initialized   2014-12-03 15:48   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:01:50 ago)

Log Read Checkpoint  File /ggsdata/dirdat/bhu/hr/tr000046

                     First Record  RBA 37973983

Current Checkpoint Detail:

Read Checkpoint #1

  GGS Log Trail

  Startup Checkpoint (starting position in the data source):

    Sequence #: 46

    RBA: 37973983

    Timestamp: Not Available

    Extract Trail: /ggsdata/dirdat/bhu/hr/tr

  Current Checkpoint (position of last record read in the data source):

    Sequence #: 46

    RBA: 37973983

    Timestamp: Not Available

    Extract Trail: /ggsdata/dirdat/bhu/hr/tr

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):

    Sequence #: 47

    RBA: 0

    Timestamp: 2014-12-03 15:49:44.703064

    Extract Trail: /ggsdata/dirdat/test/tr

8.  Stop replicat process on QA(TARGET)

GGSCI  4> stop R0000037

Sending STOP request to REPLICAT R0000037 ...

Request processed.

9.  Alter replicat process on Target site: (TARGET)

GGSCI  2> ALTER REPLICAT R0000037, EXTSEQNO 47, EXTRBA 0

REPLICAT altered.

(SOURCE)

10. Start pump process on EXP:         

GGSCI 13> start P0000037

Sending START request to MANAGER ...

EXTRACT P0000037 starting

GGSCI 14> info P0000037

EXTRACT    P0000037  Last Started 2014-12-03 15:50   Status RUNNING

Description          "Prous CMS - QAL: cms.exp -> cms.qa"

Checkpoint Lag       00:00:00 (updated 00:00:05 ago)

Log Read Checkpoint  File /ggsdata/dirdat/bhu/hr/tr000046

                     First Record  RBA 37973983

AND IT STARTED TO PROCEED WITH NEXT TRAIL FILES:

…

GGSCI 20> info P0000037

EXTRACT    P0000037  Last Started 2014-12-03 15:50   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint  File /ggsdata/dirdat/bhu/hr/tr000052

                     2014-12-03 15:00:00.000000  RBA 27682741

11. Start replicat process on QA: (TARGET)

GGSCI  3> start R0000037

Sending START request to MANAGER ...

REPLICAT R0000037 starting

GGSCI  11> info R0000037

REPLICAT   R0000037  Last Started 2014-12-03 15:51   Status RUNNING

Checkpoint Lag       168:56:44 (updated 00:00:00 ago)

Log Read Checkpoint  File /ggsdata/dirdat/test/tr000047

                     2014-11-26 14:56:55.891810  RBA 5711632

…

GGSCI  12> info R0000037

REPLICAT   R0000037  Last Started 2014-12-03 15:51   Status RUNNING

Checkpoint Lag       24:52:53 (updated 00:00:00 ago)

Log Read Checkpoint  File /ggsdata/dirdat/test/tr000050

                     2014-12-02 15:35:41.914083  RBA 25212268

Happy Learning!!!!

WARNING OGG-01519 Oracle GoldenGate Delivery for Oracle, r0000031.prm

Issue/Problem,

2014-11-11 13:06:58 WARNING OGG-01519 Oracle GoldenGate Delivery for Oracle, r0000031.prm: Waiting at EOF on input trail file /s01/ggsdata/dirdat/ig000035, which is not marked as complete; but succeeding trail file /s01/ggsdata/dirdat/ig000036 exists. If ALTER ETROLLOVER has been performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader. 

Reason for this problem

There has been a ETROLLOVER command issued on either Source Extract or Pump, due to which GG has moved to the new trail on the source Extract or Pump. While moving to the new trail file on the source environment, Golden Gate doesn’t make EOF comment or transaction mark on the old trail file. On the replicat site, GG will be waiting for the data or EOF message to come for the current trail from the source environment but another trail file started to write on the replicate site and replicat won’t be knowing it.

We have to manually move the trail file, else it will be staying on the current position.

Step involved in moving to the next trail

#1 Checking the status of current replicat

GGSCI (c959bhfengdnp) 11> info r0000031  

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:04 ago)

Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000035

                     First Record  RBA 12629

When you check at the OS level, we can see 3 more trail files created and replicate won’t be knowing it and replicat will be staying on the sequence number 35.

GGSCI (c959bhfengdnp) 12> exit

ggs@bhuggs1:/s01/11.2: ls -lrt  /s01/ggsdata/dirdat/ig*

-rw-r----- 1 ggs ggs     3523 Nov  5 11:50 /s01/ggsdata/dirdat/ig000026

-rw-r----- 1 ggs ggs     3015 Nov  5 20:44 /s01/ggsdata/dirdat/ig000027

-rw-r----- 1 ggs ggs     1288 Nov  5 20:56 /s01/ggsdata/dirdat/ig000028

-rw-r----- 1 ggs ggs     1422 Nov  5 21:27 /s01/ggsdata/dirdat/ig000029

-rw-r----- 1 ggs ggs     1288 Nov  5 21:34 /s01/ggsdata/dirdat/ig000030

-rw-r----- 1 ggs ggs 99999978 Nov  5 23:01 /s01/ggsdata/dirdat/ig000031

-rw-r----- 1 ggs ggs 99999929 Nov  5 23:05 /s01/ggsdata/dirdat/ig000032

-rw-r----- 1 ggs ggs 99999843 Nov  5 23:09 /s01/ggsdata/dirdat/ig000033

-rw-r----- 1 ggs ggs 60431010 Nov  8 11:35 /s01/ggsdata/dirdat/ig000034

-rw-r----- 1 ggs ggs    12629 Nov 10 14:00 /s01/ggsdata/dirdat/ig000035

-rw-r----- 1 ggs ggs     1560 Nov 10 16:12 /s01/ggsdata/dirdat/ig000036

-rw-r----- 1 ggs ggs     3859 Nov 11 01:58 /s01/ggsdata/dirdat/ig000037

-rw-r----- 1 ggs ggs    17533 Nov 11 14:37 /s01/ggsdata/dirdat/ig000038

ggs@bhuggs1:/s01/11.2: ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.27 19591627 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_141006.1156_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Oct  6 2014 16:45:49

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (c959bhfengdnp) 1> info r0000031 

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:13 ago)

Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000035

                     First Record  RBA 12629

#2 To check whether GG has updated all the transaction in the current trail file;

To check whether any transaction left (or) it has reached the EOF; to check that we will be using send <REPLICAT NAME> logend, if the output is “YES” then it means it has reached the EOF if not then you have to use

'grouptransops 1 & maxtransops 1' in the parameter file of replicat and let it run, see that the replicat has applied all the records in the trail which is currently reading and check again the logend command and this should result in YES.  Once you get Yes then you need to remove the parameter 'grouptransops 1 & maxtransops 1' from the Replicat file(if you are not clear, please don’t use it).

GGSCI (c959bhfengdnp) 2> send r0000031 logend

Sending LOGEND request to REPLICAT R0000031 ...

YES.

#3 Stop the Replicat to change the Sequence number of the trail file.

GGSCI (c959bhfengdnp) 3> stop r0000031

Sending STOP request to REPLICAT R0000031 ...

Request processed.

#4 Change the Sequence number of the trail file and RBA

GGSCI (c959bhfengdnp) 4> alter REPLICAT R0000031, EXTSEQNO 36,  EXTRBA 0

REPLICAT altered.

#5 Just check the current state of the Replicat before starting it

GGSCI (c959bhfengdnp) 1> info r0000031 

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:13 ago)

Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000036

                     First Record  RBA 0

#6 start the Replicat

GGSCI (c959bhfengdnp) 5> start REPLICAT R0000031

Sending START request to MANAGER ...

REPLICAT R0000031 starting

#7 checking the status of the replicat

GGSCI (c959bhfengdnp) 6> info REPLICAT R0000031

REPLICAT   R0000031  Last Started 2014-11-11 14:53   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000038

                     2014-11-11 14:52:42.769737  RBA 17852

It has reached end of the trail file and it is waiting for the information from the source.

Happy Reading :)

To Change Parallelism for a data pump job which is current running

To Change Parallelism for data pump job which is current running

To find the running jobs

SQL> select owner_name, job_name from dba_datapump_jobs;

OWNER_NAME                 JOB_NAME

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

SYSTEM                     GG1

$ expdp attach=SYSTEM.GG1

Export: Release 11.2.0.3.0 - Production on Tue Oct 7 10:47:53 2014

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

Username: system

Password:

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

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Job: GG1

  Owner: SYSTEM                        

  Operation: EXPORT                        

  Creator Privs: TRUE                          

  GUID: 04D26AD77698596AE0534F98340AEADE

  Start Time: Tuesday, 07 October, 2014 9:59:52

  Mode: TABLE                         

  Instance: orq738a2

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/******** parfile=exp.par        

     INCLUDE_METADATA      0

  State: EXECUTING                     

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /s01/oradata1/export/GG_07102014.dmp

    bytes written: 4,096

 

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_LEARNING

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Completed Rows: 27,738

  Worker Parallelism: 1

To Change the Parallelism

Export> parallel=10

After changing the  Parallelism I could see that the value has changed.

To check the status after the existing table export is completed; So please wait for some more time.

 Export> status

Export> status

Job: GG1

  Operation: EXPORT                        

  Mode: TABLE                         

  State: EXECUTING                     

  Bytes Processed: 5,866,922,168

  Percent Done: 18

  Current Parallelism: 10

  Job Error Count: 0

  Dump File: /s01/oradata1/export/GG_07102014.dmp

    bytes written: 5,866,930,176

 

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_STRUCTURES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 2

  Total Objects: 101

  Completed Rows: 18,758

  Worker Parallelism: 1

 

Worker 2 Status:

  Process Name: DW01

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: SYN_GG_SCHEMES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 3 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_AUTHORS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 4 Status:

  Process Name: DW03

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_ABSTRACTS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 5 Status:

  Process Name: DW01

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_DDL_NEWS_SUBSECTIONS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 6 Status:

  Process Name: DW05

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_TEXTS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 7 Status:

  Process Name: DW02

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_DDN_NEWS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 8 Status:

  Process Name: DW07

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_SEQUENCES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 9 Status:

  Process Name: DW03

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_REF_REFERENCES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 10 Status:

  Process Name: DW09

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_STRUCTURES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

Export>

To See the status of running job

$ expdp attach=SYSTEM.GG1

Export: Release 11.2.0.3.0 - Production on Tue Oct 7 14:26:04 2014

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

Username: / as sysdba

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

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Job: GG1

  Owner: SYSTEM                        

  Operation: EXPORT                         

  Creator Privs: TRUE                          

  GUID: 04D26AD77698596AE0534F98340AEADE

  Start Time: Tuesday, 07 October, 2014 9:59:52

  Mode: TABLE                         

  Instance: orq738a2

  Max Parallelism: 10

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/******** parfile=exp.par        

     INCLUDE_METADATA      0

  State: EXECUTING                     

  Bytes Processed: 0

  Current Parallelism: 10

  Job Error Count: 0

  Dump File: /s01/oradata1/export/GG_07102014.dmp

    bytes written: 4,096

 

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_LEARNING

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Completed Rows: 328,461

  Worker Parallelism: 1

Export>

To See the Command prompt

select owner_name,JOB_NAME,OPERATION,JOB_MODE,STATE,DEGREE from dba_datapump_jobs;

SQL> desc dba_datapump_jobs

 Name                                      Null?    Type

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

 OWNER_NAME                                         VARCHAR2(30)

 JOB_NAME                                           VARCHAR2(30)

 OPERATION                                          VARCHAR2(90)

 JOB_MODE                                           VARCHAR2(90)

 STATE                                              VARCHAR2(30)

 DEGREE                                             NUMBER

 ATTACHED_SESSIONS                                  NUMBER

 DATAPUMP_SESSIONS                                  NUMBER