网友问题
关于 tnsname的
service_name 这个参数 不是只要监听中 有监控的服务名 不是都是可以连上的么
一般，默认的情况下 监听的服务名中是包含instance_name的
所以 一般service_name 写sid 也是能链接上的
这个 我应该没有理解错吧
结论
1,对于静态监听，不管你如何变更services_names,lsnrctl status显示的注册服务不会发生变化（原因很简单：因为在监听配置文件已经写死了）也就是说你随便改service_names,都不会影响已注册到监听的数据库服务信息
2,对于动态监听，可以调整service_names的值，其值默认为db_name，也可是多个db_name,但以逗号分隔
3,基于RAC数据库，不建议配置参数service_names,可以通过DBCA显式创建不同的服务名
4,对于动态监听，service_names也可以配置为空，对应数据库实例的信息仍会注册到监听中去，是以db_name注册服务信息的
5,如果调整service_names为非db_name的值，调整或新建tnsnames.ora新条目，其节service_name的值为service_names指定的新值
仍然可以连接到数据库实例
6，其实说白了，service_names就是客户端与数据库实例连接的纽带，通过它，监听会把客户端会话导向到不同的数据库实例
7，网友的想法是正确
测试
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_name string guowang
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
instance_name string guowang
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
service_names string guowang
[oracle@seconary ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:20:51
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1981)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.3)(PORT=1981)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2015 06:20:51
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Password or Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1981)))
Services Summary...
Service "guowang" has 1 instance(s).
Instance "guowang", status UNKNOWN, has 1 handler(s) for this service...
[oracle@seconary admin]$ more listener.ora
# listener.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = guowang)
(ORACLE_HOME = /oracle/product/11.2.0/db_1)
(SID_NAME = guowang)
)
(SID_DESC =
(GLOBAL_DBNAME = ora10g)
(ORACLE_HOME = /home/ora10g/product/10.2.0/db_1)
(SID_NAME = ora10g)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3)(PORT = 1981))
)
ADR_BASE_LISTENER = /oracle
[oracle@seconary admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
GUOWANG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3)(PORT = 1981))
)
(CONNECT_DATA =
(SERVICE_NAME = guowang)
)
)
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
service_names string guowang
SQL> alter system set service_names='newly_guowang';
System altered.
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
service_names string newly_guowang
SQL>
---可见调整了数据库的service_names TNSNAMES还可以连接数据库
[oracle@seconary admin]$ sqlplus scott/system@guowang
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 06:26:44 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
监听里面注册的还是旧的SERVICE NAME
[oracle@seconary admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:26:57
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.3)(PORT=1981)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2015 06:20:51
Uptime 0 days 0 hr. 6 min. 6 sec
Trace Level off
Security ON: Password or Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1981)))
Services Summary...
Service "guowang" has 1 instance(s).
Instance "guowang", status UNKNOWN, has 1 handler(s) for this service...
让新的SERVICE NAME生效，监听里面还是注册旧的SERVICE NAME
[oracle@seconary admin]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:27:55
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.3)(PORT=1981)))
The command completed successfully
[oracle@seconary admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:28:42
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.3)(PORT=1981)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2015 06:20:51
Uptime 0 days 0 hr. 7 min. 51 sec
Trace Level off
Security ON: Password or Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1981)))
Services Summary...
Service "guowang" has 1 instance(s).
Instance "guowang", status UNKNOWN, has 1 handler(s) for this service...
SQL> alter system register;
System altered.
SQL> alter system register;
System altered.
SQL> alter system register;
System altered.
[oracle@seconary admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:30:31
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.3)(PORT=1981)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2015 06:20:51
Uptime 0 days 0 hr. 9 min. 39 sec
Trace Level off
Security ON: Password or Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1981)))
Services Summary...
Service "guowang" has 1 instance(s).
Instance "guowang", status UNKNOWN, has 1 handler(s) for this service...
经查官方手册，service_names的值默认为db_unique_name,而且在rac中不要配置此参数，建议用dbca创建指定的服务名
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name string guowang
所以说你为service_names指定的值只是db_unique_name，而不能是其它的值
---配置service_names为空，监听仍然可以注册到数据库实例
SQL> alter system set service_names='';
System altered.
SQL> alter system register;
System altered.
SQL> alter system register;
System altered.
SQL> alter system register;
System altered.
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
service_names string
SQL> quit
[oracle@seconary admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:46:05
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.3)(PORT=1981)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2015 06:20:51
Uptime 0 days 0 hr. 25 min. 13 sec
Trace Level off
Security ON: Password or Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1981)))
Services Summary...
Service "guowang" has 1 instance(s).
Instance "guowang", status UNKNOWN, has 1 handler(s) for this service...
注意我之前用的静态监听，现在如果使用动态监听，又与service_names的关系如何呢
[oracle@seconary admin]$ cp listener.ora listener.ora.20151201bak
[oracle@seconary admin]$
[oracle@seconary admin]$ rm -rf listener.ora
[oracle@seconary admin]$
[oracle@seconary admin]$ rm -rf listener.ora
[oracle@seconary admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:50:02
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@seconary admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:50:07
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Log messages written to /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secondary.redhat.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2015 06:50:07
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secondary.redhat.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@seconary admin]$
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
service_names string
SQL> alter system register;
System altered.
SQL> alter system register;
System altered.
SQL> alter system register;
System altered.
可见采用动态监听时，如果service_names配置为空，数据库实例仍然可以注册到监听中
[oracle@seconary admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:51:21
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2015 06:50:07
Uptime 0 days 0 hr. 1 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secondary.redhat.com)(PORT=1521)))
Services Summary...
Service "guowang" has 1 instance(s).
Instance "guowang", status READY, has 1 handler(s) for this service...
[oracle@seconary admin]$ sqlplus scott/system@guowang
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 06:51:45 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
service_names string
SQL> alter system set service_names='zxyone';
System altered.
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
service_names string zxyone
SQL> alter system register;
System altered.
SQL> alter system register;
System altered.
SQL> alter system register;
System altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
可见动态监听下，如何调整service_names，监听仍旧会在新的SERVICE下注册数据库实例
[oracle@seconary admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 06:53:22
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2015 06:50:07
Uptime 0 days 0 hr. 3 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/diag/tnslsnr/seconary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secondary.redhat.com)(PORT=1521)))
Services Summary...
Service "guowang" has 1 instance(s).
Instance "guowang", status READY, has 1 handler(s) for this service...
Service "guowangXDB" has 1 instance(s).
Instance "guowang", status READY, has 1 handler(s) for this service...
Service "zxyone" has 1 instance(s).
Instance "guowang", status READY, has 1 handler(s) for this service...
The command completed successfully
如何把tns中的service_name变更为上述service_names最新的值，会不会仍可以连接到数据库实例呢
[oracle@seconary admin]$ vi tnsnames.ora
GUOWANG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3)(PORT = 1981))
)
(CONNECT_DATA =
(SERVICE_NAME = zxyone)
)
)
[oracle@seconary admin]$ sqlplus scott/system@guowang
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 06:57:08 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
[oracle@seconary admin]$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause: The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener. This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
// - Wait a moment and try to connect a second time.
// - Check which services are currently known by the listener by executing:
// lsnrctl services <listener name>
// - Check that the SERVICE_NAME parameter in the connect descriptor of the
// net service name used specifies a service known by the listener.
// - If an easy connect naming connect identifier was used, check that
// the service name specified is a service known by the listener.
// - Check for an event in the listener.log file.
[oracle@seconary admin]$ tnsping guowang
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 30-NOV-2015 07:02:07
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3)(PORT = 1981))) (CONNECT_DATA = (SERVICE_NAME = zxyone)))
OK (0 msec)
[oracle@seconary admin]$ cp tnsnames.ora tnsnames.ora.20151201bak
[oracle@seconary admin]$ vi tnsnames.ora
USE_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = zxyone) --这里的service_name的值即为service_names中的值
)
)
看到没，可见通过service_names指定的tns，可以连接到数据库实例
[oracle@seconary admin]$ sqlplus scott/system@use_db
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 07:08:22 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1850479/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1850479/
7096
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
