Skip to content
Oracle DBA Scripts & Database Utilities

Control File Information (vcontrol.sql)

What This Script Does

Section titled “What This Script Does”

This script provides essential information about Oracle control files:

  • Lists all control file locations
  • Shows the status of each control file
  • Helps verify control file configuration and health
  • Essential for database health checks and recovery planning

The Script

Section titled “The Script”
rem  vcontrol.sql
rem
ttitle 'Control Files'
rem
col status format a7 heading 'STATUS'
col name format a72 heading 'CONTROL FILE NAME'
rem
select status, name
  from v$controlfile;

Usage

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

Required Privileges

Section titled “Required Privileges”
SELECT ON V$CONTROLFILE

Sample Output

Section titled “Sample Output”
Control Files


STATUS  CONTROL FILE NAME
------- ------------------------------------------------------------------------
        /u01/app/oracle/oradata/ORCL/control01.ctl
        /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
        /u01/app/oracle/oradata/ORCL/control03.ctl

Key Output Columns

Section titled “Key Output Columns”
  • STATUS - Control file status (usually blank for normal)
  • CONTROL FILE NAME - Full path to control file location

Understanding the Output

Section titled “Understanding the Output”

Status Values:

  • Blank - Control file is online and functioning normally
  • INVALID - Control file has issues or is corrupted
  • STALE - Control file is out of sync (rare)

Best Practices:

  • Should have multiple control files (minimum 2, recommended 3)
  • Control files should be on separate disks/storage
  • All control files should show blank status

Common Use Cases

Section titled “Common Use Cases”

Health Check

@vcontrol.sql
-- Verify all control files are present and healthy

Recovery Planning

@vcontrol.sql
-- Document control file locations for backup/recovery

Database Setup Verification

@vcontrol.sql
-- Confirm proper control file distribution

Troubleshooting

Section titled “Troubleshooting”

Missing Control Files:

  • Check if files exist at listed locations
  • Verify storage availability
  • Review Oracle alert log for errors

Invalid Status:

  • Indicates control file corruption
  • May require control file recovery
  • Check Oracle documentation for recovery procedures
Section titled “Related Scripts”