某省林业厅无备份情况下修复offline的数据文件

提示

本文为站长原创文章，版权所有，未经允许，禁止转载！

信息

源库环境：
硬件：IBM P550+ IBM P570
操作系统：AIX 6100-02 64bit
双机：HACMP 6.1 双机互备 + Oracle 单实例
oracle 版本：Oracle Enterprise 10.2.0.4 64bit

新库环境：
硬件：IBM P750+ IBM S824
操作系统：AIX 7100-10 64bit
oracle 版本：GI oracle 12.2.0.1.180116 + DB 12.1.0 、 12.2.0.1.180116

通过bbed工具修复dbf文件

给客户 OA 数据库做数据迁移时采用 TTS 方式，例行对相关表空间、数据文件检查时，发现文件号为：112的数据文件状态为recover，客户RMAN备份中已经没有正常状态的 112 号数据文件的备份，决定通过 bbed 来修复数据文件。首先检查一下数据文件的状态同时dump下数据文件头看看有什么有意义的信息。

SQL> select file_name,file_id,tablespace_name,status,online_status from dba_data_files where tablespace_name in ('XXLY_OA','XXLY_GSMM', 'XXLY_YLJJ', 'XXLY_GWGL', 'XXLY_DAGL', 'XXLY_GDZC', 'XXLY_ZWXX', 'XXLY_LDSP', 'XXLY_DFSJ', 'XXLY_LYGZZ', 'XXLY_RYWC','DZBZ','DZBZ1','DZBZ2','DZBZ3','DZBZ4', 'XXLYTP','DZZWINDEX','DZBZINDEX1','DZBZINDEX2') order by file_name;
/dzzwdb_dbfile01/XXLY_GWGL51.dbf    112 DZBZ        AVAILABLE RECOVER
/dzzwdb_dbfile01/XXLY_GWGL52.dbf    113 XXLY_GWGL   AVAILABLE ONLINE

# dump 的文件头信息:
DATA FILE #112: 
  (name #124) /dzzwdb_dbfile01/XXLY_GWGL51.dbf
creation size=1920000 block size=8192 status=0x1c head=124 tail=124 dup=1
 tablespace 6, index=7 krfil=112 prev_file=80
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:3 scn: 0x0000.77d9eb30 11/14/2017 09:52:02
 Stop scn: 0x0000.77da97af 11/14/2017 09:56:39
 Creation Checkpointed at scn:  0x0000.77d9eb2c 11/14/2017 09:52:02
 thread:1 rba:(0x36c3.44f9.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
 Offline scn: 0x0000.00000000 prev_range: 0
 Online Checkpointed at scn:  0x0000.00000000 
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
 Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED 
 V10 STYLE FILE HEADER:
    Compatibility Vsn = 169870080=0xa200300
    Db ID=569751737=0x21f5b8b9, Db Name='XXLYT'
    Activation ID=0=0x0
    Control Seq=2216630=0x21d2b6, File size=1920000=0x1d4c00
    File Number=112, Blksiz=8192, File Type=3 DATA
Tablespace #6 - DZBZ  rel_fn:112 
Creation   at   scn: 0x0000.77d9eb2c 11/14/2017 09:52:02
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x32de21ac scn: 0x0000.487ed832 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2b2fac39 scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 04/27/2018 12:23:05
 status:0x4 root dba:0x00000000 chkpt cnt: 3 ctl cnt:2
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.77d9eb30 11/14/2017 09:52:02
 thread:1 rba:(0x36c3.44fb.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
Backup Checkpointed at scn:  0x0000.00000000 
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp  01/01/1988 00:00:00
Platform Information:    Creation Platform ID: 6
Current Platform ID: 6  Last Platform ID: 6

# dbv检测一下dbf文件
dzzwdb:/app/oracle$dbv file=/dzzwdb_dbfile01/XXLY_GWGL51.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on 星期五 4月 27 13:26:12 2018

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

DBVERIFY - Verification starting : FILE = /dzzwdb_dbfile01/XXLY_GWGL51.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 1920000
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 8
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1919992
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 2010770238 (0.2010770238)

同时通过查询dba_extents，确认只是个空的数据文件而已，因此可以通过BBED来恢复，步骤如下：

dzzwdb:/app/oracle/product/10.2.0/rdbms/lib$bbed parfile=par.bbed Password:

BBED: Release 2.0.0.0.0 - Limited Production on 星期五 4 月 27 16:13:23 2018

Copyright (c) 1982, 2007, Oracle.

All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************
BBED> info

File#   Name                                    Size(blks)
-----   ----                                    ---------
1       /dzzwdb_dbfile01/XXLY_GWGL51.dbf        347137

2       /dzzwdb_dbfile01/XXLY_GWGL52.dbf        366849


BBED> set file 1

BBED> p kcvfhckp

BBED> d /v File: /dzzwdb_dbfile01/XXLY_GWGL51.dbf (1) 
Block: 1 Offsets: 484 to 995 Dba:0x00400001
77d9eb30 0000d2e8 3938d542 00010000 l w..0....98.B.... 000036c3 000044fb 0010ffff 02000000 l ..6...D......... 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00060006 00060100 00000000 00000000 l ................ 
00000000 1c000002 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................ 
00000000 00000000 00000000 00000000 l ................
.....

BBED> set file 1 block 1
BBED> d /v offset 484 count 16
BBED> set file 2 block 1
BBED> modify /x c3f182 offset 484
BBED> modify /x 82f1c3 offset 484
BBED> modify /x ff offset 500
BBED> sum apply

修复数据库

dzzwdb:/app/oracle/product/10.2.0/rdbms/lib$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on 星期五 4 月 27 16:25:35 2018

Copyright (c) 1982, 2007, Oracle.

Connected to:

All Rights Reserved.

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint; 
System altered.

SQL> recover datafile 112; 
Media recovery complete.

SQL> alter database datafile 112 online; 
Database altered.

SQL> alter system archive log current; 
System altered.

SQL> alter system switch logfile; 
System altered.

SQL> / SQL> exit