Here is the progress of the question:
March 3: Oracle816 server was installed on ORA-HOME1 on Server1. Database ORK1 was created. Configured tnsnames so that SQL PLUS can connect to ORK1 on server1 and ORC2 on server2.
March 5: Some Database Admin tools like EZSQL and PLSQL Developer were installed on Server1. Everything OK.
March 22: Oracle Enterprise Manager with Tuning Pack was installed on ORA-HOME2 on server1. The installation also created a seperate tnsnames file.
Now, the DB tools can not connect to ORK2 on server2. Probably because the new TNSNAMES created on March 22 causes some problem.
Question: if multi-Oracle Homes have multiple TNSNAMES, which TNSNAMES will DB admin tool select to use? Can I change it?
Operating system please!
If operating system WINDOWS NT then,
First installed oracle home is default home! The next installed will work with that installed tools . For example
Oralce 8.0.6 ---
sqlplus will use tnsnames from this home.
If you installed oracle 8.1.6 and oem then oem work sheet will use tnsnames from the new home. I got some funny problem when i install db artisan. When i select database names from tnsnames.ora in this tool it selected from old home. But if i selected the names, I got tnsnames error. Then i directly i enterd a connect string name from new oracle home tnsnames.ora and it is connected. So when it's creating the data source it's bringing information from old home, But when it's connecting to database it's looking into new home tnsnames.ora. Any way one shortcut is create 2 connect strings and put each one in each tnsnames.ora
service_a :sid =database1
service_b :sid =database2
Put service_a in old home tnsnames.ora and
service_b in new home tnsnames.ora and find out which one is working.
Other why's put same entry's in all tnsnames.ora.
Check Path in env.
The OS is NT.
Thank you for your suggestion. It works. But......
Is there any way that I can force an application to use a specific TNSNAMES file if there are more than one Oracle HOMEs exist?
Copy the tnsnames.ora that you wanted to use in all the homes networking directory(thats OH/net80/admin). Whatever product you use it resolves the service from one tnsnames.ora.
For Release2 (8.1.6) tnsnames.ora will be read in the following order:
1. current working dir
2. (/etc/tnsnames.ora for HP and /var/opt/oracle/tnsnames.ora for SUN)
4. default dir, ie, $ORACLE_HOME\network\admin
Hope it can help
in addition to cmai:
on NT: TNS_ADMIN registry key IN HOME of the product(net8) used to connect by client app. Net8 used to connect depends on tool: some tools are quite smart in selecting net8 library to use.
I just found out that you can also use Oracle Home Selector to specify which Oracle Home you want it be the derault. The the TNSNAMES will be the one under the default home. Thanks!
If you want to know for sure and what to switch it yourself go straight to the windows registry. Don't rely on the Oracle environment swither, it's flaky.
Run regedit utility
Then change the default home and last home based on the number of Oracle Homes you have.
Below this you will see HOME0, HOME1, HOME2, etc....