客户端使用SCNAIP连接11G RAC数据库报错ORA-12545解决
来源:互联网 发布:二叉排序树算法java 编辑:程序博客网 时间:2024/05/16 13:54
客户端配置及报错:
[oracle@bys3 admin]$ cat tnsnames.orabysrac =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.228)(PORT = 1521)) --其中HOST = 192.168.1.228 这里的IP为RAC的SCANIP。
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bysrac)
)
)
[oracle@bys3 admin]$ tnsping bysrac
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 20:25:19
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.228)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac)))
OK (10 msec) --tnsping可以正常联通。
客户端的使用sqlplus bys/bys@bysrac登陆时报错:ORA-12545: Connect failed because target host or object does not exist
[oracle@bys3 admin]$ sqlplus bys/bys@bysrac
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 1 20:10:31 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
################
解决方法:修改RAC的local_listener参数,将参数值中HOST=的值改为当前节点的VIP或者scanip
注意:HOST主机参数为当前节点vip的ip地址时,客户端通过SCANIP或者VIP都可以连接到RAC数据库库--我这里就是修改为VIP的IP。--其实应该修改为SCANIP,客户端统一使用SCANIP来连接。HOST主机参数为scanip地址,则修改完参数后,scanip能够正常使用,但如果有客户端配置使用vip的话,连接时则会出现错误:ORA-12514: TNS:listener does not currently know of service requested in connect descriptor。
具体修改步骤:
查看RAC的SCANIP状态及监听状态
[oracle@bysrac1 ~]$ su - gridPassword:
[grid@bysrac1 ~]$ srvctl config scan
SCAN name: bysrac-cluster-scan, Network: 1/192.168.1.128/255.255.255.128/eth0
SCAN VIP name: scan1, IP: /bysrac-cluster/192.168.1.228
[grid@bysrac1 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node bysrac1
[grid@bysrac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-JAN-2014 20:10:13
Copyright (c) 1991, 2009, 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.1.0 - Production
Start Date 28-DEC-2013 20:06:56
Uptime 4 days 0 hr. 3 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /g01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /g01/app/grid/diag/tnslsnr/bysrac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.226)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.221)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "bysrac" has 1 instance(s).
Instance "bysrac1", status READY, has 1 handler(s) for this service...
Service "caiwu" has 1 instance(s).
Instance "bysrac1", status READY, has 1 handler(s) for this service...
Service "jiaoyi" has 1 instance(s).
Instance "bysrac1", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@bysrac1 ~]$ exit
[oracle@bysrac1 ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.1.221 bysrac1 bysrac1.bys.com
192.168.1.226 bysrac1-vip
192.168.1.222 bysrac2 bysrac2.bys.com
192.168.1.227 bysrac2-vip
192.168.10.1 bysrac1-priv
192.168.10.2 bysrac2-priv
192.168.1.228 bysrac-cluster bysrac-cluster-scan
实验1:修改RAC的local_listener参数,将参数值中HOST=的值改为当前节点的VIP值--RAC多节点都需要改
[oracle@bysrac1 ~]$ sqlplus bys/bysBYS@ bysrac>select instance_name from v$instance;
INSTANCE_NAME
----------------
bysrac1
BYS@ bysrac1>show parameter local_l -------查看local_listener 参数的值
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=bysr
ac1-vip)(PORT=1521))))
BYS@ bysrac1>alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.226)(PORT=1521))))' sid='bysrac1';
System altered. --这里HOST=192.168.1.226,修改为该节点VIP的IP值。我这里只修改了一个节点的,如果多个节点,照此方法在其它节点修改。
BYS@ bysrac1>show parameter local_l
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=192.
168.1.226)(PORT=1521))))
BYS@ bysrac1>exit ---退出前可以手工注册下监听alter system register; 我这里没做也是可以的。
客户端tnsnames.ora 中使用SCANIP或者VIP任一都可以登陆到RAC数据库
1.使用配置 SCNAIP的方法可以登陆:
[oracle@bys3 admin]$ tnsping bysracTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 20:25:19
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.228)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac)))
OK (10 msec) --tnsping可以正常联通,HOST = 192.168.1.228这里tnsnames.ora里已经修改为RAC的一个节点的SCANIP了。。节约篇幅,没贴tnsnames.ora
[oracle@bys3 admin]$ sqlplus bys/bys@bysrac
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 1 20:34:53 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
BYS@ bysrac>select instance_name from v$instance;
INSTANCE_NAME
----------------
bysrac1
BYS@ bysrac>exit
2.客户端使用VIP也可以连接到RAC数据库
[oracle@bys3 admin]$ tnsping bysracTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 20:52:54
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.226)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac)))
OK (10 msec) -----tnsping可以正常联通,HOST = 192.168.1.226这里tnsnames.ora里已经修改为RAC的一个节点的VIP了。。节约篇幅,没贴tnsnames.ora
[oracle@bys3 admin]$ sqlplus bys/bys@bysrac
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 1 20:52:57 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
BYS@ bysrac>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
bysrac1 OPEN
BYS@ bysrac>exit
################
实验2:修改RAC的local_listener参数,将参数值中HOST=的值改为scanip
[oracle@bysrac1 ~]$ sqlplus bys/bys
BYS@ bysrac1>show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=192.
168.1.226)(PORT=1521))))
BYS@ bysrac1>alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.228)(PORT=1521))))';
System altered.
BYS@ bysrac1>show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=192.
客户端使用VIP时报错ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
[oracle@bys3 admin]$ tnsping bysrac
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 21:12:40
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.226)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac)))
OK (10 msec) -----tnsping可以正常联通,HOST = 192.168.1.226,这里tnsnames.ora里已经修改为RAC的一个节点的VIP了。。节约篇幅,没贴tnsnames.ora
[oracle@bys3 admin]$ sqlplus bys/bys@bysrac
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 1 21:12:44 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
客户端使用SCANIP可以正常连接
[oracle@bys3 admin]$ tnsping bysrac
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 21:13:01
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.228)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac)))
OK (10 msec) --tnsping可以正常联通,HOST = 192.168.1.228这里tnsnames.ora里已经修改为RAC的一个节点的SCANIP了。。节约篇幅,没贴tnsnames.ora
[oracle@bys3 admin]$ sqlplus bys/bys@bysrac
BYS@ bysrac>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
bysrac1 OPEN
Elapsed: 00:00:00.01
BYS@ bysrac>
- 客户端使用SCNAIP连接11G RAC数据库报错ORA-12545解决
- 客户端使用SCNAIP连接11G RAC数据库报错ORA-12545解决
- 11G RAC 启动数据库 报错; ORA-00119 ORA-00132
- oracle10g的rac中,客户端连接报ora-12545错误
- 使用统一服务名连接RAC数据库报ORA-12545错误
- oracle 11g 使用一段时间监听还在客户端连接不上,报ORA-12537
- 客户端链接RAC报错ORA-12545 的处理
- 客户端链接RAC报错ORA-12545 的处理
- plsql连接oracle 11g 报ora-12154解决
- 10G RAC节点2宕机通过修改listener.ora实现客户端通过节点2VIP连接到数据库
- 解决oracle客户端可以连接11g rac vip,但是不能连接scan ip问题
- Oracle 11gR2 RAC 使用scan IP无法连接数据库(ORA-12545)
- Oracle 11gR2 RAC 使用scan IP无法连接数据库(ORA-12545)
- 解决连接oracle 11g报ORA-01034和ORA-27101的错误
- 解决连接Oracle 11g报ORA-01034和ORA-27101的错误
- PLSQL Developer连接数据库报错ora-12514解决
- oracle 10G r2 RAC连接ORA-12545错误
- 客户端连接数据库服务端报ORA-12170,连接超时错误
- 设置双核浏览器的浏览模式<meta name=“renderer” content=“webkit|ie-comp|ie-stand”>
- 一个小玩意PHP-Valgrind的介绍
- PSNR)
- U盘启动盘修复ubuntu系统
- SQL Server 2008 安装过程中出现问题解决方案
- 客户端使用SCNAIP连接11G RAC数据库报错ORA-12545解决
- String 字符串处理
- php点运算符(.)的反思
- 去掉名字后的空格
- code review研究与学习
- openssl windows编译 32位&64位
- LINQ
- Yac (Yet Another Cache) – 无锁共享内存Cache
- 美核导弹部队曝“史上最大丑闻”