使用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

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

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180802G1P3T100?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励