Error while trying to retrieve text for error ORA-12154

[dannywcw]

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

[Govindarajan]

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).

[dannywcw]

Nope. that didnt help. Please help. URGERNT !!!

[chulett]

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.

[dannywcw]

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.

[chulett]

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?

Code: Select all

cd `cat /.dshome`
. ./dsenv

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.

[dannywcw]

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

[kcbland]

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.

[dannywcw]

hi kcbland, thanks for the reply. Can you please show me on how to perform this? import metadata? Any reference to refer to?

[kcbland]

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.

[dannywcw]

ok, let me try 1st.

[dannywcw]

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.

[kcbland]

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:

Code: Select all

cd `cat /.dshome`
pg dsenv

You need to fix the dsenv file to point correctly, then restart the DS engine to pick up the new environment settings.

[dannywcw]

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.

[ray.wurlod]

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.