【Oracle】Oracle配置大量的tns连接串，但是指向同一个库、SRVCTL详解/删库操作

Tzq@2018

已于 2024-03-12 12:51:59 修改

阅读量348

点赞数 2

分类专栏： Oracle 文章标签： oracle

于 2024-03-08 18:04:21 首次发布

本文链接：https://blog.csdn.net/tttzzzqqq2018/article/details/136568617

版权

Oracle 专栏收录该内容

121 篇文章 18 订阅

订阅专栏

【Oracle】Oracle配置大量的tns连接串，但是指向同一个库、SRVCTL详解/删库操作

一、Oracle配置大量的tns连接串，但是指向同一个库
二、SRVCTL详解
三、删库操作
四、参考资料

Oracle 需要配置大量的tns连接串，但是指向同一个库，可以创建service实现

一、Oracle配置大量的tns连接串，但是指向同一个库

Oracle 需要配置大量的tns连接串，但是指向同一个库

当前库的信息（11gR2 3节点RAC）

#监听信息
[grid@testosa:/home/grid]$srvctl config scan
SCAN name: scanip, Network: 1/192.168.1.0/255.255.255.0/ens33
SCAN VIP name: scan1, IP: /scanip/192.168.1.80
[grid@testosa:/home/grid]$lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAR-2024 14:36:38

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-MAR-2024 13:56:08
Uptime                    0 days 0 hr. 40 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/app/11.2.0/grid/log/diag/tnslsnr/testosa/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.80)(PORT=1521)))
Services Summary...
Service "rac_db" has 3 instance(s).
  Instance "racdb_1", status READY, has 1 handler(s) for this service...
  Instance "racdb_2", status READY, has 1 handler(s) for this service...
  Instance "racdb_3", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 3 instance(s).
  Instance "racdb_1", status READY, has 1 handler(s) for this service...
  Instance "racdb_2", status READY, has 1 handler(s) for this service...
  Instance "racdb_3", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@testosa:/home/grid]$

相关参数信息

SQL> show parameter name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string                 rac_db
db_unique_name                       string                 rac_db
global_names                         boolean                FALSE
instance_name                        string                 racdb_1
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string                 rac_db

可以创建service实现

srvctl add service -d rac_db -s tns1 -r racdb_1 -a racdb_2 -P basic
srvctl add service -d rac_db -s tns2 -r racdb_1 -a racdb_2 -P basic
srvctl add service -d rac_db -s tns3 -r racdb_1 -a racdb_2 -P basic
srvctl add service -d rac_db -s tns4 -r racdb_1 -a racdb_2 -P basic

srvctl start service -d rac_db -s tns1
srvctl start service -d rac_db -s tns2
srvctl start service -d rac_db -s tns3
srvctl start service -d rac_db -s tns4

执行过程

[oracle@testosa:/home/oracle]$srvctl add service -d rac_db -s tns1 -r racdb_1 -a racdb_2 -P basic
[oracle@testosa:/home/oracle]$srvctl add service -d rac_db -s tns2 -r racdb_1 -a racdb_2 -P basic
[oracle@testosa:/home/oracle]$srvctl add service -d rac_db -s tns3 -r racdb_1 -a racdb_2 -P basic
[oracle@testosa:/home/oracle]$srvctl add service -d rac_db -s tns4 -r racdb_1 -a racdb_2 -P basic
[oracle@testosa:/home/oracle]$
[oracle@testosa:/home/oracle]$srvctl start service -d rac_db -s tns1
[oracle@testosa:/home/oracle]$srvctl start service -d rac_db -s tns2
[oracle@testosa:/home/oracle]$srvctl start service -d rac_db -s tns3
[oracle@testosa:/home/oracle]$srvctl start service -d rac_db -s tns4
[oracle@testosa:/home/oracle]$

查询相关信息

参数信息

#testosa节点查询
SQL> show parameter name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string                 rac_db
db_unique_name                       string                 rac_db
global_names                         boolean                FALSE
instance_name                        string                 racdb_1
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string                 tns3, tns2, tns1, tns4
SQL>

监听信息

[grid@testosa:/home/grid]$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAR-2024 15:32:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-MAR-2024 13:56:00
Uptime                    0 days 1 hr. 36 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/app/grid/diag/tnslsnr/testosa/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.84)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac_db" has 1 instance(s).
  Instance "racdb_1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
  Instance "racdb_1", status READY, has 1 handler(s) for this service...
Service "tns1" has 1 instance(s).
  Instance "racdb_1", status READY, has 1 handler(s) for this service...
Service "tns2" has 1 instance(s).
  Instance "racdb_1", status READY, has 1 handler(s) for this service...
Service "tns3" has 1 instance(s).
  Instance "racdb_1", status READY, has 1 handler(s) for this service...
Service "tns4" has 1 instance(s).
  Instance "racdb_1", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@testosa:/home/grid]$

测试连接

#192.168.1.80 为scanip
#192.168.1.81 为testosa的ip

#以下8个连接串都测试连接成功
sqlplus sys/oracle@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.80)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tns1)))" as sysdba
sqlplus sys/oracle@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.80)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tns2)))" as sysdba
sqlplus sys/oracle@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.80)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tns3)))" as sysdba
sqlplus sys/oracle@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.80)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tns4)))" as sysdba
sqlplus sys/oracle@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.81)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tns1)))" as sysdba
sqlplus sys/oracle@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.81)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tns2)))" as sysdba
sqlplus sys/oracle@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.81)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tns3)))" as sysdba
sqlplus sys/oracle@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.81)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tns4)))" as sysdba

二、SRVCTL详解

srvctl add service -d testdb -s tns1 -r testdb1 -a testdb2 -P basic
srvctl add service -d testdb -s tns2 -r testdb1 -a testdb2 -P basic
srvctl start service -d testdb -s tns1
srvctl start service -d testdb -s tns2

-S        服务名
-r        首选实例名
-a        备选实例名
-P        TAF策略

三、删库操作

startup restrict mount;
drop database;

四、参考资料

https://www.cnblogs.com/eniniemand/p/14327795.html

Tzq@2018

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
【Oracle】Oracle配置大量的tns连接串，但是指向同一个库、SRVCTL详解/删库操作

【详解】【Oracle】Oracle配置大量的tns连接串，但是指向同一个库、SRVCTL详解/删库操作
复制链接

扫一扫

专栏目录