Oracle配置多个TNSNAMES连接串_oracle连接串配置文件-优快云博客

本文链接：https://blog.youkuaiyun.com/Sebastien23/article/details/129428812

文章详细介绍了如何检查和修改Oracle数据库的服务名，以及如何在tnsnames.ora文件中配置多个TNS连接串，包括创建别名并验证连接的正确性。通过SQL命令和编辑tnsnames.ora文件，确保了数据库的多个连接途径。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle配置多个TNSNAMES连接串

检查服务名与TNS配置
修改服务名
修改TNS配置
配置多个TNS连接串

检查服务名与TNS配置

检查服务名：

SQL> show parameter service_names

NAME           TYPE     VALUE
----           ----     -----
service_names  string   bangkok

检查tnsnames.ora配置：

[oracle@primarydb dbs]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
BANGKOK = 
(DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521))
  (CONNECT_DATA = 
    (SERVER = DEDICATED) 
    (SERVICE_NAME = bangkok) 
  ) 
)

可以通过以下TNS连接串连接到数据库：

sqlplus username/password@BANGKOK
sqlplus username/password@bangkok

修改服务名

修改服务名（一般不建议修改）：

SQL> alter system set service_names=BANGKOK_SVC scope=both;

System altered.

SQL> show parameter service_names

NAME           TYPE     VALUE
----           ----     -----
service_names  string   BANGKOK_SVC

重新通过TNS连接：

[oracle@primarydb dbs]$ sqlplus miguel/XXXXX@bangkok

ERROR: 
ORA-01034: ORACLE not available 
ORA-27101: shared memory realm does not exist 
Linux-x86_64 Error: 2: No such file or directory 
Additional information: 4376 
Additional information: -545674901 
Process ID: 0 Session ID: 0 Serial number: 0

[oracle@primarydb dbs]$ sqlplus miguel/XXXXX@BANGKOK_SVC

ERROR: 
ORA-12154: TNS:could not resolve the connect identifier specified

修改TNS配置

将tnsnames.ora中的SERVICE_NAME修改为正确的服务名：

vim $ORACLE_HOME/network/admin/tnsnames.ora
BANGKOK = 
(DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521))
  (CONNECT_DATA = 
    (SERVER = DEDICATED) 
    (SERVICE_NAME = BANGKOK_SVC) 
  ) 
)

修改后立即生效，无需重启监听。

检查TNS连接：

[oracle@primarydb admin]$ sqlplus miguel/XXXXX@bangkok

Last Successful login time: Thu Mar 09 2023 14:16:00 +08:00

Connected to: Oracle Database 19c

连接成功。

配置多个TNS连接串

定义多个TNS连接串：

[oracle@primarydb admin]$ cat tnsnames.ora 
BANGKOK = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = BANGKOK_SVC)  
    ) 
  )

BANGKOKpri = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = BANGKOK_SVC) 
    )
  )

使用tnsping命令解析tnsnames：

[oracle@primarydb admin]$ tnsping bangkok

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BANGKOK_SVC))) OK (0 msec)

[oracle@primarydb admin]$ tnsping bangkokpri

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BANGKOK_SVC))) OK (0 msec)

[oracle@primarydb admin]$ tnsping BANGKOKpri

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BANGKOK_SVC))) OK (0 msec)

都能解析成功。

检查能否正常连接：

[oracle@primarydb admin]$ sqlplus miguel/XXXXX@bangkokpri

Last Successful login time: Thu Mar 09 2023 14:31:11 +08:00

Connected to: Oracle Database 19c 

SQL> exit

[oracle@primarydb admin]$ sqlplus miguel/XXXXX@BANGKOKpri

Last Successful login time: Thu Mar 09 2023 14:35:23 +08:00

Connected to: Oracle Database 19c 

SQL> exit

[oracle@primarydb admin]$ sqlplus miguel/XXXXX@BANGKOKPRI

Last Successful login time: Thu Mar 09 2023 14:35:34 +08:00

Connected to: Oracle Database 19c 

SQL> exit