Force database open after incomplete restore (without archive/redo)

Question

For example, let's assume we need to restore the database, but we also have lost all archive logs. So we issue the following command in RMAN (just restore, without recover).

RMAN> run{
2> set newname for database to '+DGMOOTDB';
3> restore database;
4> switch datafile all;
5> }
6>

Ok, now, we can't open the database, because the files are inconsistent (CHECKPOINT_CHANGE# are not the same in all files) and we don't have any archive log to sync it.

SQL> Select Count(1),CHECKPOINT_CHANGE#,To_Char(Checkpoint_Time,'dd/mm/yyyy hh24:mi:ss') as data_hora_repl  From V$datafile_Header
group by CHECKPOINT_CHANGE#,checkpoint_time order by CHECKPOINT_CHANGE#;

  2
  COUNT(1) CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         5           32826214 29/05/2021 19:14:22
         5           32826215 29/05/2021 19:14:22

SQL> SQL>

Is there any way to open the database?

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+DGMOOTDB/MOOTDBHIST/DATAFILE/system.264.1074023789'


SQL>

Answer 1

If your rman backup was an 'online' backup, the restored data files are inherently inconsistent. The database will not open unless all of the data files are consistent. If you do not have the necessary redo (in the form of archivelogs) then you cannot recover to a consistent state.

So the short answer to your question is "no".

Answer 2

It is possible to open database after incomplete recovery even when files are inconsistent. Use the instructions in one of these posts:

Recovering an Oracle Database with Missing Redo by Simon Pane

recovering without consistency is not supported and should only be attempted as a last resort. Opening the database with the data in an inconsistent state is actually pretty simple. We simply need to set the "_allow_resetlogs_corruption" hidden initialization parameter and set the undo management to "manual" temporarily

Now, will the database open? The answer is still: "probably not"

To put it simply this ORA-00600 error means that a datafile has a recorded SCN that's ahead of the database SCN

The solution to this problem is quite simple: roll forward the current SCN until it exceeds the datafile SCN. The database automatically generates a number of internal transactions on each startup hence the way to roll forward the database SCN is to simply perform repeated shutdowns and startups.

switch back to automatic undo management using a new undo tablespace

Finally the database is opened (although the data is inconsistent) and the "old" data can be queried

https://www.parnassusdata.com/en/node/1135