欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

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

程序员文章站 2022-06-12 08:55:49
...

联系：手机(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 Database 12c 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 for Linux: Version 12.1.0.1.0 - Production 0

NLSRTL Version 12.1.0.1.0 - Production 0

数据库故障
具体脚本请参考:数据库恢复检查脚本(Oracle Database Recovery Check)
控制文件信息
【转自惜分飞】Oracle 12C的第一次异常恢复—文件头坏块
控制文件中关于数据文件信息
【转自惜分飞】Oracle 12C的第一次异常恢复—文件头坏块
数据文件头信息
【转自惜分飞】Oracle 12C的第一次异常恢复—文件头坏块
alert日志报错

Reading datafile '/app/oracle/oradata/freetouch/sales.dbf' for corruption at rdba: 0x00000001 (file 4, block 1)

Reread (file 4, block 1) found same corrupt data (no logical check)

Hex dump of (file 5, block 1) in trace file /app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc

Corrupt block relative dba: 0x00000001 (file 5, block 1)

Fractured block found during kcvxfh v8

Data in bad block:

type: 0 format: 2 rdba: 0x00000001

last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x00000001

check value in block header: 0xa701

computed block checksum: 0x0

Reading datafile '/app/oracle/oradata/freetouch/drp_200200' for corruption at rdba: 0x00000001 (file 5, block 1)

Reread (file 5, block 1) found same corrupt data (no logical check)

Hex dump of (file 4, block 1) in trace file /app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc

Corrupt block relative dba: 0x00000001 (file 4, block 1)

Fractured block found during kcvxfh v8

Data in bad block:

type: 0 format: 2 rdba: 0x00000001

last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x00000001

check value in 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_charset yes

load config file 'config.txt' successful

loading default asm disk file ......

can not open file 'asmdisk.txt', error message:No such file or directory.

loading default control file ......

unknown file format '/app/oracle/oradata/freetouch/sales.dbf'

unknown file format '/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 verify file 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/or osd_file_leader_size and/or file offset

DUL: Error: File Number can only be zero for Single Tablespace Datafiles

DUL: Warning: Cannot verify file 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/or osd_file_leader_size and/or file offset

DUL: Error: File Number can only be zero for 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]$ dbv file=/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/or its 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 ~]$ dbv file=/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/or its 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 due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;

ORA-00283: recovery session canceled due to errors

ORA-19909: datafile 4 belongs to 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 generated at 11/02/2014 19:19:54 needed for thread

1

ORA-00289: suggestion :

/app/oracle/recovery_area/VALUENET/archivelog/2014_11_02/o1_mf_1_1_%u_.arc

ORA-00280: change 129603904 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel;

ORA-00308: cannot open archived log 'cancel;'

ORA-27037: unable to 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> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 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> alter database open;

alter database open

*

ERROR at line 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 due to errors

ORA-16433: The database or pluggable database must be opened in read/write

mode.

重建控制文件后继续恢复

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 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 Production on Sun Nov 2 19:29:45 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an 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

Database Buffers 1526726656 bytes

Redo Buffers 3239936 bytes

Database mounted.

Database opened.

这次的恢复也证明Oracle 12C确实有着越来越多的用户在使用.

相关标签： 转自 Oracle 12C 第一次 异常 恢复 文件 头坏块

上一篇： 怎么把显示的时间放在左边？_html/css_WEB-ITnose

下一篇： 详解php的魔术方法__get()和__set()使用介绍

推荐阅读