DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01172 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01172: Recovery of Thread Stuck at Block of File — Causes, Fixes & Prevention

ORA-01172 occurs during Oracle media recovery or instance recovery when the recovery process halts at a specific block within a specific file and cannot proceed further. This typically means Oracle cannot find or apply the required redo information needed to bring the database to a consistent state. It is one of the more critical recovery errors a DBA can face, often appearing alongside ORA-01194 or ORA-00313.

Top 3 Causes

1. Missing or Corrupt Archive Log Files

The most common cause is a gap in the archive log chain. When Oracle's recovery process cannot locate the next required archive log, it stalls at the last successfully applied block.

-- Check which archive logs are needed for recovery
SELECT * FROM V$RECOVERY_LOG;

-- Review archive log sequence and status
SELECT SEQUENCE#, NAME, APPLIED, STATUS
FROM V$ARCHIVED_LOG
WHERE RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE)
ORDER BY SEQUENCE#;

-- Check current recovery status
SELECT * FROM V$RECOVERY_STATUS;
Enter fullscreen mode Exit fullscreen mode

If archive logs are missing, you must either restore them from backup or perform an incomplete (point-in-time) recovery up to the last available log.

2. Damaged or Missing Online Redo Logs

If the database shut down abnormally and the CURRENT or ACTIVE online redo log group was lost or corrupted, instance recovery will stall because it cannot apply the uncommitted changes stored in those logs.

-- Check online redo log group status
SELECT GROUP#, SEQUENCE#, STATUS, ARCHIVED, MEMBERS
FROM V$LOG
ORDER BY GROUP#;

-- Identify redo log member paths
SELECT GROUP#, MEMBER, STATUS
FROM V$LOGFILE
ORDER BY GROUP#;
Enter fullscreen mode Exit fullscreen mode

In RAC environments, this is thread-specific — if Thread 2's redo logs are lost, only Thread 2's recovery will fail with ORA-01172.

3. Physical Block Corruption in Data Files

Storage-level I/O errors, bad disk sectors, or OS-level failures can corrupt individual data blocks. When the recovery process attempts to apply redo to a corrupt block, it stops and raises ORA-01172.

-- Check for known corrupt blocks
SELECT FILE#, BLOCK#, BLOCKS, CORRUPTION_CHANGE#, CORRUPTION_TYPE
FROM V$DATABASE_BLOCK_CORRUPTION;

-- Validate a specific datafile for corruption
RMAN> VALIDATE DATAFILE 5;

-- Perform block-level media recovery
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 233;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

For missing archive logs — point-in-time recovery via RMAN:

RMAN> RUN {
    SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
}

-- Open with RESETLOGS after incomplete recovery
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

For corrupt online redo logs (last resort only — risk of data loss):

-- Add hidden parameter to PFILE/SPFILE before startup:
-- _allow_resetlogs_corruption=TRUE

STARTUP MOUNT;
RECOVER DATABASE UNTIL CANCEL;
-- Type CANCEL at the prompt
ALTER DATABASE OPEN RESETLOGS;

-- IMPORTANT: Remove the hidden parameter immediately after
-- Set _allow_resetlogs_corruption=FALSE or remove it entirely
Enter fullscreen mode Exit fullscreen mode

⚠️ Warning: Using _allow_resetlogs_corruption can result in data loss and logical corruption. Always consult Oracle Support before using this parameter.

For block corruption using DBMS_REPAIR:

-- Mark corrupt blocks to allow table access
BEGIN
    DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
        SCHEMA_NAME => 'SCOTT',
        OBJECT_NAME => 'EMP'
    );
END;
/

-- Validate full database for corruption
RMAN> VALIDATE DATABASE CHECK LOGICAL;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Automate RMAN Backups with Validation

Schedule daily RMAN backups that include archive logs and always run VALIDATE to confirm backup integrity. Store archive logs in at least two physically separate locations.

RMAN> RUN {
    BACKUP AS COMPRESSED BACKUPSET DATABASE
    FORMAT '/backup/rman/db_%d_%T_%s.bkp'
    TAG 'DAILY_FULL';
    BACKUP ARCHIVELOG ALL DELETE INPUT;
    VALIDATE BACKUPSET ALL;
}
Enter fullscreen mode Exit fullscreen mode

2. Deploy Oracle Data Guard and Perform Regular Block Validation

A Physical Standby database automatically detects and repairs block corruptions before they become critical. Complement this with scheduled VALIDATE DATABASE jobs to catch corruption early.

-- Schedule this weekly via DBMS_SCHEDULER
RMAN> VALIDATE DATABASE CHECK LOGICAL;

-- Monitor alert log for early warning signs
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-%'
  AND ORIGINATING_TIMESTAMP > SYSDATE - 1
ORDER BY ORIGINATING_TIMESTAMP DESC;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01194 — File needs more recovery to be consistent
  • ORA-00313 — Open failed for members of log group
  • ORA-01173 — Data dictionary indicates missing data file from system tablespace
  • ORA-10567 — Redo is inconsistent with data block

ORA-01172 is a serious but recoverable error in most cases. Acting quickly, checking your backup strategy, and understanding which cause applies to your situation will determine how much data — if any — you lose.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)