Real meaning and Simulate "ORA-01152: file was not restored from a sufficiently old backup"

I saw many times people asked the meaning of "ORA-01152: file n was not restored from a sufficiently old backup" and the difference between "ORA-01152" and "ORA-01113: file needs media recovery".

First of all,  "ORA-01152" is not related to controlfile. I say so because someone said  "ORA-01152: file was not restored from a sufficiently old backup" is caused by old controlile.
It is not true. A simple prove is: even you recreate controlfile with "create database", you can still get the error.

Then what is the real meaning for "ORA-01152: file was not restored from a sufficiently old backup"?

To figure it out, let's firstly learn below knowledge regarding how oracle know if a recovery is nessasary during startup.
Infact oracle make the judgement from below two flags in datafile header block:
1. OFFSET 0138-0139: DB_state.
00 means DB is down, 04 means DB is running.
When a DB is up and running, this flag is 04. During an normal shutdown, oracle will change this flag to 00.
So if oracle read 04 of the flag during startup, then oracle will be aware that latest shutdown was not commpleted succeesfully, so a recovery will be nessasary.

2. OFFSET 0484-0489: checkpoint_change#
During a DB clean shutdown, oracle will make a full checkpoint, so all datafiles' checkpoint_change# will be sync.
So if in a startup checking, oracle found checkpoint_change# are inconsistent among datafiles, then an recovery will be required.


OK. Now we know how oracle judge the recovery, from two flags DB_state and checkpoint_change#.
Then it is a simple math that there are four combinations:
Scenario 1.DB_state=00 and checkpoint_change# are consistent.
It means lastest shutdown is an succeed clean shutdown. Oracle won't try to perform any recovery.

Scenario 2.DB_state=04 and checkpoint_change# are inconsistent.
95% DB crashing is located in this senerio. When a DB crashed, oracle have no time to change DB_state to 00, and checkpoint_change# among datafiles are not consistent.
In this senerio, when DB startup, a recovery will be required to perform, if you try to open resetlogs without recover, you will get below error:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/data/oracle/data/miao/miao/stream01.dbf'
This is an common error we often get. We know very well about it.

Scenario 3.DB_state=04 and checkpoint_change# are consistent.
How can this happen? For example, you execute command "alter system checkpoint;" after this command you immediately execute below command:
shutdown abort;
Then checkpoint_change# among datafiles are the same. But DB_state remians 04.

Next time when oracle startup, oracle will read DB_state=04, which means, as i already introduced, latest close for this datafile is not an normal close.
The error here you will get is the same in senerio 2:
ORA-01113: file 5 needs media recovery

Scenario 4.state_flag=00 and checkpoint_change# are inconsistent.
In this case, you will get error "ORA-01152: file n was not restored from a sufficiently old backup".

But how could this happen? How can it be possible when checkpoint_change# are inconsistent while state_flag=00 which means a clean shutdown?

For example:
At time point T1, you shutdown database and made a closed clean backup.
Then you startup DB and do some operations and swtich logfile for mutiple times. Then at time point T2, again you normally shutdown the DB.

Now you delete some T2 datafiles and replace it with datafiles that you backuped at timepoint T1.
So now all these datafiles state_flag=00 because all datafiles are from an clean closed shutdown, but checkpoint_change# among these datafiles are not consistent.
In this sernerio, when you try to open resetlogs DB without recovery, you will get below error:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data/oracle/data/miao/miao/system01.dbf'