[20150529]使用bbed解決丟失的歸檔.txt
[20150529]使用bbed解決丟失的歸檔.txt
-- 以前跟別人探討過這個問題,我個人的觀點透過bbed等手段來跳過丟失的歸檔來恢復存在許多問題.
-- 我以前個人的主張是透過別的手段抽取資料檔案的資料,結合logminer來重新整合資料.但是如果抽取的logminer很多,會非常麻煩.
--實際上無論那種方式,都存在許多問題,這裡透過例子來說明bbed如何解決跳過丟失的歸檔.
1.測試環境建立:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
CREATE TABLESPACE MSSM DATAFILE
'/mnt/ramdisk/test/mssm01.dbf' SIZE 16256K AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
create table tx(id number,name varchar2(20),pad varchar2(100)) tablespace mssm ;
insert into tx values (1,'aaaa','xxxx');
insert into tx values (2,'bbbb','yyyy');
insert into tx values (3,'cccc','zzzz');
commit;
create unique index pk_tx on scott.tx(id) tablespace mssm ;
alter table tx add constraint pk_tx primary key (id);
--關閉資料庫,做一個冷備份.
$ cp /mnt/ramdisk/test/mssm01.dbf /home/oracle/mssm/mssm01.dbf_1
--啟動資料庫.
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
2.建立測試資料:
--當前log sequence=32
update tx set name='AAAA' where id=1;
commit ;
alter system archive log current ;
--當前log sequence=33
update tx set pad='XXXX' where id=1;
commit ;
alter system archive log current ;
--當前log sequence=34
update tx set name='BBBB' where id=2;
commit ;
alter system archive log current ;
--當前log sequence=35
insert into tx values (4,'dddd','vvvv');
commit ;
alter system archive log current ;
SCOTT@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- -----
AAAPpDAAGAAAAASAAA 1 AAAA XXXX
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
SCOTT@test> @ &r/lookup_rowid AAAPpDAAGAAAAASAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
64067 6 18 0 6,18 alter system dump datafile 6 block 18 ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 37
Next log sequence to archive 39
Current log sequence 39
-- log sequence=32,33,34,35已經覆蓋,關閉資料庫.
3.再做一個備份(包括arivelog):
$ cp /mnt/ramdisk/test/mssm01.dbf /home/oracle/mssm/mssm01.dbf_2
$ cp -a /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29 /home/oracle/mssm/
4.移除1個archivelog開始測試:
-- 刪除log seq=33.
$ mv o1_mf_1_33_bphmvodr_.arc o1_mf_1_33_bphmvodr_.arc_xxx
$ cp /home/oracle/mssm/mssm01.dbf_1 /mnt/ramdisk/test/mssm01.dbf
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> recover datafile 6 ;
ORA-00279: change 12691855449 generated at 05/29/2015 10:29:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_%u_.arc
ORA-00280: change 12691855449 for thread 1 is in sequence #32
Specify log: {
AUTO
ORA-00279: change 12691855551 generated at 05/29/2015 10:31:04 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_%u_.arc
ORA-00280: change 12691855551 for thread 1 is in sequence #33
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_bphmv8sm_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_bphmvodr_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--由於相應的archive log已經不存在.
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0xf47e58bf
ub2 kscnwrp @488 0x0002
ub4 kcvcptim @492 0x348291e8
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000021
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0000
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
--注意看~部分,現在seq=0x21=33,跳過1個看看!
BBED> p kcvfhckp.u.kcvcprba.kcrbaseq
ub4 kcrbaseq @500 0x00000021
BBED> modify /x 22
..
BBED> p kcvfhckp.u.kcvcprba.kcrbaseq
ub4 kcrbaseq @500 0x00000022
BBED> sum apply
Check value for File 6, Block 1:
current = 0x57ac, required = 0x57ac
--不需要做sum apply,why?還是資料庫開啟的原因.
SYS@test> recover datafile 6;
ORA-00279: change 12691855551 generated at 05/29/2015 10:31:04 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_%u_.arc
ORA-00280: change 12691855551 for thread 1 is in sequence #34
Specify log: {
auto
ORA-00326: log begins at change 12691855559, need earlier change 12691855551
ORA-00334: archived log: '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_bphmw074_.arc'
--還有修改scn的資訊.
12691855559-2^33=4101920967
4101920967 = 0xf47e58c7
12691855551-2^33=4101920959
4101920959 = 0xf47e58bf
--說明我的資料庫ub2 kscnwrp @488 0x0002,要減去2^32*2,也就是2^33.
--這樣修改為0x58bf=>0x58c7
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf47e58bf
BBED> modify /x c758
..
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf47e58c7
BBED> sum apply
Check value for File 6, Block 1:
current = 0x577a, required = 0x577a
SYS@test> recover datafile 6;
ORA-00279: change 12691855559 generated at 05/29/2015 10:31:04 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_%u_.arc
ORA-00280: change 12691855559 for thread 1 is in sequence #34
Specify log: {
auto
ORA-00279: change 12691855566 generated at 05/29/2015 10:31:28 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_35_%u_.arc
ORA-00280: change 12691855566 for thread 1 is in sequence #35
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_bphmw074_.arc' no longer needed for this recovery
ORA-00279: change 12691855572 generated at 05/29/2015 10:31:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_36_%u_.arc
ORA-00280: change 12691855572 for thread 1 is in sequence #36
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_35_bphmw7oj_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
--OK,恢復完成.
SYS@test> alter database open read only;
Database altered.
SYS@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- -------
AAAPpDAAGAAAAASAAA 1 AAAA xxxx
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
--對比前面的情況:
SCOTT@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- -----
AAAPpDAAGAAAAASAAA 1 AAAA XXXX
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
--可以發現ID=1的,PAD='XXXX'的修改丟失了.依舊是小寫的'xxxx'.所以講這樣恢復導致資料"混亂"
5.重複測試,這次刪除log seq=32看看會出現什麼情況呢?
--關閉資料庫,還原現場:
$ cp /home/oracle/mssm/mssm01.dbf_1 /mnt/ramdisk/test/mssm01.dbf
$ cd /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29
$ mv o1_mf_1_33_bphmvodr_.arc_xxx o1_mf_1_33_bphmvodr_.arc
$ mv o1_mf_1_32_bphmv8sm_.arc o1_mf_1_32_bphmv8sm_.arc_XXX
--這次移除log seq=32,看看情況如何呢?
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> recover datafile 6;
ORA-00279: change 12691855449 generated at 05/29/2015 10:29:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_%u_.arc
ORA-00280: change 12691855449 for thread 1 is in sequence #32
Specify log: {
auto
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_bphmv8sm_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_32_bphmv8sm_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@test> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--重複bbed的修改過程.
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p kcvfhckp.u.kcvcprba.kcrbaseq
ub4 kcrbaseq @500 0x00000020
BBED> modify /x 21
...
BBED> p kcvfhckp.u.kcvcprba.kcrbaseq
ub4 kcrbaseq @500 0x00000021
BBED> sum apply
Check value for File 6, Block 1:
current = 0x1a0b, required = 0x1a0b
--也沒有變化,難道這部分資訊不參與檢查和運算嗎?
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> recover datafile 6;
ORA-00279: change 12691855449 generated at 05/29/2015 10:29:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_%u_.arc
ORA-00280: change 12691855449 for thread 1 is in sequence #33
Specify log: {
auto
ORA-00326: log begins at change 12691855551, need earlier change 12691855449
ORA-00334: archived log: '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_bphmvodr_.arc'
--還有修改scn的資訊.
12691855551-2^33=4101920959
4101920959 = 0xf47e58bf
12691855449-2^33=4101920857
4101920857 = 0xf47e5859
--修改0x5859 => 0x58bf
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf47e5859
BBED> modify /x bf58
..
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf47e58bf
BBED> sum apply
Check value for File 6, Block 1:
current = 0x1918, required = 0x1918
SYS@test> recover datafile 6;
ORA-00279: change 12691855551 generated at 05/29/2015 10:29:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_%u_.arc
ORA-00280: change 12691855551 for thread 1 is in sequence #33
Specify log: {
auto
ORA-00279: change 12691855559 generated at 05/29/2015 10:31:17 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_%u_.arc
ORA-00280: change 12691855559 for thread 1 is in sequence #34
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_33_bphmvodr_.arc' no longer needed for this recovery
ORA-00279: change 12691855566 generated at 05/29/2015 10:31:28 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_35_%u_.arc
ORA-00280: change 12691855566 for thread 1 is in sequence #35
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_34_bphmw074_.arc' no longer needed for this recovery
ORA-00279: change 12691855572 generated at 05/29/2015 10:31:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_36_%u_.arc
ORA-00280: change 12691855572 for thread 1 is in sequence #36
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_29/o1_mf_1_35_bphmw7oj_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
--再看看結果如何:
SYS@test> alter database open read only;
Database altered.
SYS@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- --------
AAAPpDAAGAAAAASAAA 1 aaaa xxxx
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
--對比前面的情況:
SCOTT@test> select rowid,tx.* from scott.tx;
ROWID ID NAME PAD
------------------ ------------ -------------------- -----
AAAPpDAAGAAAAASAAA 1 AAAA XXXX
AAAPpDAAGAAAAASAAB 2 BBBB yyyy
AAAPpDAAGAAAAASAAC 3 cccc zzzz
AAAPpDAAGAAAAASAAD 4 dddd vvvv
--可以發現ID=1的,name='AAAA',PAD='XXXX'的修改丟失了.依舊是原來的資訊.
--如果講前面的測試僅僅導致資料"混亂",第2次的情況導致兩次修改都丟失了.
--實際上的情況比這些還要複雜.這種修復的意義我個人認為意義不大.但是還是給權衡利弊.
--也就是那段時間的記錄要仔細檢查.
--另外我做了多次,有幾次出現.修復方式也是一樣的,修改scn資訊.
ORA-00600: internal error code, arguments: [2608], [1], [2], [4101877000], [2], [4101877009], [], []
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1676438/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【BBED】丟失歸檔檔案情況下的恢復
- dg丟失歸檔，使用rman增量備份恢復
- nuget打包檔案丟失如何使用powershell指令碼解決指令碼
- OGG整合抽取模式丟失歸檔處理模式
- 使用RMAN增量備份處理Dataguard因歸檔丟失造成的gap
- Oracle歸檔檔案丟失導致OGG不用啟動Oracle
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- [20181227]bbed的使用問題.txt
- [20180525]丟失審計.txt
- Feign 呼叫丟失Header的解決方案Header
- vcruntime140.dll丟失的解決方法
- [20210901]cygwin下使用bbed.txt
- [20210303]bbed使用小問題.txt
- win10 計算機丟失logmanager怎麼辦 win10丟失log.dll檔案的解決方法Win10計算機
- JavaScript精度丟失原因以及解決方案JavaScript
- RocketMq訊息丟失問題解決MQ
- 高併發下丟失更新的解決方案
- JavaScript中解決計算精度丟失的問題JavaScript
- SpringCloud解決feign呼叫token丟失問題SpringGCCloud
- [20210920]bbed的assign命令.txt
- [20210304]bbed的assign命令.txt
- 測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復資料恢復
- RMAN-ERROR：因為找不到過期和丟失的歸檔日誌而備份失敗Error
- Oracle+Ogg 歸檔丟失 重新導資料建立ogg同步步驟Oracle
- 將企業檔案共享解決方案與資料丟失防護配對
- 伺服器不能啟動，修復後部分檔案丟失怎麼解決伺服器
- [20211018]奇怪的歸檔目的地.txt
- Win10系統下所有字型丟失的解決方法Win10
- 香港伺服器資料丟失怎麼解決？伺服器
- 請問 django admin 介面 css 丟失解決辦法？DjangoCSS
- AWS建立AMI映像資料丟失解決辦法
- RocketMQ訊息丟失解決方案：事務訊息MQ
- 升級Win10專業版系統後丟失檔案怎麼解決Win10
- 面對oracle ocfs2檔案丟失，你能想到解決辦法有哪些？Oracle
- 計算機提示丟失BSE.ocx檔案如何解決？計算機
- [20231020]rename IDL_UB1$後使用bbed的恢復.txt
- Oracle dg歸檔同步失敗Oracle
- vuex頁面重新整理資料丟失的解決辦法Vue
- [20231008]bbed探究lob段.txt