标签归档：ORA-00600

记录一次存储异常数据库恢复后遗症ORA-600[kafspa:columnBuffer1]错误处理

发表于 2015 年 8 月 14 日 由 惜分飞

先说下前提,这个库以前是由于存储异常,找硬件厂商做了raid重组,然后我进行数据恢复的,恢复出来数据之后,应用厂商通过验证和补数据,然后迁移到另外一台机器做生产用的,这个库一直没有怎么看,最近检查数据库发现ORA-600[kafspa:columnBuffer1]错误,通过删除异常记录的方式解决.
数据库alert日志

Mon Aug 10 00:00:21 2015
LNS: Standby redo logfile selected for thread 1 sequence 617 for destination LOG_ARCHIVE_DEST_2
Mon Aug 10 00:00:33 2015
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_j002_6900.trc  (incident=146517):
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [2883], [1], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_146517\xff_j002_6900_i146517.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_j002_6900.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_280"
ORA-20011: Approximate NDV failed: 
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [2883], [1], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 31228

分析日志发现

*** 2015-07-19 06:00:30.231
*** SESSION ID:(578.751) 2015-07-19 06:00:30.231
*** CLIENT ID:() 2015-07-19 06:00:30.231
*** SERVICE NAME:(SYS$USERS) 2015-07-19 06:00:30.231
*** MODULE NAME:(DBMS_SCHEDULER) 2015-07-19 06:00:30.231
*** ACTION NAME:(ORA$AT_OS_OPT_SY_220) 2015-07-19 06:00:30.231
 
Dump continued from file: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_j001_4444.trc
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [2883], [1], [], [], [], [], [], [], [], [], []

========= Dump for incident 146142 (ORA 600 [kafspa:columnBuffer1]) ========

*** 2015-07-19 06:00:30.231
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=g0q33k8qtbcpd) -----
/* SQL Analyze(1) */ select /*+  full(t)    no_parallel(t) no_parallel_index(t)
 dbms_stats cursor_sharing_exact use_weak_name_resl 
dynamic_sampling(0) no_monitoring no_substrb_pad 
…………
to_char(substrb(dump(max("LIST_NO"),16,0,32),1,120)) from "CHF"."T_XIFENFEI" t 
…………

对表进行收集统计信息


SQL> EXEC DBMS_STATS.gather_table_stats('CHF','T_XIFENFEI',CASCADE=>TRUE)
;
BEGIN DBMS_STATS.gather_table_stats('CHF','T_XIFENFEI',CASCADE=>TRUE); EN
D;

*
第 1 行出现错误:
ORA-20011: Approximate NDV failed: ORA-00600: 内部错误代码, 参数:
[kafspa:columnBuffer1], [2883], [1], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_STATS", line 24232
ORA-06512: 在 "SYS.DBMS_STATS", line 24332
ORA-06512: 在 line 1


SQL> desc "CHF"."T_XIFENFEI"
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -----------------

 VISIT_DATE                                         DATE
…………
 GETDRUG_FLAG                                       VARCHAR2(2)
…………

通过上面的alert日志和trace文件以及人工收集统计信息，基本上可以定位是由于数据库自动收集统计信息进程在进行统计信息收集之时，对于”CHF”.”T_XIFENFEI”表进行收集统计信息由于某种错误,从而出现该错误.查询mos，发现此类问题主要是由于varchar2类型存储的数据长度超过了表定义长度.

通过验证官方所说

C:\Users\Administrator>exp "'/ as sysdba'" tables="CHF"."T_XIFENFEI" file
=y:/1.dmp log=y:/1.log

Export: Release 11.2.0.4.0 - Production on 星期四 8月 13 11:03:22 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
当前的用户已更改为 CHF
. . 正在导出表                    T_XIFENFEI
EXP-00015: 错误出现在行 1339552 (表 T_XIFENFEI, 列 GETDRUG_FLAG), 数据类型 1
EXP-00001: 数据字段截断 - 列长度 =2, 缓冲区大小 =2 实际大小 =17Errors in file :
OCI-21500: 内部错误代码, 参数: [kghfrempty:ds], [0x00652FCC8], [], [], [], [], [
], []


----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
kgerinv_internal()+  CALL???  skgudmp()            000000000 006447680 000000000

139                                                006447680
kgerinv()+49         CALL???  kgerinv_internal()   000000001 000676B4D 0064985B0

                                                   000000000
kgerin()+49          CALL???  kgerinv()            000000018 000799612 000072000

                                                   000000000
kghnerror()+294      CALL???  kgerin()             006447680 00645092C 006447680

                                                   000000001
kghfrempty()+639     CALL???  kghnerror()          0000001F0 000000000
                                                   BE019800000000 7E01960000
kghgex()+1433        CALL???  kghfrempty()+368     000000000 00652CAD8 000000000

                                                   000000000
kghfnd()+808         CALL???  kghgex()             001004000 000000000 001BEDD10

                                                   001A7131C
kghalo()+610         CALL???  kghfnd()             00012C450 00012C4A0 000000000

                                                   006446FD0
kghgex()+445         CALL???  kghalo()             006494848 000000000 001BEDD10

                                                   00190A575
kghfnd()+808         CALL???  kghgex()             000000001 0000001A0 000000000

                                                   006493D68
kghalo()+610         CALL???  kghfnd()             000000000 006447680 0FFFFFFFF

                                                   006447680
kpuhhalo()+358       CALL???  kghalo()             000000000 000000178 07FFFFFFF

                                                   000000001
kpuertb_reallocTemp  CALL???  kpuhhalo()           00652C498 000003E84 001C0EA44

Buf()+192                                          000000000
kpuex_reallocTempBu  CALL???  kpuertb_reallocTemp  000004007 0018BA3BF 00012CAB0

f()+67                        Buf()                001AB296F
kpudefn()+347        CALL???  kpuex_reallocTempBu  00012CC38 001004000 001BEDD44

                              f()                  000000004
kpudfn()+1506        CALL???  kpudefn()            00012F3D0 000000004 006520044

                                                   000000000
OCIDefineByPos()+10  CALL???  kpudfn()             004327570 000000000 00012F3D0

2                                                  000000004
00000001400116E5     CALL???  OCIDefineByPos()     1043B9300 0043B92C0 0044002B8

                                                   004401394
000000014004AFC7     CALL???  00000001400113BA     00012F380 00012F0E0 000000068

                                                   14004B2B6
000000014001E784     CALL???  000000014004A37E     000013F30 140095A71 140097520

                                                   14009F540
00000001400027A7     CALL???  000000014001E39F     14009F838 00012FB5C 140097520

                                                   14009F540
000000014000102C     CALL???  0000000140001E2C     000000005 004327570
                                                   1D0D5749D21764D 000000000
000000014006BEF0     CALL???  000000014000100E     000130000 1AFBFE2D0D8
                                                   000000000 000000000
000000007748652D     CALL???  000000014006BDD0     000000000 000000000 000000000

                                                   000000000
00000000775BC521     CALL???  0000000077486520     000000000 000000000 000000000

                                                   000000000


call stack performance statistics:
total                  : 0.778000 sec
setup                  : 0.350000 sec
stack unwind           : 0.099000 sec
symbol translation     : 0.021000 sec
printing the call stack: 0.304000 sec
printing frame data    : 0.000000 sec
printing argument data : 0.000000 sec


----- End of Call Stack Trace -----

这里通过exp验证到数据在GETDRUG_FLAG列上有异常，本来定义列长度为2，可是实际数据长度为17，明显不符

通过plsql定位具体错误rowid

SQL> set serveroutput on
SQL> DECLARE
  2   TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  3   CURSOR c1 IS  select /*+index(t PK_T_XIFENFEI_BAK_NEW)*/ rowid from CHF.T_XIFENFEI t;
  4   r RowIDTab;
  5   rows  NATURAL := 20000;
  6   bad_rows number := 0 ;
  7   errors number;
  8   error_code number;
  9   myrowid rowid;
 10  BEGIN
 11   OPEN c1;
 12   LOOP
 13     FETCH  c1 BULK COLLECT INTO r LIMIT rows;
 14     EXIT WHEN r.count=0;
 15     BEGIN
 16      FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
 17       insert into CHF.T_XIFENFEI_new
 18       select /*+ ROWID(A) */ *
 19       from CHF.T_XIFENFEI A where rowid = r(i);
 20     EXCEPTION
 21     when OTHERS then
 22      BEGIN
 23       errors := SQL%BULK_EXCEPTIONS.COUNT;
 24       FOR err1 IN 1..errors LOOP
 25           error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
 26           myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
 27           bad_rows := bad_rows + 1;
 28           insert into system.had_rows values('CHF.T_XIFENFEI',myrowid, error_code);
 29       END LOOP;
 30       END;
 31     END;
 32    commit;
 33   END LOOP;
 34   commit;
 35   CLOSE c1;
 36   dbms_output.put_line('Total Bad Rows: '||bad_rows);
 37  END;
 38  /
Total Bad Rows: 1

PL/SQL 过程已成功完成。


SQL> SELECT row_id FROM  system.had_rows ;

 ROW_ID
 ------------------

 AAAT8wAAEAAAM29AAX

SQL> select * from  CHF.T_XIFENFEI WHERE ROWID='AAAT8wAAEAAAM29AAX';
select * from  CHF.T_XIFENFEI WHERE ROWID='AAAT8wAAEAAAM29AAX'
                        *
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kafspa:columnBuffer1], [2883], [1], [], [], [],
[], [], [], [], [], []

通过这里基本上可以定位到是该条rowid记录有问题，和业务进行沟通，确定该条记录可以删除(也不能访问，其实不删除也没用)

删除异常记录

SQL> delete from CHF.T_XIFENFEI WHERE ROWID='AAAT8wAAEAAAM29AAX';

已删除 1 行。

SQL> commit;

提交完成。

收集统计信息

SQL> EXEC DBMS_STATS.gather_table_stats('CHF','T_XIFENFEI',CASCADE=>TRUE)
;

PL/SQL 过程已成功完成。

通过清理异常记录，数据库可以正常收集统计信息，未再报ORA-00600[kafspa:columnBuffer1]错误，故障较完美解决

补充几个现象
1. analyze table “CHF”.”T_XIFENFEI” estimate statistics; 分析表统计信息正常,但是dbms_stats收集报错(因为dbms_stats相当对于每个列进行了扫描,而analyze应该不是)
2. 在报ORA-00600[kafspa:columnBuffer1]的情况下,ctas依旧可以成功,但是普通插入不行(因为ctas相当加油append操作）,因此在有些情况下,需要慎重append(特别是有逻辑坏块的时候)

发表在 ORA-xxxxx | 标签为 , | 评论关闭

bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决

发表于 2014 年 10 月 25 日 由 惜分飞

在Oracle使用的过程中,经常遭遇一些核心index出现异常，导致数据库无法正常使用,特别是在数据库open的情况下,因为出现这些bootstrap$中的部分index是无法通过设置event38003进行重建,从而导致数据库无法正常使用,最常见异常index 有:I_ICOL1, I_TS1, I_CDEF1, I_CDEF2, I_CDEF3, I_CDEF4, I_PROXY_DATA$, I_IND1, I_TS#, I_UNDO1, I_UNDO2, I_COBJ#, I_USER1, I_USER2, I_CON1, I_CON2, I_FILE1, I_FILE2, I_FILE#_BLOCK#, I_USER#, I_OBJ#, I_PROXY_ROLE_DATA$_1, I_PROXY_ROLE_DATA$_2, I_CCOL1, I_CCOL2, I_TAB1, I_COL1, I_COL2, I_COL3, I_OBJ1, I_OBJ2, I_OBJ3, I_OBJ4, I_OBJ5
重建的index异常报错有:ORA-00701,ORA-01410,ORA-08102,ORA-08103,ORA-01578,ORA-01499,ORA-00600等各类错误
因为处理细节复杂,无法一一描述出来.这里假设Oracle数据库一个非常核心的表OBJ$中的I_OBJ1异常,现在准备重建它,大概步骤如下:
1. 假设异常index信息（I_OBJ1）

SQL> alter index sys.i_obj1 rebuild;
alter index sys.i_obj1 rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

SQL> SELECT SQL_TEXT FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%obj%';

SQL_TEXT
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXT
RANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PC
TINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336))

2. 创建获取异常index所属表

SQL>  select table_name from dba_indexes where owner='SYS' AND INDEX_NAME='I_OBJ1';

TABLE_NAME
--------------------------------------------------------------------------------
OBJ$

3. 获取obj$ ddl 语句

SQL> set long 10000
SQL> set pages 1000
SQL>  SELECT DBMS_METADATA.GET_DDL('TABLE','OBJ$','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','OBJ$','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."OBJ$"
   (    "OBJ#" NUMBER NOT NULL ENABLE,
        "DATAOBJ#" NUMBER,
        "OWNER#" NUMBER NOT NULL ENABLE,
        "NAME" VARCHAR2(128) NOT NULL ENABLE,
        "NAMESPACE" NUMBER NOT NULL ENABLE,
        "SUBNAME" VARCHAR2(128),
        "TYPE#" NUMBER NOT NULL ENABLE,
        "CTIME" DATE NOT NULL ENABLE,
        "MTIME" DATE NOT NULL ENABLE,
        "STIME" DATE NOT NULL ENABLE,
        "STATUS" NUMBER NOT NULL ENABLE,
        "REMOTEOWNER" VARCHAR2(128),
        "LINKNAME" VARCHAR2(128),
        "FLAGS" NUMBER,
        "OID$" RAW(16),
        "SPARE1" NUMBER,
        "SPARE2" NUMBER,
        "SPARE3" NUMBER,
        "SPARE4" VARCHAR2(1000),
        "SPARE5" VARCHAR2(1000),
        "SPARE6" DATE,
        "SIGNATURE" RAW(16),
        "SPARE7" NUMBER,
        "SPARE8" NUMBER,
        "SPARE9" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

4. 获取obj$相关index 语句

SQL>  SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,'SYS') FROM dba_indexes where owner='SYS' AND TABLE_NAME='OBJ$';

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,'SYS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS"."I_OBJ5" ON "SYS"."OBJ$" ("SPARE3", "NAME", "NAMESPA
CE", "TYPE#", "OWNER#", "REMOTEOWNER", "LINKNAME", "SUBNAME", "OBJ#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


  CREATE INDEX "SYS"."I_OBJ4" ON "SYS"."OBJ$" ("DATAOBJ#", "TYPE#", "OWNER#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


  CREATE INDEX "SYS"."I_OBJ3" ON "SYS"."OBJ$" ("OID$")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


  CREATE UNIQUE INDEX "SYS"."I_OBJ2" ON "SYS"."OBJ$" ("OWNER#", "NAME", "NAMESPA
CE", "REMOTEOWNER", "LINKNAME", "SUBNAME", "TYPE#", "SPARE3", "OBJ#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


  CREATE UNIQUE INDEX "SYS"."I_OBJ1" ON "SYS"."OBJ$" ("OBJ#", "OWNER#", "TYPE#")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

5. 创建obj$img表和相关index

SQL>   CREATE TABLE "SYS"."OBJ$IMG"
  2     (    "OBJ#" NUMBER NOT NULL ENABLE,
  3          "DATAOBJ#" NUMBER,
  4          "OWNER#" NUMBER NOT NULL ENABLE,
  5          "NAME" VARCHAR2(128) NOT NULL ENABLE,
  6          "NAMESPACE" NUMBER NOT NULL ENABLE,
  7          "SUBNAME" VARCHAR2(128),
  8          "TYPE#" NUMBER NOT NULL ENABLE,
  9          "CTIME" DATE NOT NULL ENABLE,
 10          "MTIME" DATE NOT NULL ENABLE,
 11          "STIME" DATE NOT NULL ENABLE,
 12          "STATUS" NUMBER NOT NULL ENABLE,
 13          "REMOTEOWNER" VARCHAR2(128),
 14          "LINKNAME" VARCHAR2(128),
 15          "FLAGS" NUMBER,
 16          "OID$" RAW(16),
 17          "SPARE1" NUMBER,
 18          "SPARE2" NUMBER,
 19          "SPARE3" NUMBER,
 20          "SPARE4" VARCHAR2(1000),
 21          "SPARE5" VARCHAR2(1000),
 22          "SPARE6" DATE,
 23          "SIGNATURE" RAW(16),
 24          "SPARE7" NUMBER,
 25          "SPARE8" NUMBER,
 26          "SPARE9" NUMBER
 27     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 28   NOCOMPRESS LOGGING
 29    STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
 30    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 31    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 32    TABLESPACE "SYSTEM"
 33  ;

Table created.

SQL> 
SQL>   CREATE UNIQUE INDEX "SYS"."I_OBJ_5IMG" ON "SYS"."OBJ$IMG" ("SPARE3", "NAME", "NAMESPACE", "TYPE#", "OWNER#", "REMOTEOWNER", "LINKNAME", "SUBNAME", "OBJ#")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM";

Index created.

SQL>   CREATE INDEX "SYS"."I_OBJ4IMG" ON "SYS"."OBJ$IMG" ("DATAOBJ#", "TYPE#", "OWNER#")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM";   

Index created.

SQL>   CREATE INDEX "SYS"."I_OBJ3IMG" ON "SYS"."OBJ$IMG" ("OID$")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM";

Index created.

SQL>   CREATE UNIQUE INDEX "SYS"."I_OBJ2IMG" ON "SYS"."OBJ$IMG" ("OWNER#", "NAME", "NAMESPACE", "REMOTEOWNER", "LINKNAME", "SUBNAME", "TYPE#", "SPARE3", "OBJ#")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM";

Index created.

SQL>   CREATE UNIQUE INDEX "SYS"."I_OBJ1IMG" ON "SYS"."OBJ$IMG" ("OBJ#", "OWNER#", "TYPE#")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM"
  7  ;

Index created.

6. 获取bootstrap$语句

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','BOOTSTRAP$','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','BOOTSTRAP$','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."BOOTSTRAP$"
   (    "LINE#" NUMBER NOT NULL ENABLE,
        "OBJ#" NUMBER NOT NULL ENABLE,
        "SQL_TEXT" VARCHAR2(4000) NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 57344 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

7. 创建bootstrap$img表

SQL>   CREATE TABLE "SYS"."BOOTSTRAP$IMG"
  2     (    "LINE#" NUMBER NOT NULL ENABLE,
  3          "OBJ#" NUMBER NOT NULL ENABLE,
  4          "SQL_TEXT" VARCHAR2(4000) NOT NULL ENABLE
  5     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  6   NOCOMPRESS LOGGING
  7    STORAGE(INITIAL 57344 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  8    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  9    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 10    TABLESPACE "SYSTEM"
 11  /

Table created.

8.插入数据

insert into obj$img select * from obj$;
insert into bootstrap$img select * from bootstrap$;
commit;

9. 删除bootstrap$img对象名

delete from bootstrap$img where obj# in 
 (select obj# from obj$ 
  where name in ('OBJ$',  'I_OBJ1',  'I_OBJ2', 'I_OBJ3', 'I_OBJ4', 'I_OBJ5',
                 'BOOTSTRAP$'));
commit;

10. 插入新创建对象

insert into bootstrap$img select * from bootstrap$tmpstr;
commit;

11. 关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

12. bbed修改相关block值

[oracle@oel6 ~]$ bbed 
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 7 04:54:50 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/u01/app/oracle/oradata/XIFENFEI/system01.dbf'
        FILENAME        /u01/app/oracle/oradata/XIFENFEI/system01.dbf

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 1
        BLOCK#          1

BBED> set mode edit
        MODE            Edit

BBED> set count 32
        COUNT           32

BBED> m /x e81d
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
 Block: 1                Offsets:   196 to  227           Dba:0x00000000
------------------------------------------------------------------------
 e81d4000 12000000 00000000 41bad632 15bad632 01000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 5200 offset +2
 File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
 Block: 1                Offsets:   198 to  229           Dba:0x00000000
------------------------------------------------------------------------
 52001200 00000000 000041ba d63215ba d6320100 00000000 00000000 00000000 

 <32 bytes per line>

BBED> d offset 96
 File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
 Block: 1                Offsets:   196 to  227           Dba:0x00000000
------------------------------------------------------------------------
 e81d5200 12000000 00000000 41bad632 15bad632 01000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 1:
current = 0x5fbf, required = 0x5fbf

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/XIFENFEI/system01.dbf
BLOCK = 1


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   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED> exit

13. 启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
Database opened.

14. 验证I_OBJ1 index重建情况

SQL> SELECT OBJECT_ID,DATA_OBJECT_ID,CREATED FROM DBA_OBJECTS WHERE OBJECT_NAME='I_OBJ1';

 OBJECT_ID DATA_OBJECT_ID CREATED
---------- -------------- ---------
     77120          77120 06-AUG-14

SQL> !date  
Thu Aug  6 05:29:25 CST 2014

SQL> SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME='I_OBJ1';

HEADER_FILE HEADER_BLOCK
----------- ------------
          1        77296

SQL> SELECT SQL_TEXT FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%obj%';

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------
create unique index i_obj1 on obj$(obj#, owner#, type#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 M
AXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77120 EXTENTS (FILE 1 BLOCK 77296))

create unique index i_obj2 on obj$(owner#, name, namespace,remoteowner, linkname, subname, type#, spare3, obj#) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE (  INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77119 EXTENTS (FILE 1 BLOCK 77288))

create index i_obj3 on obj$(oid$) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 P
CTINCREASE 0 OBJNO 77118 EXTENTS (FILE 1 BLOCK 77280))

create index i_obj4 on obj$(dataobj#, type#, owner#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXE
XTENTS 2147483645 PCTINCREASE 0 OBJNO 77117 EXTENTS (FILE 1 BLOCK 77272))

create unique index i_obj5 on obj$(spare3, name, namespace, type#, owner#, remoteowner, linkname, subname, obj#) PCTFREE 10 INITRANS 2 MAXTRANS
 255 STORAGE (  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77116 EXTENTS (FILE 1 BLOCK 77264))

不同版本对应的基表和index结构不一样(本篇blog使用的是12c做试验),但是本方法支持数据库从Oracle 7到Oracle 12c对于bootstrap$中核心index处理.
因为重建bootstrap$中的核心index是一个复杂而且风险非常大的事情,在你无法确定风险或者无法正常完成此类操作之时,如有需要请联系我
Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com

发表在 非常规恢复 | 标签为 , , , , , , , , , | 评论关闭