Tuesday, October 7, 2014

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

Wednesday, October 1, 2014

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