联系:手机/微信(+86 17813235971) QQ(107644445)
标题:跳过obj$坏块方法
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.确定obj$坏块存在
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> select /*+full(obj$)*/ count(*) from obj$; select /*+full(obj$)*/ count(*) from obj$ * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 1, block # 95369) ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
2.使用dbms_repair跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('SYS','OBJ$'); BEGIN dbms_repair.skip_corrupt_blocks('SYS','OBJ$'); END; * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered ORA-06512: at "SYS.DBMS_REPAIR", line 419 ORA-06512: at line 1 --ORA-00701原因 SQL> set pages 100 SQL> SELECT * FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%OBJ$%'; LINE# OBJ# ---------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 18 90724 CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT N ULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2( 30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"MTIME" DATE NOT NULL,"STIME" DATE NOT NULL,"STATUS" NUMBER NOT NULL,"REMOTEOWNER" VARCHAR2(30),"LINKNAME" VAR CHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3 " NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 16384 NEXT 106496 MINEX TENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 90724 EXTENTS (FILE 1 BLOCK 94 505)) --obj$是BOOTSTRAP$中对象,不能被修改 --这里打破一个传奇:一般人都说BOOTSTRAP$中对象都是object_id是非常小靠前。 --但是我这个从10g升级过来的库,obj$的object_id为90724
3.使用event跳过坏块
pfile中添加 event="10231 trace name context forever, level 10" SQL> startup pfile='/tmp/pfile_new' force ORACLE instance started. Total System Global Area 622149632 bytes Fixed Size 2230912 bytes Variable Size 398460288 bytes Database Buffers 213909504 bytes Redo Buffers 7548928 bytes Database mounted. Database opened. SQL> select /*+full(obj$)*/ count(*) from obj$; COUNT(*) ---------- 74503