据我所知,故障转移配置取决于JDBC驱动程序。在Oracle的情况下,您可以使用连接描述符进行配置。因此,在您的情况下,您需要将以下内容放入tnsnames.ora文件中:
tnsnames.ora
。
CONNECTION_WITH_FAILOVER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = secondary.com)(PORT = 1521))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = preconnect)
(RETRIES = 180)
(DELAY = 10)
)
)
)
然后在你的配置文件中:
spring.datasource.url=jdbc:oracle:thin:@CONNECTION_WITH_FAILOVER
当然,您可能没有或不想使用tnsnames.ora文件,这种情况下,您可以将连接描述符作为JDBC连接字符串的一部分:
spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=secondary.com)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=180)(DELAY=10))))
有关连接描述符、tnsnames.ora
的更多详细信息和针对Oracle DB配置故障转移,请参见以下链接:
请注意,所有数据库的服务名称必须相同,因此我在您的配置中将db1
和db2
替换为db
。
如果您想要不同的服务名称,则必须通过编程方式配置单独的数据源(如Sheetal Mohan Sharma所述)。
编辑:
- 你收到的错误表明你尝试连接到服务器上不存在的服务名称 - 更多信息在这里
我今天重新仔细阅读了文档(特别是我上面链接的PDF),似乎在连接字符串中指定辅助服务名称是可能的,因此在您的情况下,在tnsnames.ora中的条目如下:
CONNECTION_WITH_FAILOVER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = secondary.com)(PORT = 1521))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = preconnect)
(RETRIES = 180)
(DELAY = 10)
(BACKUP = db2)
)
)
)
并且在application.properties中作为JDBC URL:
spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=secondary.com)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db1)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=180)(DELAY=10)(BACKUP = db2))))
我目前没有配置这样的设置,所以我将其放在了我的tnsnames.ora文件中:
CONNECTION_WITH_FAILOVER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = google.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = my-actual-database)(PORT = my-db-port))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = my-service-name)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = preconnect)
(RETRIES = 1)
(DELAY = 1)
)
)
)
为了模拟主连接失败(因为显然google.com上没有Oracle数据库),我使用连接URL:jdbc:oracle:thin:@CONNECTION_WITH_FAILOVER
成功连接到了我的数据库。
我还尝试过直接在JDBC URL中使用连接描述符:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=google.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=my-actual-database)(PORT=my-db-port))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=my-service-name)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=1)(DELAY=5))))
它也可以正常工作,但两次连接都花费了相当长的时间才建立起来(但这可能与我的网络配置或驱动程序的连接超时值有关)
- 确保您根据自己的需要配置
FAILOVER_MODE
参数-特别考虑RETIRES和DELAY值-在我提供的示例中,我使用了180次重试和每次重试之间的10秒延迟,加上每次重试的连接超时,驱动程序实际上可能需要很长时间才能切换到故障转移连接。