Oracle listener.ora sqlnet.ora tnsnames.ora 文件之间的关系

Oracle listener.ora sqlnet.ora tnsnames.ora 文件之间的关系

参考： http://www.itpub.net/246061.html

还是以例子讲解为好

服务器端设置 ：listener.ora
服务器端，客户端设置：sqlnet.ora tnsnames.ora

listener.ora
# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_2/NETWORK/ADMIN/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_2)
      (PROGRAM = extproc)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
)

注意：      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
或           (ADDRESS = (PROTOCOL = TCP)(HOST = hostIP)(PORT = 1521))

sqlnet.oraTNSNAMES
# sqlnet.ora Network Configuration File: D:/oracle/product/10.2.0/db_2/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME, EZCONNECT)

#NAMES.DEFAULT_DOMAIN=DB_DOMAIN

上面的sqlnet.ora文件说明：
SQLNET.AUTHENTICATION_SERVICES= (NTS)——这个表示采用os认证，在数据库服务器上，可以利用sqlplus “/ as sysdba”。一般这个配置在windows上是ok的，在unix环境下可能会有问题，一般在unix下可以去掉这个配置。

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, ONAMES)——表示将首先利用tnsnames进行解析；如果tnsnames解析不到，将使用hostname解析；如果hostname解析不到，将采用onames进行解析。

被注释掉的NAMES.DEFAULT_DOMAIN = DB_DOMAIN——表示采用默认的domain name为DB_DOMAIN，在tnsnames.ora中如果配置对应的解析，如果原来的别名orcl，那么，当启用这个参数后，在tnsnames中的配置要改成orcl.DB_DOMAIN。在使用tnsping时或者sqlplus登录时，只需写前面的别名，系统会自动加上后面的domain name来进行解析。

tnsnames.ora
# tnsnames.ora Network Configuration File: D:/oracle/product/10.2.0/db_2/NETWORK/ADMIN/tnsnames.ora
# Generated by Oracle configuration tools.

orcl =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostIP)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)
# hostIP
orcl =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostIP)(PORT = 1521))
    )
    (CONNECT_DATA =
#      (SERVER = DEDICATED)   －－专有服务器（DBCA创建时默认）
#      (SERVER = SHARED)        －－共享服务器
      (SERVICE_NAME = ORCL)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

注意：   服务器类型如果设置错误，会导致ORA-12523错误: TNS: 监听程序未找到适用于客户机连接的例程

1.如果tnsnames中的service_name配置错误，配置成了instance_name了，这个时候会发生tnsping能通，但是sqlplus连接不上的奇怪情况。报错ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor。这个时候查错的时候，需要检查对应的service_name。

2.确认DB INSTANCE_NAME = SID_NAME， service_names
sqlplus sys/pass AS SYSDBA
SELECT HOST_NAME, INSTANCE_NAME FROM V$INSTANCE;
SELECT NAME, VALUE FROM V$PARAMETERWHERE NAME IN ('instance_name', 'service_names')

SHOW PARAMETER SERVICE_NAME
SHOW PARAMETER SERVICE_NAMES