| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> 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?
>
> 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
>
>
I don't know where you got the idea that you have to put *all* of the
tablespaces in backup mode at the same time! Or for that matter what is
the purpose of backing up only one or two tablespaces a night? How big
is this db?
Ideally, (what I do) is run some sql that creates your backup script, which puts a tablespace into backup mode, ocopies it, puts the tablespace out of backup mode, does the next tablespace the same way, etc.. (excluding temp tablespaces,)
eg.
'host ocopy '||file_name||' d:\backup\;' sqlline,
'alter tablespace '||tablespace_name||' end backup;'
from dba_data_files
Do the same type of thing for your controlfiles etc..., then have the resulting sql run
HTH Received on Tue Sep 16 2003 - 14:42:44 CDT
 |
 |