为数据库设置多个服务名（通过SCOPE=both设置，同时修改参数文件）
SQL> show parameter service_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string vmdb SQL> alter system set service_names='vmdb,sn01,sn02' scope=both; System altered.
重启监听后，监听状态并未显示服务sn01,sn02
[oracle@CentOS ~]$ lsnrctl stop LSNRCTL :: Copyright (c) , , Oracle. All rights reserved. Connecting ))) The command completed successfully [oracle@CentOS ~]$ lsnrctl start LSNRCTL :: Copyright (c) , , Oracle. All rights reserved. Starting /dbhome_1/bin/tnslsnr: please wait... TNSLSNR - Production System parameter /dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml Listening ))) Connecting ))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR - Production Start Date :: Uptime days hr. sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter /dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION))) Services Summary... Service "vmdb" has instance(s). Instance "vmdb", status UNKNOWN, has handler(s) for this service... The command completed successfully
通过另一台机器通过服务名sn01连接可以连接上：
/sn01 SQL Production :: Copyright (c) , , Oracle. All rights reserved. ERROR: ORA: the password will expire within days Connected to: Oracle - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
在数据库中可以查询v$session视图的service_name来判断会话是由通过哪个服务名连接创建的：
SQL> col paddr format a20 SQL SQL> col username format a15 SQL> col service_name format a20 SQL> Select paddr,Sid,serial#,username,service_name From v$session Where username Is Not Null; PADDR SID SERIAL# USERNAME SERVICE_NAME -------------------- -------- ---------- --------------- -------------------- 000000008DC95250 SYS SYS$USERS 000000008DC98310 TEST vmdb 000000008DC96290 TEST sn01 000000008DC99350 TEST vmdb
重启数据库：
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area bytes Fixed Size bytes Variable Size bytes bytes Redo Buffers bytes Database mounted. Database opened.
再次查看监听，sn01,sn02服务名显示出来了：
[oracle@CentOS ~]$ lsnrctl status LSNRCTL :: Copyright (c) , , Oracle. All rights reserved. Connecting ))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR - Production Start Date :: Uptime days hr. sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter /dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/CentOS/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION))) Services Summary... Service "sn01" has instance(s). Instance "vmdb", status READY, has handler(s) for this service... Service "sn02" has instance(s). Instance "vmdb", status READY, has handler(s) for this service... Service "vmdb" has instance(s). Instance "vmdb", status UNKNOWN, has handler(s) for this service... Instance "vmdb", status READY, has handler(s) for this service... Service "vmdbXDB" has instance(s). Instance "vmdb", status READY, has handler(s) for this service... The command completed successfully
但是监听配置文件里不会自动增加服务名sn01,sn02：
/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration /dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = vmdb)
(ORACLE_HOME /dbhome_1)
(SID_NAME = vmdb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS ))
)
ADR_BASE_LISTENER = /u01/app/oracle