数据库断档后,都会有以下问题,如果不用其他恢复手段,会丢失更多数据,是DBA很头疼的一件事情,下面通过特殊手段,强制跳过丢失归档,最大程度恢复！
Oracle版本:11.2.0.4
###模拟数据
create table t1 tablespace alex as select * from dba_objects where rownum < 1000;
create table t2 tablespace alex as select * from dba_users;
create table t3 tablespace alex as select * from dba_users;
alter system switch logfile;
delete from t2 where rownum <2;
delete from t3 where rownum <2;
commit;
alter system switch logfile;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
delete from t1 where rownum <100;
delete from t2 where rownum <2;
delete from t3 where rownum <2;
commit;
alter system switch logfile;
/
/
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
----session 2(模拟备份数据)
[11g@dba 2023_12_11]$ cd /u01/app/11g/oradata/testdb/
[11g@dba testdb]$ ls
alex01.dbf control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf tyy.dbf undotbs01.dbf users01.dbf
[11g@dba testdb]$ cp alex01.dbf alex01.dbf.bak
SQL> startup
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2253944 bytes
Variable Size 1056967560 bytes
Database Buffers 654311424 bytes
Redo Buffers 6795264 bytes
Database mounted.
Database opened.
delete from t1 where rownum < 101;
delete from t2 where rownum <2;
delete from t3 where rownum <2;
commit;
alter system switch logfile;
/
/
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
delete from t2 where rownum <2;
delete from t3 where rownum <2;
delete from t1 where rownum < 101;
commit;
alter system switch logfile;
/
/
SQL> select count(*) from t1;
COUNT(*)
----------
700
SQL> select count(*) from t2;
COUNT(*)
----------
26
SQL> select count(*) from t3;
COUNT(*)
----------
26
SQL> shutdown immediate;
----session 2(模拟误删后restore数据文件)
[11g@dba testdb]$ rm alex01.dbf
[11g@dba testdb]$ mv alex01.dbf.bak alex01.dbf
----session 3(模拟断档)
[11g@dba 2023_12_11]$ mv o1_mf_1_14_lqf8z21o_.arc o1_mf_1_14_lqf8z21o_.bak
[11g@dba 2023_12_11]$ mv o1_mf_1_18_lqf96893_.arc o1_mf_1_18_lqf96893_.bak
SQL> startup mount;
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/11g/oradata/testdb/alex01.dbf'
可以看出来,已经找不到28号归档了,这时候不通过特殊的手段是不会再往下进行的。
##下面通过BBED来查看
BBED> set file 6
FILE# 6
BBED> map /v
File: /u01/app/11g/oradata/testdb/alex01.dbf (6)
Block: 1 Dba:0x01800001
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
struct kcvfhbfh, 20 bytes @0
struct kcvfhhdr, 76 bytes @20
ub4 kcvfhrdb @96
struct kcvfhcrs, 8 bytes @100
ub4 kcvfhcrt @108
ub4 kcvfhrlc @112
struct kcvfhrls, 8 bytes @116
ub4 kcvfhbti @124
struct kcvfhbsc, 8 bytes @128
ub2 kcvfhbth @136
ub2 kcvfhsta @138
struct kcvfhckp, 36 bytes @484
ub4 kcvfhcpc @140
ub4 kcvfhrts @144
ub4 kcvfhccc @148
struct kcvfhbcp, 36 bytes @152
ub4 kcvfhbhz @312
struct kcvfhxcd, 16 bytes @316
sword kcvfhtsn @332
ub2 kcvfhtln @336
text kcvfhtnm[30] @338
ub4 kcvfhrfn @368
struct kcvfhrfs, 8 bytes @372
ub4 kcvfhrft @380
struct kcvfhafs, 8 bytes @384
ub4 kcvfhbbc @392
ub4 kcvfhncb @396
ub4 kcvfhmcb @400
ub4 kcvfhlcb @404
ub4 kcvfhbcs @408
ub2 kcvfhofb @412
ub2 kcvfhnfb @414
ub4 kcvfhprc @416
struct kcvfhprs, 8 bytes @420
struct kcvfhprfs, 8 bytes @428
ub4 kcvfhtrt @444
ub4 tailchk @8188
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00102350 ----这里对应scn 需要修改
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x44dc9758
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000001c ----> 28 这里对应log 序号 需要修改
ub4 kcrbabno @504 0x0000010a
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>
##开始修改,没有28号,改为29号sequence看看
BBED> d /v offset 500 count 4
File: /u01/app/11g/oradata/testdb/alex01.dbf (6)
Block: 1 Offsets: 500 to 503 Dba:0x01800001
-------------------------------------------------------
1c000000 l ....
<16 bytes per line>
BBED> modify /x 1d offset 500
File: /u01/app/11g/oradata/testdb/alex01.dbf (6)
Block: 1 Offsets: 500 to 503 Dba:0x01800001
------------------------------------------------------------------------
1d000000
<32 bytes per line>
BBED> sum apply
Check value for File 6, Block 1:
current = 0x993c, required = 0x993c
####现在从应用28 到29号归档了,但是scn偏小,需要改大
改为16进制,应该改为102470
BBED> d /v offset 484 count 4
File: /u01/app/11g/oradata/testdb/alex01.dbf (6)
Block: 1 Offsets: 484 to 487 Dba:0x01800001
-------------------------------------------------------
50231000 l P#.. ---->++++当前为1,057,616（x86平台都需要翻转后转换）
BBED> modify /x 00702410 offset 484
File: /u01/app/11g/oradata/testdb/alex01.dbf (6)
Block: 1 Offsets: 484 to 487 Dba:0x01800001
------------------------------------------------------------------------
00702410
<32 bytes per line>
BBED> sum apply
Check value for File 6, Block 1:
current = 0xda58, required = 0xda58
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x10247000
###恢复成功,查看数据
可以看到丢了部分数据,正常t1为700,t2 t3都为27,但是库算是成功打开,比数据全部丢失强很多
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
