DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00265 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00265: Instance Recovery Required, Cannot Set ARCHIVELOG Mode

ORA-00265 occurs when a DBA attempts to switch the database to ARCHIVELOG mode while the instance still requires recovery. This typically happens after an abnormal shutdown — such as SHUTDOWN ABORT or a server crash — when the database is brought up only to the MOUNT stage without completing instance recovery first. Oracle protects data integrity by blocking the mode change until the redo log-based recovery is fully applied.

Top 3 Causes

1. Abnormal Shutdown Without Recovery Completion

After a crash or SHUTDOWN ABORT, Oracle marks the instance as requiring recovery. If you skip the OPEN phase and jump directly to MOUNT to change the archive mode, Oracle throws ORA-00265.

-- Check if recovery is needed
SELECT STATUS, DATABASE_STATUS FROM V$INSTANCE;

-- Check redo log status
SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;

-- Check recovery file status
SELECT * FROM V$RECOVER_FILE;
Enter fullscreen mode Exit fullscreen mode

2. Incorrect ARCHIVELOG Switch Procedure

Many DBAs, especially those new to Oracle, attempt the ARCHIVELOG transition without following the correct sequence. The database must be fully opened first to allow automatic instance recovery before being shut down cleanly and mounted again for the mode change.

-- WRONG approach (will cause ORA-00265 after abnormal shutdown):
-- STARTUP MOUNT;
-- ALTER DATABASE ARCHIVELOG;  <-- ORA-00265 fires here

-- CORRECT approach:
-- Step 1: Open the database to trigger automatic instance recovery
ALTER DATABASE OPEN;

-- Step 2: Shut down cleanly (NEVER use ABORT here)
SHUTDOWN IMMEDIATE;

-- Step 3: Mount the database
STARTUP MOUNT;

-- Step 4: Now safely switch to ARCHIVELOG mode
ALTER DATABASE ARCHIVELOG;

-- Step 5: Open the database
ALTER DATABASE OPEN;

-- Step 6: Verify
SELECT LOG_MODE FROM V$DATABASE;
ARCHIVE LOG LIST;
Enter fullscreen mode Exit fullscreen mode

3. RAC Environment Procedure Mistakes

In Oracle RAC, if only some instances are shut down abnormally while others are left in an inconsistent state, the remaining dirty redo entries require instance recovery. Attempting an ARCHIVELOG switch without addressing all nodes leads to ORA-00265.

-- Check all RAC instances
SELECT INST_ID, INSTANCE_NAME, STATUS FROM GV$INSTANCE;

-- Shut down all non-primary nodes first, then on primary node:
STARTUP;           -- Let Oracle auto-recover
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

The fastest and safest fix is to allow Oracle to perform automatic instance recovery by opening the database fully before attempting the mode switch.

-- Complete fix procedure

-- 1. Open the database (triggers automatic instance recovery)
ALTER DATABASE OPEN;

-- 2. Verify instance is stable
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

-- 3. Perform a clean shutdown
SHUTDOWN IMMEDIATE;

-- 4. Mount only
STARTUP MOUNT;

-- 5. Switch to ARCHIVELOG
ALTER DATABASE ARCHIVELOG;

-- 6. Open database
ALTER DATABASE OPEN;

-- 7. Set archive destination (if not already set)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/arch/oradata' SCOPE=BOTH;

-- 8. Validate the change
SELECT NAME, LOG_MODE FROM V$DATABASE;
ARCHIVE LOG LIST;

-- 9. Force a log switch to confirm archiving works
ALTER SYSTEM SWITCH LOGFILE;
SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always use clean shutdown commands. Establish a strict policy that SHUTDOWN ABORT is only used as a last resort. Use SHUTDOWN IMMEDIATE as the standard. Automate the ARCHIVELOG transition process with a validated script so no steps are skipped.

-- Daily health check query to catch issues early
SELECT
    d.NAME          AS DB_NAME,
    d.LOG_MODE      AS ARCHIVE_MODE,
    i.ARCHIVER      AS ARCHIVER_STATUS,
    i.STATUS        AS INSTANCE_STATUS
FROM V$DATABASE d, V$INSTANCE i;

-- Monitor alert log for ORA-00265 history (11g+)
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-00265%'
ORDER BY ORIGINATING_TIMESTAMP DESC;
Enter fullscreen mode Exit fullscreen mode

2. Take a cold backup before and after mode changes. Before switching to ARCHIVELOG mode, always take a full cold backup. After the switch, immediately perform an RMAN full backup to establish a new backup baseline. This ensures recoverability if anything goes wrong during the transition.

-- RMAN backup immediately after enabling ARCHIVELOG
-- Run from RMAN prompt:
-- BACKUP DATABASE PLUS ARCHIVELOG;
-- BACKUP CURRENT CONTROLFILE;

-- Verify backup completion
SELECT STATUS, START_TIME, END_TIME, INPUT_TYPE
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME DESC
FETCH FIRST 5 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00264 – No recovery required (opposite of ORA-00265)
  • ORA-00279 – Change required for thread during archive log recovery
  • ORA-01122 – Database file check failed, often co-occurs with instance recovery issues
  • ORA-01589 – Must use RESETLOGS or NORESETLOGS when opening after incomplete recovery

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