rem ***************************************************************************
rem **
rem ** Dblist.sql file 
rem ** 
rem ** Geracao de scripts para backup full on-line
rem **
rem ** Recebe os seguintes parametros
rem ** &1 - ORACLE_SID
rem ** &2 - data base do backup
rem ** &3 - destino dos arquivos de backup
rem **
rem ***************************************************************************
set serverout on
set heading off
set feedback off
set verify off
set linesize 125
declare 
  cursor C1 is
    select tablespace_name, file_name from sys.dba_data_files
    order by tablespace_name;
  v_tablespace_atu     varchar2(30) := ' ';
  v_tablespace_ant     varchar2(30) := 'firsttime';
  v_filename           varchar2(256) := ' ';
begin
  DBMS_OUTPUT.enable(10000);
  open C1;
  loop
     fetch C1 into v_tablespace_atu, v_filename;
     exit when C1%notfound;
     if v_tablespace_atu != v_tablespace_ant then
        if v_tablespace_ant = 'firsttime' then
           DBMS_OUTPUT.put_line('connect internal/preb'||'&1');
        else
           DBMS_OUTPUT.put_line('alter tablespace '||v_tablespace_ant||' end backup;');
        end if;
        DBMS_OUTPUT.put_line('alter tablespace '||v_tablespace_atu||' begin backup;');
        v_tablespace_ant := v_tablespace_atu;
     end if;
     DBMS_OUTPUT.put_line('host ocopy '||v_filename||' &3'||'\'||substr(v_filename, instr(v_filename,'\',-1)+1));
     DBMS_OUTPUT.put_line('host pkzip25 -add '||'&3'||'\'||'bac'||'&2 '||'&3'||'\'||substr(v_filename, instr(v_filename,'\',-1)+1));
     DBMS_OUTPUT.put_line('host del '||'&3'||'\'||substr(v_filename, instr(v_filename,'\',-1)+1));
  end loop;
  DBMS_OUTPUT.put_line('alter tablespace '||v_tablespace_atu||' end backup;');
  close C1;
  DBMS_OUTPUT.put_line('alter system switch logfile;');
  DBMS_OUTPUT.put_line('alter system archive log all;');
  DBMS_OUTPUT.put_line('alter database backup controlfile to trace;');
  DBMS_OUTPUT.put_line('alter database backup controlfile to '||''''||'&3'||'\'||'controlf_&2'||'.ctf'||''''||' reuse;'); 
  DBMS_OUTPUT.put_line('host pkzip25 -add '||'&3'||'\'||'bac'||'&2 '||'&3'||'\'||'controlf_&2'||'.ctf;');
  DBMS_OUTPUT.put_line('host del '||'&3'||'\'||'controlf_&2'||'.ctf;');
  DBMS_OUTPUT.put_line('exit');
end;
/
exit