 |
|
Oracle Monitoring Redo Log Status
|
Chapter 6 Oracle Tablespace and File
Internals Scripts
Monitoring Redo Log Status
Redo log status should be monitored to
determine which logs are in use and whether there are any odd status
codes, such as stale log indications or indications of corrupt redo
logs. The log files can have the following status values:
-
USED. Indicates either that a log has just been
added (never used), or that a RESETLOGS command has been issued.
-
CURRENT. Indicates a valid log that is in use.
-
ACTIVE. Indicates a valid log file that is not
currently in use.
-
CLEARING. Indicates a log is being re-created
as an empty log due to DBA action.
-
CLEARING CURRENT. Means that a current log is
being cleared of a closed thread. If a log stays in this status, it
could indicate there is some failure in the log switch.
-
INACTIVE. Means that the log is no longer
needed for instance recovery but may be needed for media recovery.
The v$logfile table has a status indicator that
gives these additional codes:
-
INVALID. File is inaccessible.
-
STALE. File contents are incomplete (such as
when an instance is shut down with SHUTDOWN ABORT or due to a system
crash).
-
DELETED. File is no longer used.
The script below provides some basic
information on log status. The listing shows an example of output
from the log_stat.sql script.
log_stat.sql
COLUMN first_change# FORMAT 99999999 HEADING Change#
COLUMN group# FORMAT 9,999 HEADING Grp#
COLUMN thread# FORMAT 999 HEADING Th#
COLUMN sequence# FORMAT 999,999 HEADING Seq#
COLUMN members FORMAT 999 HEADING Mem
COLUMN archived FORMAT a4 HEADING Arc?
COLUMN first_time FORMAT a21 HEADING 'Switch|Time'
BREAK ON thread#
SET PAGES 60 LINES 131 FEEDBACK OFF
ttitle 'Current Redo Log Status'
SPOOL log_stat
SELECT
thread#,
group#,
sequence#,
bytes,
members,
archived,
status,
first_change#,
TO_CHAR(first_time, 'DD-MM-YYYY HH24:MI:SS') first_time
FROM
sys.v_$log
ORDER BY
thread#,
group#;
SPOOL OFF
PAUSE Press Enter to continue
SET PAGES 22 LINES 80 FEEDBACK ON
CLEAR BREAKS
CLEAR COLUMNS
TTILE OFF
Here is a sample listing:
Switch
Th# Grp# Seq#
BYTES Mem Arc? STATUS Change# Time
--- ----
----- ------- -------- -------- ------- ------------------
1 1
4,489 1048576 2 NO INACTIVE 719114 15-JUN-97
16:54:23
2
4,490 1048576 2 NO INACTIVE 719117 15-JUN-97
16:56:10
3
4,491 1048576 2 NO CURRENT 719120 15-JUN-97
17:02:22
The Oracle script collection contains more than 600 working Oracle scripts. You can download them immediately at this link:
|
