Re: SQL generating SQL

From: Niall Litchfield <Niall.Litchfield_at_btinternet.com>
Date: Sat, 29 Sep 2001 10:53:27 +0100
Message-ID: <9p459n$n3r$1@plutonium.btinternet.com>

Kevin

first up it is good that you realise this. Usually this is found out only when a backup needs to be applied.

Oracle has two basic physical backup modes

Cold backup. This is nice and simple. Shutdown the database and copy all the datafiles, controlfiles,init.ora and redo log files across to tape. Unfortunately you state that you cannot shut the database down so you can't use this method.
Hot backup. The idea here is quite simple as well. You tell oracle that the database is being backed up, on a tablespace by tablespace basis. you then copy the datafiles for that tablespace to tape, and tell oracle that the tablespace is no longer being backed up. at the end of the operation you copy all of the archived redo logs to tape as well. finally you make a binary backup of the control file. You do not backup the online redo logs. basically by telling oracle that the database is being backed up oracle records block information about the tablespaces being backed up (rather than just before and after row images) in the redo logs. Thus if you recover although your files will be inconsistent oracle can bring them up to a consistent point by applying the redo.

In order to do this the database MUST be in archivelog mode, so that you can keep all the redo.

How do you do it. well this is where the using sql to write sql comes in.

a backup script might look a bit like this

ALTER TABLESPACE USERS BEGIN BACKUP;

HOST dd '/u01/oracle/instancename/users01.dbf'
'/u01/backups/instancename/users01.dbf'
HOST dd '/u01/oracle/instancename/users02.dbf'
'/u01/backups/instancename/users02.dbf'

ALTER TABLESPACE USERS END BACKUP; <etc>
ALTER DATABASE BACKUP CONTROL FILE TO ''/u01/backups/instancename/becf.ctl'; ARCHIVE LOG ALL;
HOST dd '/u01/oracle/instancename/archives/*.log' '/u01/backups/instancename/archives/*.log';

which achieves (if i can recall my unix commands correctly) what i described above.

well you can just write a script along the lines shown above. this will backup just fine. However eventually you might add a datafile to the users tablespace. at this point a manual script needs updating. if however you have used sql to write the script (by query v$datafiles) your script will automatically be updated.

You should at the very least read the backup and recover guide available at technet.oracle.com. There is also a pretty good discussion in kevin loney's Oracle DBA Handbook which is an essential reference.

finally you can also keep logical backups by using cron to automate an export each night.

HTH Niall
"Kevin S. Smith" <ksmith_at_erieplating.com> wrote in message news:tr8tl18gv3i262_at_corp.supernews.com...
> I have just come to the stunning realization (yes, I am sort of new at
this)
> that my backups that I have been creating are useless. I need to do
> complete backups at night without shutting down. Right now I am just
using
> the cron (Linux rh6.2) to dump the directories to tape without shutting
> down. Well, this is useless because the datafiles are being written to
and
> the headers and synch are getting hosed up. To make a long story short, I
> am looking for an efficient script that would capture all my datafiles,
> control file, and archive logs, so I can put them in "backup mode", and
then
> copy them to tape one at a time. I am told the best way to do this is to
> use "sql generating sql" to create a script. It is my understanding that
> this method would freeze the headers while still allowing the datafile to
be
> written to. Please tell me if i am barking up the wrong tree. This is
> obviously very important and any help would be appreciated.
>
> Oracle 8161 on Linux RedHat 6.2
>
> Thanks
>
>
>
Received on Sat Sep 29 2001 - 04:53:27 CDT