相信很多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))) # 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" # 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 # 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 # LOG_FILE_<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 # TRACE_LEVEL_<lsnr>
# 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 # 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
是以看完以上，就能信手拈來一個簡單的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
)
)
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. <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= (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 以下配置一個簡單的看看
是以，tnsnames.ora是幹嘛的呢？就是給你通過一個簡單好記的名字來進行連接配接。有了這個就不用寫主機名，端口那些了。因為你之前已經寫在tnsnames.ora裡面了，是以這隻是一個友善的工具而已。 就這麼簡單。