在ORACLE中对核心对象进行重建基本上会遇到ORA-00701错误，可以通过将数据库启动到upgrade或者配置event 38003错误来解决，但是object_id小于59的错误不能通过此方法来解决。但是可以通过重建创建对象，修改obj$与bootstrap$表来处理。下面是在自己的测试环境模拟一下对file$表及索引的操作，请非在生产环境操作，此实验也没有实际意思，只是为了好玩而以。
注意不同平台，不同数据库版本操作方法都可能不一样。
1 环境介绍
|
www.htz.pw > select * from v$version where rownum=1;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
www.htz.pw > !uname -a Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux |
2 rebuild索引报ORA-00701错误
|
www.htz.pw > alter index sys.i_file1 rebuild; alter index sys.i_file1 rebuild * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered 这里i_file1为FILE$表的索引 www.cdhtz.com > @table_index.sql Enter Search Table Owner (i.e. SCOTT|ALL(DEFAULT)) : sys Enter Search Table Name (i.e. DEPT|DEFAULT(ALL)) : file$ Enter Search Index Name (i.e. DEPT|DEFAULT(ALL)) :
OWNER TABLE_NAME INDEX_NAME POST COLUMNNAME ————————— —- ——————– FILE$:I_FILE1 1 FILE# FILE$:I_FILE2 1 TS# 2 RELFILE# |
3 创建备用FILE$表及相关索引
|
www.cdhtz.com > create table FILE$_back as select * from file$;
Table created. 如果有相当的约束请创建相应的约束 www.cdhtz.com > set long 1000000000 select dbms_metadata.get_ddl(‘INDEX’, INDEX_NAME, OWNER) as getddl 2 from dba_indexes 3 where table_name = ‘FILE$’ 4 AND table_owner = ‘SYS’;
GETDDL ————————————————————————–
CREATE UNIQUE INDEX “SYS”.”I_FILE2″ ON “SYS”.”FILE$” (“TS#”, “RELFILE#”) 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_FILE1″ ON “SYS”.”FILE$” (“FILE#”) 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” 2 rows selected. 创建索引信息 www.cdhtz.com > CREATE UNIQUE INDEX “SYS”.”I_FILE2_BACK” ON “SYS”.”FILE$_BACK” (“TS#”, “RELFILE#”) 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”; Index created. www.cdhtz.com > CREATE UNIQUE INDEX “SYS”.”I_FILE1_BACK” ON “SYS”.”FILE$_BACK” (“FILE#”) 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”; Index created. |
4 dd清空i_file1索引
|
www.cdhtz.com > @extent.sql Enter value for owner: sys Enter value for segment_name: I_FILE1 Enter value for tablespace_name:
FILE BLOCK OWNER:SEGMENT_NAME ID FNO EXTENT_ID BEGIN_END ——————- —– —– ———- ———- SYS.I_FILE1 1 1 0 392~399 ******************* 1 row selected.
www.cdhtz.com > select name from v$dbfile;
NAME ————————————————- /oracle/app/oracle/oradata/orcl1123/system01.dbf /oracle/app/oracle/oradata/orcl1123/sysaux01.dbf /oracle/app/oracle/oradata/orcl1123/undo1.dbf /oracle/app/oracle/oradata/orcl1123/user02.dbf /oracle/app/oracle/oradata/orcl1123/user01.dbf 这里将i_file1占用的块清空，以便后面查询file$表时走索引报错 www.cdhtz.com > !dd if=/dev/zero of=/oracle/app/oracle/oradata/orcl1123/system01.dbf bs=8192 seek=392 count=8 conv=notrunc 8+0 records in 8+0 records out
www.cdhtz.com > alter system flush buffer_cache;
System altered.
www.cdhtz.com > select max(file#) from file$; select max(file#) from file$ * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 1, block # 393) ORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1123/system01.dbf’ |
5 更新OBJ$基表
|
www.cdhtz.com > SELECT OBJ#, DATAOBJ#, NAME, OWNER# FROM OBJ$ WHERE NAME IN (‘I_FILE1_BACK’, ‘I_FILE2_BACK’, ‘FILE$_BACK’, ‘I_FILE1’, ‘I_FILE2’, ‘FILE$’);
OBJ# DATAOBJ# NAME OWNER# ———- ———- —————————— ———- 17 17 FILE$ 0 43 43 I_FILE1 0 44 44 I_FILE2 0 82700 82700 FILE$_BACK 0 82701 82701 I_FILE2_BACK 0 82702 82702 I_FILE1_BACK 0 下面的步骤我都是在自己的测试环境测试的，请不要用于生产环境。在ORACLE中我们都是通过对象名来访问对象，对象名最后在obj$表解析成对象ID再与其它的一些基表做映射，所以这里直接更改的obj$表。 将原来的file$及相应的索引更改为其它的名字 update obj$ set name=’FILE$_OLD’ where obj#=17; update obj$ set name=’I_FILE1_OLD’ where obj#=43; update obj$ set name=’I_FILE2_OLD’ where obj#=44; 将新创建的备份表及索引更改为原来的名字 update obj$ set name=’FILE$’ where obj#=82700; update obj$ set name=’I_FILE1′ where obj#=82702; update obj$ set name=’I_FILE2′ where obj#=82701; 更改后的信息 www.cdhtz.com > SELECT OBJ#, DATAOBJ#, NAME, OWNER# FROM OBJ$ WHERE NAME IN (‘I_FILE1_BACK’, ‘I_FILE2_BACK’, ‘FILE$_BACK’, ‘I_FILE1’, ‘I_FILE2’, ‘FILE$’); OBJ# DATAOBJ# NAME OWNER# ———- ———- —————————— ———- 82700 82700 FILE$ 0 82701 82701 I_FILE2 0 82702 82702 I_FILE1 0 |
6 更新bootstrap$表
这里重新创建了一张bootstrap$表，名为htz，更直接更新htz表中的内容，不直接使用bootstrap$表。
|
更改前需要先查询到对象的block_id号，使用下面的命令 www.cdhtz.com >select owner, segment_name, block_id from dba_extents where segment_name in (‘I_FILE1_BACK’, ‘I_FILE2_BACK’, ‘FILE$_BACK’) ;
OWNER SEGMENT_NAME BLOCK_ID ———- ——————– ———- SYS FILE$_BACK 95816 SYS I_FILE2_BACK 95824 SYS I_FILE1_BACK 95832 www.cdhtz.com > col sql_text for a140 更新后的内容如下，我这里使用的pl/sql来修改的，修改的部分都被标记了 www.cdhtz.com > select * from htz where obj# in (17, 43, 44);
LINE# OBJ# SQL_TEXT ———- ———- —————————————————————————————————————————————————— 17 17 CREATE TABLE FILE$(“FILE#” NUMBER NOT NULL,”STATUS$” NUMBER NOT NULL,”BLOCKS” NUMBER NOT NULL,”TS#” NUMBER,”RELFILE#” NUMBER,”MAXEXTEND” NUMBER,”INC” NUMBER,”CRSCNWRP” NUMBER,”CRSCNBAS” NUMBER,”OWNERINSTANCE” VARCHAR2(30),”SPARE1″ NUMBER,”SPARE2″ NUMBER,”SPARE3″ VARCHAR2(1000),”SPARE4″ DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 82700 EXTENTS (FILE 1 BLOCK 95816))
43 43 CREATE UNIQUE INDEX I_FILE1 ON FILE$(FILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PC TINCREASE 0 OBJNO 82702 EXTENTS (FILE 1 BLOCK 95832))
44 44 CREATE UNIQUE INDEX I_FILE2 ON FILE$(TS#,RELFILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 214748 3645 PCTINCREASE 0 OBJNO 82701 EXTENTS (FILE 1 BLOCK 95824)) 这里也可以更改obj#为对应的object号，但是不改也不影响 |
7 替换bootstrap$表
这里需要将数据库启动到upgrade状态，具体怎么替换bootstrap$见BLOG:substituter-bootstrap
|
www.cdhtz.com > exec DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP(‘HTZ’);
PL/SQL procedure successfully completed.
www.cdhtz.com > startup force; ORACLE instance started.
Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes Database mounted. Database opened. 数据库已经正常启动 |
8 访问file$表
|
www.cdhtz.com > set autotrace on www.cdhtz.com > select max(file#) from file$;
MAX(FILE#) ———- 6 这里访问file$没有再报错了，可以看到是执行计划是走的索引的访问。 Execution Plan ———————————————————- Plan hash value: 1331072393
————————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————————– | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| I_FILE1 | 1 | 13 | 1 (0)| 00:00:01 |
下面是10046的结果，在做10046之前，需要flush buffer_cache; *** 2014-11-19 23:24:55.730 WAIT #182959581784: nam=’SQL*Net message from client’ ela= 4533024 driver id=1650815232 #bytes=1 p3=0 obj#=532 tim=1416410695730877 CLOSE #182959581784:c=0,e=40,dep=0,type=1,tim=1416410695731026 PARSE #182959385008:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1331072393,tim=1416410695731094 EXEC #182959385008:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1331072393,tim=1416410695731225 WAIT #182959385008: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=532 tim=1416410695731249 WAIT #182959385008: nam=’db file sequential read’ ela= 18 file#=1 block#=95833 blocks=1 obj#=82702 tim=1416410695731347 FETCH #182959385008:c=0,e=118,p=1,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1331072393,tim=1416410695731386 WAIT #182959385008: nam=’SQL*Net message from client’ ela= 110 driver id=1650815232 #bytes=1 p3=0 obj#=82702 tim=1416410695731534 FETCH #182959385008:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1331072393,tim=1416410695731556 WAIT #182959385008: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=82702 tim=1416410695731570
这里我们可以看到是访问的95833这个块 |
9 使用bbed替换回原来的bootstrap$
|
BBED> set offset 96 OFFSET 96
BBED> set count 20 COUNT 20
BBED> dump File: /oracle/app/oracle/oradata/orcl1123/system01.dbf (0) Block: 1 Offsets: 96 to 115 Dba:0x00000000 ———————————————————————— 60764100 07000000 00000000 60776c2d 748b7933
<32 bytes per line>
BBED> set mode edit MODE Edit
BBED> modify /x 08024000 offset 96 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /oracle/app/oracle/oradata/orcl1123/system01.dbf (0) Block: 1 Offsets: 96 to 115 Dba:0x00000000 ———————————————————————— 08024000 07000000 00000000 60776c2d 748b7933
<32 bytes per line>
BBED> dump offset 96 count 10 File: /oracle/app/oracle/oradata/orcl1123/system01.dbf (0) Block: 1 Offsets: 96 to 105 Dba:0x00000000 ———————————————————————— 08024000 07000000 0000
<32 bytes per line>
BBED> p kcvfh.kcvfhrdb ub4 kcvfhrdb @96 0x00400208
BBED> sum apply Check value for File 0, Block 1: current = 0x5c33, required = 0x5c33
BBED> verify DBVERIFY – Verification starting FILE = /oracle/app/oracle/oradata/orcl1123/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
www.cdhtz.com > startup ORACLE instance started.
Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes Database mounted. Database opened.
www.cdhtz.com > select max(file#) from file$; select max(file#) from file$ * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 1, block # 393) ORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1123/system01.dbf’ 这里我们又看到了报错。 |
通过上面的方法我们可以达到重建对象号小于60的对象，但是没有任何的实际意义，请不要在生产环境操作
rebuild object that object id less than 60：等您坐沙发呢！