Re: Losing control files

From: Stephan Witt <witt_at_beusen.de>
Date: 1996/12/18
Message-ID: <32B83510.41C67EA6@beusen.de>#1/1

Miran Novak wrote:
>
> Dafydd wrote:
> >
> > Hello,
> >
> > I am a UNIX sysadmin who knows very little about Oracle DBA. (It's
> > someone else's job.) Recently we had a problem whose solution was to do
> > a complete restore from tape, thus losing that day's data. I would like
> > to offer the problem to this group to see if a better solution could have
> > been found.
> >
> > Our databases are kept on different drives from the log files and the
> > control files. The drive holding the control files failed, was
> > replaced, and restored from tape. So, now the control files are "a day
> > older" than the rest of the databases. In general terms, how could we
> > resynchronize the controls, ie. bring them up to date?
> >
> > Thanks,
> > David
> > --
> > David Barr | A competent and self-confident person
> > dafydd_at_pacbell.net | is incapable of jealously in anything.
> > San Jose, CA | Jealousy is invariably a symptom of
> > USA | neurotic insecurity. --Lazarus Long
>
> Hi David,
>
> i'm not 100% sure, if this works, but bevor loosing a day's work, try
> this:
> - STARTUP MOUNT your database;
> - backup your current controlfile using
> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;
> at this point you should get a tracefile in $ORACLE_HOME/rdbms/log;
> - edit this tracefile, delete all the stuff till ...STARTUP NOMOUNT;
> - save the file as <myname>.sql
> - SHUTDOWN your database
> and now, run the file <myname>.sql from sqldba / svrmgrl;
> this creates a new - IMHO up-to-date - controlfile, recovers your
> database
> and brings your database - hopefully ;-) - online;
>
> BTW, always split your controlfiles (Oracle recommends at least 3)
> across
> different disks to prevent such situations.
>
> Hope this helps.
> Good Luck
>
> Miran
>
> --
> mailto:Miran.Novak_at_mch.sni.de , SNI AG, ORACLE Database Support
Hi David,

another solution to try if the first - and better one - given by Miran does not work is the use of the backup controlfile and recovery of the database. In any case, if possible, you should do a full cold backup of your inoperable database, just in case you have to repeat the procedure.

That should do the task:

1. start sqldba or svrmgrl
2. connect internal
3. start the instance

SQLDBA> startup mount
4. check for read only tablespaces

   and set the datafiles offline if there is one (for example) SQLDBA> alter database datafile '/opt/app/oracle/data/721/readonly.dbf' offline;
5. recover your database
SQLDBA> recover databse using backup controlfile; Oracle prompts for redo log files. If all goes well you end up with "Media recovery complete."
6. open your database
SQLDBA> alter database open resetlogs;
Statement processed.
(What an understatement :)
7. If you did step 4:
SQLDBA> alter tablespace readonly online; 8. Really important: DO A FULL BACKUP NOW.

   Within step 6 you lost your history...

Hope it helps, Stephan

-- 
---------------------------------------------------------------
<stephan.witt_at_beusen.de>  | "beusen" Software+Systeme GmbH
fon: +49 30 549932-62     | Landsberger Allee 392
fax: +49 30 549932-21     | 12681 Berlin, Germany
---------------------------------------------------------------

Received on Wed Dec 18 1996 - 00:00:00 CST