Re: Online NT Backup Script -Help

From: Klaus Zeuch <KZeuchnospam_at_hotmail.com>
Date: Wed, 4 Oct 2000 13:55:09 +0200
Message-ID: <8rf7k8$9ac$1@papyrus.erlm.siemens.de>

You could run a procedure like the following that creates a command-script (for svrmgrl) - replace your_directory, your_command_file and your_target_directory with your settings. For a successful execution parameter utl_file_dir must be set in your init.ora - see Oracle doc's.

declare

   v_fileid UTL_FILE.FILE_TYPE;
   v_buffer varchar2(200);
begin

   begin

      v_fileid := UTL_FILE.FOpen('your_directory', 'your_command_file', 'w', 500);

   exception

      WHEN UTL_FILE.INVALID_PATH THEN
         Raise_application_Error(-20999,'UTL_FILE: directory error');
      WHEN UTL_FILE.INVALID_MODE THEN
         Raise_application_Error(-20999,'UTL_FILE: OpenMode error');
      WHEN UTL_FILE.INVALID_OPERATION THEN
         Raise_application_Error(-20999,'UTL_FILE: Operation error');

   end;

• backup files of offline / read-only tablespaces first for v_tbs in (SELECT tablespace_name FROM dba_tablespaces WHERE status != 'ONLINE' ) loop for v_file in (SELECT file_name FROM dba_data_files WHERE tablespace_name = v_tbs.tablespace_name ) loop v_buffer := 'host ''copy ' || v_file.file_name || ' your_target_directory'';'; UTL_FILE.Put(v_FileId, v_Buffer); UTL_FILE.New_Line(v_FileId); end loop; end loop;
• backup files of online tablespaces for v_tbs in (SELECT tablespace_name FROM dba_tablespaces WHERE status = 'ONLINE' ) loop v_buffer := 'ALTER TABLESPACE ' || v_tbs.tablespace_name || ' BEGIN BACKUP;'; UTL_FILE.Put(v_FileId, v_Buffer); UTL_FILE.New_Line(v_FileId); for v_file in (SELECT file_name FROM dba_data_files WHERE tablespace_name = v_tbs.tablespace_name ) loop v_buffer := 'host ''copy ' || v_file.file_name || ' your_target_directory'';'; UTL_FILE.Put(v_FileId, v_Buffer); UTL_FILE.New_Line(v_FileId); end loop; v_buffer := 'ALTER TABLESPACE ' || v_tbs.tablespace_name || ' END BACKUP;'; UTL_FILE.Put(v_FileId, v_Buffer); UTL_FILE.New_Line(v_FileId); end loop; -- don't forget: controlfile and redo-logs are essential parts of a valid online-backup -- e.g. alter database backup controlfile to trace;

   UTL_FILE.FClose(v_FileId);
end;
/

Klaus

swaite <swaite_at_equiplease.com> schrieb in im Newsbeitrag: rdrC5.1$fc1.224_at_wdc-read-01.qwest.net...
> 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
>
>
>
Received on Wed Oct 04 2000 - 06:55:09 CDT