How to find archive log history

archive log files

How to find archive log history

Before explaining the details about How to find archive log history,it will be good to understand the archive log and archive related view in database

General Archive log information

So what is archive log in Oracle database

archive log files

What Is the Archived Redo Log?
1) We all know that redo logs stored the redo information and redo log   files are in circular fashion.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. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind.This process is only possible if the database is running in ARCHIVELOG mode You can choose automatic or manual archiving.

2) 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.

V$DATABASE
Shows if the database is in ARCHIVELOG or NOARCHIVELOG mode and if MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG
Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.

 

Some queries using these views

To see the current archiving mode, query the V$DATABASE view:
SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
————
NOARCHIVELOG

The following query returns information about the online redo log file from the
control file:

SVRMGR> select group#, sequence#, bytes, members, status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
———- ———- ———- ———- —————-
1 41 20971520 1 INACTIVE
2 42 20971520 1 CURRENT
2 rows selected.
The following query returns information about all members of a group:

SVRMGR> select * from v$logfile;
GROUP# STATUS MEMBER
———- ——- ————————————————————-
1 /u03/databases/TEST/redologs/TEST_redolog01.dbf
2 /u03/databases/TEST/redologs/TEST_redolog02.dbf
2 rows selected.

 

One interesting way to get the archive  log information about a instance

SQL> ARCHIVE LOG LIST

Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FLASH
Oldest online log sequence 21160
Next log sequence to archive 21165
Current log sequence 21165

 

V$log_history is the view which can be used to find the latest history of archive log in the system

 

How to find archive log history

The below query tells us the amount of redo generated per day from history
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;

 

Sample output

DAY COUNT of Archive log Sequence MIN# Sequence MAX# DAILY_AVG_MB
5/29/2015 252 706343 706605 53657
5/28/2015 1090 705263 706344 221388
5/27/2015 1086 704181 705262 221388
5/26/2015 995 703197 704182 201728
5/25/2015 865 702431 703197 156672
5/24/2015 586 701545 702433 181452
5/23/2015 983 700564 701546 200908
5/22/2015 799 699665 700564 184115
5/21/2015 925 698742 699666 188620
5/20/2015 953 697822 698745 189030
5/19/2015 716 696904 697821 187596

The below query tells us the hourly distribution per day of archive generated from history

SET LINESIZE 150
set heading on
column “00:00” format 9999
column “01:00” format 9999
column “02:00” format 9999
column “03:00” format 9999
column “04:00” format 9999
column “05:00” format 9999
column “06:00” format 9999
column “07:00” format 9999
column “08:00” format 9999
column “09:00” format 9999
column “10:00” format 9999
column “11:00” format 9999
column “12:00” format 9999
column “13:00” format 9999
column “14:00” format 9999
column “15:00” format 9999
column “16:00” format 9999
column “17:00” format 9999
column “18:00” format 9999
column “19:00” format 9999
column “20:00” format 9999
column “21:00” format 9999
column “22:00” format 9999
column “23:00” format 9999

 

SELECT TO_CHAR(FIRST_TIME, ‘DD/MM’) AS “DAY”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’00’, 1, 0), ’99’)) “00:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’01’, 1, 0), ’99’)) “01:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’02’, 1, 0), ’99’)) “02:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’03’, 1, 0), ’99’)) “03:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’04’, 1, 0), ’99’)) “04:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’05’, 1, 0), ’99’)) “05:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’06’, 1, 0), ’99’)) “06:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’07’, 1, 0), ’99’)) “07:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’08’, 1, 0), ’99’)) “08:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’09’, 1, 0), ’99’)) “09:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’10’, 1, 0), ’99’)) “10:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’11’, 1, 0), ’99’)) “11:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’12’, 1, 0), ’99’)) “12:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’13’, 1, 0), ’99’)) “13:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’14’, 1, 0), ’99’)) “14:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’15’, 1, 0), ’99’)) “15:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’16’, 1, 0), ’99’)) “16:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’17’, 1, 0), ’99’)) “17:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’18’, 1, 0), ’99’)) “18:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’19’, 1, 0), ’99’)) “19:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’20’, 1, 0), ’99’)) “20:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’21’, 1, 0), ’99’)) “21:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’22’, 1, 0), ’99’)) “22:00”
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, ‘HH24′), ’23’, 1, 0), ’99’)) “23:00”
FROM V$LOG_HISTORY WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, ‘DD/MM’) order by TO_CHAR(FIRST_TIME, ‘DD/MM’);

Sample output

DAY 0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
15/9 0 0 0 0 51 28 34 35 41 34 39 32 39 41 36 28 32 27 38 43 39 30 32 26
16/9 27 30 44 27 50 26 31 34 45 31 36 34 46 47 39 31 37 28 42 49 42 33 48 26
17/9 28 27 28 30 49 22 21 25 37 25 29 22 28 20 30 22 28 24 29 29 36 25 32 21
18/9 29 27 97 6 10 0 4 0 0 0 67 90 0 10 17 6 4 6 5 31 28 27 33 47
19/9 31 28 52 33 49 27 52 32 57 39 38 30 43 51 42 30 37 27 40 43 44 32 34 25
20/9 36 29 51 35 58 30 39 34 39 35 46 33 43 51 36 31 43 27 40 38 42 39 41 27
21/9 37 32 51 37 60 28 37 38 41 32 36 34 40 51 40 29 38 28 40 38 45 46 38 25
22/9 32 28 56 32 55 29 38 41 52 38 36 29 43 62 36 28 34 24 37 35 35 32 45 22
23/9 30 48 35 35 53 36 43 50 47 29 55 36 65 42 37 42 35 37 42 41 49 33 35 26
24/9 57 46 58 58 54 29 33 25 39 26 33 23 33 46 31 25 26 26 28 52 38 28 44 28
25/9 27 27 30 24 37 43 30 40 40 23 31 27 41 56 29 24 44 23 30 30 27 29 31 22

If you just want to current day,then below query could be used

set linesize 200
set trimspool on
set feedback off

Column 00 format 999
Column 01 format 999
Column 02 format 999
Column 03 format 999
Column 04 format 999
Column 05 format 999
Column 06 format 999
Column 07 format 999
Column 08 format 999
Column 09 format 999
Column 10 format 999
Column 11 format 999
Column 12 format 999
Column 13 format 999
Column 14 format 999
Column 15 format 999
Column 16 format 999
Column 17 format 999
Column 18 format 999
Column 19 format 999
Column 20 format 999
Column 21 format 999
Column 22 format 999
Column 23 format 999

select trunc(first_time) AS Day,
sum(DECODE(to_char(first_time, ‘HH24′), ’00’, 1, 0)) AS “00”,
sum(DECODE(to_char(first_time, ‘HH24′), ’01’, 1, 0)) AS “01”,
sum(DECODE(to_char(first_time, ‘HH24′), ’02’, 1, 0)) AS “02”,
sum(DECODE(to_char(first_time, ‘HH24′), ’03’, 1, 0)) AS “03”,
sum(DECODE(to_char(first_time, ‘HH24′), ’04’, 1, 0)) AS “04”,
sum(DECODE(to_char(first_time, ‘HH24′), ’05’, 1, 0)) AS “05”,
sum(DECODE(to_char(first_time, ‘HH24′), ’06’, 1, 0)) AS “06”,
sum(DECODE(to_char(first_time, ‘HH24′), ’07’, 1, 0)) AS “07”,
sum(DECODE(to_char(first_time, ‘HH24′), ’08’, 1, 0)) AS “08”,
sum(DECODE(to_char(first_time, ‘HH24′), ’09’, 1, 0)) AS “09”,
sum(DECODE(to_char(first_time, ‘HH24′), ’10’, 1, 0)) AS “10”,
sum(DECODE(to_char(first_time, ‘HH24′), ’11’, 1, 0)) AS “11”,
sum(DECODE(to_char(first_time, ‘HH24′), ’12’, 1, 0)) AS “12”,
sum(DECODE(to_char(first_time, ‘HH24′), ’13’, 1, 0)) AS “13”,
sum(DECODE(to_char(first_time, ‘HH24′), ’14’, 1, 0)) AS “14”,
sum(DECODE(to_char(first_time, ‘HH24′), ’15’, 1, 0)) AS “15”,
sum(DECODE(to_char(first_time, ‘HH24′), ’16’, 1, 0)) AS “16”,
sum(DECODE(to_char(first_time, ‘HH24′), ’17’, 1, 0)) AS “17”,
sum(DECODE(to_char(first_time, ‘HH24′), ’18’, 1, 0)) AS “18”,
sum(DECODE(to_char(first_time, ‘HH24′), ’19’, 1, 0)) AS “19”,
sum(DECODE(to_char(first_time, ‘HH24′), ’20’, 1, 0)) AS “20”,
sum(DECODE(to_char(first_time, ‘HH24′), ’21’, 1, 0)) AS “21”,
sum(DECODE(to_char(first_time, ‘HH24′), ’22’, 1, 0)) AS “22”,
sum(DECODE(to_char(first_time, ‘HH24′), ’13’, 1, 0)) AS “23”
FROM v$log_history
WHERE trunc(FIRST_TIME) = trunc(sysdate – 1)
GROUP BY trunc(first_time);