rem $DBA/archwait.sql
rem
rem Waits until all of the online redo logs that need to be archived have been
rem archived.
rem
rem <<<<<<<<<<<<<<<<<<< MODIFICATION HISTORY >>>>>>>>>>>>>>>>>>>
rem 03/03/97 Brian Lomasky	Increase dbms_output buffer size.
rem 02/14/97 Brian Lomasky	Original
rem
set pagesize 0
set feedback off
set verify off
set head off
set term on
set serveroutput on size 1000000
declare
	seq1	number;
	seq2	number;
	n	number;
	no_seq	exception;
	forever	exception;
	cursor log1 is
		select min(sequence#) from v$log where status = 'CURRENT';
	cursor log2 is
		select min(sequence#) from v$log where archived = 'NO';
begin
	n := 0;
	loop
		dbms_output.put_line('archwait executing...');
		-- Get sequence number of the current online redo log
		open log1;
		open log2;
		fetch log1 into seq1;
		if log1%notfound then
			raise no_seq;
		end if;
		dbms_output.put_line(
			'Current online redo log sequence number is:      '||
			seq1);
		-- Get sequence number of the next redo log to be archived
		fetch log2 into seq2;
		if log2%notfound then
			raise no_seq;
		end if;
		dbms_output.put_line(
			'Next online redo log sequence to be archived is: '||
			seq2);
		close log1;
		close log2;
		-- Done if sequence numbers match
		if seq1 = seq2 then
			dbms_output.put_line('Sequence numbers match, so'||
				' control file can now be backed up...');
			exit;
		end if;
		n := n + 1;
		if n = 60 then
			raise forever;
		end if;
		dbms_output.put_line(
			'Waiting for 1 minute before retrying fetch...');
		dbms_lock.sleep(60.0);
	end loop;
	commit;
exception
	when no_seq then
		rollback;
		raise_application_error(-20000, '>>>> Unexpected error' ||
			' accessing V$LOG - archwait aborting...');
	when forever then
		rollback;
		raise_application_error(-20000, '>>>> Waited too long' ||
			' for redo log to be archived - archwait aborting...');
end;
/