There are multiple TNSNames files that can also cause connection errors

Today handled a tnsnames file configuration problem, let me think of a tnsnames file related knowledge points

TNSNames files can exist under multiple paths, Oracle selects them according to the order

Now let's simulate

$HOME/.tnsnames.ora

/etc/tnsnames.ora

$ORACLE _home/network/admin/tnsnames.ora+++ We will configure this file by default.

The order of precedence for these three positions

First, the correct tnsnames configuration files are stored in the above three places

[Oracle@localhost admin]$ ll $HOME/.tnsnames.ora
-RW-R-----1 Oracle oinstall 175 DEC 22:02/home/oracle/.tnsnames.ora
[Oracle@localhost admin]$ Ll/etc/tnsnames.ora
-RW-R-----1 Oracle oinstall 175 DEC 22:02/etc/tnsnames.ora
[Oracle@localhost admin]$ Ll/opt/app/oracle/product/11.2.0/network/admin/tnsnames.ora
-RW-R-----1 Oracle oinstall 175 DEC 21:59/opt/app/oracle/product/11.2.0/network/admin/tnsnames.ora

+ + + Normal access is available

[Oracle@localhost admin]$ Sqlplus Scott/tiger@goolen
Sql*plus:release 11.2.0.1.0 Production on Thu Dec 26 22:03:10 2013
Copyright (c) 1982, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

Sql>
Sql> exit

++++ First, we change the $home/.tnsnames.ora file to the wrong, connect again

[Oracle@localhost admin]$ Sqlplus Scott/tiger@goolen
Sql*plus:release 11.2.0.1.0 Production on Thu Dec 26 22:03:29 2013
Copyright (c) 1982, Oracle. All rights reserved.
ERROR:
Ora-12154:tns:could not resolve the connect identifier specified

++++ Connection Error

++++ After you delete $home/.tnsnames.ora, you can connect normally

[Oracle@localhost admin]$ rm-rf $HOME/.tnsnames.ora
[Oracle@localhost admin]$ Sqlplus Scott/tiger@goolen
Sql*plus:release 11.2.0.1.0 Production on Thu Dec 26 22:03:47 2013
Copyright (c) 1982, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

Sql> exit

+++++ the above description in three paths $home/.tnsnames.ora is the first choice

+ + + Second, we'll change the/etc/tnsnames.ora file to the wrong

[Oracle@localhost admin]$ Sqlplus Scott/tiger@goolen
Sql*plus:release 11.2.0.1.0 Production on Thu Dec 26 22:04:04 2013
Copyright (c) 1982, Oracle. All rights reserved.
ERROR:
Ora-12154:tns:could not resolve the connect identifier specified

+ + + Connection error

+ + + DELETE/etc/tnsnames.ora

[Oracle@localhost admin]$ Sqlplus Scott/tiger@goolen
Sql*plus:release 11.2.0.1.0 Production on Thu Dec 26 22:04:45 2013
Copyright (c) 1982, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

Sql> exit

++++ connection is normal after deletion

+++++ above, in three paths, the/etc/tnsnames.ora precedence order second

+ + + and our default configuration of $oracle_home/network/admin/tnsnames.ora is actually ranked third

+ + + If the TNSNames file exists under the previous path, but we only change the $oracle_home/network/admin/tnsnames.ora, it can easily cause connection errors

+ + + in the face of such problems, we should not forget to check the above path if there is a tnsnames file exists

+ + + Add, tnsnames more than three paths can exist

1. $HOME/.tnsnames.ora
2. $TNS _admin/tnsnames.ora
3./var/opt/oracle/tnsnames.ora Or/etc/tnsnames.ora
4. $ORACLE _home/network/admin/tnsnames.ora

Interested students can try