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.