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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3f67ccd2$0$28117$afc38c87_at_news.optusnet.com.au...
> Thomas T wrote:
>
> [snip]
> > 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.

Okay, cool. Just wanted to make sure I wasn't doing anything else un-ordinary.

> >
> > 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.

I forgot about that fact (altering a tablespace requiring immediate backup). But for now, those are our "history" information, holding info that our application couldn't handle. (Fun things, like treating a number field like a string, with a max length of 7 characters, resulting in constraint violations.) I created it recently when we hit that limit. With the lifecycle of this application and database coming to a close, we might not ever alter that tablespace again. In fact, I'm toying with the idea of creating a 'history' schema in our new system to hold the tables of the old system- I'd probably make one large tablespace, drop all triggers & procedures, and make the resulting tablespace read-only.

> [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
Great, thanks again! One script on the web used "log current", and I couldn't understand why... but all my docs reference 7.3 or 8i, and if there were any differences, I must've missed the paragraph. I'm going to practice some recovery operations on my test server. I figure I can try (a) restoring an out-of-date tablespace, and (b) deleting a tablespace with Oracle offline and re-creating it with a text editor under the same filename. Option "b" should definately confuse Oracle... hopefully simulating a media failure. I'm going to perform a "test" 9i upgrade on my test server soon (to see if we -need- support to fly out and upgrade our db, but so far I'm darn sure I can do it myself), so I think I'm going to try recovery on both versions. Then I could get some practice on RMAN with 9i. (If 8i's is that bad, I might as well not bother with it.) Unfortunately I can't really test a 7.3 recovery until it happens... so I'll just put faith into those 7 year old hard drives.

-Thomas Received on Thu Sep 18 2003 - 15:06:16 CDT