v$archive_log tips Oracle Database Tips by Donald BurlesonDecember 3, 2015

The v$archived_log view on the Oracle instance gives comprehensive information on the log transfer service and the log apply service operations. The script arch_log.sql from the Oracle code depot  queries v$archived_log and gives the following output showing the status of archive redo logs on the standby database:

select
   thread#,
   sequence#,
   applied,
   registrar
from   Oracle code depot
   v$archived_log;

   THREAD#  SEQUENCE# APP REGISTR
---------- ---------- --- -------
         1        585 YES RFS
         1        586 YES RFS
         1        587 NO  RFS
         1        588 NO  RFS
         1        589 NO  RFS

            TIP - The archived logs that have REGISTRAR='RFS' and APPLIED='YES' can be safely removed from the archive log location of standby site

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 views. This script shows an example of a script to monitor archive log switches:

REM NAME         :log_hist.sql
REM PURPOSE      :Provide info on logs for last 24 hours since last
REM PURPOSE      :log switch
REM USE          :From SQLPLUS
REM Limitations  : None
REM MRA 10/14/01 Updated for Oracle9i
REM

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
START title132 "Log History Report"
SPOOL rep_out\&db\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
WHERE
  a.first_time>
   (SELECT b.first_time-1
   FROM v$loghist b WHERE b.switch_change# =
    (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

Also see these related notes on v$archived_log