天萃荷净
Oracle研究中心案例分析:运维DBA反映Oracle数据库报错ORA-00701,该问题可以通过将数据库启动到upgrade或者配置event 38003错误来解决。
在ORACLE中对核心对象进行重建基本上会遇到ORA-00701错误,可以通过将数据库启动到upgrade或者配置event 38003错误来解决,但是object_id小于59的错误不能通过此方法来解决。但是可以通过重建创建对象,修改obj$与bootstrap$表来处理。下面是在自己的测试环境模拟一下对file$表及索引的操作,请非在生产环境操作,此实验也没有实际意思,只是为了好玩而以。
注意不同平台,不同数据库版本操作方法都可能不一样。
1 环境介绍
oracleplus.net> select * from v$version where rownum=1;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
oracleplus.net> !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错误
oracleplus.net> 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$表的索引
oracleplus.net> @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$表及相关索引
oracleplus.net> create table FILE$_back as select * from file$;
Table created.
如果有相当的约束请创建相应的约束
oracleplus.net> 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.
创建索引信息
oracleplus.net> CREATE UNIQUE INDEX “SYS”.”I_FILE2_BACK