Thanks for the question, Kamran.
Asked: September 11, 2020 - 5:49 am UTC
Last updated: September 11, 2020 - 8:27 am UTC
Version: 11.2.0.1
Viewed 1000+ times
# 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 ;
DAY COUNT# MIN# MAX# DAILY_AVG_MB ---------- ---------- ---------- ---------- ------------ 2020-09-09 6 1 6 300
[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]$
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
Kamran Abbasov, September 12, 2020 - 5:54 am UTC
Need more information on Administration? Check out the Administrators guide for the Oracle Database