ORA-12571: TNS:packet writer failure

shails · Post by **shails** » Wed Sep 16, 2009 11:11 am

I am running a Job which reads the data from a dataset and writes the data to a Oracle Table. There are no column level transformations or filters in the Job. This is a simple Job which inserts/updates the source data to the target table on a daily basis.

Dataset -> Transformer -> Oracle Enterprise Stage

The Job is aborting consistently with the following error.
ORA-12571: TNS:packet writer failure
and when I restart the Job, the Job runs successfully.

We turned on the tracing at the database level/network level/Job level but no issues were seen in the trace files by the Oracle/IBM customer support. Right now Information Server is on windows and we recently migrated to AIX and tested the Jobs on AIX but there are no TNS errors reported on AIX.
Today when I ran the Jobs on windows every job that I start is failing with TNS error and our DBA informed me that he is seeing blocking locks on the tables. Does the blocking locks cause this error? If so why is the same job with the same data running fine on AIX? Is this error environment related(Windows Vs AIX) or network related? I worked on multiple projects but haven't encountered this error in my earlier projects.

Moving to AIX will help us overcome this issue but I want to understand the root cause of this problem. Can someone help me out?

Thanks in advance for your inputs.

Sreenivasulu · Post by **Sreenivasulu** » Wed Sep 16, 2009 11:54 am

Its nothing to do with datastage

TNSPacket failure is a connectivity issue in your network wherin the ping does not return packets .

Regards
Sreeni

shails · Post by **shails** » Wed Sep 16, 2009 12:07 pm

Thanks for your reply.
But why other applications that connect to the same database server are not throwing the same error.

Sreenivasulu · Post by **Sreenivasulu** » Wed Sep 16, 2009 12:16 pm

Sorry if i have put it wrongly.
TNSPacket error means you are not able to connect to the database from datastage.

Have you installed the oracle client on the datastage server ?

Regards
Sreeni

shails · Post by **shails** » Wed Sep 16, 2009 12:31 pm

Oracle client is installed on the Datastage Server. If I restart the Job, the Job completes successfully.

shails · Post by **shails** » Thu Sep 17, 2009 1:07 pm

Did anyone encounter this issue earlier?

Sreenivasulu · Post by **Sreenivasulu** » Fri Sep 18, 2009 3:12 am

Have encoutered this issue many times but has only one solution.
It has nothing to do with datastage.

Regards
Sreeni

shails · Post by **shails** » Fri Sep 18, 2009 9:28 am

Since it has nothing to do with datastage, Can you please tell me what can be done to avoid this error? Any database related settings/ network settings that can be configured?
Thanks.

chulett · Post by **chulett** » Fri Sep 18, 2009 9:33 am

Have you tried googling for "ORA-12571", there's all kinds of resources out there for this error and the things you can try to trouble-shoot it.

shails · Post by **shails** » Fri Sep 18, 2009 9:46 am

Thanks Craig.
We tried all possible options but the oracle trace files doesn't tell anything about the problem.