Error while trying to retrieve text for error ORA-12154
Moderators: chulett, rschirm, roy
Error while trying to retrieve text for error ORA-12154
Hi, please advice on the below error. Our server has just change its ip from xxx.xxx.xx.x to aaa.aaa.aa.a
I've verified our tnsnames.ora file and it is correct but when we run our datastage job, we hit the below error. Please advice on what to do, thank you
what else do i need to check?
logica_online_rchg..LOGICA_TRANS_CIDMDEV: Oracle Error - OCI_INVALID_HANDLE
Error while trying to retrieve text for error ORA-12154
I've verified our tnsnames.ora file and it is correct but when we run our datastage job, we hit the below error. Please advice on what to do, thank you
what else do i need to check?
logica_online_rchg..LOGICA_TRANS_CIDMDEV: Oracle Error - OCI_INVALID_HANDLE
Error while trying to retrieve text for error ORA-12154
dwcw
-
- Participant
- Posts: 24
- Joined: Mon Jul 12, 2004 10:16 am
Re: Error while trying to retrieve text for error ORA-12154
Check with do you have an entry "TNSNAMES.ora" in SQLNET.ora file. Since you have modified the IP (server) also check the relative information in your drivers file. Also check your datastage Oracle configuration file (tnsnames.ora).
Would need a wee bit more information to be able to help. First off, I don't see how changing your DataStage server's IP address would affect your ability to connect to Oracle instances on other servers. Those on the same server could be if your tnsname uses IP addresses.
So... all jobs that access Oracle fail? One job? Only jobs that access a certain instance? Please be more specific about the nature of your 'urgent' issue.
ps. I wouldn't focus on the ORA-12154 error, it's probably a result of the OCI_INVALID_HANDLE error. And the action recommended when you look that OCI_INVALID_HANDLE error up says: "An internal error not normally visible to the user. Contact Worldwide Customer Support.". At worst case, open a TAR with Oracle Support and see what they can track down.
So... all jobs that access Oracle fail? One job? Only jobs that access a certain instance? Please be more specific about the nature of your 'urgent' issue.
ps. I wouldn't focus on the ORA-12154 error, it's probably a result of the OCI_INVALID_HANDLE error. And the action recommended when you look that OCI_INVALID_HANDLE error up says: "An internal error not normally visible to the user. Contact Worldwide Customer Support.". At worst case, open a TAR with Oracle Support and see what they can track down.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks for your reply.
1 job only fail. This particular job access the DB[CIDMDEV]. I've tried to restart the DB. Still not working. Tried to restart DataStage and still not working.
I've already tried alternative way to test the DB but still unable to run my job.
Tried using sqlplus to connect , successful
Tried tnsping my DB, successful
Urgent as in i need to transform files quickly. Please advice thank you.
1 job only fail. This particular job access the DB[CIDMDEV]. I've tried to restart the DB. Still not working. Tried to restart DataStage and still not working.
I've already tried alternative way to test the DB but still unable to run my job.
Tried using sqlplus to connect , successful
Tried tnsping my DB, successful
Urgent as in i need to transform files quickly. Please advice thank you.
dwcw
Still looking for details. Where does this database live? On the DataStage server? Are there other jobs that access this instance that work and this one job the only one that fails? Or is this 1 job literally the only job to access this instance?
Both tnsping and sqlplus were successful when executed from where? Your PC or from the DataStage server? When you run these tools from the server, is the environment the same as your DataStage job - meaning, did you source your dsenv file first then run them?
Urgent support comes from your Official support provider, that's why your company pays them the big bucks, after all. This is a volunteer site where people post when they can. Me, I'm offline and heading into work after this post, for example.
Both tnsping and sqlplus were successful when executed from where? Your PC or from the DataStage server? When you run these tools from the server, is the environment the same as your DataStage job - meaning, did you source your dsenv file first then run them?
Code: Select all
cd `cat /.dshome`
. ./dsenv
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
CIDMDEV DB is located at the same box with the DataStage server. There is one and only job that connect to the database.
the sqlplus and tnsping was executed at DataStage server.
dsadmin@dwdev/ardent/Ascential/DataStage/DSEngine>. ./dsenv
dsadmin@dwdev/ardent/Ascential/DataStage/DSEngine>tnsping CIDMDEV
TNS Ping Utility for HPUX: Version 10.1.0.2.0 - Production on 29-NOV-2007 23:31:02
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.64.104)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CIDMDEV)))
OK (0 msec)
Please advice.
Thank you chulett
the sqlplus and tnsping was executed at DataStage server.
dsadmin@dwdev/ardent/Ascential/DataStage/DSEngine>. ./dsenv
dsadmin@dwdev/ardent/Ascential/DataStage/DSEngine>tnsping CIDMDEV
TNS Ping Utility for HPUX: Version 10.1.0.2.0 - Production on 29-NOV-2007 23:31:02
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.64.104)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CIDMDEV)))
OK (0 msec)
Please advice.
Thank you chulett
dwcw
Instead of running a job, try to import metadata using DS Manager and see if DS can connect to your instance. It appears that something else is different than just an IP change. DS works thru the OCI client to connect, and the only pieces required is the dsenv file points its ORACLE_HOME to a valid OCI installation. There may have been some permissions changed or something else. Start with trying to import metadata.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Use DS Manager, go to the menu-->Import-->Table Definitions-->PlugIn Metadata-->Oracle 9. Pick the DSN from the drop-down list, enter the login credentials.
This is standard development stuff, it's fairly well documented how to import table definitions, one of the first steps in any job designing.
This is standard development stuff, it's fairly well documented how to import table definitions, one of the first steps in any job designing.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
hi,
I've done the below
Use DS Manager, go to the menu-->Import-->Table Definitions-->PlugIn Metadata-->Oracle 9. Pick the DSN from the drop-down list, enter the login credentials
BUT hit the below error:
Error while trying to retrieve text for error ORA-12154
There is no drop-down list for me to select, therefore i enter the Database Source Name manually.
Please advice. The root cause is
Error while trying to retrieve text for error ORA-12154
I've checked my tnsnames.ora. It's configured correctly.
I've done the below
Use DS Manager, go to the menu-->Import-->Table Definitions-->PlugIn Metadata-->Oracle 9. Pick the DSN from the drop-down list, enter the login credentials
BUT hit the below error:
Error while trying to retrieve text for error ORA-12154
There is no drop-down list for me to select, therefore i enter the Database Source Name manually.
Please advice. The root cause is
Error while trying to retrieve text for error ORA-12154
I've checked my tnsnames.ora. It's configured correctly.
dwcw
If there's no drop down list then DS can't see the tnsnames.ora file. You need to check the dsenv file and make sure the $ORACLE_HOME setting correctly points to the tnsnames.ora, which should be underneath the OCI client directory in $ORACLE_HOME/network/admin. The dsenv file is in the DS install directory, cut and paste this into your unix telnet session and verify:
You need to fix the dsenv file to point correctly, then restart the DS engine to pick up the new environment settings.
Code: Select all
cd `cat /.dshome`
pg dsenv
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
thanks for the help kcbland
My dsenv script looks like this :
# PLATFORM SPECIFIC SECTION
set +u
if [ -z "$DSHOME" ] && [ -f "/.dshome" ]
then
DSHOME=`cat /.dshome`
export DSHOME
fi
if [ -z "$DSHOME" ]
then
DSHOME=/ardent/Ascential/DataStage/DSEngine; export DSHOME
fi
if [ -z "$APT_ORCHHOME" ]
then
APT_ORCHHOME=; export APT_ORCHHOME
fi
if [ -z "$UDTHOME" ]
then
UDTHOME=/ardent/Ascential/DataStage/ud41; export UDTHOME
UDTBIN=/ardent/Ascential/DataStage/ud41/bin; export UDTBIN
fi
if [ -n "$DSHOME" ] && [ -d "$DSHOME" ]
then
ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
HOME=${HOME:-/}; export HOME
#LANG="<langdef>";export LANG
#LC_ALL="<langdef>";export LC_ALL
#LC_CTYPE="<langdef>";export LC_CTYPE
#LC_COLLATE="<langdef>";export LC_COLLATE
#LC_MONETARY="<langdef>";export LC_MONETARY
#LC_NUMERIC="<langdef>";export LC_NUMERIC
#LC_TIME="<langdef>";export LC_TIME
#LC_MESSAGES="<langdef>"; export LC_MESSAGES
#LD_PRELOAD must be unset on HP-UX 11.00
if [ 1111 -le `uname -r | cut -f2 -d.``uname -r | cut -f3 -d.` ]
then
#SHLIB_PATH=/ardent/Ascential/DataStage/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RI
SC:$DSHOME/java/jre/lib/PA_RISC/hotspot:$SHLIB_PATH
SHLIB_PATH=/ardent/Ascential/DataStage/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RIS
C2.0:$DSHOME/java/jre/lib/PA_RISC2.0/hotspot:/oracle/product/10.1.0/lib32:/oracle/product/10.1.0/lib:/oracle/product/10.1.0/bi
n:$SHLIB_PATH
export SHLIB_PATH
#LD_PRELOAD=$DSHOME/java/jre/lib/PA_RISC/hotspot/libjvm.sl
LD_PRELOAD=$DSHOME/java/jre/lib/PA_RISC2.0/hotspot/libjvm.sl
export LD_PRELOAD
else
SHLIB_PATH=/oracle/product/10.1.0/lib32:/oracle/product/10.1.0/lib:/oracle/product/10.1.0/bin:/ardent/Ascentia
l/DataStage/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RISC:$DSHOME/java/jre/lib/PA_RISC/hotspot:$SHL
IB_PATH
export SHLIB_PATH
fi
fi
Where do i need to change it? My $ORACLE_HOME is at "/oracle/product/10.1.0/network/admin"
When i want to reset my DataStage i face the following error:
# cd $DSHOME
ksh: /ardent/uv: not found
Therefore i have difficulties is resetting the DataStage. Please advice thank you. Please let me know if you need more info.
My dsenv script looks like this :
# PLATFORM SPECIFIC SECTION
set +u
if [ -z "$DSHOME" ] && [ -f "/.dshome" ]
then
DSHOME=`cat /.dshome`
export DSHOME
fi
if [ -z "$DSHOME" ]
then
DSHOME=/ardent/Ascential/DataStage/DSEngine; export DSHOME
fi
if [ -z "$APT_ORCHHOME" ]
then
APT_ORCHHOME=; export APT_ORCHHOME
fi
if [ -z "$UDTHOME" ]
then
UDTHOME=/ardent/Ascential/DataStage/ud41; export UDTHOME
UDTBIN=/ardent/Ascential/DataStage/ud41/bin; export UDTBIN
fi
if [ -n "$DSHOME" ] && [ -d "$DSHOME" ]
then
ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
HOME=${HOME:-/}; export HOME
#LANG="<langdef>";export LANG
#LC_ALL="<langdef>";export LC_ALL
#LC_CTYPE="<langdef>";export LC_CTYPE
#LC_COLLATE="<langdef>";export LC_COLLATE
#LC_MONETARY="<langdef>";export LC_MONETARY
#LC_NUMERIC="<langdef>";export LC_NUMERIC
#LC_TIME="<langdef>";export LC_TIME
#LC_MESSAGES="<langdef>"; export LC_MESSAGES
#LD_PRELOAD must be unset on HP-UX 11.00
if [ 1111 -le `uname -r | cut -f2 -d.``uname -r | cut -f3 -d.` ]
then
#SHLIB_PATH=/ardent/Ascential/DataStage/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RI
SC:$DSHOME/java/jre/lib/PA_RISC/hotspot:$SHLIB_PATH
SHLIB_PATH=/ardent/Ascential/DataStage/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RIS
C2.0:$DSHOME/java/jre/lib/PA_RISC2.0/hotspot:/oracle/product/10.1.0/lib32:/oracle/product/10.1.0/lib:/oracle/product/10.1.0/bi
n:$SHLIB_PATH
export SHLIB_PATH
#LD_PRELOAD=$DSHOME/java/jre/lib/PA_RISC/hotspot/libjvm.sl
LD_PRELOAD=$DSHOME/java/jre/lib/PA_RISC2.0/hotspot/libjvm.sl
export LD_PRELOAD
else
SHLIB_PATH=/oracle/product/10.1.0/lib32:/oracle/product/10.1.0/lib:/oracle/product/10.1.0/bin:/ardent/Ascentia
l/DataStage/branded_odbc/lib:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/PA_RISC:$DSHOME/java/jre/lib/PA_RISC/hotspot:$SHL
IB_PATH
export SHLIB_PATH
fi
fi
Where do i need to change it? My $ORACLE_HOME is at "/oracle/product/10.1.0/network/admin"
When i want to reset my DataStage i face the following error:
# cd $DSHOME
ksh: /ardent/uv: not found
Therefore i have difficulties is resetting the DataStage. Please advice thank you. Please let me know if you need more info.
dwcw
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The uv command is in bin, as are all the other executables. In your case, I guess this is /ardent/bin/uv.
It doesn't really matter where in dsenv you set your Oracle environment variables, though after any DataStage-specific ones seems to be somewhat of a convention. Also make sure that PATH and LD_LIBRARY_PATH include the relevant Oracle references, and that the 32-bit Oracle library is found before the 64-bit Oracle library.
It doesn't really matter where in dsenv you set your Oracle environment variables, though after any DataStage-specific ones seems to be somewhat of a convention. Also make sure that PATH and LD_LIBRARY_PATH include the relevant Oracle references, and that the 32-bit Oracle library is found before the 64-bit Oracle library.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.