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.

Monitoring the rate of Redo Log Generation

If you're running in archivelog mode, it is good to know the amount of redo generated for your database on a daily basis. This information can be useful in determining whether or not your archive log destination is large enough to hold all the log files that must be archived. If, for example, your database is generating 500 megabytes of redo per day and your archive log destination can hold only 2 gigabytes of data, you would need to clear out the archive log destination directory at least every four days.
One way to monitor the rate of redo log generation is to use operating system commands to list all the redo log files generated each day, total those files, and multiply by the file size. Another approach is to query the v$log_history view. The query in the following example returns the number of redo log files started on a per day basis:

SQL> SELECT TRUNC(first_time), COUNT(*)

  2  FROM v$log_history

  3  GROUP BY TRUNC(first_time)

  4  ORDER BY TRUNC(first_time);

TRUNC(FIRST_TIME)     COUNT(*)

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

08-Nov-1999 12:00:00         2

22-Nov-1999 12:00:00         7

23-Nov-1999 12:00:00         1

26-Nov-1999 12:00:00         3

28-Nov-1999 12:00:00         4

Only Useful

This information is useful only if all your log files are the same size. In this example, if the log files are each 10 megabytes, you can see that the rate of redo generation ranges from 20 megabytes (2 * 10 megabytes) to 70 megabytes (7 * 10 megabytes) per day. One caveat: The date in the FIRST_TIME column is not the date that the log file is filled. Rather, it's the date that Oracle starts writing to the log file. Still, the results are generally close enough for the purpose of determining the rate of log file generation.

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.