Oracle 重做日志和归档重做日志之间的大小差异

嗨，我对重做日志和存档重做日志的大小感到困惑

Here I used script for monitoring

# Daily Count and Size of Redo Log Space (Single Instance)
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
   SELECT
   To_Char(First_Time,'YYYY-MM-DD') DAY,
   Count(1) Count#,
   Min(RECID) Min#,
   Max(RECID) Max#
FROM
   v$log_history
GROUP BY
   To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B
;

RESULT

DAY            COUNT#       MIN#       MAX# DAILY_AVG_MB
---------- ---------- ---------- ---------- ------------
2020-09-09          6          1          6          300

But the total size of archive redo logs different

[oracle@localhost archivelog]$ du -h
58M     ./2020_09_09
58M     .
[oracle@localhost archivelog]$ cd 2020_09_09/
[oracle@localhost 2020_09_09]$ du -h
58M     .
[oracle@localhost 2020_09_09]$ ls
o1_mf_1_3_hokmyqvv_.arc  o1_mf_1_4_hokn4s92_.arc  o1_mf_1_5_hoknsz36_.arc  o1_mf_1_6_hol3smy3_.arc
[oracle@localhost 2020_09_09]$ ls -ltr
total 58684
-rw-r----- 1 oracle oinstall 15892992 Sep  9 17:51 o1_mf_1_3_hokmyqvv_.arc
-rw-r----- 1 oracle oinstall   127488 Sep  9 17:54 o1_mf_1_4_hokn4s92_.arc
-rw-r----- 1 oracle oinstall  1089024 Sep  9 18:06 o1_mf_1_5_hoknsz36_.arc
-rw-r----- 1 oracle oinstall 42897408 Sep  9 22:21 o1_mf_1_6_hol3smy3_.arc
[oracle@localhost 2020_09_09]$

重做日志文件的大小保持不变。归档日志文件的大小与复制出来的重做大小成正比。

因此，您不能使用重做日志文件大小来预测存档日志文件大小，就像这样。从v $ archive_log获取实际大小:

select bytes * blocksize / 1024 / 1024 redo_mb
from   v$log;

REDO_MB   
    524288 
    524288 
    524288 

select recid, blocks * block_size  / 1024 / 1024 archive_mb
from   v$archived_log
where  name like '%2020_09_11%'
order  by recid desc;

RECID           ARCHIVE_MB       
      72    55.19189453125 

请注意，在我强制执行日志切换后，重做日志文件的大小仍然相同。但是生成的存档日志要小得多:

alter system archive log current;

select bytes * blocksize / 1024 / 1024 redo_mb
from   v$log;

REDO_MB   
    524288 
    524288 
    524288 

select recid, blocks * block_size  / 1024 / 1024 archive_mb
from   v$archived_log
where  name like '%2020_09_11%'
order  by recid desc;

RECID           ARCHIVE_MB       
      73    14.99902343750 
      72    55.19189453125