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

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. (Yes,
> yes,
> I know, what took me so long...) Anyway, I'm setting up the scripts to
> take
> the hot backups, and I have a question. The current cold backup is done
> off-hours, when everybody's gone home (or darn well should have!) and
> nobody's on the system. In changing this for an online backup, I'd like
> to take two tablespaces offline a night, 4 days a week, and on the 5th day
> (Friday), take a full cold backup. 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. Most of their operations right
> now involve just select statements, not much DML. Even when there is a
> lot of DML ops going on, not too many people are here at 0400 in the
> morning!
>
> So here's my questions! Do I have to force a logfile switch after
> bringing
> each tablespace out from backup mode? If I do the following:
>
> 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? Or should it be more like:
>
> alter tablespace usertemp end backup;
> alter system switch logfile;
> alter database backup controlfile to trace;
> alter tablespace userdata end backup;
> alter system switch logfile;
> alter database backup controlfile to trace;
>
> That seems like overkill, though... not to mention extra controlfile
> backup
> dumps that I'd have to keep an eye on. 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.

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

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

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

>
> 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
>
> -Thomas
Received on Tue Sep 16 2003 - 15:19:54 CDT