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

"Glen A Stromquist" <glen_stromquist_at_no.spam.yahoo.com> wrote in message news:UIJ9b.22716$Cu3.4996_at_edtnps84...
> 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!
> >
> > [..snip..]
> > 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.
> =====================================================================
> spool backup<db>.sql
> select 'alter tablespace '||tablespace_name||' begin backup;' sqlline,
> 'host ocopy '||file_name||' d:\backup\;' sqlline,
> 'alter tablespace '||tablespace_name||' end backup;'
> from dba_data_files
> /
> spo off
> ======================================================================
>
>
> Do the same type of thing for your controlfiles etc..., then have the
> resulting sql run
>
> HTH
Actually, I never said I wanted to put all of the tablespaces into backup mode at the same time! But that's what the scripts I've seen on the 'net are doing. I just wanted to make sure that wasn't another myth, where the books say one thing, but the reality is different.

The server runs Netware, and if I try to intersperse a "host" command between the "begin backup" and "end backup" commands, the "host" conflicts with "end backup". SQL*Plus doesn't wait for the host command to finish before moving on to the next SQL command. If it did, things would be easier for me! I suppose I could load SQL*Plus, put a tablespace into backup mode, exit SQL*Plus, copy the file, load SQL*Plus, end backup mode, take the next tablespace into backup mode, exit SQL*Plus, copy the file, etc- but it doesn't quite appear "clean" to me. (Must be the programmer in me taking over, <grin>)

The db is small by most comparisons; about 2.5 gigabytes. From what I've read from Howard Rogers' manual (dated 2000), it makes sense to just take a backup of a few tablespaces a night- especially if disk space is low. (Which, on this old server, space is relatively low.) As long as long as the archive logs are being saved, the database won't mind if the data files are old. After all, only the tablespace files themselves are what's needed to bring Oracle up; the archive logs and redo information is what keeps Oracle up to date. I'd imagine I could even go back to a month's old set of tablespace data files, and still perform recovery- although I'd also imagine that it'd take forever for that database to finish recovery and come back online.

Thanks,

-Thomas Received on Tue Sep 16 2003 - 20:12:43 CDT