DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00279 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00279: Change Generated Needed for Thread – What You Need to Know

ORA-00279 is an informational message Oracle generates during database recovery operations, indicating that a specific archived redo log file is needed to continue applying changes up to a required SCN (System Change Number). Rather than a fatal error, it acts as a prompt asking the DBA to supply the next required archive log. You'll typically encounter this alongside ORA-00280 and ORA-00278 during manual recovery sessions.

Top 3 Causes

1. Archive Log File Not Found in Expected Location

Oracle cannot automatically locate the next archive log during recovery because files have been moved, deleted, or reside in a non-default directory.

-- Check current archive log destination settings
SHOW PARAMETER LOG_ARCHIVE_DEST;

-- List available archived logs in the control file
SELECT NAME, SEQUENCE#, FIRST_CHANGE#, STATUS
FROM V$ARCHIVED_LOG
WHERE THREAD# = 1
ORDER BY SEQUENCE#;
Enter fullscreen mode Exit fullscreen mode

2. Incomplete (Point-In-Time) Recovery in Progress

When restoring a database to a specific point in time, Oracle applies archive logs sequentially and will pause at each missing log, issuing ORA-00279.

-- Perform point-in-time recovery using RMAN (recommended)
RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2025-01-15 09:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}

-- Open database after incomplete recovery (RESETLOGS is mandatory)
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

3. Archive Gap in Data Guard Standby Environment

In a Data Guard setup, a network interruption between Primary and Standby can cause archive logs to be missing on the Standby side, triggering ORA-00279 during managed recovery.

-- Check for archive gaps on Standby
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#
FROM V$ARCHIVE_GAP;

-- Force archive log transfer from Primary
ALTER SYSTEM ARCHIVE LOG SEQUENCE 105 THREAD 1;

-- Register the log manually on Standby and resume recovery
ALTER DATABASE REGISTER LOGFILE '/arch/arch_1_105_XXXXXXXX.arc';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Option 1: Provide archive log path manually in SQL*Plus
RECOVER DATABASE;
-- When prompted with ORA-00279, type the full path:
-- /arch/arch_1_105_XXXXXXXX.arc

-- Option 2: Use AUTO to let Oracle find logs automatically
RECOVER AUTOMATIC DATABASE;

-- Option 3: Cancel recovery and open with RESETLOGS (last resort)
RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode 
-- RMAN: Catalog missing archive logs and retry recovery
RMAN> CATALOG START WITH '/backup/archive/';
RMAN> RECOVER DATABASE;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Set an archive log retention policy in RMAN to prevent logs from being deleted before they are backed up or applied to standby. 
-- Retain archive logs until backed up twice
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Enter fullscreen mode Exit fullscreen mode
  1. Monitor Data Guard gaps proactively using a scheduled script against V$ARCHIVE_GAP, and ensure FAL_SERVER/FAL_CLIENT parameters are correctly configured so gaps are resolved automatically without DBA intervention. 
-- Schedule this as a monitoring query on Standby
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#,
       (HIGH_SEQUENCE# - LOW_SEQUENCE# + 1) AS MISSING_LOGS
FROM V$ARCHIVE_GAP;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-00278 Archive log no longer needed for recovery
ORA-00280 Change is in a specific archive sequence number
ORA-00283 Recovery session canceled due to errors
ORA-00308 Cannot open archived log file specified
ORA-01547 OPEN RESETLOGS failed – recovery not complete

📖 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)