Cleaning up abnormal stopped (orphaned) datapumb jobs

Reason : Some time data pumb jobs marked as orphaned (stopped abnormally) and job information still remain in database. On starting datapump job start, job name table automatically created in database for keeping job related information. All data export and import operation will aborted.

Solution : Find orphaned datapumb jobs, delete metadata, start job again. Step by step cleanup orphaned datapump jobs is discussed below.

1. First check the orphaned datapump jobs status .

SELECT * FROM dba_datapump_jobs;

2. Check the state field. For orphaned jobs the state will be NOT RUNNING
3. Drop the master table (holding metadata).

DROP TABLE SYSTEM.FULLEXP1;
DROP TABLE SYSTEM.FULLEXP2;

Check job status now !

SELECT * FROM dba_datapump_jobs;

4. In this stage you did not get any orphaned jobs if the jobs have a master table. If there are still jobs listed in dba_datapump_jobs do cleanup process like below.

DECLARE
job1 NUMBER;
BEGIN
job1 := DBMS_DATAPUMP.ATTACH('EXPFULL','SYSTEM');
DBMS_DATAPUMP.STOP_JOB (job1);
END;
/
DECLARE
job2 NUMBER;
BEGIN
job2 := DBMS_DATAPUMP.ATTACH('EXPFULL','SYSTEM');
DBMS_DATAPUMP.STOP_JOB (job2);
END;
/

In my case, trace file contents as:

Trace file /oradisk/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_dm00_11242.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oradisk/oracle/product/11.2.0/db
System name: Linux
Node name: primarydb
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 04:15:13 EDT 2009
Machine: x86_64
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 11242, image: oracle@primarydb (DM00)


*** 2010-08-17 10:12:02.399
*** SESSION ID:(1188.9) 2010-08-17 10:12:02.400
*** CLIENT ID:() 2010-08-17 10:12:02.400
*** SERVICE NAME:(SYS$USERS) 2010-08-17 10:12:02.400
*** MODULE NAME:(Data Pump Master) 2010-08-17 10:12:02.400
*** ACTION NAME:(EXPFULL) 2010-08-17 10:12:02.400

KUPC: Setting remote flag for this process to FALSE
prvtaqis - Enter
prvtaqis subtab_name upd
prvtaqis sys table upd
KUPP: Initialization complete for master process DM00

*** 2010-08-17 10:12:04.018
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 6
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 6
kwqberlst ascn 392980029 lascn 22

Troubleshooting ORA-16191, ORA-01017

In 11gR1, 11gR2, ambience up log carriage for DataGuard, Streams, CDC, etc., you are acceptable to run into this annoyance. No amount how anxiously you accomplish the SYS passwords in the passwordfile the aforementioned byte-for-byte on both the antecedent and destination hosts, the antecedent archiver will abort aggravating to log in to the destination DB with these letters accounting to the active log:

ORA-01017: invalid username/password; logon denied
ORA-16191: Primary log shipping client not logged on standby

The band-aid is to actualize the passwordfiles with orapwd application the altercation 'ignorecase=y':

% orapwd file=orapwORCL password=sys_password ignorecase=y entries=5 force=y

This affair has been accurate by several bloggers and appointment participants, but there is still no agenda in Metalink as of today. I'm not abiding what allotment of the codepath contains this bug or if conceivably this is one of those that would be addressed with the "not a bug" acknowledgment from dev. You will apparently accept begin this commodity afterwards afterward the instructions in the absurdity documentation:

ORA-3136 WARNING : Inbound Connection Timed Out in alert log

Oracle Database 10.2 to onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.This timeout restriction was introduced to combat Denial of Service (Dos) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.

There can be following main causes/reasons for this error:
1. Server gets a connection request from a malicious client which is not supposed to connect to the database , in which case the error thrown is the correct behavior. You can get the client address for which the error was thrown via sqlnet log file.
2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If its taking longer period, then its worth checking all the below points before going for the workadound:

1. Check whether local connection on the database server is sucessful & quick.
2. Check antivirous, firewall, ensore its not preventing clients applications, which requied database connectivity
3. If local connections are quick ,then check for underlying network delay with the help of your network administrator.
4. Check whether your Database performance has degraded by anyway.
5. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them resolved first. These critical errors might have triggered the slowness of the database server.

Solution: metalink note: 793259.1 & 465043.1

In server side sqlnet.ora file add following:
SQLNET.INBOUND_CONNECT_TIMEOUT = 120

In listener.ora file add following:
INBOUND_CONNECT_TIMEOUT_LISTENER = 110