接到第一个使用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)
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: { 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: { 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确实有着越来越多的用户在使用.
联系:手机(13429648788) QQ(107644445)
链接:http://www.xifenfei.com/5597.html
作者:惜分飞