Re: Online NT Backup Script -Help

From: <oratune_at_aol.com>
Date: Tue, 03 Oct 2000 22:20:25 GMT
Message-ID: <8rdm33$ilm$1@nnrp1.deja.com>

In article <eRrC5.2$fc1.484_at_wdc-read-01.qwest.net>,   "swaite" <swaite_at_equiplease.com> wrote:
> I am looking for the script to do an Online (Archivelog) backup of an
 Oracle
> 8i Database running on Win NT where I move (copy) the appropriate
 files to
> an intermediate drive and then to tape instead of direct to tape.
 After I
> put the tablespaces into backup state I want to copy the corresponding
> datafiles to a network drive and back them up from there. Any help
 would
> really be appreciated
>
> Sean Waite
> Sunrise International Leasing
> 612-513-3272
> swaite_at_equiplease.com
>
>

The task is not as daunting as it may seem. Much of the needed information is available from DBA_DATA_FILES. The following PL/SQL script should generate the necessary batch file for you:

declare

     cursor get_dfile_info is
     select tablespace_name, file_name
     from dba_data_files
     order by tablespace_name;
     prev_tspace   dba_data_files.tablespace_name%type:=null;
begin
     for dfile_rec in get_dfile_info loop
         --
         -- First tablespace
         --
         if prev_tspace is null then
               dbms_output.put_line('alter
tablespace '||dfile_rec.tablespace_name||' begin backup;');
               dbms_output.put_line('host
ocopy '||dfile_rec.file_name||' <location here>');
         end if;
         --
         -- Next file for same tablespace
         --
         if prev_tspace = dfile_rec.tablespace_name then
               dbms_output.put_line('host
ocopy '||dfile_rec.file_name||' <location here>');
         end if;
         --
         -- Next tablespace in list
         --
         -- Use prev_tspace to bring previous tablespace
         -- back online
         --
         if prev_tspace != dfile_rec.tablespace_name then
               dbms_output.put_line('alter tablespace '||prev_tspace||'
end backup;');
               dbms_output.put_line('alter
tablespace '||dfile_rec.tablespace_name||' begin backup;');
               dbms_output.put_line('host
ocopy '||dfile_rec.file_name||' <location here>');
         end if;
         prev_tspace := dfile_rec.tablespace_name;
     end loop;
     --
     -- Bring final tablespace back online
     --
     dbms_output.put_line('alter tablespace '||prev_tspace||' end

backup;');
end;
/

Spool the output from this script to a file named backup.sql (for lack of creativity). Remember to put your specific location/file name in the proper locations in this script. From SQL*Plus you should be able to then run the resulting script and perform a hot backup of your datafiles.

--

David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Oct 03 2000 - 17:20:25 CDT