Re: Control File Backup

Lots of comments.... (sorry!)

"VA Access" <vaaccess_at_my-deja.com> wrote in message news:95pia1$kbt$1_at_nnrp1.deja.com...
> I guess I should have stated that the system is in backup mode at the
> time of the command execution that I listed below. All of the
> tablespaces are put into backup mode by executing the following command
> for each tablespace:
>
> alter tablespace SYSTEM begin backup;
>
> Then, the ocopy73 commands are executed to copy the datafiles and the
> control file is copied as well.

That's your first mistake! I presume you are doing hot backups, because you want to allow Users to still access data, and perform transactions, whilst the backup is taking place. Be aware that whilst a tablespace is in hot backup mode, every transaction, even of a single field in a single row, results in the entire *BLOCK* in which that row is found being written into the Log Buffer and hence into your Redo Logs and archives.

Accordingly, the amount of Redo generated whilst in hot backup mode is potentially enormous, and is likely to cripple your redo sub-system.

For that reason, the very firm recommendation from Oracle is that you place a SINGLE tablespace into hot backup mode, copy the relevant files, then take it out of hot backup mode -and then move onto the next tablespace. Most of your database is therefore NOT in hot backup mode at any one moment, and hence the extra redo generation is minimised as far as possible. You're also supposed to make the time between a 'begin backup' command and an 'end backup' command as short as possible, for the same reason.

>What is happening is that the control
> file wasn't copying when the AT commands are executed, but would copy
> when I ran the commands by double-clicking on the .cmd file that
> started the process.
> I changed the commands so it will copy the control files with a dos
> copy command instead of the Oracle ocopy command. We'll see if that
> works tonight.
>

OK. But (as you go on to say) so long as you understand that the resulting file will be useless. But fair enough that you want to just make sure the thing works.

> However, based on what you're saying the consultant that set all this
> up didn't do it correctly. By using the "alter database backup
> controlfile" command that you mentioned, how would I get the control
> files and all the datafiles to be in sync?

First off, the chances of you ever having to use the binary backup of your control file are small. You'd only ever be facing that problem if you lost *all* exisitng copies of your control file -and I'm hoping that you have done the sensible thing and mirrored your Control Files within Oracle so that there are multiple copies. (I always recommend 3-way mirroring).

So in normal recovery situations (where your control files are not lost, but one of your datafiles is, say), it's dead easy: Control File is at time 21000, 9 data files are at time 21000, 1 datafile is at time 16000 (restored from backup). At startup, SMON reads the timestamp in the Controlfile, spots one of the data files is out of synch, and prompts for recovery of that datafile. When you then say "recover database" or "recover datafile X", Redo is applied to the recalcitrant datafile, and it is rolled forward to time 21000.

If you ever restored a binary copy of your Control File (and one that was useable because it had been taken with the 'alter database backup controlfile command), then you'd potentially have this problem: 10 datafiles all beautifully consistent at time 21000, and a Control File at time 16000.

Now, you can't apply redo to Control Files. So how do you force the Control File to synchronise with the data files? Short answer is, you tell SMON not to panic about the timestamp on the Control File, and to check the data files: "recover database USING BACKUP CONTROLFILE". That still will leave the Control File out of synch with everything else, so now you force the issue: "alter database open RESETLOGS".

The resetlogs commands effectively forces synchronisation back to time 1.

In short, any time you use a binary backup of your control file to open a database, you MUST issue a resetlogs.

Now think about that: if the timestamps on everything have just been reset back to time 1, you've got a problem on your hands. Your old backups were all stamped at 16000. 16000 can not be rolled forward to 1. Hence all prior backups are useless. Guess what? Your archives are all at time 16000, 17000, 18000 and so on too... hence all prior archives are useless. See the problem? Anytime you open a database with a resetlogs, it effectively has no backups, and no archives. You are in a hugely vulnerable position, and hence the standard advice is always to immediately shutdown the database and take a new, cold, base line backup.

Short story is, using binary versions of your Control File is a very expensive exercise, and for that reason it's usually much better to take trace file backups: "alter database backup controlfile to trace". The trace script doesn't really back the control file up -it's just a set of SQL statements which will reconstruct a control file from scratch, and it will do so in a sneaky fashion whereby the timestamps on the datafiles are written into the header of the controlfile -and hence, the new control files will be set at time 21000 in our previous example. No expensive resetlogs option is therefore required, and no loss of effective backups and archives takes place.

In practice, I'd do both -a binary, consistent backup AND the trace file option (there *is* a use for a binary copy, but it's a bit obscure. Still, if you need that flexibility, you need it bad!).

>Could the "alter database
> backup controlfile" command be run during the backup of the datafiles?
>

Yes, theoretically. It doesn't matter if you do it before or after your datafiles (or in the middle of it, if you were doing the proper technique of one tablespace backed up at a time).... as I've explained, when it comes time to use the thing, its timestamp is irrelevant. You'll be forcing the issue with a resetlogs option anyway.

> What is the best way to do a backup, then? Right now I could take the
> database offline and copy the files and bring it back up nightly. That
> wouldn't be a big problem...
>

Cold backups are a doddle to perform, and if you have that luxury of being able to shutdown and copy *.* to somewhere, I'd thoroughly recommend it. The presence of archives means you have the luxury of choosing whether to do hot or cold, and cold is always simpler to perform.

But you really ought to get your head around hot backups, since even if you don't use them in your current
position, there's a good chance they'll be used in your next one!

Check out http://www.geocities.com/hjroz2000/backup_and_recovery.htm There's a 70-odd page pdf document there I wrote a while back. Explains all this stuff in some detail, and has apparently been of help to a number of posters here. I'll happily take this offline with you if you contact me, too.

Regards
HJR
> Thanks for the help.
> Mike
>
> In article <3a7b410e$1_at_news.iprimus.com.au>,
> "Howard J. Rogers" <howardjr_at_www.com> wrote:
> > You don't say what is going wrong, and I'm assuming you physically
 aren't
> > getting a copy of your Control File in the requisite directory, but
 let me
> > tell you straight up that even if it was working, your backup would be
> > worthless.
> >
> > You can NOT take hot backups of ANYTHING in Oracle, unless you have
> > something that can make the resulting inconsistent mess consistent
 once
> > more.
> >
> > Now with datafiles, we have redo. Applying redo makes inconsistent
> > datafiles consistent. The restored files can thus be made useable,
 provided
> > all required redo is applied.
> >
> > What do we have that we can apply to Control Files? Er... nothing.
> >
> > You therefore cannot just copy Control Files whilst the database is
 hot.
> > For this reason, Oracle cunningly invented the piece of SQL
 syntax: 'alter
> > database backup controlfile to 'd:\blah\blah\blah.bkp' -since this
 command
> > causes Oracle itself to generate the requisite consistent image.
 Unless you
> > use that command (or, preferably, it's trace file cousin), your
 control file
> > backups will be unuseable when it comes to the crunch.
> >
> > Regards
> > HJR
> >
> > "VA Access" <vaaccess_at_my-deja.com> wrote in message
> > news:95f1j5$g4s$1_at_nnrp1.deja.com...
> > > Each night I run a process that puts the DB into hot backup mode and
> > > then copies the db files to a directory which is backed up a little
 bit
> > > later. Anyway, I noticed that the Control Files are not getting
 copied
> > > correctly, but all the datafile are doing just fine. So, my
 question
> > > is, why?
> > >
> > > Here's what happens:
> > > SQL runs that puts all the tablespaces into backup mode.
> > >
> > > Then, the copying occurs. Here's a snipit:
> > > PLUS33W.EXE sys/PASSWORD_at_INSTANCE
> > > d:\orant\bin\ocopy73 d:\orant\database\CTL1CODA.ORA
 d:\dba_admin\backup
> > > d:\orant\bin\ocopy73 d:\orant\database\LOG1CODA.ORA
 d:\dba_admin\backup
> > >
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Feb 06 2001 - 14:07:03 CST