联系：手机(13429648788)qq(107644445) 链接：http://www.xifenfei.com/5597.html 标题：oracle 12c的第一次异常恢复—文件头坏块 作者：惜分飞 接到第一个使用oracle 12c作为生产库的恢复救援.有两个业务数据文件报文件头损坏,其他数据文件全部是9月份的一
联系：手机(13429648788) qq(107644445)
链接：http://www.xifenfei.com/5597.html
标题：oracle 12c的第一次异常恢复—文件头坏块
作者：惜分飞
接到第一个使用oracle 12c作为生产库的恢复救援.有两个业务数据文件报文件头损坏,其他数据文件全部是9月份的一次备份,在当前的条件下,希望我们能够帮他们恢复出来业务文件中的数据
数据库版本信息
sql> select* from v$version;
banner                                                                               con_id
-------------------------------------------------------------------------------- ----------
oracle database12c enterprise edition release 12.1.0.1.0 - 64bit production              0
pl/sql release 12.1.0.1.0 - production                                                    0
core    12.1.0.1.0      production                                                        0
tns forlinux: version 12.1.0.1.0 - production                                            0
nlsrtl version 12.1.0.1.0 - production                                                    0
数据库故障
具体脚本请参考:数据库恢复检查脚本(oracle database recovery check)
控制文件信息
控制文件中关于数据文件信息
数据文件头信息
alert日志报错
reading datafile '/app/oracle/oradata/freetouch/sales.dbf' for corruption at rdba: 0x00000001 (file4, block 1)
reread (file4, block 1) found same corrupt data (no logical check)
hex dump of (file5, block 1) in trace file /app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc
corrupt block relative dba: 0x00000001 (file5, block 1)
fractured block found during kcvxfh v8
data inbad block:
 type: 0format: 2 rdba: 0x00000001
 last change scn: 0x0000.00000000seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency valuein tail: 0x00000001
 check valuein block header: 0xa701
 computed block checksum: 0x0
reading datafile'/app/oracle/oradata/freetouch/drp_200200'for corruption at rdba: 0x00000001 (file5, block 1)
reread (file5, block 1) found same corrupt data (no logical check)
hex dump of (file4, block 1) in trace file /app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc
corrupt block relative dba: 0x00000001 (file4, block 1)
fractured block found during kcvxfh v8
data inbad block:
 type: 0format: 2 rdba: 0x00000001
 last change scn: 0x0000.00000000seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency valuein tail: 0x00000001
 check valuein block header: 0xa701
 computed block checksum: 0x0
odu无法识别异常文件
[oracle@db odu]$ ./odu
oracle data unloader trial version 4.1.3
copyright (c) 2008,2009,2010,2011 xiongjun. all rights reserved.
loading default config.......
byte_order little
block_size  8192
db_timezone -7
invalid db timezone:-7
client_timezone 8
invalid client timezone:8
asmfile_extract_path/home/oracle/hongye/odu/data
data_path  /home/oracle/hongye/odu/data
lob_path  /home/oracle/hongye/odu/data
charset_name zhs16gbk
ncharset_name al16utf16
output_form  dmp
error at line 10.
lob_storage infile
clob_byte_order big
trace_level 1
delimiter |
unload_deleted yes
file_header_offset 0
is_tru64 no
record_row_addr no
convert_clob_charsetyes
load config file 'config.txt'successful
loading default asm diskfile ......
can not openfile 'asmdisk.txt', error message:no suchfile or directory.
loading default controlfile ......
unknown fileformat '/app/oracle/oradata/freetouch/sales.dbf'
unknown fileformat '/app/oracle/oradata/freetouch/drp_200200'
ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
   1    1    1  8192   194560 n       0/app/oracle/oradata/freetouch/system01.dbf
   6    2   10  8192    45840 n       0/app/oracle/oradata/freetouch/example01.dbf
   1    3    3  8192   907520 n       0/app/oracle/oradata/freetouch/sysaux01.dbf
   4 1024   10  8192        0 n       0/app/oracle/oradata/freetouch/sales.dbf
   5 1024    9  8192        0 n       0/app/oracle/oradata/freetouch/drp_200200
   4    6    6  8192   128320 n       0/app/oracle/oradata/freetouch/users01.dbf
   7    7    7  8192   780288 n       0/app/oracle/oradata/freetouch/undotbs03.dbf
  11    8    8  8192    25600 n       0/app/oracle/oradata/freetouch/indx01.dbf
load control file 'control.txt'successful
loading dictionary data......done
loading scanned data......done
dul无法识别异常文件
[oracle@db dul]$ ./dul
data unloader: 10.2.0.5.32 - internal only - on sun nov  2 23:34:42 2014
with 64-bit io functions
copyright (c) 1994 2014 bernard van duijnen all rights reserved.
strictly oracle internal use only
dul: warning: ulimit process stack size is only 33554432
found db_id = 270587870
found db_name = valuenet
dul: warning: cannot verifyfile number for /app/oracle/oradata/freetouch/sales.dbf
dul: warning: first four bytes(76 162 0 0) of block 2 are not the start of a proper data block header
dul: warning: block corruption or configuration error
dul: warning: check db_block_size and/orosd_file_leader_size and/orfile offset
dul: error: file number can only be zerofor single tablespace datafiles
dul: warning: cannot verifyfile number for /app/oracle/oradata/freetouch/drp_200200
dul: warning: first four bytes(76 162 0 0) of block 2 are not the start of a proper data block header
dul: warning: block corruption or configuration error
dul: warning: check db_block_size and/orosd_file_leader_size and/orfile offset
dul: error: file number can only be zerofor single tablespace datafiles
dul> show datafiles;
ts# rf# start   blocks offs open  err file name
  0   1     0   194561    0    1    0/app/oracle/oradata/freetouch/system01.dbf
  1   3     0   907521    0    1    0/app/oracle/oradata/freetouch/sysaux01.dbf
  4   6     0   128321    0    1    0/app/oracle/oradata/freetouch/users01.dbf
  7   7     0   780289    0    1    0/app/oracle/oradata/freetouch/undotbs03.dbf
 11   8     0    25601    0    1    0/app/oracle/oradata/freetouch/indx01.dbf
  6  10     0    45841    0    1    0/app/oracle/oradata/freetouch/example01.dbf
该异常文件使用dul/odu均无法正常识别.证明文件头确实已经损坏
dbv 检测
[oracle@db trace]$ dbvfile=/app/oracle/oradata/freetouch/drp_200200
dbverify: release 12.1.0.1.0 - production on sun nov 2 14:08:34 2014
copyright (c) 1982, 2013, oracle and/orits affiliates.  all rights reserved.
dbverify - verification starting : file =/app/oracle/oradata/freetouch/drp_200200
dbverify - verification complete
total pages examined         : 194560
total pages processed (data) : 114596
total pages failing   (data) : 0
total pages processed (index): 26198
total pages failing   (index): 0
total pages processed (other): 37787
total pages processed (seg)  : 1
total pages failing   (seg)  : 0
total pages empty            : 15979
total pages marked corrupt   : 0
total pages influx           : 0
total pages encrypted        : 0
highest block scn            : 129603862 (0.129603862)
[oracle@db ~]$ dbvfile=/app/oracle/oradata/freetouch/sales.dbf
dbverify: release 12.1.0.1.0 - production on sun nov 2 23:12:05 2014
copyright (c) 1982, 2013, oracle and/orits affiliates.  all rights reserved.
dbverify - verification starting : file =/app/oracle/oradata/freetouch/sales.dbf
dbverify - verification complete
total pages examined         : 655360
total pages processed (data) : 294938
total pages failing   (data) : 0
total pages processed (index): 233404
total pages failing   (index): 0
total pages processed (lob)  : 38
total pages failing   (lob)  : 0
total pages processed (other): 23252
total pages processed (seg)  : 0
total pages failing   (seg)  : 0
total pages empty            : 103728
total pages marked corrupt   : 0
total pages influx           : 0
total pages encrypted        : 0
highest block scn            : 134665298 (0.134665298)
dbv检测结果无坏块,但是v$datafile_header和alert日志中报坏块,初步判断是由于该文件是bigfile,dbv未检测到文件头坏块,实际该该数据文件头损坏，其他block正常.所幸的是该库有9月份的rman备份(中间归档丢失),因此使用rman还原出来9月份的数据文件,然后使用dd拷贝两个 block(block 0和block 1)到异常文件.
[root@db freetouch]# dd if=/app1/oracle/oradata/freetouch/sales.dbf bs=8192 count=2 of=/tmp/odu/sales.2
2+0 records in
2+0 records out
16384 bytes (16 kb) copied, 0.0176368 s, 929 kb/s
[root@db freetouch]# dd if=/tmp/odu/sales.2 of=/app/oracle/oradata/freetouch/sales.dbf bs=8192 count=2 conv=notrunc 
2+0 records in
2+0 records out
16384 bytes (16 kb) copied, 6.4281e-05 s, 255 mb/s
[root@db freetouch]# dd if=/app1/oracle/oradata/freetouch/drp_200200 bs=8192 count=2 of=/tmp/odu/drp_200200.2
2+0 records in
2+0 records out
16384 bytes (16 kb) copied, 0.0185934 s, 881 kb/s
[root@db freetouch]# dd if=/tmp/odu/drp_200200.2 of=/app/oracle/oradata/freetouch/drp_200200 bs=8192 count=2 conv=notrunc 
2+0 records in
2+0 records out
16384 bytes (16 kb) copied, 6.4419e-05 s, 254 mb/s
尝试恢复数据库
sql> recover database;
ora-00283: recovery session canceled dueto errors
ora-01610: recovery using the backup controlfileoption must be done
sql> recover database using backup controlfile;
ora-00283: recovery session canceled dueto errors
ora-19909: datafile 4 belongsto an orphan incarnation
ora-01110: data file 4:'/app/oracle/oradata/freetouch/sales.dbf'
使用bbed修改相关文件头,然后继续恢复
具体见：bbed解决ora-01190类似方法处理
sql> recover database using backup controlfile;
ora-00279: change 129603904 generatedat 11/02/2014 19:19:54 neededfor thread
1
ora-00289: suggestion :
/app/oracle/recovery_area/valuenet/archivelog/2014_11_02/o1_mf_1_1_%u_.arc
ora-00280: change 129603904for thread 1 is in sequence #1
specify log: {=suggested | filename | auto | cancel}
cancel;
ora-00308: cannotopen archived log'cancel;'
ora-27037: unableto obtain file status
linux-x86_64 error: 2:no such file or directory
additional information: 3
specify log: {=suggested | filename | auto | cancel}
cancel
media recovery cancelled.
sql> alterdatabase open resetlogs;
alter database open resetlogs
*
error atline 1:
ora-01092: oracle instance terminated. disconnection forced
ora-00600: internal error code, arguments: [2662], [0], [129603911], [0],
[129603913], [29360256], [], [], [], [], [], []
process id: 19881
session id: 1 serial number: 3
出现ora-600[2662]错误,因为scn相差比较小，重启数据库机器,出现ora-600[4194]错误
sql> startup pfile='/tmp/pfile.txt'mount
oracle instance started.
total system global area 2505338880 bytes
fixed size                 2291472 bytes
variable size            973080816 bytes
database buffers         1526726656 bytes
redo buffers                3239936 bytes
database mounted.
sql> alterdatabase open;
alter database open
*
error atline 1:
ora-01113: file 1 needs media recovery
ora-01110: data file 1:'/app/oracle/oradata/freetouch/system01.dbf'
sql> recover database ;
ora-00283: recovery session canceled dueto errors
ora-16433: the database or pluggable database must be opened in read/write
mode.
重建控制文件后继续恢复
sql> alterdatabase open resetlogs;    
alter database open resetlogs
*
error atline 1:
ora-01092: oracle instance terminated. disconnection forced
ora-00600: internal error code, arguments: [4194], [46], [19], [], [], [], [],
[], [], [], [], []
process id: 20351
session id: 1 serial number: 3
设置undo_management=manual然后继续恢复
[oracle@db tmp]$ sqlplus /as sysdba
sql*plus: release 12.1.0.1.0 productionon sun nov 2 19:29:45 2014
copyright (c) 1982, 2013, oracle. all rights reserved.
connected toan idle instance.
sql> startup pfile='/tmp/pfile.txt'
oracle instance started.
total system global area 2505338880 bytes
fixed size                 2291472 bytes
variable size            973080816 bytes
databasebuffers         1526726656 bytes
redo buffers                3239936 bytes
databasemounted.
databaseopened.
这次的恢复也证明oracle 12c确实有着越来越多的用户在使用.