RA-01503 ORA-01189 When trying to Recreate Controlfile


SYMPTOMS
Sun Aug 17 14:04:07 2014
Media Recovery failed with error 16433
Slave exiting with ORA-283 exception
Errors in file /oradump/prod/edwp/logs/diag/diag/rdbms/
edwprod/EDWPROD/trace/EDWPROD_pr00_32964892.trc:
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...
Normally, the solution is to recreate the controlfile. However, the following error is produced when trying to recreate the controlfile:
REATE CONTROLFILE REUSE DATABASE "EDWPROD" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 2: '/ora/prod/edwprod/data/EDWPROD/datafile/o1_mf_undotbs1_334h0kxm_.dbf’
CAUSE
In order to recreate the controlfile, all datafiles must be at the same incarnation, or resetlogs time.
We cannot allow datafiles to be at different incarnations.
Using the current controlfile, which was used and failed with recovery before, we can see that all datafiles, except datafile# 1, have a RESETLOGS_TIME of 17-AUG-2014 13:28:19:
SQL> select status,checkpoint_change#,checkpoint_time, resetlogs_change#,
 2 resetlogs_time, count(*), fuzzy from v$datafile_header
 3 group by status,checkpoint_change#,checkpoint_time, resetlogs_change#,
 4 resetlogs_time, fuzzy;
STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME RESETLOGS_CHANGE# RESETLOGS_TIME COUNT(*) FUZ
---------- ------------------ -------------------- ----------------- ------------------------- ---------- ---
ONLINE 3645505893814 17-AUG-2014 11:37:33 1 27-APR-2007 14:07:53 1 NO
ONLINE 3645505893818 17-AUG-2014 13:34:48 3645505897826 17-AUG-2014 13:28:19 156 YES
SQL> select file#, status, checkpoint_change#, checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME RESETLOGS_CHANGE# RESETLOGS_TIME FUZ
------- ---------- ------------------ -------------------- ----------------- ------------------------- ---
 1 ONLINE 3645505893814 17-AUG-2014 11:37:33 1 27-APR-2007 14:07:53 NO
 2 ONLINE 3645505893818 17-AUG-2014 13:34:48 3645505897826 17-AUG-2014 13:28:19 YES
...
 157 ONLINE 3645505893818 17-AUG-2014 13:34:48 3645505897826 17-AUG-2014 13:28:19 YES
157 rows selected.
SOLUTION
Suggestion
Before recreating the controlfile as described below, take a operating system backup of the current controlfile to be on safe side.
Steps to solve this issue:
The below will only work if you have the redo logs (archived and/or online) to recover the database.
1) Recreate the controlfile with just the datafiles that has lower incarnation, or resetlogs time. 
 This list must include the system datafile.
From the previous example, the CREATE CONTROLFILE command should have the datafile with a RESETLOGS TIME of 27-APR-2007 14:07:53. 
 In this case it will be datafile# 1:
CREATE CONTROLFILE REUSE DATABASE "EDWPROD" RESETLOGS ARCHIVELOG
 MAXLOGFILES 40
 MAXLOGMEMBERS 3
 MAXDATAFILES 200
 MAXINSTANCES 8
 MAXLOGHISTORY 37744
LOGFILE
 GROUP 1 (
 '/ora/prod/edwprod/data/EDWPROD/onlinelog/o1_mf_1_1PN15JjCD_.log',
 '/ora/prod/edwprod/index/EDWPROD/onlinelog/o1_mf_1_1PN15ZLTY_.log'
 ) SIZE 1000M BLOCKSIZE 512,
...
GROUP 12 (
 '/ora/prod/edwprod/data/EDWPROD/onlinelog/o1_mf_12_1PN0wPCV4_.log',
 '/ora/prod/edwprod/index/EDWPROD/onlinelog/o1_mf_12_1PN0wdYFq_.log'
 ) SIZE 1000M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
 '/ora/prod/edwprod/data/EDWPROD/datafile/o1_mf_system_334h00q2_.dbf'
CHARACTER SET WE8MSWIN1252
;
2) Once the controlfile is recreated, perform recovery to bring it / rollforward to new incarnation:
SQL> recover database using backup controlfile until cancel;
Apply the redo log(s) requested until it gets to the new incarnation
3) Now recreate the controfile again to include "ALL Datafiles". And finally perform recovery of all the datafiles:
SQL> recover database using backup controlfile until cancel;
Apply the redo log(s) requested to get to the desired point in time
4) Open the database with resetlogs:
SQL> alter database open resetlogs;
Information: ORA-01503 ORA-01189 When trying to Recreate Controlfile (Doc ID 1918706.1)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: