使用bbed跳过丢失的归档进行recover datafile
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 使用bbed跳过丢失的归档进行recover datafile
这篇文字写于2010年，详见原文链接 使用bbed 跳过丢失的归档进行recover datafile
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 |
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> ! |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
[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> ! |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
[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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 |
|
1 |
下面做一个datafile header的dump 来看看情况: |
|
1 2 3 |
SQL> alter session set events 'immediate trace name FILE_HDRS level 10'; Session altered. |
|
1 2 3 4 5 6 7 8 9 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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 |
|
1 |
下面我们来看看能恢复不了呢？ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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 |
One Response to “使用bbed跳过丢失的归档进行recover datafile”
强大
Leave a Reply
You must be logged in to post a comment.