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