community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

ORA-12592 TNS Bad Packet Error When Writing from .YXDB

Meteor

I have issues writing data to my Oracle database when the source is a .YXDB, and it seems especially likely to occur for larger files (>100MB compressed).

 

I tried different transaction sizes and also modifying the Send_Timeout parameter in sqlnet.ora, but to no success.

 

Finally, I tried writing the YXDB to a .CSV first, and that worked.

 

Any ideas why I might be having this issue?

 

 

Alteryx Certified Partner
Alteryx Certified Partner

@jbrider,

 

While I don't have a direct answer for you, I might suggest trying a BLOCK UNTIL DONE tool just prior to the output tool.

 

Cheers,
Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Nebula
Nebula

Hey @jbrider

 

What was the error message you were getting?   I can mock this scenario up on SQL to see if I get the same?

Like Mark said on the thread - you can try putting a choke on the process using the throttle tool, but not sure if this would help?

 

Let me know the error message and we can iterate from there

 

Cheers

Sean

Meteor

 

This is the error message I'm getting:

Output Data (3) DataWrap2OCI::SendBatch: ORA-12592: TNS:bad packet¶¶

 

Also, I tried adding a Block Until Done to get all of the data read in before it started writing out, but the same error still occurred.

I also looked at Throttle, but it only allows 60k records/minute max.  I have 12M rows to low, so it is a little bit time-prohibitive.

Nebula
Nebula

Hi Moderator team ( @SophiaF@ChristineB@JessicaS)

Would you mind passing this on to one of the team that deals with the connectors - this does seem to be a network-level issue (given the description of the error saying "Bad packet")?

Meteoroid

Were you able to resolve this issue? I appear to have the same problem.

Atom

We are having this same issue as well, has anyone found an appropriate resolution?

 

Alteryx Certified Partner
Alteryx Certified Partner

I've googled the problem and found articles that point to network issues.  

 

https://kb.informatica.com/solution/23/Pages/40/262530.aspx

 

Solution

To resolve this issue, add SQLNET.SEND_TIMEOUT=<seconds> to sqlnet.ora on the server side.

In addition, contact your DBA for exact value.

Example

SQLNET.SEND_TIMEOUT=600

More Information

SQLNET.SEND_TIMEOUT is used to specify the time, in seconds, for a database server to complete a send operation to clients after connection establishment.

 
For environments in which clients shut down on occasion or abnormally, setting this parameter is recommended. If the database server is unable to complete a send operation in the time specified, then it logs an ORA-12535: TNS:operation timed out and ORA-12608: TNS: Send timeout occurred to thesqlnet.log file. Without this parameter, the database server may continue to send responses to clients that are unable to receive data due to a downed computer or is in a busy state.
 
You can also set this parameter on the client-side to specify the time, in seconds, for a client to complete the send operations to the database server after connection establishment. Without this parameter, the client may continue to send requests to a database server already saturated with requests.
 

 

https://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm#NETRF006

 

Cheers,

Mark

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteoroid

@jbyrne4 Unfortunately no one replied to my post but after some research I found that the same workflow does not error out on my laptop but it errors out on the Alteryx server. I think tried other ETL tools recreating the same process and they all worked. So it was fair to say it was not oracle or the network. It had something to do with the Alteryx server. Are you running yours on the Alteryx server? I was trying to import a large file but added 2 columns using the formula tool before it imported. I removed the formula tool and imported the file as is then completed the 2 additional columns using the in database formula tool and it worked. After hours of frustration it makes little sense that this was the solution.

 

 

Meteor

I have tried all kinds of different hacks to get it to work...

-Block Until Done to read in all of the data before writing

-Lowering Transaction Size (just ends up failing at the same rate and you end up having like 800k/10M records of a file written to the database, which is a mess)

-Running from Server, Running from Workstation

-Reading from CSV File

-Reading from YXDB

 

My DBA's could not see any issues with the network.  Additionally, with programs like SQL Developer loader, I can load without fail...albeit more slowly than Alteryx and not something I can schedule on the server like I need.

 

Alteryx is really the only ETL tool I can use at this time, so it's frustrating that larger write jobs are extremely unreliable.

 

 

 

Labels