老版本PL/SQL Developer操作数据库导致ORA-00600[17113]

在巡检某运营商的计费库时,发现alert日志中发现如下错误

Thu Feb  2 13:54:52 2012
Errors in file /oracle9/app/admin/bill/udump/bill1_ora_35651918.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []

查看trace文件

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_bill_1
Release:        1
Version:        6
Machine:        00F64FF34C00
Instance name: bill1
Redo thread mounted by this instance: 1
Oracle process number: 200
Unix process pid: 35651918, image: oracle@zwq_bill_1 (TNS V1-V3)

*** 2012-02-02 13:54:52.169
*** SESSION ID:(210.1380) 2012-02-02 13:54:52.150
********** Internal heap ERROR 17113 addr=0 *********
******************************************************
HEAP DUMP heap name=""  desc=0
 extent sz=0x0 alt=0 het=0 rec=0 flg=0 opc=0
 parent=0 owner=0 nex=0 xsz=0x0
 Hla: -1
*** 2012-02-02 13:54:52.169
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Current SQL statement for this session:
declare
  t_owner varchar2(30);
  t_name  varchar2(30);
  procedure check_mview is
    dummy integer;
  begin
    if :object_type = 'TABLE' then
      select 1 into dummy
      from sys.all_objects
      where owner = :object_owner
      and object_name = :object_name
      and object_type = 'MATERIALIZED VIEW'
      and rownum = 1;
      :object_type := 'MATERIALIZED VIEW';
    end if;
  exception
    when others then null;
  end;
begin
  :sub_object := null;
  if :deep != 0 then
    begin
      if :part2 is null then
        select constraint_type, owner, constraint_name
          into :object_type, :object_owner, :object_name
          from sys.all_constraints c
         where c.constraint_name = :part1 and c.owner = user
           and rownum = 1;
      else
        select constraint_type, owner, constraint_name, :part3
          into :object_type, :object_owner, :object_name, :sub_object
          from sys.all_constraints c
         where c.constraint_name = :part2 and c.owner = :part1
           and rownum = 1;
      end if;
      if :object_type = 'P' then :object_type := 'PRIMARY KEY'; end if;
      if :object_type = 'U' then :object_type := 'UNIQUE KEY'; end if;
      if :object_type = 'R' then :object_type := 'FOREIGN KEY'; end if;
      if :object_type = 'C' then :object_type := 'CHECK CONSTRAINT'; end if;        
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  :sub_object := :part2;
  if (:part2 is null) or (:part1 != user) then
    begin
      select object_type, user, :part1
      into :object_type, :object_owner, :object_name
      from sys.all_objects
      where owner = user
      and object_name = :part1
      and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
      and rownum = 1;
      if :object_type = 'SYNONYM' then
        select s.table_owner, s.table_name
          into t_owner, t_name
          from sys.all_synonyms s
         where s.synonym_name = :part1
           and s.owner = user
           and rownum = 1;
        select o.object_type, o.owner, o.object_name
          into :object_type, :object_owner, :object_name
          from sys.all_objects o 
         where o.owner = t_owner
           and o.object_name = t_name
           and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
           and rownum = 1;
      end if;
      :sub_object := :part2;
      if :part3 is not null then
        :sub_object := :sub_object || '.' || :part3;
      end if;
      check_mview;
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  begin
    select s.table_owner, s.table_name
      into t_owner, t_name
      from sys.all_synonyms s
     where s.synonym_name = :part1
       and s.owner = 'PUBLIC'
       and rownum = 1;
    select o.object_type, o.owner, o.object_name
      into :object_type, :object_owner, :object_name
      from sys.all_objects o 
     where o.owner = t_owner
       and o.object_name = t_name
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  :sub_object := :part3;
  begin
    select o.object_type, o.owner, o.object_name
      into :object_type, :object_owner, :object_name
      from sys.all_objects o
     where o.owner = :part1
       and o.object_name = :part2
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null
    then
      select 'USER', null, :part1
      into :object_type, :object_owner, :object_name
      from sys.all_users u
      where u.username = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null and :deep != 0
    then
      select 'ROLE', null, :part1
      into :object_type, :object_owner, :object_name
      from sys.session_roles r
      where r.role = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  :object_owner := null;
  :object_type := null;
  :object_name := null;
  :sub_object := null;
end;

通过观察trace的这部分可以知道,是执行这条sql语句导致了ora-600[17113]错误的产生,而这条语句是查询系统对象,初步怀疑是oracle客户端上产生,继续阅读trace文件发现

    SO: 7000000b926f188, type: 4, owner: 7000000bd298b00, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 7000000bd298b00, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-00C8-0009F6EF, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 47, prv: 0, sql: 7000000d481ee78, psql: 7000000e086d8d8, user: 567/IBILLAPP
    O/S info: user: Administrator, term: WWW-39A255460E8, ospid: 784:2080, machine: WORKGROUP\WWW-39A255460E8
              program: plsqldev.exe
    application name: PL/SQL Developer, hash value=1190136663
    action name: Main session, hash value=1773317990
    last wait for 'SQL*Net more data from client' blocking sess=0x0 seq=78 wait_time=4
                driver id=54435000, #bytes=34, =0
    temporary object counter: 0

从这里进一步确认是有人使用PL/SQL Developer从WORKGROUP\WWW-39A255460E8的机器上操作该数据库导致,查询mos,果然发现该问题记录[ID 396326.1]。
解决办法:使用新版本的plsql dev工具即可解决问题。

ORA-19583/ORA-27206/ORA-06512

检查alert日志发现近期数据库出现了ORA-19583/ORA-27206/ORA-06512错误

Fri Dec 30 12:22:36 2011
Errors in file /oracle9/app/admin/ykcdb/udump/ykcdb_ora_5390564.trc:
ORA-19583: conversation terminated due to error
ORA-27206: requested file not found in media management catalog
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 625

进一步查看trace文件发现

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      OFFON2
Release:        3
Version:        5
Machine:        000A36B8D600
Instance name: ykcdb
Redo thread mounted by this instance: 1
Oracle process number: 79
Unix process pid: 5390564, image: oracle@OFFON2 (TNS V1-V3)

*** 2011-12-30 12:22:36.051
*** SESSION ID:(32.62184) 2011-12-30 12:22:36.023
FATAL ERROR IN TWO-TASK SERVER: error = 12152
*** 2011-12-30 12:22:36.051
ksedmp: internal or fatal error
ORA-19583: conversation terminated due to error
ORA-27206: requested file not found in media management catalog
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 625
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1025AE55C ?
opitsk+0968          bl       ksedmp               20289E71C ?
opiino+0798          bl       opitsk               000000000 ? 000000000 ?
opiodr+08c8          bl       _ptrgl               
opidrv+032c          bl       opiodr               3C00000018 ? 4101C7F40 ?
                                                   FFFFFFFFFFFF810 ? 0A03547C8 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A039C2D0 

查看mos发现[ID 558993.1]提供了错误原因和解决方案
错误原因:RMAN backup to tape using veritas netbackup,The MML parameters like NB_ORA_CLIENT and NB_ORA_SERV were not given in proper case while configuring channel in RMAN backup.
解决方法:The NB_ORA_CLIENT and NB_ORA_SERV MML parameters are case sensitive. So make sure to give these parameters in same case while allocating or configuring RMAN channel as it is configure in Veritas server.

obj$坏块情况下exp导出单个表解决方案

在前面一篇(obj$坏块exp不能执行原因探讨)已经研究了在obj$出现坏块的情况下,导致exp导出单个表不能成功的原因,这篇给出解决方案
1.重新创建exu81javt视图

SQL> CREATE OR REPLACE view exu81javt (objid) AS
  2      SELECT  obj#
  3          FROM    sys.obj$
  4          WHERE   name = 'oracle/aurora/rdbms/DbmsJava' AND
  5                  type# = 29 AND
  6                  owner# = 0 AND
  7                  status = 1
  8   /    

View created.

SQL> GRANT SELECT ON sys.exu81javt TO PUBLIC;

Grant succeeded.

SQL>  set autot  trace
SQL> SELECT COUNT(*)      FROM   SYS.EXU81JAVT;


Execution Plan
----------------------------------------------------------
Plan hash value: 2521745379

---------------------------------------------------------------------------------------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |        |     1 |    35 |     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |        |     1 |    35 |            |      |

|*  2 |   TABLE ACCESS BY INDEX ROWID| OBJ$   |     1 |    35 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I_OBJ2 |     1 |       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"=1)
   3 - access("OWNER#"=0 AND "NAME"='oracle/aurora/rdbms/DbmsJava' AND
              "TYPE#"=29)
       filter("TYPE#"=29)

通过对这个视图的重新创建,是的原来需要对obj$表全表扫描,改为走I_OBJ2索引,从而避免了部分坏块导致的exp异常。

2.测试exp导出单表

[oracle@node1 tmp]$ exp "'/ as sysdba'" tables=chf.t_undo file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log INDEXES =n \
> COMPRESS =n CONSISTENT =n GRANTS =n STATISTICS =none TRIGGERS =n CONSTRAINTS =n

Export: Release 11.2.0.3.0 - Production on Sun Jan 15 23:39:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
Current user changed to CHF
. . exporting table                         T_UNDO       1636 rows exported
Export terminated successfully without warnings.

测试证明修改了exu81javt视图后,exp导出单个表成功

3.生成导出脚本

SELECT 'exp "''' || '/ as sysdba''" tables=' || U.NAME || '.' || O.NAME ||
       ' file=' || '&PATH' || U.NAME || '_' || O.NAME || '.dmp log=' ||
       '&PATH' || U.NAME || '_' || O.NAME ||
       '.log buffer=1024000  COMPRESS =N STATISTICS =NONE'
  FROM TAB$ T, OBJ$ O, USER$ U
 WHERE O.TYPE# = 2
   AND T.OBJ# = O.OBJ#
   AND U.USER# = O.OWNER#
   AND u.name IN('CHF');

Execution Plan
----------------------------------------------------------
Plan hash value: 3095026863

-----------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |    31 |  1829 |    32   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                 |         |    31 |  1829 |    32   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |         |    33 |  1782 |    31   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| USER$   |     1 |    17 |     1   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | I_USER1 |     1 |       |     0   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN           | I_OBJ2  |    33 |  1221 |    30   (0)| 00:00:01 |

|   6 |   TABLE ACCESS CLUSTER        | TAB$    |     1 |     5 |     1   (0)| 00:00:01 |

|*  7 |    INDEX UNIQUE SCAN          | I_OBJ#  |     1 |       |     0   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

4.补充说明
1)并非所有的obj$坏块都可以通过该方法,使得exp导出单个表正常
2)在系统确实无救,有不想使用dul/odu的情况下,可以尝试这种方法抢救数据。

obj$坏块exp不能执行原因探讨

上篇(obj$坏块exp/expdp导出不能执行),验证了在obj$有坏块的情况下,不能执行exp/expdp操作,这篇是说明是什么原因导致在obj$有坏块的情况下exp不能正常执行
一.启动数据库级别会话跟踪

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 15 11:37:07 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create pfile='/tmp/pfile' from spfile;

File created.

--------------------------------------------------
在pfile中添加
event='10046 trace name context forever,level 12'
--------------------------------------------------

SQL> startup pfile='/tmp/pfile' 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.

二.执行单表导出,找到trace文件

[oracle@node1 trace]$ exp "'/ as sysdba'" tables=chf.t1 file=/tmp/xifenfei.dmp \
> log=/tmp/xifenfei.log INDEXES =n  COMPRESS =n CONSISTENT =n GRANTS =n \
> STATISTICS =none TRIGGERS =n CONSTRAINTS =n

Export: Release 11.2.0.3.0 - Production on Sun Jan 15 11:48:50 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
Current user changed to CHF
. . exporting table                             T1

--另外会话观察
Tasks: 241 total,   1 running, 240 sleeping,   0 stopped,   0 zombie
Cpu(s):  8.9%us,  1.2%sy,  0.0%ni, 85.1%id,  4.8%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8165060k total,  7168288k used,   996772k free,   266028k buffers
Swap:  8289500k total,      168k used,  8289332k free,  4653408k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                         
 4829 oracle    18   0 69812  12m 9144 S 51.1  0.2   0:03.64 exp               tables=chf.t1 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log INDEXES =n COMPRESS
 4830 oracle    18   0  829m  62m  58m D 27.9  0.8   0:03.85 oraclechf (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))  

[oracle@node1 trace]$ ll |grep 4830
-rw-r----- 1 oracle oinstall 14101447 01-15 11:49 chf_ora_4830.trc
-rw-r----- 1 oracle oinstall    75398 01-15 11:49 chf_ora_4830.trm
1

<strong>三.阅读trace文件</strong>
因为是obj$对象出现坏块,导致exp不能执行,如果是使用了obj$表的index,那么不会每次都报错,而我测试了多次都报错,所以怀疑是对obj$表进行全表扫描导致该错误发生,而使得exp不能继续下去。所以这次查找trace文件,重点是关注obj$表的全表扫描操作,经过耐心查找,终于发现了一个对obj$全表扫描的操作
1
PARSING IN CURSOR #46986932266584 len=41 dep=0 uid=0 oct=3 lid=0 tim=1326599330636591 hv=2311813821 ad='7be773c8' sqlid='ftx7dd64wqypx'
SELECT COUNT(*)      FROM   SYS.EXU81JAVT
END OF STMT
PARSE #46986932266584:c=2999,e=2938,p=5,cr=23,cu=0,mis=1,r=0,dep=0,og=1,plh=23986678,tim=1326599330636590
WAIT #46986932266584: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636682
WAIT #46986932266584: nam='SQL*Net message from client' ela= 42 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636738
EXEC #46986932266584:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=23986678,tim=1326599330636788
WAIT #46986932266584: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636810
WAIT #46986932266584: nam='SQL*Net message from client' ela= 91 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636913
WAIT #46986932266584: nam='SQL*Net message to client' ela= 9 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668126
FETCH #46986932266584:c=30995,e=31256,p=0,cr=989,cu=0,mis=0,r=1,dep=0,og=1,plh=23986678,tim=1326599330668198
STAT #46986932266584 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=989 pr=0 pw=0 time=31173 us)'
STAT #46986932266584 id=2 cnt=1 pid=1 pos=1 obj=90724 op='TABLE ACCESS FULL OBJ$ (cr=989 pr=0 pw=0 time=31156 us cost=220 size=18270 card=522)'
WAIT #46986932266584: nam='SQL*Net message from client' ela= 76 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668403
CLOSE #46986932266584:c=0,e=10,dep=0,type=0,tim=1326599330668452
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668481
WAIT #0: nam='SQL*Net message from client' ela= 113 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668606

四.对EXU81JAVT对象深究

SQL> select object_type from dba_objects where object_name='EXU81JAVT';

OBJECT_TYPE
-------------------
VIEW

SQL> set long 1000
SQL> select TEXT from dba_views where view_name='EXU81JAVT';

TEXT
------------------------------------------------------
SELECT  obj#
        FROM    sys.obj$
        WHERE   name LIKE '%DbmsJava' AND
                type# = 29 AND
                owner# = 0 AND
                status = 1


SQL> SELECT  obj#
  2          FROM    sys.obj$
  3          WHERE   name LIKE '%DbmsJava' AND
  4                type# = 29 AND
  5                owner# = 0 AND
  6                status = 1     ;

      OBJ#
----------
     17671

SQL> select name from obj$ where obj#=17671;

NAME
------------------------------
oracle/aurora/rdbms/DbmsJava

现在稳定已经定位到,是因为exp判断是否使用了java,是去找”/oracle/aurora/rdbms/DbmsJava”.这个对象的,如果java enabled,那么它就会使用dbms_java做一些转换,实际上oracle是查找视图exu81javt来确定DbmsJava的。
这里的EXU81JAVT是查询obj$而是通过name LIKE ‘%DbmsJava’,导致index不能正常使用,从而使得obj$全表扫描,而obj$有坏块,从而使得exp在obj$有坏块的情况下,不能正常执行

obj$坏块exp/expdp导出不能正常执行

今天有个朋友的多个库同时出现了obj$表出现坏块,总计数据量在100T-200T之间,而且是非归档模式,幸好数据不是很重要,不然将是一个非常大的悲剧。
我通过试验模拟证明obj$表如果出现坏块(具体方法见:bbed破坏数据文件),数据库的不能通过逻辑导出,然后建库。
一.alert发现坏块

Sat Jan 14 17:36:53 2012
Errors in file /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_493.trc:
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

二.检查坏块对象

[oracle@node1 chf]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 14 17:40:29 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> col owner for a10
SQL> col SEGMENT_NAME for a15
SQL> col SEGMENT_TYPE for a10
SQL> col TABLESPACE_NAME for a10
SQL> col PARTITION_NAME for a10
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 95369
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 95369 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER      SEGMENT_NAME    SEGMENT_TY TABLESPACE PARTITION_
---------- --------------- ---------- ---------- ----------
SYS        OBJ$            TABLE      SYSTEM

三.验证坏块方法
1.sql查询

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.dump数据文件

SQL> alter system dump datafile 1 block 95369;

System altered.

--查看dump文件
Start dump data blocks tsn: 0 file#:1 minblk 95369 maxblk 95369
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0 rdba=4289673
BH (0x6aff6a88) file#: 1 rdba: 0x00417489 (1/95369) class: 1 ba: 0x6af3c000
  set: 19 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 25,19
  dbwrid: 0 obj: 90724 objn: 90724 tsn: 0 afn: 1 hint: f
  hash: [0x6d7f6088,0x838655e0] lru: [0x6aff6ca0,0x6aff6a40]
  ckptq: [NULL] fileq: [NULL] objq: [0x6b3f2458,0x6a3e03c8] objaq: [0x6b3f2468,0x6a3e03d8]
  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 0
  flags: only_sequential_access auto_bmr_tried
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
BH (0x6d7f5fd8) file#: 1 rdba: 0x00417489 (1/95369) class: 1 ba: 0x6d72a000
  set: 23 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 16,28
  dbwrid: 0 obj: 90724 objn: 90724 tsn: 0 afn: 1 hint: f
  hash: [0x838655e0,0x6aff6b38] lru: [0x60ff3ac0,0x83b346e8]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' tch: 0 lfb: 33
  flags:
Block dump from disk:
buffer tsn: 0 rdba: 0x00417489 (1/95369)
scn: 0x0000.00db0299 seq: 0x01 flg: 0x06 tail: 0x39393332
frmt: 0x02 chkval: 0x05f8 type: 0x06=trans data
Hex dump of corrupt header 2 = BROKEN
Dump of memory from 0x00002B5631A02A00 to 0x00002B5631A02A14
2B5631A02A00 0000A206 00417489 00DB0299 06010000  [.....tA.........]
2B5631A02A10 000005F8                             [....]     

SQL>   select object_name from dba_objects where object_id=90724;

OBJECT_NAME
----------------------------------
OBJ$
       

3.bbed

[oracle@node1 chf]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sat Jan 14 18:28:25 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename 'system01.dbf'
        FILENAME        ./system01.dbf

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 95369
        BLOCK#          95369

BBED> verify
DBVERIFY - Verification starting
FILE = ././system01.dbf
BLOCK = 95368

Block 95368 is corrupt
Corrupt block relative dba: 0x00417489 (file 0, block 95369)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x00417488
 last change scn: 0x0000.00da8bce seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x65636238
 check value in block header: 0x9925
 computed block checksum: 0x8a94


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   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED

4.dbv

[oracle@node1 chf]$ dbv file=system01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jan 14 18:29:43 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/chf/system01.dbf
Page 95369 is influx - most likely media corrupt
Corrupt block relative dba: 0x00417489 (file 1, block 95369)
Fractured block found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x00417489
 last change scn: 0x0000.00db0299 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x39393332
 check value in block header: 0x5f8
 computed block checksum: 0xe93



DBVERIFY - Verification complete

Total Pages Examined         : 172800
Total Pages Processed (Data) : 132246
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 15726
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3548
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 21278
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Total Pages Encrypted        : 0
Highest block SCN            : 16682372 (0.16682372)

5.rman

[oracle@node1 chf]$ rman target / 

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jan 14 18:30:54 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CHF (DBID=3444205684)

RMAN> backup check logical validate datafile 1;

Starting backup at 2012-01-14 18:31:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/chf/system01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    FAILED 0              21278        172802          16682540  
  File Name: /opt/oracle/oradata/chf/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              132247                  
  Other      0              3548            

validate found one or more corrupt blocks
See trace file /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_2429.trc for details
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              882             
Finished backup at 2012-01-14 18:31:34


SQL>  select file#,block#,blocks from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS
---------- ---------- ----------
         1      95369          1

6.ANALYZE

SQL> ANALYZE TABLE sys.OBJ$ VALIDATE STRUCTURE;
ANALYZE TABLE sys.OBJ$ VALIDATE STRUCTURE
*
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'

三.测试逻辑导出数据
1.exp导出单个表

[oracle@node1 chf]$ exp "'/ as sysdba'" tables=chf.t_undo file=/tmp/chf.dmp log=/tmp/chf.log

Export: Release 11.2.0.3.0 - Production on Sat Jan 14 17:41:35 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
EXP-00008: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
EXP-00000: Export terminated unsuccessfully

2.expdp导出单个表

[oracle@node1 chf]$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp  tables=chf.t_odu

Export: Release 11.2.0.3.0 - Production on Sat Jan 14 17:55:09 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=xifenfei.dmp tables=chf.t_odu 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [TABLE:"CHF"."T_ODU"] 
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7a8608a8     20462  package body SYS.KUPW$WORKER
0x7a8608a8      9028  package body SYS.KUPW$WORKER
0x7a8608a8     10935  package body SYS.KUPW$WORKER
0x7a8608a8      2728  package body SYS.KUPW$WORKER
0x7a8608a8      9697  package body SYS.KUPW$WORKER
0x7a8608a8      1775  package body SYS.KUPW$WORKER
0x7a864160         2  anonymous block

Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [TABLE:"CHF"."T_ODU"] 
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7a8608a8     20462  package body SYS.KUPW$WORKER
0x7a8608a8      9028  package body SYS.KUPW$WORKER
0x7a8608a8     10935  package body SYS.KUPW$WORKER
0x7a8608a8      2728  package body SYS.KUPW$WORKER
0x7a8608a8      9697  package body SYS.KUPW$WORKER
0x7a8608a8      1775  package body SYS.KUPW$WORKER
0x7a864160         2  anonymous block

Job "SYS"."SYS_EXPORT_TABLE_01" stopped due to fatal error at 17:55:24

3.exp表空间传输

[oracle@node1 chf]$ exp userid=\'/ as sysdba\' tablespaces=users file=/tmp/users.dmp transport_tablespace=y

Export: Release 11.2.0.3.0 - Production on Sat Jan 14 18:00:21 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
EXP-00008: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
EXP-00000: Export terminated unsuccessfully

4.expdp表空间传输

[oracle@node1 chf]$ expdp userid=\'/ as sysdba\' dumpfile=xienfei.dmp  transport_tablespaces=users

Export: Release 11.2.0.3.0 - Production on Sat Jan 14 18:12:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  userid="/******** AS SYSDBA" dumpfile=xienfei.dmp transport_tablespaces=users 
ORA-39123: Data Pump transportable tablespace job aborted
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 18:12:14

四.总结obj$表坏块
1.在执行导出过程中,应该会去查询obj$表,而该表因出现坏块,不能被正常方法,导致逻辑备份异常终止。
2.如果你真的出现了obj$坏块,而你没有备份,那么恭喜你,悲剧的人生开始了。该对象出现问题,逻辑备份都不能工作,就算你有心重建库也不会给你这个机会,可能你不得不借助odu/dul之类的工具去解决问题。再次提醒各位,备份重于一切,安全重于泰山。