Ask the Expert

Can't start up database without error ORA-01110

I can't get our database to start up correctly. I keep getting the following error. I have tried copying CONTROL02 to CONTROL01 and from CONTROL01 to CONTROL02 but it just always results in another error (ORA-01110). Our database is not in a live environment as we are in a testing phase of an application that uses Oracle as its database. Unfortunately we also do not have a backup which we can restore to (very stupid, I know!). I've cut and pasted the error message below. I really would appreciate your assistance as I am not an experienced DBA and also do not have access to Oracle skills in our small organization.
ORA-00214: controlfile 'G:ORACLEPRODUCT10.1.0ORADATAORCLCONTROL02.CTL'
version 5674 inconsistent with file
'G:ORACLEPRODUCT10.1.0ORADATAORCLCONTROL01.CTL' version 5672
Kind regards.

    Requires Free Membership to View

It looks like your control files are not consistent with each other. The best way to determine which control file is most current is to look at the timestamp of the files. The most recently modified file is the most current. You can copy the most recent control file over the other control files and you should not see the ORA-00214 error any more.

I always back up my control file to a binary backup file. If you have a binary backup of your control file, you can use this binary backup to overwrite the inconsistent control files. Once done, use the following steps to open the database:

STARTUP MOUNT
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
Apply recovery until all of your archived redo logs have been applied. Then reply CANCEL.
ALTER DATABASE OPEN RESETLOGS;
If you do not have a binary backup of your control file, you can create the control file from scratch with the CREATE CONTROLFILE command.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE orcl NORESETLOGS ARCHIVELOG
   MAXLOGFILES 32
   MAXLOGMEMBERS 2
   MAXDATAFILES 100
LOGFILE GROUP 1 ('/directory/redo01a.log','/directory/redo01b.log') SIZE 100K
        GROUP 2 ('/directory/redo02a.log','/directory/redo02b.log') SIZE 100K
        GROUP 3 ('/directory/redo03a.log','/directory/redo03b.log') SIZE 100K
DATAFILE 
   '/directory/system01.dbf', 
   '/directory/users01.dbf',
   '/directory/undo_tbs01.dbf';
RECOVER DATABASE;
ALTER DATABASE OPEN;
You will have to supply correct information to the CREATE CONTROLFILE command, which means you'll have to know where each and every file in your database resides on your server. This is why a good backup of your control file is essential. It is easy to make mistakes in the step above. But you can still get the database open. For more information on the CREATE CONTROLFILE command, refer to the Oracle documentation.

Once you have the database open, ensure you have a good backup so that you do not have to repeat this recovery operation in the future.

This was first published in November 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: