Here is one that I used to use some time ago (for Oracle 6 and 7).
This does not support all the *new* stuff available since Oracle8, but it
should not be difficult to change it.. if you do, I would appreciate a copy
:)
- Kirti
#!/bin/ksh
#---------------------------------------------------------------------------
----
# crdb.ksh - creates sql script that contains statements necessary
# to recreate a database with the current physical
# configuration.
#
# parameters - SID (oracle database identifier)
#
# output - crdb_<SID>.sql in $ORACLE_HOME/admin/$ORACLE_SID/scripts
directory
#
# Author - Quin Bligh
# Kirti Deshpande
#
#---------------------------------------------------------------------------
----
get_perm_db_params ()
{
echo "Getting permanent database parameters from controlfile trace..."
touch $TFILE
sleep 2
sqlplus -s <<END1 > /dev/null
sys/$SYSPW
alter database backup controlfile to trace;
END1
(sqlplus -s <<END1A
sys/$SYSPW
set heading off
set feedback off
set pages 0
select replace(value,'?','$ORACLE_HOME')
from v\$parameter
where name = 'user_dump_dest';
END1A
) |
read USER_DUMP_DEST
TRACE_FILE=`find $USER_DUMP_DEST -newer $TFILE -name ora_"*".trc -print
2>/dev/null`
MAXLOGFILES=`grep -i maxlogfiles ${TRACE_FILE}`
MAXDATAFILES=`grep -i maxdatafiles ${TRACE_FILE}`
MAXINSTANCES=`grep -i maxinstances ${TRACE_FILE}`
MAXLOGMEMBERS=`grep -i maxlogmembers ${TRACE_FILE}`
MAXLOGHISTORY=`grep -i maxloghistory ${TRACE_FILE}`
rm -f $TFILE
(sqlplus -s <<END1C
sys/$SYSPW
set heading off
set feedback off
set pages 0
select value
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
END1C
) | read NLS_CHARSET
echo "Char set is $NLS_CHARSET"
}
#---------------------------------------------------------------------------
----
get_dbs_files ()
{
echo "Getting system tablespace definition..."
>$SYSFILE
sqlplus -s <<END2 > $TFILE
sys/$SYSPW
set heading off
set feedback off
set pages 0
select '''' || file_name || '''' || ' size ' || bytes/1024 || 'k'
from dba_data_files
where tablespace_name = 'SYSTEM';
END2
i=0
num=`wc -l $TFILE`
cat $TFILE | while read lines
do
i=$(($i + 1))
if [ $i -lt $num ] ; then
echo "\t$lines," >> $SYSFILE
else
echo "\t$lines" >> $SYSFILE
fi
done
}
#---------------------------------------------------------------------------
----
get_log_files ()
{
echo "Getting log file definitions..."
>$LOGFILE
HOLDGROUP=""
(sqlplus -s <<END3
sys/$SYSPW
set heading off
set feedback off
set pages 0
select a.group# || ' ' || a.member || ' ' || b.bytes/1024
from v\$logfile a, v\$log b
where a.group# = b.group#
order by a.group#;
END3
) |
while read GROUP MEMBER SIZE
do
if [[ $GROUP != $HOLDGROUP ]]
then
[ -z "$HOLDGROUP" ] || echo "\n\t\t ) size ${HOLDSIZE}k," >> $LOGFILE
echo "\tGROUP $GROUP (" >> $LOGFILE
HOLDGROUP=$GROUP
HOLDSIZE=$SIZE
else
echo "," >> $LOGFILE
fi
echo "\t\t '$MEMBER'\c" >> $LOGFILE
done
echo "\n\t\t ) size ${HOLDSIZE}k" >> $LOGFILE
}
#---------------------------------------------------------------------------
----
get_dbf_files ()
{
concat_file ()
{
i=0
num=`wc -l /tmp/$TSNAME.$$`
cat /tmp/$TSNAME.$$ | while read lines
do
i=$(($i + 1))
echo $lines | grep "^create" >/dev/null
if [ $? -eq 0 ] ; then
echo "\n$lines" >> $DBFILES
else
echo $lines | grep -v "size" >/dev/null
if [ $? -eq 0 ] ; then
echo "\t$lines" >> $DBFILES
elif [ $i -lt $num ] ; then
echo "\t$lines," >> $DBFILES
else
echo "\t$lines;" >> $DBFILES
fi
fi
done
}
echo "Getting all other tablespace definitions..."
>$TFILE
>$DBFILES
(sqlplus -s <<END4
sys/$SYSPW
set heading off
set feedback off
set pages 0
select tablespace_name ||' '|| initial_extent ||' '||
next_extent ||' '|| min_extents ||' '|| max_extents ||' '||
pct_increase
from dba_tablespaces
where tablespace_name != 'SYSTEM'
and status != 'INVALID'
order by tablespace_name;
END4
) |
while read TSNAME INITEXT NEXTEXT MINEXT MAXEXT PCTINC
do
echo "create tablespace $TSNAME" > /tmp/$TSNAME.$$
echo "\t default storage (" >> /tmp/$TSNAME.$$
echo "\t\t initial $INITEXT" >> /tmp/$TSNAME.$$
echo "\t\t next $NEXTEXT" >> /tmp/$TSNAME.$$
echo "\t\t minextents $MINEXT" >> /tmp/$TSNAME.$$
echo "\t\t maxextents $MAXEXT" >> /tmp/$TSNAME.$$
echo "\t\t pctincrease $PCTINC" >> /tmp/$TSNAME.$$
echo "\t )" >> /tmp/$TSNAME.$$
echo "\t datafile" >> /tmp/$TSNAME.$$
sqlplus -s <<END5 >> /tmp/$TSNAME.$$
sys/$SYSPW
set heading off
set feedback off
set pages 0
select '''' || file_name || '''' || ' size ' || bytes/1024 || 'k'
from dba_data_files
where tablespace_name = '$TSNAME'
order by file_name;
END5
concat_file
rm -f /tmp/$TSNAME.$$
done
}
#---------------------------------------------------------------------------
----
get_rbs ()
{
echo "Getting rollback segment definitions..."
>$RBS
(sqlplus -s <<END6
sys/$SYSPW
set heading off
set feedback off
set pages 0
select a.segment_name ||' ' || a.tablespace_name ||' '||
decode(b.optsize,0,a.initial_extent+3*a.next_extent,
null,a.initial_extent+3*a.next_extent,b.optsize)
from dba_rollback_segs a, v\$rollstat b
where a.tablespace_name != 'SYSTEM'
and a.segment_id = b.usn
order by a.segment_name;
END6
) |
while read RBNAME TSNAME OPTSIZE
do
echo "create rollback segment $RBNAME tablespace $TSNAME storage" \
"(optimal $OPTSIZE);" >>$RBS
done
}
#---------------------------------------------------------------------------
----
cat_sql ()
{
echo "Building sql script..."
cat -s <<END > $SQL
REM *
connect internal
!cat ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora | grep -vi "rollback_segments"
> ${ORACLE_HOME}/dbs/init${ORACLE_SID}_0.ora
startup nomount pfile=$ORACLE_HOME/dbs/init${ORACLE_SID}_0.ora
set echo on
spool crdb${ORACLE_SID}
REM * Create the <dbname> database.
REM * SYSTEM tablespace configuration guidelines:
REM * General-Purpose ORACLE RDBMS 50Mb
REM * Additional dictionary for applications 10-50Mb
REM * Redo Log File configuration guidelines:
REM * Use 3+ redo log files to relieve ``cannot allocate new log...''
waits.
REM * Use ~100Kb per redo log file per connection to reduce checkpoints.
REM *
create database "$ORACLE_SID"
$MAXLOGFILES
$MAXDATAFILES
$MAXINSTANCES
$MAXLOGMEMBERS
$MAXLOGHISTORY
character set "$NLS_CHARSET"
datafile
`cat $SYSFILE`
logfile
`cat $LOGFILE`
;
shutdown;
startup open pfile=${ORACLE_HOME}/dbs/init${ORACLE_SID}_0.ora
REM * install data dictionary views:
@${ORACLE_HOME}/rdbms/admin/catalog.sql
@${ORACLE_HOME}/rdbms/admin/catproc.sql
@${ORACLE_HOME}/rdbms/admin/catblock.sql
REM * Install dbms utilities
@${ORACLE_HOME}/rdbms/admin/dbmspool.sql
@${ORACLE_HOME}/rdbms/admin/dbmspipe.sql
REM *
REM * Create rest of datafiles
REM *
`cat $DBFILES`
REM *
REM * Create rollback segments.
REM *
`cat $RBS`
REM *
REM * Restart the instance to activate the the additional rollback segments.
REM *
shutdown
disconnect
connect internal
startup
REM * Alter SYS and SYSTEM users.
REM *
alter user sys temporary tablespace temp;
alter user system temporary tablespace temp;
END
}
#---------------------------------------------------------------------------
----
# main ()
#
NAME=`basename $0`
case $# in
0)
echo "Enter oracle sid: [$ORACLE_SID] \c"
read MYSID
export ORACLE_SID=${MYSID:-$ORACLE_SID}
;;
1)
export ORACLE_SID=$1
;;
*)
echo "Usage: $NAME [sid]"
exit 1
;;
esac
clear
echo ""
echo "Running $NAME to create $ORACLE_SID rebuild script\n"
echo " Please enter password for 'SYS' : \c"
stty -echo
read SYSPW
stty echo
echo "\n\n\n"
# Set up ORACLE Environment
export ORAENV_ASK=NO
. oraenv
#
# Check if the SYS password is correct and if the DB is up
#
sqlplus -s << END > /tmp/crdb_chk.$$
SYS/$SYSPW
exit;
END
grep 'ORA-01017' /tmp/crdb_chk.$$ > /dev/null
if [ $? = 0 ]
then
echo "\n\n"
echo "--------- ERROR ----------"
echo "**** Invalid SYS password "
echo "\n\n\n"
exit 1
fi
grep 'ORA-01034' /tmp/crdb_chk.$$ > /dev/null
if [ $? = 0 ]
then
echo "\n\n"
echo "------------ ERROR ------------"
echo "**** Database is not accessible"
echo "\n\n\n"
exit 1
fi
TFILE=/tmp/crdb_tmp.$$
SYSFILE=/tmp/crdb_sysfile.$$
LOGFILE=/tmp/crdb_logfile.$$
DBFILES=/tmp/crdb_dbfiles.$$
RBS=/tmp/crdb_rbs.$$
SQL=$ORACLE_BASE/admin/${ORACLE_SID}/scripts/crdb_${ORACLE_SID}.sql
trap "rm -f /tmp/crdb_*.$$;exit" 0 1 2 3 4 5 6 15
get_perm_db_params
get_dbs_files
get_log_files
get_dbf_files
get_rbs
cat_sql
chmod 640 $SQL
echo "\n$NAME done creating sql script! -- See $SQL"
#--- End of File -------
-----Original Message-----
Sent: Monday, September 09, 2002 10:33 AM
To: Multiple recipients of list ORACLE-L
If you export with rows=n, you get a text file with all the ddl to recreate
the exportable objects. However, packages/procedures are formatted and not
at all easy to use to recreate and the storage clauses are all in bytes. Not
a pretty method, but certainly one that can be used.
-----Original Message-----
Sent: Friday, September 06, 2002 3:59 PM
To: Multiple recipients of list ORACLE-L
Export can do this. Right?
But the output is not a script but a binary file only Import can understand.
ltiu
Connie Milliken wrote:
>Does anyone have a script that will write another script to recreate a
>particular database quickly with all the info specific to that particular
>database?
>
>Seems to me that I have seen this somewhere before, but I am not sure
where.
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
