使用bbed恢复offline drop的数据文件的测试

最近模拟处理了一起由于同事误操作导致的数据库无法打开的情况。

现场环境：

OS：rhel 5.5 64bit

DB:oracle 10.2.0.5单机 非归档模式

现场无备份；

问题起因：

现场由于需要更换UPS，客户直接shutdown掉数据库服务器及磁盘阵列（磁盘阵列作为大磁盘挂载到服务器作为数据存储），UPS更换完毕后，只开启了数据库服务器，同事没有注意到磁盘阵列未启动，尝试启动数据库，提示ora 01057 ora 01110不能识别数据文件，从而使得数据库无法启动；

如果此时发现磁盘阵列未启动，而重新启动阵列，再去启动数据库，一切正常；悲催的是同事通过度娘查找了一篇文章，mount状态下执行了alter database datafile '*.dbf'offline drop;然后打开了数据库，运行了大概8个小时，期间有应用连接，redo log切换比较频繁，更悲催的是数据库非归档，无备份，此时已经无法正常恢复数据库，只能通过非常规手段，比如DUL、ODU、PRM-DUL等，当然也可以通过bbed恢复，下面就在实验环境中实现BBED恢复；

实验过程：

本实验分两次：redo未被覆盖以及被覆盖

首先搭建测试环境：

1、创建表空间

CREATE SMALLFILE TABLESPACE"TEST_TABLESPACE" DATAFILE '/u01/app/ora10g/oradata/testdb/test.dbf'SIZE 20M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2、创建用户及赋权限

CREATE USER "TEST" PROFILE"DEFAULT" IDENTIFIED BY "test1234" DEFAULT TABLESPACE"TEST_TABLESPACE" TEMPORARY TABLESPACE "TEMP" ACCOUNTUNLOCK;

GRANT CONNECT,RESOURCE,DBA TO TEST;

3、创建表并插入部分数据

create table test(id number,name varchar2(12))tablespace TEST_TABLESPACE;

insert into test values (1,'colin');

insert into test values (2,'janey');

insert into test values (3,'tom');

insert into test values (4,'adele');

select * from test;

ID NAME

---------- ------------

1 colin

2 janey

3 tom

4 adele

redo未被覆盖

前期数据文件：

SQL> select file#||' '||name||' '||bytes fromv$datafile ;

FILE#||''||NAME||''||BYTES

-----------------------------------------------------------------------

1/u01/app/ora10g/oradata/testdb/system01.dbf 314572800

2/u01/app/ora10g/oradata/testdb/undotbs01.dbf 288358400

3/u01/app/ora10g/oradata/testdb/sysaux01.dbf 125829120

4/u01/app/ora10g/oradata/testdb/users01.dbf 5242880

5/u01/app/ora10g/oradata/testdb/test.dbf 20971520

执行offline drop:

SQL>alter database datafile'/u01/app/ora10g/oradata/testdb/test.dbf' offline drop;

SQL>commit;

SQL>select * from test;

select* from test

*

ERRORat line 1:

ORA-00376:file 5 cannot be read at this time

ORA-01110:data file 5: '/u01/app/ora10g/oradata/testdb/test.dbf'

SQL> select LOG_MODE,OPEN_MODE from v$database;

LOG_MODE OPEN_MODE

----------------------

NOARCHIVELOGREAD WRITE

SQL> alter database datafile'/u01/app/ora10g/oradata/testdb/test.dbf' online;

alterdatabase datafile '/u01/app/ora10g/oradata/testdb/test.dbf' online

*

ERRORat line 1:

ORA-01113:file 5 needs media recovery if it was restored from backup, or END

BACKUPif it was not

ORA-01110:data file 5: '/u01/app/ora10g/oradata/testdb/test.dbf'

此时执行恢复：

SQL>recover datafile '/u01/app/ora10g/oradata/testdb/test.dbf';

Mediarecovery complete.

SQL>alter database datafile '/u01/app/ora10g/oradata/testdb/test.dbf' online;

Databasealtered.

SQL> select * from test;

ID NAME

----------------------

1 colin

2 janey

3 tom

4 adele

恢复成功，此时由于redo未进行切换，原来的操作还记录在日志中；

redo被覆盖

启动数据库到mount状态；

执行：

SQL>alter database datafile '/u01/app/ora10g/oradata/testdb/test.dbf' offline drop;

Databasealtered.

SQL>recover database;

ORA-00283:recovery session canceled due to errors

ORA-00264:no recovery required

SQL>alter database open;

Databasealtered.

SQL>select * from test.test;

select* from test.test

*

ERRORat line 1:

ORA-00376:file 5 cannot be read at this time

ORA-01110:data file 5: '/u01/app/ora10g/oradata/testdb/test.dbf'

重启数据库：

SQL>shutdown immediate

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SQL>startup

ORACLEinstance started.

TotalSystem Global Area 281018368 bytes

FixedSize 2095672 bytes

RedoBuffers 6291456 bytes

Databasemounted.

Databaseopened.

SQL>select file#,status,checkpoint_change# from v$datafile_header order by 1;

FILE# STATUS CHECKPOINT_CHANGE#

----------------- ------------------

1 ONLINE 380235

2 ONLINE 380235

3 ONLINE 380235

4 ONLINE 380235

5 OFFLINE 379951

SQL>select* from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

-------------------- ---------- ---------- ---------- --- ------------

FIRST_CHANGE#FIRST_TIME

-------------------------

380398 02-AUG-18

380400 02-AUG-18

380402 02-AUG-18

切换一下日志：

SQL>alter system switch logfile;

Systemaltered.

SQL>select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

-------------------- ---------- ---------- ---------- --- ------------

FIRST_CHANGE#FIRST_TIME

-------------------------

380416 02-AUG-18

380400 02-AUG-18

SQL>alter database datafile '/u01/app/ora10g/oradata/testdb/test.dbf' online;

alterdatabase datafile '/u01/app/ora10g/oradata/testdb/test.dbf' online

*

ERRORat line 1:

ORA-01113:file 5 needs media recovery if it was restored from backup, or END

BACKUPif it was not

ORA-01110:data file 5: '/u01/app/ora10g/oradata/testdb/test.dbf'

尝试恢复无果：

SQL> recover datafile 5;

ORA-00279:change 380354 generated at 08/02/2018 14:47:31 needed for thread 1

ORA-00289:suggestion :

/u01/app/ora10g/flash_recovery_area/TESTDB/archivelog/2018_08_02/o1_mf_1_27_%u_.a

rc

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

Specifylog: {=suggested | filename | AUTO | CANCEL}

cancel

Mediarecovery cancelled.

SQL>alter database datafile '/u01/app/ora10g/oradata/testdb/test.dbf' online;

alterdatabase datafile '/u01/app/ora10g/oradata/testdb/test.dbf' online

*

ERRORat line 1:

ORA-01113:file 5 needs media recovery if it was restored from backup, or END

BACKUPif it was not

ORA-01110: data file 5:'/u01/app/ora10g/oradata/testdb/test.dbf'

注：sequence已经过去，无法执行恢复，所以这里只能使用bbed修改文件头了。归档以及备份真的很重要。

下面通过BBED恢复

记得修改前备份所有的数据文件！！！！

BBED> info

File# Name Size(blks)

----- ---- ----------

1 /u01/app/ora10g/oradata/testdb/system01.dbf 38400

2 /u01/app/ora10g/oradata/testdb/undotbs01.dbf 35200

3 /u01/app/ora10g/oradata/testdb/sysaux01.dbf 15360

4 /u01/app/ora10g/oradata/testdb/users01.dbf 640

5 /u01/app/ora10g/oradata/testdb/test.dbf 2560

查看数据文件5的信息：这是异常文件信息

BBED> set file 5 block 1

FILE# 5

BLOCK# 1

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4kscnbas @484 0x0005cdc2 ------数据文件头的SCN低4字节值

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x3a991b03

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4kcrbaseq @500 0x0000001b ---数据文件当前写的redolog sequence。

ub4kcrbabno @504 0x0000315b ---数据文件当前写的redolog的block号

ub2 kcrbabof @508 0x0010

ub1 kcvcpetb[0] @512 0x02

ub1 kcvcpetb[1] @513 0x00

ub1 kcvcpetb[2] @514 0x00

ub1 kcvcpetb[3] @515 0x00

ub1 kcvcpetb[4] @516 0x00

ub1 kcvcpetb[5] @517 0x00

ub1 kcvcpetb[6] @518 0x00

ub1 kcvcpetb[7] @519 0x00

查看正常数据文件信息：以4号文件为例

BBED> set file 4 block 1

FILE# 4

BLOCK# 1

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x0005cdf2

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x3a991b3a

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub2 kcrbabof @508 0x0010

ub1 kcvcpetb[0] @512 0x02

ub1 kcvcpetb[1] @513 0x00

ub1 kcvcpetb[2] @514 0x00

ub1 kcvcpetb[3] @515 0x00

ub1 kcvcpetb[4] @516 0x00

ub1 kcvcpetb[5] @517 0x00

ub1 kcvcpetb[6] @518 0x00

ub1 kcvcpetb[7] @519 0x00

BBED> d /v offset 484 count 16

File: /u01/app/ora10g/oradata/testdb/users01.dbf(4)

-------------------------------------------------------

f2cd0500 0000af6d 3a1b993a 01008b00 l.......m:..:....

BBED> set file 5 block 1

FILE# 5

BLOCK# 1

BBED>modify/x f2cd05 offset 484

File: /u01/app/ora10g/oradata/testdb/test.dbf(5)

------------------------------------------------------------------------

BBED> d /v offset 484 count 16

File: /u01/app/ora10g/oradata/testdb/test.dbf(5)

-------------------------------------------------------

BBED>modify/x 1B offset 500

File: /u01/app/ora10g/oradata/testdb/test.dbf(5)

------------------------------------------------------------------------

BBED>sum apply

Check value for File 5, Block 1:

current = 0x5ea6, required = 0x5ea6

重启数据库后再次核查：

BBED> set file 5 block 1

FILE# 5

BLOCK# 1

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x0005d1a4

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x3a991fef

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbabno @504 0x0000096a

ub2 kcrbabof @508 0x0010

ub1 kcvcpetb[0] @512 0x02

ub1 kcvcpetb[1] @513 0x00

ub1 kcvcpetb[2] @514 0x00

ub1 kcvcpetb[3] @515 0x00

ub1 kcvcpetb[4] @516 0x00

ub1 kcvcpetb[5] @517 0x00

ub1 kcvcpetb[6] @518 0x00

ub1 kcvcpetb[7] @519 0x00

BBED> set file 4 block 1

FILE# 4

BLOCK# 1

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x0005d1a4

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x3a991fef

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbabno @504 0x0000096a

ub2 kcrbabof @508 0x0010

ub1 kcvcpetb[0] @512 0x02

ub1 kcvcpetb[1] @513 0x00

ub1 kcvcpetb[2] @514 0x00

ub1 kcvcpetb[3] @515 0x00

ub1 kcvcpetb[4] @516 0x00

ub1 kcvcpetb[5] @517 0x00

ub1 kcvcpetb[6] @518 0x00

ub1 kcvcpetb[7] @519 0x00

在数据库中验证：

SQL> select file#,checkpoint_change#from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 381348

2 381348

3 381348

4 381348

5 381348

SQL> select file#,checkpoint_change#from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 381348

2 381348

3 381348

4 381348

5 381348

SQL> select * from ies_ms.test;

ID NAME

---------- ------------

1 colin

2 janey

3 tom

4 adele

至此数据库已恢复，完成后记得逻辑导出所有用户数据。