Database SOS – 第121页 – Professional Database Recovery Support@+8617813235971

kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)

alert日志错误kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)
该错误是出现在win 2008 + 11.2.0.1的数据库中

Sun Jun 03 01:00:05 2012
kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DF0, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DF0, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
Sun Jun 03 02:00:18 2012
kewastUnPackStats(): bad magic 1 (0x0000000035387DD4, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DD4, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
Sun Jun 03 03:00:32 2012
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)

错误原因:
出现该问题是 Bug:8967729/unpublished bug 8730312
Active Session History中某些数据(the moduile, action program, etc. information)未被正确存储,然后awr程序在整点(默认配置)的时候访问V$ACTIVE_SESSION_HISTORY 视图失败,从而出现了该错误.出现该问题可能导致ash和awr使用异常或不准确

问题解决:
1.在Oracle12c release and the 11.2.0.2 and higher patchsets中被修复
2.打上one-off Patch:8730312(one-off补丁没有经过oracle的严格测试,在打该补丁前可能需要进行测试)

ORA-39126: 在 KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] 中 Worker 发生意外致命错误

使用impdp导入数据报如下错误导致导入终止

处理对象类型 SCHEMA_EXPORT/TABLE/TRIGGER
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39126: 在 KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] 中 Worker 发生意外致命错误
ORA-06502: PL/SQL: 数字或值错误
LPX-00225: end-element tag "HIST_GRAM_LIST_ITEM" does not match start-element tag "EPVALUE"
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPW$WORKER", line 9001
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
26ABF4B0     20462  package body SYS.KUPW$WORKER
26ABF4B0      9028  package body SYS.KUPW$WORKER
26ABF4B0     16665  package body SYS.KUPW$WORKER
26ABF4B0      3956  package body SYS.KUPW$WORKER
26ABF4B0      9725  package body SYS.KUPW$WORKER
26ABF4B0      1775  package body SYS.KUPW$WORKER
290D454C         2  anonymous block
ORA-39097: 数据泵作业出现意外的错误 -1427
ORA-39065: DISPATCH 中出现意外的主进程异常错误
ORA-01427: 单行子查询返回多个行
作业 "EAS"."SYS_IMPORT_SCHEMA_01" 因致命错误于 15:21:20 停止

从这里可以看出是在执行TABLE_STATISTICS的时候因为EPVALUE列的数据类型和导入数据不匹配,问题发生上面错误,导致impdp job终止.

解决办法
参考文档:[ID 878626.1]
1.如果数据已经expdp导出,建议在导入的时候屏蔽掉统计信息导入EXCLUDE=STATISTICS,导入后使用DBMS_STATS 重新收集统计信息
2.如果数据尚未expdp导出,建议在导出的时候屏蔽掉统计信息导出EXCLUDE=STATISTICS导入后使用DBMS_STATS 重新收集统计信息

undo坏块导致数据库异常终止案例

在处理的众多undo问题的数据库中,这个是第一例遇到因为undo出现坏块导致数据库自动down案例(oracle 9.2.0.8 aix)
undo坏块导致数据库down

Fri Jun  1 00:45:13 2012
Successfully onlined Undo Tablespace 1.
Fri Jun  1 00:45:13 2012
SMON: enabling tx recovery
Fri Jun  1 00:45:13 2012
Database Characterset is ZHS16GBK
Fri Jun  1 00:45:13 2012
SMON: about to recover undo segment 52
SMON: about to recover undo segment 52
SMON: mark undo segment 52 as available
SMON: Restarting fast_start parallel rollback
SMON: about to recover undo segment 52
SMON: mark undo segment 52 as available
SMON: ignoring slave err,downgrading to serial rollback
SMON: about to recover undo segment 52
ORACLE Instance acc1 (pid = 9) - Error 1578 encountered while recovering transaction (52, 29).
Fri Jun  1 00:45:14 2012
Errors in file /oraacc/app/admin/acc/bdump/acc1_smon_734734.trc:
ORA-01578: ORACLE data block corrupted (file # 169, block # 55887)
ORA-01110: data file 169: '/dev/raccount07_01lv'
Fri Jun  1 00:45:15 2012
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Fri Jun  1 00:45:16 2012
Errors in file /oraacc/app/admin/acc/bdump/acc1_pmon_766940.trc:
ORA-00474: SMON process terminated with error
Fri Jun  1 00:45:16 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 766940

这里可以看出数据库因为回滚段52出现坏块导致回滚的时候smon终止,数据库down

检测坏块

$ dbv file='/dev/raccount07_01lv' blocksize=8192
DBVERIFY: Release 9.2.0.8.0 - Production on Fri Jun 1 01:25:10 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
DBVERIFY - Verification starting : FILE = /dev/raccount07_01lv
DBV-00200: Block, dba 708893135, already marked corrupted
DBV-00200: Block, dba 708893151, already marked corrupted
DBV-00200: Block, dba 708893263, already marked corrupted
DBVERIFY - Verification complete
Total Pages Examined         : 1048320
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1048320
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Highest block SCN            : 12843497215383 (2990.1545000343)

检测对应文件号/数据块号

SQL> select DBMS_UTILITY.data_block_address_file(708893135) file#,
  2  DBMS_UTILITY.data_block_address_block(708893135) block#  from dual;
     FILE#     BLOCK#
---------- ----------
       169      55759
SQL> select DBMS_UTILITY.data_block_address_file(708893151) file#,
  2  DBMS_UTILITY.data_block_address_block(708893151) block#  from dual;
     FILE#     BLOCK#
---------- ----------
       169      55775
SQL> select DBMS_UTILITY.data_block_address_file(708893263) file#,
  2  DBMS_UTILITY.data_block_address_block(708893263) block#  from dual;
     FILE#     BLOCK#
---------- ----------
       169      55887

解决办法

--隐含参数
_corrupted_rollback_segments= _SYSSMU52$
--open库后
alter session set "_smu_debug_mode"=4;
drop rollback segment "_SYSSMU52$";

连续两次REMOTE_LISTENER 设置为null导致pmon和listener异常

平台系统版本相关信息

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> show parameter cluster;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string      192.168.16.11
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;
www.xifenfei.com
-------------------
2012-05-30 11:07:12

pmon和监听负载
pmon和LISTENER进程负载均比较高

  PID     %CPU ResSize    Char Command
10617230  72.9  143924 21014  ora_pmon_ahunicom1
22675560  49.9  142000  1547  oracleahunicom1 (LOCAL=NO)
 5243206  30.6   49728  2579  /oracle10/app/product/db/10.2.0/bin/tnslsnr LISTENER -inherit

监听日志
每秒钟很多类此pmon注册监听信息

27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0

通过这两点可以确定是因为pmon在不停的动态注册监听导致监听日志,pmon,listener进程异常

查询MOS[ID 982068.1]
问题原因

After altering the value of the parameter REMOTE_LISTENER,
excessive CPU is seen for the TNS listener process (TNSLSNR) and the listener.log file grows rapidly.
Alert log confirms the REMOTE_LISTENER parameter in the SPFILE was altered.
Listener.log shows continuous service_update triggered from PMON to the TNS listener, 100's per second.
REMOTE_LISTENER had been set to null twice
查询alert日志,果真发现:
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH SID='AHUNICOM1';
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH;

解决方案

alter system set remote_listener = 'remote_rac' scope=memory sid = 'AHUNICOM1';
alter system set remote_listener = '' scope=memory sid = 'AHUNICOM1';
--然后重启节点,pmon和监听恢复正常

使用bbed修复损坏datafile header

相关信息和准备工作

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;
www.xifenfei.com
-------------------
2012-05-29 19:39:48

启动数据块异常

SQL> startup
ORACLE instance started.
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'
ORA-01115: IO error reading block from file 11 (block # 1)
ORA-27072: skgfdisp: I/O error

bbed检测datafile header

[oracle@xifenfei ~]$ bbed password=blockedit blocksize=8192  listfile=/home/oracle/bbed.file mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Sat May 26 05:29:37 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /home/oracle/users01.dbf                                             0
     2  /home/oracle/system01.dbf.head                                       0
     3  /home/oracle/data11.ora                                              0
     4  /u01/oracle/oradata/xifenfei/system01.dbf                            0
     5  /u01/oracle/oradata/xifenfei/users01.dbf                             0
     6  /home/oracle/data11.ora.10                                           0
    11  /u01/oracle/oradata/xifenfei/bbed01.dbf                              0
    12  /u01/oracle/oradata/xifenfei/bbed02.dbf                              0
BBED> set file 11
        FILE#           11
BBED> set block 1
        BLOCK#          1
BBED> map
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                                     Dba:0x02c00001
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> d
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:    0 to  511           Dba:0x02c00001
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--header 记录全部为0，证明数据文件header坏掉

拷贝数据块
为了方便,拷贝同一个表空间的数据块

BBED> set file 12
        FILE#           12
BBED> set block 1
        BLOCK#          1
BBED> d count 16
 File: /u01/oracle/oradata/xifenfei/bbed02.dbf (12)
 Block: 1                Offsets:    0 to   15           Dba:0x03000001
------------------------------------------------------------------------
 0b020000 01000003 00000000 00000104
 <32 bytes per line>
BBED> copy dba 0x03000001 to dba 0x02c00001
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:    0 to   15           Dba:0x02c00001
------------------------------------------------------------------------
 0b020000 01000003 00000000 00000104
 <32 bytes per line>
BBED> show
        FILE#           11
        BLOCK#          1
        OFFSET          0
        DBA             0x02c00001 (46137345 11,1)
        FILENAME        /u01/oracle/oradata/xifenfei/bbed01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/bbed.file
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           16
        LOGFILE         log.bbd
        SPOOL           No
BBED> map
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                                     Dba:0x02c00001
------------------------------------------------------------
 Data File Header
 struct kcvfh, 360 bytes                    @0
 ub4 tailchk                                @8188

修改数据块内容

BBED>  p kcvfh
struct kcvfh, 360 bytes                     @0
   struct kcvfhbfh, 20 bytes                @0
      ub1 type_kcbh                         @0        0x0b
      ub1 frmt_kcbh                         @1        0x02
      ub1 spare1_kcbh                       @2        0x00
      ub1 spare2_kcbh                       @3        0x00
      ub4 rdba_kcbh                         @4        0x03000001
      ub4 bas_kcbh                          @8        0x00000000
      ub2 wrp_kcbh                          @12       0x0000
      ub1 seq_kcbh                          @14       0x01
      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
      ub2 chkval_kcbh                       @16       0xb10a
      ub2 spare3_kcbh                       @18       0x0000
   struct kcvfhhdr, 76 bytes                @20
      ub4 kccfhswv                          @20       0x09200000
      ub4 kccfhcvn                          @24       0x08000000
      ub4 kccfhdbi                          @28       0x5314b4cd
      text kccfhdbn[0]                      @32      X
      text kccfhdbn[1]                      @33      I
      text kccfhdbn[2]                      @34      F
      text kccfhdbn[3]                      @35      E
      text kccfhdbn[4]                      @36      N
      text kccfhdbn[5]                      @37      F
      text kccfhdbn[6]                      @38      E
      text kccfhdbn[7]                      @39      I
      ub4 kccfhcsq                          @40       0x000001d8
      ub4 kccfhfsz                          @44       0x00001400
      s_blkz kccfhbsz                       @48       0x00
      ub2 kccfhfno                          @52       0x000c
      ub2 kccfhtyp                          @54       0x0003
      ub4 kccfhacid                         @56       0x00000000
      ub4 kccfhcks                          @60       0x00000000
      text kccfhtag[0]                      @64
      text kccfhtag[1]                      @65
      text kccfhtag[2]                      @66
      text kccfhtag[3]                      @67
      text kccfhtag[4]                      @68
      text kccfhtag[5]                      @69
      text kccfhtag[6]                      @70
      text kccfhtag[7]                      @71
      text kccfhtag[8]                      @72
      text kccfhtag[9]                      @73
      text kccfhtag[10]                     @74
      text kccfhtag[11]                     @75
      text kccfhtag[12]                     @76
      text kccfhtag[13]                     @77
      text kccfhtag[14]                     @78
      text kccfhtag[15]                     @79
      text kccfhtag[16]                     @80
      text kccfhtag[17]                     @81
      text kccfhtag[18]                     @82
      text kccfhtag[19]                     @83
      text kccfhtag[20]                     @84
      text kccfhtag[21]                     @85
      text kccfhtag[22]                     @86
      text kccfhtag[23]                     @87
      text kccfhtag[24]                     @88
      text kccfhtag[25]                     @89
      text kccfhtag[26]                     @90
      text kccfhtag[27]                     @91
      text kccfhtag[28]                     @92
      text kccfhtag[29]                     @93
      text kccfhtag[30]                     @94
      text kccfhtag[31]                     @95
   ub4 kcvfhrdb                             @96       0x00000000
   struct kcvfhcrs, 8 bytes                 @100
      ub4 kscnbas                           @100      0xc00a3405
      ub2 kscnwrp                           @104      0x0b2c
   ub4 kcvfhcrt                             @108      0x2ebeb8c3
   ub4 kcvfhrlc                             @112      0x2e51408f
   struct kcvfhrls, 8 bytes                 @116
      ub4 kscnbas                           @116      0x0002ab78
      ub2 kscnwrp                           @120      0x0000
   ub4 kcvfhbti                             @124      0x00000000
   struct kcvfhbsc, 8 bytes                 @128
      ub4 kscnbas                           @128      0x00000000
      ub2 kscnwrp                           @132      0x0000
   ub2 kcvfhbth                             @136      0x0000
   ub2 kcvfhsta                             @138      0x0000 (NONE)
   struct kcvfhckp, 36 bytes                @140
      struct kcvcpscn, 8 bytes              @140
         ub4 kscnbas                        @140      0xc00b6467
         ub2 kscnwrp                        @144      0x0b2c
      ub4 kcvcptim                          @148      0x2ebf0c07
      ub2 kcvcpthr                          @152      0x0001
      union u, 12 bytes                     @156
         struct kcvcprba, 12 bytes          @156
            ub4 kcrbaseq                    @156      0x00000015
            ub4 kcrbabno                    @160      0x0000429a
            ub2 kcrbabof                    @164      0x0010
         struct kcvcptr, 12 bytes           @156
            struct kcrtrscn, 8 bytes        @156
               ub4 kscnbas                  @156      0x00000015
               ub2 kscnwrp                  @160      0x429a
            ub4 kcrtrtim                    @164      0x09110010
      ub1 kcvcpetb[0]                       @168      0x02
      ub1 kcvcpetb[1]                       @169      0x00
      ub1 kcvcpetb[2]                       @170      0x00
      ub1 kcvcpetb[3]                       @171      0x00
      ub1 kcvcpetb[4]                       @172      0x00
      ub1 kcvcpetb[5]                       @173      0x00
      ub1 kcvcpetb[6]                       @174      0x00
      ub1 kcvcpetb[7]                       @175      0x00
   ub4 kcvfhcpc                             @176      0x0000000d
   ub4 kcvfhrts                             @180      0x2ebeea4f
   ub4 kcvfhccc                             @184      0x0000000c
   struct kcvfhbcp, 36 bytes                @188
      struct kcvcpscn, 8 bytes              @188
         ub4 kscnbas                        @188      0x00000000
         ub2 kscnwrp                        @192      0x0000
      ub4 kcvcptim                          @196      0x00000000
      ub2 kcvcpthr                          @200      0x0000
      union u, 12 bytes                     @204
         struct kcvcprba, 12 bytes          @204
            ub4 kcrbaseq                    @204      0x00000000
            ub4 kcrbabno                    @208      0x00000000
            ub2 kcrbabof                    @212      0x0000
         struct kcvcptr, 12 bytes           @204
            struct kcrtrscn, 8 bytes        @204
               ub4 kscnbas                  @204      0x00000000
               ub2 kscnwrp                  @208      0x0000
            ub4 kcrtrtim                    @212      0x00000000
      ub1 kcvcpetb[0]                       @216      0x00
      ub1 kcvcpetb[1]                       @217      0x00
      ub1 kcvcpetb[2]                       @218      0x00
      ub1 kcvcpetb[3]                       @219      0x00
      ub1 kcvcpetb[4]                       @220      0x00
      ub1 kcvcpetb[5]                       @221      0x00
      ub1 kcvcpetb[6]                       @222      0x00
      ub1 kcvcpetb[7]                       @223      0x00
   ub4 kcvfhbhz                             @224      0x00000000
   struct kcvfhxcd, 16 bytes                @228
      ub4 space_kcvmxcd[0]                  @228      0x00000000
      ub4 space_kcvmxcd[1]                  @232      0x00000000
      ub4 space_kcvmxcd[2]                  @236      0x00000000
      ub4 space_kcvmxcd[3]                  @240      0x00000000
   word kcvfhtsn                            @244      12
   ub2 kcvfhtln                             @248      0x0004
   text kcvfhtnm[0]                         @250     B
   text kcvfhtnm[1]                         @251     B
   text kcvfhtnm[2]                         @252     E
   text kcvfhtnm[3]                         @253     D
   text kcvfhtnm[4]                         @254
   text kcvfhtnm[5]                         @255
   text kcvfhtnm[6]                         @256
   text kcvfhtnm[7]                         @257
   text kcvfhtnm[8]                         @258
   text kcvfhtnm[9]                         @259
   text kcvfhtnm[10]                        @260
   text kcvfhtnm[11]                        @261
   text kcvfhtnm[12]                        @262
   text kcvfhtnm[13]                        @263
   text kcvfhtnm[14]                        @264
   text kcvfhtnm[15]                        @265
   text kcvfhtnm[16]                        @266
   text kcvfhtnm[17]                        @267
   text kcvfhtnm[18]                        @268
   text kcvfhtnm[19]                        @269
   text kcvfhtnm[20]                        @270
   text kcvfhtnm[21]                        @271
   text kcvfhtnm[22]                        @272
   text kcvfhtnm[23]                        @273
   text kcvfhtnm[24]                        @274
   text kcvfhtnm[25]                        @275
   text kcvfhtnm[26]                        @276
   text kcvfhtnm[27]                        @277
   text kcvfhtnm[28]                        @278
   text kcvfhtnm[29]                        @279
   ub4 kcvfhrfn                             @280      0x0000000c
   struct kcvfhrfs, 8 bytes                 @284
      ub4 kscnbas                           @284      0x00000000
      ub2 kscnwrp                           @288      0x0000
   ub4 kcvfhrft                             @292      0x2ebee9f9
   struct kcvfhafs, 8 bytes                 @296
      ub4 kscnbas                           @296      0x00000000
      ub2 kscnwrp                           @300      0x0000
   ub4 kcvfhbbc                             @304      0x00000000
   ub4 kcvfhncb                             @308      0x00000000
   ub4 kcvfhmcb                             @312      0x00000000
   ub4 kcvfhlcb                             @316      0x00000000
   ub4 kcvfhbcs                             @320      0x00000000
   ub2 kcvfhofb                             @324      0x0000
   ub2 kcvfhnfb                             @326      0x0000
   ub4 kcvfhprc                             @328      0x00000000
   struct kcvfhprs, 8 bytes                 @332
      ub4 kscnbas                           @332      0x00000000
      ub2 kscnwrp                           @336      0x0000
   struct kcvfhprfs, 8 bytes                @340
      ub4 kscnbas                           @340      0x00000000
      ub2 kscnwrp                           @344      0x0000
   ub4 kcvfhtrt                             @356      0x00000000
/*需要修改内容
ub4 rdba_kcbh                         @4        0x03000001
ub4 kccfhfsz                          @44       0x00001400
ub2 kccfhfno                          @52       0x000c
struct kcvfhcrs, 8 bytes                 @100
      ub4 kscnbas                           @100      0xc00a3405
      ub2 kscnwrp                           @104      0x0b2c
ub4 kcvfhrfn                             @280      0x0000000c
/
/*修改值(通过错误提示结合file$表)
rdba_kcbh  02c00001
kccfhfsz  00000500
kccfhfno 000b
kscnbas c00a32b8
kcvfhrfn 0000000b
/
BBED> set offset 4
        OFFSET          4
BBED> m /x 0100c002
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:    4 to   19           Dba:0x02c00001
------------------------------------------------------------------------
 0100c002 00000000 00000104 0ab10000
 <32 bytes per line>
BBED> set offset 44
        OFFSET          44
BBED> m /x 00050000
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:   44 to   59           Dba:0x02c00001
------------------------------------------------------------------------
 00050000 00200000 0c000300 00000000
 <32 bytes per line>
BBED> set offset 52
        OFFSET          52
BBED> m /x
BBED-00203: incomplete/malformed command
BBED> m /x 0b00
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:   52 to   67           Dba:0x02c00001
------------------------------------------------------------------------
 0b000300 00000000 00000000 00000000
 <32 bytes per line>
BBED> set offset 100
        OFFSET          100
BBED> m /x b8320ac0
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:  100 to  115           Dba:0x02c00001
------------------------------------------------------------------------
 b8320ac0 2c0b0000 c3b8be2e 8f40512e
 <32 bytes per line>
BBED> set offset 280
        OFFSET          280
BBED> m /x 0b000000
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:  280 to  295           Dba:0x02c00001
------------------------------------------------------------------------
 0b000000 00000000 00000000 f9e9be2e
 <32 bytes per line>
BBED> sum apply
Check value for File 11, Block 1:
current = 0xa777, required = 0xa777

重建控制文件open数据库

SQL> alter database backup controlfile to trace as '/tmp/t_xifenfie.ctl';
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 '/u01/oracle/oradata/xifenfei/redo01.log'  SIZE 100M,
  9    GROUP 2 '/u01/oracle/oradata/xifenfei/redo02.log'  SIZE 100M,
 10    GROUP 3 '/u01/oracle/oradata/xifenfei/redo03.log'  SIZE 100M
 11  DATAFILE
 12    '/u01/oracle/oradata/xifenfei/system01.dbf',
 13    '/u01/oracle/oradata/xifenfei/undotbs01.dbf',
 14    '/u01/oracle/oradata/xifenfei/cwmlite01.dbf',
 15    '/u01/oracle/oradata/xifenfei/drsys01.dbf',
 16    '/u01/oracle/oradata/xifenfei/example01.dbf',
 17    '/u01/oracle/oradata/xifenfei/indx01.dbf',
 18    '/u01/oracle/oradata/xifenfei/odm01.dbf',
 19    '/u01/oracle/oradata/xifenfei/tools01.dbf',
 20    '/u01/oracle/oradata/xifenfei/users01.dbf',
 21    '/u01/oracle/oradata/xifenfei/xdb01.dbf',
 22    '/u01/oracle/oradata/xifenfei/bbed01.dbf',
 23    '/u01/oracle/oradata/xifenfei/bbed02.dbf'
 24  CHARACTER SET ZHS16GBK
 25  ;
Control file created.
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open;
Database altered.

至此通过拷贝相同表空间的datafile header修复损坏的datafile header

系统中数据文件第一个数据块和oracle 中第一个数据块关系

数据文件第一个数据块到底有没有纳入数据块的数据块计算中,也就是我们通常所说的rdba(file#,block),是否真的是从数据文件的第一个数据块开始计算的？下面通过实验验证
相关信息和准备工作

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;
www.xifenfei.com
-------------------
2012-05-29 19:39:48
SQL> select name,block_size from v$datafile where file#=9;
NAME                                                         BLOCK_SIZE
------------------------------------------------------------ ----------
/u01/oracle/oradata/xifenfei/users01.dbf                           8192
--dd出来数据文件第一和第二个数据块
[oracle@xifenfei ~]$ dd if=/u01/oracle/oradata/xifenfei/users01.dbf of=user.01 bs=8192 count=1
1+0 records in
1+0 records out
[oracle@xifenfei ~]$ dd if=/u01/oracle/oradata/xifenfei/users01.dbf of=user.02 bs=8192 count=1 skip=1
1+0 records in
1+0 records out
[oracle@xifenfei ~]$ ll user.*
-rw-r--r--  1 oracle oinstall 8192 May 26 04:43 user.01
-rw-r--r--  1 oracle oinstall 8192 May 26 04:44 user.02

bbed验证

[oracle@xifenfei ~]$ bbed password=blockedit blocksize=8192  listfile=/home/oracle/bbed_new.file mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Sat May 26 04:56:49 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/xifenfei/users01.dbf                             0
     2  /home/oracle/user.01                                                 0
     3  /home/oracle/user.02                                                 0
--users01.dbf(完整数据文件,第一个数据块)
BBED> set file 1
        FILE#           1
BBED> set block 1
        BLOCK#          1
BBED> d /v count 128
 File: /u01/oracle/oradata/xifenfei/users01.dbf (1)
 Block: 1       Offsets:    0 to  127  Dba:0x00400001
-------------------------------------------------------
 0b020000 01004002 00000000 00000104 l ......@.........
 7f4b0000 00002009 00000008 cdb41453 l .K.... ........S
 58494645 4e464549 c7010000 800c0000 l XIFENFEI........
 00200000 09000300 00000000 00000000 l . ..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 47180000 00000000 cf4d851e l ....G........M..
 8f40512e 78ab0200 00000000 00000000 l .@Q.x...........
 <16 bytes per line>
--直接设置file 2错误(后续提供其他方法)
BBED> set file 2
BBED-00307: incorrect blocksize (8192) or truncated file
--查看users01.dbf(第二个数据块)
BBED> set file 3
        FILE#           3
BBED> set block 1
        BLOCK#          1
BBED> d /v count 128
 File: /home/oracle/user.02 (3)
 Block: 1       Offsets:    0 to  127  Dba:0x00c00001
-------------------------------------------------------
 0b020000 01004002 00000000 00000104 l ......@.........
 7f4b0000 00002009 00000008 cdb41453 l .K.... ........S
 58494645 4e464549 c7010000 800c0000 l XIFENFEI........
 00200000 09000300 00000000 00000000 l . ..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 47180000 00000000 cf4d851e l ....G........M..
 8f40512e 78ab0200 00000000 00000000 l .@Q.x...........
 <16 bytes per line>
--查看users01.dbf(真正第一个数据块)
BBED> set filename 'user.01'
        FILENAME        user.01
BBED> d /v count 128
 File: user.01 (0)
 Block: 1       Offsets:    0 to  127  Dba:0x00000000
-------------------------------------------------------
 00020000 00200000 800c0000 5d5c5b5a l ..... ......]\[Z
 00000000 86280000 00000000 00000000 l .....(..........
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 <16 bytes per line>

通过这个对比可以知道:当我们直接使用bbed查看数据块内容的时候,自动屏蔽了数据文件上真正的第一个数据块.其实block 1是数据文件上的第二个数据块

hexdump验证

--users01.dbf(完整文件)
[oracle@xifenfei ~]$ hexdump -C /u01/oracle/oradata/xifenfei/users01.dbf|head -20
00000000  00 02 00 00 00 20 00 00  80 0c 00 00 5d 5c 5b 5a  |..... ......]\[Z|
00000010  00 00 00 00 86 28 00 00  00 00 00 00 00 00 00 00  |.....(..........|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000  0b 02 00 00 01 00 40 02  00 00 00 00 00 00 01 04  |......@.........|
00002010  7f 4b 00 00 00 00 20 09  00 00 00 08 cd b4 14 53  |.K.... ........S|
00002020  58 49 46 45 4e 46 45 49  c7 01 00 00 80 0c 00 00  |XIFENFEI........|
00002030  00 20 00 00 09 00 03 00  00 00 00 00 00 00 00 00  |. ..............|
00002040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002060  00 00 00 00 47 18 00 00  00 00 00 00 cf 4d 85 1e  |....G........M..|
00002070  8f 40 51 2e 78 ab 02 00  00 00 00 00 00 00 00 00  |.@Q.x...........|
00002080  00 00 00 00 00 00 00 00  00 00 04 00 58 0d 0b c0  |............X...|
00002090  2c 0b 00 00 5a ea be 2e  01 00 aa bd 15 00 00 00  |,...Z...........|
000020a0  02 00 00 00 10 00 ff bf  02 00 00 00 00 00 00 00  |................|
000020b0  5a 00 00 00 4f ea be 2e  59 00 00 00 00 00 00 00  |Z...O...Y.......|
000020c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000020f0  00 00 00 00 09 00 00 00  05 00 55 53 45 52 53 00  |..........USERS.|
00002100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
--users01.dbf(第一个数据块文件)
[oracle@xifenfei ~]$ hexdump -C user.01
00000000  00 02 00 00 00 20 00 00  80 0c 00 00 5d 5c 5b 5a  |..... ......]\[Z|
00000010  00 00 00 00 86 28 00 00  00 00 00 00 00 00 00 00  |.....(..........|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000
--users01.dbf(第二个数据块文件)
[oracle@xifenfei ~]$ hexdump -C user.02|head -20
00000000  0b 02 00 00 01 00 40 02  00 00 00 00 00 00 01 04  |......@.........|
00000010  7f 4b 00 00 00 00 20 09  00 00 00 08 cd b4 14 53  |.K.... ........S|
00000020  58 49 46 45 4e 46 45 49  c7 01 00 00 80 0c 00 00  |XIFENFEI........|
00000030  00 20 00 00 09 00 03 00  00 00 00 00 00 00 00 00  |. ..............|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00000060  00 00 00 00 47 18 00 00  00 00 00 00 cf 4d 85 1e  |....G........M..|
00000070  8f 40 51 2e 78 ab 02 00  00 00 00 00 00 00 00 00  |.@Q.x...........|
00000080  00 00 00 00 00 00 00 00  00 00 04 00 58 0d 0b c0  |............X...|
00000090  2c 0b 00 00 5a ea be 2e  01 00 aa bd 15 00 00 00  |,...Z...........|
000000a0  02 00 00 00 10 00 ff bf  02 00 00 00 00 00 00 00  |................|
000000b0  5a 00 00 00 4f ea be 2e  59 00 00 00 00 00 00 00  |Z...O...Y.......|
000000c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000000f0  00 00 00 00 09 00 00 00  05 00 55 53 45 52 53 00  |..........USERS.|
00000100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000110  00 00 00 00 00 00 00 00  09 00 00 00 00 00 00 00  |................|
00000120  00 00 00 00 f9 e9 be 2e  00 00 00 00 00 00 00 00  |................|
00000130  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

通过hexdump对三个文件的对比可以知道users01.dbf的头两个数据文件确实是由第一和第二个数据块组成.然后结合上面bbed dump出来的结果.可以再次证明数据文件第一个数据块,不能被bbed识别(从第二个数据文件开始)

实验总结
我们的数据文件其实是从文件的第二个数据块开始记录起(该数据块为block 1).也就是说系统的数据块和oracle中的rdba标示的数据块不是一致.而是系统数据块比oracle数据块多1.
因这个原因解释了以前的一个疑问:Oracle数据文件大小的限制为什么指定数据文件最大值为(2^22-1*block_size),而不是根据rowid的2^22*block_size
关于类此问题在windows验证请见:在UltraEdit中定位数据文件内容

拷贝windows中datafile header方法(ocopy)

在很多时候,我们需要对数据文件的头部进行分析,但是因为人不在本地,数据文件本身很大,网络又不好.这个时候我们可能要求对方传过来文件文件的头部几M即可.在unix/linux中可以使用dd实现该需求;在win中可以使用ocopy实现该需求.dd实现请参考:dd操作数据文件;这里讲win下面实现方法:
ocopy语法

D:\>ocopy
OCOPY v2.0 - Copyright 1989-1993 Oracle Corp.  All rights reserved.
Usage:
    ocopy from_file [to_file [a | size_1 [size_n]]]
    ocopy -b from_file to_drive
    ocopy -r from_drive to_dir

ocopy拷贝数据文件header

D:\>ocopy  E:\oracle\oradata\xifenfei\SYSAUX01.DBF d:\sysaux.dbf 20480 1
D:\SYSAUX.DBF
OCOPY - Write error.
--忽略(未找到原因)
D:\>dir sysaux*
 驱动器 D 中的卷没有标签。
 卷的序列号是 000B-FBCB
 D:\ 的目录
2012/05/07  22:28             1,024 SYSAUX.DB2
2012/05/07  22:28             1,024 SYSAUX.DB3
2012/05/07  22:28             1,024 SYSAUX.DB4
2012/05/07  22:28             1,024 SYSAUX.DB5
2012/05/07  22:28             1,024 SYSAUX.DB6
2012/05/07  22:28             1,024 SYSAUX.DB7
2012/05/07  22:28             1,024 SYSAUX.DB8
2012/05/07  22:28             1,024 SYSAUX.DB9
2012/05/07  22:28        20,971,520 SYSAUX.DBF
               9 个文件     20,979,712 字节
               0 个目录 28,771,282,944 可用字节
--SYSAUX.DBF是我们需要的文件

上传到linux中bbed验证

[oracle@xifenfei ~]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri May 25 08:31:12 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/home/oracle/sysaux.dbf'
        FILENAME        /home/oracle/sysaux.dbf
BBED> set blocksize 8192
        BLOCKSIZE       8192
--从win中拷贝的数据库,第一个block非bbed有效块
BBED> set block 2
        BLOCK#          2
BBED> map
 File: /home/oracle/sysaux.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header
 struct kcvfh, 360 bytes                    @0
 ub4 tailchk                                @8188
BBED> map /v
 File: /home/oracle/sysaux.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header
 struct kcvfh, 360 bytes                    @0
    struct kcvfhbfh, 20 bytes               @0
    struct kcvfhhdr, 76 bytes               @20
    ub4 kcvfhrdb                            @96
    struct kcvfhcrs, 8 bytes                @100
    ub4 kcvfhcrt                            @108
    ub4 kcvfhrlc                            @112
    struct kcvfhrls, 8 bytes                @116
    ub4 kcvfhbti                            @124
    struct kcvfhbsc, 8 bytes                @128
    ub2 kcvfhbth                            @136
    ub2 kcvfhsta                            @138
    struct kcvfhckp, 36 bytes               @140
    ub4 kcvfhcpc                            @176
    ub4 kcvfhrts                            @180
    ub4 kcvfhccc                            @184
    struct kcvfhbcp, 36 bytes               @188
    ub4 kcvfhbhz                            @224
    struct kcvfhxcd, 16 bytes               @228
    word kcvfhtsn                           @244
    ub2 kcvfhtln                            @248
    text kcvfhtnm[30]                       @250
    ub4 kcvfhrfn                            @280
    struct kcvfhrfs, 8 bytes                @284
    ub4 kcvfhrft                            @292
    struct kcvfhafs, 8 bytes                @296
    ub4 kcvfhbbc                            @304
    ub4 kcvfhncb                            @308
    ub4 kcvfhmcb                            @312
    ub4 kcvfhlcb                            @316
    ub4 kcvfhbcs                            @320
    ub2 kcvfhofb                            @324
    ub2 kcvfhnfb                            @326
    ub4 kcvfhprc                            @328
    struct kcvfhprs, 8 bytes                @332
    struct kcvfhprfs, 8 bytes               @340
    ub4 kcvfhtrt                            @356
 ub4 tailchk                                @8188
--数据块拷贝出来正常

使用dbms_metadata.get_ddl出现ORA-31605错误

使用dbms_metadata.get_ddl出现ORA-31605错误

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;
www.xifenfei.com
-------------------
2012-05-26 23:10:22
SQL> select dbms_metadata.get_ddl('TABLE','XFF_IOT','CHF1') from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3320
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1
no rows selected

错误原因
dbms_metadata.get_ddl需要调用Oracle dictionary table “sys.metastylesheet.”中的XSL stylesheets,但是由于某种原因,使得调用失败,出现上述错误.因为该错误可能有:
1.XSL stylesheets没有安装
2.使用alter database 修改数据库字符集(本库是因为昨天修改字符集导致)

解决办法(sys用户执行)
1.在10g及其以上版本中(不带参数)

SQL> exec dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.

2.在9i版本中(带dir参数)

SQL> exec dbms_metadata_util.load_stylesheets('/u01/oracle/9.2.0/db_1/rdbms/xml/xsl');
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl('TABLE','XFF_IOT','CHF1') from dual;
DBMS_METADATA.GET_DDL('TABLE','XFF_IOT','CHF1')
--------------------------------------------------------------------------------
  CREATE TABLE "CHF1"."XFF_IOT"
   (    "ID" NUMBER,
        "NAME" VARCHAR2(30),
         CONSTRAINT "CHF_IOT_ID#_PK" PRIMARY KEY ("ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 PCTTHRESHOLD 50
DBMS_METADATA.GET_DDL('TABLE','XFF_IOT','CHF1')
--------------------------------------------------------------------------------

asm备份元数据之md_backup和md_restore

在11g的asm中增加了md_backup和md_restore命令,用来备份和还原asm的元数据信息
当前磁盘组相关信息

SQL> select PATH,b.NAME from v$asm_disk a,v$asm_diskgroup b where a.GROUP_NUMBER=b.GROUP_NUMBER;
PATH                                     NAME
---------------------------------------- ----------
/dev/oracleasm/disks/VOL2                DATA
/dev/oracleasm/disks/VOL1                DATA
/dev/oracleasm/disks/VOL4                XIFENFEI
/dev/oracleasm/disks/VOL3                XIFENFEI

md_backup操作

--备份所有mount磁盘组
ASMCMD> md_backup /tmp/xifenfei.md
Disk group metadata to be backed up: DATA
Disk group metadata to be backed up: XIFENFEI
Current alias directory path: XFF/ARCHIVELOG
Current alias directory path: XFF/ARCHIVELOG/2012_04_30
Current alias directory path: XFF/ONLINELOG
Current alias directory path: rac-cluster/OCRFILE
Current alias directory path: XFF/ARCHIVELOG/2012_05_01
Current alias directory path: XFF/CONTROLFILE
Current alias directory path: XFF/ARCHIVELOG/2012_04_13
Current alias directory path: rac-cluster/ASMPARAMETERFILE
Current alias directory path: rac-cluster
Current alias directory path: XFF
Current alias directory path: XFF/ARCHIVELOG/2012_03_03
Current alias directory path: XFF/PARAMETERFILE
Current alias directory path: XFF/DATAFILE
Current alias directory path: ASM/DATAFILE
Current alias directory path: XFF/CONTROLFILE
Current alias directory path: XFF
Current alias directory path: XFF/ONLINELOG
Current alias directory path: XFF/TEMPFILE
Current alias directory path: ASM
--备份指定磁盘组
ASMCMD> md_backup /tmp/xifenfei_data.md -G DATA
Disk group metadata to be backed up: DATA
Current alias directory path: XFF/ARCHIVELOG/2012_03_03
Current alias directory path: XFF/CONTROLFILE
Current alias directory path: XFF/ARCHIVELOG/2012_05_01
Current alias directory path: XFF/ARCHIVELOG
Current alias directory path: rac-cluster/OCRFILE
Current alias directory path: XFF/ARCHIVELOG/2012_05_24
Current alias directory path: XFF/ONLINELOG
Current alias directory path: XFF/ARCHIVELOG/2012_04_30
Current alias directory path: rac-cluster/ASMPARAMETERFILE
Current alias directory path: rac-cluster
Current alias directory path: XFF
Current alias directory path: XFF/ARCHIVELOG/2012_04_13

md_restore操作

--生产sql文件(未执行)
ASMCMD> md_restore -S  /tmp/get_dg_sql -G data /tmp/xifenfei_data.md
Current Diskgroup metadata being restored: DATA
破坏XIFENFEI磁盘组中的其中一个asm disk(/dev/oracleasm/disks/VOL3)
[root@rac1 tmp]#  dd if=/dev/zero of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 4.8629e-05 seconds, 84.2 MB/s
--尝试还原磁盘组(操作单位是磁盘组)
ASMCMD>  md_restore /tmp/xifenfei.md --silent -G xifenfei
Current Diskgroup metadata being restored: XIFENFEI
ASMCMD-9352: CREATE DISKGROUP failed
ORA-15018: diskgroup cannot be created
ORA-15033: disk /dev/oracleasm/disks/VOL4 belongs to diskgroup "XIFENFEI" (DBD ERROR: OCIStmtExecute)
--如果一个磁盘组中某个asm disk 出了问题,这种方法不能生效,甚至需要先dd 处理掉所有该磁盘组中的asm disk

总结说明
md_backup和md_restore是磁盘组级别的备份和还原,如果一个磁盘组的某个asm disk出现问题,使用这对命令解决起来还是很麻烦,甚至根本不可行(因为代价太大:要删除该磁盘组其他asm disk header,然后要重新还原所有数据文件),这样的情景下dd或者kfed的备份还是非常有必要,ASM DISK HEADER 备份与恢复.如果是一个磁盘组都损坏,需要还原磁盘组,这个时候这个命令非常的完美(至少比起dd和kfed方便很多).md_backup/md_restore和dd与kfed是互补的命令,而不是md_backup/md_restore出现使得dd和kfed在asm元数据的备份恢复上就没有用武之地.

sql_id和hash value的部分转换

从oracle 10g开始引进了sql_id，在老版本的oralce中,要表明一条sql,一般使用hash value，而在10g及其以后版本中一般建议使用sql_id,从9i的sp和10g的awr中也可以看出.对于Library Cache对象,Oracle使用MD5算法进行哈希,生成一个128位的Hash Value,其中低32位作为HASH VALUE显示,SQL_ID则取了后64位.既然hash value和sql_id之前存在着这样的关系,那么我们就可以通过函数实现两者的部分转换(因为最终取值长度不同,所以不能完全转换)
1.查询sql_id和hash value

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
  2  "www.xifenfei.com" from dual;
www.xifenfei.com
-------------------
2012-05-26 01:05:39
SQL> select sql_id,hash_value from v$sql where sql_text like
  2  'select * from dual';
SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

2.oracle自带函数转换sql_id to hash value

SQL> select dbms_utility.SQLID_TO_SQLHASH('a5ks9fhw2v9s1') hash_value FROM DUAL;
HASH_VALUE
----------
 942515969

3.自己编写函数sql_id to hash value

SQL> CREATE OR REPLACE FUNCTION sql_id_2_hash_value (sql_id VARCHAR2)
  2     RETURN NUMBER
  3  IS
  4     l_output   NUMBER := 0;
  5  BEGIN
  6         SELECT TRUNC (
  7                   MOD (
  8                      SUM (
  9                         (INSTR ('0123456789abcdfghjkmnpqrstuvwxyz',
 10                                 SUBSTR (LOWER (TRIM (sql_id)), LEVEL, 1))
 11                          - 1)
 12                         * POWER (32, LENGTH (TRIM (sql_id)) - LEVEL)),
 13                      POWER (2, 32)))
 14           INTO l_output
 15           FROM DUAL
 16     CONNECT BY LEVEL <= LENGTH (TRIM (sql_id));
 17     RETURN l_output;
 18  END;
 19  /
函数已创建。
SQL> select sql_id_2_hash_value('a5ks9fhw2v9s1') hash_value FROM DUAL;
HASH_VALUE
----------
 942515969

4.hash value 转换为部分 sql_id

SQL> CREATE OR REPLACE FUNCTION hash_value_2_sql_id (p_hash_value NUMBER)
  2     RETURN VARCHAR2
  3  IS
  4     l_output   VARCHAR2 (8) := '';
  5  BEGIN
  6     FOR i
  7        IN (    SELECT SUBSTR (
  8                          '0123456789abcdfghjkmnpqrstuvwxyz',
  9                          1
 10                          + FLOOR (
 11                               MOD (p_hash_value / (POWER (32, LEVEL - 1)), 32)),
 12                          1)
 13                          sqlidchar
 14                  FROM DUAL
 15            CONNECT BY LEVEL <= LN (p_hash_value) / LN (32)
 16              ORDER BY LEVEL DESC)
 17     LOOP
 18        l_output := l_output || i.sqlidchar;
 19     END LOOP;
 20
 21     RETURN l_output;
 22  END;
 23  /
函数已创建。
SQL> select hash_value_2_sql_id(942515969) from dual;
HASH_VALUE_2_SQL_ID(942515969)
--------------------------------------------------------
2v9s1

参考:http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/