Oracle ORA-00022 (invalid session ID; access denied)
Posted: Sun May 23, 2010 7:22 am
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!
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!