DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01190 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01190: Control File or Data File Is from Before the Last RESETLOGS

ORA-01190 occurs when Oracle detects that a control file or data file belongs to a database incarnation prior to the last RESETLOGS operation. This mismatch prevents the database from opening or completing recovery, as mixing files from different incarnations would compromise data consistency. It is most commonly encountered during incomplete recovery, Point-In-Time Recovery (PITR), or when restoring from an outdated backup.

Top 3 Causes

1. Restoring an Old Backup Taken Before the Last RESETLOGS

Using a backup created before the last RESETLOGS means the files carry an older incarnation's SCN, which conflicts with the current control file.

-- Check current database incarnation and RESETLOGS SCN
SELECT incarnation#, resetlogs_change#, resetlogs_time, status
FROM v$database_incarnation
ORDER BY incarnation#;

-- Verify data file header RESETLOGS info
SELECT file#, name, resetlogs_change#, checkpoint_change#
FROM v$datafile_header
ORDER BY file#;
Enter fullscreen mode Exit fullscreen mode

2. Incarnation Mismatch Between Control File and Data Files

If you recreate a control file or restore a backup control file, its incarnation reference may not match the data files on disk.

-- Identify the mismatch
SELECT d.resetlogs_change# db_resetlogs,
       h.resetlogs_change# file_resetlogs,
       h.file#, h.name
FROM v$database d, v$datafile_header h
WHERE d.resetlogs_change# != h.resetlogs_change#;

-- Recreate control file aligned with current data files (example)
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
  MAXLOGFILES 16
  MAXDATAFILES 100
LOGFILE
  GROUP 1 '/u01/oradata/ORCL/redo01.log' SIZE 200M,
  GROUP 2 '/u01/oradata/ORCL/redo02.log' SIZE 200M
DATAFILE
  '/u01/oradata/ORCL/system01.dbf',
  '/u01/oradata/ORCL/sysaux01.dbf',
  '/u01/oradata/ORCL/users01.dbf'
CHARACTER SET AL32UTF8;
Enter fullscreen mode Exit fullscreen mode

3. Incorrect UNTIL Clause in RMAN Recovery

Specifying a recovery target SCN or time that falls before the last RESETLOGS causes RMAN to select incompatible backup sets.

-- List all incarnations in RMAN
RMAN> LIST INCARNATION OF DATABASE;

-- Reset to the correct incarnation before recovery
RMAN> RESET DATABASE TO INCARNATION 3;

-- Recover to a specific SCN within the correct incarnation
RMAN> RUN {
  SET UNTIL SCN 1234567;
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 – Identify the incarnation gap

SELECT incarnation#, resetlogs_change#, resetlogs_time, status
FROM v$database_incarnation;
Enter fullscreen mode Exit fullscreen mode

Step 2 – Reset RMAN to the correct incarnation and recover

RMAN> RESET DATABASE TO INCARNATION <correct_incarnation#>;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Step 3 – If using a backup control file, finalize with RESETLOGS

-- In SQL*Plus (MOUNT state)
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Always take a full backup immediately after RESETLOGS. A fresh backup anchors the new incarnation and ensures clean recovery paths going forward. 
-- Run right after RESETLOGS
RMAN> BACKUP DATABASE PLUS ARCHIVELOG TAG 'POST_RESETLOGS';
RMAN> BACKUP CURRENT CONTROLFILE TAG 'POST_RESETLOGS_CF';
Enter fullscreen mode Exit fullscreen mode
  • Use an RMAN Recovery Catalog. The catalog tracks multiple incarnations automatically, allowing RMAN to select the right backup set without manual intervention. Always connect to the catalog before performing any cross-incarnation recovery to avoid ORA-01190 and related errors.

Related Errors

Error Code Description
ORA-01194 Data file needs more recovery
ORA-01152 File restored from obsolete incarnation
ORA-01110 Identifies the specific file causing the issue
ORA-01547 Recovery not needed but incorrectly invoked

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