sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-00701产生原因和解决办法

时间:2016-11-02 20:48   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 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” 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.
oracleplus.net> 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索引

oracleplus.net> @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.

oracleplus.net> 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$表时走索引报错
oracleplus.net> !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

oracleplus.net> alter system flush buffer_cache;

System altered.

oracleplus.net> 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$基表

oracleplus.net> 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;

更改后的信息
oracleplus.net> 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号,使用下面的命令
oracleplus.net>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
oracleplus.net> col sql_text for a140

更新后的内容如下,我这里使用的pl/sql来修改的,修改的部分都被标记了
oracleplus.net> 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
oracleplus.net> exec DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP(‘HTZ’);

PL/SQL procedure successfully completed.

oracleplus.net> 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$表

oracleplus.net> set autotrace on
oracleplus.net> 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

oracleplus.net> 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.

oracleplus.net> select max(file#) from file$;
select max(file#) from file$
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, Oracleоblock # 393)
ORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1123/system01.dbf’

这里我们又看到了报错。
通过上面的方法我们可以达到重建对象号小于60的对象,但是没有任何的实际意义,请不要在生产环境操作
本文固定链接: http://www.htz.pw/2014/11/21/rebuild-object-that-object-id-less-than-60.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-00701产生原因和解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1147.html

Oracle研究中心

关键词:

rebuild object that object id less than 60

ORA-00701解决方法笔记