Oracle ORA-00022 (invalid session ID; access denied)

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
DS_FocusGroup
Premium Member
Premium Member
Posts: 197
Joined: Sun Jul 15, 2007 11:45 pm
Location: Prague

Oracle ORA-00022 (invalid session ID; access denied)

Post by DS_FocusGroup »

Hi,

We've suddenly run into this major issue where all of our production jobs that have an Oracle Stage in the source are failing with the following error:

<Stage Name>,0: (aptoci.C:355). Message: ORA-00022: invalid session ID; access denied

....
<Stage Name>,0: (aptoci.C:355). Message: ORA-03113: end-of-file on communication channel
(aptoci.C:491). Message: ORA-03114: not connected to ORACLE


Job Design:
Oracle Enterprise Stage -> Transformer -> Teradata Connector Stage

Observations:
1) The problem seems to be only with the Oracle stage as there are other jobs where the source is a flat file and these are successfully loading into the target Teradata table.

2) The error is not specific to one particular DataStage project as I have tested these jobs across multiple environments (dev, tst, prod) with same results.

3) The jobs were running fine till end of last week and I haven't been able to come up with any explanation as to what's caused this to occur. To my knowledge, nothing has changed on either the DataStage server or at the Oracle end.

4)I'm able to connect to Oracle from the ETL server itself using the other client tools (SQL Plus) and also able to do a TNSPING, so this does not seem to be a connectivity issue b/w the servers.

5) Another observation that has me stumped is the inconsistency of the behaviour - I can browse the data using the "View Data" option in the Oracle Enterprise Stage, but when I execute the same job (using the same connection parameters), it fails with the aforementioned error!

Are there any environment variable settings in specific that I need to check?

Any ideas or suggestions are most welcome!
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Let's see... by the numbers.

1) Good, that helps narrow things down.

2) Good, that lets us know that it is probably Oracle.

3) Keep looking. Obviously, something has changed.

4) Helpful information, but not conclusive. The EOF issue is probably a red herring, an artifact of the true issue.

5) View Data works quite differently from what happens under the covers when the job runs, so we've seen all those same kinds of inconsistencies before. Pay that no mind.

Me, I would concentrate on the first message you posted: ORA-00022: invalid session ID; access denied. And definitely (and heavily) involve your DBA if you haven't already done so.

:?: Does the "update action" make any difference in your Oracle issue? Meaning do normal insert/update operations succeed and bulk load operations fail? Or vice-versa? Or does nothing work? I get the impression it is the latter, the nothing, but wanted to explicitly ask.

As stated earlier - if you literally mean all Oracle access across all DataStage servers suddenly stopped working at the same time - I would be giving Oracle your full attention and the Evil Eye.
-craig

"You can never have too many knives" -- Logan Nine Fingers
DS_FocusGroup
Premium Member
Premium Member
Posts: 197
Joined: Sun Jul 15, 2007 11:45 pm
Location: Prague

Post by DS_FocusGroup »

Thanks Craig. The Oracle Stage is being used as a source so not many options/operations to play with.

As far as getting the DBA onboard is concerned, tried that but didn't get much out of it - "Since you can connect from your ETL server to Oracle using the same user ID albeit a different application (SQL Plus), this is not an oracle-related issue and is specific to your application's configuration (DataStage)". Not sure how what exactly I should have him check...

I've also tried using explicit values for the connection params (Server, User & Pass) instead of parameter-set in the original job to discount any issues with the latter (in case the value file somehow got corrupted). Didn't work :?

What's really got me stumped is that some of the jobs run OK in one instance and rerunning the same job with same settings causes the reported error! Doesn't make sense!

Any system-level env variables I need to be aware of?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

DS_FocusGroup wrote:As far as getting the DBA onboard is concerned, tried that but didn't get much out of it - "Since you can connect from your ETL server to Oracle using the same user ID albeit a different application (SQL Plus), this is not an oracle-related issue and is specific to your application's configuration (DataStage)".
Classic cop-out... and unacceptable. Way to trouble-shoot, there, DBA man. :roll:

Seems like a little more information has comes to light... that or my dim bulb just brightened. I thought nothing ever worked (ran) post the nothing changed incident? If one sometimes works and sometimes doesn't with no changes on your part, that still screams ORACLE to me but means it is intermittent, perhaps load dependant. Maybe network but I'd still set my primary target as Oracle. Those are the worst to track down. If things never worked, that would be easier.

See if your DBA can monitor your connections for you. Sounds like it shouldn't be too hard for you to catch something in that net, perhaps what he sees will shed some light on the subject.

Food for thought... changes... anything change on the Oracle server that would have ramped up its usage? Anything on the Oracle side that day, even "unrelated" changes - new apps hitting it, new users, report writers, patches, anything? Does the volume of what you do from the DataStage side affect this? Meaning if you have nothing else going on, DataStage-wise, and you run an Oracle sourcing job, does it still fail? Intermittent problems like you are describing are almost always resource related, the problem is finding the resource that is getting exhausted.

Let's save any "system level" changes / settings discussion for last. I don't think it will come into play in a SEOSW scenario where you know there have been no high-level DataStage tweaks made.

Suddenly Everything Oracle Stopped Working
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply