Re: benefits of multiple switch logfile calls?

"lsattle" <lsattle_at_yahoo.com> wrote in message news:95898986.0312040755.16c6f7ac_at_posting.google.com...

>
> I'm not sure of where you are mentally on all this. The switch log
> files / archive log files, to my knowledge is intending to get any
> current redo out of the online redo logs and into the archive redo
> logs.
>
> I'm not completely clear on switching vs archiving. I would think
> that just archiving would be enough.

There's no difference. If you 'archivelog current', a log switch is induced anyway.

>
> What I am certain of is that while your tblspaces are in "begin
> backup" and you are gziping or copy data files, transactions and / or
> checkpoints are occuring in your system. After you do the "end
> backup" it is wise to get all redo out to the archive directory.
>
> Then when you restore and do the recovery command (our recovery
> command is "recover database using backup controlfile until cancel")
> the recovery will only need archived redo logs.

This is a concern. What do you mean "our recovery command is..."?? The recovery command that one issues should depend on the nature of the recovery, which itself should be dependent on the nature of the failure. And in particular, why on Earth so many people enjoy throwing the 'using backup controlfile' line in there, I have no idea. You only need that line when you've actually restored your controlfile, and you would never need to do that if you've (a) multiplexed your controlfiles as you should (b) done backup controlfile to trace as you should and (c) mirrored your controlfiles as you should.

Restoring a controlfile unnecessarily is a crazy thing to do, since it requires the use of the 'until cancel' syntax, which itself requires that the database then be opened with a resetlogs, which thus means no prior backups or archives are usable without severe hoop-jumping. It is, in short, an incredibly expensive way of doing recovery. And almost certainly is totally unnecessary.

It also explains why the original poster complains about the recovery process using redo from a time after the backup completed: because the restored controlfile has no idea when to stop recovering, because it is itself out of date.

A one-size-fits-all recovery strategy is feeble DBA-ing. No offence.

>
> In fact what we do is not restore the online redo logs at all, we let
> the database rebuild them during the recovery. I will explain why.
>
> We do backups similar to you, but use netbackup to copy the files to
> tape. This copy can take from 10 minutes to 6 hours depending on the
> size of the db. If we let the system backup the redo logs, who knows
> where in the time frame they will get backed up. If we back them up
> after the "end backup" they will contain advanced data.

You can never, ever backup online redo logs hot. Period. It's not a question of "who knows when in time they will get backed up", but simply that you can never copy anything hot in Oracle without the resulting output files being internally inconsistent and all over the place -and hence unusable. Applying redo, however, makes a hot-copied datafile consistent again. And the 'backup controlfile' command is cunningly written by Oracle to produce a read-consistent snapshot of the controlfile. So for data files and control files, Oracle has provided a mechanism that makes an inconsistent hot copy usable. There is no such mechanism for the redo logs themselves. Therefore, they can't be copied. Simple as that.

>So we like to
> control the whole situation by doing the "end backup" followed by:
>
> alter system checkpoint;
> alter system switch logfile;

You're controlling nothing here. You're merely duplicating expensive checkpoints, since one is issued at a log switch in any case.

> host sleep 10
> alter database backup controlfile to '$gOUT_DIR/cntrlfile';
> alter database backup controlfile to trace;

And the point of doing two controlfile backups is???

> Note I now believe these last steps are out of order.

Before worrying about the order of steps, I'd take a serious look at your entire understanding, or lack thereof, of backup and recovery procedures. Yours are a mess, frankly.

>I think we
> should get the controlfile backup first, and then get the log switch.
> Note we do restore the backup of the control file before the recovery
> because again who knows where in the backup process the controlfile
> was backed up.

Oh dear.

"We restore the controlfile because who knows when it was backed up". Why restore something that doesn't actually need restoring?? You lose a 2GB datafile, you're telling me you restore the entire set of datafiles and the controlfiles? Why???? Oracle backup and recovery doesn't need to work like this. You only need to restore that which is causing a problem, recover it, and Oracle's internal SCN timestamping functions take care of the rest. That's why Oracle invented 'recover datafile' and 'recover tablespace' commands as well as 'recover database'. The idea is that you analyze what has gone wrong with your database, and pick the appropriate command to do the necessary recovery work. Your approach would seem to be 'let's not analyze anything, but just restore the lot, and recover the lot'. When the problem is merely one corrupt block in one small data file that is not in any case mission-critical, that would be, er, an interesting approach.

Regards
HJR

-- 
------------------------------------
Oracle insights at www.dizwell.com
------------------------------------