Redo Logs consist of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has associated online redo logs to protect the database in case of an instance failure. Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.
Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.
The Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving. An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group.
If someone asks for daily log switch frequency & size of archives generated daily, we can use the below queries to find the details, Same queries can be executed on RAC setup.
Daily Archive Log Generation
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
Hourly Archive Log Generation
set pages 1000 alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
Sample output of daily Archive Log Generation query:
SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1; DAY THREAD# GB ARCHIVES_GENERATED -------------------- ---------- ---------- ------------------ 10-OCT-2016 00:00:00 1 19 233 11-OCT-2016 00:00:00 1 34 417 12-OCT-2016 00:00:00 1 42 522 13-OCT-2016 00:00:00 1 40 487 14-OCT-2016 00:00:00 1 46 550 15-OCT-2016 00:00:00 1 34 418 16-OCT-2016 00:00:00 1 54 651 17-OCT-2016 00:00:00 1 29 356 18-OCT-2016 00:00:00 1 27 322 19-OCT-2016 00:00:00 1 42 514 20-OCT-2016 00:00:00 1 36 442 21-OCT-2016 00:00:00 1 31 364 22-OCT-2016 00:00:00 1 30 359 23-OCT-2016 00:00:00 1 47 554 24-OCT-2016 00:00:00 1 28 336 25-OCT-2016 00:00:00 1 31 384 26-OCT-2016 00:00:00 1 14 187
Sample output of Hourly Archive Log Generation query:
SQL> select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ; HOUR THREAD# GB ARCHIVES -------------------- ---------- ---------- ---------- 10-OCT-2016 07:00:00 1 0 4 10-OCT-2016 08:00:00 1 2 23 10-OCT-2016 09:00:00 1 2 20 10-OCT-2016 10:00:00 1 0 4 10-OCT-2016 11:00:00 1 1 9 10-OCT-2016 12:00:00 1 1 11 10-OCT-2016 13:00:00 1 0 4 10-OCT-2016 14:00:00 1 0 2 10-OCT-2016 15:00:00 1 0 3 10-OCT-2016 16:00:00 1 0 2 10-OCT-2016 17:00:00 1 0 1 10-OCT-2016 18:00:00 1 1 11 10-OCT-2016 19:00:00 1 1 16 10-OCT-2016 20:00:00 1 1 17 10-OCT-2016 21:00:00 1 4 46 ... [snip]... ............. 25-OCT-2016 15:00:00 1 0 6 25-OCT-2016 16:00:00 1 0 2 25-OCT-2016 17:00:00 1 0 2 25-OCT-2016 18:00:00 1 1 10 25-OCT-2016 19:00:00 1 2 29 25-OCT-2016 20:00:00 1 2 25 25-OCT-2016 21:00:00 1 4 46 25-OCT-2016 22:00:00 1 5 54 25-OCT-2016 23:00:00 1 2 19 26-OCT-2016 00:00:00 1 1 16 26-OCT-2016 01:00:00 1 3 40 26-OCT-2016 02:00:00 1 2 30 26-OCT-2016 03:00:00 1 2 28 26-OCT-2016 04:00:00 1 2 29 26-OCT-2016 05:00:00 1 2 21 26-OCT-2016 06:00:00 1 2 21 26-OCT-2016 07:00:00 1 0 2 385 rows selected.