This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Tag: Name

Recover Oracle DB With Missing DataFiles or Missing UNDO or Multiple ResetLogs Issue

If you have run through the Create ControlFile script method (why are you still using this and not the NID utility!!?) but it failed because you were missing one of the datafiles, then you will struggle to open and resetlogs the database due to Oracle errors.
You will then retry the process (maybe with the correct datafiles in place this time) and it will fail because you’ve run resetlogs too many times and the datafiles are all out of sync with the incarnation of the database.

The post also assumes that one of the missing datafiles is required for your UNDO tablespace.
This post is based on the post here: https://dbakevin.blogspot.co.uk/2011/02/simulate-one-ora-01161-solving-for-ora.html  and will guide you through the process of getting the database back, provided you now have the required datafiles in place (you don’t have to have them, but it would be good if you did).

1, Delete all existing database control files at the operating system level.
These control files are useless to us as they contain the new DB name and sequence numbers, but we can’t use them as we will have to re-run the CREATE CONTROLFILE statement.

2, Change the DB init file to use rollback segments (as you have no automatic UNDO without your undo tablespace) and allow resetlogs to corrupt the database, by putting the following in the init<SID>.ora:

undo_tablespace='SYSTEM'
undo_management='MANUAL'
_allow_resetlogs_corruption=true
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$)

3, Edit the CREATE CONTOLFILE SET DATABASE “<SID>” script to comment out ALL tablespaces except SYSTEM.

This will allow us to create a basic database will rollback.

4, Create the database using the CREATE CONTROLFILE script as you would have done previously.

5, Alter database open resetlogs.

6, Re-name all “MISSING” datafiles based on FILE_ID taken from production.

NOTE: If you don’t know this information (file_id to filename mapping), then you will become very stuck.

alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00004' to '/u01/app/oracle/oradata/day/users01.dbf';

7, Bring all “MISSING” data files online (first attempt):
alter database datafile 4 online;

You will see:

ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '/u01/app/oracle/oradata/day/users01.dbf'

8, Shutdown immediate.

9, Startup mount.

10, Recover until cancel.

11, Bring all “MISSING” data files online (again):
alter database datafile 4 online;

12, Alter database open resetlogs.

13, Re-Create UNDO tablespace as per the previous specifications (take the spec from production) (maybe use REUSE command so it doesn’t need to create the file) . Use the same file name as before.

14, Shutdown IMMEDIATE.

15, Adjust the init<SID>.ora to re-add the UNDO tablespace and remove the entries that you added in step #2.

16, Startup.