- 浏览: 978826 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
孤星119:
好熟悉的数据库字段啊, 上家公司做的项目每天都跟这些字段打招呼 ...
Oracle exp compress参数引起的空间浪费 -
itspace:
quxiaoyong 写道遇到个问题,网上一搜,全他妈这篇文章 ...
数据库连接错误ORA-28547 -
quxiaoyong:
遇到个问题,网上一搜,全他妈这篇文章。你转来转去的有意思吗?
数据库连接错误ORA-28547 -
hctech:
关于version count过高的问题,不知博主是否看过ey ...
某客户数据库性能诊断报告 -
itspace:
invalid 写道写的不错,我根据这个来安装,有点理解错误了 ...
AIX 配置vncserver
客户一rac数据库发生故障,1号节点的vip漂移至2号节点,由于部分业务没有采用负载均衡模式连接,即直接连接1号节点vip,当1号节点的vip漂移至2号节点后,业务连接出现异常。
主要表现为tnsping出现TNS-12541,sqlplus连接出现ORA-12520
连接配置文件为
~
检查监听状态似乎也正常:
于是检查2号节点监听文件,可以看到参数IP = FIRST,这个参数在单实例默认监听中很少看到,意思为监听只启动在ip为130.36.23.9,130.36.23.10,如果这台主机上有第3个ip,即本案例1号节点的vip,当业务程序采用此vip连接时,将报TNS-12541错误。
于是将监听文件修改如下,采用此配置,监听将会启动在所有ip地址上。
重启监听,但应用连接报如下错误,此错误提示比较明显
修改连接串如下:即将(INSTANCE_NAME = crmdb1)配置去掉,应用连接正常。
故障至此,已经解决。但我们看到实例crmdb1在监听状态显示中存在,且状态为READY
也就意味着这是由PMON进程注册的(监听动态注册,启用event可以跟踪pmon进程注册监听过程,如oradebug Event 10257 trace name context forever, level 16 ),在rac环境中,如启用remote_listener参数,那实例将在rac节点的监听中互相注册。其主要作用就是起到服务器端负载均衡作用,即用户进程连接至监听之后,监听根据pmon的Oracle繁忙程度的注册信息,自动将会话连接至相对空闲的实例,详见metalink 263599.1。
主要表现为tnsping出现TNS-12541,sqlplus连接出现ORA-12520
引用
$ tnsping CRMDB_1
TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production on 16-APR-2011 13:56:35
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
/opt/oracle/product/10.2.0.4/rnd/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = crmdb)))
TNS-12541: TNS:no listener
$ sqlplus "drb/***@ractest"
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Apr 16 16:46:25 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production on 16-APR-2011 13:56:35
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
/opt/oracle/product/10.2.0.4/rnd/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = crmdb)))
TNS-12541: TNS:no listener
$ sqlplus "drb/***@ractest"
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Apr 16 16:46:25 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
连接配置文件为
引用
ractest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(service_name = crmdb)
(INSTANCE_NAME = crmdb1)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(service_name = crmdb)
(INSTANCE_NAME = crmdb1)
)
)
~
检查监听状态似乎也正常:
引用
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=130.36.23.9)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=130.36.23.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "crmdb" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmdbXDB" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 1 handler(s) for this service...
Service "crmdb_XPT" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmsrv1" has 1 instance(s).
Instance "crmdb2", status READY, has 2 handler(s) for this service...
The command completed successfully
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=130.36.23.9)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=130.36.23.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "crmdb" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmdbXDB" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 1 handler(s) for this service...
Service "crmdb_XPT" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmsrv1" has 1 instance(s).
Instance "crmdb2", status READY, has 2 handler(s) for this service...
The command completed successfully
于是检查2号节点监听文件,可以看到参数IP = FIRST,这个参数在单实例默认监听中很少看到,意思为监听只启动在ip为130.36.23.9,130.36.23.10,如果这台主机上有第3个ip,即本案例1号节点的vip,当业务程序采用此vip连接时,将报TNS-12541错误。
引用
LISTENER_CRMDB02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.9)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.10)(PORT = 1521)(IP =FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.9)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.10)(PORT = 1521)(IP =FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
于是将监听文件修改如下,采用此配置,监听将会启动在所有ip地址上。
引用
LISTENER_CRMDB02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = crmdb02_vip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = crmdb02)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = crmdb02_vip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = crmdb02)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
重启监听,但应用连接报如下错误,此错误提示比较明显
引用
$ sqlplus "drb/***@ractest"
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Apr 16 16:42:24 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12521: TNS:listenr does not currently know of instance requested in
connect descriptor
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Apr 16 16:42:24 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12521: TNS:listenr does not currently know of instance requested in
connect descriptor
修改连接串如下:即将(INSTANCE_NAME = crmdb1)配置去掉,应用连接正常。
引用
ractest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(service_name = crmdb)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.36.23.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(service_name = crmdb)
)
)
故障至此,已经解决。但我们看到实例crmdb1在监听状态显示中存在,且状态为READY
引用
Service "crmdb" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmdbXDB" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 1 handler(s) for this service...
Service "crmdb_XPT" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmsrv1" has 1 instance(s).
Instance "crmdb2", status READY, has 2 handler(s) for this service...
The command completed successfully
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmdbXDB" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 1 handler(s) for this service...
Service "crmdb_XPT" has 2 instance(s).
Instance "crmdb1", status READY, has 1 handler(s) for this service...
Instance "crmdb2", status READY, has 2 handler(s) for this service...
Service "crmsrv1" has 1 instance(s).
Instance "crmdb2", status READY, has 2 handler(s) for this service...
The command completed successfully
也就意味着这是由PMON进程注册的(监听动态注册,启用event可以跟踪pmon进程注册监听过程,如oradebug Event 10257 trace name context forever, level 16 ),在rac环境中,如启用remote_listener参数,那实例将在rac节点的监听中互相注册。其主要作用就是起到服务器端负载均衡作用,即用户进程连接至监听之后,监听根据pmon的Oracle繁忙程度的注册信息,自动将会话连接至相对空闲的实例,详见metalink 263599.1。
发表评论
-
buffer cache 的内部结构
2020-03-18 14:21 579BUFFER CACHE作为数据块的 ... -
Oracle OMC介绍
2020-03-18 13:19 487Oracle管理云服务(OMC)的大数据平台,自动收集的企业 ... -
参加Oracle勒索病毒防范专题培训会议
2019-09-27 17:15 5132019年7月22日,受邀参加Oracle勒索病毒防范专题培训 ... -
记一次内存换IO的Oracle优化
2019-09-27 16:50 827某客户数据库从P595物理 ... -
如何定位Oracle SQL执行计划变化的原因
2019-07-03 14:49 1460性能优化最难的是能够 ... -
如何定位Oracle SQL执行计划变化的原因
2018-10-30 09:24 1185性能优化最难的是能够 ... -
数据库性能优化目标
2018-10-08 10:59 518从数据库性能优化的场 ... -
数据库无法打开的原因及解决办法
2018-10-05 20:45 2120数据库的启动是一个相当复杂的过程。比如,Oracle在启动之前 ... -
怎么样彻底删除数据库?
2018-09-18 11:10 599Oracle提供了drop database命令用来删除数据库 ... -
Oracle减少日志量的方法
2018-09-10 10:17 867LGWR进程将LOG BUFFER中的 ... -
如何快速关闭数据库
2018-09-09 13:14 1233“一朝被蛇咬,十年怕井绳”。在没被“蛇”咬之前,很多DBA喜欢 ... -
关于《如何落地智能化运维》PPT
2018-05-17 10:19 1129在DTCC 2018发表《如何落地智能化运维》演讲,主要内容如 ... -
记录在redhat5.8平台安装oracle11.2容易忽视的几个问题
2018-05-11 19:58 578问题一:ping不通问题 在虚拟机上安装好linux系统后, ... -
《Oracle DBA实战攻略》第一章
2018-05-11 10:42 947即日起,不定期更新《OracleDBA实战攻略》一书电子版,请 ... -
Oracle 12c新特性
2018-05-11 10:33 900查询所有pdb [oracle@gj4 ~]$ sqlplu ... -
关于修改memory_target的值后数据库无法启动的问题
2017-02-28 12:24 3983操作系统:RHEL6.5 数据库版本:11.2.0.4 ... -
10g rac安装error while loading shared libraries libpthread.so.0 问题
2017-02-28 12:22 69511g rac安装在二节点跑脚本一般会报此错误: 解决这个问 ... -
记一次Oracle会话共享模式故障处理过程
2017-02-27 19:16 799故障简述 XXX第八人民医院HIS数据库7月13日11点左右从 ... -
RESMGR:cpu quantum等待事件处理过程
2017-02-27 18:23 2615由于数据库上线过程中出现大量的RESMGR:cpu quant ... -
谈谈log file sync
2014-03-19 14:18 1759数据库中的log file sync等待事件指的是,当user ...
相关推荐
在使用Oracle12cRAC(Real Application Clusters)数据库的过程中,可能会遇到客户端连接时出现ora-12520或ora-12516错误的问题。这两种错误代码分别表示“无法为服务找到可用实例”和“TNS:没有监听器”,通常与...
在使用PL/SQL Developer或其他客户端工具尝试连接Oracle数据库时,可能会遇到`ORA-12541:TNS:无监听程序`的错误。这个错误意味着客户端无法与Oracle数据库服务器建立连接,因为监听程序(Listener)没有运行。监听...
错误描述:oracle远程连接服务器出现 ORA-12170 TNS:连接超时 错误检查:有很多是oracle自身安装的问题,但是我这里服务器配置正常,监听正常,服务正常,远程可以ping通服务器。 这里主要是防火墙问题,解决办法: ...
Oracle RAC 监听器配置文件 listener.ora 详解 Oracle RAC 监听器配置文件 listener.ora 是一个核心配置文件,用于配置 Oracle Net Listener 的参数。本文将详细介绍 listener.ora 文件的配置参数、协议地址参数、...
在客户端配置 TNS 时,需要在 tnsnames.ora 中添加 LOAD_BALANCE=ON 选项。例如: RACDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.170)(PORT = 1521)) (ADDRESS = ...
用oracle数据库新建连接时遇到ora-12505,此问题解决后又出现ora-12519错误,郁闷的半天,经过一番折腾问题解决,下面小编把我的两种解决方案分享给大家,仅供参考。 解决方案一: 今天工作时在新建连接的时候遇到...
主要给大家介绍了关于Oracle在dbca时报:ORA-12547: TNS:lost contact错误的解决方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起看看吧。
- `Listener LISTENER is running on node(s): rac2, rac1`: 表示TNS监听器在rac2和rac1两个节点上都运行着。 #### 命令:`srvctl config listener -a` 此命令用于查看TNS监听器的配置信息。 - **输出示例**: ...
1. 对于RAC集群,`tnsname.ora`中的`HOST`应使用公共IP而不是扫描IP,否则会导致`ORA-28545`错误。 2. 透明网关的工作原理是通过配置文件(如`initdg4msql.ora`)中的信息连接到目标SQL Server数据库。如果要更改SID...
1、ORA-12541:TNS:没有监听器 原因:没有启动监听器或者监听器损坏。若是前者,使用命令net start OracleOraHome10gTNSListener(名字可能有出入)即可;如果是后者,则使用“Net Configuration Assistant”工具向导之...
遇到ORA-00600内部错误时,这是一个表示Oracle内部错误的代码,通常需要Oracle的支持团队进行处理。错误可能与硬件限制、系统性能或特定进程有关。解决方法可能包括增加硬件资源、优化系统设置,或者在某些情况下...
前几天升级Oracle数据库,从11.2.0.1升级至11.2.0.4,安装完成后,打开PL/SQL,显示ORA-12514,如图: 问题阐述 ORA-12514:监听程序当前无法识别链接描述符中请求的服务,简单的来说就是Oracle数据库的监听器配置有...
ORA-12154:TNS:无法解析指定的连接标识符 ORA-12154:TNS:无监听程序 错误分析一、PL/SQL 客户端登录到数据库,如果配置错误会有以上错误,如下图。 这个错误主要是pl/sql客户端的tnsnames.ora配置错误,或者是输入 ...
在本文中,我们将深入探讨如何配置一个 RAC 实例作为主数据库,并配置一个单实例 DG 作为备用数据库。 一、环境与参数规划 在配置之前,我们需要确保所有必要的硬件和软件参数已设定好。以下是环境概述: - 主库...
TNS,全称为Transparent Network Substrate,透明网络子结构,是Oracle数据库提供的一种网络服务,用于管理和解析数据库连接信息。 在安装Oracle客户端后,TNS配置文件`tnsnames.ora`位于`instantclient_11_2\...
SID=racdbexport TNS_ADMIN=$ORACLE_HOME/network/adminexport NLS_LANG=AMERICAN_AMERICA.UTF8source ~/.bash_profile检查系统限制//确保系统满足 Oracle RAC 的最小限制要求,包括 ulimit 和 semsys 配置ulimit -...
ORA-12560: TNS:protocol adapter error Cause: A generic protocol adapter error occurred. Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error ...
- **故障现象**:连接数据库时出现ORA-12541错误,提示“TNS: no listener”。 - **原因分析**:客户端tnsnames.ora配置文件中的监听器设置不正确。 - **解决方法**: 1. 确认监听器已经启动。 2. 检查客户端...
- **故障现象**: 连接数据库时报错`ORA-12541: TNS: no listener`。 - **原因分析**: 可能是客户端`tnsnames.ora`配置文件中的监听端口号设置不正确,或是数据库服务器的监听服务未启动。 - **解决方法**: 1. ...