标签云
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,679)
- DB2 (22)
- MySQL (73)
- Oracle (1,541)
- 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)
-
最近发表
- 解决oracle数据文件路径有回车故障
- .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-08102
异常断电数据库恢复-从ORA-600 2131到ORA-08102: 未找到索引关键字, 对象号 39
数据库启动报ORA-600 2131,以前遇到过类似问题:ORA-600 2131故障处理
SQL> alter database mount; alter database mount * 第 1 行出现错误: ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Tue Jun 04 14:12:18 2024 RECO started with pid=15, OS id=3244 Tue Jun 04 14:12:18 2024 MMON started with pid=16, OS id=3256 Tue Jun 04 14:12:18 2024 MMNL started with pid=17, OS id=3432 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... starting up 1 shared server(s) ... ORACLE_BASE from environment = E:\app\Administrator Tue Jun 04 14:12:22 2024 alter database mount exclusive Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_2536.trc (incident=427583): ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], [] Tue Jun 04 14:12:28 2024 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ORA-600 signalled during: alter database mount exclusive...
重建ctl,然后重试recover 数据库,报ORA-600 kdourp_inorder2和ORA-600 3020错误,这些错误本质都是由于redo信息和block信息不匹配导致
SQL> recover datafile 1; ORA-00283: 恢复会话因错误而取消 ORA-10562: Error occurred while applying redo to data block (file# 1, block# 74805) ORA-10564: tablespace SYSTEM ORA-01110: 数据文件 1: 'E:\ORADATA\XFF\SYSTEM01.DBF' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 8 ORA-00600: 内部错误代码, 参数: [kdourp_inorder2], [16], [3], [0], [108], [], [], [], [], [], [], [] SQL> recover datafile 7; ORA-00283: 恢复会话因错误而取消 ORA-00600: 内部错误代码, 参数: [3020], [7], [385], [29360513], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 7, block# 385, file offset is 3153920 bytes) ORA-10564: tablespace UNDOTBS2 ORA-01110: 数据文件 7: 'E:\ORADATA\XFF\UNDOTBS2.DBF' ORA-10560: block type 'KTU UNDO BLOCK'
通过屏蔽一致性,修改文件头scn,强制打开数据库
SQL> recover database until cancel; ORA-00279: 更改 56782359 (在 06/04/2024 14:00:36 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\ARCHIVELOG\ARC0000005415_1165094245.0001 ORA-00280: 更改 56782359 (用于线程 1) 在序列 #5415 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误 ORA-01194: 文件 1 需要更多的恢复来保持一致性 ORA-01110: 数据文件 1: 'E:\ORADATA\XFF\SYSTEM01.DBF' ORA-01112: 未启动介质恢复 SQL> alter database open resetlogs; 数据库已更改。
尝试导出数据报ORA-08102,导致数据库无法正常导出
C:\Users\Administrator>expdp "'/ as sysdba'" full=y dumpfile=full_20240604_%U.dmp DIRECTORY=expdp_dir logfile=full_20240604.log parallel=2 EXCLUDE=STATISTICS,AUDIT Export: Release 11.2.0.4.0 - Production on 星期二 6月 4 18:40:26 2024 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31626: 作业不存在 ORA-31633: 无法创建主表 "SYS.SYS_EXPORT_FULL_05" ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: 在 "SYS.KUPV$FT", line 1038 ORA-08102: 未找到索引关键字, 对象号 39, 文件 1, 块 97540 (2)
obj 39 为OBJ$的I_OBJ4对象报ORA-08102
SQL> select owner,object_name,object_type from dba_objects where object_id=39 2 / OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ------------------- SYS I_OBJ4 INDEX
该对象属于bootstrap$中核心对象,无法直接rebuild,参考下面文章处理,然后再尝试导出数据
分享I_OBJ4 ORA-8102故障恢复案例
使用bbed 修复I_OBJ4 index 报ORA-8102错误
bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决
C:\Users\Administrator>expdp "'/ as sysdba'" full=y dumpfile=full_20240604_%U.dmp DIRECTORY=expdp_dir logfile=full_20240604.log parallel=2 EXCLUDE=STATISTICS,AUDIT Export: Release 11.2.0.4.0 - Production on 星期二 6月 4 18:43:47 2024 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31626: 作业不存在 ORA-31637: 无法创建作业 SYS_EXPORT_FULL_01 (用户 SYS) ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: 在 "SYS.KUPV$FT_INT", line 798 ORA-39080: 无法为数据泵作业创建队列 "KUPC$C_1_20240604184348" 和 "KUPC$S_1_20240604184348" ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: 在 "SYS.KUPC$QUE_INT", line 1534 ORA-08102: 未找到索引关键字, 对象号 53, 文件 1, 块 97715 (2)
通过类似方法分析确认为CDEF$的I_CDEF1 index,处理方法和I_OBJ4一样,然后导出数据成功,导入到新库中,在这个迁移过程中遭遇Wrapped 加密的package body无效的问题,具体参见:数据泵迁移Wrapped PLSQL之后报PLS-00753
通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误
在最近的数据库恢复中,经历了多次11.2库由于各种原因,数据库打开后,报ORA-8102错误,而且错误对象是OBJ$上的I_OBJ4这个index上,而且不能创建新表,周末开会闲着无事,进行了一个简单的模拟重现
数据库版本信息11.2.0.4
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
定位需要破坏的OBJ$上记录,为了使之和I_OBJ4中记录不一致,从而实现ORA-8102错误
SQL> select object_id,object_type from dba_objects where object_name='I_OBJ4'; OBJECT_ID OBJECT_TYPE ---------- ------------------- 87404 INDEX SQL> select max(DATAOBJ#) from obj$; MAX(DATAOBJ#) ------------- 87420 SQL> select dump(87420,16) from dual; DUMP(87420,16) ----------------------- Typ=2 Len=4: c3,9,4b,15 SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#, dbms_rowid.rowid_row_number(rowid) row# 2 3 from obj$ where DATAOBJ#=87420; FILE# BLOCK# ROW# ---------- ---------- ---------- 1 98085 40 SQL> alter system dump datafile 1 block 98085; System altered. SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_26373.trc --dump该记录显示 tab 0, row 40, @0x11fc tl: 72 fb: --H-FL-- lb: 0x0 cc: 18 col 0: [ 2] c1 02 col 1: [ 4] c3 09 4b 15 col 2: [ 1] 80 col 3: [12] 5f 4e 45 58 54 5f 4f 42 4a 45 43 54 col 4: [ 2] c1 02 col 5: *NULL* col 6: [ 1] 80 col 7: [ 7] 78 71 08 18 0c 26 24 col 8: [ 7] 78 73 03 0d 15 2e 2b col 9: [ 7] 78 71 08 18 0c 26 24 col 10: [ 1] 80 col 11: *NULL* col 12: *NULL* col 13: [ 1] 80 col 14: *NULL* col 15: [ 1] 80 col 16: [ 4] c3 07 38 24 col 17: [ 1] 80 tab 0, row 41, @0x9af tl: 2 fb: --HDFL-- lb: 0x2
这里我们知道i_obj4中的dataobj#最大值为87420对应的16进制记录为04 c3 09 4b 15
使用bbed破坏记录,修改dataobj#中的值,使得obj$.dataobj#和i_obj4中的dataobj#不匹配
SQL> select name from v$datafile where file#=1; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/xifenfei/system01.dbf SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. [oracle@localhost ~]$ bbed blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/xifenfei/system01.dbf' Password: BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 14:23:02 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> show all FILE# 0 BLOCK# 1 OFFSET 0 DBA 0x00000000 (0 0,1) FILENAME /u01/app/oracle/oradata/xifenfei/system01.dbf BIFILE bifile.bbd LISTFILE BLOCKSIZE 8192 MODE Edit EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL No BBED> set block 98085 BLOCK# 98085 BBED> p *kdbr[40] rowdata[2446] ------------- ub1 rowdata[2446] @4696 0x2c BBED> x /rnnncnnncc rowdata[2446] @4696 ------------- flag@4696: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@4697: 0x00 cols@4698: 18 col 0[2] @4699: 1 col 1[4] @4702: 87420 col 2[1] @4707: 0 col 3[12] @4709: _NEXT_OBJECT col 4[2] @4722: 1 col 5[0] @4725: *NULL* col 6[1] @4726: 0 col 7[7] @4728: xq...&$ col 8[7] @4736: xs....+ col 9[7] @4744: xq...&$ col 10[1] @4752: . col 11[0] @4754: *NULL* col 12[0] @4755: *NULL* col 13[1] @4756: . col 14[0] @4758: *NULL* col 15[1] @4759: . col 16[4] @4761: Ã.8$ col 17[1] @4766: . BBED> set block 98085 BLOCK# 98085 BBED> set offset 4702 OFFSET 4702 BBED> set count 32 COUNT 32 BBED> d File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 98085 Offsets: 4702 to 4733 Dba:0x00000000 ------------------------------------------------------------------------ 04c3094b 1501800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 7108180c <32 bytes per line> BBED> set offset +4 OFFSET 4706 BBED> d File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 98085 Offsets: 4706 to 4737 Dba:0x00000000 ------------------------------------------------------------------------ 1501800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 7108180c 26240778 <32 bytes per line> BBED> m /x 17 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 98085 Offsets: 4706 to 4737 Dba:0x00000000 ------------------------------------------------------------------------ 1701800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 7108180c 26240778 <32 bytes per line> BBED> sum apply Check value for File 0, Block 98085: current = 0xd361, required = 0xd361 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf BLOCK = 98085 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
使用bbed 修改04 c3 09 4b 15为04 c3 09 4b 17
重现在obj$的I_OBJ4 index上报ORA-8102错误,而且不能创建新对象
SQL> startup ORACLE instance started. Total System Global Area 1570009088 bytes Fixed Size 2253584 bytes Variable Size 469765360 bytes Database Buffers 1090519040 bytes Redo Buffers 7471104 bytes Database mounted. Database opened. SQL> create table t1 as select * from dba_users; create table t1 as select * from dba_users * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 87404, file 1, block 97266 (2) SQL> col OBJECT_NAME for a30 SQL> select object_name,object_type from dba_objects where object_id=87404; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- I_OBJ4 INDEX
如果修复该问题请见:使用bbed 修复I_OBJ4 index 报ORA-8102
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