Re: Hot backups

From: John Hough <q6y_at_ornl.gov>
Date: 1997/04/09
Message-ID: <334B956D.21B0@ornl.gov>#1/1

Brian Lane wrote:
>
> Hi all,
> Does anyone have any Sun Unix scripts for doing Oracle hot backups.
> We have just found out that our current backup srategy is inconsistant and
> now the s... has hit the fan.
>
> Regards
> Brian

Brian:

Here is a SQL Command File to create a hot backup script. You might have to add a few "/" or semicolons, but it should create you a pretty good backup command file.

Note: This script only backups the tablespace datafiles. For a backup to be complete you will need to backup controlfiles, and of course archive log files. Note that in the cursor it assumes that the temporary tablespace is named 'temp'. This will of course have to be changed if your name for the temporary space is different.

Hope this helps!

John Hough

##################################################################

set serveroutput on size 10000
set echo off feed off pages 0
spool hot.backup
select 'File created '||to_char(sysdate,'dd-Mon-yy hh24:mm:ss')   from dual;
prompt
begin
  declare
    target_dir varchar2(100) := '/data/oracle/bkp/prd/backups';     source_file varchar2(100);
    ts_name varchar2(100);
    prev_ts_name varchar2(100);
    cursor mycur is

      select file_name,lower(tablespace_name)
        from sys.dba_data_files
       where instr(file_name,'temp') = 0
       order by 2;

  begin
    prev_ts_name := 'X';
    open mycur;
    fetch mycur into source_file,ts_name;     while mycur%found loop

      if ts_name <> prev_ts_name then
         dbms_output.put_line ('#######################');
         dbms_output.put_line ('# Tablespace '||ts_name||'. . .');
         dbms_output.put_line ('#######################');
         dbms_output.put_line ('alter tablespace '||ts_name||' begin
backup');
      end if;
      dbms_output.put_line ('host cp '||source_file||' '||target_dir);
      prev_ts_name := ts_name;
      fetch mycur into source_file,ts_name;
      if ts_name <> prev_ts_name then
         dbms_output.put_line ('alter tablespace '||prev_ts_name||' end

backup')
;

         dbms_output.put_line ('alter system checkpoint');
      end if;
    end loop;
         dbms_output.put_line ('alter tablespace '||prev_ts_name||' end

backup')
;

         dbms_output.put_line ('alter system checkpoint');   end;
end;
/ Received on Wed Apr 09 1997 - 00:00:00 CDT