Introduction

Before explaining the details about How to find archive log history, how to find archive log sequence number in oracle, how to check archive log mode in oracle,how to check archive log location in oracle, how to check archive log size in oracle etc, it will be good to understand the archive log and archive related view in the database

General Archive log information

So what is archive log in Oracle database

What Is the Archived Redo Log?
1) We all know that redo logs stored the redo information and redo log files are in a 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
Describe 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.

How to check archive log mode in oracle

SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG

OR

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

How to check online redo log file status

SQL> 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.

How to check all members of a group

SQL> 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.

how to check archive log generation time in oracle

Here are the scripts to find all archive log start and end times in the last week

select sequence#, name,creator, to_char(first_time,'dd-mm-yyyy hh24:mi:ss'), to_char(completion_time,'dd-mm-yyyy hh24:mi:ss')
from v$archived_log
where first_time > sysdate- 7
order by 1

how to check archive log location in oracle

This can be found using any of these methods

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

or

select destination,STATUS from v$archive_dest where statuS='VALID';

or

Show parameter db_recovery_file_dest

How to find archive log history

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

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 the 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 the 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);

how to check archive log size in oracle

The archive log size is the same as the redo log size.

We can check the archive log size generated per day using the below query

select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024/1024 SIZE_GB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;

We can check the archive log size generated per hr using the below query

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';
SQL> select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024/1024 SIZE_GB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;

how to find archive log sequence number in oracle

select sequence#, name,creator, to_char(first_time,'dd-mm-yyyy hh24:mi:ss'), first_change#, next_change#
from v$archived_log
where first_time > sysdate- 7
order by 1

If you want to find the sequence based on the SCN no

select sequence#, name,creator, to_char(first_time,'dd-mm-yyyy hh24:mi:ss'), first_change#, next_change#
from v$archived_log
where <SCN no> between first_change# and next_change#;

Hope you like queries on the archive log and it helps you in day-to-day administration

Also Reads
Oracle DBA scripts
alter system switch logfile
alter table move
How to check datafile Size in Oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12007.htm