备库批量查询失败的原因分析

目前线上有一套环境是10gR2的，采用了一主两备的架构。在其中一个备库上每天凌晨会开放一个窗口运行一些批量的查询，目前使用dg broker会在指定的时间把备库置为read-only,查询完毕之后修改为online状态。
但是近几天开发的同事突然找到我说，最近几天开始批量查询会频频报错，希望我帮忙查看一下。
语句运行报错，听起来原因应该很简单吧，最大的可能就是备库没有打开，或者是ddl,dml语句之类的。但是看到错误日志，让我着实有些奇怪。
错误日志如下，可以看到是一条查询语句。
[2016.03.06 04:10:02.352]org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL [select bind_flag
from test_billing where cn_master=?]; SQL state [60000]; error code [604];
ORA-00604: error occurred at recursive SQL level 1

[2016.03.06
04:10:02.352]ORA-16000: database open for read-only access

[2016.03.06
04:10:02.352]; nested exception is java.sql.SQLException: ORA-00604: error
occurred at recursive SQL level 1

[2016.03.06
04:10:02.352]ORA-16000: database open for read-only access

[2016.03.06
04:10:02.352]
除了这些信息，应用端就没有更多的日志了，而从数据库层面来看，得到的日志如下。

Completed:
ALTER DATABASE OPEN READ ONLY

Mon Mar
07 03:45:14 CST 2016

Redo
Shipping Client Connected as PUBLIC

--
Connected User is Valid

RFS[99]:
Assigned to RFS process 28797

RFS[99]:
Identified database type as 'physical standby'

RFS[99]:
Archived Log: '/U01/app/oracle/admin/acccomdb/arch/1_24776_782846320.dbf'

Mon Mar
07 07:02:59 CST 2016

ORA-1652:
unable to extend temp segment by 128 in tablespace                 TEMP2

Mon Mar
07 07:02:59 CST 2016

ORA-1652:
unable to extend temp segment by 128 in tablespace                 TEMP2

Mon Mar
07 08:10:03 CST 2016

Stopping
background process MMNL

Mon Mar
07 08:10:04 CST 2016

Stopping
background process MMON

Mon Mar
07 08:10:05 CST 2016

Active
process 10256 user 'oracle' program 'oracle@testdb.cyou.com'

Active
process 10258 user 'oracle' program 'oracle@testdb.cyou.com'

Active
process 10254 user 'oracle' program 'oracle@testdb.cyou.com'

Active
process 10401 user 'oracle' program 'oracle@testdb.cyou.com'

CLOSE:
waiting for server sessions to complete.

CLOSE:
all sessions shutdown successfully.

通过上面的日志也分析不出哪里可能有很明显的错误，TEMP的也算是一个引子吧。在排查了各种可能出现的场景后，却始终得不到有效的思路。
我在备库想看看这个问题是否发生。于是根据日志中的语句查询了一下，发现没有任何问题。

select
bind_flag from test_billing where cn_master=?  语句可以顺利输出结果。

自己也尝试了dml的情况，错误信息也会有所不同。

SQL>
update test_billing set  bind_flag=0
where cn_master='660078174';

update test_billing
set  bind_flag=0 where
cn_master='660078174'

       *

ERROR at
line 1:

ORA-01552:
cannot use system rollback segment for non-system tablespace 'ACC_DATA'

这个时候就停下来，开始理一理思路，之前从来没有反馈过这个问题，而问题是在最近发生的。那么应用端是否在最近有什么变化呢，得到的反馈是在1月中下旬有一次变更，但是这都过去好久了，不足以佐证现在的问题。

那数据库端有什么变化吗？唯一的变更就是在最近把这个批量查询从备库2迁到了备库1去做。这个也是近1个月以前完成的事情了，也不足以证明最近的问题。

而从数据库层面，如果存在问题，那看似只有bug的可能性了，但是查了mos一圈，发现了几种可能的场景，但是都和目前的情况不符合，目前查到有两种场景，一种是略微复杂的查询，一种是带有db link的查询。参考链接如下：

Dblink
on Physical standby - ORA-16000 (Doc ID 1296288.1)

ORA-16000
With A Semantic Query On A Read-only Database (Doc ID 1928638.1)

目前的情况是这个语句非常简单，实在找不出来可能的原因了。

开发的同事也催的比较紧，但是感觉从数据库层面得到的信息着实有限。无奈之下，开启了手工debug方式。就从alert日志中的那个关于temp的报错开始分析。

还是采用之前的一种分析方式，用两个脚本来分析。

第一个脚本是 check_temp.sh

function
get_temp_usage

{

sqlplus
-s  / as sysdba <<eof <="" eof

set time
on

set
pages 100

set
linesize 200

col
sysdate format a10

col
username format a15

col sid
format 9999

col
serial# format 99999

col
blocks format 99999999

col
sql_text format a70

set
feedback off

select
systimestamp from dual;

set
feedback on

SELECT
sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text

  FROM v\$session a, v\$sort_usage b, v\$sqlarea
c

  WHERE b.tablespace = 'TEMP'

  and a.saddr = b.session_addr

  AND c.address= a.sql_address

  AND c.hash_value = a.sql_hash_value

  AND b.blocks*8192 > 0

  ORDER BY b.tablespace, b.blocks;

EOF

}

get_temp_usage

第二个脚本 是tmp.sh

for i in
{1..36000}

do

sh
check_temp.sh >> check_temp.log

sleep 10

done
然后在第二天上班的时候就会得到一个比较详细的列表了。

但是今天到公司之后查看详细记录，发现竟然返回都是空行，看来这些语句运行都成了问题。

打开备库又运行了一次查询，sqlplus中运行没有任何问题。

这个时候就有些怀疑是jdbc驱动的问题了，但是这个似乎还是无从考证了。而且查看mos也没有得到确凿的证据。

于是打电话给开发，进一步做了确认，想他们确认连接的IP，端口和用户，然后得到了下面的这个配置信息。
select bind_flag from test_billing where cn_master=?
jdbc:oracle:thin:@10.127.1.17:1525:acccomdb
               
TEST_SHINK
看起来也没有什么问题，TEST_SHINK是一个连接用户，会连接到owner用户的表。

和开发进一步进行沟通和协调，想让他们给半个小时左右的时间继续跑一下失败的批量查询，看看我从数据库端能够发现什么。

但是应用运行之后，查看系统级，没有任何的抖动，数据库层面也可以看到应用是连接进来了。

然后开发就提供了最新的日志。
[2016.03.09 11:04:56.274]org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL [select bind_flag
from test_billing where cn_master=?]; SQL state [60000]; error code [604];
ORA-00604: error occurred at recursive SQL level 1
[2016.03.09 11:04:56.274]ORA-16000: database open for read-only access
[2016.03.09 11:04:56.274]; nested exception is java.sql.SQLException:
ORA-00604: error occurred at recursive SQL level 1
[2016.03.09 11:04:56.274]ORA-16000: database open for read-only access
这个错误和之前的基本是一致的。

看着这个日志就坐在那里发呆，还有什么特别的方式来调试一下这个问题呢。实在不行就只能10046来做一些分析了。但是在做这个之前，我还是耐着性子再看一看。

于是我切换到了这个连接用户，
alter session set current_schema= TEST_SHINK;
然后再次运行这个报错的语句，终于得到了期望之中的报错。
SQL> select bind_flag from test_billing where cn_master= '660078174';
select bind_flag from test_billing where cn_master= '660078174'
                     
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
有了报错信息，就有了处理问题的方向，于是换了一个方式。采用owenr用户的方式来查看，就没有问题了。
SQL> select bind_flag from acc.test_billing where cn_master= '660078174';
 BIND_FLAG
----------
    689537

这个问题是怎么回事呢？

TEST_SHINK下的都是同义词，指向ACC这个owner用户，那么这个同义词有什么特别的呢。进一步查看，发现同义词test_billing是INVALID的。
 看起来这个问题总算有了解决的思路，我们可以在主库直接编译运行即可，然后这部分变更会同步到备库。当然同义词为失效的时候，直接查询也会自动编译。所以在主库只需要运行一条select语句即可。在主库中确认，发现TEST_SHINK下的同义词test_billing确实也是INVALID的。

那么在主库运行下面的sql即可。
> select count(*)from TEST_SHINK.TEST_BILLING where cn_master= '660078174';
  COUNT(*)
----------
         1

然后再次查看状态，同义词的状态就自动为VALID的了。
然后在备库开启日志应用，然后切换回只读状态，再次验证就没有问题了，然后让应用进一步确认，发现终于正常了。
那么还有一个问题需要解决的就是，根据开发的反馈，应该是从3月4日开始出现了问题，这个test_billing是否在3月3日左右出现了一些变化，导致同义词失效呢。

可以从dba_objects进行论证，当然也是带着一丝侥幸，得到了期望的结果。最新的一次ddl操作是在3月3日的早晨。

> SELECT
OWNER,OBJECT_NAME,OBJEcT_TYPE,LAST_DDL_TIME,STATUS FROM DBA_OBJECTS WHERE
OBJECT_NAME='TEST_BILLING';

OWNER               
OBJECT_NAME         
OBJECT_TYPE        
LAST_DDL_TIME       STATUS
-------------------- -------------------- -------------------
------------------- ----------
ACC                 
TEST_BILLING        
TABLE              
2016-03-03 07:38:28 VALID
APP_ACC_USER         TEST_BILLING        
SYNONYM            
2016-03-03 07:38:29 VALID
TEST_SHINK           TEST_BILLING        
SYNONYM            
2016-03-09 11:16:59 VALID
因为这个用户应用只在备库使用，所以就导致了这个看起来奇怪的问题，看来都是事出有因，耐心一些，细致一些还是会有发现。

时间： 2024-10-23 15:18:32

备库批量查询失败的原因分析的相关文章

11g备库无法开启ADG的原因分析

今天碰到一个有些奇怪的问题,但是奇怪的现象背后都是有本质的因果. 下午在做一个环境的检查时,发现备库是在mount阶段,这可是一个11gR2的库,没有ADG实在是太浪费了,对于这种情况感觉太不应该了. 所以尝试启动至open阶段,发现状态一直是read only,在ADG中应该是READ ONLY WITH APPLY才对啊. 使用dg broker设置为READ-ONLY,备库的数据库日志如下:      Standby Database:           stestdb3, Enable

Win7蓝牙驱动安装失败的原因分析与解决方法

  蓝牙是一种支持设备短距离通信的无线电技术,现在不管是手机还是电脑都已经普遍的使用蓝牙功能了.如果Win7系统电脑没有安装蓝牙驱动的话,是需要安装后才能使用的.最近,有用户在安装蓝牙驱动的时候,发现安装失败.怎么办?出现这样的问题要怎么解决?现在就和大家说一下Win7蓝牙驱动安装失败的原因分析与解决方法. 步骤如下: 一.原因分析: 优化软件将系统INF文件夹自带文件误删了. 二.解决方法: 1.进入C盘系统分区的Windowsinf目录,查看是否有"bth.inf"文件,备注:如果

php resizeimage 部分jpg文件 生成缩略图失败的原因分析及解决办法_php实例

今天遇到GD的resizeimage 函数处理jpg后缀文件的缩略图的时候 提示该图片不是合法的jpg图片并报错 <b>Warning</b>: imagecreatefromjpeg(): gd-jpeg, libjpeg: recoverable error: Invalid SOS parameters for sequential JPEG 国内网上查了很多资料也没找到有效的解决办法,原来只要把 GD的jpeg文件支持打开即可解决 ini_set('gd.jpeg_igno

小米红米Note刷机什么原因 红米Note刷机失败的原因分析

红米Note刷机失败的原因分析 1.小米红米Note的ROM刷机包存在问题 最常见的就是刷机包问题,通常因为刷机包下载的并飞您的小米 红米 Note机型,或者刷机包制作者制作ROM出现问题,另外就是刷机包下载过程中出现下载不完整的错误. 2.小米红米Note的Recovery版本不对 有的机友用官方Recovery去刷第三方ROM,是无法刷入的.另外,较为老版本的Recovery无法兼容一些新的刷机脚本,会导致刷机无法执行,我们推荐大家用基于CWM的Recovery. 3.小米红米Note的电量

ubuntu16.04下vim安装失败的原因分析及解决方案_Linux

先给大家说下问题描述? 重装了ubuntu系统,安装vim出现了以下问题: sudo apt-get install vim 正在读取软件包列表... 完成 正在分析软件包的依赖关系树 正在读取状态信息... 完成 有一些软件包无法被安装.如果您用的是 unstable 发行版,这也许是 因为系统无法达到您要求的状态造成的.该版本中可能会有一些您需要的软件 包尚未被创建或是它们已被从新到(Incoming)目录移出. 下列信息可能会对解决问题有所帮助: 下列软件包有未满足的依赖关系: vim :

在Yii2中使用Pjax导致Yii2内联脚本载入失败的原因分析_php实例

当我用defunkt/jquery-pjax载入Yii2的ActiveForm时发生一个错误,正常情况下是 ActiveForm的两个js应该先载入,而实际情况是 typeError:JQuery(...).yiiActiveForm is not a function. 在github的issues对这个问题已经讨论并得到了解决. Pjax首先通过html()执行内联的<script>,然后才通过executeScriptTags()执行带着src的<script>,所以导致找不

Oracle备库TNS连接失败的分析

今天在测试12c的temp_undo的时候,准备在备库上测试一下,突然发现备库使用TNS连接竟然失败. 抛出的错误如下: $ sqlplus sys/oracle@testdb as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 15:30:10 2016 Copyright (c) 1982, 2014, Oracle.  All rights reserved. ERROR: ORA-12514: TNS:listen

sql 查询慢的原因分析_MsSql

查询速度慢的原因很多,常见如下几种: 1.没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2.I/O吞吐量小,形成了瓶颈效应. 3.没有创建计算列导致查询不优化. 4.内存不足 5.网络速度慢 6.查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 7.锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) 8.sp_lock,sp_who,活动的用户查看,原因是读写竞争资源. 9.返回了不必要的行和列 10.查询语句不好,没有优化 ●可以通过如下方法来优化查询

Jquery Ajax请求文件下载操作失败的原因分析及解决办法

jQuery确实是一个挺好的轻量级的JS框架,能帮助我们快速的开发JS应用,并在一定程度上改变了我们写JavaScript代码的习惯. 废话少说,直接进入正题,我们先来分析一下失败的原因 一.失败的原因 那是因为response原因,一般请求浏览器是会处理服务器输出的response,例如生成png.文件下载等,然而ajax请求只是个"字符型"的请求,即请求的内容是以文本类型存放的.文件的下载是以二进制形式进行的,虽然可以读取到返回的response,但只是读取而已,是无法执行的,说白