oracle相同SID对外提供多个service_names
oracle相同SID对外提供多个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
版权声明：本文为sorliran原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。