Oracle配置多个TNSNAMES连接串

2023-03-11 06:28| 来源: 网络整理| 查看: 265

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 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 9 14:31:11 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Thu Mar 09 2023 14:16:00 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0

连接成功。

配置多个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 Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 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 Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 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 Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> exit

【本文地址】

公司简介

联系我们