【转自惜分飞】Oracle 12C的第一次异常恢复—文件头坏块

联系：手机(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

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

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

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

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 ses本文来源gaodai$ma#com搞$$代**码网$sion 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

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确实有着越来越多的用户在使用.

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：【转自惜分飞】Oracle 12C的第一次异常恢复—文件头坏块

Hi，您需要填写昵称和邮箱！