Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Vivek Tuning for Row Locks. Oracle
  • Kill a session dynanically using execute immediate Oracle
  • Locktree.sql Oracle
  • How to Use DBMS_STATS to Move Statistics to a Different Database Oracle
  • block_ident.sql Oracle
  • TRUNCATE Privs Oracle
  • get_vmstat_linux Oracle
  • Creating never expiring DB user accounts in Oracle Oracle
  • How to choose Driver table in SQL statement Oracle
  • tab.sql Oracle
  • logminer and my_lbu Oracle
  • Backup and Recovery Scenarios Oracle
  • On solaris 10, “S” link is not part of $ORACLE_HOME/bin/oracle as default. ( For 9.2.0.8) Oracle
  • longtx.sql with the flag whether session is blocking any DML locks or not. Oracle
  • ORA-8031 issue and solution if it is occuring due to truncate. Oracle

find_arc.sql

Posted on 09-Apr-2026 By Admin

Monitoring Oracle Archive Logs: A Quick Guide

As an Oracle DBA, keeping a close eye on your archived redo logs is crucial for maintaining database health and ensuring smooth recovery operations. Today, I’ll share a simple yet powerful script that helps you monitor your archive logs effectively.

The Power of v$archived_log

The v$archived_log view is a goldmine of information about your archived redo logs. It contains details about all archived logs that have been generated by your database, including their sequence numbers, timestamps, and whether they’ve been applied to a standby database.

The Monitoring Script

Here’s a script that gives you a clear view of your archive log activity over the past 24 hours:

-- Set display preferences for better readability
set lines 120 pages 200

-- Format dates for better human readability
alter session set nls_date_format='DD-MON-RRRR:Hh24:MI:SS';

-- Query archive log information from the past day
select
    dest_id,
    FIRST_TIME,
    THREAD#,
    SEQUENCE#,
    ARCHIVED,
    APPLIED
from
    v$archived_log
where
    first_time > sysdate -1
order by
    1,2
/

Understanding the Output

This script returns several key pieces of information:

  1. DEST_ID: The archive destination ID where the log was archived
  2. FIRST_TIME: When the log was first created
  3. THREAD#: The thread number (useful in RAC environments)
  4. SEQUENCE#: The log sequence number
  5. ARCHIVED: Whether the log has been archived (YES/NO)
  6. APPLIED: Whether the log has been applied (relevant for standby databases)

Why This Matters

Regularly monitoring your archive logs helps you:

  • Verify that logs are being archived properly
  • Identify any gaps in your log sequence
  • Monitor the lag between primary and standby databases
  • Plan your backup and recovery strategies effectively

Pro Tip

For a more comprehensive view, you might want to combine this with information from v$archive_dest and v$archive_dest_status to get a complete picture of your archive log destinations and their status.

What archive log monitoring techniques do you find most valuable in your environment? Share your thoughts in the comments!

Oracle, SQL scripts

Post navigation

Previous Post: pvm_pre_change.sql
Next Post: Running PDB on single node in RAC

Related Posts

  • oradebug ipcrm ipcs Oracle
  • Handling LOB data in Oracle Oracle
  • Renaming Oracle Instance Name Oracle
  • Jai Shree Ram Oracle
  • Absolute file number and relative file number Oracle
  • AWR license Oracle

Categories

  • AMC10 (0)
  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Django (0)
  • GIT (1)
  • Linux/Unix (150)
  • MYSQL (5)
  • Oracle (403)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (1)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (18)
  • rman-dataguard (26)
  • shell (151)
  • SQL scripts (349)
  • SQL Server (6)
  • Uncategorized (9)
  • Videos (0)

Recent Posts

  • Tutornet.US — Source of excellent learning14-Oct-2026
  • OCI Infrastructure Patching14-Oct-2026
  • Free API credits for AI models14-Oct-2026
  • AMC1020-Sep-2026
  • Key Management in Oracle: The Core Issue: Missing Master Key12-May-2026
  • SAT Mathematics 10 questions and answer at the end.30-Apr-2026
  • top 10 AI news today30-Apr-2026
  • runon_allpdbs_show_conname.sh23-Apr-2026
  • runon_allcdbs_find_pdbs.sh23-Apr-2026
  • Running PDB on single node in RAC09-Apr-2026

Archives

  • 2026
  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Unix split command to split files Linux/Unix
  • proper cpu stats Linux/Unix
  • Test Case for Inserting Multiple (2.3 Million rows in 26 Seconds) Oracle
  • Proc code Oracle
  • Jai Shree Ram Oracle
  • SQL Server Vs Oracle Architecture difference SQL Server
  • Very clear article about oracle dataguard Oracle
  • This is im telling Kishore Oracle

Copyright © 2026 pvmehta.com.

Powered by PressBook News WordPress theme