ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > [20121127]ora-0060[2662]错误的产生以及模拟.txt

[20121127]ora-0060[2662]错误的产生以及模拟.txt

原创 Linux操作系统 作者：lfree 时间：2012-11-27 16:51:20 0 删除 编辑
[20121127]ora-0060[2662]错误的产生以及模拟.txt

转抄链接：http://www.xifenfei.com/1509.html

一、错误现象（alert日志中）
Errors in file /opt/oracle/admin/conner/udump/conner_ora_31607.trc:
ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], []

二、错误解释
ORA-600 [2662] "Block SCN is ahead of Current SCN"，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的
dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数，分别代表不同的含义
ORA-600 [2662] [a] [b] {c} [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg {c} dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
注：897694446<897695488

三、错误原因
1.使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库
2.硬件错误引起数据库没法写控制文件和重做日志文件
3.错误的部分恢复数据库
4.恢复了控制文件但是没有使用recover database using backup controlfile进行恢复
5.数据库crash后设置了_DISABLE_LOGGING隐含参数
6.在并行服务器环境中DLM存在问题

四、解决办法
1、如果SCN相差不多，可以通过多次重起数据库解决（每次加1）

2、通过10015 ADJUST_SCN事件来增进current SCN
1）计算level
1.1) Arg {c}* 4得出一个数值，假设为V_Wrap
1.2) 如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level
1.3）SCN被增进了1024*1024*1024*level（level*10 billion）

根据以上介绍，看看自己是否能产生这个错误。不过自己有点疑问，如果传输表空间,数据文件里面记录的scn比当前数据库的scn大，oracle是如何处理?
难道数据库也会选择最大的scn同步吗？

1.建立测试例子以及环境：
SQL> select * from v$version where rownum<=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> select ora_rowscn ,rowid ,dept1.* from dept1;

ORA_ROWSCN ROWID                  DEPTNO DNAME          LOC
---------- ------------------ ---------- -------------- -------------
3010500340 AAAcC1AAIAAAACDAAA         50 TEST           BBBBB
3010500340 AAAcC1AAIAAAACDAAB         10 ACCOUNTING     NEW YORK
3010500340 AAAcC1AAIAAAACDAAC         20 RESEARCH       DALLAS
3010500340 AAAcC1AAIAAAACDAAD         30 SALES          CHICAGO
3010500340 AAAcC1AAIAAAACDAAE         40 OPERATIONS     BOSTON
3179982460 AAAcC1AAIAAAACHAAA         60 AAAAAA         BBBBB

6 rows selected.

SQL> @ lookup_rowid AAAcC1AAIAAAACHAAA

    OBJECT       FILE      BLOCK        ROW
---------- ---------- ---------- ----------
    114869          8        135          0

SQL> select current_scn from v$database ;

CURRENT_SCN
-----------
 3179982478

$ bc
bc 1.06
Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc.
This is free software with ABSOLUTELY NO WARRANTY.
For details type `warranty'.
obase=16
3179982460
BD8AAE7C
3179992460
BD8AD58C

2.关闭数据库，修改file=8,block=135的信息。
--intel 系列CPU ，标识16进制是小头在前。

BBED> find /x 7cae8abd top
 File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
 Block: 135                                                                    Offsets:    8 to 8191                                                                 Dba:0x02000087
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 7cae8abd 00000206 b8b50000 01000000 b5c00100 99ab8abd 00000000 02003200 80000002 09000c00 ea250000 072ec000 97130c00 00800000 546f81b3 05000a00 3b2c0000 f526c000 ab140c00 01200000

BBED> find
 File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
 Block: 135                                                                    Offsets:   88 to 8191                                                                 Dba:0x02000087
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 7cae8abd 00000000 00000000 00010100 ffff1400 851f711f 711f0000 0100851f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

BBED> find
BBED-00212: search string not found
--find发现仅仅存在两处。

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
 Block: 135                                   Dba:0x02000087
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

 struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    sb2 ktbbhict                            @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[2], 48 bytes            @44

 struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100
    sb1 kdbhntab                            @101
    sb2 kdbhnrow                            @102
    sb2 kdbhfrre                            @104
    sb2 kdbhfsbo                            @106
    sb2 kdbhfseo                            @108
    sb2 kdbhavsp                            @110
    sb2 kdbhtosp                            @112

 struct kdbt[1], 4 bytes                    @114
    sb2 kdbtoffs                            @114
    sb2 kdbtnrow                            @116

 sb2 kdbr[1]                                @118

 ub1 freespace[8049]                        @120

 ub1 rowdata[19]                            @8169

 ub4 tailchk                                @8188

BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       0x0005
      ub2 kxidslt                           @70       0x000a
      ub4 kxidsqn                           @72       0x00002c3b
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       0x00c026f5
      ub2 kubaseq                           @80       0x14ab
      ub1 kubarec                           @82       0x0c
   ub2 ktbitflg                             @84       0x2001 (KTBFUPB)
   union _ktbitun, 2 bytes                  @86
      sb2 _ktbitfsc                         @86       0
      ub2 _ktbitwrp                         @86       0x0000
   ub4 ktbitbas                             @88       0xbd8aae7c
--感觉仅仅修改offset=8那里就可以了。即 ub4 bas_kcbh 
--把scn调大3179992460（增加10000）,十六进制BD8AD58C。

BBED> set offset 8
BBED> modify /x 8cd58abd
BBED-00209: invalid number (8cd58abd)

BBED> modify /x 8cd5

BBED> set offset +2
        OFFSET          10

BBED> modify /x 8abd

--后面补上的。
BBED> set offset 88
BBED> modify /x 8cd5
BBED> set offset +2
BBED> modify /x 8abd
--

BBED> sum
Check value for File 8, Block 135:
current = 0xb5b8, required = 0xce48

BBED> sum apply
Check value for File 8, Block 135:
current = 0xce48, required = 0xce48

BBED> p kcbh.bas_kcbh
ub4 bas_kcbh                                @8        0xbd8ad58c

BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       0x0005
      ub2 kxidslt                           @70       0x000a
      ub4 kxidsqn                           @72       0x00002c3b
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       0x00c026f5
      ub2 kubaseq                           @80       0x14ab
      ub1 kubarec                           @82       0x0c
   ub2 ktbitflg                             @84       0x2001 (KTBFUPB)
   union _ktbitun, 2 bytes                  @86
      sb2 _ktbitfsc                         @86       0
      ub2 _ktbitwrp                         @86       0x0000
   ub4 ktbitbas                             @88       0xbd8ad58c

BBED> sum apply
Check value for File 8, Block 135:
current = 0xce48, required = 0xce48

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle11g/oradata/test/test01.dbf
BLOCK = 135

Block 135 is corrupt
Corrupt block relative dba: 0x02000087 (file 0, block 135)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x02000087
 last change scn: 0x0000.bd8ad58c seq: 0x2 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xae7c0602
 check value in block header: 0xce48
 computed block checksum: 0x0

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED

--不行，还要修改tailchk。

BBED> p tailchk
ub4 tailchk                                 @8188     0xae7c0602

--注意这里offset=8190
BBED> modify /x 8cd5 offset 8190
 File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
 Block: 135                                                                    Offsets: 8190 to 8191                                                                 Dba:0x02000087
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 8cd5

 <80 bytes per line>

BBED> sum apply
Check value for File 8, Block 135:
current = 0xb5b8, required = 0xb5b8

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle11g/oradata/test/test01.dbf
BLOCK = 135

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

--OK,修改完成。

3.启动数据库测试看看。

SQL> select ora_rowscn ,rowid ,dept1.* from dept1;

ORA_ROWSCN ROWID                  DEPTNO DNAME          LOC
---------- ------------------ ---------- -------------- -------------
3010500340 AAAcC1AAIAAAACDAAA         50 TEST           BBBBB
3010500340 AAAcC1AAIAAAACDAAB         10 ACCOUNTING     NEW YORK
3010500340 AAAcC1AAIAAAACDAAC         20 RESEARCH       DALLAS
3010500340 AAAcC1AAIAAAACDAAD         30 SALES          CHICAGO
3010500340 AAAcC1AAIAAAACDAAE         40 OPERATIONS     BOSTON
3179982460 AAAcC1AAIAAAACHAAA         60 AAAAAA         BBBBB

6 rows selected.
--奇怪ora_rowscn为什么还是3179982460。而不是3179992460.
--主要offset=88 itl中的内容没有修改。修改以后直接报错。(前面修改ITL的部分是后来补上的)

SQL> select current_scn from v$database ;
select current_scn from v$database
                        *
ERROR at line 1:
ORA-00600: internal error code, arguments: [2662], [0], [3179982959], [0], [3179992460], [33554567], [], [], [], [], [], []

SQL> @10to16 33554567

10 to 16 HEX
--------------
       2000087

SQL> select
dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
from dual;

    RFILE#     BLOCK#
---------- ----------
         8        135


按照文档的解决方法：

转抄链接：http://www.xifenfei.com/1509.html

1、如果SCN相差不多，可以通过多次重起数据库解决（每次加1）

2、通过10015 ADJUST_SCN事件来增进current SCN
1）计算level
1.1) Arg {c}* 4得出一个数值，假设为V_Wrap
1.2) 如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level
1.3）SCN被增进了1024*1024*1024*level（level*10 billion）

我的相差不多：
SQL> select 3179992460- current_scn from v$database ;

3179992460-CURRENT_SCN
----------------------
                  8747

等上一段时间就应该没有问题了。不等执行如下：
create table t1 (a number,b varchar2(10));
begin
 for i in 1..10000         loop
  insert into t1 values (i,'test');
  commit;
 end loop;
end;
/

SQL> select 3179992460- current_scn from v$database ;

3179992460-CURRENT_SCN
----------------------
                 -1711

SQL> select ora_rowscn ,rowid ,dept1.* from dept1;

ORA_ROWSCN ROWID                  DEPTNO DNAME          LOC
---------- ------------------ ---------- -------------- -------------
3010500340 AAAcC1AAIAAAACDAAA         50 TEST           BBBBB
3010500340 AAAcC1AAIAAAACDAAB         10 ACCOUNTING     NEW YORK
3010500340 AAAcC1AAIAAAACDAAC         20 RESEARCH       DALLAS
3010500340 AAAcC1AAIAAAACDAAD         30 SALES          CHICAGO
3010500340 AAAcC1AAIAAAACDAAE         40 OPERATIONS     BOSTON
3179992460 AAAcC1AAIAAAACHAAA         60 AAAAAA         BBBBB

6 rows selected.

--现在问题消失。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-750075/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20121127]rman备份数据文件大小与truncate.txt
下一篇： [20121128]传输表空间与scn.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969412

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593