[20170309]關於線上日誌與歸檔2.txt
[20170309]關於線上日誌與歸檔2.txt
--//當日志寫滿了,或者執行手工了切換,再或者rman備份時有時也會觸發日誌切換:
alter system switch logfile ;
alter system archive log current ;
--//本文簡單探究日誌歸檔是如何儲存的.探查日誌檔案頭塊.
1.環境:
--//啟動到mount狀態.
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
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 YES INACTIVE 13276910949 2017-02-28 14:40:12 13276931102 2017-03-09 10:01:48
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 696 52428800 512 1 YES INACTIVE 13276931102 2017-03-09 10:01:48 13276931986 2017-03-09 10:02:36
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 697 52428800 512 1 NO CURRENT 13276931986 2017-03-09 10:02:36 2.814750E+14
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.
--//僅僅拿seq#=696 來研究.
$ ls -l /mnt/ramdisk/book/redo02.log /u01/app/oracle/archivelog/book/1_696_896605872.dbf
-rw-r----- 1 oracle oinstall 52429312 2017-03-09 10:02:36 /mnt/ramdisk/book/redo02.log
-rw-r----- 1 oracle oinstall 1626112 2017-03-09 10:02:38 /u01/app/oracle/archivelog/book/1_696_896605872.dbf
--//前面我們探查了日誌檔案的OS塊.下面將繼續探究日誌的檔案頭塊1.
2.取出檔案頭儲存為二進位制以及轉儲格式
$ dd if=/mnt/ramdisk/book/redo01.log bs=512 count=1 skip=1 of=r1.dd
1+0 records in
1+0 records out
512 bytes (512 B) copied, 6.5894e-05 seconds, 7.8 MB/s
$ dd if=/u01/app/oracle/archivelog/book/1_696_896605872.dbf bs=512 count=1 skip=1 of=d1.dd
1+0 records in
1+0 records out
512 bytes (512 B) copied, 5.7258e-05 seconds, 8.9 MB/s
$ xxd -c 16 d1.dd > d1.txt
$ xxd -c 16 r1.dd > r1.txt
$ diff r1.txt d1.txt
1c1
< 0000000: 0122 0000 0100 0000 b802 0000 0080 c7b6 ."............?
---
> 0000000: 0122 0000 0100 0000 b802 0000 0080 d6b6 ."............?
15c15
< 00000e0: 0300 0000 0c4c eb37 0000 0000 0000 8000 .....L?........
---
> 00000e0: 0300 0000 0c4c eb37 0000 0000 1100 8000 .....L?........
--//偏移0x14-0x15是檢查和,對於日誌檔案.僅僅修改了 0xeb-0xef出為0x00008000->0x11008000.
SYS@book> alter system dump logfile '/u01/app/oracle/archivelog/book/1_696_896605872.dbf' validate;
System altered.
DUMP OF REDO FROM FILE '/u01/app/oracle/archivelog/book/1_696_896605872.dbf'
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=36178=0x8d52, File size=102400=0x19000
File Number=2, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000696, SCN 0x0003175de41e-0x0003175de792"
thread: 1 nab: 0xc68 seq: 0x000002b8 hws: 0x3 eot: 0 dis: 0
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low scn: 0x0003.175de41e (13276931102) 03/09/2017 10:01:48
Next scn: 0x0003.175de792 (13276931986) 03/09/2017 10:02:36
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.175de41e (13276931102) 03/09/2017 10:01:48
Disk cksum: 0xb6d6 Calc cksum: 0xb6d6
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 283 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800011
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 8
Format ID is 2
redo log key is 635b658f7133c1a828b05e569341c54e
redo log key flag is 5
Enabled redo threads: 1
END OF REDO DUMP
$ egrep "^DUMP|^ Miscellaneous" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_20294.trc
DUMP OF REDO FROM FILE '/u01/app/oracle/archivelog/book/1_696_896605872.dbf'
Miscellaneous flags: 0x800011
DUMP OF REDO FROM FILE '/u01/app/oracle/archivelog/book/1_695_896605872.dbf'
Miscellaneous flags: 0x802011
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo02.log'
Miscellaneous flags: 0x800000
--//感覺就是前面0x80足夠.
3.再看看別的伺服器:
select 'alter system dump logfile ''' ||name || ''' validate;' from v$archived_log where dest_id=1 and name is not null order by stamp desc,name;
--輸出儲存檔案,執行它:
--//伺服器1:
$ egrep "^ Miscellaneous" xxxxx_ora_29111.trc | uniq -c
100 Miscellaneous flags: 0x800011
--//可以發現僅僅就是修改0x2eb-0x2fe 為0x1100,全是0x800011.
--//伺服器1:
$ egrep "^ Miscellaneous" /u01/app/oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx_ora_16752.trc | sort |uniq -c
78 Miscellaneous flags: 0x800011
13 Miscellaneous flags: 0x800021
--//嗯,存在2種可能.
$ egrep "^DUMP|^ Miscellaneous" /u01/app/oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx1_ora_16752.trc | grep -B1 0x800021 | grep "'" | cut -d" " -f6
'+RECOC1/xxxx/archivelog/2017_03_09/thread_2_seq_2695.564.938158875'
'+RECOC1/xxxx/archivelog/2017_03_08/thread_1_seq_3401.388.938060823'
'+RECOC1/xxxx/archivelog/2017_03_07/thread_2_seq_2690.1139.937974461'
'+RECOC1/xxxx/archivelog/2017_03_04/thread_2_seq_2683.420.937715223'
'+RECOC1/xxxx/archivelog/2017_03_03/thread_2_seq_2681.1194.937628857'
'+RECOC1/xxxx/archivelog/2017_03_01/thread_2_seq_2676.1138.937456023'
'+RECOC1/xxxx/archivelog/2017_02_28/thread_2_seq_2674.1242.937110459'
'+RECOC1/xxxx/archivelog/2017_02_25/thread_2_seq_2668.951.936851263'
'+RECOC1/xxxx/archivelog/2017_02_24/thread_2_seq_2665.1050.936764823'
'+RECOC1/xxxx/archivelog/2017_02_23/thread_1_seq_3359.1085.936714747'
'+RECOC1/xxxx/archivelog/2017_02_23/thread_2_seq_2663.344.936714739'
'+RECOC1/xxxx/archivelog/2017_02_23/thread_1_seq_3357.832.936714663'
'+RECOC1/xxxx/archivelog/2017_02_23/thread_2_seq_2661.907.936689855'
'+RECOC1/xxxx/archivelog/2017_03_09/thread_2_seq_2695.564.938158875'
'+RECOC1/xxxx/archivelog/2017_03_08/thread_1_seq_3401.388.938060823'
'+RECOC1/xxxx/archivelog/2017_03_07/thread_2_seq_2690.1139.937974461'
'+RECOC1/xxxx/archivelog/2017_03_04/thread_2_seq_2683.420.937715223'
'+RECOC1/xxxx/archivelog/2017_03_03/thread_2_seq_2681.1194.937628857'
'+RECOC1/xxxx/archivelog/2017_03_01/thread_2_seq_2676.1138.937456023'
'+RECOC1/xxxx/archivelog/2017_02_28/thread_2_seq_2674.1242.937110459'
'+RECOC1/xxxx/archivelog/2017_02_25/thread_2_seq_2668.951.936851263'
'+RECOC1/xxxx/archivelog/2017_02_24/thread_2_seq_2665.1050.936764823'
'+RECOC1/xxxx/archivelog/2017_02_23/thread_1_seq_3359.1085.936714747'
'+RECOC1/xxxx/archivelog/2017_02_23/thread_2_seq_2663.344.936714739'
'+RECOC1/xxxx/archivelog/2017_02_23/thread_1_seq_3357.832.936714663'
'+RECOC1/xxxx/archivelog/2017_02_23/thread_2_seq_2661.907.936689855'
SELECT distinct creator
FROM v$archived_log
WHERE name IN ('+RECOC1/xxxx/archivelog/2017_03_09/thread_2_seq_2695.564.938158875'
,'+RECOC1/xxxx/archivelog/2017_03_08/thread_1_seq_3401.388.938060823'
,'+RECOC1/xxxx/archivelog/2017_03_07/thread_2_seq_2690.1139.937974461'
,'+RECOC1/xxxx/archivelog/2017_03_04/thread_2_seq_2683.420.937715223'
,'+RECOC1/xxxx/archivelog/2017_03_03/thread_2_seq_2681.1194.937628857'
,'+RECOC1/xxxx/archivelog/2017_03_01/thread_2_seq_2676.1138.937456023'
,'+RECOC1/xxxx/archivelog/2017_02_28/thread_2_seq_2674.1242.937110459'
,'+RECOC1/xxxx/archivelog/2017_02_25/thread_2_seq_2668.951.936851263'
,'+RECOC1/xxxx/archivelog/2017_02_24/thread_2_seq_2665.1050.936764823'
,'+RECOC1/xxxx/archivelog/2017_02_23/thread_1_seq_3359.1085.936714747'
,'+RECOC1/xxxx/archivelog/2017_02_23/thread_2_seq_2663.344.936714739'
,'+RECOC1/xxxx/archivelog/2017_02_23/thread_1_seq_3357.832.936714663'
,'+RECOC1/xxxx/archivelog/2017_02_23/thread_2_seq_2661.907.936689855'
,'+RECOC1/xxxx/archivelog/2017_03_09/thread_2_seq_2695.564.938158875'
,'+RECOC1/xxxx/archivelog/2017_03_08/thread_1_seq_3401.388.938060823'
,'+RECOC1/xxxx/archivelog/2017_03_07/thread_2_seq_2690.1139.937974461'
,'+RECOC1/xxxx/archivelog/2017_03_04/thread_2_seq_2683.420.937715223'
,'+RECOC1/xxxx/archivelog/2017_03_03/thread_2_seq_2681.1194.937628857'
,'+RECOC1/xxxx/archivelog/2017_03_01/thread_2_seq_2676.1138.937456023'
,'+RECOC1/xxxx/archivelog/2017_02_28/thread_2_seq_2674.1242.937110459'
,'+RECOC1/xxxx/archivelog/2017_02_25/thread_2_seq_2668.951.936851263'
,'+RECOC1/xxxx/archivelog/2017_02_24/thread_2_seq_2665.1050.936764823'
,'+RECOC1/xxxx/archivelog/2017_02_23/thread_1_seq_3359.1085.936714747'
,'+RECOC1/xxxx/archivelog/2017_02_23/thread_2_seq_2663.344.936714739'
,'+RECOC1/xxxx/archivelog/2017_02_23/thread_1_seq_3357.832.936714663'
,'+RECOC1/xxxx/archivelog/2017_02_23/thread_2_seq_2661.907.936689855')
AND dest_id = 1;
CREATOR
-------
FGRD
--//噢.明白了0x800021是執行alter system archive log current ;(也許還包含全備份時的情況)手工切換日誌的標識.前面的伺服器1
--//沒有因為是測試庫,是因為完全沒有人執行過這樣的命令.
--//參考連結:http://blog.itpub.net/267265/viewspace-1711428/
--//如果你看gv$archived_log檢視定義:
欄位creator的定義如下,來之底層x$kccal.alflg:
DECODE
(
BITAND (alflg, 4)
,4, 'RFS'
,DECODE
(
BITAND (alflg, 16 + 32 + 64 + 128 + 256)
,16, 'ARCH'
,32, 'FGRD'
,64, 'RMAN'
,128, 'SRMN'
,256, 'LGWR'
,'UNKNOWN'
)
)
--//查詢其中1個.
SYS@xxxx1> select alflg from x$kccal where ALNAM='+RECOC1/xxxx/archivelog/2017_03_07/thread_2_seq_2690.1139.937974461';
ALFLG
----------
34
--//對應二進位制 10010. 32對應就是FGRD.
--//btw,我也看了一個10g的庫.結果如下:
606 Miscellaneous flags: 0x11
257 Miscellaneous flags: 0x21
--//感覺10g的資料庫格式還不一樣.11g做了許多改動.而且Miscellaneous flags: 0x21的creator='FGRD'.再次佐證自己的判斷.
--//有此可以得出結論:
--//11g在歸檔時僅僅修改Miscellaneous flags: 0x800011 或者 0x800021.
0x800011 是正常切換.
0x800021 是手工執行alter system archive log current ;切換.
4.總結:
--//日誌檔案頭在轉儲成歸檔時,修改 Miscellaneous flags: 0x800011或者0x800011.偏移位於0x2eb-0x2ef處.當然檢查和也需要重新計
--//算.當然也可能存在別的可能,我沒有遇到.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2135094/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20170309]關於線上日誌與歸檔1.txt
- [20170310]關於線上日誌與歸檔3.txt
- [20170310]關於線上日誌與歸檔4.txt
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- [20170309]dg環境下線上日誌損壞13.txt
- 關於歸檔日誌的切換測試
- 非歸檔模式下線上日誌檔案破壞後例項恢復案例模式
- 節點2線上日誌生成歸檔日誌在節點1上的初步分析
- 關於歸檔日誌的幾個引數選項
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- 為什麼我的歸檔檔案比我設定的線上日誌的大小要小？
- oracle 歸檔日誌開啟，關閉Oracle
- 關閉和開啟歸檔日誌
- Oracle 12C 新特性：關於歸檔日誌的備份Oracle
- 歸檔日誌大小 與使用rman 備份後的歸檔日誌產生的備份集大小的關係
- 歸檔日誌
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- 日誌檔案和歸檔日誌檔案的關係以及如何切換日誌
- 前線上日誌檔案損壞與ora-600 [4000]處理
- ORACLE 歸檔日誌開啟關閉方法Oracle
- 線上日誌檔案損壞恢復方法
- 關於一個歸檔問題？
- oracle 關於-日誌檔案Oracle
- 歸檔日誌多歸檔路徑 duplex
- oracle歸檔日誌Oracle
- Oracle 歸檔日誌Oracle
- 歸檔日誌挖掘
- PostgreSQL 歸檔日誌SQL
- Oracle 線上日誌管理Oracle
- 線上日誌損壞
- 因歸檔日誌無法歸檔造成的 oracle shutdown immediate無法關閉Oracle
- 控制檔案/歸檔日誌
- 歸檔oracle alert日誌Oracle
- 14. 日誌歸檔
- PostgreSQL歸檔日誌配置SQL
- rman清理歸檔日誌
- archive log 歸檔日誌Hive
- 備份歸檔日誌