網友問題

關於 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>

網友問題--service_names與tnsnames.ora中的service_name關係

網友問題

結論

測試

相關文章