tnsping fails from 10G oracle home

[Dave Chamberlin]

Greetings. I have upgraded the database on an Oracle EBS system (11.5.10.2) from oracle 9.2.0.8 to 10.2.0.4 on hpux. All is working fine except some database links. If I use tnsping on the server - it fails, and does not find the parameter file (sqlnet.ora). If I expicitly set TNS_ADMIN, it works. On my other 9.2.0.8 systems, tnsping works ok WITHOUT setting TNS_ADMIN. How can I get the 10G system to behave the same way? I am accessing the links via TOAD. Any insights?

[TwoProc · Accepted solution]

OK Dave, I just check on my 10.2.0.4 database running the same EBS system - and it works fine without TNS_ADMIN being defined, as it pulls the settings from $ORACLE_HOME/network/admin directory, in the tnsnames.ora file.

So, is $TNS_ADMIN set to $ORACLE_HOME/network/admin? If it was, then NOT having TNS_ADMIN set would work exactly the same as having it set because $TNS_ADMIN would be set to the default, which is $ORACLE_HOME/network/admin.

The 11.5.10.2 system, after running autconfig doesn't want to use $ORACLE_HOME/network/admin for TNS_ADMIN, it wants to use $ORACLE_HOME/network/admin/[instance_name]_[machine_name].

So, lets say your instance name is PROD and your machine name is HP01. Then your $TNS_ADMIN is going to be set to $ORACLE_HOME/network/admin/PRDL_HP01 -
and in *that directory* you'll find your tnsnames.ora file.

That's why when you unset $TNS_ADMIN it no longer works, because the file is no longer in the default directory. Why is this done like this? So that Oracle EBS can have an ORACLE_HOME used for multiple databases, not just one ORACLE_HOME per ERP database instance.

However, for keeping patches straight, I, probably like you, keep all ORACLE_HOMEs separate anyway, so this little maneuver by the ORACLE Apps tech team does nothing for you.

Therefore the solution is VERY SIMPLE:

$> cd $ORACLE_HOME/network/admin
$> ln -s [instance_name]_[machine_name]/tnsnames.ora .
$> ln -s [instance_name]_[machine_name]/sqlnet.ora .
$> ln -s [instance_name]_[machine_name]/listener.ora .

So the commands above, in our example of database PROD hostname HP01 would be:
$> cd $ORACLE_HOME/network/admin
$> ln -s PROD_HP01/tnsnames.ora .
$> ln -s PROD_HP01/sqlnet.ora .
$> ln -s PROD_HP01/listener.ora .

Now, unset TNS_ADMIN
$> unset TNS_ADMIN

and then tnsping your database instance

$> tnsping PROD

... and it should be working now.

[Dave Chamberlin]

Your point is correct. Oracle uses the tnsnames under the SID_host directory - not the one in the admion dir. I made copies into that area for now and it works fine. thanks

[TwoProc]

Copying is a great idea as well, as it gives you a whole 'nother copy of the production files. However, if necessary changes are made to the real files during autoconfig, you'll have to fix them manually, whereas with a symbolic link, you're always running the latest and greatest.

However, this file, though regenerated with each run of autoconfig, shouldn't change much, if ever, except for major patching rounds or upgrades.