[20170307]dg環境下線上日誌損壞12.txt
[20170307]dg環境下線上日誌損壞12.txt
http://blog.itpub.net/267265/viewspace-2134665/
http://blog.itpub.net/267265/viewspace-2134481/
--//前面的連結我測試瞭如果日誌實時傳輸與應用的情況下,主庫的崩潰並且線上日誌刪除的情況下(包括主機的備用日誌)情況下,
--//利用備庫接收日誌來恢復主庫的情況.做一點點總結:
1.將備用日誌複製過來,必須執行如下命令,加入最後應用的scn號.
recover database using backup controlfile until change 13276911099;
2.採用這樣的恢復的資料庫最後scn存在差異,這樣導致open resetlogs時,備庫無法在應用日誌.我的測試採用備庫scn-1的方式解決.
--//當採用如下恢復方式時:
SYS@book> recover database until change 13276911099;
ORA-00279: change 13276910487 generated at 02/28/2017 14:40:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_694_896605872.dbf
ORA-00280: change 13276910487 for thread 1 is in sequence #694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01112: media recovery not started
--//要獲取/mnt/ramdisk/book/redo01.log由於這個檔案已經刪除.無法獲取.
--//由於我備庫日誌,線上日誌大小一致的,是否可以使用備庫當時的接收日誌來代替/mnt/ramdisk/book/redo01.log,欺騙oracle完成recover
--//database 過程呢?本測試僅僅就這樣的情況做一下嘗試:
1.環境:
--//首先還原環境,我包括異常關機的冷備份,複製回來,刪除主庫的線上日誌以及備用日誌.
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ /bin/cp -ar /u01/backup/20170301B/* /mnt/ramdisk/book/ ==>/* 使用冷備庫恢復 */
$ ls /mnt/ramdisk/book/r*.log -1
/mnt/ramdisk/book/redo01.log
/mnt/ramdisk/book/redo02.log
/mnt/ramdisk/book/redo03.log
/mnt/ramdisk/book/redostb01.log
/mnt/ramdisk/book/redostb02.log
/mnt/ramdisk/book/redostb03.log
/mnt/ramdisk/book/redostb04.log
$ /bin/rm /mnt/ramdisk/book/r*.log ==>/* 刪除日誌 */
--//清除前面測試中歸檔日誌,僅僅保留seq#=694的歸檔日誌,因為recover時需要(在我的測試裡面).
$ cd /u01/app/oracle/archivelog/book/
$ rm ....
2.確定備庫當時的接收日誌:
--//備庫執行:
SYS@bookdg> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------ ---------- ------- ------------ -------- ------------ ----- --- ---------- ------------- ------------------- ------------ --------- ------------ -------------------
4 1337401710 1 695 52428800 512 78848 YES ACTIVE 13276910949 2017-02-28 14:40:12 13276911100 2017-02-28 14:42:35
5 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
6 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
7 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SYS@bookdg> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ------ ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 695 52428800 512 1 YES CURRENT 13276910949 2017-02-28 14:40:12 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 YES UNUSED 13276889179 2017-02-27 08:59:01 2.814750E+14
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 0 52428800 512 1 YES UNUSED 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//確定接收日誌的備用日誌是group#=4,/mnt/ramdisk/book/redostb01.log.
3.複製到主機:
$ scp /mnt/ramdisk/book/redostb01.log oracle@192.168.100.78:/mnt/ramdisk/book/redo01.log
oracle@192.168.100.78's password:
redostb01.log 100% 50MB 25.0MB/s 00:02
--//注意這樣因為redo的檔案頭不一樣,oracle不會認為那個檔案group#1的.
4.先恢復看看:
SYS@book> recover database ;
ORA-00279: change 13276910487 generated at 02/28/2017 14:40:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_694_896605872.dbf
ORA-00280: change 13276910487 for thread 1 is in sequence #694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-00341: log 1 of thread 1, wrong log # 4 in header
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-01112: media recovery not started
--//很明顯檔案頭不對.
--//補充說明一點,如果使用好的日誌檔案頭替換備用日誌的檔案頭,測試可以透過的,以下是當時的測試:
$ dd if=/u01/backup/20170301B/redo01.log skip=1 bs=512 count=1 of=/mnt/ramdisk/book/redo01.log conv=notrunc
--//說明實際上redo檔案也與oracle的資料檔案一樣有1個OS的頭,從os看檔案大小:redo大小+512K.一般redo檔案的塊大小512位元組.
$ ls -l /mnt/ramdisk/book/redo01.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-28 14:42:35 /mnt/ramdisk/book/redo01.log
--//建立redo大小50M, 而從os看50*1024*1024+512=52429312.正好對上.
SYS@book> recover database until cancel ;
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.
--//這裡不報錯,應用日誌正常.說明差別在redo檔案頭的位置.另外我一直以為僅僅檔案頭不同,後面的資訊都是一一對應的,實際上不是,
--//大家可以使用md5sum驗證.
--//也就是講透過修改備用日誌檔案頭這種方式是可行的.
5.兩者不同型別日誌對比分析:
SYS@book> alter system dump logfile '/u01/backup/20170301B/redo01.log' validate;
System altered.
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.
--//分別儲存不同的檔案,對比看看:
$ diff a2 a1
1c1
< DUMP OF REDO FROM FILE '/u01/backup/20170301B/redo01.log'
---
> DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
11c11
< Control Seq=36119=0x8d17, File size=102400=0x19000
---
> Control Seq=36141=0x8d2d, File size=102400=0x19000
14c14
< thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
---
> thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x2 eot: 0 dis: 0
21c21
< Disk cksum: 0x95a3 Calc cksum: 0x95a3
---
> Disk cksum: 0xb59c Calc cksum: 0xb59c
27,28c27,28
< Unprotected mode
< Miscellaneous flags: 0x800000
---
> Maximize performance mode
> Miscellaneous flags: 0x822000
--//Control Seq=36119=0x8d17感覺在丟失redo檔案後修改有點難度.先不管這些.
--//為了修改方便建立別名bvi80,這樣每行80列,對應16個字元:
$ alias bvi80='env COLUMNS=80 bvi '
$ bvi80 -b 512 -s 512 /mnt/ramdisk/book/redo01.log => 當前是備用檔案日誌
$ bvi80 -b 512 -s 512 /u01/backup/20170301B/redo01.log => 好的日誌檔案
--//開啟複製並且貼上:
$ diff a1.txt a2.txt
1c1
< 00000200 01 22 00 00 01 00 00 00 B7 02 00 00 00 80 9C B5 ................
--- ~~~~~
> 00000200 01 22 00 00 01 00 00 00 B7 02 00 00 00 80 A3 95 .,..............
3,4c3,4
< 00000220 00 00 00 00 2D 8D 00 00 00 90 01 00 00 02 00 00 ....-...........
~~~~
< 00000230 04 00 02 00 6E D8 B7 4F 00 00 00 00 00 00 00 00 ....n..O........
--- ~~
> 00000220 00 00 00 00 17 8D 00 00 00 90 01 00 00 02 00 00 ................
> 00000230 01 00 02 00 6E D8 B7 4F 00 00 00 00 00 00 00 00 ....n..O........
11c11
< 000002A0 B0 1E 71 35 06 20 0E 00 00 00 00 00 02 00 00 00 ..q5. ..........
--- ~~
> 000002A0 B0 1E 71 35 06 20 0E 00 00 00 00 00 01 00 00 00 ..q5. ..........
13c13
< 000002C0 FF FF FF FF FF FF 00 00 00 00 00 00 00 00 00 02 ................
---
> 000002C0 FF FF FF FF FF FF 00 00 00 00 00 00 01 00 00 02 ................
15c15 ~~
< 000002E0 03 00 00 00 4C BB DB 37 00 00 00 00 00 20 82 00 ....L..7..... ..
---
> 000002E0 03 00 00 00 4C BB DB 37 00 00 00 00 00 00 80 00 ....L..7........
--//注意看下劃線的地方. 偏移14-15位元組(從0算起)是檢查和,0x230對應redo gruop#(我這裡從備用日誌複製過來,group#=4,要修改為1).
--//這樣就可以欺騙oracle認為group#=1.
--//0x224-0x225對應的是Control Seq(0x2d8d) ,後面2個我猜測 偏移0x2AC,0x2CC位置.
--//對應的是hws,eot,這個什麼意思不懂,僅僅找到這個連結http://mbouayoun.developpez.com/dumpredo/提到
--//hws: 0x4 c'est header write seq# ,法文不知道c'est表示什麼?
--//這樣就知道要修改的位置:
--//group# 一定要修改
group# 0x0400 => 0x0100. 偏移0x230
hws 0x0200 => 0x0100 偏移0x2AC
eot 0x0000 => 0x0100 偏移0x2CC
cksum 0x9CB5 => ???
--//說明一點,如果僅僅改group#,檢查和,使用recover database until最後一樣報錯,只有改hws,eot才ok.測試不再貼出.
--//計算檢查和,很簡單就是把改動再次做一次異或操作.我這裡不考慮大小頭問題^_^.因為我算出來直接就修改了.
9cb5
0400
0100
0200
0100
0000
0100
----
9bb5
--//也就是修改檢查和為9bb5 (注:不考慮大小頭問題)>
--//注:我使用微軟自帶的計算器算的.不知道有什麼好方法,再多一點很麻煩的.
--//我使用bvi修改,完成後檢查:
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.
--//檢查完整性ok.
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
VALIDATE ONLY
FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
Activation ID=1337448558=0x4fb7d86e
Control Seq=36141=0x8d2d, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000695, SCN 0x0003175d9565-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low scn: 0x0003.175d9565 (13276910949) 02/28/2017 14:40:12
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.175d9565 (13276910949) 02/28/2017 14:40:12
Disk cksum: 0xb59b Calc cksum: 0xb59b
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Maximize performance mode
Miscellaneous flags: 0x822000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 0
Format ID is 2
redo log key is 12e926f77b40c080dc716e8a264c329f
redo log key flag is 5
Enabled redo threads: 1
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 76Kb in 0.01s => 7.47 Mb/sec
Total redo bytes: 1023Kb Longest record: 2Kb, moves: 0/10 moved: 0Mb (0%)
Longest LWN: 2Kb, reads: 142
Last redo scn: 0x0003.175d95fb (13276911099)
Change vector header moves = 0/23 (0%)
----------------------------------------------
--//注意看~,說明修改正確.
6.開始恢復:
--//萬事具備只欠東方^_^.
SYS@book> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
SYS@book> recover database until cancel;
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=1;;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------ ---------------
1 13276911100 2017-02-28 14:42:35 7 925702 ONLINE 839 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//scn =13276911100(不再是13276911099) , fuzzy = No.視乎redo檔案頭記錄的這些控制某種行為.
--//嘗試open看看,因為有現在日誌,可以嘗試noresetlogs.
SYS@book> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--// /mnt/ramdisk/book/redo03.log不存在.
--//在備庫執行:
SYS@bookdg> alter system dump logfile '/mnt/ramdisk/book/redostb02.log' validate;
System altered.
--//確認這個檔案記錄的是seq#=694的日誌檔案.
$ scp /mnt/ramdisk/book/redostb02.log oracle@192.168.100.78:/mnt/ramdisk/book/redo03.log
oracle@192.168.100.78's password:
redostb02.log 100% 50MB 50.0MB/s 00:01
$ bvi80 -b 512 -s 512 /mnt/ramdisk/book/redo03.log
--//僅僅需要修改0x230處 0x0500 => 0x0300,重新計算檢查和.
2B19
0500
0300
----
2D19
--//這個過程略.
SYS@book> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 63227
Session ID: 232 Serial number: 3
--//alert.log:
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_58093.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'
USER (ospid: 58093): terminating the instance due to error 313
--//group#=2 被刪除,不要緊clear看看.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@book> alter database clear logfile group 2 ;
Database altered.
SYS@book> alter database open ;
Database altered.
--//OK,終於開啟了,驗證看看是否傳輸正常.
SYS@book> alter database clear logfile group 4 ;
Database altered.
SYS@book> alter database clear logfile group 5 ;
Database altered.
SYS@book> alter database clear logfile group 6 ;
Database altered.
SYS@book> alter database clear logfile group 7 ;
Database altered.
7.檢查備庫是否正常.
--//主庫執行:
SYS@book> alter system set log_archive_dest_state_2=defer scope=memory;
System altered.
SYS@book> alter system set log_archive_dest_state_2=enable scope=memory;
System altered.
--//備庫執行:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS 17272 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 17270 IDLE LGWR 3 1 697 40 1 0
ARCH 17231 CLOSING ARCH 4 1 695 1 154 0
MRP0 17274 APPLYING_LOG N/A N/A 1 697 40 102400 0
--//seq#=697都傳輸應用過來,沒有問題.
總結:
--//如果主庫的異常關閉,可以使用備庫的日誌來恢復是不成問題的
1.確定那個備庫日誌接收當前.
2.複製過來:
--//修改組號 0x0400 => 0x0100.
hws 0x0200 => 0x0100
eot 0x0000 => 0x0100
http://blog.itpub.net/267265/viewspace-2134665/
SYS@book> recover database until change 13276911099;
ORA-00279: change 13276910487 generated at 02/28/2017 14:40:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_694_896605872.dbf
ORA-00280: change 13276910487 for thread 1 is in sequence #694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01112: media recovery not started
--//問題依舊.無法繼續下去,缺少seq#=695.複製到/mnt/ramdisk/book/redo01.log看看:
$ cp /u01/app/oracle/archivelog/book/1_695_896605872.dbf /mnt/ramdisk/book/redo01.log
SYS@book> recover database until change 13276911099;
ORA-00283: recovery session canceled due to errors
ORA-00341: log 1 of thread 1, wrong log # 4 in header
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
--//檔案頭識別錯誤,是否可以修改這個欺騙oracle,留下下次測試.
$ mv 1_695_896605872.dbf 1_695_896605872.dbf_yyy
--//我前面的測試recover database until change 13276911099;要找/mnt/ramdisk/book/redo01.log檔案.而我複製備庫的standby 對
--//應日誌覆蓋/mnt/ramdisk/book/redo01.log,提示ORA-00341: log 1 of thread 1, wrong log # 4 in header.
--//是否修改檔案頭告之檔案是log 1 of thread 1.
--//另外重新測試移除歸檔目錄的1_695_896605872.dbf檔案,因為有時候測試open resetlogs開啟.
1.環境:
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//啟動到mount.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 695 52428800 512 1 NO CURRENT 13276910949 2017-02-28 14:40:12 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 693 52428800 512 1 YES INACTIVE 13276889179 2017-02-27 08:59:01 13276910486 2017-02-28 14:40:06
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 694 52428800 512 1 YES ACTIVE 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
2.先測試使用原來的檔案頭覆蓋備用庫的檔案頭:
$ dd if=/u01/backup/20170301B/redo01.log skip=1 bs=512 count=1 of=/mnt/ramdisk/book/redo01.log conv=notrunc
--//說明實際上redo檔案也與oracle的資料檔案一樣有1個OS的頭,從os看檔案大小:redo大小+512K.一般redo檔案的塊大小512位元組.
$ ls -l /mnt/ramdisk/book/redo01.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-28 14:42:35 /mnt/ramdisk/book/redo01.log
--//建立redo大小50M, 而從os看50*1024*1024+512=52429312.正好對上.
SYS@book> recover database until cancel ;
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.
--//這裡不報錯,說明差別在redo檔案頭的位置.另外我一直以為僅僅檔案頭不同,後面的資訊都是一一對應的,實際上不是,大家可以使用md5sum驗證.
3.對比分析:
SYS@book> alter system dump logfile '/u01/backup/20170301B/redo01.log' validate;
System altered.
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.
--//分別儲存不同的檔案,對比看看:
$ diff a2 a1
1c1
< DUMP OF REDO FROM FILE '/u01/backup/20170301B/redo01.log'
---
> DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
11c11
< Control Seq=36119=0x8d17, File size=102400=0x19000
---
> Control Seq=36141=0x8d2d, File size=102400=0x19000
14c14
< thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
---
> thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x2 eot: 0 dis: 0
21c21
< Disk cksum: 0x95a3 Calc cksum: 0x95a3
---
> Disk cksum: 0xb59c Calc cksum: 0xb59c
27,28c27,28
< Unprotected mode
< Miscellaneous flags: 0x800000
---
> Maximize performance mode
> Miscellaneous flags: 0x822000
--//Control Seq=36119=0x8d17感覺在丟失redo檔案後修改有點難度.先不管這些.
--//為了修改方便建立別名bvi80,這樣每行80列,對應16個字元:
$ alias bvi80='env COLUMNS=80 bvi '
$ bvi80 -b 512 -s 512 /mnt/ramdisk/book/redo01.log => 當前是備用檔案日誌
$ bvi80 -b 512 -s 512 /u01/backup/20170301B/redo01.log => 好的日誌檔案
--//開啟複製並且貼上:
$ diff a1.txt a2.txt
1c1
< 00000200 01 22 00 00 01 00 00 00 B7 02 00 00 00 80 9C B5 ................
--- ~~~~~
> 00000200 01 22 00 00 01 00 00 00 B7 02 00 00 00 80 A3 95 .,..............
3,4c3,4
< 00000220 00 00 00 00 2D 8D 00 00 00 90 01 00 00 02 00 00 ....-...........
~~~~
< 00000230 04 00 02 00 6E D8 B7 4F 00 00 00 00 00 00 00 00 ....n..O........
--- ~~
> 00000220 00 00 00 00 17 8D 00 00 00 90 01 00 00 02 00 00 ................
> 00000230 01 00 02 00 6E D8 B7 4F 00 00 00 00 00 00 00 00 ....n..O........
11c11
< 000002A0 B0 1E 71 35 06 20 0E 00 00 00 00 00 02 00 00 00 ..q5. ..........
--- ~~
> 000002A0 B0 1E 71 35 06 20 0E 00 00 00 00 00 01 00 00 00 ..q5. ..........
13c13
< 000002C0 FF FF FF FF FF FF 00 00 00 00 00 00 00 00 00 02 ................
---
> 000002C0 FF FF FF FF FF FF 00 00 00 00 00 00 01 00 00 02 ................
15c15 ~~
< 000002E0 03 00 00 00 4C BB DB 37 00 00 00 00 00 20 82 00 ....L..7..... ..
---
> 000002E0 03 00 00 00 4C BB DB 37 00 00 00 00 00 00 80 00 ....L..7........
--//注意看下劃線的地方. 偏移14-15位元組(從0算起)是檢查和,0x230對應redo gruop#(我這裡從備用日誌複製過來,group#=4,要修改為1).
--//0x224-0x225對應的是Control Seq(0x2d8d) ,後面2個我猜測 偏移0x2AC,0x2CC位置.
--//group# 一定要修改 0x0400 => 0x0100.
hws 0x0200 => 0x0100
eot 0x0000 => 0x0100
--//計算檢查和,很簡單就是把改動再次做一次異或操作.
9cb5
0400
0100
0200
0100
0000
0100
----
9bb5 =>可以使用微軟自帶的計算器算.
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.
--//檢查完整性ok.
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
VALIDATE ONLY
FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
Activation ID=1337448558=0x4fb7d86e
Control Seq=36141=0x8d2d, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000695, SCN 0x0003175d9565-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low scn: 0x0003.175d9565 (13276910949) 02/28/2017 14:40:12
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.175d9565 (13276910949) 02/28/2017 14:40:12
Disk cksum: 0xb59b Calc cksum: 0xb59b
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Maximize performance mode
Miscellaneous flags: 0x822000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 0
Format ID is 2
redo log key is 12e926f77b40c080dc716e8a264c329f
redo log key flag is 5
Enabled redo threads: 1
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 76Kb in 0.01s => 7.47 Mb/sec
Total redo bytes: 1023Kb Longest record: 2Kb, moves: 0/10 moved: 0Mb (0%)
Longest LWN: 2Kb, reads: 142
Last redo scn: 0x0003.175d95fb (13276911099)
Change vector header moves = 0/23 (0%)
----------------------------------------------
SYS@book> recover database until cancel ;
ORA-00279: change 13276910487 generated at 02/28/2017 14:40:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_694_896605872.dbf
ORA-00280: change 13276910487 for thread 1 is in sequence #694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_694_896605872.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_695_896605872.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> recover database until cancel ;
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.
--OK!!
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=1;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
1 13276911100 2017-02-28 14:42:35 7 925702 ONLINE 839 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//scn =13276911100(不再是13276911099) , fuzzy = YES.視乎redo檔案頭記錄的這些控制某種行為.
thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//說明我的測試必須要改這裡,不然使用recover database until cancel ;最後會報錯.
4.繼續:
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@book> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//這個是因為shutdown abort,redo03.log中記錄的檢查點狀態還是STATUS=ACTIVE.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 695 52428800 512 1 NO CURRENT 13276910949 2017-02-28 14:40:12 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 693 52428800 512 1 YES INACTIVE 13276889179 2017-02-27 08:59:01 13276910486 2017-02-28 14:40:06
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 694 52428800 512 1 YES ACTIVE 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//在備庫執行:
SYS@bookdg> alter system dump logfile '/mnt/ramdisk/book/redostb02.log' validate;
System altered.
--//確認這個檔案記錄的是seq#=694的日誌檔案.
$ scp /mnt/ramdisk/book/redostb02.log oracle@192.168.100.78:/mnt/ramdisk/book/redo03.log
oracle@192.168.100.78's password:
redostb02.log 100% 50MB 50.0MB/s 00:01
$ bvi80 -b 512 -s 512 /mnt/ramdisk/book/redo03.log
--//僅僅需要修改0x230處 0x0500 => 0x0300,重新計算檢查和.
2B19
0500
0300
----
2D19
--//其他不改也可以.
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 58093
Session ID: 232 Serial number: 3
--//alert.log:
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_58093.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'
USER (ospid: 58093): terminating the instance due to error 313
--//group#=2 被刪除,不要緊!! alter database clear logfile group 2 ;看看.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@book> alter database clear logfile group 2 ;
Database altered.
SYS@book> alter database open ;
Database altered.
--//OK,終於開啟了,驗證看看是否傳輸正常.
SYS@book> alter database clear logfile group 4 ;
Database altered.
SYS@book> alter database clear logfile group 5 ;
Database altered.
SYS@book> alter database clear logfile group 6 ;
Database altered.
SYS@book> alter database clear logfile group 7 ;
Database altered.
5.檢查備庫是否正常.
--//主庫執行:
SYS@book> alter system set log_archive_dest_state_2=defer scope=memory;
System altered.
SYS@book> alter system set log_archive_dest_state_2=enable scope=memory;
System altered.
--//備庫執行:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS 15403 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 15405 IDLE LGWR 3 1 697 45 1 0
ARCH 14998 CLOSING ARCH 4 1 695 1 154 0
MRP0 15410 APPLYING_LOG N/A N/A 1 697 45 102400 0
--//seq#=697都傳輸應用過來,沒有問題.
總結:
--//如果主庫的異常關閉,可以使用備庫的日誌來恢復是不成問題的
1.確定那個備庫日誌接收當前.
2.備庫複製過來:
--//這樣就知道要修改的日誌檔案頭位置:
--//group# 一定要修改!!
group# 0x0400 => 0x0100. 偏移0x230
hws 0x0200 => 0x0100 偏移0x2AC
eot 0x0000 => 0x0100 偏移0x2CC
cksum 0x9CB5 => ???
--//計算檢查和,很簡單就是把改動再次做一次異或操作.
3.雖然成功了,不具有普遍性,hws,eot表示什麼不懂,而且我看到hws有不是0x0200的情況.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2134816/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20170303]dg環境下線上日誌損壞8.txt
- [20170309]dg環境下線上日誌損壞13.txt
- [20170310]dg環境下線上日誌損壞14.txt
- [20170301]dg環境下線上日誌損壞5.txt
- 線上日誌損壞
- 線上日誌檔案損壞恢復方法
- 前線上日誌檔案損壞與ora-600 [4000]處理
- redo日誌損壞
- 物理DG從庫損壞後的重建
- rac+dg環境刪除歸檔日誌
- ASM 仲裁盤OCR DG損壞，重建步驟ASM
- 當前日誌損壞的案例
- 當前日誌損壞的案例(轉)
- 損壞聯機日誌 恢復
- 非歸檔模式下線上日誌檔案破壞後例項恢復案例模式
- 重做日誌檔案損壞測試
- 聯機日誌檔案損壞問題
- INACTIVE日誌組損壞的修復
- 聯機日誌損壞恢復實驗
- 損壞聯機日誌的恢復方法
- 當前日誌組全部損壞的恢復
- 當前聯機日誌檔案損壞
- 每天日誌上T的環境用duplicate做DG的注意事項
- Oracle DG環境中的管理操作Oracle
- RAC環境下的redo日誌組重建
- linux下修復磁碟損壞Linux
- DG環境下重新構建物理備庫oracle12COracle
- Oracle 線上日誌管理Oracle
- Oracle RAC 環境下的連線管理Oracle
- downstream環境下archive程式停止傳輸日誌Hive
- 控制檔案損壞重建實驗（下）
- win下oracle9201當前日誌組損壞的修復過程Oracle
- 03【線上日誌分析】之hadoop-2.7.3編譯和搭建叢集環境(HDFS HA,Yarn HA)Hadoop編譯Yarn
- 非線上日誌丟失
- RAC 線上日誌的管理
- Window離線環境下如何安裝pyhanlpHanLP
- 【RAC】RAC環境下歸檔日誌格式約定
- windows環境下Django環境搭建WindowsDjango