Re: help recovering archives when all files in sync

"Syltrem" <syltrem_at_videotron.ca> wrote in message news:noZH5.3492$5I.65909_at_weber.videotron.net...
> Hello!
>
> I was following this conversation and it raised a question:
>
> If I always do online backups (never offline), how do I restore my entire
> database (let's say the whole place blew up)?
>
> Specifically:
> Do I need to restore my redo log files from tape, or will the ALTER
 DATABASE
> OPEN RESETLOGS create them?

The one thing you DO NOT copy onto tape when you do hot ('online') backups is your online Redo Logs. It makes zero sense to do so, because they will be internally inconsistent, and there is no mechanism in Oracle to make them consistent.

Alter database backup controlfile to 'c:\backupctl.bkp' guarantees a consistent image of the control file

The Archive Logs allow you to make the internally-inconsistent datafile copies consistent once more (and hence roll-forwardable).

But what will you apply to the online Redo Logs to make them consistent? Answer: nothing.

So... Hot Backups = Don't bother backing up the Online Redo Logs

Hence the answer to your specific question must be, since the Redo Logs won't be in the backup, you'll have nothing to restore. Instead, you issue the alter database create logfile group 1 'path/filename' size Xm' command to create brand new groups whilst waiting patiently at the Mount stage. After that, you can issue the alter database open resetlogs command.

Since you've lost your current Redo Log group, which by definition hasn't been archived yet, yes, you will have lost data.

> Do I need to recreate the controlfile from a script (create controlfile
> reuse...) or should I use the actual controlfiles that I saved to tape
> during the backup?
>

Good question. The usual rule of thumb is never use a binary copy of the Control File from backup, because its use requires a resetlogs -but you're going to be doing that any way in a total disaster situation.

Myself, I think I would still use the script (I can't think of any reason not to, but I confess -for obvious reasons- never having tried it in this sort of situation!), but you wouldn't be able to use it as actually generated by Oracle. The trace file says to startup nomount (fine!), create the control file (fine), and then to alter database mount (fine) then recover the database (not fine), and then to open the database (totally impossible given the lack of redo logs at this point).

You would therefore have to edit the trace file to 'recover database until cancel', and not try to open the database at all. You then get your control file back, and proceed to the mount stage. You then start the incomplete recovery prompted by the script. When the recovery process prompts for the Redo Log sequence that you know no longer exists, you cancel the recovery. You then issue the commands needed to create new redo log groups. You then open the database with a resetlogs.

I only ever use binary images of the Control File for recoveries where the database structure itself has changed (ie, dumbo Junior dropped an entire tablespace, and you need an old version of the Control File that correctly identifies the relevant tablespace).

Regards
HJR
> Thanks!
>
>
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:39f057ef$1_at_news.iprimus.com.au...
> > Yes, don't restore the Control Files or online Redo Logs.
> >
> > If you restore everything from a cold backup, you have a perfectly
> > synchronised database as at the time of the cold backup.
> >
> > What prompts the application of Archives is when SMON spots that the
> > timestamps on the Control Files don't agree with the timestamps on
> > everything else. So make sure you leave the Control Files at the point
 in
> > time in the future, and bring back only the 'old' datafiles. The same
 goes
> > for the Redo Logs (since you'll need their content to bring the database
> > bang up to date -overwrite them with last night's backup, and you've
 just
> > lost data).
> >
> > The rules of backup and recovery are really very simple -and the
 simplest
 of
> > them all is, if you have archives, you only ever restore the files which
 are
> > causing the problem, not the whole lot.
> >
> > Regards
> > HJR
> > --
>
> --------------------------------------------------------------------------
> > Opinions expressed are my own, and not those of Oracle Corporation
> > Oracle DBA Resources:

 http://www.geocities.com/howardjr2000
>
> --------------------------------------------------------------------------
> >
> > "Joe Gulla" <jgulla_at_erols.com> wrote in message
> > news:8siobp$nb7$1_at_bob.news.rcn.net...
> > > I was trying to recover data from point of a full cold backup to a
 time
 in
> > > future. I have all archives
> > > but since I replace all of database files from time of backup when I
 do
 a
> > > recover all instance is in ssync.
> > > Is there a way around this?
> > > Joe G
> > >
> > >
> >
> >
>
>
Received on Sat Oct 21 2000 - 02:38:10 CDT