#!/bin/sh
#***************************************************************************
#
#   Script:	$DBA/crdb
#
#	Generate a crdb<SID>.sql 'CREATE DATABASE' script for any running
#	instance, including CREATE statements for all tablespaces and rollback
#	segments.  The objective is to simplify a DBA's job for creating or
#	migrating databases based on current configurations.  In conjunction
#	with a full export, this script can be used to backup and recreate the
#	full database fairly automatically.
#
#	Note:	The outputs of this script can be used to restructure your
#		databases.  Please use these outputs only after careful review
#		since they have not been tested under all possible conditions.
#
#   Usage:	crdb [oracle_sid] <userid/password>
#		where:
#			oracle_sid is optional.  If specified, it is the SID for
#			  which this script is being run (Default of the current
#			  ORACLE_SID).
#			<userid/password> is optional.  If specified, it is the
#			  username and password used to access the DBA_ and V$_
#			  tables in order to create the script file.  (Default
#			  of "/").
#  Restrictions:
#		1) ORACLE_BASE must be set.  (The init.ora file for the STARTUP
#		   command is assumed to be in
#		   $ORACLE_BASE/admin/$ORACLE_SID/init$ORACLE_SID_0.ora).  A
#		   warning will be displayed if the PFILE clause on the STARTUP
#		   command needs to be manually changed.
#		2) oraenv must be found in the PATH.
#
#   Example:     crdb PROD 'system/manager'
#
#   Script :     gendb.sql  (v1.1)
#                (c) 1995-1997 Sunrise Systems, Inc.  All Rights Reserved
#
#   Written By : Noorali Sonawalla
#                Sunrise Systems, Inc.   (1/28/95)
#                Tel : (732) 603-2200
#                Fax : (732) 603-2208
#                email :  noorali@sunrisesys.com
#
#   Modified by: Brian Lomasky - 11/24/97 - Fix user_dump_dest beginning with ?.
#   Modified by: Brian Lomasky - 10/01/97 - Use SYS.TS$ instead of
#					    DBA_TABLESPACES so I can access
#					    TEMPORARY and READ ONLY status.
#   Modified by: Brian Lomasky - 08/04/97 - Use ORACLE_SID instead of DB_NAME
#					    so it matches for use with Oracle
#					    Parallel Server.  Create all redo
#					    logs threads for Parallel Server.
#					    Create spool log file.  Use the
#					    init<SID>_0.ora file located in
#					    $ORACLE_BASE/admin/$ORACLE_SID for
#					    the initial database startup.
#   Modified by: Brian Lomasky - 04/18/97 - Combine gendbcon and gendb.sql into
#					    this file.  Remove control file.
#					    Rename to crdb.  Call oraenv to
#					    set the proper ORACLE_HOME.  Use
#					    svrmgrl instead of sqldba, if reqd.
#					    Allow optional ORACLE_SID.  Use nawk
#					    instead of awk, if available.  Use
#					    PL/SQL scripts to intelligently
#					    calculate size divisor and better
#					    placement of output data.
#   Modified by: Brian Lomasky - 02/20/97 - Pass gendbcon parameters as $1 $2
#					    via "set" statement.
#   Modified By  Brian Lomasky - 11/22/96 - Skip rollback segment alter offline
#					    if it is already offline.
#   Modified By  Brian Lomasky - 07/31/96 - Fix comments.  Calculate default
#					    temporary and default tablespaces
#					    for SYSTEM and SYS accounts.
#   Modified By  Brian Lomasky - 06/13/96 - Also run dbmspool.sql, prvtpool.sql,
#					    and utlmontr.sql.
#   Modified By  Brian Lomasky - 04/04/96 - Renamed output file from
#					    cr<SID>.sql to crdb<SID>.sql,
#					    Reformat lines for ease of analysis.
#   Modified by: Brian Lomasky - 04/03/96 - Renamed from gendb.sh,  Renamed
#					    gencf.sh to gendbcon, Renamed
#					    cr<SID>.sql to crdb<SID>.sql
#
#***************************************************************************
#
# Check number of input arguments
#
if [ $# -gt 2 ]
then
	echo "Invalid # arguments ... Usage $0 [ORACLE_SID] [userid/password]"
	echo "please try again ..."
	exit 1
fi
#
#  set ORACLE_SID, ORACLE_HOME, and login password (must have DBA privileges)
#
if [ $# -eq 2 ]
then
	USERPW=$2; export USERPW
else
	USERPW=/; export USERPW
fi
if [ $# -ge 1 ]
then
	ORACLE_SID=$1; export ORACLE_SID
fi
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=
#
#	Verify ORACLE_BASE and the init_0.ora file
#
if [ "$ORACLE_BASE" = "" ]
then
	echo "Error:  The ORACLE_BASE environment variable is not set."
	echo "please try again ..."
	exit 1
fi
if [ ! -r ${ORACLE_BASE}/admin/${ORACLE_SID}/pfile/init${ORACLE_SID}_0.ora ]
then
	echo " "
	echo "Warning:  The" \
	    "${ORACLE_BASE}/admin/${ORACLE_SID}/pfile/init${ORACLE_SID}_0.ora"
	echo "          file does not exist."
	echo "          You must manually change the PFILE parameter to the"
	echo "          STARTUP command in the created script to refer to your"
	echo "          init${ORACLE_SID}_0.ora file.\007"
	echo " "
	echo "          Continuing..."
	echo " "
fi
bkup_ctrl=crdb$ORACLE_SID.tmp; export bkup_ctrl
temp_out=crdb2$ORACLE_SID.tmp; export temp_out
prev_dump_dest=crdb3$ORACLE_SID.tmp; export prev_dump_dest
curr_dump_dest=crdb4$ORACLE_SID.tmp; export curr_dump_dest
ctrl_file_parms=crdb5$ORACLE_SID.tmp; export ctrl_file_parms
before_ctrl_params=crdb6$ORACLE_SID.tmp; export before_ctrl_params
after_ctrl_params=crdb7$ORACLE_SID.tmp; export after_ctrl_params
backup_ctrl_file=crcf$ORACLE_SID.sql; export backup_ctrl_file
if [ -r $ORACLE_HOME/bin/sqldba ]
then
	prg='sqldba lmode=y'
else
	prg=svrmgrl
fi
#
# See if nawk should be used instead of awk
#
(nawk '{ print ; exit }' /etc/passwd) > /dev/null 2>&1
if [ ${?} -eq 0 ]
then
	cmd=nawk
else
	cmd=awk
fi
#
# First generate the DDL for control file
#
# get user_dump_destination using sqlplus into a file
#
#echo ".... creating a backup of control file ... please wait ..."
sqlplus -s $USERPW >/dev/null<<!EOD
set echo on
select * from dual;
set heading off
set pagesize 0
set verify off
set feedback off
set termout off
set echo off
set embedded off
set recsep off
set time off
set timing off
col value format a68
spool $temp_out
select 'USRDMPDEST', replace(value, '?', '$ORACLE_HOME') value
from sys.v_\$parameter where name = 'user_dump_dest';
spool off
exit
!EOD
if [ ! -s $temp_out ]
then
	cat $temp_out
	if [ -r $temp_out ]
	then
		rm -f $temp_out
	fi
	echo "oops ...    SQL*Plus error ..."
	echo "            invalid userid/password ..."
	echo "            or insufficient Oracle access ..."
	echo "            or Oracle is not available ..."
	echo "            or check for some other SQL*Plus errors ..."
	echo "aborting..."
	exit 1
fi
#set `egrep -v ">" $temp_out`
set `grep "USRDMPDEST " $temp_out`
DUMPDIR=$2; export DUMPDIR
rm -f $temp_out
#echo ".... oracle dump directory for $ORACLE_SID = $DUMPDIR  ..."
#
# list all files in this user dump destination directory prior to the backup
# of the control file
ls -1t $DUMPDIR | head -20 >$prev_dump_dest
if [ $? != 0 ]
then
	echo "oops ... no access to user dump dest directory $DUMPDIR ..."
	echo "aborting..."
	exit 1
fi
if [ ! -s $prev_dump_dest ]       # file empty or does not exist
then
	echo "empty file" >$prev_dump_dest
fi
#
# use sqldba/svrmgrl to backup controlfile
#
$prg 1>/dev/null <<!EOD > $bkup_ctrl
connect internal
alter database backup controlfile to trace;
exit
!EOD
grep "ORA-" $bkup_ctrl >$temp_out
if [ -s $temp_out ]       # file empty or does not exist
then
	cat $temp_out
	if [ -r $temp_out ]
	then
		rm -f $temp_out
	fi
	echo "oops ... error in executing $prg ..."
	echo "aborting..."
	echo "======================  $prg output is:"
	cat $bkup_ctrl
	exit 1
fi
if [ -r $temp_out ]
then
	rm -f $temp_out
fi
rm -f $bkup_ctrl
#
# check the list of all files in the dump destination, excluding files
# which existed prior to running sqldba/svrmgrl.
#
ls -1t $DUMPDIR | head -20 | egrep -v -f $prev_dump_dest >$curr_dump_dest
rm -f $prev_dump_dest
#
# get the file name of the newly created file;  note that Oracle does not
# allow us to specify a file name in the 'backup controlfile' command.
#
cat $curr_dump_dest | while read line
do
	x=`grep "CREATE CONTROLFILE REUSE" $DUMPDIR/$line`
	if [ "$x" != "" ]
	then
		#
		# copy the file
		#
		cp $DUMPDIR/$line $backup_ctrl_file
		rm $DUMPDIR/$line
	else
		echo "oops ... no access to user dump dest file" \
			"$DUMPDIR/$line ..."
		echo "aborting..."
		exit 1
	fi
done
rm -f $curr_dump_dest
#
# extract parameter values from it
#
cat $backup_ctrl_file | $cmd '
/MAXLOGFILES /		{ print "    " substr($1 "            ",1,20) $2 }
/MAXLOGMEMBERS /	{ print "    " substr($1 "            ",1,20) $2 }
/MAXDATAFILES /		{ print "    " substr($1 "            ",1,20) $2 }
/MAXINSTANCES /		{ print "    " substr($1 "            ",1,20) $2 }
/MAXLOGHISTORY /	{ print "    " substr($1 "            ",1,20) $2 }
' >$ctrl_file_parms
rm $backup_ctrl_file
#
# Next, generate the DDL for 'Create Database'
#
sqlplus -s $USERPW >/dev/null<<!EOD
clear columns
clear breaks
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
create table crdb_temp (code VARCHAR2(1), lineno NUMBER, text VARCHAR2(80));
set linesize 80
set termout off
set recsep off
set time off
set timing off
def q=chr(39)
column maxmem1 new_value maxmem_len
select max(length(member) + 2) maxmem1 from sys.v_\$logfile;
declare
	cursor mode_cursor is select
		log_mode
		from sys.v_\$database;
	cursor datafile_cursor (my_tablespace in varchar2) is select
		file_name,
		bytes
		from sys.dba_data_files
		where tablespace_name = my_tablespace
		order by file_id;
	cursor log_cursor is select
		group#,
		members,
		bytes
		from sys.v_\$log
		where thread# = 1
		order by 1;
	cursor thread_cursor is select
		thread#,
		group#,
		members,
		bytes
		from sys.v_\$log
		where thread# > 1
		order by 1, 2;
	cursor logfile_cursor (my_group in number) is select
		member
		from sys.v_\$logfile
		where group# = my_group;
	cursor tablespace_cursor is select
		ts.name,
		ts.blocksize * ts.dflinit,
		ts.blocksize * ts.dflincr,
		ts.dflminext,
		ts.dflmaxext,
		ts.dflextpct,
		decode(mod(ts.online$, 65536),1,'ONLINE',2,'OFFLINE',
			4,'READ ONLY','UNDEFINED'),
		decode(floor(ts.online$/65536),0,'PERMANENT',1,'TEMPORARY')
		from sys.ts$ ts
		where ts.name <> 'SYSTEM' and mod(ts.online$, 65536) != 3
		order by 1;
	cursor rollback_cursor is select
		owner,
		segment_name,
		tablespace_name,
		initial_extent,
		next_extent,
		min_extents,
		max_extents,
		pct_increase
		from sys.dba_rollback_segs
		where segment_name not in ('SYSTEM', 'R000')
		order by segment_name;
	cursor optimal_cursor (my_segment_name in varchar2) is select
		decode(c.optsize, NULL, a.initial_extent * a.min_extents,
			c.optsize)
		from	sys.dba_rollback_segs a,
			sys.v_\$rollname b,
			sys.v_\$rollstat c
		where my_segment_name not in ('SYSTEM', 'R000')
		and a.segment_name = my_segment_name
		and a.segment_name = b.name
		and b.usn = c.usn;

	lv_log_mode		sys.v_\$database.log_mode%TYPE;
	lv_file_name		sys.dba_data_files.file_name%TYPE;
	lv_bytes		number;
	prev_thread#		sys.v_\$log.thread#%TYPE;
	lv_thread#		sys.v_\$log.thread#%TYPE;
	prev_group#		sys.v_\$log.group#%TYPE;
	lv_group#		sys.v_\$log.group#%TYPE;
	lv_members		sys.v_\$log.members%TYPE;
	lv_member		sys.v_\$logfile.member%TYPE;
	lv_tablespace_name	sys.dba_tablespaces.tablespace_name%TYPE;
	lv_initial_extent	sys.dba_tablespaces.initial_extent%TYPE;
	lv_next_extent		sys.dba_tablespaces.next_extent%TYPE;
	lv_min_extents		sys.dba_tablespaces.min_extents%TYPE;
	lv_max_extents		sys.dba_tablespaces.max_extents%TYPE;
	lv_pct_increase		sys.dba_tablespaces.pct_increase%TYPE;
	lv_tablesp_status	varchar2(30);
	lv_tablesp_contents	varchar2(30);
	lv_owner		sys.dba_rollback_segs.owner%TYPE;
	lv_segment_name		sys.dba_rollback_segs.segment_name%TYPE;
	lv_optimal		number;
	initial_extent_size	varchar2(16);
	next_extent_size	varchar2(16);
	owner_name		varchar2(6);
	optimal_size		varchar2(10);
	lv_lineno		number := 0;
	bytes_size		varchar2(16);
	a_lin			varchar2(80);
	n			number;
	r			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) > 80 then
			lv_lineno := lv_lineno + 1;
			insert into crdb_temp values (x_cod, lv_lineno,
				x_lin);
			if x_force = 0 then
				return '    ' || x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into crdb_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 crdb_temp values (
					x_cod, lv_lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;
begin
	a_lin := '';
	open mode_cursor;
	fetch mode_cursor into lv_log_mode;
	if mode_cursor%found then
		a_lin := wri('A', a_lin, '    ' || lv_log_mode, 1);
	end if;
	close mode_cursor;
	a_lin := wri('0', a_lin, '    DATAFILE ', 0);
	r := 0;
	open datafile_cursor ('SYSTEM');
	loop
		fetch datafile_cursor into
			lv_file_name,
			lv_bytes;
		exit when datafile_cursor%notfound;
		r := r + 1;
		if r != 1 then
			a_lin := wri('0', a_lin, ',', 1);
		end if;
		/* Calculate sizes in Mbytes or Kbytes, if possible */
		if mod(lv_bytes, 1048576) = 0 then
			bytes_size := to_char(lv_bytes / 1048576) || 'M';
		elsif mod(lv_bytes, 1024) = 0 then
			bytes_size := to_char(lv_bytes / 1024) || 'K';
		else
			bytes_size := to_char(lv_bytes);
		end if;
		a_lin := wri('0', a_lin, &q || lv_file_name || &q || ' SIZE ' ||
			bytes_size, 0);
	end loop;
	close datafile_cursor;
	a_lin := wri('0', a_lin, '', 1);
	prev_group# := 99999;
	open log_cursor;
	loop
		fetch log_cursor into
			lv_group#,
			lv_members,
			lv_bytes;
		exit when log_cursor%notfound;
		/* Calculate sizes in Mbytes or Kbytes, if possible */
		if mod(lv_bytes, 1048576) = 0 then
			bytes_size := to_char(lv_bytes / 1048576) || 'M';
		elsif mod(lv_bytes, 1024) = 0 then
			bytes_size := to_char(lv_bytes / 1024) || 'K';
		else
			bytes_size := to_char(lv_bytes);
		end if;
		if prev_group# != 99999 then
			a_lin := wri('1', a_lin, ',', 1);
		end if;
		a_lin := wri('1', a_lin, '    GROUP' ||
			to_char(lv_group#, 'B99') || ' (', 0);
		prev_group# := lv_group#;
		r := 0;
		open logfile_cursor (lv_group#);
		loop
			fetch logfile_cursor into lv_member;
			exit when logfile_cursor%notfound;
			r := r + 1;
			if r != 1 then
				a_lin := wri('1', a_lin, '', 1);
				a_lin := wri('1', a_lin, '    ', 0);
			end if;
			if r = lv_members then
				a_lin := wri('1', a_lin, &q ||
					rpad(lv_member || &q, &maxmem_len, ' '),
					0);
			else
				a_lin := wri('1', a_lin, &q ||
					rpad(lv_member || &q, &maxmem_len, ' ')
					|| ',', 0);
			end if;
		end loop;
		close logfile_cursor;
		a_lin := wri('1', a_lin, ') SIZE ' || bytes_size, 0);
	end loop;
	close log_cursor;
	a_lin := wri('1', a_lin, ';', 1);
	prev_thread# := 99999;
	open thread_cursor;
	loop
		fetch thread_cursor into
			lv_thread#,
			lv_group#,
			lv_members,
			lv_bytes;
		exit when thread_cursor%notfound;
		if prev_thread# <> lv_thread# then
			prev_thread# := lv_thread#;
			a_lin := wri('1', a_lin, ' ', 1);
			a_lin := wri('1', a_lin,
				'ALTER DATABASE ADD LOGFILE THREAD ' ||
				to_char(lv_thread#), 1);
			prev_group# := 99999;
		end if;
		/* Calculate sizes in Mbytes or Kbytes, if possible */
		if mod(lv_bytes, 1048576) = 0 then
			bytes_size := to_char(lv_bytes / 1048576) || 'M';
		elsif mod(lv_bytes, 1024) = 0 then
			bytes_size := to_char(lv_bytes / 1024) || 'K';
		else
			bytes_size := to_char(lv_bytes);
		end if;
		if prev_group# != 99999 then
			a_lin := wri('1', a_lin, ',', 1);
		end if;
		a_lin := wri('1', a_lin, '    GROUP' ||
			to_char(lv_group#, 'B99') || ' (', 0);
		prev_group# := lv_group#;
		r := 0;
		open logfile_cursor (lv_group#);
		loop
			fetch logfile_cursor into lv_member;
			exit when logfile_cursor%notfound;
			r := r + 1;
			if r != 1 then
				a_lin := wri('1', a_lin, '', 1);
				a_lin := wri('1', a_lin, '    ', 0);
			end if;
			if r = lv_members then
				a_lin := wri('1', a_lin, &q ||
					rpad(lv_member || &q, &maxmem_len, ' '),
					0);
			else
				a_lin := wri('1', a_lin, &q ||
					rpad(lv_member || &q, &maxmem_len, ' ')
					|| ',', 0);
			end if;
		end loop;
		close logfile_cursor;
		a_lin := wri('1', a_lin, ') SIZE ' || bytes_size, 0);
	end loop;
	close thread_cursor;
	a_lin := wri('1', a_lin, ';', 1);
	if prev_thread# <> 99999 then
		a_lin := wri('1', a_lin, 'rem', 1);
	end if;
	open tablespace_cursor;
	loop
		fetch tablespace_cursor into
			lv_tablespace_name,
			lv_initial_extent,
			lv_next_extent,
			lv_min_extents,
			lv_max_extents,
			lv_pct_increase,
			lv_tablesp_status,
			lv_tablesp_contents;
		exit when tablespace_cursor%notfound;
		a_lin := wri('2', a_lin, 'rem', 1);
		a_lin := wri('2', a_lin,
			'rem ----------------------------------------', 1);
		a_lin := wri('2', a_lin, 'rem', 1);
		a_lin := wri('2', a_lin, 'CREATE TABLESPACE ' ||
			lv_tablespace_name || ' DATAFILE', 1);
		r := 0;
		open datafile_cursor (lv_tablespace_name);
		loop
			fetch datafile_cursor into
				lv_file_name,
				lv_bytes;
			exit when datafile_cursor%notfound;
			r := r + 1;
			if r != 1 then
				a_lin := wri('2', a_lin, ',', 1);
			end if;
			/* Calculate sizes in Mbytes or Kbytes, if possible */
			if mod(lv_bytes, 1048576) = 0 then
				bytes_size := to_char(lv_bytes / 1048576) ||
					'M';
			elsif mod(lv_bytes, 1024) = 0 then
				bytes_size := to_char(lv_bytes / 1024) || 'K';
			else
				bytes_size := to_char(lv_bytes);
			end if;
			a_lin := wri('2', a_lin, '    ' || &q || lv_file_name ||
				&q || ' SIZE ' || bytes_size, 0);
		end loop;
		close datafile_cursor;
		a_lin := wri('2', a_lin, ' ', 0);
		/* Calculate sizes in Mbytes or Kbytes, if possible */
		if mod(lv_initial_extent, 1048576) = 0 then
			initial_extent_size := to_char(lv_initial_extent /
				1048576) || 'M';
		elsif mod(lv_initial_extent, 1024) = 0 then
			initial_extent_size := to_char(lv_initial_extent /
				1024) || 'K';
		else
			initial_extent_size := to_char(lv_initial_extent);
		end if;
		if mod(lv_next_extent, 1048576) = 0 then
			next_extent_size := to_char(lv_next_extent / 1048576) ||
				'M';
		elsif mod(lv_next_extent, 1024) = 0 then
			next_extent_size := to_char(lv_next_extent / 1024) ||
				'K';
		else
			next_extent_size := to_char(lv_next_extent);
		end if;
		a_lin := wri('2', a_lin, 'default storage', 1);
		a_lin := wri('2', a_lin, '    (initial ' || initial_extent_size,
			0);
		a_lin := wri('2', a_lin, ' next ' || next_extent_size, 0);
		a_lin := wri('2', a_lin, ' pctincrease ' || lv_pct_increase, 0);
		a_lin := wri('2', a_lin, ' minextents ' || lv_min_extents, 0);
		a_lin := wri('2', a_lin, ' maxextents ' || lv_max_extents, 0);
		a_lin := wri('2', a_lin, ')', 0);
		if lv_tablesp_contents = 'TEMPORARY' then
			a_lin := wri('2', a_lin, ' TEMPORARY', 0);
		end if;
		a_lin := wri('2', a_lin, ';', 1);
		if lv_tablesp_status = 'READ ONLY' then
			a_lin := wri('2', a_lin, 'ALTER TABLESPACE ' ||
				lv_tablespace_name || ' READ ONLY;', 1);
		end if;
	end loop;
	close tablespace_cursor;
	a_lin := wri('2', a_lin, 'rem', 1);
	a_lin := wri('2', a_lin,
		'rem ----------------------------------------', 1);
	a_lin := wri('2', a_lin, 'rem', 1);
	a_lin := wri('2', a_lin, 'rem  Create additional rollback segments' ||
		' in the rollback tablespace', 1);
	a_lin := wri('2', a_lin, 'rem', 1);
	a_lin := wri('2', a_lin,
		'rem ----------------------------------------', 1);
	a_lin := wri('2', a_lin, 'rem', 1);
	open rollback_cursor;
	loop
		fetch rollback_cursor into
			lv_owner,
			lv_segment_name,
			lv_tablespace_name,
			lv_initial_extent,
			lv_next_extent,
			lv_min_extents,
			lv_max_extents,
			lv_pct_increase;
		exit when rollback_cursor%notfound;
		if lv_owner = 'PUBLIC' then
			owner_name := ' PUBLIC ';
		else
			owner_name := ' ';
		end if;
		/* Calculate sizes in Mbytes or Kbytes, if possible */
		if mod(lv_initial_extent, 1048576) = 0 then
			initial_extent_size := to_char(lv_initial_extent /
				1048576) || 'M';
		elsif mod(lv_initial_extent, 1024) = 0 then
			initial_extent_size := to_char(lv_initial_extent /
				1024) || 'K';
		else
			initial_extent_size := to_char(lv_initial_extent);
		end if;
		if mod(lv_next_extent, 1048576) = 0 then
			next_extent_size := to_char(lv_next_extent / 1048576) ||
				'M';
		elsif mod(lv_next_extent, 1024) = 0 then
			next_extent_size := to_char(lv_next_extent / 1024) ||
				'K';
		else
			next_extent_size := to_char(lv_next_extent);
		end if;
		a_lin := wri('2', a_lin, 'CREATE' || owner_name ||
			'ROLLBACK SEGMENT ' || lv_segment_name, 0);
		a_lin := wri('2', a_lin, ' TABLESPACE ' || lv_tablespace_name
			|| ' STORAGE', 1);
		a_lin := wri('2', a_lin, '    (initial ' ||
			initial_extent_size, 0);
		a_lin := wri('2', a_lin, ' next ' || next_extent_size, 0);
		a_lin := wri('2', a_lin, ' minextents ' || lv_min_extents, 0);
		a_lin := wri('2', a_lin, ' maxextents ' || lv_max_extents, 0);
		open optimal_cursor (lv_segment_name);
		fetch optimal_cursor into
			lv_optimal;
		if optimal_cursor%found then
			if mod(lv_optimal, 1048576) = 0 then
				optimal_size := to_char(lv_optimal / 1048576) ||
					'M';
			elsif mod(lv_optimal, 1024) = 0 then
				optimal_size := to_char(lv_optimal / 1024) ||
					'K';
			else
				optimal_size := to_char(lv_optimal);
			end if;
			if lv_optimal != 0 then
				a_lin := wri('2', a_lin, ' optimal ' ||
					optimal_size, 0);
			end if;
		end if;
		close optimal_cursor;
		a_lin := wri('2', a_lin, ');', 1);
	end loop;
	close rollback_cursor;
	commit;
end;
/
set termout off
spool crdb$ORACLE_SID.sql
col parameter format a17
col value format a30
col val format a40
col newval format a10 newline
select 'rem  ************************************************' from dual;
select 'rem  *  Script     : crdb$ORACLE_SID.sql to Create Database' from dual;
select 'rem  *  Date       :',
       to_char(sysdate,'MM/DD/YY   HH24:MM:SS') "value" from dual;
select 'rem  *  Notes :                                      ' from dual;
select 'rem  *  -  This script includes CREATE DATABASE,     ' from dual;
select 'rem  *     CREATE other TABLESPACES, CREATE ROLLBACK ' from dual;
select 'rem  *     SEGMENT, statements.                      ' from dual;
select 'rem  *     It also runs catalog.sql, catproc.sql,' from dual;
select 'rem  *     dbmspool.sql, prvtpool.sql, and utlmontr.sql under SYS'
	from dual;
select 'rem  *     and catdbsyn.sql and pupbld.sql under SYSTEM  ' from dual;
select 'rem  *' from dual;
select 'rem  *  -  You should (if needed) :' from dual;
select 'rem  *     point to the correct init.ora file,' from dual;
select 'rem  *     and ensure that the rollback segments are' from dual;
select 'rem  *     enabled in the init.ora file after the' from dual;
select 'rem  *     database is created.' from dual;
select 'rem  *' from dual;
select 'rem  ************************************************' from dual;
select 'rem' from dual;
select 'rem  Database name        :', value from sys.v_\$parameter
	where name = 'db_name';
select 'rem  Database created     :', created  from sys.v_\$database;
select 'rem  Database log_mode    :', log_mode from sys.v_\$database;
select 'rem  Database blocksize   :', value || ' bytes' "VAL"
       from sys.v_\$parameter where name = 'db_block_size';
select 'rem  Database buffers     :', value || ' blocks' "VAL"
       from sys.v_\$parameter where name = 'db_block_buffers';
select 'rem  Database log_buffers :', value || ' blocks' "VAL"
       from sys.v_\$parameter where name = 'log_buffer';
col value format a50
select 'rem  Database ifile       :', 'rem       ' "NEWVAL", value
       from sys.v_\$parameter where name = 'ifile';
select 'rem' from dual;
select 'rem  Database Options     :' from dual;
rem
rem  v_$option is available only in v7.1
rem
rem select 'rem ', parameter, '   :', value from sys.v_\$option;
select 'rem' from dual;
select 'rem  Note:  Use ALTER SYSTEM BACKUP CONTROLFILE TO TRACE;' from dual;
select 'rem         to generate a script to create controlfile' from dual;
select 'rem         and compare it with the output of this script.' from dual;
select 'rem         Add MAXLOGFILES, MAXDATAFILES, etc. if reqd.' from dual;
select 'rem' from dual;
col GROUP format a14
col MEMBER format a64
break on GROUP
select 'spool crdb$ORACLE_SID.lst' from dual;
select 'connect internal' from dual;
select 'startup nomount pfile=${ORACLE_BASE}/admin/${ORACLE_SID}/pfile' ||
	'/init${ORACLE_SID}_0.ora'
	from dual;
select '/* please verify/change the following parameters as needed */'
	from dual;
select 'CREATE DATABASE "' || value || '"' from sys.v_\$parameter
	where name = 'db_name';
select text from crdb_temp where code = 'A' order by lineno;
select '   /*option start:use control file*/' from dual;
select '   MAXLOGFILES    ' || max(group#)*max(members)*4 "MEMBER"
	from sys.v_\$log;
select '   MAXLOGMEMBERS  ' || max(members) * 2 "MEMBER" from sys.v_\$log;
select '   /* MAXDATAFILES  255 */' from dual;
select '   /* MAXINSTANCES    1 */' from dual;
select '   /* MAXLOGHISTORY 100 */' from dual;
select '   /*option end  :use control file*/' from dual;
select text from crdb_temp where code = '0' order by lineno;
select '    LOGFILE' from dual;
select text from crdb_temp where code = '1' order by lineno;
select 'rem ----------------------------------------' from dual;
select 'rem' from dual;
select 'rem  Need a basic rollback segment before proceeding' from dual;
select 'rem' from dual;
select 'CREATE ROLLBACK SEGMENT r000 TABLESPACE SYSTEM '  from dual;
select '    storage (initial 500K next 500K minextents 2);' from dual;
select 'ALTER ROLLBACK SEGMENT r000 ONLINE;' from dual;
select 'commit;' from dual;
select 'rem ----------------------------------------' from dual;
select 'rem' from dual;
select 'rem Create DBA views' from dual;
select 'rem' from dual;
select '@\$ORACLE_HOME/rdbms/admin/catalog.sql' from dual;
select 'commit;' from dual;
select 'rem ----------------------------------------' from dual;
select 'rem' from dual;
select 'rem  Additional Tablespaces' from dual;
select text from crdb_temp where code = '2' order by lineno;
select 'ALTER ROLLBACK SEGMENT ' || segment_name || ' ' || status || ';'
	from sys.dba_rollback_segs
	where segment_name not in ('SYSTEM', 'R000') and status = 'ONLINE'
	order by 1;
select 'rem' from dual;
select 'rem  Take the initial rollback segment (r000) offline' from dual;
select 'rem' from dual;
select 'ALTER ROLLBACK SEGMENT r000 OFFLINE;' from dual;
select 'rem' from dual;
select 'rem ----------------------------------------' from dual;
select 'rem' from dual;
select 'ALTER USER SYS TEMPORARY TABLESPACE ' || temporary_tablespace || ';'
	from sys.dba_users where username = 'SYS';
select 'ALTER USER SYSTEM TEMPORARY TABLESPACE ' || temporary_tablespace ||
	' DEFAULT TABLESPACE ' || default_tablespace || ';'
	from sys.dba_users where username = 'SYSTEM';
select 'rem' from dual;
select 'rem ----------------------------------------' from dual;
select 'rem' from dual;
select 'rem  Run other @\$ORACLE_HOME/rdbms/admin required scripts' from dual;
select 'rem' from dual;
select 'commit;' from dual;
select 'rem' from dual;
select '@\$ORACLE_HOME/rdbms/admin/catproc.sql' from dual;
select 'rem @\$ORACLE_HOME/rdbms/admin/catparr.sql' from dual;
select '@\$ORACLE_HOME/rdbms/admin/dbmspool.sql' from dual;
select '@\$ORACLE_HOME/rdbms/admin/prvtpool.sql' from dual;
select '@\$ORACLE_HOME/rdbms/admin/utlmontr.sql' from dual;
select 'rem' from dual;
select 'commit;' from dual;
select 'rem' from dual;
select 'connect system/manager' from dual;
select '@\$ORACLE_HOME/sqlplus/admin/pupbld.sql' from dual;
select '@\$ORACLE_HOME/rdbms/admin/catdbsyn.sql' from dual;
select 'commit;' from dual;
select 'spool off' from dual;
select 'exit' from dual;
spool off
drop table crdb_temp;
exit
!EOD
#
# Now, merge the 'Create Database' DDL with some of the parameters found
#      only in the DDL for control file.
#      This step depends on /option start:/ and /option end  :/ markers
#      placed within the 'Create Database' file.
#
cat crdb$ORACLE_SID.sql | $cmd '
	/option start:/ { print "    /* using actual control file values */"
			  exit }
			{ print $0 }
	' >$before_ctrl_params
cat crdb$ORACLE_SID.sql | $cmd '
	/option end  :/ { startpr = NR }
			{ if ( NR > startpr && startpr > 0 ) { print $0 } }
	' >$after_ctrl_params
cat $before_ctrl_params $ctrl_file_parms $after_ctrl_params >crdb$ORACLE_SID.sql
rm -f $ctrl_file_parms
rm -f $before_ctrl_params
rm -f $after_ctrl_params
echo ".... crdb$ORACLE_SID.sql contains the CREATE DATABASE script ... "