Re: Hot backup- want to group tablespaces in script during off-hours

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 17 Sep 2003 12:50:41 +1000
Message-Id: <3f67ccd2$0$28117$afc38c87@news.optusnet.com.au>

Thomas T wrote:

[snip]

>>
>> But technically, given the provisos you mention about DML activity, the
>> approach outlined in your first code snippet will work OK.

>
> HJR, thanks for the great information! I'll go take my newly-scheduled
> jobs off of hold (I created the scripts while I waited for feedback), and
> let the
> first one run tonight. This database isn't long for this world; we've got
> a new box running an old Oracle 8i (I'm forever locked into being years
> behind) with a Veritas Oracle Agent handling the online backups and
> archived
> redo logs. I even grab a cold backup once a month; which might change in
> the future once we make the move to the new system. (The promise of
> Oracle9i is in the near future- just in time for 10G to be released!) I
> think I'll change the scripts from FILO to FIFO order when ending backup
> mode; that might confuse -me- in a month or two. Glad to know I don't
> have to force log switches after each tablespace ends backup mode, too-
> the amount of "un-filled" 2k archive log files would probably increase
> quickly.
>
> What, in your opinion, would 'good practice' be? Would it be taking a
> backup of each tablespace individually? Or am I doing other things in an
> off-the-wall manner?

No, you're doing just fine. Good practice would be ye olde

alter tablespace X begin backup
copy it
alter tablespace X end backup
alter tablespace Y begin backup
copy it
alter tablespace Y end backup

...and so on.

That's because best practice is to minimise the amount of time that a tablespace stays in hot backup mode, because of the increased redo issue. (Actually, best practice these days is to use RMAN to do the backup, and then the whole issue goes away at a stroke. Not sure I would recommend RMAN in version 8, though. It was a swine).

But, since you have little or no redo to worry about, you can ditch that piece of advice. My only concern was... what happens when the script you write today is still being run in 5 years' time (it's happened!) because "it's always worked"... but in 5 years' time, your database is a big bugger doing 24 by 7 operations.

But that's all. For you today, and with that proviso, the 'bung everything into hot backup mode and copy it in one go' will serve you well enough.

>
> So I could just copy the read-only tablespaces onto the tape? I never
> thought of trying that (or searching for information on that). Then
> again, since they -are- read only, I might as well just leave them for
> Friday's cold-backup job.

Sounds like a good idea. In theory, you only ever need one backup of read-only tablespaces. But that's not much use when a failure happens and you find yourself thinking "I wonder where I put that 16-month-old backup?!" Weekly sounds a much better proposition. And yes, it can go straight on to tape.

Just bear in mind that if you ever alter it back to read-write mode, you need a fresh backup of it immediately, since the old one will be useless. Indeed, any alteration of a tablespace's read-write-only mode necessitates a new backup, whichever way the alteration is made.

[snip]

>>
>> I've never particularly bought that argument, only because online redo
>> log multiplexing, not to mention hardware mirroring, is supposed to mean
>> you never lose online redo logs. But if an extra log switch means people
>> feel more secure, they might as well go ahead and do it.

>
> Great info! The box I'm working on doesn't have hardware- or software-
> mirroring, and as you know, there's no multiplexing of redo logs on Oracle
> 7.3. I guess I'll stick with the log switch. (But you did remind me to
> look into possibly duplexing the logs on the 8i machine.) One question
> though, aren't "alter system switch logfile" and "alter system archive log
> current" basically the same command? "Log current" is supposed to
> manually archive the current group, but doesn't the log file get archived
> anyway from "switch logfile"?

Until you get to very recent versions, the two commands are essentially identical. By causing a log switch, you invoke ARCH. By invoking ARCH, you cause a log switch. There is, IIRC, a feature in 9i whereby the current log can be archived without the log switch. Quite why anyone would need that, I can't think offhand (the theory is, I suppose, that you get your archive without inducing the checkpoint that accompanies a log switch)... but I would always do the log switch command myself.

Regards
HJR Received on Tue Sep 16 2003 - 21:50:41 CDT