Oracle tnsname.ora中service_name的理解

Oracle tnsnames.ora中global_dbname，service_name，SID等配置项易混淆。下面我们通过实验来看看service_name配置。

[grid@nodedg admin]$ lsnrctl status nodedg

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 06-MAR-2019 14:23:08

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=nodedg)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     nodedg
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                06-MAR-2019 09:17:21
Uptime                    0 days 5 hr. 5 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/grid/product/18.3.0/crs/network/admin/listener.ora
Listener Log File         /app/grid/diag/tnslsnr/nodedg/nodedg/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nodedg)(PORT=1522)))
Services Summary...
Service "eighteen" has 1 instance(s).
  Instance "dg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

nodedg监听在1522端口注册了一个service：eighteen，那么如果想通过TNS协议链接实例需要配置tnsnames.ora

[oracle@node admin]$ more tnsnames.ora

nodedg =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.0.138)(PORT = 1522))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = eighteen)
     (RU=A)
   )
 )

service_name配置的是eighteen，正是nodedg监听中的service

[oracle@node admin]$ tnsping nodedg

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 06-MAR-2019 14:38:00

Copyright (c) 1997, 2018, 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.6.0.138)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = eighteen) (RU=A)))
OK (0 msec)
[oracle@node admin]$ sqlplus system/oracle@nodedg

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 6 14:38:10 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Thu Feb 28 2019 10:36:41 +08:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

col name for a15
col open_mode for a10
col database_role for a18
col db_unique_name for a15
col cdb for a10
SQL> select name,open_mode,database_role,db_unique_name,cdb from v$database;

NAME		OPEN_MODE  DATABASE_ROLE      DB_UNIQUE_NAME  CDB
--------------- ---------- ------------------ --------------- ----------
EIGHTEEN	READ ONLY  PHYSICAL STANDBY   dg	      YES
		WITH APPLY

tnsnames.ora中的service_name是监听程序监听到的service_name