Query Duration=0与ORA-01555

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Query Duration=0与ORA-01555

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.ALERT日志错误
奇怪之处:Query Duration=0 sec,竟然出现了ORA-01555

Tue Feb  7 02:41:34 2012
ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x0b2e.efcd78a9):
Tue Feb  7 02:41:34 2012
SELECT "ID_NO","CUST_ID" FROM "DBACCADM"."DCUSTMSG" "C" WHERE "ID_NO"=:1

2.ORA-01555解释
超过了undo_retention时间，undo被覆盖导致ORA-01555

[zwq_acc1:/home/oraeye/check]oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

3.数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

4.undo基本信息
从这里可以发现，两个节点的undo表空间还有很多剩余，缺发生了undo被覆盖从而出现了ORA-01555

SQL> col name for a20
SQL> col value for a15
SQL> SELECT INST_ID, NAME, VALUE
  2    FROM GV$PARAMETER
  3   WHERE UPPER (Name) LIKE '%UNDO%';
   INST_ID NAME                 VALUE
---------- -------------------- ---------------
         1 undo_management      AUTO
         1 undo_tablespace      UNDOTBS1
         1 undo_suppress_errors FALSE
         1 undo_retention       1800
         2 undo_management      AUTO
         2 undo_tablespace      UNDOTBS2
         2 undo_suppress_errors FALSE
         2 undo_retention       1800
8 rows selected.
TABLESPACE_NAME                CURRENT_TOTAL(MB)   USED(MB)   FREE(MB)      FREE% AUT MAX_TOTAL(MB)
------------------------------ ----------------- ---------- ---------- ---------- --- -------------
UNDOTBS1                                   40950    1587.94 39362.0625      96.12 NO          40950
UNDOTBS2                                   57330    1926.31 55403.6875      96.64 NO          57330
SQL> SELECT DISTINCT STATUS ,
  2                  COUNT(*) "EXTENT_NUM",
  3                  SUM(BYTES) / 1024 / 1024 / 1024 "UNDO(G)"
  4    FROM DBA_UNDO_EXTENTS
  5   GROUP BY STATUS;
STATUS    EXTENT_NUM    UNDO(G)
--------- ---------- ----------
ACTIVE           208 .273658752
EXPIRED         7651 2.42865753
UNEXPIRED        941 .752548218

查询MOS[ID 761128.1]，发现可能是Oracle bug导致(BUG:6799685 – ORA-1555 ERROR WITH QUERY DURATION=0 AND UNDO_RETENTION=1800和BUG:5475085 – V$UNDOSTAT.EXPBLKREUCNT IS NEVER INCREMENTED)

5.解决方法
Increase the size of the UNDO tablespace and increase the UNDO_RETENTION parameter value to try to prevent required undo expiring too quickly.
基于本库，因为undo空间还有很大剩余，直接设置UNDO_RETENTION=3600即可(可以从一定程度上缓解整个问题，但是要从根本上解决整个问题，需要升级到10.2.0.4及其以上版本)

ORA-00600[4454]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00600[4454]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库版本信息

SQL> select * from gv$version;
   INST_ID BANNER
---------- ----------------------------------------------------------------
         1 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         1 PL/SQL Release 9.2.0.8.0 - Production
         1 CORE 9.2.0.8.0       Production
         1 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         1 NLSRTL Version 9.2.0.8.0 - Production
         2 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         2 PL/SQL Release 9.2.0.8.0 - Production
         2 CORE 9.2.0.8.0       Production
         2 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         2 NLSRTL Version 9.2.0.8.0 - Production
10 rows selected.

早上检查某运营商的结算数据库时发现

Mon Feb  6 16:03:23 2012
Errors in file /oracle9/app/admin/settl/udump/settl1_ora_1355948.trc:
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []
Mon Feb  6 16:03:23 2012
Errors in file /oracle9/app/admin/settl/udump/settl1_ora_1355948.trc:
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []

trace文件信息

*** SESSION ID:(100.35758) 2012-02-06 16:03:23.223
*** 2012-02-06 16:03:23.223
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []
Current SQL statement for this session:
select trim(operator_id) into :b1  from b_sys_proc where program_name=:b2
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746CC ?
ksfdmp+0018          bl       01FD4010
kgeriv+0118          bl       _ptrgl
kgeasi+00cc          bl       kgeriv               1101FAF48 ? 110248038 ?
                                                   00000000C ? 110235978 ?
                                                   000000040 ?
ktcsptg+00c4         bl       kgeasi               1100062D8 ? 110389E88 ?
                                                   116600001166 ? 200000002 ?
                                                   000000000 ? 70000010738F0E8 ?
                                                   0FFFFFFFF ? 0FFFFFFFF ?
opiexe+0524          bl       ktcsptg              000000000 ? 000000000 ?
opiefn0+01c0         bl       opiexe               49FFFFA640 ? 4900000001 ?
                                                   FFFFFFFFFFFA5C8 ?
opiefn+0100          bl       opiefn0              01000CF60 ? 1029C61B0 ?
                                                   000000002 ? FFFFFFFFFFFC1BC ?
                                                   000000001 ? 00000000C ?
                                                   00000000B ? 110061F50 ?
opiodr+08cc          bl       _ptrgl
ttcpip+0cc4          bl       _ptrgl
opitsk+0d60          bl       ttcpip               11000CF60 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF48 ?
                                                   FFFFFFFFFFFF790 ? 0A000EEA8 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A0147B50 ?
                                                   FFFFFFFFFFFF790 ?
main+0138            bl       01FD3A28
__start+0098         bl       main                 000000000 ? 000000000 ?
………………
SO: 70000010738f0e8, type: 4, owner: 7000001043acd90, flag: INIT/-/-/0x00
    (session) trans: 70000010dde1dd8, creator: 7000001043acd90, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-0064-7625733A, short-term DID: 0000-0000-00000000
              txn branch: 7000001147dbed8
              oct: 0, prv: 0, sql: 700000117b7f778, psql: 0, user: 24/SETTLE
    O/S info: user: settle, term: , ospid: 1400836, machine: zwq_jies2
              program: /settlement/pkg01/bin/long/long_app@zwq_jies2 (T
    application name: /settlement/pkg01/bin/long/long_app@zwq_jies2 (T, hash value=0
    last wait for 'SQL*Net message from client' blocking sess=0x0 seq=483 wait_time=240
                driver id=54435000, #bytes=1, =0

查询MOS，发现是Bug# 1402161（虽然在mos中声明该bug是在9.2.0.1中已经被修复，但是这里的trace文件中的Call Stack Trace和mos中记录一致，而且网络上也存在很多9.2.0.8中关于该bug的情况），trace文件最后一段可以看出是在节点2上（zwq_jies2）运行/settlement/pkg01/bin/long/long_app程序导致出现该错误
产生该错误原因：

This exception is signalled because the savepoint number is not what was
  expected.
The current transaction savepoint is less than the beginning savepoint of
  the transaction.

解决方法：
把/settlement/pkg01/bin/long/long_app中的需要运行的程序分割成几个小程序运行。

Break the job into smaller chunks and reconnect for each part of the job
  to reset the savepoint number.

ORA-00600[ktspNextL1:4]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00600[ktspNextL1:4]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在检查某运营商的客服数据库时发现如下错误

Tue Jan 31 22:00:50 2012
Errors in file /oracle10/admin/ahunicom/bdump/ahunicom1_j005_24445074.trc:
ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
Tue Jan 31 22:01:18 2012
Trace dumping is performing id=[cdmp_20120131220118]
Tue Jan 31 22:01:18 2012
Errors in file /oracle10/admin/ahunicom/bdump/ahunicom1_j005_24445074.trc:
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1500
ORA-06512: at "SYS.DBMS_SPACE", line 1566
], [], [], [], [], [], [], []

查看trace文件

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle10/app/product/db/10.2.0
System name:    AIX
Node name:      zwq_kfdb1
Release:        1
Version:        6
Machine:        00C5C4764C00
Instance name: ahunicom1
Redo thread mounted by this instance: 1
Oracle process number: 192
Unix process pid: 24445074, image: oracle@zwq_kfdb1 (J005)
*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2012-01-31 22:00:50.874
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-31 22:00:50.874
*** SERVICE NAME:(SYS$USERS) 2012-01-31 22:00:50.874
*** SESSION ID:(454.44574) 2012-01-31 22:00:50.874
*** 2012-01-31 22:00:50.873
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
Current SQL statement for this session:
insert into wri$_adv_objspace_trend_data select timepoint,  space_usage, space_alloc, quality from  table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL
, NULL,  NULL, 'FALSE', :5, 'FALSE'))
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000010d9a65e0      1834  package body SYS.DBMS_SPACE
70000010d9a65e0      3922  package body SYS.DBMS_SPACE
70000010d9a65e0      4233  package body SYS.DBMS_SPACE
70000011a7d9c88         1  anonymous block
700000180678048       344  SYS.WRI$_ADV_OBJSPACE_TREND_T
700000180678048      1485  SYS.WRI$_ADV_OBJSPACE_TREND_T
7000001334867d0      1535  package body SYS.PRVT_ADVISOR
7000001334867d0      1618  package body SYS.PRVT_ADVISOR
70000011f9f64a8       186  package body SYS.DBMS_ADVISOR
70000010d9a65e0      1500  package body SYS.DBMS_SPACE
70000010d9a65e0      1566  package body SYS.DBMS_SPACE
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              70000017E9975D0 ? 100000001 ?
ksedmp+0290          bl       ksedst               104A2CDB0 ?
ksfdmp+0018          bl       03F2735C
kgerinv+00dc         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              000000000 ? 000000001 ?
                                                   000000005 ? 7000000E19760FC ?
                                                   7000000E1976014 ?
ktspGetNextL1ForSca  bl       01F94828
n+0104
ktspScanInit+026c    bl       ktspGetNextL1ForSca  A0A0E1B89 ? 10564BCF4 ?
                              n                    80003000804DC ?
ktspGenExtentMap1+0  bl       ktspScanInit         000000000 ?
0e8
kteinmap1+00bc       bl       ktspGenExtentMap1    000000000 ? 000000001 ?
                                                   FFFFFFFFFFE3A80 ?
kteinmap+0010        bl       kteinmap1            000000000 ? 00000000D ?
kdgini+036c          bl       kteinmap             FFFFFFFFFFE3DA0 ?
kdg_block_auto+018c  bl       kdgini               000000000 ? A0B4A708C ?
                                                   5676A0005676A ?
                                                   80003000804DC ? 000000000 ?
                                                   70000016AD82170 ? 110195498 ?
                                                   70000016AD82148 ?
ktsa_object_space_u  bl       kdg_block_auto       FFFFFFFFFFE4C50 ?
sage+0950                                          FFFFFFFFFFE4CA0 ? 200000780 ?
                                                   70000011A7D9C88 ?

由trace文件中的insert语句可以知道，这个错误是DBMS_SPACE.OBJECT_GROWTH_TREND进行空间分析时被触发
查询MOS[ID 841158.1]，发现这个是一个没有公布的bug(5649098),在11.1.0.7中被修复

解决方案:
1.忽略这个错误，因为这个错误是不可重复的，发生的概率不大
2.升级到11.1.0.7及其以上版本
3.如果遇到这个错误，可以手工执行dbms_space.auto_space_advisor_job_proc();

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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：老版本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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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导出单个表解决方案

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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不能执行原因探讨

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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

<strong>三.阅读trace文件</strong>
因为是obj$对象出现坏块，导致exp不能执行，如果是使用了obj$表的index，那么不会每次都报错，而我测试了多次都报错，所以怀疑是对obj$表进行全表扫描导致该错误发生，而使得exp不能继续下去。所以这次查找trace文件，重点是关注obj$表的全表扫描操作，经过耐心查找，终于发现了一个对obj$全表扫描的操作

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导出不能正常执行

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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之类的工具去解决问题。再次提醒各位，备份重于一切，安全重于泰山。

ORA-600 kcratr_nab_less_than_odr故障解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 kcratr_nab_less_than_odr故障解决

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

朋友的数据库服务器出现ORA-00600[kcratr_nab_less_than_odr]，不能open数据库
1.open数据库报ORA-00600[kcratr_nab_less_than_odr]

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189],
[43531], [43569], [], [], [], [], [], [], []

2.查看alert日志

Wed Jan 11 13:56:16 2012
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Completed redo scan
 read 54591 KB redo, 0 data blocks need recovery
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc  (incident=818557):
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
Incident details in: d:\dbdms\diag\rdbms\dbdms\dbdms\incident\incdir_818557\dbdms_ora_3936_i818557.trc
Aborting crash recovery due to error 600
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
Trace dumping is performing id=[cdmp_20120110214555]

3.查看trace文件

Trace file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 2 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:2250M/4060M, Ph+PgF:5868M/8119M
Instance name: dbdms
Redo thread mounted by this instance: 1
Oracle process number: 17
Windows thread id: 3108, image: ORACLE.EXE (SHAD)
…………………………
WARNING! Crash recovery of thread 1 seq 99189 is
ending at redo block 43531 but should not have ended before
redo block 43569
Incident 826550 created, dump file: d:\dbdms\diag\rdbms\dbdms\dbdms\incident\incdir_826550\dbdms_ora_3108_i826550.trc
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []

通过alert和trace中的内容可以知道，数据库需要恢复到rba到43569，但是因为某种原因实例恢复的时候，只能利用1 thread 99189 seq#，恢复rba到43531。从而导致数据库无法正常open

This Problem is caused by Storage Problem of the Database Files.
The Subsystem (eg. SAN) crashed while the Database was open.
The Database then crashed since the Database Files were not accessible anymore.
This caused a lost Write into the Online RedoLogs and so Instance Recovery is not possible and raising the ORA-600.

4.解决方法

SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
------------
MOUNTED
--尝试直接recover database
SQL> RECOVER DATABASE ;
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
--提示不用恢复
--再打开数据库，还是kcratr_nab_less_than_odr错误警告
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189],
[43531], [43569], [], [], [], [], [], [], []
--尝试不完全恢复
SQL> RECOVER DATABASE UNTIL CANCEL;
ORA-10879: error signaled in parallel recovery slave
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1: 'D:\DBDMS\DATA\SYSTEM01.DBF'
--重建控制文件
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:/1.TXT';
数据库已更改。
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP NOMOUNT;
ORACLE 例程已经启动。
Total System Global Area  417546240 bytes
Fixed Size                  2176328 bytes
Variable Size             268438200 bytes
Database Buffers          138412032 bytes
Redo Buffers                8519680 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DBDMS" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 18688
  7  LOGFILE
  8    GROUP 1 'D:\DBDMS\LOG\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\DBDMS\LOG\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\DBDMS\LOG\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\DBDMS\DATA\SYSTEM01.DBF',
 13    'D:\DBDMS\DATA\SYSAUX01.DBF',
 14    'D:\DBDMS\DATA\RBSG01.DBF',
 15    'D:\DBDMS\DATA\DATA01.DBF',
 16    'D:\DBDMS\DATA\INDX01.DBF',
 17    'D:\DBDMS\DATA\DATA02.DBF',
 18    'D:\DBDMS\DATA\DATA03.DBF',
 19    'D:\DBDMS\DATA\DATA04.DBF',
 20    'D:\DBDMS\DATA\INDX02.DBF',
 21    'D:\DBDMS\DATA\SYSTEM02.DBF'
 22  CHARACTER SET ZHS16GBK
 23  ;
控制文件已创建。
--继续尝试恢复
SQL> RECOVER DATABASE ;
完成介质恢复。
SQL> ALTER DATABASE OPEN;
数据库已更改。
--open成功

在这次恢复中，主要就是重建控制文件，然后直接恢复成功，如果redo有损坏，那么可能需要使用不完全恢复，然后使用resetlogs打开数据库

ORA-01555 caused by SQL statement below

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01555 caused by SQL statement below

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一.发现ORA-01555

Mon Dec 26 10:08:22 2011
ORA-01555 caused by SQL statement below (Query Duration=49146 sec, SCN: 0x0b4b.17f5ae42):
Mon Dec 26 10:08:22 2011
SELECT COMPANY_ID,
       COMPANY_MOBILE,
       TO_CHAR(NVL(REG_DATE, SYSDATE - 100), 'yyyymmddhh24miss'),
       BAK_FIELD2
  FROM TAB_XN_COMPANY
 WHERE (COMPANY_STATUS = 1 OR
       (COMPANY_STATUS = 3 AND
       NVL(UNREG_DATE, SYSDATE + 100) >=
       TO_DATE('20111226094500', 'yyyymmddhh24miss')))
   AND NVL(REG_DATE, SYSDATE - 100) <=
       TO_DATE('20111226095959', 'yyyymmddhh24miss')
   AND PAY_TYPE > 0

二.数据库状态

[oracle@ora02 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 4 10:48:17 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
SQL> select sum(maxbytes)/1024/1024/1024,
   2 SUM(USER_BYTES)/1024/1024/1024 FROM dba_data_files where tablespace_NAME='UNDOTBS1';
SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024
---------------------------- ------------------------------
                  61.9999847                     32.6834106
SQL> SELECT DISTINCT STATUS "状态",
  2                  COUNT(*) "EXTENT数量",
  3                  SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"
  4    FROM DBA_UNDO_EXTENTS
  5   GROUP BY STATUS;
状态      EXTENT数量   UNDO大小
--------- ---------- ----------
ACTIVE             1 .000976563
EXPIRED         2549 31.2333298
UNEXPIRED          3 .000175476

通过undo_retention保留时间为10800秒，而该sql执行了49146秒，在这49146秒钟，TAB_XN_COMPANY表中的数据被修改，而且被修改的undo数据在10800秒后被覆盖导致，导致原查询语句不能获取到scn小于或者等于查询时候的数据块内容(在undo中)，所以出现ORA-01555。从这里也可以看出来，在undo空间还剩余的情况下，如果超过了undo_retention限制，undo内容还是有可能被覆盖，而不是使用未使用的undo

三.出现ORA-1555原因
The ORA-1555 errors can happen when a query is unable to access enough undo to build a copy of the data at the time the query started. Committed “versions” of blocks are maintained along with newer uncommitted “versions” of those blocks so that queries can access data as it existed in the database at the time of the query. These are referred to as “consistent read” blocks and are maintained using Oracle undo management.
就是一个查询要访问某个数据块，而这个数据块在这个查询执行过程中修改过，那么该查询需要查询undo中数据块，而undo中该数据块已经不存在，从而出现ORA-1555

四.ORA-1555解决方法
Case 1 – Rollback Overwritten
1.缩短sql运行时间
2.增加undo_retention，这个同时需要考虑undo空间大小
3.减少commit(rollback)次数
4.在一条sql中尽量使数据块访问一次
4.1)Using a full table scan rather than an index lookup
4.2)Introducing a dummy sort so that we retrieve all the data, sort it and then sequentially visit these data blocks.

Case 2 – Rollback Transaction Slot Overwritten
这种问题，主要是延迟块清理导致，一般建议在进行大批量的dml操作后，使用全表(全index)扫描执行一遍，或者收集全部统计信息