分析drop col对于数据存储块做了什么

发表于 2016 年 4 月 4 日 由 惜分飞

oracle 的alter table drop col具体内部是对于数据存储块操作的,如果drop col之后dul之类的工具是否可以恢复,这里我通过具体测试,结合bbed,dump block等方法来说明该问题
1.创建测试表，并写入硬盘

SQL> create table xff.t_xifenfei as select object_id,owner,object_name from dba_objects;

Table created.

SQL> desc xff.t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)


SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

2.找出来测试表一个block分析drop col对于存储的影响

SQL> select   rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno,object_id
  4    5  from xff.t_xifenfei where rownum<5;

ROWID                 REL_FNO    BLOCKNO      ROWNO  OBJECT_ID
------------------ ---------- ---------- ---------- ----------
AAAZ9wAAEAAAJojAAA          4      39459          0         20
AAAZ9wAAEAAAJojAAB          4      39459          1         46
AAAZ9wAAEAAAJojAAC          4      39459          2         28
AAAZ9wAAEAAAJojAAD          4      39459          3         15

3. dump block，并且记录该block 1,2,和最后一条记录

SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 4 block 39459;

System altered.

SQL> oradebug TRACEFILE_NAME
/home/u01/diag/rdbms/orcl/orcl/trace/orcl_ora_14069.trc


block_row_dump:
tab 0, row 0, @0x1f70
tl: 16 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 15
col  1: [ 3]  53 59 53
col  2: [ 5]  49 43 4f 4c 24
tab 0, row 1, @0x1f5e
tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 2f
col  1: [ 3]  53 59 53
col  2: [ 7]  49 5f 55 53 45 52 31
…………
tab 0, row 288, @0x589
tl: 22 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 3]  c2 03 5b
col  1: [ 3]  53 59 53
col  2: [10]  49 5f 4a 4f 42 5f 4e 45 58 54

4. 使用bbed查看该block 1,2,和最后一条记录

[oracle@localhost ~]$ bbed password=blockedit blocksize=8192 filename=/usr/local/oradata/qsng/users01.dbf 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 22:25:28 2016

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

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

BBED> set block 39459
        BLOCK#          39459

BBED> map
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459                                 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[289]                              @142     

 ub1 freespace[821]                         @720     

 ub1 rowdata[6647]                          @1541    

 ub4 tailchk                                @8188    


BBED> p *kdbr[0]
rowdata[6631]
-------------
ub1 rowdata[6631]                           @8172     0x2c

BBED> x /rncc
rowdata[6631]                               @8172    
-------------
flag@8172: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8173: 0x00
cols@8174:    3

col    0[2] @8175: 20 
col    1[3] @8178: SYS
col    2[5] @8182: ICOL$


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 8172 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c000302 c1150353 59530549 434f4c24 02067576 

 <32 bytes per line>

BBED> p *kdbr[1]
rowdata[6613]
-------------
ub1 rowdata[6613]                           @8154     0x2c

BBED> x /rncc
rowdata[6613]                               @8154    
-------------
flag@8154: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8155: 0x00
cols@8156:    3

col    0[2] @8157: 46 
col    1[3] @8160: SYS
col    2[7] @8164: I_USER1


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 8154 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c000302 c12f0353 59530749 5f555345 52312c00 0302c115 03535953 0549434f 
 4c240206 7576 

 <32 bytes per line>

BBED> p *kdbr[288]
rowdata[0]
----------
ub1 rowdata[0]                              @1541     0x2c

BBED> x /rncc
rowdata[0]                                  @1541    
----------
flag@1541: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1542: 0x00
cols@1543:    3

col    0[3] @1544: 290 
col    1[3] @1548: SYS
col   2[10] @1552: I_JOB_NEXT


BBED> set count 32
        COUNT           32

BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 1541 to 1572           Dba:0x00000000
------------------------------------------------------------------------
 2c000303 c2035b03 5359530a 495f4a4f 425f4e45 58542c00 0303c203 5a035359 

 <32 bytes per line>

5. 删除中间列，并且写入硬盘

SQL> ALTER TABLE XFF.T_XIFENFEI DROP COLUMN owner;

Table altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

6. 查询确定相同行所在block没有发生改变

SQL> select   rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno,object_id
  4    5  from xff.t_xifenfei where rownum<5;

ROWID                 REL_FNO    BLOCKNO      ROWNO  OBJECT_ID
------------------ ---------- ---------- ---------- ----------
AAAZ9wAAEAAAJojAAA          4      39459          0         20
AAAZ9wAAEAAAJojAAB          4      39459          1         46
AAAZ9wAAEAAAJojAAC          4      39459          2         28
AAAZ9wAAEAAAJojAAD          4      39459          3         15

7. drop col之后dump block继续分析

SQL> alter system dump datafile 4 block 39459;

System altered.

SQL>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/home/u01/diag/rdbms/orcl/orcl/trace/orcl_ora_14784.trc

SQL> 

tab 0, row 0, @0x1f70
tl: 12 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
tab 0, row 1, @0x1f5e
tl: 14 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 2f
col  1: [ 7]  49 5f 55 53 45 52 31
…………
tab 0, row 288, @0x589
tl: 18 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 3]  c2 03 5b
col  1: [10]  49 5f 4a 4f 42 5f 4e 45 58 54

8. 使用bbed查看drop col后的数据存储情况

$ bbed password=blockedit blocksize=8192 filename=/usr/local/oradata/qsng/users01.dbf 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 22:31:37 2016

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

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

BBED> set block 39459
        BLOCK#          39459

BBED> map 
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459                                 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[289]                              @142     

 ub1 freespace[821]                         @720     

 ub1 rowdata[6647]                          @1541    

 ub4 tailchk                                @8188    


BBED> p *kdbr[0]
rowdata[6631]
-------------
ub1 rowdata[6631]                           @8172     0x2c

BBED> x /rncc
rowdata[6631]                               @8172    
-------------
flag@8172: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8173: 0x02
cols@8174:    2

col    0[2] @8175: 20 
col    1[5] @8178: ICOL$


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 8172 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c020202 c1150549 434f4c24 434f4c24 0106de78 

 <32 bytes per line>

BBED> p *kdbr[1]
rowdata[6613]
-------------
ub1 rowdata[6613]                           @8154     0x2c

BBED> x /rncc
rowdata[6613]                               @8154    
-------------
flag@8154: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8155: 0x02
cols@8156:    2

col    0[2] @8157: 46 
col    1[7] @8160: I_USER1


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 8154 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c020202 c12f0749 5f555345 52315345 52312c02 0202c115 0549434f 4c24434f 
 4c240106 de78 

 <32 bytes per line>

BBED> p *kdbr[288]
rowdata[0]
----------
ub1 rowdata[0]                              @1541     0x2c

BBED> set count 32
        COUNT           32

BBED> x /rncc
rowdata[0]                                  @1541    
----------
flag@1541: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1542: 0x02
cols@1543:    2

col    0[3] @1544: 290 
col   1[10] @1548: I_JOB_NEXT


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 1541 to 1572           Dba:0x00000000
------------------------------------------------------------------------
 2c020203 c2035b0a 495f4a4f 425f4e45 58544e45 58542c02 0203c203 5a09495f 

 <32 bytes per line>

通过上述测试可以得出如下结论:
1. drop col是真的把对应列存储在block中的内容除掉,而且把后面的列的内容前移了,并且以前多于的内容(因为一行内容前移,后面就出现空闲记录不设置为空,而就是最初内容,下次如果行长度发生改变的时候使用,就和类似update把列修改短了一样)
2. drop col只是导致一行的长度变短,但是每行的偏移量未发生改变,也就是说,每行所在的偏移量没有改变,drop col之后,每行后面多了一些空闲空间
3. 根据上面分析的原理,drop col 是真的从block内部把这一列的数据使用后面列的数据覆盖了,因此从原理上而言,dul无法恢复drop col的数据(最后一列有可能可以恢复,因为他不会被覆盖),对于drop col,只能是通过备份不完全恢复,全库闪回,dg延迟应用等方法解决

发表在 Oracle | 标签为 , | 评论关闭

ADHU(ASM Disk Header Utility)—asm disk header备份恢复工具

发表于 2016 年 4 月 1 日 由 惜分飞

adhu（ASM Disk Header Utility）作为oracle asm中和kfed,amdu齐名的asm三大恢复神器之一,没有被oracle大力推广(属于内部工具),随着kfed功能增强和asm disk header自动备份功能的完善,adhu oracle基本上停止的开发支持,可以用来作为10.2.0.5之前asm版本的磁盘头保护工具
adhu预览
这里可以通过shell封装的utildhu调用adhu程序,实现更加人性化和自动化操作,它含有install,check,repair三个命令参数.

[root@xff1 tmp]# su - grid
xff1:/home/grid> cd /tmp/adhu/
xff1:/tmp/adhu> ls -l
total 68
-rwxr-xr-x 1 grid oinstall 18902 Nov  1  2008 adhu
-rw-r--r-- 1 grid oinstall  1970 Nov  1  2008 README
-rwxr-xr-x 1 grid oinstall  6964 Mar 21 16:20 utildhu
-rw-r--r-- 1 root root     12634 Mar 21 16:05 utildhu.zip
xff1:/tmp/adhu> ./utildhu 
Usage: utildhu install/check/repair [device name]

$utildhu install 
Will gather a list of member ASM disks and create
the backup directory  ./HeaderBackup
The ./HeaderBackup directory will contain the 
backup header of every asm disk in this database

$utildhu  check
Will run /tmp/adhu/adhu for every disk discovered by
$utildhu install and will email recipients configured 
in RECIPIENTS if there are errors in the disk header

It is hoped that the user will enter valid RECIPIENT
email addresses, and will place this utility in $ORA_ASM_HOME

$utildhu  repair <device name>
Will repair the device provided using the backup 
header blocks that have been copied previously.
This does assume that you have backup header blocks
in ./HeaderBackup

Sample crontab entry to run a check every 5 minutes
#Minute (0-59)  Hour (0-23)  Day of Month (1-31)  
Month (1-12 or Jan-Dec)  Day of Week (0-6 or Sun-Sat)  Command
0,5,10,15,20,25,30,35,40,45,50,55 * * * * *  utildhu check

Please read the README for more information

adhu install
install主要是实现utildhu.config配置文件生成和第一次asm 磁盘头备份

xff1:/tmp/adhu> ./utildhu install
xff1:/tmp/adhu> ls -l
total 64
-rwxr-xr-x 1 grid oinstall 18902 Nov  1  2008 adhu
drwxr-xr-x 2 grid oinstall  4096 Mar 21 16:23 HeaderBackup
-rw-r--r-- 1 grid oinstall  1117 Mar 21 16:23 persistent-log.utildhu
-rw-r--r-- 1 grid oinstall  1970 Nov  1  2008 README
-rwxr-xr-x 1 grid oinstall  6964 Mar 21 16:20 utildhu
-rw-r--r-- 1 grid oinstall   243 Mar 21 16:23 utildhu.config
-rw-r--r-- 1 grid oinstall   710 Mar 21 16:23 utildhu.out
-rw-r--r-- 1 root root     12634 Mar 21 16:05 utildhu.zip
xff1:/tmp/adhu> cd HeaderBackup/
xff1:/tmp/adhu/HeaderBackup> ls -ltr
total 12
-rw-r--r-- 1 grid oinstall 4096 Mar 21 16:23 oradata1p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 16:23 oradata2p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 16:23 ocrvotep1
xff1:/tmp/adhu/HeaderBackup> more ../utildhu.config 
/dev/mapper/oradata1p1                                                          
/dev/mapper/oradata2p1                                                          
/dev/mapper/ocrvotep1                                                           
xff1:/tmp/adhu>  more ../persistent-log.utildhu
Mon Mar 21 16:23:29 CST 2016
ASM Disk Header Check Utility Installed on 
Devices configured are:
/dev/mapper/oradata1p1                                                          
/dev/mapper/oradata2p1                                                          
/dev/mapper/ocrvotep1                                                           
ADHU: /dev/mapper/oradata1p1: Status 0x01 Mon Mar 21 16:23:29 2016
ADHU: /dev/mapper/oradata1p1: Diskgroup:DATA Disk:DATA_0000 #0
ADHU: /dev/mapper/oradata1p1: valid disk header found
ADHU: /dev/mapper/oradata1p1: backup block updated

###ADHU check run at Mon Mar 21 16:23:29 CST 2016  NO ERRORS FOUND

xff1:/tmp/adhu> ls -l HeaderBackup/
total 16
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:04 ocrvotep1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1.corrupt
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:04 oradata2p1

adhu check
对于正常的asm disk,主要是为了生成新的磁盘头备份

xff1:/tmp/adhu> ls -l HeaderBackup/
total 16
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:04 ocrvotep1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1.corrupt
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:04 oradata2p1
xff1:/tmp/adhu> ./utildhu check
xff1:/tmp/adhu> ls -l HeaderBackup/
total 16
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:11 ocrvotep1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:11 oradata1p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1.corrupt
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:11 oradata2p1

adhu repair
repair主要是修复磁盘头,当asm 磁盘头损坏之时,可以通过这个命令实现磁盘头修复

xff1:/tmp/adhu> dd if=/dev/zero of=/dev/mapper/oradata1p1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000282838 s, 14.5 MB/s
xff1:/tmp/adhu> ./utildhu check
xff1:/tmp/adhu> tail -f persistent-log.utildhu

###ADHU check run at Mon Mar 21 23:04:04 CST 2016  ERRORS FOUND
ADHU: /dev/mapper/oradata1p1: Status 0x08 Mon Mar 21 23:04:04 2016
ADHU: /dev/mapper/oradata1p1: Diskgroup:DATA Disk:DATA_0000 #0
ADHU: /dev/mapper/oradata1p1: corrupt disk header encountered
ADHU: /dev/mapper/oradata1p1: valid backup block found
ADHU: /dev/mapper/oradata1p1: CORRUPT HEADER NOT REPAIRED

xff1:/tmp/adhu> kfed read /dev/mapper/oradata1p1 
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F6BD9981400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

xff1:/tmp/adhu> sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 23:07:25 2016

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 Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup data dismount;
alter diskgroup data dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DATA" precludes its dismount


SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15013: diskgroup "DATA" is already mounted


SQL>  alter diskgroup data dismount force;

Diskgroup altered.

SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15040: diskgroup is incomplete


xff1:/tmp/adhu> ./utildhu repair /dev/mapper/oradata1p1 
DEVICE  /dev/mapper/oradata1p1 REPAIRED AT  Mon Mar 21 23:06:06 CST 2016
ADHU: /dev/mapper/oradata1p1: Status 0x04 Mon Mar 21 23:06:06 2016
ADHU: /dev/mapper/oradata1p1: Diskgroup:DATA Disk:DATA_0000 #0
ADHU: /dev/mapper/oradata1p1: corrupt disk header encountered
ADHU: /dev/mapper/oradata1p1: valid backup block found
ADHU: /dev/mapper/oradata1p1: disk header repaired

xff1:/tmp/adhu> sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 23:08:48 2016

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 Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup data mount;

Diskgroup altered.

adhu直接使用
adhu [-dir dirname ] [-repair] [-quiet] [-readonly] [-syslog mask ] devname
默认情况下adhu将disk header备份为当前目录下的备份文件。 使用-dir选项可以指定存放的目录。
当需要使用adhu去修复一个损坏的asm disk header时使用-repair 选项。
-quiet 选项将过滤所有正常的输出信息，若执行成功则不打印任何输出。
-readonly选项 以只读方式来打开disk device，这样备份block将不被写入，而备份文件将在可能的情况下写入。
-syslog选项控制是否写出结果到系统日志和标准输出。
devname代表为asm disk的设备文件，asm头的备份文件将以该device name为基础，并存放在当前目录或者-dir指定的目录。

发表在 Oracle ASM | 标签为 , , , , | 评论关闭

强制关机导致数据库无法正常启动恢复

发表于 2016 年 3 月 28 日 由 惜分飞

有客户qq找到我,说有朋友推荐,让我帮他们恢复数据库.由于强制关机后,数据库无法正常启动.
数据库recover database失败

Mon Mar 28 10:20:33 2016
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 32 slaves
Mon Mar 28 10:20:36 2016
Recovery of Online Redo Log: Thread 1 Group 2 Seq 18686 Reading mem 0
  Mem# 0: E:\ORACLE_DATA\YCCY\REDO02.LOG
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18687 Reading mem 0
  Mem# 0: E:\ORACLE_DATA\YCCY\REDO03.LOG
Recovery of Online Redo Log: Thread 1 Group 1 Seq 18688 Reading mem 0
  Mem# 0: E:\ORACLE_DATA\YCCY\REDO01.LOG
Mon Mar 28 10:20:38 2016
Hex dump of (file 45, block 7431) in trace file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0q_2968.trc
Corrupt block relative dba: 0x0b401d07 (file 45, block 7431)
Mon Mar 28 10:20:38 2016
Hex dump of (file 45, block 7836) in trace file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr01_2220.trc
Bad header found during media recovery
Corrupt block relative dba: 0x0b401e9c (file 45, block 7836)
Data in bad block:
Bad header found during media recovery
 type: 0 format: 0 rdba: 0x1d070000
 last change scn: 0x4917.f8dc0b40 seq: 0x0 flg: 0x00
 spare1: 0x6 spare2: 0xa2 spare3: 0xc7f7
 consistency value in tail: 0x06010000
 check value in block header: 0x601
 block checksum disabled
Reading datafile 'E:\ORACLE_DATA\YCCY\DT_SYS_IDX12.DBF' for corruption at rdba: 0x0b401d07 (file 45, block 7431)
Reread (file 45, block 7431) found valid data
Repaired corruption at (file 45, block 7431)
Hex dump of (file 45, block 7556) in trace file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0q_2968.trc
Corrupt block relative dba: 0x0b401d84 (file 45, block 7556)
Bad header found during media recovery
Data in bad block:
 type: 106 format: 3 rdba: 0x1d840000
 last change scn: 0x461d.391a0b40 seq: 0x0 flg: 0x00
 spare1: 0x6 spare2: 0xa2 spare3: 0x2499
 consistency value in tail: 0x06013999
 check value in block header: 0x401
 block checksum disabled
Reading datafile 'E:\ORACLE_DATA\YCCY\DT_SYS_IDX12.DBF' for corruption at rdba: 0x0b401d84 (file 45, block 7556)
Reread (file 45, block 7556) found valid data
Repaired corruption at (file 45, block 7556)
Mon Mar 28 10:20:38 2016
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1334748, kcbzfw()+3094]
Mon Mar 28 10:20:39 2016
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0k_3900.trc  (incident=131189):
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131189\yccy_pr0k_3900_i131189.trc
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0r_3060.trc  (incident=131245):
ORA-07445: exception encountered: core dump [kcbzfw()+3094] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1334748] [UNABLE_TO_READ] []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 169345, file offset is 1387274240 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131245\yccy_pr0r_3060_i131245.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942]
Mon Mar 28 10:20:39 2016
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0d_2112.trc  (incident=131133):
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131133\yccy_pr0d_2112_i131133.trc
Mon Mar 28 10:20:39 2016
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0e_3260.trc  (incident=131141):
ORA-00600: internal error code, arguments: [3020], [5], [163457], [21134977], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 163457, file offset is 1339039744 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131141\yccy_pr0e_3260_i131141.trc
Mon Mar 28 10:20:39 2016
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr04_3980.trc  (incident=131021):
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131021\yccy_pr04_3980_i131021.trc
Data in bad block:
 type: 0 format: 0 rdba: 0x1e9c0000
 last change scn: 0x4915.f8320b40 seq: 0x0 flg: 0x00
 spare1: 0x6 spare2: 0xa2 spare3: 0x8029
 consistency value in tail: 0x0602e40c
 check value in block header: 0x602
 block checksum disabled
Reading datafile 'E:\ORACLE_DATA\YCCY\DT_SYS_IDX12.DBF' for corruption at rdba: 0x0b401e9c (file 45, block 7836)
Reread (file 45, block 7836) found valid data
Repaired corruption at (file 45, block 7836)
Mon Mar 28 10:20:39 2016
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0f_816.trc  (incident=131149):
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131149\yccy_pr0f_816_i131149.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942]
Mon Mar 28 10:20:39 2016
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0i_2132.trc  (incident=131173):
ORA-00600: internal error code, arguments: [3020], [5], [154240], [21125760], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 154240, file offset is 1263534080 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131173\yccy_pr0i_2132_i131173.trc
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0k_3900.trc  (incident=131190):
ORA-07445: exception encountered: core dump [kcbzdh()+942] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131190\yccy_pr0k_3900_i131190.trc
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr01_2220.trc  (incident=131037):
ORA-00600: internal error code, arguments: [kcbrapply_14], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131037\yccy_pr01_2220_i131037.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942]
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0f_816.trc  (incident=131150):
ORA-07445: exception encountered: core dump [kcbzdh()+942] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131150\yccy_pr0f_816_i131150.trc
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr01_2220.trc  (incident=131038):
ORA-07445: exception encountered: core dump [kcbzdh()+942] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbrapply_14], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131038\yccy_pr01_2220_i131038.trc
Mon Mar 28 10:20:39 2016
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0h_4036.trc  (incident=131165):
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131165\yccy_pr0h_4036_i131165.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942]
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B, kcbzpnd()+299]
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1351BB9, kcbs_dump_adv_state()+1529]
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B, kcbzpnd()+299]
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0h_4036.trc  (incident=131166):
ORA-07445: exception encountered: core dump [kcbzdh()+942] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131166\yccy_pr0h_4036_i131166.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B, kcbzpnd()+299]
Mon Mar 28 10:20:40 2016
Checker run found 60 new persistent data failures
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0d_2112.trc  (incident=131134):
ORA-07445: exception encountered: core dump [kcbzpnd()+299] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131134\yccy_pr0d_2112_i131134.trc
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr04_3980.trc  (incident=131022):
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+1529] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1351BB9] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131022\yccy_pr04_3980_i131022.trc
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0e_3260.trc  (incident=131142):
ORA-07445: exception encountered: core dump [kcbzpnd()+299] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [3020], [5], [163457], [21134977], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 163457, file offset is 1339039744 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131142\yccy_pr0e_3260_i131142.trc
Mon Mar 28 10:20:41 2016
Trace dumping is performing id=[cdmp_20160328102041]
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0i_2132.trc  (incident=131174):
ORA-07445: exception encountered: core dump [kcbzpnd()+299] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [3020], [5], [154240], [21125760], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 154240, file offset is 1263534080 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131174\yccy_pr0i_2132_i131174.trc
Mon Mar 28 10:20:41 2016
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0, 0000000074CAE3F0]
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr06_2684.trc  (incident=131077):
ORA-07445: exception encountered: core dump [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131077\yccy_pr06_2684_i131077.trc
Mon Mar 28 10:20:42 2016
Exception [type: ACCESS_VIOLATION, UNABLE_TO_WRITE] [ADDR:0x0] [PC:0x4D20D2, kslgetl()+54]
Mon Mar 28 10:20:42 2016
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pmon_3856.trc  (incident=130853):
ORA-07445: exception encountered: core dump [kslgetl()+54] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x4D20D2] [UNABLE_TO_WRITE] []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_130853\yccy_pmon_3856_i130853.trc
Trace dumping is performing id=[cdmp_20160328102042]
Errors in file d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131077\yccy_pr06_2684_i131077.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] []
Process debug not enabled via parameter _debug_enable
Trace dumping is performing id=[cdmp_20160328102043]
Mon Mar 28 10:21:01 2016
RECO (ospid: 3524): terminating the instance due to error 472
Instance terminated by RECO, pid = 3524

通过观察这段日志,基本上可以发现主要是FILE 45,虽然提示坏块但是最终验证确定为正常块（类似：Reread (file 45, block 7836) found valid data),这里主要是file 5，报了大量的ORA-600[3020].

对数据文件逐个进行recover操作

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1.7103E+10 bytes
Fixed Size                  2192864 bytes
Variable Size            9059699232 bytes
Database Buffers         8019509248 bytes
Redo Buffers               21762048 bytes
数据库装载完毕。
SQL> recover datafile 1;
完成介质恢复。
SQL> recover  datafile 2;
ORA-03113: 通信通道的文件结尾
进程 ID: 1652
会话 ID: 551 序列号: 55

SQL> recover datafile 3;
完成介质恢复。
SQL> recover datafile 4;
完成介质恢复。

SQL> recover datafile 5;
ORA-03113: 通信通道的文件结尾
进程 ID: 4900
会话 ID: 551 序列号: 56131

SQL> recover datafile 6;
完成介质恢复。
…………
SQL> recover datafile 63;
完成介质恢复。
SQL> recover datafile 64;
完成介质恢复。

除掉datafile 2，5之外，其他文件全部recover成功.

对于file 2 尝试处理
无法通过recover成功,只能暂时放弃,后续考虑先offline open库,然后把这个文件强制online

SQL> recover  datafile 2 ;
ORA-03113: 通信通道的文件结尾
进程 ID: 5020
会话 ID: 551 序列号: 3


Mon Mar 28 10:47:12 2016
ALTER DATABASE RECOVER  datafile 2  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 18688 Reading mem 0
  Mem# 0: E:\ORACLE_DATA\YCCY\REDO01.LOG
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0, 0000000074CAE3F0]
Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_ora_3508.trc  (incident=143022):
ORA-07445: 出现异常错误: 核心转储 [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] []
Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_143022\yccy_ora_3508_i143022.trc
Errors in file d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_143022\yccy_ora_3508_i143022.trc:
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] []

对于file 5处理

SQL> recover datafile 5;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [5], [163457], [21134977], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 163457, file
offset is 1339039744 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: 数据文件 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'


SQL> recover  datafile 5 allow 1 corruption;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [5], [162433], [21133953], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 162433, file
offset is 1330651136 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: 数据文件 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'


SQL> recover  datafile 5 allow 1 corruption;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [5], [166272], [21137792], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 166272, file
offset is 1362100224 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: 数据文件 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'



SQL> recover  datafile 5 allow 1 corruption;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [5], [169346], [21140866], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 169346, file
offset is 1387282432 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: 数据文件 5: 'E:\ORACLE_DATA\YCCY\DT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'


SQL> recover  datafile 5 allow 1 corruption;
完成介质恢复。

open数据库并online datafile 2

SQL> startup pfile='d:/pfile.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1.7103E+10 bytes
Fixed Size                  2192864 bytes
Variable Size            9059699232 bytes
Database Buffers         8019509248 bytes
Redo Buffers               21762048 bytes
数据库装载完毕。
SQL> alter database datafile 2 offline;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> shutdown immediate;
ORA-03113: 通信通道的文件结尾
SQL> conn / as sysdba
已连接到空闲例程。

SQL> startup pfile='d:/pfile.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1.7103E+10 bytes
Fixed Size                  2192864 bytes
Variable Size            9059699232 bytes
Database Buffers         8019509248 bytes
Redo Buffers               21762048 bytes
数据库装载完毕。
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         3 INACTIVE
         2 CURRENT

SQL> recover database until cancel;
ORA-00279: 更改 1226478477 (在 03/28/2016 20:23:37 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\ORACLE\FLASH_RECOVERY_AREA\YCCY\ARCHIVELOG\2016_03_28\O1_MF_1_18689_%U_.ARC
ORA-00280: 更改 1226478477 (用于线程 1) 在序列 #18689 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\ORACLE_DATA\YCCY\REDO02.LOG
已应用的日志。
完成介质恢复。
SQL> alter database datafile 2 online;

数据库已更改。

SQL> alter database open resetlogs;

数据库已更改。

数据库基本上属于正常打开,处理掉3020部分的坏块基本ok

发表在 Oracle备份恢复 | 标签为 , , , , | 一条评论