十二、通过BBED强制恢复offline状态的datafile
在这篇文章里,我们通过BBED强制让丢失了online需要的archive log的offline状态的datafile恢复成了online,并且成功抢救出来了数据。
希望如下的恢复过程能对朋友们有所帮助。

$ sqlplus ‘/ as sysdba’;

SQL*Plus: Release 9.2.0.6.0 – Production on Wed Oct 14 13:21:25 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production

SQL_astca>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dras20/astca/arch
Oldest online log sequence 9377
Next log sequence to archive 9379
Current log sequence 9379

SQL_astca > create tablespace testscn datafile ‘/dras20/astca/testscn_01.dbf’ size 100M extent management local uniform size 1M segment space management auto;

Tablespace created

SQL_astca > create table tbtestscn tablespace testscn as select * from dba_users;

Table created

SQL_astca > select count(*) from tbtestscn;

COUNT(*)
———-
32

SQL_astca > select username from tbtestscn where rownum<5;

USERNAME
------------------------------
SYS
SCOTT
SYSTEM
DBSNMP

SQL_astca>select file_id from dba_data_files where file_name=’/dras20/astca/testscn_01.dbf’;

FILE_ID
———-
139

SQL_astca>alter database datafile ‘/dras20/astca/testscn_01.dbf’ offline;

Database altered.

SQL_astca>select status from v$datafile where file#=139;

STATUS
——-
RECOVER

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

$ cd /dras20/astca/arch
$ ls -l
total 1888
-rw-r—– 1 oracle dba 901120 Oct 14 16:45 1_9379.dbf
-rw-r—– 1 oracle dba 48640 Oct 14 17:18 1_9380.dbf
-rw-r—– 1 oracle dba 1024 Oct 14 17:18 1_9381.dbf
-rw-r—– 1 oracle dba 1536 Oct 14 17:18 1_9382.dbf
-rw-r—– 1 oracle dba 1024 Oct 14 17:18 1_9383.dbf
-rw-r–r– 1 oracle dba 17 Oct 14 15:19 login.sql

$ rm *.dbf

$ ls -l
total 8
-rw-r–r– 1 oracle dba 17 Oct 14 15:19 login.sql

SQL_astca>alter database datafile ‘/dras20/astca/testscn_01.dbf’ online;
alter database datafile ‘/dras20/astca/testscn_01.dbf’ online
*
ERROR at line 1:
ORA-01113: file 139 needs media recovery
ORA-01110: data file 139: ‘/dras20/astca/testscn_01.dbf’

SQL_astca>select count(*) from tbtestscn;
select count(*) from tbtestscn
*
ERROR at line 1:
ORA-00376: file 139 cannot be read at this time
ORA-01110: data file 139: ‘/dras20/astca/testscn_01.dbf’

SQL_astca>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

这里直接改datafile 139的datafile header中的checkpoint scn和RBA是不行的!
因为oracle在对某个datafile做offline的时候实际上是相当于offline immediate,此时不会改datafile header中的内容,而只是修改control文件,等到再想online的时候一定要做recovery,对这一点,403e也有描述:
Offline normal (tablespace):
1、Checkpoints data blocks of tablespace
2、Updates file headers and control file

Offline immediate (tablespace or data file):
1、Only update control file
2、Data files require recovery

BBED无法改control文件,所以上述这条只改datafile header中的checkpoint scn和RBA的路是走不通的。

这里我采取的方法是先改datafile header,再重建控制文件,即可强制恢复offline状态的datafile,在修改上述offline的datafile header的过程中我是通过比对system01.dbf的datafile header来修改offline datafile的datafile header,完整的恢复过程可见附带的文件session.log

其中重要的步骤如下:
1、 先通过比对system01.dbf的datafile header的内容来修改datafile 139的datafile header。
2、 重建控制文件
3、 用带*._allow_resetlogs_corruption=TRUE的pfile启库到mount状态
4、 用open resetlogs强制打开上述数据库
5、 最后shutdown immediate再startup

我们来看一下最后的结果:
SQL_astca>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL_astca>startup
ORACLE instance started.

Total System Global Area 824150304 bytes
Fixed Size 743712 bytes
Variable Size 285212672 bytes
Database Buffers 536870912 bytes
Redo Buffers 1323008 bytes
Database mounted.
Database opened.
SQL_astca>select count(*) from tbtestscn;

COUNT(*)
———-
32

SQL_astca> select username from tbtestscn where rownum<5;

USERNAME
------------------------------
SYS
SCOTT
SYSTEM
DBSNMP

SQL_astca>select status from v$datafile where file#=139;

STATUS
——-
ONLINE

Trackback

no comment untill now

Add your comment now

切换到手机版