这篇文字写于2010年，详见原文链接 使用bbed 跳过丢失的归档进行recover datafile
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create tablespace alex datafile '/oracle/product/oradata/red5db/alex01.dbf' size 10M
2 extent management local uniform size 1M
3 segment space management auto;
Tablespace created.
SQL> create table t1 tablespace alex as select * from dba_objects;
Table created.
SQL> create table t2 tablespace alex as select * from dba_users;
Table created.
SQL> create table t3 tablespace alex as select * from dba_users;
Table created.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch1
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch1
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL> select count(*) from t1;
COUNT(*)
----------
50411
SQL> select count(*) from t2;
COUNT(*)
----------
34
SQL> select count(*) from t3;
COUNT(*)
----------
34
SQL> delete from t2 where rownum <2;
1 row deleted.
SQL> delete from t3 where rownum <2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch1
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> delete from t1 where rownum <10001;
10000 rows deleted.
SQL> delete from t2 where rownum <2;
1 row deleted.
SQL> delete from t3 where rownum <2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch1
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@red-db1 ~]$ cd /oracle/arch1
[oracle@red-db1 arch1]$ ls -ltr
-rw-r----- 1 oracle oinstall 6760448 12-20 21:52 1_1_738279142.dbf
-rw-r----- 1 oracle oinstall 1024 12-20 21:52 1_2_738279142.dbf
-rw-r----- 1 oracle oinstall 3072 12-20 21:52 1_3_738279142.dbf
-rw-r----- 1 oracle oinstall 5632 12-20 21:53 1_4_738279142.dbf
-rw-r----- 1 oracle oinstall 4369408 12-20 21:54 1_5_738279142.dbf
-rw-r----- 1 oracle oinstall 2048 12-20 21:54 1_6_738279142.dbf
-rw-r----- 1 oracle oinstall 12800 12-20 21:54 1_7_738279142.dbf
-rw-r----- 1 oracle oinstall 1024 12-20 21:54 1_8_738279142.dbf
[oracle@red-db1 arch1]$ cp /oracle/product/oradata/red5db/alex01.dbf /oracle/product/oradata/red5db/alex01_bak.dbf
[oracle@red-db1 arch1]$ ls -ltr /oracle/product/oradata/red5db/alex01*
-rw-r----- 1 oracle oinstall 10493952 12-20 21:58 /oracle/product/oradata/red5db/alex01.dbf
-rw-r----- 1 oracle oinstall 10493952 12-20 21:58 /oracle/product/oradata/red5db/alex01_bak.dbf
[oracle@red-db1 udump]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Dec 20 21:58:03 2010
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1272912 bytes
Variable Size 109052848 bytes
Database Buffers 109051904 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> delete from t1 where rownum <10001;
10000 rows deleted.
SQL> delete from t2 where rownum <2;
1 row deleted.
SQL> delete from t3 where rownum <2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch1
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL> startup
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1272912 bytes
Variable Size 109052848 bytes
Database Buffers 109051904 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> delete from t1 where rownum <10001;
10000 rows deleted.
SQL> delete from t2 where rownum <2;
1 row deleted.
SQL> delete from t3 where rownum <2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch1
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@red-db1 udump]$ cd /oracle/product/oradata/red5db
[oracle@red-db1 red5db]$ rm alex01.dbf
[oracle@red-db1 red5db]$ mv alex01_bak.dbf alex01.dbf
[oracle@red-db1 udump]$ cd /oracle/arch1
[oracle@red-db1 arch1]$ ls -ltr
total 17028
-rw-r----- 1 oracle oinstall 6760448 Dec 20 21:52 1_1_738279142.dbf
-rw-r----- 1 oracle oinstall 1024 Dec 20 21:52 1_2_738279142.dbf
-rw-r----- 1 oracle oinstall 3072 Dec 20 21:52 1_3_738279142.dbf
-rw-r----- 1 oracle oinstall 5632 Dec 20 21:53 1_4_738279142.dbf
-rw-r----- 1 oracle oinstall 4369408 Dec 20 21:54 1_5_738279142.dbf
-rw-r----- 1 oracle oinstall 2048 Dec 20 21:54 1_6_738279142.dbf
-rw-r----- 1 oracle oinstall 12800 Dec 20 21:54 1_7_738279142.dbf
-rw-r----- 1 oracle oinstall 1024 Dec 20 21:54 1_8_738279142.dbf
-rw-r----- 1 oracle oinstall 4751872 Dec 20 22:00 1_9_738279142.dbf
-rw-r----- 1 oracle oinstall 218112 Dec 20 22:00 1_10_738279142.dbf
-rw-r----- 1 oracle oinstall 523776 Dec 20 22:00 1_11_738279142.dbf
-rw-r----- 1 oracle oinstall 711168 Dec 20 22:00 1_12_738279142.dbf
[oracle@red-db1 arch1]$ rm 1_2_738279142.dbf
[oracle@red-db1 arch1]$ rm 1_9_738279142.dbf
[oracle@red-db1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Dec 20 22:15:04 2010
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover datafile 36;
ORA-00279: change 5068824 generated at 12/20/2010 21:58:30 needed for thread 1
ORA-00289: suggestion : /oracle/arch1/1_9_738279142.dbf <== 该归档已经被我RM掉了
ORA-00280: change 5068824 for thread 1 is in sequence #9
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/arch1/1_9_738279142.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/arch1/1_9_738279142.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> select RTCKP_SCN,RTCKP_TIM,RTCKP_RBA_SEQ,RTCKP_RBA_BNO,RTSEQ from x$kccrt;
RTCKP_SCN RTCKP_TIM RTCKP_RBA_SEQ RTCKP_RBA_BNO RTSEQ
---------------- -------------------- ------------- ------------- ----------
5071949 12/20/2010 22:01:41 13 25960 13 <== 最新的SCN值
###### 使用如下方式查询也可以：######
SQL> select name, to_char(checkpoint_change#,'XXXXXXXXXXXX') from v$datafile where name like '%alex%';
NAME TO_CHAR(CHECK
------------------------------------------------------------ -------------
/oracle/product/oradata/red5db/alex01.dbf 4D644D <== 5071949
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x004d5818
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2c014506
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000009
ub4 kcrbabno @504 0x0000027b
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
下面做一个datafile header的dump 来看看情况:
SQL> alter session set events 'immediate trace name FILE_HDRS level 10';
Session altered.
Tablespace #25 - ALEX rel_fn:36
Creation at scn: 0x0000.004d54d4 12/20/2010 21:51:13
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2c013ee6 scn: 0x0000.004d507b reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2ba3523b scn: 0x0000.0040f2ab prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 12/20/2010 23:40:10
status:0x4 root dba:0x00000000 chkpt cnt: 10 ctl cnt:9
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.0022584d 12/20/2010 21:58:30
BBED> modify /x 0a offset 500
File: /oracle/product/oradata/red5db/alex01.dbf (36)
Block: 1 Offsets: 500 to 1011 Dba:0x09000001
------------------------------------------------------------------------
0a000000 7b020000 1000a32b 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 0a000a00 0a000100 00000000 00000000
00000000 02000009 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 36, Block 1:
current = 0x667a, required = 0x667a
SQL> recover datafile 36;
ORA-00279: change 5067988 generated at 12/20/2010 21:58:30 needed for thread 1
ORA-00289: suggestion : /oracle/arch1/1_160_738279142.dbf
ORA-00280: change 5067988 for thread 1 is in sequence #160
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-01013: user requested cancel of current operation
SQL> recover datafile 36;
ORA-00279: change 5067988 generated at 12/20/2010 21:58:30 needed for thread 1
ORA-00289: suggestion : /oracle/arch1/1_10_738279142.dbf
ORA-00280: change 5067988 for thread 1 is in sequence #10
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00326: log begins at change 5069504, need earlier change 5067988
ORA-00334: archived log: '/oracle/arch1/1_10_738279142.dbf'
BBED> d /v
File: /oracle/product/oradata/red5db/alex01.dbf (36)
Block: 1 Offsets: 484 to 995 Dba:0x09000001
-------------------------------------------------------
d4544d00 00004d00 0645012c 0100012c l .TM...M..E.,...,
0a000000 7b020000 1000a32b 02000000 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 ................
00000000 00000000 00000000 00000000 l ................
0a000a00 0a000100 00000000 00000000 l ................
00000000 02000009 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 ................
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 ................
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 ................
<16 bytes per line>
BBED> modify /x c05a4d
File: /oracle/product/oradata/red5db/alex01.dbf (36)
Block: 1 Offsets: 484 to 995 Dba:0x09000001
------------------------------------------------------------------------
c05a4d00 00004d00 0645012c 0100012c 0a000000 7b020000 1000a32b 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0a000a00 0a000100 00000000 00000000 00000000 02000009 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 36, Block 1:
current = 0x686e, required = 0x686e
下面我们来看看能恢复不了呢？
SQL> recover datafile 36;
ORA-00279: change 5069504 generated at 12/20/2010 21:58:30 needed for thread 1
ORA-00289: suggestion : /oracle/arch1/1_10_738279142.dbf
ORA-00280: change 5069504 for thread 1 is in sequence #10
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
哈哈 终于可以恢复了。。。。
SQL> alter database open;
Database altered.
SQL> show user
USER is "SYS"
SQL> select count(*) from t1;
COUNT(*)
----------
40411
SQL> select count(*) from t2;
COUNT(*)
----------
32
SQL> select count(*) from t3;
COUNT(*)
----------
32
发表回复