REM Script to create a hot backup recovery script on UNIX
REM Created 5/03/01 MRA
REM
create table rec_temp (line_no number,line_txt varchar2(2000))
storage (initial 1m next 1m pctincrease 0);
truncate table rec_temp;
set verify off embedded off lines 1000 termout off long 1000
define dest_dir=&1;
define tbsp=&2;
define file_date=&3;
declare
--
-- Declare cursors
--
-- Cursor to get the/all tablespace names
--
cursor get_tbsp is
select tablespace_name from dba_tablespaces
where tablespace_name like upper('%&2%');
--
-- Cursor to create HOST recovery commands
--
cursor uncomp_com (tbsp varchar2) is
select
'/bin/uncompress -c < &&dest_dir/'||tablespace_name||'_'||to_char(to_date('&3','dd/mm/yy'),'dd_mon_yy')||'.Z | /bin/tar xf -'||chr(10)
from dba_tablespaces where tablespace_name=tbsp;
--
-- Temporary variable declarations
--
tbsp_name varchar2(64);
line_num number:=0;
line_text varchar2(2000);
fetch_text varchar2(2000);
min_value number;
first_tbsp boolean;
temp_var varchar2(128);
--
-- Begin build of commands into temporary table
--
begin
dbms_output.put_line('tbsp:'||'&tbsp');
--
-- first, create script header
--
line_num := line_num+1;
select 'REM Online Recovery Script for '||name||' instance' 
into line_text from v$database;
insert into rec_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM Script uses UNIX Uncompress and tar format backup commands'  
into line_text from dual;
insert into rec_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM created on '||to_char(sysdate, 'dd-mon-yyyy hh24:mi')||' by user '||user  
into line_text from dual;
insert into rec_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM developed for Knology by Mike Ault - DBAGroup 3-May-2001'  
into line_text from dual;
insert into rec_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM '  
into line_text from dual;
insert into rec_temp values (line_num,line_text);
line_num := line_num+1;
select 'spool &&dest_dir/log/hot_bu_rec_'||to_char(sysdate,'dd_mon_yy')||'.log'
into line_text from dual;
insert into rec_temp values (line_num,line_text);
line_num := line_num+1;
--
-- Now get tablespace names and loop through until all are handled
--
commit;
open get_tbsp;
first_tbsp:=TRUE;
loop
-- 
-- Get name
--
	fetch get_tbsp into tbsp_name;
	exit when get_tbsp%NOTFOUND;
--
-- Add comments to script showing which tablespace
--
	select 'REM' into line_text from dual;
  	insert into rec_temp values (line_num,line_text);
	line_num:=line_num+1;
	select 'REM Recovery for tablespace '||tbsp_name into line_text from dual;
  	insert into rec_temp values (line_num,line_text);
	line_num:=line_num+1;
	select 'REM' into line_text from dual;
  	insert into rec_temp values (line_num,line_text);
	line_num:=line_num+1;
--
-- The actual recovery commands are per tablespace
--
	open uncomp_com (tbsp_name);
        line_text:=NULL;
	fetch uncomp_com into fetch_text;
	select trim(fetch_text) into line_text from dual;
        insert into rec_temp values (line_num,line_text);
        line_num:=line_num+1;
	close uncomp_com;
end loop;
  close get_tbsp;
commit;
--
-- Now get all archived logs
--
  select 'REM' into line_text from dual;
  insert into rec_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'REM Recovery for archive logs' into line_text from dual;
  insert into rec_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'REM' into line_text from dual;
  insert into rec_temp values (line_num,line_text);
  line_num:=line_num+1;
--
-- The next command builds the actual recovery command  
--
  temp_var:=null;
  select substr (value,1,instr(value,'/',-1,1)) into temp_var 
   from v$parameter where name='log_archive_dest';
  if temp_var is not null 
  then
  select '!/bin/uncompress -c < &dest_dir/'||substr (value,instr(value,'/',-1,1)+1,length(value))||'_'||
  to_char(to_date('&3','dd/mm/yy'),'dd_mon_yy')||'.Z | tar xf - '
  into line_text from v$parameter where name='log_archive_dest';
  insert into rec_temp values (line_num,line_text);
  line_num:=line_num+1;
  select '!/bin/uncompress '||substr (value,1,instr(value,'/',-1,1))||'/*.Z'
  into line_text from v$parameter where name='log_archive_dest';
  insert into rec_temp values (line_num,line_text);
  line_num:=line_num+1;
  else
   select 'REM no log_archive_dest specified' into line_text from dual;
   insert into rec_temp values (line_num,line_text);
   line_num:=line_num+1;
  end if;
  temp_var:=null;
  select substr (value,10,instr(value,'/',-1,1)) into temp_var 
   from v$parameter where name='log_archive_dest_1';
  if temp_var is not null 
  then
  select '!/bin/uncompress -c < &dest_dir/'||substr (value,instr(value,'/',-1,1)+1,length(value))||'_'||
  to_char(to_date('&3','dd/mm/yy'),'dd_mon_yy')||'.Z | tar xf - '
  into line_text from v$parameter where name='log_archive_dest_1';
  insert into rec_temp values (line_num,line_text);
  line_num:=line_num+1;
  select '!/bin/uncompress '||substr (value,10,instr(value,'/',-1,1))||'/*.Z'
  into line_text from v$parameter where name='log_archive_dest_1';
  insert into rec_temp values (line_num,line_text);
  line_num:=line_num+1;
  else
   select 'REM no log_archive_dest_1 specified' into line_text from dual;
   insert into rec_temp values (line_num,line_text);
   line_num:=line_num+1;
  end if;
   select 'spool off'||chr(10) into line_text from dual;
   insert into rec_temp values (line_num,line_text);
   line_num:=line_num+1;
   commit;
end;
/
rem
rem Now generate output based on rec_temp table contents
rem
set verify off feedback off heading off termout off pages 0
set embedded on lines 1000
column line_no noprint
column dbname new_value db noprint
select value dbname from v$parameter where name='db_name';
spool rep_out/&db/thot_rec.sql
select * from rec_temp order by line_no;
spool off
rem directory syntax for UNIX
rem 
! sed '1,$ s/ *$//g' rep_out/&db/thot_rec.sql>rep_out/&db/hot_rec.sql
rem
drop table rec_temp;
set verify on feedback on heading on termout on pages 22
set embedded off lines 80
clear columns
undef dest_dir
undef tbsp
undef file_date