Re: How to set the WHOLE Database in backup mode

James Lorenzen <lorenzen+news_at_tiny.net> wrote in message news:<230420030849585720%lorenzen+news_at_tiny.net>...
> If you do not want to shutdown your dtabase (and lose the SGA and other
> interesting bits). The following PL*SQL can accomplish this for you.
> Please note that :
> 1. Hot backups require that archive log mode is on
> 2. Temp tablespaces based on temp files cannot be backed up.
>
> The following PL*SQL code will place the Oracle database into backup
> mode. It selects all of the tablespaces and uses the Oracle 8i ³EXECUTE
> IMMEDIATE² command. This is just a sample of the code and it requires
> more extensive error checking. This can be handled by adding an
> exception handler to this code.
>
> Errors will occur if the database is not in archive log mode. This code
> must be run as an Oracle user with the privileges for backup (dba or
> sysoper).
>
> The database can be taken out of backup mode by replacing the beg_end
> value with the word END and remove the log switch command.
>
> declare
> cmd VARCHAR2(1000) ;
> beg_end VARCHAR2(6) := 'BEGIN' ;
> begin
> dbms_output.enable(1000000) ;
> cmd := 'Begin @' || TO_CHAR(SYSDATE,'hh24:mi:ss');
> dbms_output.put_line(cmd) ;
> cmd := 'ALTER SYSTEM SWITCH LOGFILE ' ;
> EXECUTE IMMEDIATE cmd ;
> dbms_output.put_line(cmd) ;
> FOR ts_rec IN (SELECT tablespace_name FROM dba_tablespaces) LOOP
> cmd := 'ALTER TABLESPACE ' || LOWER(ts_rec.tablespace_name)
> || ' ' || beg_end || ' BACKUP ' ;
> EXECUTE IMMEDIATE cmd ;
> dbms_output.put_line(cmd) ;
> END LOOP ;
> cmd := 'End @' || TO_CHAR(SYSDATE,'hh24:mi:ss') ;
> dbms_output.put_line(cmd) ;
> end ;
>
> HTH
> James
>

But the OP stated:
"I wonder if having to use this command
a dozen of times (for each tablespace separately) will garantee that all tablespaces are in sync when the last of them is set in backup mode."

if his requirement is that the tablespaces (read datafiles in backupset) be consistent - then your code might accomplish the task that you interpreted it to be - but it does not fit his requirements. A cold backup would produce a backup set with all tablespaces (again, datafiles) in sync.

Of course, during media recovery - the datafiles would be brought to a consistend SCN, if the datafiles were generated by a hot backup method.

Paul

> In article <1ac7c7b3.0304221633.67ee444e_at_posting.google.com>, Paul
> Drake <drak0nian_at_yahoo.com> wrote:
>
> > Rick Denoire <100.17706_at_germanynet.de> wrote in message
> > news:<frg8avcq7pigvia1qt6jpni7mcm9skvnff_at_4ax.com>...
> > > Well we know the command "alter tablespace <tralala> begin backup",
> > > but if there are a dozen of tablespaces, and one has to make a hot
> > > Snapshot of the DB files, then I wonder if having to use this command
> > > a dozen of times (for each tablespace separately) will garantee that
> > > all tablespaces are in sync when the last of them is set in backup
> > > mode.
> > >
> > > So is there any other command to set the DB as a whole in the backup
> > > mode?
> > >
> > > Thanks
> > > Rick Denoire
> >
> > sure -
> > sqlplus /nolog
> >
> > alter system checkpoint;

> > alter system archive log current;
> > shutdown abort
> > startup restrict
> > shutdown immediate
> >
> > <run your cold backup job>
> >
> > startup
> > quit
> >
> > you now have a consistent backup set - and you didn't even have to put
> > any tablespaces in backup mode - and it won't require any recovery.
> >
> > if that it not what you wanted, then you must change what you want.
> > "hot" or "consistent" are mutually exclusive concepts.
> >
> > Pd
Received on Wed Apr 23 2003 - 14:00:08 CDT