sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle坏块 遇到obj$坏块并跳过obj$坏块的方法案例

时间:2016-07-24 21:52   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 分享一篇关于Oracle数据库obj$坏块的案例,遇到obj$坏块并跳过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

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

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle坏块 遇到obj$坏块并跳过obj$坏块的方法案例

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

Oracle研究中心

关键词:

Oracle坏块

Oracle obj$坏块

跳过obj$坏块的方法案例

使用dbms_repair跳过坏块