标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,678)
- DB2 (22)
- MySQL (73)
- Oracle (1,540)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (563)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
标签归档:ORA-00600
记录一次存储异常数据库恢复后遗症ORA-600[kafspa:columnBuffer1]错误处理
先说下前提,这个库以前是由于存储异常,找硬件厂商做了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(特别是有逻辑坏块的时候)
bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决
在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