 |
|
Monitoring Redo Log Switches
|
Chapter 6 Oracle Tablespace and File
Internals Scripts
Monitoring Redo Log
Switches
In addition to the alert logs, the frequency of
log switches can also be monitored via the v$log_history and v$archived_log DPTs. A script using these
DPTs for that purpose is shown in below. An example of output from
an archive log switch script is also shown below.
log_hist.sql
COLUMN
thread# FORMAT 999 HEADING 'Thrd#'
COLUMN
sequence# FORMAT 99999 HEADING 'Seq#'
COLUMN
first_change# HEADING 'SCN Low#'
COLUMN
next_change# HEADING 'SCN High#'
COLUMN
archive_name FORMAT a50 HEADING 'Log File'
COLUMN
first_time FORMAT a20 HEADING 'Switch Time'
COLUMN
name FORMAT a30 HEADING 'Archive Log'
SET LINES 132
FEEDBACK OFF VERIFY OFF
ttitle "Log
History Report"
SPOOL
log_hist
REM
SELECT
X.recid,
a.thread#,
a.sequence#,
a.first_change#,
a.switch_change#,
TO_CHAR(a.first_time,'DD-MON-YYYY
HH24:MI:SS') first_time,
x.name
FROM
v$loghist a,
v$archived_log x
SEE CODE DEPOT FOR FULL SCRIPT
(SELECT MAX(c.switch_change#) FROM
v$loghist c)) AND
x.recid(+)=a.sequence#;
SPOOL OFF
SET LINES 80 VERIFY ON FEEDBACK ON
CLEAR COLUMNS
TTITLE OFF
PAUSE Press Enter to continue
Here is a sample listing:
RECID Thrd# Seq# SCN Low# SWITCH_CHANGE#
Switch Archive
Time Log
---- ----- ---- -------- --------------
----------- -------
1 8
375520 409741 05-SEP-2001 08:18:06
The Oracle script collection contains more than 600 working Oracle scripts. You can download them immediately at this link:
|
