Lesson 6	Archive log history
Objective	View a list of archived log files

Archive log history

Two views provide helpful information about log files and archive log files. These views are v$log and v$log_history. The v$log view tells you the status of the online redo log files, whereas v$log_history provides information about filled log files.

Viewing the online redo log file status

You can query the v$log view to get a snapshot of the current redo log file status. The v$log view tells you which files have been archived, and also tells you which file is currently being used. For example:

SQL> select group#, sequence#, 

2         archived, status 

3  from v$log;

GROUP# SEQUENCE# ARC STATUS

--------- --------- --- ----------------

1     16031 YES INACTIVE

2     16034 NO  CURRENT

3     16029 YES INACTIVE

4     16030 YES INACTIVE

5     16032 NO  INACTIVE

6     16033 NO  INACTIVE

In this example, log file groups 2, 5, and 6 have not yet been archived. Group 2 is the active group. It won't need to be archived until it is full. Groups 5 and 6 are full and awaiting archival. Note:

If you need to add or remove members from a log file group, you can query v$log to verify that it's not the active group. Oracle cycles through the groups in order, so this would be a good time to modify group 1, because Oracle will cycle through groups 3, 4, 5, and 6 before using 1 again.

Viewing the log history

The v$log_history view returns information about filled redo log groups, regardless of whether they have been archived or not. The following example shows one way to query v$log_history:

SQL> SELECT sequence#, first_change#, first_time

2  FROM v$log_history

3  ORDER BY sequence#;

SEQUENCE# FIRST_CHANGE# FIRST_TIME 

--------- ------------- --------------------

16017        118936 08-Nov-1999 07:16:01

16018        118941 08-Nov-1999 07:22:10

16019        138947 22-Nov-1999 07:07:48

16020        138953 22-Nov-1999 08:55:56

16021        138954 22-Nov-1999 08:55:57

16022        138955 22-Nov-1999 08:55:58

16023        138956 22-Nov-1999 08:55:59

16024        138957 22-Nov-1999 08:55:59

16025        138958 22-Nov-1999 08:56:04

16026        158960 23-Nov-1999 11:10:31

16027        178965 26-Nov-1999 02:35:35

16028        198970 26-Nov-1999 04:03:52

16029        198991 26-Nov-1999 10:40:36

16030        218998 28-Nov-1999 11:06:15

16031        219003 28-Nov-1999 04:08:50

16032        219004 28-Nov-1999 04:09:14

16033        219005 28-Nov-1999 04:17:04

The sequence number that you see here is the log sequence number. This is used as part of the filename when log files are archived. The FIRST_CHANGE# column shows you the system change number (SCN) of the first entry in the log file. The FIRST_TIME column shows you the date and time that the first entry was recorded.
Use v$log_history to track the rate of redo generation for your database.
Take time now that you've learned about the v$log and v$log_history views, and query these views in your database. In the next lesson, you can review what you've learned in this module and take a quiz to see how well you mastered the material.