Skip to content
Oracle DBA Scripts & Database Utilities

Redo Log Thread Information (vthread.sql)

What This Script Does

Section titled “What This Script Does”

This script provides comprehensive information about redo log threads:

  • Shows thread status and configuration
  • Displays instance uptime and open time
  • Lists current redo log group and sequence
  • Shows checkpoint information
  • Essential for RAC environment monitoring

The Script

Section titled “The Script”
rem  vthread.sql
rem
ttitle 'Redo Log Thread Definition'
rem
col thread# format 99 heading 'THREAD'
col status format a6 heading 'STATUS'
col enabled format a8 heading 'ENABLED'
col groups format 9 heading 'GROUPS'
col instance format a8 heading 'INSTANCE'
col days format 999 heading 'DAYS'
col up_time format a8 heading 'UP TIME'
col open_time format a17 heading 'OPEN TIME'
col current_group# format 9 heading 'CURRENT|GROUP'
col sequence# format 99999 heading 'SEQ'
col checkpoint_change# format 99999999999999 heading 'CHECKPOINT|CHANGE'
col checkpoint_time format a17 heading 'CHECKPOINT TIME'
rem
select instance,
       trunc( sysdate - open_time ) days,
       to_char( trunc(sysdate) + ( sysdate - open_time ), 'hh24:mi:ss' ) up_time,
       to_char( open_time, 'mm/dd/yy hh24:mi:ss' ) open_time,
       thread#, status, enabled, groups,
       current_group#, sequence#, checkpoint_change#,
       to_char( checkpoint_time, 'mm/dd/yy hh24:mi:ss' ) checkpoint_time
  from v$thread;

Usage

Section titled “Usage”
-- Basic usage
@vthread.sql

Required Privileges

Section titled “Required Privileges”
SELECT ON V$THREAD

Sample Output

Section titled “Sample Output”
Redo Log Thread Definition


INSTANCE    DAYS UP TIME  OPEN TIME         THREAD STATUS ENABLED  GROUPS CURRENT SEQ   CHECKPOINT      CHECKPOINT TIME
                                                                           GROUP        CHANGE
-------- ------- -------- ----------------- ------ ------ -------- ------ ------- ----- --------------- -----------------
ORCL1          5 120:45:30 06/01/24 08:15:22      1 OPEN   ENABLED       3       2  1234      45678901234 06/06/24 09:00:15
ORCL2          5 120:45:30 06/01/24 08:15:25      2 OPEN   ENABLED       3       1  1235      45678901235 06/06/24 09:00:16

Key Output Columns

Section titled “Key Output Columns”
  • INSTANCE - Instance name (important in RAC)
  • DAYS - Number of days instance has been up
  • UP TIME - Formatted uptime (HH:MM:SS)
  • OPEN TIME - When the instance was opened
  • THREAD - Redo thread number
  • STATUS - Thread status (OPEN/CLOSED)
  • ENABLED - Whether thread is enabled
  • GROUPS - Number of redo log groups for this thread
  • CURRENT GROUP - Active redo log group
  • SEQ - Current log sequence number
  • CHECKPOINT CHANGE - Latest checkpoint SCN
  • CHECKPOINT TIME - Time of latest checkpoint

Understanding the Output

Section titled “Understanding the Output”

Thread Status:

  • OPEN - Thread is active and writing redo
  • CLOSED - Thread is inactive (normal for stopped instances)

Enabled Status:

  • ENABLED - Thread can be opened by an instance
  • DISABLED - Thread cannot be used

RAC Considerations:

  • Each RAC instance typically has its own thread
  • Thread numbers should match instance numbers
  • All active instances should show OPEN status

Common Use Cases

Section titled “Common Use Cases”

RAC Health Check

@vthread.sql
-- Verify all RAC instances have active threads
-- Check thread configuration consistency

Recovery Planning

@vthread.sql
-- Document current log sequence numbers
-- Note checkpoint information for recovery

Performance Monitoring

@vthread.sql
-- Monitor log switching frequency
-- Check for redo log bottlenecks

Instance Monitoring

@vthread.sql
-- Check instance uptime
-- Verify instance startup times

Troubleshooting

Section titled “Troubleshooting”

Missing Threads:

  • In RAC, should see one thread per active instance
  • Missing threads may indicate instance startup issues
  • Check alert logs for thread-related errors

CLOSED Status:

  • Normal for stopped instances
  • Unexpected CLOSED status may indicate instance crash
  • Review instance startup procedures

DISABLED Threads:

  • May indicate configuration issues
  • Check thread creation and enablement
  • Verify RAC setup procedures

Sequence Number Issues:

  • Large gaps between instances may indicate problems
  • Very high sequences may indicate frequent log switches
  • Consider redo log sizing

RAC-Specific Analysis

Section titled “RAC-Specific Analysis”

Thread Assignment:

-- Each instance should have its own thread
-- Thread# often matches instance number
-- All should be ENABLED and OPEN

Load Balancing:

-- Compare sequence numbers across threads
-- Similar sequences indicate balanced load
-- Large differences may show uneven load

Recovery Coordination:

-- Checkpoint SCNs help coordinate recovery
-- All threads should have recent checkpoints
-- Large SCN gaps may indicate issues
Section titled “Related Scripts”