ORA-01152

I would really appreciate a better error message than ORA-01152 that can hide couple of different situations, in spite of the fact that is always saying that  the system change number  in the datafile header is newer that the system change number in the controlfile.  A typical situation when Oracle is signalling the ORA-01152 and is asking for the non-existent archivelog; you need to supply the online redo log in order to recover the database:

 

 

SQL> alter database open;

 

ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘C:\APP\ORACLE_BASE\ORADATA\TEST\SYSTEM01.DBF’

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 17080879 generated at 04/10/2015 11:41:07 needed for thread 1
ORA-00289: suggestion :
C:\APP\ORACLE_BASE\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2015_04_23\O1_MF_1_1_%U_.A

RC
ORA-00280: change 17080879 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
‘C:\APP\ORACLE_BASE\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2015_04_23\O1_MF_1_1_%U_.

ARC’
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) Le fichier spÚcifiÚ est introuvable.

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
——————
17080879
17080879
17080879
17080879
17080879
17080879
17080879
17080879
17080879
17080879
17080879

11 rows selected.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
———- ———- ———- ———- ———- ———- —
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
—————- ————- ——— ———— ———
1          1          1   52428800        512          1 NO
CURRENT               17079687 10-APR-15   2.8147E+14

3          1          0   52428800        512          1 YES
UNUSED                       0                      0

2          1          0   52428800        512          1 YES
UNUSED                       0                      0

SQL> select * from v$logfile;

GROUP# STATUS  TYPE
———- ——- ——-
MEMBER
——————————————————————————–

IS_
—
3         ONLINE
C:\APP\ORACLE_BASE\ORADATA\TEST\REDO03.LOG
NO

2         ONLINE
C:\APP\ORACLE_BASE\ORADATA\TEST\REDO02.LOG
NO

GROUP# STATUS  TYPE
———- ——- ——-
MEMBER
——————————————————————————–

IS_
—

1         ONLINE
C:\APP\ORACLE_BASE\ORADATA\TEST\REDO01.LOG
NO

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 17080879 generated at 04/10/2015 11:41:07 needed for thread 1
ORA-00289: suggestion :
C:\APP\ORACLE_BASE\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2015_04_23\O1_MF_1_1_%U_.A

RC
ORA-00280: change 17080879 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\APP\ORACLE_BASE\ORADATA\TEST\REDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL>