Yesterday I was asked by a colleague to look into a test system. There was data deleted through the application. He tried to restore it with from a database backup 2 days ago with no luck. He seemed to be struggling with recover — either getting “ORA-16433: The database or pluggable database must be opened in read/write mode” or “ORA-01190: control file or data file 1 is from before the last RESETLOGS“.

I checked the system and the latest database backup was from 2 days ago. And there was only one backup of control file which was strange to me because in RMAN I could see the retention policy was set to recovery window 5 days. However, the RMAN backup log showed the previous copy was deleted right away during the deletion of obsolete because of retention policy was redundancy 1.

Anyway, I used that backup control file and did an incomplete restore/recovery to the SCN right before deletion. Below were my steps:

set dbid <>;

startup nomount;

restore controlfile from '<full path of backup of controlfile>';

alter database mount;

list incarnation;
reset database to incarnation 2;

list backup summary;
list backupset <key>;

run
{
set until scn <scn>;
restore database;
recover databsae;
}

alter databse open resetlogs;

In above steps, I had to reset database incarnation because even the backup of control file only contained 1 incarnation (I verified that after I restored the control file from the backup), but once I started to restore, Oracle reliazed there were more incarnations since there were resetlogs operations. I am not sure where Oracle got this information. And “list backupset <key>” was to find the low SCN I needed to use in “set until scn“.

The restore and recover went through fine. The real problem was when I tried to open database with resetlogs, Oracle crashed with the following errors:

I was stuck there. Initially I was not sure what caused this and thought there were something wrong with the backup files. Then I tried a couple of restore/recovery to different SCNs further back. All failed with the same errors and Oracle just crashed. The operation of resetlogs acutally did succeeded, it failed at database opening. I stared to searching internet and MOS Doc ID 1902011.1 suggests if there are multiple $ORACLE_HOME and wrong environment is used, wrong Oracle binary files will be used to start up the database, then ORA-00704 bootstrap process failuer message is thrown out. I didn’t think too much because this test system should have one Oracle HOME usually. I looked around and only saw an empty 11g directory timestamped after the latest backup. I started to dig into alert log and solved myterious failure there:

All backups were from Oracle 11g! There was a database upgrade from 11.2.0.4.0 to 12.1.0.2.0 after the latest backup. That’s why I saw an empty 11.2.0 directory. And it also explained why I saw RMAN only maintained 1 copy of controlfile and deleted the copy older than 1 day. That’s because RMAN retention policy of 11g was set to keep 1 copy.

So to solve it, need to install 11.2.0.4 again and open the database. Then upgrade from there.

One thing I don’t understand though — I followed the article (reference 6) to check “Compatibility Vsn” in file headers of all data files restored/recovered. They all showed “202375680=0xc100200” which is 12.1.0.2.

SQL> alter session set tracefile_identifier=’datafile_hdr’;
SQL> alter session set events ‘immediate trace name file_hdrs level 10’;

Also checked the restored control file from the backup before the upgrade — I restored it and mounted database then ran:

SQL>alter session set events 'immediate trace name controlf level 9';

From the trace file, it also showed “202375680=0xc100200“

References:

1. ORA-00704 : Bootstrap Process Failure – Causes and Solution (Doc ID 1902011.1)
2. Database Startup Failure After RMAN Restore with ORA-00704, ORA-00604, ORA-00904 (Doc ID 2540757.1)
3. How to find the name of an old Restore Point when the database will not open: ORA-38760 (Doc ID 1288189.1)
4. https://www.dba-career.com/2017/04/ora-00904-iunusablebeginning-invalid.html
5. https://ramilrahimov.wordpress.com/2018/09/28/rman-duplicate-ora-00904-i-unusablebeginning-invalid-identifier/
6. https://oraclehandson.wordpress.com/2016/10/27/ora-00704-bootstrap-process-failure/

• Oracle