|
|
接到第一个使用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
数据库故障
具体脚本请参考:<a href="http://www.xifenfei.com/oracle_recovery_check" title="数据库恢复检查脚本(Oracle Database Recovery Check)" target="_blank">数据库恢复检查脚本(Oracle Database Recovery Check)</a>
控制文件信息
<img src="http://www.xifenfei.com/wp-content/uploads/2014/11/1.jpg" alt="1" width="1417" height="135" class="alignleft size-full wp-image-5598" />
控制文件中关于数据文件信息
<img src="http://www.xifenfei.com/wp-content/uploads/2014/11/2.jpg" alt="2" width="1408" height="250" class="alignleft size-full wp-image-5599" />
数据文件头信息
<img src="http://www.xifenfei.com/wp-content/uploads/2014/11/3.jpg" alt="3" width="1407" height="242" class="alignleft size-full wp-image-5600" />
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
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修改相关文件头,然后继续恢复
具体见：<a href="http://www.xifenfei.com/2666.html" title="bbed解决ORA-01190" target="_blank">bbed解决ORA-01190</a>类似方法处理
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: {<RET>=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: {<RET>=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的第一次异常恢复—文件头坏块
|
|
发表于 2014-11-3 13:56:22
