共計 10194 個字符，預(yù)計需要花費 26 分鐘才能閱讀完成。
本篇內(nèi)容介紹了“如何理解 Oracle 歸檔日志比聯(lián)機重做日志小很多的情況”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！
1：檢查參數(shù) ARCHIVE_LAG_TARGET
ARCHIVE_LAG_TARGET 參數(shù)可以設(shè)置一個時間，通過時間限制，指定數(shù)據(jù)庫強制進行 Log Switch 進行歸檔。如果這個參數(shù)設(shè)置過小，有可能導(dǎo)致聯(lián)機重做日志還沒有寫滿就切換了，這樣就有可能導(dǎo)致歸檔日志遠小于聯(lián)機重做日志(redo log)。
SQL show parameter archive_lag_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 SQL如果參數(shù) archive_lag_target 為 0，那么可以排除這方面的因素。
2：檢查是否存在人為切換 redo log 的可能性。
一些命令可以引起重做日志的切換，具體請見下面
SQL alter system archive log current; # 歸檔命令也會造成日志切換 SQL alter system switch logfile; # 直接切換日志組 RMAN backup archivelog all; RMAN backup database plus archivelog; SELECT TO_CHAR(FIRST_TIME, YYYY-MM-DD HH24:MI:SS), BLOCKS * BLOCK_SIZE / 1024 / 1024, COMPRESSED FROM V$ARCHIVED_LOG;如下案例的截圖如下所示，從截圖看歸檔日志的大小在 31M 左右徘徊。另外，可以看到?jīng)]有啟用歸檔日志壓縮選項(其實 ORACLE 不支持歸檔日志壓縮，這個后面說明)。從歸檔日志大小的規(guī)律可以看出，這個不是某個重做日志切換命令引起的。
3：一些 Bug 引起的，如下 metalink 文檔所示：
BUG 9272059 - REDO LOG SWITCH AT 1/8 OF SIZE DUE TO CMT CPU S BUG 10354739 - REDOLOGSIZE NOT COMPLETLY USED BUG 12317474 - FREQUENT REDO LOG SWITCHES GENERATING SMALL SIZED ARCHIVELOGS BUG 5450861 - ARCHIVE LOGS ARE GENERATED WITH A SMALLER SIZE THAN THE REDO LOG FILES BUG 7016254 - DECREASE CONTROL FILE ENQUEUE WAIT AT LOG SWITCH4：跟 CPU 個數(shù) CPU_COUNT 以及 log_buffer、redo log size 有關(guān)。
歸檔日志的大小是真實的在線日志文件的使用量，也就是在線日志文件切換前其中寫入的內(nèi)容的大小。為了更好的并行，減少沖突，提高并發(fā)，減少 redo allocation latch 的等待，ORACLE 會將 redo buffer 分成若干小的 buffer，每份小的 buffer 叫 strand。按每 16 個 CPU 分一股(strand), 每一股獨立從 redo buffer 以及 redo log 中分配一塊空間，當這一塊 redo buffer 用完，會寫入 redo log 并且繼續(xù)從 redo log 中分配相同大小的空間，如果無法分配空閑空間就會進行日志切換，而不管其他 strand 是否寫完。
如上所示 CPU_COUNT 為 112，那么 112/16=7 , 那么 redo buffer 和 redo log 都可以分成 7 部分
SQL select 112.0/16 from dual; 112.0/16 ---------- 7 SQL select 341655552/1024/1024/7 from dual; --log buffer 341655552/1024/1024/7 --------------------- 46.546875 SQL select 200/7 from dual; --redo log size 200/7 ---------- 28.5714286 SQL當 log buffer 的大小是 325.828125M(341655552)，分成 7 股 (strand) 的話，每個 strand 還是 325.828125M/7=46.546875M。而 redo log 的大小是 200M 的時候，redo log 中的空間會按 strand 的個數(shù)平均分配，也就是每塊 200M/7=28.5714286M。
這樣，當每個 strand 中的內(nèi)容寫到 28M 多左右的時候，就會日志切換，而不是 46M。相當于 log buffer 中的一部分空間被浪費了。所以你看到的歸檔日志基本是 30M 左右大小(其中一股(strand)28.6 再加上其它各股也有部分內(nèi)容寫入，所以歸檔日志的大小就是一個波動的范圍)
其它各個特殊場景分析，可以參考“歸檔日志的大小比在線日志的大小小很多[1]”這篇文章的介紹。當然這篇文章分析過程還忽略了其它各股其實也是有部分數(shù)據(jù)的。這個需要特別注意。
如果你對這個機制不是很清楚，上面鏈接的這篇博客已經(jīng)不可訪問了，下面是我摘抄的部分內(nèi)容到此，方便大家深入理解：
比如 CPU 的個數(shù)是 64 個，則會有 64/16= 4 個 strand
例 1)：當 log buffer 的大小和 redo log file 的大小都是 256M 的時候，則每個 strand 都是 256M/4=64M。每一個 redo log file 被啟用時，會預(yù)先將 redo log file 中的大小分配出 4 個 64M 與 log buffer 對應(yīng)，如圖：
因為 log buffer 的大小和 redo log file 的大小都是 256M，則 redo log file 沒有剩余的未分配的空間了。
每個進程產(chǎn)生的 redo 會分配到 log buffer 上的 1,2,3,4 其中的某一個 strand 上，單個進程只能對應(yīng)一個 strand， 這樣當數(shù)據(jù)庫中只有某些進程 (比如極端的情況，只有某一個進程) 產(chǎn)生的 redo 很多的時候，其中一個 strand 會快速寫滿，比如圖中的 strand 1：
寫滿之后 LGWR 會將 log buffer 中 strand 1 的內(nèi)容寫入到 redo log file 中，并且試圖從 redo log file 中分配一個新的 64M 空間，發(fā)現(xiàn)沒有了，則將所有 strand 中的內(nèi)容寫入日志，并作日志切換。
這樣，可能會導(dǎo)致 redo log file 只寫入了一個 strand 的內(nèi)容，其他部分幾乎是空的，則產(chǎn)生的 archive log 會只接近 64M，而不是 256M。當 CPU_COUNT 很大時，這個差值會更大。
例 2)：當 log buffer 的大小是 256M，而 redo log file 的大小是 1G 的時候，每個 strand 還是 256M/4=64M。每一個 redo log file 被啟用時，會預(yù)先將 redo log file 中的大小分配出 4 個 64M 與 log buffer 對應(yīng)，如圖：
這時，redo log file 中還有 1G-256M=768M 剩余的未分配的空間。
如果 strand 1 寫滿之后，LGWR 會將 log buffer 中 strand 1 的內(nèi)容寫入到 redo log file 中，并且試圖從 redo log file 中分配一個新的 64M 空間，然后不斷往下寫。圖片
直到 redo log file 中再沒有可分配空間了，則將所有 strand 中的內(nèi)容寫入日志，并作日志切換。
例 3)：當 log buffer 的大小是 256M，而 redo log file 的大小是 100M 的時候，每個 strand 還是 256M/4=64M。但是 redo log file 中的空間會按 strand 的個數(shù)平均分配，也就是每塊 100M/4=25M。
這樣，當每個 strand 中的內(nèi)容寫到 25M 的時候，就會日志切換，而不是 64M。相當于 log buffer 中的一部分空間被浪費了。
5：檢查是否開啟歸檔日志壓縮
此功能的目的是在歸檔傳輸?shù)竭h程或者歸檔存儲到磁盤之前進行壓縮，以便減少歸檔日志傳輸?shù)臅r間和占用的磁盤空間。可以使用下面腳本檢查。
SELECT NAME, ARCHIVELOG_COMPRESSION FROM V$DATABASE; SELECT TO_CHAR(FIRST_TIME, YYYY-MM-DD HH24:MI:SS), BLOCKS * BLOCK_SIZE / 1024 / 1024, COMPRESSED FROM V$ARCHIVED_LOG; SQL SELECT NAME, 2 ARCHIVELOG_COMPRESSION 3 FROM V$DATABASE; NAME ARCHIVEL --------- -------- GSPP DISABLED起初，估計很多人都會被這個所迷惑，其實 ORACLE 10g、 11g 都是不支持歸檔日志壓縮的，也沒有明確的官方文檔說明，其實歸檔日志壓縮本來是 ORACLE 10g 計劃引入的新特性，不幸的是這個計劃放棄了，而且 ORACLE 11g 也不支持。
Archive compression was a planned new feature for 10G, but unfortunately it was withdrawn and it is still not available in 11g .This feature is expected in future releases
最后大家可以去 metalink 上看看 Archived redolog is (significant) smaller than the redologfile. (文檔 ID 1356604.1)這篇文章，官方文檔不愧是官方文檔，最全面的闡述了歸檔日志比重做日志小的原因。
Archived redolog is (significant) smaller than the redologfile. (文檔 ID 1356604.1)
There are 2 possible causes for this : 1. Documented and designed behaviour due to explicit forcing an archive creation before the redolog file is full SQL alter system switch logfile; SQL alter system archive log current; RMAN backup archivelog all; RMAN backup database plus archivelog; ARCHIVE_LAG_TARGET : limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the specified amount of time elapses. you can see this aswell in RAC with an idle/low-load instance. 2. Undocumented, but designed behaviour : BUG 9272059 - REDO LOG SWITCH AT 1/8 OF SIZE DUE TO CMT CPU S BUG 10354739 - REDOLOGSIZE NOT COMPLETLY USED BUG 12317474 - FREQUENT REDO LOG SWITCHES GENERATING SMALL SIZED ARCHIVELOGS BUG 5450861 - ARCHIVE LOGS ARE GENERATED WITH A SMALLER SIZE THAN THE REDO LOG FILES BUG 7016254 - DECREASE CONTROL FILE ENQUEUE WAIT AT LOG SWITCH Explanation : As per Bug: 5450861 (closed as Not a Bug): * The archive logs do not have to be even in size. This was decided a very long time ago, when blank padding the archive logs was stopped, for a very good reason - in order to save disk space. * The log switch does not occur when a redo log file is 100% full. There is an internal algorithm that determines the log switch moment. This also has a very good reason - doing the log switch at the last moment could incur performance problems (for various reasons, out of the scope of this note). As a result, after the log switch occurs, the archivers are copying only the actual information from the redo log files. Since the redo logs are not 100% full after the log switch and the archive logs are not blank padded after the copy operation has finished, this results in uneven, smaller files than the original redo log files. There are a number of factors which combine to determine the log switch frequency. These are the most relevant factors in this case: a) RDBMS parameter LOG_BUFFER_SIZE If this is not explicitly set by the DBA then we use a default; at instance startup the RDBMS calculates the number of shared redo strands as ncpus/16, and the size of each strand is 128Kb * ncpus (where ncpus is the number of CPUs in the system). The log buffer size is the number of stands multiplied by the strand size. The calculated or specified size is rounded up to a multiple of the granule size of a memory segment in the SGA. For 11.2 if SGA size = 128GB then granule size is 512MB 64GB = SGA size 128GB then granule size is 256MB 32GB = SGA size 64GB then granule size is 128MB 16GB = SGA size 32GB then granule size is 64MB 8GB = SGA size 16GB then granule size is 32MB 1GB = SGA size 8GB then granule size is 16MB SGA size 1GB then granule size is 4MB There are some minimums and maximums enforced. b) System load Initially only one redo strand is used, ie the number of active redo strands is 1, and all the processes copy their redo into that one strand. When/if there is contention for that strand then the number of active redo strands is raised to 2. As contention for the active strands increases, the number of active strands increases. The maxmum possible number of active redo strands is the number of strands initially allocated in the log buffer. (This feature is called dynamic strands , and there is a hidden parameter to disable it which then allows processes to use all the strands from the outset). c) Log file size This is the logfile size decided by the DBA when the logfiles are created. d) The logfile space reservation algorithm When the RDBMS switches into a new online redo logfile, all the log buffer redo strand memory is mapped to the logfile space. If the logfile is larger than the log buffer then each strand will map/reserve its strand size worth of logfile space, and the remaining logfile space (the log residue) is still available. If the logfile is smaller than the log buffer, then the whole logfile space is divided/mapped/reserved equally among all the strands, and there is no unreserved space (ie no log residue). When any process fills a strand such that all the reserved underlying logfile space for that strand is used, AND there is no log residue, then a log switch is scheduled. Example : 128 CPU s so the RDBMS allocates a log_buffer of size 128Mb containing 8 shared strands of size 16Mb. It may be a bit larger than 128Mb as it rounds up to an SGA granule boundary. The logfiles are 100Mb, so when the RDBMS switches into a new online redo logfile each strand reserves 100Mb/8 = 25600 blocks and there is no log residue. If there is low system load, only one of the redo strands will be active/used and when 25600 blocks of that strand are filled then a log switch will be scheduled - the created archive logs have a size around 25600 blocks. With everything else staying the same (128 cpu s and low load), using a larger logfile would not really reduce the amount of unfilled space when the log switches are requested, but it would make that unfilled space less significant as a percentage of the total logfile space, eg - with a 100Mb logfile, the log switch happens with 7 x 16Mb logfile space unfilled (ie the logfile is 10% full when the log switch is requested) - with a 1Gb logfile, the log switch would happen with 7 x 16Mb logfile space unfilled (ie the logfile is 90% full when the log switch is requested) With a high CPU_COUNT, a low load and a redo log file size smaller than the redolog buffer, you may see small archived log files because of log switches at about 1/8 of the size of the define log file size. This is because CPU_COUNT defines the number of redo strands (ncpus/16). With a low load only a single strand may be used. With redo log file size smaller than the redolog buffer, the log file space is divided over the available strands. When for instance only a single active strand is used, a log switch can already occur when that strand is filled.
“如何理解 Oracle 歸檔日志比聯(lián)機重做日志小很多的情況”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實用文章！
