-- 一般在 DBCA 建库之前应该先配置监听，否则在图形化界面的 Automatic Maintenance Tasks 选项无法开启
-- 动态监听一般使用 netca 图形化界面配置比较简单，图形化界面也可以减少出错
-- 监听文件存储在 $ORACLE_HOME/network/admin/ 目录下，文件名称为 listener.ora

-- 使用 cat 命令可以查看监听文件，动态监听文件一般都是这种形式

点击(此处)折叠或打开

[oracle@BLACKBOX admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BLACKBOX)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle

-- 默认静态监听的配置

点击(此处)折叠或打开

[oracle@BLACKBOX admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BLACKBOX)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BLACKBOX)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = BLACKBOX)
)
)

-- 使用 lsnrctl 命令进入监听管理界面后，查看监听状态

点击(此处)折叠或打开

LSNRCTL> stat
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=BLACKBOX)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 04-AUG-2016 13:48:36
Uptime 0 days 7 hr. 37 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/BLACKBOX/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=BLACKBOX)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BLACKBOX" has 2 instance(s).
Instance "BLACKBOX", status UNKNOWN, has 1 handler(s) for this service...
Instance "BLACKBOX", status READY, has 1 handler(s) for this service...
Service "BLACKBOXXDB" has 1 instance(s).
Instance "BLACKBOX", status READY, has 1 handler(s) for this service...
The command completed successfully

-- 测试监听连接情况
-- 通过windows客户端远程连接到已经关闭的数据库

点击(此处)折叠或打开

C:\>sqlplus sys/oracle@blackbox as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 8月 4 22:16:27 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
已连接到空闲例程。

-- 启动数据库，查看数据库名称

点击(此处)折叠或打开

SYS@blackbox> startup
ORACLE 例程已经启动。
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
数据库装载完毕。
数据库已经打开。
SYS@blackbox> show parameter name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string BLACKBOX
db_unique_name string BLACKBOX
global_names boolean FALSE
instance_name string BLACKBOX
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string BLACKBOX

-- 添加非默认静态监听

点击(此处)折叠或打开

[oracle@BLACKBOX admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BLACKBOX)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = BLACKBOX)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BLACKBOX)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
LSNR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BLACKBOX)(PORT = 1526))
)
)
SID_LIST_LSNR =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BLACKBOX)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = BLACKBOX)
)
)

-- 配置连接字符串，测试监听

点击(此处)折叠或打开

[oracle@BLACKBOX admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
BLACKBOX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BLACKBOX)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BLACKBOX)
)
)
lsnr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BLACKBOX)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BLACKBOX)
)
)

-- 修改系统参数 local_listener

点击(此处)折叠或打开

SYS@blackbox> alter system set local_listener = lsnr;
System altered.
SYS@blackbox> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LSNR

-- 通过 lsnr 连接字符串连接数据库测试静态非默认监听

点击(此处)折叠或打开

[oracle@BLACKBOX ~]$ sqlplus eoda/foo@lsnr
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 4 21:58:29 2016
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Session altered.
EODA@lsnr> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIG_TABLE TABLE

ITPub博客

静态监听的配置

最新文章