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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3f677106$0$28121$afc38c87_at_news.optusnet.com.au...
> Thomas T wrote:
>
> > Hey all, got a question. I've got a few tablespaces in an Oracle7.3
> > database, and just recently put the system into archivelog mode.
> > [..snip..]
> > I know putting more then one
> > tablespace into backup mode can generate excessive redo; but nobody's on
> > the system- and if they are, they'd be one or two people, and I doubt
they
> > could do much work in 5 minutes to strain the redo logs.
> > [..snip..]
> > So here's my questions! Do I have to force a logfile switch after
> > bringing each tablespace out from backup mode?
> >
> > alter tablespace userdata begin backup;
> > alter tablespace usertemp begin backup;
> > {copy files}
> > alter tablespace usertemp end backup;
> > alter tablespace userdata end backup;
> > alter system switch logfile;
> > alter database backup controlfile to trace;
> >
> > Would that hamper recovery in any way?
> > [..snip..] And one final question; do I have
> > to end backup on these tablespaces in FILO order?
>
> If there is very little DML taking place on the database, then there is no
> harm in putting several tablespaces into hot backup mode simultaneously,
> copying the lot in one go, and then ending the backup.
>
> 'Begin backup' freezes the datafiles' SCN from the updating tendencies of
> CKPT and, as you recognize, causes block-level redo to be generated by the
> first bit of DML that touches a block. Whilst the first one is purely
> internal house-keeping, the second effect is enough to bring a
> DML-intensive database to its knees. Since you aren't a DML-intensive
> database, there's no need to kneel!
>
> Tablespaces can be brought out of hot backup mode in any order.
>
> A single log switch at the end is sufficient. The point of that log switch
> is to make the backupset just taken 'complete' and recoverable by causing
> any redo that was generated whilst the tablespaces were being backed up to
> be itself backed up (ie, archived). If you switched at the end of each
> tablespace, your 'recoverable backup granularity' would be at the
> tablespace level. If you leave the switch to the end, the entire set of
> tablespaces might not be recoverable with the latest backup. But since you
> don't generate much redo anyway, what does it really matter?
>
> The only concern I have on your approach is that although it's fine for
now,
> it's not 'good practice', and if your business conditions ever change in
> the future, perhaps when you have long since left, some mug or other will
> still be running a script 'wot the previous DBA knocked up' that *will*
> cause problems... because by then, maybe they will be running 24x7 with
> masses of DML. It's never a good idea to hard-code practices which work
> today but which might not work tomorrow.
>
> 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?

> > Thanks! I'm playing with some of our read-only tablespaces right now
> > while
> > the users are online. I'd imagine not much-- if ANY-- redo log
operation
> > is
> > occuring when I put the read-only spaces into backup mode.
>
>
> Of course, theoretically, you don't need to backup read-only tablespaces
at
> all after the time you put them into hot backup mode.
>
> In practice, you of course do... and when you do so, there's no need to
put
> them into hot backup mode. The datafile headers are already locked from
> being changed by CKPT, and there's no possibility of block fractures,
> because no blocks are being updated.

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.

> >I think I
> > might be nit-picking over these details, especially over the order in
> > which the
> > tablespaces end backup mode. If I am overanalyzing, feel free to let me
> > know! I assume (uh-oh) that these questions might still be valid for
9i?
>
>
> Correct. The technicalities of hot backups haven't changed since about
> version 7.

That's good to know; I'm not -quite- working with obsolete procedures.

> > The scripts I've seen on the 'net, including 8i, perform auto-generated
> > sql's by selecting all the tablespace_name from dba_data_files- thereby
> > putting all the tablespaces into backup mode. Then they issue just one
> > switch logfile after all the copies are done and the tablespaces are out
> > of
> > backup mode. I've even seen a few that don't switch the logfile!
>
> Again, any script that puts the entire database into hot backup mode, then
> copies the lot, and then takes them out of hot backup mode is asking for
> trouble. In the presence of even moderate amounts of DML, the extra redo
> generated will likely bring the database to a grinding halt. That is
> definitely not good practice.
>
> The idea of *not* taking a logfile switch is one to which I myself am
> partial, because a log switch will happen, eventually, anyway, given
enough
> activity on the database. However, it is true that if you had a
> catastrophic failure before that switch you *might* end up losing the last
> bit of redo in the online logs which makes the hot-backed-up datafile
> copies in your latest backup internally consistent. Without that log
> switch, therefore, and with the online logs totally lost, you would then
> have to restore from the last-but-one backup, and perform an incomplete
> recovery to a point well short of where you could have gotten if you had
> performed the log switch.
>
> 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 softwaremirroring,  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"?

> > Or, (laughs) is it just another Oracle Myth that the idea of "putting
all
> > the tablespaces into backup mode at once" is bad/harmful/stupid?
>
>
> It's definitely not a myth. In hot backup mode, it is easy to measure an
> increase of 30 times the amount of redo generated by a transaction as
> compared to the same transaction in non-hot-backup-mode. Extra redo being
> written in those quantities means you stress LGWR, whom you wait on before
> a commit can be a commit. If LGWR is writing more, then logs fill up
> quicker, and log switching takes place quicker, and that stresses ARCH.
And
> if LGWR catches up with ARCH, you have a database stall on your hands.
> Never mind the possible existence of standby databases, where if ARCH is
> stressed, so is your network traffic. Or even, with 9i Data Guard, if LGWR
> is stressed, there's a possibilty (depending on how you configure Ddata
> Guard) of the primary database simply doing a shutdown abort because LGWR
> can't keep up.
>
> The assumption underpinning those dire scenarios, however, is always that
> your database is subject to DML during the hot backup (a reasonable
> assumption given that most people do hot backups to support 24 x 7
> operations). If that's not true for you, then the dire scenarios also
> become not true for you.
>
> But are they ever true for someone else? Absolutely.
>
> Regards
> HJR
Glad to know it's not another myth. I'm still reeling from the impact of recently discovering the tuning myths! (So much for a cache hit ratio.) Our future system would definately be affected by that scenario- but not as much as you guys with "real" (aka big OLTP's) databases probably would, so I'll keep that non-myth info in mind. But I think this old system will be safe for a while during the late night backup; especially since right now, at 2230 EST, I'm the only one logged in to any of our servers. Which reminds me; I missed lunch- or dinner, or both. Thanks again!

-Thomas Received on Tue Sep 16 2003 - 21:29:53 CDT