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

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 systemand  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?

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

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?

-Thomas Received on Tue Sep 16 2003 - 13:03:21 CDT