Redo Log File Information (vlogfile.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script queries Oracle views to provide database information via the vlogfile.sql script.

The Script

rem  vlogfile.sql
rem
ttitle 'Redo Log Files'
rem
col group# format 99 heading 'GROUP'
col status format a7 heading 'STATUS'
col member format a66 heading 'FILE NAME'
rem
select group#, status, member
  from v$logfile;

What This Script Does

This simple but essential script displays information about all redo log files in the database, showing the log group numbers, file status, and physical file paths. It’s fundamental for redo log management, configuration verification, and troubleshooting redo log issues.

Key Features

• Complete Log File Inventory: Shows all redo log files in the database
• Group Organization: Displays which group each file belongs to
• Status Information: Shows current status of each log file
• Physical Locations: Lists complete file paths for each redo log member
• Simple Format: Clean, easy-to-read output

Usage

@vlogfile.sql

No parameters required - shows all redo log files.

Required Privileges

SELECT on V$LOGFILE

Sample Output

                                           Redo Log Files

GROUP STATUS  FILE NAME
----- ------- ------------------------------------------------------------------
    1         /u01/oradata/PROD/redo01a.log
    1         /u02/oradata/PROD/redo01b.log
    2         /u01/oradata/PROD/redo02a.log
    2         /u02/oradata/PROD/redo02b.log
    3         /u01/oradata/PROD/redo03a.log
    3         /u02/oradata/PROD/redo03b.log
    4 INVALID  /u01/oradata/PROD/redo04a.log
    4         /u02/oradata/PROD/redo04b.log

Key Output Columns

• GROUP: Redo log group number
• STATUS: Status of the log file member
• FILE NAME: Complete path to the physical redo log file

Understanding Redo Log Status

Normal Status (Blank)

• Empty status: File is valid and available
• Normal operation: File can be used for redo generation
• Healthy state: No issues with the file

Error Status Values

INVALID

• File problem: Physical file has issues
• Potential causes: Disk failure, file corruption, permission problems
• Impact: Group may still function if other members are valid

STALE

• Out of sync: File is behind other group members
• Recovery needed: File needs to be synchronized
• Temporary state: Usually resolves after log switch

DELETED

• File removed: Physical file has been deleted
• Administrative action: File was intentionally removed
• Recovery required: File needs to be recreated or dropped

Redo Log Configuration Analysis

Group Membership

-- Standard configuration shows:
-- Multiple groups (typically 3-4 minimum)
-- Multiple members per group (2+ for redundancy)
-- Even distribution across storage devices

Redundancy Assessment

-- Each group should have:
-- At least 2 members for redundancy
-- Members on different physical devices
-- Members on different controllers/storage arrays

Storage Distribution

-- Look for:
-- Files spread across multiple mount points
-- Balanced I/O distribution
-- Separation from data files

Common Use Cases

1. Configuration Verification

   • Verify redo log setup after installation
   • Confirm redundancy configuration
   • Validate file locations

2. Troubleshooting

   • Identify failed or invalid redo log files
   • Locate missing or corrupted files
   • Diagnose redo log issues

3. Maintenance Planning

   • Plan redo log file movements
   • Assess storage requirements
   • Schedule redo log maintenance

4. Documentation

   • Document current redo log configuration
   • Create deployment specifications
   • Support disaster recovery planning

Advanced Analysis

Complete Redo Log Information

-- Combine with V$LOG for complete picture:
SELECT l.group#, l.sequence#, l.bytes/1024/1024 mb,
       l.status group_status, lf.member, lf.status file_status
FROM v$log l, v$logfile lf
WHERE l.group# = lf.group#
ORDER BY l.group#, lf.member;

Group Size Analysis

-- Check redo log sizes:
SELECT group#, bytes/1024/1024 size_mb, members
FROM v$log
ORDER BY group#;

Current Redo Activity

-- See which group is currently active:
SELECT group#, sequence#, status, first_change#
FROM v$log
WHERE status = 'CURRENT';

Redo Log Management

Adding Redo Log Members

-- Add members for redundancy:
ALTER DATABASE ADD LOGFILE MEMBER
'/u03/oradata/PROD/redo01c.log' TO GROUP 1;

Dropping Invalid Members

-- Remove invalid or problematic members:
ALTER DATABASE DROP LOGFILE MEMBER
'/u01/oradata/PROD/redo04a.log';

Creating New Groups

-- Add new redo log groups:
ALTER DATABASE ADD LOGFILE GROUP 5
('/u01/oradata/PROD/redo05a.log',
 '/u02/oradata/PROD/redo05b.log') SIZE 1G;

Troubleshooting Redo Log Issues

Invalid Status Resolution

-- Steps to resolve INVALID status:
-- 1. Check physical file existence
-- 2. Verify file permissions
-- 3. Check disk space and errors
-- 4. Consider dropping and recreating member

Missing Files

-- If files are missing:
-- 1. Check if files were accidentally deleted
-- 2. Restore from backup if possible
-- 3. Drop missing members if group has other valid members
-- 4. Add new members to maintain redundancy

Performance Issues

-- If redo performance is poor:
-- 1. Check file locations (different devices?)
-- 2. Verify adequate size to avoid frequent switches
-- 3. Consider adding more groups
-- 4. Check storage subsystem performance

Best Practices

Redundancy

• Minimum 2 members: Each group should have at least 2 members
• Different devices: Members on separate physical storage
• Different controllers: Avoid single points of failure

Sizing

• Adequate size: Large enough to avoid frequent log switches
• Consistent sizing: All groups should be the same size
• Growth planning: Size for expected transaction volume

Location

• Fast storage: Redo logs should be on fast, dedicated storage
• Separate from data: Don’t mix with data files
• Raw devices: Consider raw devices or ASM for performance

Monitoring and Alerts

Regular Checks

-- Monitor for:
-- Invalid or stale log files
-- Missing redundancy (single member groups)
-- Uneven group sizes
-- Files on inappropriate storage

Automated Monitoring

-- Create alerts for:
-- Status != '' (any error status)
-- Groups with only one valid member
-- Log switch frequency issues

Integration with Other Scripts

Complete Redo Analysis

-- Use with related scripts:
-- V$LOG for group status and activity
-- V$LOG_HISTORY for switch patterns
-- Alert log for redo-related errors

Performance Correlation

-- Correlate with:
-- Log file sync wait events
-- Log file parallel write waits
-- Archive log performance

Related Scripts

• vlog.sql - Redo log group information and status
• vslog.sql - Standby redo log information
• varchlog.sql - Archive log analysis
• vcontrol.sql - Control file information

Common Administrative Tasks

Health Check Routine

1. Run vlogfile.sql to check file status
2. Verify all groups have multiple members
3. Check for any INVALID status files
4. Validate file locations and storage distribution

Maintenance Planning

1. Document current configuration
2. Plan for storage changes or upgrades
3. Schedule redo log maintenance windows
4. Prepare for disaster recovery scenarios