Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Recreate database script

RE: Recreate database script

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Mon, 09 Sep 2002 08:18:27 -0800
Message-ID: <F001.004CB07C.20020909081827@fatcity.com> 





Forgot to mention that this will only build the db create script. 
Nothing more than that. 






-----Original Message-----


Sent: Monday, September 09, 2002 10:07 AM
To: 'ORACLE-L_at_fatcity.com'




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
:) 






#!/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: kirti.deshpande_at_verizon.com

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: ListGuru_at_fatcity.com (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).


Received on Mon Sep 09 2002 - 11:18:27 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US