What you can do if all copies of your Oracle control files are lost

recreating control file requires earlier script dumped by controlfile trace command. if  DBA does not have the script, the only option is to restore from backup. hence the best practise is to backup controlfile automatically when backing up entire DB.

Oracle RMAN provides such functionality which allows auto controlfile(plus parameter file) backup while performing DB files backup.

in any case of the file lost, we can always restore from the good copy of backup.

in this case, the health of RMAN backup becomes substantial.

alan

Thanks Alan!

For detail of Control File autoback, please refer to the below link:

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10770/rcmsynta9a.htm

RMAN creates a copy of the control file for read consistency, this is the snapshot controlfile. Due to the changes made to the controlfile backup mechanism in 11gR2 any instances in the cluster may write to the snapshot controlfile. Therefore, the snapshot controlfile file needs to be visible to all instances.

The same happens when a backup of the controlfile is created directly from sqlplus any instance in the cluster may write to the backup controfile file.

In 11gR2 onwards, the controlfile backup happens without holding the control file enqueue. For non-RAC database, this doesn't change anything.

But, for RAC database, the snapshot controlfile location must be in a shared file system that will be accessible from all the nodes.

2)Data files are intact but Online Redo logs are lost: In this case you have lost your online redo log file also. If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open the database with RESETLOGS options. But when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file.

AFAIK, if you lose the redo logs then your database is garbage and you will not be able to recover. The redo logs contain information on what data blocks in the data files should be updated or not and if you don't have that information there is no way for Oracle to get the db back in a consistent state.

Resetlogs will not bail you out...

Regards

Bart

Bart

You can survive the loss and or corruption of online redo logs and get your database back up and running under various scenarios. There may be a price to pay in lost data if the online  redo log has not been archived or is active. Details are in the My Oracle Support Articles below and involves the use of the SQL command "alter database clear logfile group ;"

REDO LOG CORRUPTION - DROPPING REDO LOGS NOT POSSIBLE - CLEAR LOGFILE [ID 1078456.6]

Loss Of Online Redo Log And ORA-312 And ORA-313 [ID 117481.1]

I have used it in the past on development and test databases but would probably be hitting last chance saloon before considering it in a production environment.

Hi Allan,

It seems from the documentation that you can only do this (clearing online logs):

- If the DB is still running (therefore the database can make sure all dirty blocks are flushed to both the data file and the cleared (new) redo log, i.e. by issuing a new checkpoint or something like that

- If the DB is down but the redo log to be cleared was not current (this compromises your backup but not the database itself)

If you have a crashed database (ie.. 'shutdown abort' not 'shutdown immediate') and you lose the active (current) redo log (plus its copy if you duplex redo logs) then there is (as I understand it) still no way to recover - except (from 117481.1):

-If the log is current they should simply perform fake recovery  and then    open resetlogs

Doing this would probably mean you lose the most recent transactions (the ones in the missing redo log) and also you get consistency issues (either at the DB or in the application).....Because some of the commits in the missing redo made it to the datafiles but Oracle does not know about it or how to roll them back.

So to do this for a mission critical DB it seems like really tricky.

Am I correct or is my understanding of Oracle consistency messed up? :-)

Bart

As I said in my last post

"There may be a price to pay in lost data if the online  redo log has not been archived or is active."

! "would probably be hitting last chance saloon before considering it in a production environment."

At which point the storage, BRS and DBA team might be scanning the job listing at McDonalds.

If the lost redo log is an inactive logfile, you can clear the logfile or drop it if you have 2 or more others.

If the lost redo log/redo log group  is current  it requires fake recovery with "recover database until cancel"and "alter database open resetlogs".

If open reset logs fails then one would have to recover from backup. You will probably lose your most recent transactions in this case. If you had a multiple log group members and a sensible placement policy you would be unlucky to lose both. (see Recover database after disk loss [ID 230829.1])

If you experience a database crash and lose your current or unarchived online redo logs you are most likely going to lose transaction data.