相信很多oracle 初学者对监听都会感到迷惑,特别是用类似sqlplus,sqldev等客户端连接数据库的时候,经常遇到TNS错误。因为最近在搭建ADG,所以就来谈谈关于监听的那点事。
很多人由于对监听的不理解,所以就会使用类似netca的工具来建立监听,或者其他。其实我不赞成的这样的学习方法的。反正我就刚开始学的时候,在windows下,看到了就点了几下,其实没什么用。图形这东西,隐藏了很多东西。用命令,用配置参数的方法,其实你能理解的更多。以下是我学习的关于监听的一点经验,分享在这里,希望对大家有点帮助。
关于监听,有主要的几个配置文件就是listener.ora ,tnsnames.ora ,sqlnet.ora (一般在$ORACLE_HOME/network/admin/目录下,有GI的话在GI的这个目录下也有)。其实sqlnet.ora不经常用,服务器端配置一些连接参数用的。比较复杂,此处不讲。
关于listener.ora:
这样说吧,listener.ora文件是真正用来监听的,真的符合的上监听的名字的。负责监视端口上发来的请求就是监听的含义。
那么,如何配置一个简单的监听呢?
先展示一下从官方的配置案例及说明:(没兴趣的可以直接跳)
#
# NAME
# listener.ora
# FUNCTION
# Network Listener startup parameter file example --作用是:启动监听配置文件的样本
# NOTES
# This file contains all the parameters for listener.ora, --这个参考样本包括了所有的配置参数
# and could be used to configure the listener by uncommenting
# and changing values. Multiple listeners can be configured
# in one listener.ora, so listener.ora parameters take the form
# of SID_LIST_<lsnr>, where <lsnr> is the name of the listener --在此样本中<lsnr>的替代是listener,也就是说,完全可以模仿这个来改一个,替换<lsnr>的值就行了
# this parameter refers to. All parameters and values are
# case-insensitive.
# <lsnr>
# This parameter specifies both the name of the listener, and
# it listening address(es). Other parameters for this listener
# us this name in place of <lsnr>. When not specified,
# the name for <lsnr> defaults to "LISTENER", with the default
# address value as shown below.
#
# LISTENER =
# (ADDRESS_LIST= --地址列表。这里填IP和端口,最好把localhost换成主机名。在/etc/hosts里面有解析 也就是192.168.56.xx hostname一行。listener会读取hosts解析IP
# (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
# (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
# This parameter specifies both the name of the listener, and
# it listening address(es). Other parameters for this listener
# us this name in place of <lsnr>. When not specified,
# the name for <lsnr> defaults to "LISTENER", with the default
# address value as shown below.
#
# LISTENER =
# (ADDRESS_LIST= --地址列表。这里填IP和端口,最好把localhost换成主机名。在/etc/hosts里面有解析 也就是192.168.56.xx hostname一行。listener会读取hosts解析IP
# (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
# (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
# SID_LIST_<lsnr>
# List of services the listener knows about and can connect
# clients to. There is no default. See the Net8 Administrator's
# Guide for more information.
#
# SID_LIST_LISTENER= --填写你的SID的地方,以及在这个监听上的服务。一般有这些就够了。监听就可以起来了
# (SID_LIST=
# (SID_DESC=
# #BEQUEATH CONFIG
# (GLOBAL_DBNAME=salesdb.mycompany)
# (SID_NAME=sid1)
# (ORACLE_HOME=/private/app/oracle/product/8.0.3)
# #PRESPAWN CONFIG
# (PRESPAWN_MAX=20)
# (PRESPAWN_LIST=
# (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
# )
# )
# )
# PASSWORDS_<lsnr> --以下很多参数,不一一整了。
# Specifies a password to authenticate stopping the listener.
# Both encrypted and plain-text values can be set. Encrypted passwords
# can be set and stored using lsnrctl.
# LSNRCTL> change_password
# Will prompt for old and new passwords, and use encryption both
# to match the old password and to set the new one.
# LSNRCTL> set password
# Will prompt for the new password, for authentication with
# the listener. The password must be set before running the next
# command.
# LSNRCTL> save_config
# Will save the changed password to listener.ora. These last two
# steps are not necessary if SAVE_CONFIG_ON_STOP_<lsnr> is ON.
# See below.
#
# Default: NONE
#
# PASSWORDS_LISTENER = 20A22647832FB454 # "foobar"
# List of services the listener knows about and can connect
# clients to. There is no default. See the Net8 Administrator's
# Guide for more information.
#
# SID_LIST_LISTENER= --填写你的SID的地方,以及在这个监听上的服务。一般有这些就够了。监听就可以起来了
# (SID_LIST=
# (SID_DESC=
# #BEQUEATH CONFIG
# (GLOBAL_DBNAME=salesdb.mycompany)
# (SID_NAME=sid1)
# (ORACLE_HOME=/private/app/oracle/product/8.0.3)
# #PRESPAWN CONFIG
# (PRESPAWN_MAX=20)
# (PRESPAWN_LIST=
# (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
# )
# )
# )
# PASSWORDS_<lsnr> --以下很多参数,不一一整了。
# Specifies a password to authenticate stopping the listener.
# Both encrypted and plain-text values can be set. Encrypted passwords
# can be set and stored using lsnrctl.
# LSNRCTL> change_password
# Will prompt for old and new passwords, and use encryption both
# to match the old password and to set the new one.
# LSNRCTL> set password
# Will prompt for the new password, for authentication with
# the listener. The password must be set before running the next
# command.
# LSNRCTL> save_config
# Will save the changed password to listener.ora. These last two
# steps are not necessary if SAVE_CONFIG_ON_STOP_<lsnr> is ON.
# See below.
#
# Default: NONE
#
# PASSWORDS_LISTENER = 20A22647832FB454 # "foobar"
# SAVE_CONFIG_ON_STOP_<lsnr>
# Tells the listener to save configuration changes to listener.ora when
# it shuts down. Changed parameter values will be written to the file,
# while preserving formatting and comments.
# Default: OFF
# Values: ON/OFF
#
# SAVE_CONFIG_ON_STOP_LISTENER = ON
# Tells the listener to save configuration changes to listener.ora when
# it shuts down. Changed parameter values will be written to the file,
# while preserving formatting and comments.
# Default: OFF
# Values: ON/OFF
#
# SAVE_CONFIG_ON_STOP_LISTENER = ON
# USE_PLUG_AND_PLAY_<lsnr>
# Tells the listener to contact an Onames server and register itself
# and its services with Onames.
# Values: ON/OFF
# Default: OFF
#
# USE_PLUG_AND_PLAY_LISTENER = ON
# Tells the listener to contact an Onames server and register itself
# and its services with Onames.
# Values: ON/OFF
# Default: OFF
#
# USE_PLUG_AND_PLAY_LISTENER = ON
# LOG_FILE_<lsnr>
# Sets the name of the listener's log file. The .log extension
# is added automatically.
# Default=<lsnr>
#
# LOG_FILE_LISTENER = lsnr
# Sets the name of the listener's log file. The .log extension
# is added automatically.
# Default=<lsnr>
#
# LOG_FILE_LISTENER = lsnr
# LOG_DIRECTORY_<lsnr>
# Sets the directory for the listener's log file.
# Default: <oracle_home>/network/log
#
# LOG_DIRECTORY_LISTENER = /private/app/oracle/product/8.0.3/network/log
# Sets the directory for the listener's log file.
# Default: <oracle_home>/network/log
#
# LOG_DIRECTORY_LISTENER = /private/app/oracle/product/8.0.3/network/log
# TRACE_LEVEL_<lsnr>
# Specifies desired tracing level.
# Default: OFF
# Values: OFF/USER/ADMIN/SUPPORT/0-16
#
# TRACE_LEVEL_LISTENER = SUPPORT
# Specifies desired tracing level.
# Default: OFF
# Values: OFF/USER/ADMIN/SUPPORT/0-16
#
# TRACE_LEVEL_LISTENER = SUPPORT
# TRACE_FILE_<lsnr>
# Sets the name of the listener's trace file. The .trc extension
# is added automatically.
# Default: <lsnr>
#
# TRACE_FILE_LISTENER = lsnr
# Sets the name of the listener's trace file. The .trc extension
# is added automatically.
# Default: <lsnr>
#
# TRACE_FILE_LISTENER = lsnr
# TRACE_DIRECTORY_<lsnr>
# Sets the directory for the listener's trace file.
# Default: <oracle_home>/network/trace
#
# TRACE_DIRECTORY_LISTENER=/private/app/oracle/product/8.0.3/network/trace
# CONNECT_TIMEOUT_<lsnr>
# Sets the number of seconds that the listener waits to get a
# valid database query after it has been started.
# Default: 10
#
# CONNECT_TIMEOUT_LISTENER=10
# Sets the directory for the listener's trace file.
# Default: <oracle_home>/network/trace
#
# TRACE_DIRECTORY_LISTENER=/private/app/oracle/product/8.0.3/network/trace
# CONNECT_TIMEOUT_<lsnr>
# Sets the number of seconds that the listener waits to get a
# valid database query after it has been started.
# Default: 10
#
# CONNECT_TIMEOUT_LISTENER=10
所以看完以上,就能信手拈来一个简单的listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=Flyme)(PORT=1521)) --主机名和端口
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=xiaoxy1) --填SID
(ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4) --家目录也就是$ORACLE_HOME
)
)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=Flyme)(PORT=1521)) --主机名和端口
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=xiaoxy1) --填SID
(ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4) --家目录也就是$ORACLE_HOME
)
)
so easy
这样监听就起来,当然这个刚起来,还没注册上服务,一般写了SID,等下就会注册了,要是等不及就进数据库敲alter system register;
那么,这个监听能起作用吗?试试就知道。
在另外一台电脑上用tnsping 测试成功。有没有服务还不知道。
再次测试,果然是可以了,估计那边监听也注册上服务了,如果对这些还有些晕的同学,就不用强制理解了,反正就是这样都可以建立监听,so easy
好了,再来说以下tnsnames.ora
这个东西,说起来就更加简单了,为什么呢?因为他只是一个解析文件而已,类似/etc/hosts
依照惯例,来一发官方说明 (跳过无所谓)
# The following is the general syntax for any entry in
# a tnsnames.ora file. There could be several such entries
# tailored to the user's needs.
# a tnsnames.ora file. There could be several such entries
# tailored to the user's needs.
<alias>= [ (DESCRIPTION_LIST = # Optional depending on whether u have --这里就很直白的说了alias就是别称的意思。可以随便取。那个description_list可以不管,简单
# one or more descriptions
# If there is just one description, unnecessary ]
(DESCRIPTION= --对于这个别称的描述,也就是你对这个别称的配置
[ (SDU=2048) ] # Optional, defaults to 2048 --非必需
# Can take values between 512 and 32K
[ (ADDRESS_LIST= # Optional depending on whether u have
# one or more addresses
# If there is just one address, unnecessary ] --list类的,都是如果没有多个就忽略
(ADDRESS= --地址,要记得,地址都是包括主机IP和端口的
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=tcp) --连接使用的协议
(HOST=<hostname>) --主机名,如果你在本地的机器上的/etc/hosts上有解析你要连接的主机的IP,就可以写那个数据库主机的名字,要不就还是写IP吧。
(PORT=<portnumber (1521 is a standard port used)>) --一般是1521
) --以下,如果没有多个IP就不用写了,一个就行。
[ (ADDRESS=
(PROTOCOL=ipc)
(KEY=<ipckey (PNPKEY is a standard key used)>)
)
]
[ (ADDRESS=
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=decnet)
(NODE=<nodename>)
(OBJECT=<objectname>)
)
]
... # More addresses
[ ) ] # Optional depending on whether ADDRESS_LIST is used or not
[ (CONNECT_DATA=
# one or more descriptions
# If there is just one description, unnecessary ]
(DESCRIPTION= --对于这个别称的描述,也就是你对这个别称的配置
[ (SDU=2048) ] # Optional, defaults to 2048 --非必需
# Can take values between 512 and 32K
[ (ADDRESS_LIST= # Optional depending on whether u have
# one or more addresses
# If there is just one address, unnecessary ] --list类的,都是如果没有多个就忽略
(ADDRESS= --地址,要记得,地址都是包括主机IP和端口的
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=tcp) --连接使用的协议
(HOST=<hostname>) --主机名,如果你在本地的机器上的/etc/hosts上有解析你要连接的主机的IP,就可以写那个数据库主机的名字,要不就还是写IP吧。
(PORT=<portnumber (1521 is a standard port used)>) --一般是1521
) --以下,如果没有多个IP就不用写了,一个就行。
[ (ADDRESS=
(PROTOCOL=ipc)
(KEY=<ipckey (PNPKEY is a standard key used)>)
)
]
[ (ADDRESS=
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=decnet)
(NODE=<nodename>)
(OBJECT=<objectname>)
)
]
... # More addresses
[ ) ] # Optional depending on whether ADDRESS_LIST is used or not
[ (CONNECT_DATA=
(SID=<oracle_sid>)
[ (GLOBAL_NAME=<global_database_name>) ]
)
]
[ (SOURCE_ROUTE=yes) ]
)
(DESCRIPTION=
[ (SDU=2048) ] # Optional, defaults to 2048
# Can take values between 512 and 32K
[ (ADDRESS_LIST= ] # Optional depending on whether u have more
# than one address or not
# If there is just one address, unnecessary
(ADDRESS
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=tcp)
(HOST=<hostname>)
(PORT=<portnumber (1521 is a standard port used)>)
)
[ (ADDRESS=
(PROTOCOL=ipc)
(KEY=<ipckey (PNPKEY is a standard key used)>)
)
]
... # More addresses
[ ) ] # Optional depending on whether ADDRESS_LIST
# is being used
[ (CONNECT_DATA=
(SID=<oracle_sid>)
[ (GLOBAL_NAME=<global_database_name>) ]
)
]
[ (SOURCE_ROUTE=yes) ]
)
[ (CONNECT_DATA= --这里需要写,这里是连接参数
(SID=<oracle_sid>) --连接的数据库SID
[ (GLOBAL_NAME=<global_database_name>) ] --全局名称,一般没什么必要
)
]
... # More descriptions --还有更多的选项,比如 service_name,server 等
[ ) ] # Optional depending on whether DESCRIPTION_LIST is used or not
[ (GLOBAL_NAME=<global_database_name>) ]
)
]
[ (SOURCE_ROUTE=yes) ]
)
(DESCRIPTION=
[ (SDU=2048) ] # Optional, defaults to 2048
# Can take values between 512 and 32K
[ (ADDRESS_LIST= ] # Optional depending on whether u have more
# than one address or not
# If there is just one address, unnecessary
(ADDRESS
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=tcp)
(HOST=<hostname>)
(PORT=<portnumber (1521 is a standard port used)>)
)
[ (ADDRESS=
(PROTOCOL=ipc)
(KEY=<ipckey (PNPKEY is a standard key used)>)
)
]
... # More addresses
[ ) ] # Optional depending on whether ADDRESS_LIST
# is being used
[ (CONNECT_DATA=
(SID=<oracle_sid>)
[ (GLOBAL_NAME=<global_database_name>) ]
)
]
[ (SOURCE_ROUTE=yes) ]
)
[ (CONNECT_DATA= --这里需要写,这里是连接参数
(SID=<oracle_sid>) --连接的数据库SID
[ (GLOBAL_NAME=<global_database_name>) ] --全局名称,一般没什么必要
)
]
... # More descriptions --还有更多的选项,比如 service_name,server 等
[ ) ] # Optional depending on whether DESCRIPTION_LIST is used or not
以下配置一个简单的看看
所以,tnsnames.ora是干嘛的呢?就是给你通过一个简单好记的名字来进行连接。有了这个就不用写主机名,端口那些了。因为你之前已经写在tnsnames.ora里面了,所以这只是一个方便的工具而已。
就这么简单。