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

bbed简单替换block测试

有朋友在我论坛中提问copy一个块，如何在前台显示其中数据,前段时间比较忙,没有及时答复该问题,今天通过试验方式进行了测试说明,本试验简单,仅是同一个数据文件中的同一个对象中的两个block进行了替换
创建测试表

SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei as select * from dba_objects;
Table created.
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where segment_name='T_XIFENFEI' AND OWNER='CHF';
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          4        176          8
         1          4        184          8
         2          4        192          8
         3          4        200          8
         4          4        208          8
         5          4        216          8
         6          4        224          8
         7          4        232          8
         8          4        240          8
         9          4        248          8
        10          4        256          8
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
        11          4        264          8
        12          4        272          8
        13          4        280          8
        14          4        288          8
        15          4        296          8
        16          4        384        128
        17          4        512        128
        18          4        640        128
        19          4        768        128
        20          4        896        128
        21          4       1024        128
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
        22          4       1152        128
        23          4       1280        128
        24          4       1408        128
25 rows selected.
SQL>  select * from (select distinct dbms_rowid.rowid_relative_fno(rowid),
  2  dbms_rowid.rowid_block_number(rowid) from t_xifenfei ORDER BY 2 )where rownum<5 ;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                  179
                                   4                                  180
                                   4                                  181
                                   4                                  182

查询file 4 block 180 数据情况

SQL> select object_id from t_xifenfei where  dbms_rowid.rowid_relative_fno(rowid)=4
    2 and dbms_rowid.rowid_block_number(rowid)=180;
 OBJECT_ID
----------
        81
        82
        83
        84
        85
        86
        87
        88
        89
        90
        91
 OBJECT_ID
----------
        92
        93
        94
        95
        96
        97
        98
        99
       100
       101
       102
 OBJECT_ID
----------
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
 OBJECT_ID
----------
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
 OBJECT_ID
----------
       125
       126
       127
       129
       128
       130
       131
       132
       133
       134
       135
 OBJECT_ID
----------
       137
       136
       138
       139
       140
       141
       142
       143
       144
       145
       146
 OBJECT_ID
----------
       147
       148
       149
       150
       151
       153
       152
       154
       155
       156
76 rows selected.

查询file 4 block 181 数据情况

SQL> select object_id from t_xifenfei where  dbms_rowid.rowid_relative_fno(rowid)=4
   2 and dbms_rowid.rowid_block_number(rowid)=181;
 OBJECT_ID
----------
       157
       158
       159
       160
       161
       162
       163
       164
       165
       166
       167
 OBJECT_ID
----------
       168
       169
       170
       171
       172
       173
       174
       175
       176
       177
       178
 OBJECT_ID
----------
       179
       180
       181
       182
       183
       184
       185
       186
       187
       188
       189
 OBJECT_ID
----------
       190
       191
       192
       193
       194
       195
       196
       197
       198
       199
       200
 OBJECT_ID
----------
       201
       202
       203
       204
       205
       206
       208
       207
       209
       210
       211
 OBJECT_ID
----------
       212
       213
       214
       215
       216
       217
       218
       219
       220
       221
       222
 OBJECT_ID
----------
       223
       224
       225
       226
       227
       228
       229
       230
       231
75 rows selected.

定位file 4 文件名

SQL> select name from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/users01.dbf

bbed 替换file 4 block 180到file 4 block 181

[oracle@oel6 ~]$ bbed filename='/u01/app/oracle/oradata/ORCL/users01.dbf' mode=edit blocksize=8192
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 6 21:17:11 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        /u01/app/oracle/oradata/ORCL/users01.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> map
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 180                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 96 bytes                     @20
 struct kdbh, 14 bytes                      @124
 struct kdbt[1], 4 bytes                    @138
 sb2 kdbr[76]                               @142
 ub1 freespace[856]                         @294
 ub1 rowdata[7038]                          @1150
 ub4 tailchk                                @8188
BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x010000b4
   ub4 bas_kcbh                             @8        0x000b258a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0eb6
   ub2 spare3_kcbh                          @18       0x0000
BBED> p kcbh block 181
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x010000b5
   ub4 bas_kcbh                             @8        0x000b258a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xa1c5
   ub2 spare3_kcbh                          @18       0x0000
BBED> copy block 180 to block 181
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 181              Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 06a20000 b4000001 8a250b00 00000204 b60e0000 01000000 252d0100 84250b00
 00000000 03003200 b0000001 ffff0000 00000000 00000000 00000000 00800000
 84250b00 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00014c00
 ffffaa00 02045803 58030000 4c00221f c21e661e 0e1eb01d 521df81c 9e1c421c
 ea1b901b 361be11a 8c1a361a da198019 2419c818 73181918 b9176017 0717ae16
 5416f815 a2154915 f0149614 3914dc13 7f131f13 c8126e12 1412ba11 61110511
 a9104f10 f20f900f 370fdc0e 710e070e a60d430d df0c850c 2a0ccf0b 640bfa0a
 9d0a400a e6098509 2e09d408 79081e08 c3076607 0907a206 3806e005 75050b05
 b0045a04 02040000 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>
BBED> p kcbh block 181
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x010000b4
   ub4 bas_kcbh                             @8        0x000b258a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0eb6
   ub2 spare3_kcbh                          @18       0x0000
BBED> p rdba_kcbh
ub4 rdba_kcbh                               @4        0x010000b4
BBED> d
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 181              Offsets:    4 to  515           Dba:0x00000000
------------------------------------------------------------------------
 b4000001 8a250b00 00000204 b60e0000 01000000 252d0100 84250b00 00000000
 03003200 b0000001 ffff0000 00000000 00000000 00000000 00800000 84250b00
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00014c00 ffffaa00
 02045803 58030000 4c00221f c21e661e 0e1eb01d 521df81c 9e1c421c ea1b901b
 361be11a 8c1a361a da198019 2419c818 73181918 b9176017 0717ae16 5416f815
 a2154915 f0149614 3914dc13 7f131f13 c8126e12 1412ba11 61110511 a9104f10
 f20f900f 370fdc0e 710e070e a60d430d df0c850c 2a0ccf0b 640bfa0a 9d0a400a
 e6098509 2e09d408 79081e08 c3076607 0907a206 3806e005 75050b05 b0045a04
 02040000 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>
BBED> set count 32
        COUNT           32
BBED> d
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 181              Offsets:    4 to   35           Dba:0x00000000
------------------------------------------------------------------------
 b4000001 8a250b00 00000204 b60e0000 01000000 252d0100 84250b00 00000000
 <32 bytes per line>
BBED> m /x b5
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 181              Offsets:    4 to   35           Dba:0x00000000
------------------------------------------------------------------------
 b5000001 8a250b00 00000204 b60e0000 01000000 252d0100 84250b00 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 0, Block 181:
current = 0x0eb7, required = 0x0eb7
BBED> p kcbh block 181
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x010000b5
   ub4 bas_kcbh                             @8        0x000b258a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0eb7
   ub2 spare3_kcbh                          @18       0x0000

验证替换后的file 4 block 181

SQL>  select object_id from t_xifenfei where  dbms_rowid.rowid_relative_fno(rowid)=4
   2  and dbms_rowid.rowid_block_number(rowid)=181;
 OBJECT_ID
----------
        81
        82
        83
        84
        85
        86
        87
        88
        89
        90
        91
 OBJECT_ID
----------
        92
        93
        94
        95
        96
        97
        98
        99
       100
       101
       102
 OBJECT_ID
----------
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
 OBJECT_ID
----------
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
 OBJECT_ID
----------
       125
       126
       127
       129
       128
       130
       131
       132
       133
       134
       135
 OBJECT_ID
----------
       137
       136
       138
       139
       140
       141
       142
       143
       144
       145
       146
 OBJECT_ID
----------
       147
       148
       149
       150
       151
       153
       152
       154
       155
       156
76 rows selected.

通过替换block 180的block到181,查询block 181和180数据相同,证明替换block成功

创建控制文件出现ORA-01565 ORA-27041 OSD-04002

oracle 在win平台上创建控制文件可能会出现ORA-01565 ORA-27041 OSD-04002错误

C:\Users\feicheng>sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期六 9月 13 16:20:38 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area  400846848 bytes
Fixed Size                  2281656 bytes
Variable Size             188747592 bytes
Database Buffers          201326592 bytes
Redo Buffers                8491008 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
 13    'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
 14    'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
 15    'D:\惜分飞\USERS01.DBF'
 16  CHARACTER SET ZHS16GBK
 17  ;
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-01565: ???? 'D:\???\USERS01.DBF' ???
ORA-27041: ??????
OSD-04002: ????????????
O/S-Error: (OS 123) ????????????????????????????????

alert日志对应错误提示为

Sat Sep 13 16:27:48 2014
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\oracle
Sat Sep 13 16:28:11 2014
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'D:\???\USERS01.DBF'
CHARACTER SET ZHS16GBK
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Errors in file D:\ORACLE\diag\rdbms\xff\xff\trace\xff_ora_8136.trc:
ORA-01565: ???? 'D:\???\USERS01.DBF' ???
ORA-27041: ??????
OSD-04002: 无法打开文件
O/S-Error: (OS 123) 文件名、目录名或卷标语法不正确。
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'D:\???\USERS01.DBF'
CHARACTER SET ZHS16GBK

ORA-01565 ORA-27041 OSD-04002的含义大致为:在创建控制文件的时候,有数据文件无法不存在.
另外在alert日志里面也可以看到,sqlplus中的”D:\惜分飞\USERS01.DBF”变为了”D:\???\USERS01.DBF”导致无法定位到数据文件,从而在创建数据文件之时出现ORA-01565 ORA-27041 OSD-04002错误.
解决放方法:
1.创建控制文件语句中不含中文

C:\Users\feicheng>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期六 9月 13 16:32:09 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
已连接到空闲例程。
SQL> STARTUP NOMOUNT
ORACLE 例程已经启动。
Total System Global Area  400846848 bytes
Fixed Size                  2281656 bytes
Variable Size             188747592 bytes
Database Buffers          201326592 bytes
Redo Buffers                8491008 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
 13    'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
 14    'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
 15    'D:\xifenfei\USERS01.DBF'
 16  CHARACTER SET ZHS16GBK
 17  ;
控制文件已创建。

2.设置nls_lang为american_america.ZHS16GBK

C:\Users\feicheng>set NLS_LANG=american_america.ZHS16GBK
C:\Users\feicheng>sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 13 16:29:04 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
 13    'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
 14    'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
 15    'D:\惜分飞\USERS01.DBF'
 16  CHARACTER SET ZHS16GBK
 17  ;
Control file created.

此时alert日志提示

Sat Sep 13 16:29:22 2014
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'D:\惜分飞\USERS01.DBF'
CHARACTER SET ZHS16GBK
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Sat Sep 13 16:29:25 2014
Successful mount of redo thread 1, with mount id 3507744098
Completed: CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'D:\惜分飞\USERS01.DBF'
CHARACTER SET ZHS16GBK

通过此实验简单说明:在oracle使用该过程中,尽可能少用中文路径或者文件名

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets—201407

Patchsets
^l12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)	17694377
11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	13390677
11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER)	10404530
11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)	10098816
11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER)	6890831
10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER)	8202632
^d10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	6810189
^e10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	5337014
10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)	4547817
10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER)	4505133
10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER)	4163362
10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	3761843
9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER)	4547809
9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER)	4163445
9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER)	3948480
9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0)	3501955
9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER)	3095277
9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	2761332
9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)	2632931
9.0.1.5 (9.0.1.5 PATCHSET)	3301544
9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER)	2517300
9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER)	2271678
8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER)	2376472
8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER)	2189751
8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER)	1909158

PSU, SPU(CPU), Bundle Patches

12.1.0.1
Description	PSU	GI PSU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
JUL2014	18522516 (12.1.0.1.4)	^j18705901 / ^k18705972(12.1.0.1.4)	19062327 (12.1.0.1.11)
APR2014	18031528 (12.1.0.1.3)	^j18139660 / ^k18413105 (12.1.0.1.3)	18448604 (12.1.0.1.7)
JAN2014	17552800 (12.1.0.1.2)	17735306 (12.1.0.1.2)	17977915 (12.1.0.1.3)
OCT2013	17027533 (12.1.0.1.1)	17272829 (12.1.0.1.1)	17363796 (12.1.0.1.1)	17363795 (12.1.0.1.1)

11.2.0.4
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch (Windows 32bit & 64bit)
JUL2014	18522509 (11.2.0.4.3)	18681862	18706472 (11.2.0.4.3)	18842982
APR2014	18031668 (11.2.0.4.2)	18139690	18139609 (11.2.0.4.2)	18296644
JAN2014	17478514 (11.2.0.4.1)	17551709	N/A	17987366

11.2.0.3
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch(Windows^64bit)	Bundle Patch(Windows^32bit)
JUL2014	18522512 (11.2.0.3.11)	18681866	18706488 (11.2.0.3.11)	18940194	18940193
APR2014	18031683 (11.2.0.3.10)	18139695	18139678 (11.2.0.3.10)	18372244	18372243
JAN2014	17540582 (11.2.0.3.9)	17478415	17735354 (11.2.0.3.9)	18075406	17906981
OCT2013	16902043 (11.2.0.3.8)	17082364	17272731 (11.2.0.3.8)	17363850	17363844
JUL2013	16619892 (11.2.0.3.7)	16742095	16742216 (11.2.0.3.7)	16803775	16803774
APR2013	16056266 (11.2.0.3.6)	16294378	16083653 (11.2.0.3.6)	16345834	16345833
JAN2013	14727310 (11.2.0.3.5)	14841409	14727347 (11.2.0.3.5)	16042648	16042647
OCT2012	14275605 (11.2.0.3.4)	14390252	14275572 (11.2.0.3.4)	14613223	14613222
JUL2012	13923374 (11.2.0.3.3)	14038787	13919095 (11.2.0.3.3)	14223718	14223717
APR2012	13696216 (11.2.0.3.2)	13632717	13696251 (11.2.0.3.2)	13885389	13885388
JAN2012	13343438 (11.2.0.3.1)	13466801	13348650 (11.2.0.3.1)	13413168	13413167

11.2.0.2
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch(Windows^64bit)	Bundle Patch(Windows^32bit)
^aOCT2013	17082367 (11.2.0.2.12)	17082375	17272753 (11.2.0.2.12)	17363838	17363837
JUL2013	16619893 (11.2.0.2.11)	16742100	16742320 (11.2.0.2.11)	16345852	16345851
APR2013	16056267 (11.2.0.2.10)	16294412	16166868 (11.2.0.2.10)	16345846	16345845
JAN2013	14727315 (11.2.0.2.9)	14841437	14841385 (11.2.0.2.9)	16100399	16100398
OCT2012	14275621 (11.2.0.2.8)	14390377	14390437 (11.2.0.2.8)	14672268	14672267
JUL2012	13923804 (11.2.0.2.7)	14038791	14192201 (11.2.0.2.7)	14134043	14134042
APR2012	13696224 (11.2.0.2.6)	13632725	13696242 (11.2.0.2.6)	13697074	13697073
JAN2012	13343424 (11.2.0.2.5)	13343244	13653086 (11.2.0.2.5)	13413155	13413154
OCT2011	12827726 (11.2.0.2.4)	12828071	12827731 (11.2.0.2.4)	13038788	13038787
JUL2011	12419331 (11.2.0.2.3)	12419321	12419353 (11.2.0.2.3)	12714463	12714462
APR2011	11724916 (11.2.0.2.2)	11724984	12311357 (11.2.0.2.2)	11896292	11896290
JAN2011	10248523 (11.2.0.2.1)	N/A	N/A	10432053	10432052


11.2.0.1
Description	PSU	CPU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^aJUL2011	12419378 (11.2.0.1.6)	12419278	12429529	12429528
APR2011	11724930 (11.2.0.1.5)	11724991	11731176	11883240
JAN2011	10248516 (11.2.0.1.4)	10249532	10432045	10432044
OCT2010	9952216 (11.2.0.1.3)	9952260	10100101	10100100
JUL2010	9654983 (11.2.0.1.2)	9655013	9736865	9736864
APR2010	9352237 (11.2.0.1.1)	9369797	N/A	N/A


11.1.0.7
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^{^bJUL2014}	18522513 (11.1.0.7.20)	18681875	18944208	18944207
^bAPR2014	18031726 (11.1.0.7.19)	18139703	18372258	18372257
^{^{^bJAN2014}}	17465583 (11.1.0.7.18)	17551415	17906936	17906935
^bOCT2013	17082366 (11.1.0.7.17)	17082374	17363760	17363759
^{^bJUL2013}	16619896 (11.1.0.7.16)	16742110	16803788	16803787
^bAPR2013	16056268 (11.1.0.7.15)	16308394	16345862	16345861
^bJAN2013	14739378 (11.1.0.7.14)	14841452	15848067	15848066
^bOCT2012	14275623 (11.1.0.7.13)	14390384	14672313	14672312
JUL2012	13923474 (11.1.0.7.12)	14038803	14109868	14109867
APR2012	13621679 (11.1.0.7.11)	13632731	13715810	13715809
JAN2012	13343461 (11.1.0.7.10)	13343453	13460956	13460955
OCT2011	12827740 (11.1.0.7.9)	12828097	12914916	12914915
JUL2011	12419384 (11.1.0.7.8)	12419265	12695278	12695277
APR2011	11724936 (11.1.0.7.7)	11724999	11741170	11741169
JAN2011	10248531 (11.1.0.7.6)	10249534	10350788	10350787
OCT2010	9952228 (11.1.0.7.5)	9952269	9773825	9773817
JUL2010	9654987 (11.1.0.7.4)	9655014	9869912	9869911
APR2010	9352179 (11.1.0.7.3)	9369783	9392335	9392331
JAN2010	9209238 (11.1.0.7.2)	9114072	9166861	9166858
OCT2009	8833297 (11.1.0.7.1)	8836375	8928977	8928976
JUL2009	N/A	8534338	8553515	8553512
APR2009	N/A	8290478	8343070	8343061


11.1.0.6
Description	CPU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^aJUL2009	8534378	8563155	8563154
APR2009	8290402	8333657	8333655
JAN2009	7592335	7631981	7631980
OCT2008	7375639	7378393	7378392
JUL2008	7150417	7210197	7210195
APR2008	6864063	6867180	6867178


10.2.0.5
Description	PSU	SPU(CPU)	Bundle Patch(Windows^64bit)	Bundle Patch(Windows^32bit)	Bundle Patch(Windows^Itanium)
^abJUL2013	16619894 (10.2.0.5.12)	16742123	16803782	16803780	16803781
^bAPR2013	16056270 (10.2.0.5.11)	16270946	16345857	16345855	16345856
^bJAN2013	14727319 (10.2.0.5.10)	14841459	15848062	15848060	15848061
^bOCT2012	14275629 (10.2.0.5.9)	14390396	14553358	14553356	14553357
^bJUL2012	13923855 (10.2.0.5.8)	14038805	14134053	14134051	14134052
^bAPR2012	13632743 (10.2.0.5.7)	13632738	13654815	13654814	13870404
JAN2012	13343471 (10.2.0.5.6)	13343467	^b13460968	^b13460967	N/A
^bOCT2011	12827745 (10.2.0.5.5)	12828105	^c12914913	12914911	N/A
JUL2011	12419392 (10.2.0.5.4)	12419258	12429524	12429523	N/A
APR2011	11724962 (10.2.0.5.3)	11725006	12328269	12328268	N/A
JAN2011	10248542 (10.2.0.5.2)	10249537	10352673	10352672	N/A
OCT2010	9952230 (10.2.0.5.1)	9952270	10099855	10058290	N/A


10.2.0.4
Description	PSU	SPU(CPU)	Bundle Patch(Windows^32bit)	Bundle Patch(Windows^64bit)	Bundle Patch(Windows^Itanium)
^bgJUL2013	16619897 (10.2.0.4.17)	16742253	N/A	N/A	N/A
^bgAPR2013	16056269 (10.2.0.4.16)	16270931	N/A	N/A	N/A
^bgJAN2013	14736542 (10.2.0.4.15)	14841471	N/A	N/A	N/A
^bgOCT2012	14275630 (10.2.0.4.14)	14390410	N/A	N/A	N/A
^bgJUL2012	13923851 (10.2.0.4.13)	14038814	N/A	N/A	N/A
^abAPR2012	12879933 (10.2.0.4.12)	12879926	13928775	13928776	N/A
JAN2012	12879929 (10.2.0.4.11)	12879912	^b13654060	N/A	N/A
^bOCT2011	12827778 (10.2.0.4.10)	12828112	12914908	12914910	12914909
JUL2011	12419397 (10.2.0.4.9)	12419249	12429519	12429521	12429520
APR2011	11724977 (10.2.0.4.8)	11725015	12328501	12328503	12328502
JAN2011	10248636 (10.2.0.4.7)	10249540	10349197	10349200	10349198
OCT2010	9952234 (10.2.0.4.6)	9952272	10084980	10084982	10084981
JUL2010	9654991 (10.2.0.4.5)	9655017	9777076	9777078	9777077
APR2010	9352164 (10.2.0.4.4)	9352191	9393548	9393550	9393549
JAN2010	9119284 (10.2.0.4.3)	9119226	9169457	9169460	9169458
OCT2009	8833280 (10.2.0.4.2)	8836308	8880857	8880861	8880858
JUL2009	8576156 (10.2.0.4.1)	8534387	8559466	8559467	8541782
APR2009	N/A	8290506	8307237	8307238	8333678
JAN2009	N/A	7592346	7584866	7584867	N/A
OCT2008	N/A	7375644	7386320	7386321	N/A
JUL2008	N/A	7150470	7218676	7218677	N/A


10.2.0.3
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)	Bundle Patch (Windows^64bit)
^aJAN2009	7592354	7631956	7631958	7631957
OCT2008	7369190	7353782	7353784	7353785
JUL2008	7150622	7252496	7252497	7252498
APR2008	6864068	6867054	6867055	6867056
JAN2008	6646853	6637237	6637238	6637239
OCT2007	6394981	6430171	6430173	6430174
JUL2007	6079591	6116131	6038242	6116139
APR2007	5901891	5948242	5916262	5948243
JAN2007	5881721	5846376	5846377	5846378


10.2.0.2
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^Itanium)
ⁱJAN2009	7592355	N/A	N/A	N/A
^hOCT2008	7375660	N/A	N/A	N/A
^hJUL2008	7154083	N/A	N/A	N/A
^hAPR2008	6864071	N/A	N/A	N/A
^aJAN2008	6646850	N/A	N/A	N/A
^fOCT2007	6394997	6397028	6397030	6397029
JUL2007	6079588	6013105	6013121	6013118
APR2007	5901881	5912173	5912179	5912176
JAN2007	5689957	5716143	5699839	5699824
OCT2006	5490848	5502226	5500921	5500894
JUL2006	5225799	5251025	5251028	5251026
APR2006	5079037	5140461	5140567	5140508


10.2.0.1
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^Itanium)
APR2007	5901880	N/A	N/A	N/A
JAN2007	5689937	5695784	5695786	5695785
OCT2006	5490846	5500927	5500954	5500951
JUL2006	5225798	5239698	5239701	5239699
APR2006	5049080	5059238	5059261	5059251
JAN2006	4751931	4751539	4770480	4751549


10.1.0.5
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JAN2012	13343482	13413002	13413003
OCT2011	12828135	12914905	12914906
JUL2011	12419228	12429517	12429518
APR2011	11725035	11731119	11731120
JAN2011	N/A	N/A	N/A
OCT2010	9952279	10089559	10089560
JUL2010	9655023	9683651	9683652
APR2010	9352208	9390288	9390289
JAN2010	9119261	9187104	9187105
OCT2009	8836540	8785211	8785212
JUL2009	8534394	8656224	8656226
APR2009	8290534	8300356	8300360
JAN2009	7592360	7486619	7586049
OCT2008	7375686	7367493	7367494
JUL2008	7154097	7047034	7047037
APR2008	6864078	6867107	6867108
JAN2008	6647005	6637274	6637275
OCT2007	6395024	6408393	6408394
JUL2007	6079585	6115804	6115818
APR2007	5901877	5907304	5907305
JAN2007	5689908	5716295	5634747
OCT2006	5490845	5500883	5500885
JUL2006	5225797	5251148	5251140
APR2006	5049074	5057606	5057609
JAN2006	4751932	4882231	4882236


10.1.0.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
APR2007	5901876	5909871	5909879
JAN2007	5689894	5695771	5695772
OCT2006	5490844	5500878	5500880
JUL2006	5225796	5239736	5239737
APR2006	5049067	5059200	5059227
JAN2006	4751928	4751259	4745040
OCT2005	4567866	4579182	4579188
JUL2005	4392423	4440706	4404600
APR2005	4210374	4287619	4287611


10.1.0.3
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JAN2007	5923277	N/A	N/A
OCT2006	5566825	N/A	N/A
JUL2006	5435164	N/A	N/A
APR2006	5158022	N/A	N/A
JAN2006	4751926	4741077	4741084
OCT2005	4567863	4567518	4567523
JUL2005	4392409	4389012	4389014
APR2005	4193286	4269715	4158888
JAN2005	4003062	4074232	3990812


10.1.0.2
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
APR2005	4193293	4181849	4213305
JUL2005	4400766	4388944	4388948
JAN2005	4003051	4104364	4083038


9.2.0.8
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JUL2010	9655027	9683644	9683645
APR2010	9352224	9390286	N/A
JAN2010	9119275	9187106	N/A
OCT2009	8836758	8785185	8785186
JUL2009	8534403	8427417	8427418
APR2009	8290549	8300340	8300346
JAN2009	7592365	7703210	7703212
OCT2008	7375695	7394394	7394402
JUL2008	7154111	7047026	7047029
APR2008	6864082	6867138	6867139
JAN2008	6646842	6637265	6637266
OCT2007	6395038	6417013	6417014
JUL2007	6079582	6130293	6130295
APR2007	5901875	5916268	5916275
JAN2007	N/A	N/A	N/A
OCT2006	5490859	5652380	5639519


9.2.0.7
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JUL2007	6079579	6146759	6146748
APR2007	5901872	5907274	5907275
JAN2007	5689875	5654905	5654909
OCT2006	5490841	5500873	5500874
JUL2006	5225794	5250980	5250981
APR2006	5049060	5064365	5064364
JAN2006	4751923	4751528	4741074
OCT2005	4567854	4579590	4579599
JUL2005	4547566	N/A	N/A


9.2.0.6
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
OCT2006	5490840	5500865	5500871
JUL2006	5225793	5239794	5239793
APR2006	5049051	5059614	5059615
JAN2006	4751921	4751261	4751262
OCT2005	4567846	4579093	4579097
JUL2005	4392392	4445852	4401917
APR2005	4193295	4269928	4213298


9.2.0.5
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
OCT2006	5689708	N/A	N/A
JUL2006	5435138	N/A	N/A
APR2006	5219762	N/A	N/A
OCT2005	4560421	N/A	N/A
JUL2005	4392256	4387563	4391819
APR2005	4193299	4195791	4214192
JAN2005	4003006	4104374	3990809


9.2.0.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JAN2005	4002994	4104369	4083202


8.1.7.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)
JAN2007	5689799	5686514
OCT2006	5490835	5496067
JUL2006	5225788	5236412
APR2006	5045247	5057601
JAN2006	4751906	4751570
OCT2005	4560405	4554818
JUL2005	4392446	4437058
APR2005	4193312	4180163
JAN2005	4002909	3921893

参考文档：Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)

ORACEL坏查询对象批量脚本

查询坏块

SQL> set lines 120
SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         3      35418          1                  0 FRACTURED
         3      61344          1                  0 FRACTURED
         3      31065          1                  0 CORRUPT
         3      36673          1                  0 CORRUPT
         3      36721          1                  0 CORRUPT
         3      42881          1                  0 CORRUPT
         1      66738          1                  0 CORRUPT
         3      36329          1                  0 CORRUPT
         3      36617          1                  0 CORRUPT
         3      32404          1                  0 FRACTURED
         3      36281          1                  0 FRACTURED
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         3      36625          1                  0 FRACTURED
         1      39041          1                  0 CORRUPT
         3      36713          1                  0 CORRUPT
        10      69927          1                  0 FRACTURED
        26      94244          1                  0 CORRUPT
已选择16行。

查询坏块对应对象

SQL> set pagesize 2000
SQL>          set linesize 250
SQL>      SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2       , greatest(e.block_id, c.block#) corr_start_block#
  3       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6       , null description
  7    FROM dba_extents e, v$database_block_corruption c
  8   WHERE e.file_id = c.file#
  9     AND e.block_id <= c.block# + c.blocks - 1
 10     AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13       , header_block corr_start_block#
 14       , header_block corr_end_block#
 15       , 1 blocks_corrupted
 16       , 'Segment Header' description
 17    FROM dba_segments s, v$database_block_corruption c
 18   WHERE s.header_file = c.file#
 19     AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22       , greatest(f.block_id, c.block#) corr_start_block#
 23       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26       , 'Free Block' description
 27    FROM dba_free_space f, v$database_block_corruption c
 28   WHERE f.file_id = c.file#
 29     AND f.block_id <= c.block# + c.blocks - 1
 30     AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, corr_start_block#;
OWNER            SEGMENT_TYPE       SEGMENT_NAME                 PARTITION_NAME                      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------------- ----------------- ----------------------- ------------------------------- ------ ----------------- --------------- ---------------- --------------
SYS              TABLE              OBJ$                                                        1             39041           39041                1
                                                                                                1             66738           66738                1 Free Block
SYSMAN           INDEX              MGMT_METRICS_1HOUR_PK                                       3             31065           31065                1
SYS              TABLE              WRH$_SQL_BIND_METADATA                                      3             32404           32404                1
SYS              TABLE              WRH$_BG_EVENT_SUMMARY                                       3             35418           35418                1
SYS              INDEX PARTITION    WRH$_FILESTATXS_PK      WRH$_FILEST_1232289473_41482        3             36281           36281                1
SYS              TABLE PARTITION    WRH$_SYSTEM_EVENT       WRH$_SYSTEM_1232289473_41482        3             36329           36329                1
SYS              TABLE PARTITION    WRH$_SGASTAT            WRH$_SGASTA_1232289473_41482        3             36617           36617                1
SYS              INDEX PARTITION    WRH$_SGASTAT_U          WRH$_SGASTA_1232289473_41482        3             36625           36625                1
SYS              INDEX PARTITION    WRH$_PARAMETER_PK       WRH$_PARAME_1232289473_41482        3             36673           36673                1
SYS              TABLE PARTITION    WRH$_SERVICE_STAT       WRH$_SERVIC_1232289473_41482        3             36713           36713                1
SYS              INDEX PARTITION    WRH$_SERVICE_STAT_PK    WRH$_SERVIC_1232289473_41482        3             36721           36721                1
SYS              TABLE PARTITION    WRH$_LATCH              WRH$_LATCH_1232289473_41482         3             42881           42881                1
SYS              TABLE              WRI$_ADV_ACTIONS                                            3             61344           61344                1
EXAM             TABLE              EXAM_ITEMS_OLD                                             10             69927           69927                1
CPR              TABLE              NEED_MONITOR                                               26             94244           94244                1
已选择16行。
SQL>
SQL> spool off

在Chrome高版本中配置使用AWR-Format for Chrome插件

问题背景
从Chrome 21版本开始，不再直接支持非Chrome官网下载，导致AWR-Format无法正常使用，按照网络上描述：
1. 使用 -enable-easy-off-store-extension-install 不行
2. 直接使用开发者模式安装也不行
不能解决问题，通过查资料和分析语句，解决该问题，和大家分享

使用方法
下载地址：新Chrome AWR-Format
处理步骤：解压该文件，启动chrome 开发者模式，增加该目录，关闭开发者模式

具体参考
支持新Chrome AWR-Format

记录一次system表空间坏块(ORA-01578)数据库恢复

半夜朋友打来求救电话,说xx医院his系统因为存储异常导致system坏块无法正常启动,因为是win平台无法使用bbed,无法修复system 坏块,请求技术支持
dbv检查system文件报坏块

对应具体地址为:file 1 block 39041和66738

判断控制文件异常
通过数据库恢复检查脚本(Oracle Database Recovery Check)脚本检测数据库发现控制文件明显异常(checkpoint scn)

尝试恢复数据库

因此对该库进行了不完全恢复,然后尝试resetlogs打开数据库,数据库报ORA-600 2662错误

Fri Aug 29 02:35:08 2014
alter database open resetlogs
Fri Aug 29 02:35:11 2014
RESETLOGS after complete recovery through change 451371288
Resetting resetlogs activation ID 1232269761 (0x4972f1c1)
Fri Aug 29 02:35:15 2014
Setting recovery target incarnation to 3
Fri Aug 29 02:35:15 2014
Assigning activation ID 1384652231 (0x52881dc7)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=17, OS id=1084
Fri Aug 29 02:35:15 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=18, OS id=2836
Fri Aug 29 02:35:15 2014
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: Z:\ORACLE\PRODUCT\10.2.0\ORCL\REDO01.LOG
Successful open of redo thread 1
Fri Aug 29 02:35:15 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Aug 29 02:35:15 2014
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Fri Aug 29 02:35:15 2014
ARC0: Becoming the heartbeat ARCH
Fri Aug 29 02:35:15 2014
SMON: enabling cache recovery
Fri Aug 29 02:35:16 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4824.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [451371311], [0], [451374534], [8388977], [], []
Fri Aug 29 02:35:16 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4824.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [451371311], [0], [451374534], [8388977], [], []
Fri Aug 29 02:35:16 2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Fri Aug 29 02:35:17 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2928.trc:
ORA-00600: ??????, ??: [], [], [], [], [], [], [], []
Instance terminated by USER, pid = 4824
ORA-1092 signalled during: alter database open resetlogs...

ORA-600 2662 该错误解决思路很明显,推进scn,数据库报ORA-01578

Fri Aug 29 02:42:47 2014
SMON: enabling cache recovery
Fri Aug 29 02:42:47 2014
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Fri Aug 29 02:42:49 2014
SMON: enabling tx recovery
Fri Aug 29 02:42:49 2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Aug 29 02:42:50 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_4804.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 39041)
ORA-01110: 数据文件 1: 'Z:\ORACLE\PRODUCT\10.2.0\ORCL\SYSTEM01.DBF'
Fri Aug 29 02:42:50 2014
LOGSTDBY: Validating controlfile with logical metadata
Fri Aug 29 02:42:51 2014
LOGSTDBY: Validation complete
ORA-604 signalled during: alter database open...

使用event跳过坏块，启动数据库成功

Fri Aug 29 02:48:59 2014
SMON: enabling cache recovery
Fri Aug 29 02:49:00 2014
Successfully onlined Undo Tablespace 1.
Fri Aug 29 02:49:00 2014
SMON: enabling tx recovery
Fri Aug 29 02:49:00 2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=34, OS id=3096
Fri Aug 29 02:49:01 2014
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Aug 29 02:49:01 2014
Completed: alter database open

查询坏块对象

因为这些对象均不是核心对象,直接进行truncate然后插入老数据

后续还有大量错误修复

ORA-12012: error on auto execute of job 1
ORA-08102: index key not found, obj# 239, file 1, block 1674 (2)
ORA-00600: 内部错误代码, 参数: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []
ORA-00600: internal error code, arguments: [6749], [3], [12606796], [173], [], [], [], []
ORA-00600: 内部错误代码, 参数: [13013], [52898], [52895], [38288618], [44], [38288618], [17], []
ORA-00600: 内部错误代码, 参数: [13013], [5001], [52895], [38286476], [5], [38286476], [17], []

再次说明,很多时候数据库恢复不要看成多神秘,就是几个参数搞定,更加不要神化有坏块就bbed修复,当然非常极端,使用N中工具,N种尝试的也存在.做好备份重于一切

ORACLE 8.1.7 数据库ORA-600 4000故障恢复

在数据库的恢复过程中遇到ORA-600 4000错误挺多的，但是在oracle 8i(8.1.7)中遇到此类问题，还是第一次，做个记忆，供参考：
数据库故障起因：因为存储异常，导致当前redo损坏，并_allow_resetlogs_corruption参数尝试打开数据库

Media Recovery Log
kcrrga: Warning.  Log sequence in archive filename wrapped
to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.
Old log archive with same name might be overwritten.
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup cont...
Wed Aug 20 23:01:43 2014
ALTER DATABASE RECOVER    CANCEL
Media Recovery Cancelled
Completed: ALTER DATABASE RECOVER    CANCEL
Wed Aug 20 23:01:50 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 262618871
Wed Aug 20 23:01:50 2014
Thread 1 opened at log sequence 1
  Current log# 3 seq# 1 mem# 0: F:\REDO01.LOG
Successful open of redo thread 1.
Wed Aug 20 23:01:50 2014
SMON: enabling cache recovery
Wed Aug 20 23:01:50 2014
Errors in file C:\oracle\admin\YCFD\udump\ORA00320.TRC:
ORA-00600: ??????????: [4000], [3], [], [], [], [], [], []
SMON: disabling cache recovery
Wed Aug 20 23:01:51 2014
ORA-704 signalled during: alter database open resetlogs

数据库遭遇ORA-600 4000错误，数据库无法打开，分析对应trace日志

Dump file C:\oracle\admin\YCFD\udump\ORA00320.TRC
Wed Aug 20 23:01:50 2014
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: ycfd
Redo thread mounted by this instance: 1
Oracle process number: 8
Windows thread id: 320, image: ORACLE.EXE
*** SESSION ID:(7.1) 2014-08-20 23:01:50.838
*** 2014-08-20 23:01:50.838
ksedmp: internal or fatal error
ORA-00600: ??????????: [4000], [3], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
----- Call Stack Trace -----

这里可以看出来，是因为数据库在启动之时需要执行select ctime, mtime, stime from obj$ where obj# = :1语句，但是由于每种原因出现ORA-600 4000导致数据库无法正常启动，继续分析日志

lock header dump:  0x0040003e
 Object id on Block? Y
 seg/obj: 0x12  csc: 0x00.fb5c5c5  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   xid:  0x0003.012.0002ae94    uba: 0x00801f5b.5389.11  --U-    1  fsc 0x0000.0fb5c5c6
SQL> select checkpoint_change# from v$database;
263570122

此处比较明显,通过xid可以知道三号回滚段中对应一个事务出现问题：
1. 该block为file 1 bock 62，object_id为 18（obj$)上有一个事务
2. 该事务的scn为263,570,886>database scn(263570122)导致该故障发生
3. 当数据库访问到file 1 block 62的时候，发现有一个事务，而该事务的scn大于数据库scn，从而出现ORA-600[4000]
解决该问题有几种方法
1. 修改block 62，人工提交该事务
2. 修改数据库scn，让数据库scn大于itl scn
解决block 62 事务问题后出现如下错误

Wed Aug 20 23:03:55 2014
SMON: enabling cache recovery
Wed Aug 20 23:03:55 2014
Dictionary check beginning
Dictionary check complete
Wed Aug 20 23:03:55 2014
SMON: enabling tx recovery
Wed Aug 20 23:03:56 2014
Errors in file C:\oracle\admin\YCFD\bdump\ycfdSMON.TRC:
ORA-00600: internal error code, arguments: [4193], [21173], [21181], [], [], [], [], []
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: F:\REDO03.LOG
SMON: terminating instance due to error 600
Instance terminated by SMON, pid = 2468

数据库出现ORA-600 4193,这个是常见错误,因为redo记录和undo记录不匹配导致,可以直接使用_corrupted_rollback_segments/_offline_rollback_segments屏蔽回滚段跳过

Wed Aug 20 23:08:10 2014
SMON: enabling cache recovery
SMON: enabling tx recovery
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
Wed Aug 20 23:08:15 2014
Completed: alter database open

其他类似文章：
ORA-600[4194]/[4193]解决
 通过bbed解决ORA-600 4000案例
 通过bbed解决ORA-00600[4000]案例
 记录一次ORA-600 4000数据库故障恢复

一起ORA-600 3020故障恢复的大体思路

recover database 报ORA-600 3020

Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
Tue Aug 19 19:37:29 2014
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_pr0s_4296.trc  (incident=39403):
ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
ORA-10560: block type 'KTU SMU HEADER BLOCK'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39403\yygdb_pr0s_4296_i39403.trc
ORA-00600: internal error code, arguments: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39147):
ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\YYGD
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39147\yygdb_ora_12460_i39147.trc
Tue Aug 19 19:37:31 2014
Trace dumping is performing id=[cdmp_20140819193731]
Tue Aug 19 19:37:32 2014
Recovery Slave PR0S previously exited with an exception
Shutting down recovery slaves due to error 10877
Media Recovery failed with error 10877
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

使用allow 1 corruption跳3020错误继续恢复

Tue Aug 19 19:38:53 2014
ALTER DATABASE RECOVER  database allow 1 corruption
Media Recovery Start
Fast Parallel Media Recovery enabled
 ALLOW CORRUPTION option must use serial recovery
Warning: Datafile 10 (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF) is offline during full
database recovery and will not be recovered
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
CORRUPTING BLOCK 240 OF FILE 3 AND CONTINUING RECOVERY
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc:
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
ORA-10560: block type 'KTU SMU HEADER BLOCK'
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39148):
ORA-00600: 内部错误代码, 参数: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39148\yygdb_ora_12460_i39148.trc
Media Recovery failed with error 600
ORA-283 signalled during: ALTER DATABASE RECOVER  database allow 1 corruption  ...
Tue Aug 19 19:38:56 2014
Trace dumping is performing id=[cdmp_20140819193856]
Tue Aug 19 19:38:59 2014
Sweep Incident[39148]: completed
Tue Aug 19 19:39:05 2014
ALTER DATABASE RECOVER  database allow 1 corruption
Media Recovery Start
Fast Parallel Media Recovery enabled
 ALLOW CORRUPTION option must use serial recovery
Warning: Datafile 10 (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF) is offline during full
database recovery and will not be recovered
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
CORRUPTING BLOCK 90586 OF FILE 2 AND CONTINUING RECOVERY
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc:
ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
Completed: ALTER DATABASE RECOVER  database allow 1 corruption

继续open数据库报ORA-01578错误,数据库无法open

Thread 1 opened at log sequence 5730
  Current log# 3 seq# 5730 mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Aug 19 19:39:34 2014
SMON: enabling cache recovery
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39149):
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39150):
ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54
ORA-00312: 联机日志 1 线程 1: 'E:\ORACLE\ORADATA\YYGDB\REDO01.LOG'
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39150\yygdb_ora_12460_i39150.trc
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc:
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54
ORA-00312: 联机日志 1 线程 1: 'E:\ORACLE\ORADATA\YYGDB\REDO01.LOG'
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39151):
Error 1578 happened during db open, shutting down database
USER (ospid: 12460): terminating the instance due to error 1578
Tue Aug 19 19:39:41 2014
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw3_18508.trc  (incident=38659):
ORA-01578: ORACLE data block corrupted (file # , block # )
Tue Aug 19 19:39:41 2014
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw5_12160.trc  (incident=38675):
ORA-01578: ORACLE data block corrupted (file # , block # )
Tue Aug 19 19:39:42 2014
Instance terminated by USER, pid = 12460
ORA-1092 signalled during: alter database open...
ORA-1092 : opiodr aborting process unknown ospid (5084_12460)

由于undo 表空间有坏块，导致数据库open失败,尝试修改undo_management= “MANUAL”，继续启动数据库

Tue Aug 19 19:50:06 2014
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 16 processes
Started redo scan
Completed redo scan
 3 redo blocks read, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 5731, block 2, scn 101497289
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5731 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO01.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 5731, block 5, scn 101517294
 0 data blocks read, 0 data blocks written, 3 redo blocks read
Tue Aug 19 19:50:08 2014
Thread 1 advanced to log sequence 5732 (thread open)
Thread 1 opened at log sequence 5732
  Current log# 2 seq# 5732 mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Aug 19 19:50:08 2014
SMON: enabling cache recovery
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #3 is offline, but is part of an online tablespace.
data file 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
File #10 is offline, but is part of an online tablespace.
data file 10: 'D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF'
File #11 is offline, but is part of an online tablespace.
data file 11: 'D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT.DBF'
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan : on 4 X 8 NUMA system
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
Hex dump of (file 1, block 7065) in trace file
d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc
Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00401b99
 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xac3b0601
 check value in block header: 0x2e13
 computed block checksum: 0xa4ac
Reread of rdba: 0x00401b99 (file 1, block 7065) found same corrupted data
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42814):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_ora_14296_i42814.trc
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42815):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42815\yygdb_ora_14296_i42815.trc
Tue Aug 19 19:50:12 2014
Trace dumping is performing id=[cdmp_20140819195012]
Tue Aug 19 19:50:12 2014
Sweep Incident[42814]: completed
Hex dump of (file 1, block 7065) in trace file
d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_m000_11592_i42814_a.trc
Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
Fractured block found during validation
Data in bad block:
 type: 6 format: 2 rdba: 0x00401b99
 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xac3b0601
 check value in block header: 0x2e13
 computed block checksum: 0xa4ac
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Hex dump of (file 1, block 7065) in trace file
d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc
Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00401b99
 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xac3b0601
 check value in block header: 0x2e13
 computed block checksum: 0xa4ac
Reread of rdba: 0x00401b99 (file 1, block 7065) found same corrupted data
Corrupt Block Found
         TSN = 0, TSNAME = SYSTEM
         RFN = 1, BLK = 7065, RDBA = 4201369
         OBJN = 1164, OBJD = 1164, OBJECT = SYS_FBA_BARRIERSCN, SUBOBJECT =
         SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42816):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42816\yygdb_ora_14296_i42816.trc
Trace dumping is performing id=[cdmp_20140819195014]
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42817):
Starting background process FBDA
Tue Aug 19 19:50:18 2014
FBDA started with pid=86, OS id=17700
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc  (incident=42910):
ORA-01578: ORACLE data block corrupted (file # 1, block # 7065)
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
Trace dumping is performing id=[cdmp_20140819195018]
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc  (incident=42911):
ORA-01578: ORACLE data block corrupted (file # 1, block # 7065)
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
ORA-604 signalled during: alter database open...

数据库不完全open成功，报了604错误，通过分析undo$，直接使用_offline_rollback_segments屏蔽了status$=5的回滚段,数据库open正常,因为system有大量坏块,幸运的是使用exp导出来几个业务用户的表数据全部OK.
数据库备份重于一切,别寄希望数据库非常规恢复

又一起存储故障导致ORA-00333 ORA-00312恢复

数据库启动报ORA-00333 ORA-00312错误，无法正常open数据库

Thu Aug 07 10:42:03  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\bdump\szcg_arc0_4724.trc:
ORA-00333: redo log read error block 63489 count 2048
ORA-00312: online log 2 thread 1: 'F:\ORADATA\SZCG\REDO02.LOG'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1) 函数不正确。
Thu Aug 07 10:42:03  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\bdump\szcg_arc0_4724.trc:
ORA-00333: redo log read error block 63489 count 2048
Thu Aug 07 10:42:03  2014
ARC0: All Archive destinations made inactive due to error 333
Thu Aug 07 10:42:03  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_1856.trc:
ORA-00449: 后台进程 'LGWR' 因错误 340 异常终止
ORA-00340: 处理联机日志  (用于线程 ) 时出现 I/O 错误
Thu Aug 07 10:42:03  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_6548.trc:
ORA-00449: 后台进程 'LGWR' 因错误 340 异常终止
ORA-00340: 处理联机日志  (用于线程 ) 时出现 I/O 错误
Thu Aug 07 10:42:03  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_8104.trc:
ORA-00449: 后台进程 'LGWR' 因错误 340 异常终止
ORA-00340: 处理联机日志  (用于线程 ) 时出现 I/O 错误
Thu Aug 07 10:42:03  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\bdump\szcg_lgwr_884.trc:
ORA-00340: IO error processing online log 3 of thread 1
ORA-00345: redo log write error block 65238 count 13
ORA-00312: online log 3 thread 1: 'F:\ORADATA\SZCG\REDO03.LOG'
ORA-27070: async read/write failed
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 1) 函数不正确。
Thu Aug 07 10:42:03  2014
LGWR: terminating instance due to error 340
Thu Aug 07 10:42:05  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_8104.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00449: background process 'LGWR' unexpectedly terminated with error 340
ORA-00340: IO error processing online log  of thread
Thu Aug 07 10:42:05  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_1856.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00449: background process 'LGWR' unexpectedly terminated with error 340
ORA-00340: IO error processing online log  of thread
Thu Aug 07 10:42:05  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_6548.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00449: background process 'LGWR' unexpectedly terminated with error 340
ORA-00340: IO error processing online log  of thread
Thu Aug 07 17:40:05  2014
ALTER DATABASE OPEN
Thu Aug 07 17:40:05  2014
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Thu Aug 07 17:40:06  2014
Started redo scan
Thu Aug 07 17:40:06  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_5168.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-00312: 联机日志 3 线程 1: 'F:\ORADATA\SZCG\REDO03.LOG'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 1) 函数不正确。
Thu Aug 07 17:40:06  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_5168.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-00312: 联机日志 3 线程 1: 'F:\ORADATA\SZCG\REDO03.LOG'
ORA-27091: 无法将 I/O 排队
ORA-27070: 异步读取/写入失败
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1) 函数不正确。
Thu Aug 07 17:40:06  2014
Aborting crash recovery due to error 333
Thu Aug 07 17:40:06  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_5168.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-333 signalled during: ALTER DATABASE OPEN...

进一步检查发现在7月6日系统就已经报io异常

Sun Jul 06 10:05:23  2014
ARC0: All Archive destinations made inactive due to error 333
Sun Jul 06 10:06:07  2014
KCF: write/open error block=0xd03 online=1
     file=3 F:\ORADATA\SZCG\SYSAUX01.DBF
     error=27070 txt: 'OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 1) 函数不正确。'
Automatic datafile offline due to write error on
file 3: F:\ORADATA\SZCG\SYSAUX01.DBF
Sun Jul 06 10:06:23  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\bdump\szcg_arc1_2676.trc:
ORA-00333: redo log read error block 63489 count 2048
ORA-00312: online log 2 thread 1: 'F:\ORADATA\SZCG\REDO02.LOG'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1) 函数不正确。
Thu Aug 07 10:36:54  2014
ARC1: All Archive destinations made inactive due to error 333
Thu Aug 07 10:37:25  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\bdump\szcg_m000_5832.trc:
ORA-01135: file 3 accessed for DML/query is offline
ORA-01110: data file 3: 'F:\ORADATA\SZCG\SYSAUX01.DBF'

检查硬件发现raid一块盘完全损坏，另外一块盘也处于告警状态，保护现场拷贝文件过程中发现redo02,redo03,sysaux无法拷贝,使用rman检查发现
sysaux-block

因为redo完全损坏，使用工具跳过坏块，拷贝相关有坏块文件到其他目录，重命名相关文件尝试启动数据库，依然报ORA-00333 ORA-00312

Started redo scan
Thu Aug 07 17:40:06  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_5168.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-00312: 联机日志 3 线程 1: 'F:\ORADATA\SZCG\REDO03.LOG'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 1) 函数不正确。
Thu Aug 07 17:40:06  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_5168.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-00312: 联机日志 3 线程 1: 'F:\ORADATA\SZCG\REDO03.LOG'
ORA-27091: 无法将 I/O 排队
ORA-27070: 异步读取/写入失败
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1) 函数不正确。
Thu Aug 07 17:40:06  2014
Aborting crash recovery due to error 333
Thu Aug 07 17:40:06  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_5168.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-333 signalled during: ALTER DATABASE OPEN...

设置隐含参数_allow_resetlogs_corruption,尝试强制拉库

Started redo scan
Fri Aug 08 12:13:25  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_3892.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-00312: 联机日志 3 线程 1: 'F:\ORADATA\SZCG\REDO03.LOG'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 1) 函数不正确。
Fri Aug 08 12:13:25  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_3892.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-00312: 联机日志 3 线程 1: 'F:\ORADATA\SZCG\REDO03.LOG'
ORA-27091: 无法将 I/O 排队
ORA-27070: 异步读取/写入失败
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1) 函数不正确。
Fri Aug 08 12:13:25  2014
Aborting crash recovery due to error 333
Fri Aug 08 12:13:25  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_3892.trc:
ORA-00333: 重做日志读取块 63016 计数 8192 出错
ORA-333 signalled during: ALTER DATABASE OPEN...
Fri Aug 08 12:13:45  2014
ALTER DATABASE RECOVER  database until cancel
Fri Aug 08 12:13:45  2014
Media Recovery Start
 parallel recovery started with 15 processes
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
Fri Aug 08 12:13:55  2014
ALTER DATABASE RECOVER    CANCEL
Fri Aug 08 12:13:59  2014
ORA-1547 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
Fri Aug 08 12:13:59  2014
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Fri Aug 08 12:14:12  2014
alter database open resetlogs
Fri Aug 08 12:14:13  2014
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
ORA-1245 signalled during: alter database open resetlogs...
Fri Aug 08 12:54:11  2014
alter tablespace sysaux offline
Fri Aug 08 12:54:11  2014
ORA-1109 signalled during: alter tablespace sysaux offline...
Fri Aug 08 13:05:30  2014
alter database open
Fri Aug 08 13:05:30  2014
ORA-1589 signalled during: alter database open...

在offline过程中，数据库检查到sysaux数据文件为offline状态，当表空间只有一个数据文件，而且该数据文件为offline，数据库将会尝试offline sysaux表空间，但是发现该表空间文件非正常scn，无法offline 表空间，导致resetlogs操作失败。这里是操作失误应该先online相关数据文件，然后再进行resetlogs操作

Sat Aug 09 11:56:03  2014
alter database datafile 3 online
Sat Aug 09 11:56:04  2014
Completed: alter database datafile 3 online
Sat Aug 09 11:56:08  2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Sat Aug 09 11:56:18  2014
ARCH: Encountered disk I/O error 19502
Sat Aug 09 11:56:18  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_4516.trc:
ORA-19502: 文件 "F:\ARCHIVE\ARC01745_0814618167.001", 块编号 55297 写错误 (块大小 = 512)
ORA-27072: 文件 I/O 错误
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 1) 函数不正确。
ORA-19502: 文件 "F:\ARCHIVE\ARC01745_0814618167.001", 块编号 55297 写错误 (块大小 = 512)
Sat Aug 09 11:56:18  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_4516.trc:
ORA-19502: 文件 "F:\ARCHIVE\ARC01745_0814618167.001", 块编号 55297 写错误 (块大小 = 512)
ORA-27072: 文件 I/O 错误
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 1) 函数不正确。
ORA-19502: 文件 "F:\ARCHIVE\ARC01745_0814618167.001", 块编号 55297 写错误 (块大小 = 512)
ARCH: I/O error 19502 archiving log 3 to 'F:\ARCHIVE\ARC01745_0814618167.001'
Sat Aug 09 11:56:18  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_4516.trc:
ORA-00265: 要求实例恢复, 无法设置 ARCHIVELOG 模式
Archive all online redo logfiles failed:265
RESETLOGS after incomplete recovery UNTIL CHANGE 77983856
Resetting resetlogs activation ID 3562192628 (0xd452bef4)
Online log F:\ORADATA\SZCG\REDO01.LOG: Thread 1 Group 1 was previously cleared
Online log F:\ORADATA\SZCG\REDO02.LOG: Thread 1 Group 2 was previously cleared
Online log D:\REDO04.LOG: Thread 1 Group 4 was previously cleared
Sat Aug 09 11:56:22  2014
Setting recovery target incarnation to 3
Sat Aug 09 11:56:23  2014
Assigning activation ID 3602586269 (0xd6bb1a9d)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=33, OS id=5900
Sat Aug 09 11:56:23  2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=34, OS id=5776
Sat Aug 09 11:56:24  2014
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: F:\ORADATA\SZCG\REDO01.LOG
Successful open of redo thread 1
Sat Aug 09 11:56:24  2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Aug 09 11:56:24  2014
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Sat Aug 09 11:56:24  2014
ARC0: Becoming the heartbeat ARCH
Sat Aug 09 11:56:24  2014
SMON: enabling cache recovery
Sat Aug 09 11:56:25  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_4516.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [77983864], [0], [77992379], [8388617], [], []
Sat Aug 09 11:56:26  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_4516.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [77983864], [0], [77992379], [8388617], [], []
Sat Aug 09 11:56:26  2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 4516
ORA-1092 signalled during: alter database open resetlogs...

ORA-600 2662这个错误很熟悉，直接推SCN,数据库open,但是报ORA-600 4194

Sat Aug 09 12:01:28  2014
SMON: enabling cache recovery
Dictionary check complete
Sat Aug 09 12:01:32  2014
SMON: enabling tx recovery
Sat Aug 09 12:01:32  2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=34, OS id=6116
Sat Aug 09 12:01:34  2014
LOGSTDBY: Validating controlfile with logical metadata
Sat Aug 09 12:01:34  2014
LOGSTDBY: Validation complete
Sat Aug 09 12:01:34  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\bdump\szcg_smon_920.trc:
ORA-00600: internal error code, arguments: [4194], [21], [53], [], [], [], [], []
Sat Aug 09 12:01:36  2014
Doing block recovery for file 2 block 319
Resuming block recovery (PMON) for file 2 block 319
Block recovery from logseq 2, block 56 to scn 1073742003
Sat Aug 09 12:01:36  2014
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: F:\ORADATA\SZCG\REDO02.LOG
Block recovery stopped at EOT rba 2.79.16
Block recovery completed at rba 2.79.16, scn 0.1073742002
Doing block recovery for file 2 block 153
Resuming block recovery (PMON) for file 2 block 153
Block recovery from logseq 2, block 56 to scn 1073741986
Sat Aug 09 12:01:36  2014
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: F:\ORADATA\SZCG\REDO02.LOG
Block recovery completed at rba 2.66.16, scn 0.1073741988
Sat Aug 09 12:01:36  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\bdump\szcg_smon_920.trc:
ORA-01595: error freeing extent (4) of rollback segment (10))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [21], [53], [], [], [], [], []
Sat Aug 09 12:01:36  2014
Errors in file d:\oracle\product\10.2.0\admin\szcg\udump\szcg_ora_5272.trc:
ORA-00600: internal error code, arguments: [4194], [21], [53], [], [], [], [], []
Sat Aug 09 12:01:36  2014
Completed: alter database open

尝试重建undo表空间并切换undo_tabspace到新undo表空间解决，因为数据库在恢复过程中使用了隐含参数强制拉库,不能保证数据一致性,强烈建议逻辑方式重建数据库
在本次故障中，所幸的是只有redo和sysaux文件损坏，如果是业务数据文件或者system数据文件损坏，恢复的后果可能更加麻烦，丢失数据可能更加多。再次说明：数据库备份非常重要,数据的安全性不能完全寄希望于硬件之上

某集团ebs数据库redo undo丢失导致悲剧

某集团的ebs系统因磁盘空间不足把redo和undo存放到raid 0之上，而且该库无任何备份。最终悲剧发生了,raid 0异常导致redo undo全部丢失,数据库无法正常启动(我接手之时数据库已经resetlogs过,但是未成功)

Sun Jul 27 11:31:27 2014
SMON: enabling cache recovery
SMON: enabling tx recovery
Sun Jul 27 11:31:27 2014
Database Characterset is ZHS16GBK
Sun Jul 27 11:31:27 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/bdump/prod_smon_454754.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 42 cannot be read at this time
ORA-01110: data file 42: '/prod/oracle/PROD/logdata/undo/undo1.dbf'
Sun Jul 27 11:31:27 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/bdump/prod_smon_454754.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 42 cannot be read at this time
ORA-01110: data file 42: '/prod/oracle/PROD/logdata/undo/undo1.dbf'
Sun Jul 27 11:31:27 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/bdump/prod_smon_454754.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 42 cannot be read at this time
ORA-01110: data file 42: '/prod/oracle/PROD/logdata/undo/undo1.dbf'
Sun Jul 27 11:31:27 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_663670.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 41 cannot be read at this time
ORA-01110: data file 41: '/prod/oracle/PROD/logdata/undo/undo2.dbf'
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 663670
ORA-1092 signalled during: ALTER DATABASE OPEN...

查询相关文件状态发现,undo表空间文件丢失,被offline处理
df_status
因为以前alert日志被清理,通过这里大概猜测是offline丢失的undo文件,然后resetlogs了数据库,现在处理方式为
使用_corrupted_rollback_segments屏蔽回滚段,然后尝试启动数据库

Tue Jul 29 11:40:39 2014
SMON: enabling cache recovery
SMON: enabling tx recovery
Tue Jul 29 11:40:39 2014
Database Characterset is ZHS16GBK
Tue Jul 29 11:40:39 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/bdump/prod_smon_569378.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number  with name "" too small
Tue Jul 29 11:40:39 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/bdump/prod_smon_569378.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number  with name "" too small
Tue Jul 29 11:40:39 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/bdump/prod_smon_569378.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number  with name "" too small
Tue Jul 29 11:40:39 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_585786.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number  with name "" too small
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 585786
ORA-1092 signalled during: alter database open...

该错误是由于数据库启动需要找到对应的回滚段,但是由于undo异常导致该回滚段无法找到，因此出现该错误，解决方法是通过修改数据scn，让其不找回滚段,从而屏蔽该错误.数据库启动后,删除undo重新创建新undo

Tue Jul 29 15:59:22 2014
drop tablespace undo2 including contents and datafiles
Tue Jul 29 15:59:23 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_782490.trc:
ORA-01122: database file 41 failed verification check
ORA-01110: data file 41: '/prod/oracle/PROD/logdata/undo/undo2.dbf'
ORA-01565: error in identifying file '/prod/oracle/PROD/logdata/undo/undo2.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Tue Jul 29 15:59:23 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_782490.trc:
ORA-01259: unable to delete datafile /prod/oracle/PROD/logdata/undo/undo2.dbf
Tue Jul 29 15:59:23 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_782490.trc:
ORA-01122: database file 42 failed verification check
ORA-01110: data file 42: '/prod/oracle/PROD/logdata/undo/undo1.dbf'
ORA-01565: error in identifying file '/prod/oracle/PROD/logdata/undo/undo1.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
ORA-01259: unable to delete datafile /prod/oracle/PROD/logdata/undo/undo2.dbf
Tue Jul 29 15:59:23 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_782490.trc:
ORA-01259: unable to delete datafile /prod/oracle/PROD/logdata/undo/undo1.dbf
Tue Jul 29 15:59:23 2014
Completed: drop tablespace undo2 including contents and datafiles
Tue Jul 29 15:59:56 2014
create undo tablespace undotbs1 datafile '/prod/oracle/PROD/logdata/undo_new01.dbf' size 100M autoextend on next 128M maxsize 30G
Tue Jul 29 15:59:57 2014
Completed: create undo tablespace undotbs1 datafile '/prod/oracle/PROD/logdata/undo_new01.dbf' size 100M autoextend on next 128M maxsize 30G
Tue Jul 29 16:00:03 2014
alter tablespace undotbs1 add datafile '/prod/oracle/PROD/logdata/undo_new02.dbf' size 100M autoextend on next 128M maxsize 30G
Completed: alter tablespace undotbs1 add datafile '/prod/oracle/PROD/logdata/undo_new02.dbf' size 100M autoextend on next 128M maxsize 30G

业务运行过程中,数据库报大量ORA-600 4097,ORA-600 kdsgrp1,ORA-600 kcfrbd_3错误

Tue Jul 29 16:07:03 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_950484.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Tue Jul 29 16:07:06 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_950484.trc:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []
Tue Jul 29 16:10:06 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_917702.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Tue Jul 29 16:10:07 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_917702.trc:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []
Tue Jul 29 16:12:45 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/bdump/prod_m000_880692.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Tue Jul 29 16:21:23 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_1040638.trc:
ORA-00600: 内部错误代码, 参数: [kcfrbd_3], [41], [231381], [1], [12800], [12800], [], []
Tue Jul 29 16:21:37 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_1040638.trc:
ORA-00600: 内部错误代码, 参数: [kcfrbd_3], [41], [231381], [1], [12800], [12800], [], []
Tue Jul 29 16:21:56 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_1040638.trc:
ORA-00600: 内部错误代码, 参数: [kcfrbd_3], [41], [231381], [1], [12800], [12800], [], []
Tue Jul 29 16:22:18 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_1040638.trc:
ORA-00600: 内部错误代码, 参数: [kcfrbd_3], [41], [231381], [1], [12800], [12800], [], []
Tue Jul 29 16:22:28 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_1105950.trc:
ORA-00600: 内部错误代码, 参数: [4097], [], [], [], [], [], [], []
Tue Jul 29 16:22:33 2014
Errors in file /prod/oracle/PROD/db/tech_st/10.2.0/admin/PROD_erpserver/udump/prod_ora_1159232.trc:
ORA-00600: 内部错误代码, 参数: [kcfrbd_3], [42], [61235], [1], [12800], [12800], [], []

出现该错误有几个原因和解决方法：
ORA-600 kdsgrp1 是因为相关坏块引起(tab,index,memory,cr block等),结合日志分析对象异常原因,根据具体情况确定对象然后选择合适处理方案(具体参考NOTE:1332252.1)
ORA-600 4097 由于数据库异常关闭然后open,创建回滚段,可能触发bug导致该问题(虽然说在当前版本修复,但是实际处理我确实按照NOTE:1030620.6解决)
ORA-600 kcfrbd_3 有事务的block被访问之后,根据回滚槽信息定位到相关回滚段,而正好新建的回滚段信息又和以前的名字编号一致,从而反馈出来是数据文件大小不够,从而出现该错误(具体参考NOTE:601798.1)
最终该数据库虽然恢复了,抢救了大量数据,但是对于ebs系统来说,丢失redo和undo数据的损失还是巨大的.再次温馨提示:数据库的redo,undo也很重要,数据库的备份更加重要

bbed简单替换block测试

Patchsets

PSU, SPU(CPU), Bundle Patches

12.1.0.1

11.2.0.4

11.2.0.3

11.2.0.2

11.2.0.1

11.1.0.7

11.1.0.6

10.2.0.5

10.2.0.4

10.2.0.3

10.2.0.2

10.2.0.1

10.1.0.5

10.1.0.4

10.1.0.3

10.1.0.2

9.2.0.8

9.2.0.7

9.2.0.6

9.2.0.5

9.2.0.4

8.1.7.4