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

INST-5174 Oracle Internet Directory - Required Schemas already loaded

After a successful installation of Oracle Internet Directories (OID)(It is part of Oracle identity management). I attempted to remove it and re-install it with a different configuration.

I started to get a error message as INST-5174

I have removed the ODS related schemas in the database using cascade option.

SQL> DROP USER ODS CASCADE;

SQL> DROP USER ODSSM CASCADE;

That didn't not work and the error still appears.

Note:

·        The Database is 11g and it was brand new before installing OID so there is no other data doesn't show any other schemas related to OID.

Finding:

I have reviewed the installation log file and oracle is trying connect to the database and does some pre-check; in that oracle find the below entire in the sys user table

SQL> SELECT mrc_name FROM schema_version_registry where comp_id = 'OID';

MRC_NAME

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

DEFAULT_PREFIX

Table description

SQL> desc schema_version_registry

 Name                                      Null?    Type

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

 COMP_ID                                   NOT NULL VARCHAR2(30)

 COMP_NAME                                          VARCHAR2(255)

 MRC_NAME                                  NOT NULL VARCHAR2(30)

 MR_NAME                                            VARCHAR2(30)

 MR_TYPE                                            VARCHAR2(30)

 OWNER                                              VARCHAR2(30)

 VERSION                                            VARCHAR2(30)

 STATUS                                             VARCHAR2(11)

 UPGRADED                                           CHAR(1)

 START_TIME                                         TIMESTAMP(6)

 MODIFIED                                           TIMESTAMP(6)

I have found some other rows which are present in the table;

SQL> select * from schema_version_registry where COMP_ID='OID';

OID

Oracle Internet Directory

DEFAULT_PREFIX                 OID

IM                             ODS

11.1.1.7.0                     VALID       N

30-SEP-14 09.09.07.464367 AM

SQL> delete schema_version_registry where COMP_ID='OID';

1 row deleted.

SQL> commit;

Commit complete.

I have used the delete statement to delete the row; I HAVE DONE THIS IN THE DEV SYSTEM. IF YOU ARE DOING IT IN THE PRODUCTION SYSTEM, Check with oracle support.

Happy Learning