oracle监听器初识-配置多SERVICE_NAMES
现象：
为数据库设置多个服务名（通过SCOPE=both设置，同时修改参数文件）
1 SQL> show parameter service_names; 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 service_names string vmdb 6 7 SQL> alter system set service_names='vmdb,sn01,sn02' scope=both; 8 9 System altered.
重启监听后，监听状态并未显示服务sn01,sn02
1 [oracle@CentOS ~]$ lsnrctl stop 2 3 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-MAR-2018 18:29:21 4 5 Copyright (c) 1991, 2009, Oracle. All rights reserved. 6 7 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CentOS)(PORT=1521))) 8 The command completed successfully 9 [oracle@CentOS ~]$ lsnrctl start 10 11 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-MAR-2018 18:29:24 12 13 Copyright (c) 1991, 2009, Oracle. All rights reserved. 14 15 Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... 16 17 TNSLSNR for Linux: Version 11.2.0.1.0 - Production 18 System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 19 Log messages written to /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml 20 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CentOS)(PORT=1521))) 21 22 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CentOS)(PORT=1521))) 23 STATUS of the LISTENER 24 ------------------------ 25 Alias LISTENER 26 Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production 27 Start Date 26-MAR-2018 18:29:24 28 Uptime 0 days 0 hr. 0 min. 0 sec 29 Trace Level off 30 Security ON: Local OS Authentication 31 SNMP OFF 32 Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 33 Listener Log File /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml 34 Listening Endpoints Summary... 35 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CentOS)(PORT=1521))) 36 Services Summary... 37 Service "vmdb" has 1 instance(s). 38 Instance "vmdb", status UNKNOWN, has 1 handler(s) for this service... 39 The command completed successfully
通过另一台机器通过服务名sn01连接可以连接上：
1 [oracle@centos-sample ~]$ sqlplus test/test@192.168.8.141/sn01 2 3 SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 25 20:06:35 2018 4 5 Copyright (c) 1982, 2009, Oracle. All rights reserved. 6 7 ERROR: 8 ORA-28002: the password will expire within 7 days 9 10 11 12 Connected to: 13 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 14 With the Partitioning, OLAP, Data Mining and Real Application Testing options 15 16 SQL>
在数据库中可以查询v$session视图的service_name来判断会话是由通过哪个服务名连接创建的：
1 SQL> col paddr format a20 2 SQL> col Sid format 9999999 3 SQL> col username format a15 4 SQL> col service_name format a20 5 SQL> Select paddr,Sid,serial#,username,service_name From v$session Where username Is Not Null; 6 7 PADDR SID SERIAL# USERNAME SERVICE_NAME 8 -------------------- -------- ---------- --------------- -------------------- 9 000000008DC95250 17 19 SYS SYS$USERS 10 000000008DC98310 36 7 TEST vmdb 11 000000008DC96290 37 22 TEST sn01 12 000000008DC99350 38 11 TEST vmdb
重启数据库：
1 SQL> shutdown immediate; 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down. 5 SQL> startup 6 ORACLE instance started. 7 8 Total System Global Area 776646656 bytes 9 Fixed Size 2217384 bytes 10 Variable Size 583010904 bytes 11 Database Buffers 188743680 bytes 12 Redo Buffers 2674688 bytes 13 Database mounted. 14 Database opened.
再次查看监听，sn01,sn02服务名显示出来了：
1 [oracle@CentOS ~]$ lsnrctl status 2 3 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-MAR-2018 18:30:29 4 5 Copyright (c) 1991, 2009, Oracle. All rights reserved. 6 7 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CentOS)(PORT=1521))) 8 STATUS of the LISTENER 9 ------------------------ 10 Alias LISTENER 11 Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production 12 Start Date 26-MAR-2018 18:29:24 13 Uptime 0 days 0 hr. 1 min. 5 sec 14 Trace Level off 15 Security ON: Local OS Authentication 16 SNMP OFF 17 Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 18 Listener Log File /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml 19 Listening Endpoints Summary... 20 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CentOS)(PORT=1521))) 21 Services Summary... 22 Service "sn01" has 1 instance(s). 23 Instance "vmdb", status READY, has 1 handler(s) for this service... 24 Service "sn02" has 1 instance(s). 25 Instance "vmdb", status READY, has 1 handler(s) for this service... 26 Service "vmdb" has 2 instance(s). 27 Instance "vmdb", status UNKNOWN, has 1 handler(s) for this service... 28 Instance "vmdb", status READY, has 1 handler(s) for this service... 29 Service "vmdbXDB" has 1 instance(s). 30 Instance "vmdb", status READY, has 1 handler(s) for this service... 31 The command completed successfully
但是监听配置文件里不会自动增加服务名sn01,sn02：
1 [oracle@CentOS ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 2 # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 3 # Generated by Oracle configuration tools. 4 5 SID_LIST_LISTENER = 6 (SID_LIST = 7 (SID_DESC = 8 (GLOBAL_DBNAME = vmdb) 9 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) 10 (SID_NAME = vmdb) 11 ) 12 ) 13 14 LISTENER = 15 (DESCRIPTION = 16 (ADDRESS = (PROTOCOL = TCP)(HOST = CentOS)(PORT = 1521)) 17 ) 18 19 ADR_BASE_LISTENER = /u01/app/oracle
listener.ora文件典型配置：
1 SID_LIST_LISTENER = 2 (SID_LIST = 3 (SID_DESC = 4 (SID_NAME = PLSExtProc) 5 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) 6 (PROGRAM = extproc) 7 ) 8 (SID_DESC = 9 (GLOBAL_DBNAME = vmdb) 10 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) 11 (SID_NAME = vmdb) 12 ) 13 (SID_DESC = 14 (GLOBAL_DBNAME = sn01) 15 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) 16 (SID_NAME = vmdb) 17 ) 18 (SID_DESC = 19 (GLOBAL_DBNAME = sn02) 20 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) 21 (SID_NAME = vmdb) 22 ) 23 ) 24 25 LISTENER = 26 (DESCRIPTION = 27 (ADDRESS = (PROTOCOL = TCP)(HOST = CentOS)(PORT = 1521)) 28 )
以下解释来自老盖的《深入浅出ORACLE》：
监听器文件主要包含两个部分：
- 第一部分LISTENER信息，这部分包含了监听的协议、地址以及端口等信息。
- 第二部分SID_LIST_LISTENER信息，这部分信息用于提供对外的数据库服务列表。第一个SID_DESC部分（SID_NAME = PLSExtProc）是数据库缺省就包含的对外部存储过程提供的本地监听，此外三个SID_DESC部分是对数据库的三个SERVICE_NAMES所设置的监听服务，对于同一个SID对应的数据库，可以对外提供多个服务名供客户端访问。
设置服务名的参数为GLOBAL_DBNAME，当处理客户端连接请求时，监听器首先尝试将GLOBAL_DBNAME和客户端请求中的SERVICE_NAME相匹配；如果客户端连接请求的是SID信息，则Oracle不检查GLOBAL_DBNAME设置，而是对监听器中设置的SID_NAME进行匹配。
启动这个监听后，可以看到对于不同服务名Oracle所启动的监听信息。首先输出的信息显示了监听器文件地址以及监听日志文件位置（监听器日志在诊断数据库异常或攻击信息时非常有用）。
通过服务名，Oracle可以将客户端和服务器彻底隔离开来，对于客户端来说，它不用关心数据库的名字、实例名到底是什么，它只需要知道数据库对外提供的服务名就行了，这个名字可能和实例名相同，也可能不相同。
（注：通过查询会话视图v$session的service_names，可以区分哪些会话来自哪个service_name，所以可以通过提供不同的service_name给不同的下游系统，用来区分哪些会话由哪些系统创建）
从8i开始，oracle引入了动态服务注册（Dynamic Service Registration）的功能，所谓动态注册是指当实例启动之后，由后台进程PMON在监听器中注册数据库服务信息。在动态注册机制下，原来监听器中的SID_LIST部分将不再需要。动态注册的服务名，由于监听器确切地知道实例的状态，所以正常状态通常显示为READY，而对于静态注册的服务名，则状态显示为UNKNOW。
以下演示，删除监听配置文件listener.ora后，监听器依然可以知道动态注册的服务名：
1 [oracle@CentOS ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/ 2 [oracle@CentOS admin]$ ll 3 总用量 16 4 -rw-r--r-- 1 oracle oinstall 461 3月 26 18:17 listener.ora 5 drwxr-xr-x. 2 oracle oinstall 4096 5月 7 2017 samples 6 -rw-r--r--. 1 oracle oinstall 187 5月 7 2007 shrept.lst 7 -rw-r-----. 1 oracle oinstall 325 5月 7 2017 tnsnames.ora 8 [oracle@CentOS admin]$ lsnrctl stop 9 10 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-MAR-2018 13:29:44 11 12 Copyright (c) 1991, 2009, Oracle. All rights reserved. 13 14 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CentOS)(PORT=1521))) 15 The command completed successfully 16 17 [oracle@CentOS admin]$ ll 18 总用量 16 19 -rw-r--r-- 1 oracle oinstall 461 3月 26 18:17 listener.ora 20 drwxr-xr-x. 2 oracle oinstall 4096 5月 7 2017 samples 21 -rw-r--r--. 1 oracle oinstall 187 5月 7 2007 shrept.lst 22 -rw-r-----. 1 oracle oinstall 325 5月 7 2017 tnsnames.ora 23 [oracle@CentOS admin]$ mv listener.ora listener.ora_bak 24 [oracle@CentOS admin]$ lsnrctl start 25 26 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-MAR-2018 13:30:10 27 28 Copyright (c) 1991, 2009, Oracle. All rights reserved. 29 30 Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... 31 32 TNSLSNR for Linux: Version 11.2.0.1.0 - Production 33 Log messages written to /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml 34 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CentOS)(PORT=1521))) 35 36 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) 37 STATUS of the LISTENER 38 ------------------------ 39 Alias LISTENER 40 Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production 41 Start Date 27-MAR-2018 13:30:11 42 Uptime 0 days 0 hr. 0 min. 1 sec 43 Trace Level off 44 Security ON: Local OS Authentication 45 SNMP OFF 46 Listener Log File /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml 47 Listening Endpoints Summary... 48 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CentOS)(PORT=1521))) 49 The listener supports no services 50 The command completed successfully 51 [oracle@CentOS admin]$ lsnrctl status 52 53 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-MAR-2018 13:35:54 54 55 Copyright (c) 1991, 2009, Oracle. All rights reserved. 56 57 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) 58 STATUS of the LISTENER 59 ------------------------ 60 Alias LISTENER 61 Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production 62 Start Date 27-MAR-2018 13:30:11 63 Uptime 0 days 0 hr. 5 min. 43 sec 64 Trace Level off 65 Security ON: Local OS Authentication 66 SNMP OFF 67 Listener Log File /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml 68 Listening Endpoints Summary... 69 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CentOS)(PORT=1521))) 70 Services Summary... 71 Service "sn01" has 1 instance(s). 72 Instance "vmdb", status READY, has 1 handler(s) for this service... 73 Service "sn02" has 1 instance(s). 74 Instance "vmdb", status READY, has 1 handler(s) for this service... 75 Service "vmdb" has 1 instance(s). 76 Instance "vmdb", status READY, has 1 handler(s) for this service... 77 Service "vmdbXDB" has 1 instance(s). 78 Instance "vmdb", status READY, has 1 handler(s) for this service... 79 The command completed successfully
可以看到只有动态注册的服务名，没有了UNKNOW。
启动监听时，虽然显示The listener supports no services，但是由于动态注册是PMON进程主动向监听器注册，所以监听状态依然能看到动态注册的服务处于READY状态，可以对外提供访问服务。
