rem $DBA/creback.sql
rem
rem This script creates a bkup_<sid>.lst Unix shell script which is then used to
rem backup the data files for the current database.
rem
rem Parameters:
rem	&1 = Oracle SID
rem	&2 = "COLD", if performing a cold backup,
rem	     "HOT", if performing a hot backup (one tablespace at a time),
rem	     "HOTCTRL", if performing a hot control file backup,
rem	     "HOTBEGIN", if performing a hot backup "BEGIN BACKUP" (to ready
rem			 all tablespaces to be backed up at the same time),
rem	     "HOTEND", if performing a hot backup "END BACKUP" (to un-ready
rem		       all tablespaces from being backed up at the same time),
rem	     "HOTALLCTRL", if performing a batch hot control file backup.
rem	&3 = "FS", if tablespace files and online redo log files are located on
rem		   filesystems,
rem	     "RAW", if tablespace files and online redo log files are located on
rem		    raw device partitions.
rem	&4 = Disk filesystem or tape device to backup to (or ARCHDIR if HOTCTRL
rem	     or HOTALLCTRL).
rem	&5 = 1, if a "restore_<SID>" file is to be created,
rem	     0, if no "restore_<SID>" file is to be created.
rem	&6 = Type of backup destination; Must be one of: "DISK" or "TAPE"
rem	&7 = Disk filesystem or tape device to backup to (valid if HOTCTRL).
rem
rem NOTE:
rem	For Oracle 7.3, modify begin_cursor to skip tablespaces whose CONTENTS
rem	  is TEMPORARY.
rem
rem
rem <<<<<<<<<<<<<<<<<<< MODIFICATION HISTORY >>>>>>>>>>>>>>>>>>>
rem 01/28/98 Brian Lomasky	Remove asterisks from output file.
rem				  Double-escape v$log, since fix_bkup will
rem				  always remove the first one.
rem 01/23/98 Brian Lomasky	Added Oracle 7.3 documentation note.
rem 03/03/97 Brian Lomasky	Abort if any SQL*Plus error is returned.
rem 02/21/97 Brian Lomasky	Add HOTBEGIN, HOTEND, and HOTCTRL backup types
rem				  parameter, Add raw partition processing, Add
rem				  destination device.  Modify restore file
rem				  creation.  Add type of backup destination.
rem 11/19/96 Brian Lomasky	Add reset of any previously-failed hot backup
rem				  for a tablespace.
rem 11/11/96 Brian Lomasky	Original
rem
set echo off
set feedback off
set heading off
set pagesize 0
set verify off
set termout off
drop table c_backu_temp;
set termout on
define q=chr(39)
create table c_backu_temp (	code VARCHAR2(1),
				lineno NUMBER,
				text VARCHAR2(132));
declare
	cursor begin_cursor is select
		distinct b.tablespace_name
		from v$backup a, dba_data_files b
		where a.file# = b.file_id and a.status = 'NOT ACTIVE'
		/* and contents <> 'TEMPORARY' */
		order by 1;
	cursor end_cursor is select
		distinct b.tablespace_name
		from v$backup a, dba_data_files b
		where a.file# = b.file_id and a.status = 'ACTIVE'
		order by 1;
	cursor ctrl_cursor is select
		rtrim(name)
		from v$controlfile
		where upper('&2') = 'COLD' or upper('&2') = 'HOTCTRL' or
			upper('&2') = 'HOTALLCTRL';
	cursor log_cursor is select
		rtrim(member)
		from v$logfile
		where upper('&2') = 'COLD';
	cursor dbf_cursor is select
		rtrim(tablespace_name),
		rtrim(file_name)
		from sys.dba_data_files
		where upper('&2') != 'HOTEND' and upper('&2') != 'HOTCTRL' and
			upper('&2') != 'HOTALLCTRL'
		order by 1, 2;
	lv_name			varchar2(257);
	lv_tablespace		sys.dba_data_files.tablespace_name%TYPE;
	prev_tablespace		sys.dba_data_files.tablespace_name%TYPE;
	lv_file			sys.dba_data_files.file_name%TYPE;
	lv_lineno		number;
	n			number;
	a_lin			varchar2(132);
	my_directory		varchar2(80);
	my_file_name		varchar2(80);
	temp_file		varchar2(80);
	np			number;
	lnp			number;

	function wri(x_cod in varchar2, x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 131
		then
			lv_lineno := lv_lineno + 1;
			insert into c_backu_temp values (x_cod, lv_lineno,
				x_lin || '\');
			if x_force = 0
			then
				return x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into c_backu_temp values
					(x_cod, lv_lineno, x_str);
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into c_backu_temp values (
					x_cod, lv_lineno, x_lin || x_str);
				return '';
			end if;
		end if;
	end wri;

	function fild(x in varchar2) return varchar2 is
	begin
		temp_file := x;
		np := instr(x, ']');
		if np != 0 then
			temp_file := substr(x, 1, np);
		end if;
		np := instr(temp_file, '/');
		lnp := 0;
		while np != 0
		loop
			lnp := np;
			np := instr(temp_file, '/', lnp + 1);
		end loop;
		if lnp != 0 then
			temp_file := substr(x, 1, lnp - 1);
		end if;
		return temp_file;
	end fild;

	function filn(x in varchar2) return varchar2 is
	begin
		temp_file := x;
		np := instr(x, ']');
		if np != 0 then
			temp_file := substr(x, np + 1);
		end if;
		np := instr(temp_file, '/');
		while np != 0
		loop
			temp_file := substr(temp_file, np + 1);
			np := instr(temp_file, '/');
		end loop;
		return temp_file;
	end filn;

begin
	a_lin := '';
	lv_lineno := 0;
	prev_tablespace := '@';
	a_lin := wri('B', a_lin, 'set -e', 1);
	-- See if a "restore_<SID>" file is to be created
	if '&5' = '1' then
		a_lin := wri('R', a_lin, 'set -e', 1);
		if upper('&2') = 'HOT' then
			a_lin := wri('R', a_lin, '#', 1);
			a_lin := wri('R', a_lin,
				'# Note that this file must only be' ||
				' used as a TEMPLATE for the', 1);
			a_lin := wri('R', a_lin,
				'# recovery process, as restoring' ||
				' from HOT backups require', 1);
			a_lin := wri('R', a_lin, '# manual work.  Only the' ||
				' DBA_DATA_FILES are listed in this script!',
				1);
			a_lin := wri('R', a_lin,
				'# Please consult the ORACLE' ||
				' documentation for further information.', 1);
			a_lin := wri('R', a_lin, '#', 1);
		end if;
	end if;
	-- If performing a HOT backup:
	if upper('&2') = 'HOT' then
		--
		-- Reset from any previously failed Hot Backup (HOT backup)
		--
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin,
			'# Reset from any previously-failed Hot Backup', 1);
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin, 'sqlplus -s / <<XXX', 1);
		a_lin := wri('B', a_lin, 'whenever sqlerror exit sql.sqlcode',
			1);
		open end_cursor;
		loop
			fetch end_cursor into lv_tablespace;
			exit when end_cursor%NOTFOUND;
			-- End previously-failed backup of the tablespace
			a_lin := wri('B', a_lin, 'alter tablespace ' ||
				lv_tablespace || ' end backup;', 1);
		end loop;
		close end_cursor;
		a_lin := wri('B', a_lin, 'exit', 1);
		a_lin := wri('B', a_lin, 'XXX', 1);
		a_lin := wri('B', a_lin, 'if [ $? -ne 0 ]; then', 1);
		a_lin := wri('B', a_lin, '    exit 2', 1);
		a_lin := wri('B', a_lin, 'fi', 1);
	end if;
	if upper('&2') = 'HOTBEGIN' then
		--
		-- Begin the Hot Backup
		--
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin, '# Begin the Hot Backup', 1);
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin, 'sqlplus -s / <<XXX', 1);
		a_lin := wri('B', a_lin, 'whenever sqlerror exit sql.sqlcode',
			1);
		open begin_cursor;
		loop
			fetch begin_cursor into lv_tablespace;
			exit when begin_cursor%NOTFOUND;
			-- End the backup of the tablespace
			a_lin := wri('B', a_lin, 'alter tablespace ' ||
				lv_tablespace || ' begin backup;', 1);
		end loop;
		close begin_cursor;
		a_lin := wri('B', a_lin, 'exit', 1);
		a_lin := wri('B', a_lin, 'XXX', 1);
		a_lin := wri('B', a_lin, 'if [ $? -ne 0 ]; then', 1);
		a_lin := wri('B', a_lin, '    exit 2', 1);
		a_lin := wri('B', a_lin, 'fi', 1);
	end if;
	if upper('&2') = 'HOTEND' then
		--
		-- End the Hot Backup
		--
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin, '# End the Hot Backup', 1);
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin, 'sqlplus -s / <<XXX', 1);
		a_lin := wri('B', a_lin, 'whenever sqlerror exit sql.sqlcode',
			1);
		open end_cursor;
		loop
			fetch end_cursor into lv_tablespace;
			exit when end_cursor%NOTFOUND;
			-- End the backup of the tablespace
			a_lin := wri('B', a_lin, 'alter tablespace ' ||
				lv_tablespace || ' end backup;', 1);
		end loop;
		close end_cursor;
		a_lin := wri('B', a_lin, 'exit', 1);
		a_lin := wri('B', a_lin, 'XXX', 1);
		a_lin := wri('B', a_lin, 'if [ $? -ne 0 ]; then', 1);
		a_lin := wri('B', a_lin, '    exit 2', 1);
		a_lin := wri('B', a_lin, 'fi', 1);
	end if;
	--
	-- Backup and Restore the Redo Log Files (COLD backup only)
	--
	if upper('&2') = 'COLD' then
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin, '# Backup Redo Log Files', 1);
		a_lin := wri('B', a_lin, '#', 1);
		-- See if a "restore_<SID>" file is to be created
		if '&5' = '1' then
			a_lin := wri('R', a_lin, '#', 1);
			a_lin := wri('R', a_lin, '# Restore Redo Log Files', 1);
			a_lin := wri('R', a_lin, '#', 1);
		end if;
	end if;
	open log_cursor;
	loop
		fetch log_cursor into lv_name;
		exit when log_cursor%NOTFOUND;
		a_lin := wri('B', a_lin, 'copy_log ' || lv_name || ' &4 DATA '
			|| upper('&3') || ' ' || upper('&6'), 1);
		a_lin := wri('B', a_lin, 'if [ $? -ne 0 ]; then', 1);
		a_lin := wri('B', a_lin, '    exit 2', 1);
		a_lin := wri('B', a_lin, 'fi', 1);
		-- See if a "restore_<SID>" file is to be created
		if '&5' = '1' then
			-- Extract the redo log's directory
			my_directory := fild(lv_name);
			-- Extract the redo log's filename
			my_file_name := filn(lv_name);
			a_lin := wri('R', a_lin, 'rest_log &4/' ||
				my_file_name || ' ' || my_directory, 1);
			a_lin := wri('R', a_lin, 'if [ $? -ne 0 ]; then', 1);
			a_lin := wri('R', a_lin, '    exit 2', 1);
			a_lin := wri('R', a_lin, 'fi', 1);
		end if;
	end loop;
	close log_cursor;
	--
	-- Backup and Restore the Data Files (COLD, HOT, or HOTBEGIN backup)
	--
	if upper('&2') = 'COLD' or upper('&2') = 'HOT' or
		upper('&2') = 'HOTBEGIN'
	then
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin, '# Backup Database Files', 1);
		a_lin := wri('B', a_lin, '#', 1);
	end if;
	-- If performing a HOTBEGIN backup:
	if upper('&2') = 'HOTBEGIN' then
		-- Issue special call to copy_log to begin passing a
		-- list of the database files to be backed up
		a_lin := wri('B', a_lin, 'copy_log &1 x INIT ' || upper('&3')
			|| ' ' || upper('&6'), 1);
		a_lin := wri('B', a_lin, 'if [ $? -ne 0 ]; then', 1);
		a_lin := wri('B', a_lin, '    exit 2', 1);
		a_lin := wri('B', a_lin, 'fi', 1);
	end if;
	-- If not performing a HOTEND nor a HOTCTRL nor a HOTALLCTRL backup:
	if upper('&2') != 'HOTEND' and upper('&2') != 'HOTCTRL' and
		upper('&2') != 'HOTALLCTRL'
	then
		-- See if a "restore_<SID>" file is to be created
		if '&5' = '1' then
			a_lin := wri('R', a_lin, '#', 1);
			a_lin := wri('R', a_lin, '# Restore Database Files', 1);
			a_lin := wri('R', a_lin, '#', 1);
		end if;
	end if;
	open dbf_cursor;
	loop
		fetch dbf_cursor into lv_tablespace, lv_file;
		exit when dbf_cursor%NOTFOUND;
		if prev_tablespace != lv_tablespace and upper('&2') = 'HOT'
		then
			if prev_tablespace != '@' then
				-- End backup of the previous tablespace
				a_lin := wri('B', a_lin, 'sqlplus -s / <<XXX',
					1);
				a_lin := wri('B', a_lin,
					'whenever sqlerror exit sql.sqlcode',
					1);
				a_lin := wri('B', a_lin, 'alter tablespace ' ||
					prev_tablespace || ' end backup;', 1);
				a_lin := wri('B', a_lin, 'exit', 1);
				a_lin := wri('B', a_lin, 'XXX', 1);
				a_lin := wri('B', a_lin,
					'if [ $? -ne 0 ]; then', 1);
				a_lin := wri('B', a_lin, '    exit 2', 1);
				a_lin := wri('B', a_lin, 'fi', 1);
			end if;
			-- Start backup of the data files for this tablespace
			a_lin := wri('B', a_lin, 'sqlplus -s / <<XXX', 1);
			a_lin := wri('B', a_lin,
				'whenever sqlerror exit sql.sqlcode', 1);
			a_lin := wri('B', a_lin, 'alter tablespace ' ||
				lv_tablespace || ' begin backup;', 1);
			a_lin := wri('B', a_lin, 'exit', 1);
			a_lin := wri('B', a_lin, 'XXX', 1);
			a_lin := wri('B', a_lin, 'if [ $? -ne 0 ]; then', 1);
			a_lin := wri('B', a_lin, '    exit 2', 1);
			a_lin := wri('B', a_lin, 'fi', 1);
			prev_tablespace := lv_tablespace;
		end if;
		-- If performing a HOTBEGIN backup:
		if upper('&2') = 'HOTBEGIN' then
			-- Issue special call to copy_log to pass list of
			-- database files to be copied
			a_lin := wri('B', a_lin, 'copy_log &1 ' || lv_file ||
				' PASS_FILE ' || upper('&3') || ' ' ||
				upper('&6'), 1);
		else
			-- Call copy_log to copy the file
			a_lin := wri('B', a_lin, 'copy_log ' || lv_file ||
				' &4 DATA ' || upper('&3') || ' ' ||
				upper('&6'), 1);
		end if;
		a_lin := wri('B', a_lin, 'if [ $? -ne 0 ]; then', 1);
		a_lin := wri('B', a_lin, '    exit 2', 1);
		a_lin := wri('B', a_lin, 'fi', 1);
		-- See if a "restore_<SID>" file is to be created
		if '&5' = '1' then
			-- Extract the database file's directory
			my_directory := fild(lv_file);
			-- Extract the database file's filename
			my_file_name := filn(lv_file);
			a_lin := wri('R', a_lin, 'rest_log &4/' ||
				my_file_name || ' ' || my_directory, 1);
			a_lin := wri('R', a_lin, 'if [ $? -ne 0 ]; then', 1);
			a_lin := wri('R', a_lin, '    exit 2', 1);
			a_lin := wri('R', a_lin, 'fi', 1);
		end if;
	end loop;
	close dbf_cursor;
	-- If we found at least one file to backup:
	if prev_tablespace != '@' then
		-- If performing a HOT backup:
		if upper('&2') = 'HOT' then
			-- End backup of the previous tablespace
			a_lin := wri('B', a_lin, 'sqlplus -s / <<XXX', 1);
			a_lin := wri('B', a_lin,
				'whenever sqlerror exit sql.sqlcode', 1);
			a_lin := wri('B', a_lin, 'alter tablespace ' ||
				prev_tablespace || ' end backup;', 1);
			a_lin := wri('B', a_lin, 'exit', 1);
			a_lin := wri('B', a_lin, 'XXX', 1);
			a_lin := wri('B', a_lin, 'if [ $? -ne 0 ]; then', 1);
			a_lin := wri('B', a_lin, '    exit 2', 1);
			a_lin := wri('B', a_lin, 'fi', 1);
		end if;
	end if;
	--
	-- Backup the Control Files (COLD or HOTCTRL or HOTALLCTRL backups)
	--
	if upper('&2') = 'COLD' or upper('&2') = 'HOTCTRL' or
		upper('&2') = 'HOTALLCTRL'
	then
		a_lin := wri('B', a_lin, '#', 1);
		a_lin := wri('B', a_lin, '# Backup Control Files', 1);
		a_lin := wri('B', a_lin, '#', 1);
		-- See if a "restore_<SID>" file is to be created
		if '&5' = '1' then
			a_lin := wri('R', a_lin, '#', 1);
			a_lin := wri('R', a_lin, '# Restore Control Files', 1);
			a_lin := wri('R', a_lin, '#', 1);
		end if;
	end if;
	n := 0;
	open ctrl_cursor;
	loop
		fetch ctrl_cursor into lv_name;
		exit when ctrl_cursor%NOTFOUND;
		n := n + 1;
		-- If performing a HOTCTRL or a HOTALLCTRL backup:
		if upper('&2') = 'HOTCTRL' or upper('&2') = 'HOTALLCTRL' then
			-- Backup only the first control file
			if n = 1 then
				-- Extract only the control file's filename
				my_file_name := filn(lv_name);
				a_lin := wri('B', a_lin, 'if [ -r &4/' ||
					my_file_name || ' ]; then', 1);
				a_lin := wri('B', a_lin, '  rm &4/' ||
					my_file_name, 1);
				a_lin := wri('B', a_lin, 'fi', 1);
				a_lin := wri('B', a_lin, 'sqlplus -s / <<XXX',
					1);
				a_lin := wri('B', a_lin,
					'whenever sqlerror exit sql.sqlcode',
					1);
				a_lin := wri('B', a_lin,
					'alter system switch logfile;', 1);
				a_lin := wri('B', a_lin, '@\$DBA/archwait', 1);
				a_lin := wri('B', a_lin, 'alter database' ||
					' backup controlfile to ' || &q || '&4'
					|| '/' || my_file_name || &q || ';', 1);
				--
				-- Display redo log recovery information
				--
				a_lin := wri('B', a_lin, 'rem', 1);
				a_lin := wri('B', a_lin, 'rem Display redo' ||
					' log recovery information', 1);
				a_lin := wri('B', a_lin, 'rem', 1);
				a_lin := wri('B', a_lin, 'set pagesize 9999',
					1);
				a_lin := wri('B', a_lin, 'set heading on', 1);
				a_lin := wri('B', a_lin,
					'column group# format 9999' ||
					' heading GRP#', 1);
				a_lin := wri('B', a_lin,
					'column thread# format 9999' ||
					' heading THR#', 1);
				a_lin := wri('B', a_lin,
					'column sequence# format 99999' ||
					' heading SEQ#', 1);
				a_lin := wri('B', a_lin, 'column members' ||
					' format 999 heading MBR', 1);
				a_lin := wri('B', a_lin,
					'column status format a8', 1);
				a_lin := wri('B', a_lin,
					'column first_change# format' ||
					' 9999999999 heading CHG#', 1);
				a_lin := wri('B', a_lin,
					'select group#,thread#,sequence#,' ||
					'bytes,members,archived,status,', 1);
				a_lin := wri('B', a_lin,
					'first_change#,first_time' ||
					' from v\\$log;', 1);
				a_lin := wri('B', a_lin, 'exit', 1);
				a_lin := wri('B', a_lin, 'XXX', 1);
				a_lin := wri('B', a_lin,
					'if [ $? -ne 0 ]; then', 1);
				a_lin := wri('B', a_lin, '    exit 2', 1);
				a_lin := wri('B', a_lin, 'fi', 1);
				-- If performing a HOTCTRL backup:
				if upper('&2') = 'HOTCTRL' then
					-- Backup the control file
					a_lin := wri('B', a_lin, 'copy_log ' ||
						' &4/' || my_file_name ||
						' &7 DATA ' ||
						upper('&3') || ' ' ||
						upper('&6'), 1);
				else
					-- Issue special call to copy_log to
					-- pass backup control filespec to be
					-- copied
					a_lin := wri('B', a_lin, 'copy_log &1'
						|| ' &4/' || my_file_name ||
						' PASS_ARCH ' || upper('&3') ||
						' ' || upper('&6'), 1);
				end if;
				a_lin := wri('B', a_lin,
					'if [ $? -ne 0 ]; then', 1);
				a_lin := wri('B', a_lin, '    exit 2', 1);
				a_lin := wri('B', a_lin, 'fi', 1);
			end if;
		else
			-- If performing a COLD backup:
			if upper('&2') = 'COLD' then
				-- Backup and Restore all control files
				a_lin := wri('B', a_lin, 'copy_log ' ||
					lv_name || ' &4 DATA ' ||
					upper('&3') || ' ' || upper('&6'), 1);
				a_lin := wri('B', a_lin,
					'if [ $? -ne 0 ]; then', 1);
				a_lin := wri('B', a_lin, '    exit 2', 1);
				a_lin := wri('B', a_lin, 'fi', 1);
				-- Extract the control file's directory
				my_directory := fild(lv_name);
				-- Extract the control file's filename
				my_file_name := filn(lv_name);
				-- See if a "restore_<SID>" file is to be
				-- created
				if '&5' = '1' then
					a_lin := wri('R', a_lin, 'rest_log &4/'
						|| '/' || my_file_name ||
						' ' || my_directory, 1);
					a_lin := wri('R', a_lin,
						'if [ $? -ne 0 ]; then', 1);
					a_lin := wri('R', a_lin, '    exit 2',
						1);
					a_lin := wri('R', a_lin, 'fi', 1);
				end if;
			end if;
		end if;
	end loop;
	close ctrl_cursor;
end;
/

rem
rem Create backup script
rem
set linesize 132
set termout off
set concat +
spool bkup_&1.lst
set concat .
select '#!/bin/sh' from dual;
select '#' from dual;
select '# bkup_&1' from dual;
select '#' from dual;
select '# &2 backup script for database &1' from dual;
select '#' from dual;
select text from c_backu_temp
	where code = 'B'
	order by lineno;
spool off
rem
rem Create recovery script
rem
set concat +
spool restore_&1.lst
set concat .
select '#!/bin/sh' from dual;
select '#' from dual;
select '# restore_&1' from dual;
select '#' from dual;
select '# &2 restore script for database &1' from dual;
select '#' from dual;
select text from c_backu_temp
	where code = 'R' and '&5' = '1'
	order by lineno;
spool off
rem
rem Done
rem
drop table c_backu_temp;
set termout on
exit