Re: Extracting create db script

From: Andy <enzoweb_at_hotmail.com>
Date: 21 Jan 2002 18:14:26 -0800
Message-ID: <8d4033cd.0201211814.5d3f9ca0_at_posting.google.com>


If it's Unix, I use a script I got from http://www.dbaoncall.net, I've copied the link below.

They've split it up into 3 separate scripts, and I haven't tested these, so can't say they work. However, I have pasted the script I use and can verify it does work.

If this doesn't come out, email me at enzoweb_at_hotmail.com and I'll send it as an attachment.

Before running it, set your ORACLE_BASE otherwise it'll halt. Also, edit the script and change the pfile line accordingly. I use this all the time and it works a treat. If you change the database structure though, make sure you run this again to pick up any new datafiles etc.

http://www.dbaoncall.net/index_files/index_unix_scripts.html

Make sure it is executable, and run it with

crdb.sh SID sys/password

#***************************************************************************
#
# Script: crdb.sh
#
# 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) This script has been tested for Oracle 7, 8.0.3,
and 8.0.4.
# 2) 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.
# 3) oraenv must be found in the PATH.
# 4) The DBMS_SQL package must be executable by the
current
# username; see restriction #6 below.
# 5) If the current Oracle version is before Oracle 7.3,
you must
# comment out the below two "set trimspool on" lines.
# 6) If the current Oracle version is before Oracle
7.2.2.3, you
# must modify the script to remove all references to
the
# DBMS_SQL package, since this script uses this
package (and
# PL/SQL 2.2+ supports dynamic SQL).
# 7) If the current Oracle version is before Oracle 7.1,
you must
# comment out the reference to "sys.v_\$option".
# 8) The OPTIMAL value is not calculated for private
rollback
# segments that are allocated to a remote Parallel
Server
# instance.
# 9) The script must be executed from an account which
has DBA
# privileges or equivalent.
#
# 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_at_sunrisesys.com
#
# Modified by: Brian Lomasky - 08/24/98 - Added error debugging
info.
# Automatically support
autoextend
# for Oracle7/8. Fix
owner_name
# typo for public rollback
segments.
# Modified by: Brian Lomasky - 07/27/98 - Support autoextend,
support Oracle8,
# Use DBA_TABLESPACES
instead of
# SYS.TS$ for Oracle8 (since
SYS.TS$
# changed in Oracle8), add
set
# trimspool lines, skip
optimal value
# if null, enhance
restriction
# comments, use svrmgrl if
it exists
# (even if sqldba also
exists), remove
# utlmontr.sql. Enable redo
log
# threads for Parallel
Server.
# Modified by: Brian Lomasky - 06/09/98 - To handle maxextents
unlimited.
# 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/svrmgrl ]
then

        prg=svrmgrl
else

        prg='sqldba lmode=y'
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 echo off
set feedback off
set embedded off
set heading off
set pagesize 0
set recsep off
set termout off
set time off
set timing off
set verify 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, create the temp file which will contain the DDL to create the
database
#
# Note: To diagnose problems in the following PL/SQL script, change
the
# following sqlplus invocation line to: sqlplus $USERPW <<!EOD
# in order to display any error messages to the screen. Note that you
will
# also see a log of line continuation counters - This is normal when
running
# SQL*Plus via a Unix "here document".
#

sqlplus -s $USERPW >/dev/null<<!EOD
set echo off
set embedded off
set feedback off
set heading off
set linesize 80
set pagesize 0
set recsep off
set sqlprompt ""
set termout off
set time off
set timing off
set verify off
clear columns
clear breaks
set termout on
create table crdb_temp (code VARCHAR2(1), lineno NUMBER, text VARCHAR2(80));
def q=chr(39)
column maxmem1 new_value maxmem_len
select max(length(member) + 2) maxmem1 from sys.v_\$logfile; declare
        cursor ora8_cursor is select
                table_name
                from sys.dba_tables
                where owner = 'SYS' and table_name = 'TABPART$';
        cursor filext_cursor is select
                table_name
                from sys.dba_tables
                where owner = 'SYS' and table_name = 'FILEXT$';
        cursor mode_cursor is select
                log_mode
                from sys.v_\$database;
        cursor param_cursor is select
                to_number(value)
                from sys.v_\$parameter
                where name = 'db_block_size';
        cursor datafile_cursor (my_tablespace in varchar2) is select
                file_id,
                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
                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;

        ora8                    boolean;
        filext                  boolean;
        cursor1                 integer;
        cursor2                 integer;
        stmnt                   varchar2(2000);
        rows_processed          number;
        lv_table_name           sys.dba_tables.table_name%TYPE;
        lv_log_mode             sys.v_\$database.log_mode%TYPE;
        lv_db_block_size        number;
        lv_file_id              sys.dba_data_files.file_id%TYPE;
        lv_file_name            sys.dba_data_files.file_name%TYPE;
        lv_bytes                number;
        lv_maxbytes             number;
        lv_increment_by         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_min_extlen           number;
        lv_logging              varchar2(9);
        lv_owner                sys.dba_rollback_segs.owner%TYPE;
        lv_segment_name        
sys.dba_rollback_segs.segment_name%TYPE;
        lv_optimal              number;
        owner_name              varchar2(8);
        lv_lineno               number := 0;
        a_lin                   varchar2(80);
        n                       number;
        r                       number;
        error_desc              varchar2(132);

        function wri(x_cod in varchar2, x_lin in varchar2, x_str in
varchar2,
                x_force in number) return varchar2 is
        begin
                error_desc := 'wri ' || x_str;
                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;

        function km(b in number) return varchar2 is
        begin
                /* Calculate sizes in Mbytes or Kbytes, if possible */
                if mod(b, 1048576) = 0 then
                        return to_char(b / 1048576) || 'M';
                elsif mod(b, 1024) = 0 then
                        return to_char(b / 1024) || 'K';
                else
                        return to_char(b);
                end if;
        end km;

        procedure fetch_autoextend(my_file in number) is
        begin
                if ora8 then
                        cursor2 := dbms_sql.open_cursor;
                        stmnt := 'select inc,maxextend' ||
                                ' from sys.file$ where file#=' ||
                                to_char(my_file);
                        dbms_sql.parse(cursor2, stmnt,
dbms_sql.native);
                        dbms_sql.define_column(cursor2, 1,
lv_increment_by);
                        dbms_sql.define_column(cursor2, 2,
lv_maxbytes);
                        rows_processed := dbms_sql.execute(cursor2);
                        if dbms_sql.fetch_rows(cursor2) = 0 then
                                lv_increment_by := 0;
                                lv_maxbytes := 0;
                        else
                                dbms_sql.column_value(cursor2, 1,
                                        lv_increment_by);
                                dbms_sql.column_value(cursor2, 2,
lv_maxbytes);
                                lv_maxbytes := lv_maxbytes *
lv_db_block_size;
                        end if;
                        dbms_sql.close_cursor(cursor2);
                else
                        if filext then
                                cursor2 := dbms_sql.open_cursor;
                                stmnt := 'select inc,maxextend' ||
                                        ' from sys.filext$ where
file#=' ||
                                        to_char(my_file);
                                dbms_sql.parse(cursor2, stmnt,
dbms_sql.native);
                                dbms_sql.define_column(cursor2, 1,
                                        lv_increment_by);
                                dbms_sql.define_column(cursor2, 2,
                                        lv_maxbytes);
                                rows_processed :=
dbms_sql.execute(cursor2);
                                if dbms_sql.fetch_rows(cursor2) = 0
then
                                        lv_increment_by := 0;
                                        lv_maxbytes := 0;
                                else
                                        dbms_sql.column_value(cursor2,
1,
                                                lv_increment_by);
                                        dbms_sql.column_value(cursor2,
2,
                                                lv_maxbytes);
                                        lv_maxbytes := lv_maxbytes *
                                                lv_db_block_size;
                                end if;
                                dbms_sql.close_cursor(cursor2);
                        else
                                lv_increment_by := 0;
                                lv_maxbytes := 0;
                        end if;
                end if;
        end fetch_autoextend;

        procedure print_tablespace_lines is
        begin
                error_desc := 'tablespace ' || lv_tablespace_name;
                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_id,
                                lv_file_name,
                                lv_bytes;
                        exit when datafile_cursor%notfound;
                        error_desc := 'datafile ' ||
substr(lv_file_name,1,100);
                        fetch_autoextend(lv_file_id);
                        r := r + 1;
                        if r != 1 then
                                a_lin := wri('2', a_lin, ',', 1);
                        end if;
                        a_lin := wri('2', a_lin, '    ' || &q ||
lv_file_name ||
                                &q || ' SIZE ' || km(lv_bytes), 0);
                        if lv_increment_by <> 0 then
                                a_lin := wri('2', a_lin, ' autoextend
on', 0);
                                a_lin := wri('2', a_lin, ' next ' ||
                                        km(lv_increment_by), 0);
                                if lv_maxbytes > 2097152 *
lv_db_block_size then
                                        a_lin := wri('2', a_lin,
                                                ' maxsize unlimited',
0);
                                else
                                        a_lin := wri('2', a_lin, '
maxsize ' ||
                                                km(lv_maxbytes), 0);
                                end if;
                        end if;
                end loop;
                close datafile_cursor;
                error_desc := 'tablespace storage ' ||
lv_tablespace_name;
                a_lin := wri('2', a_lin, ' ', 0);
                a_lin := wri('2', a_lin, 'default storage', 1);
                a_lin := wri('2', a_lin, '    (initial ' ||
                        km(lv_initial_extent), 0);
                a_lin := wri('2', a_lin, ' next ' ||
km(lv_next_extent), 0);
                a_lin := wri('2', a_lin, ' pctincrease ' ||
lv_pct_increase, 0);
                a_lin := wri('2', a_lin, ' minextents ' ||
lv_min_extents, 0);
                if lv_max_extents > 999999 then
                        a_lin := wri('2', a_lin, ' maxextents
unlimited', 0);
                else
                        a_lin := wri('2', a_lin, ' maxextents ' ||
                                lv_max_extents, 0);
                end if;
                a_lin := wri('2', a_lin, ')', 0);
                if lv_min_extlen <> 0 then
                        a_lin := wri('2', a_lin, ' MINIMUM EXTENT ' ||
                                km(lv_min_extlen), 0);
                end if;
                if lv_logging = 'NOLOGGING' then
                        a_lin := wri('2', a_lin, ' NOLOGGING', 0);
                end if;
                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 print_tablespace_lines;
begin
        error_desc := 'init';
        --
        -- See if Oracle8
        --
        open ora8_cursor;
        fetch ora8_cursor into lv_table_name;
        if ora8_cursor%found then
                ora8 := TRUE;
        else
                ora8 := FALSE;
        end if;
        close ora8_cursor;
        --
        -- See if FILEXT$ table exists (to support autoextend)
        --
        open filext_cursor;
        fetch filext_cursor into lv_table_name;
        if filext_cursor%found then
                filext := TRUE;
        else
                filext := FALSE;
        end if;
        close filext_cursor;
        a_lin := '';
        --
        -- Get db_block_size value
        --
        open param_cursor;
        fetch param_cursor into lv_db_block_size;
        close param_cursor;
        --
        -- Get log mode
        --
        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_id,
                        lv_file_name,
                        lv_bytes;
                exit when datafile_cursor%notfound;
                error_desc := 'SYSTEM datafile ' ||
substr(lv_file_name,1,100);
                fetch_autoextend(lv_file_id);
                r := r + 1;
                if r != 1 then
                        a_lin := wri('0', a_lin, ',', 1);
                end if;
                a_lin := wri('0', a_lin, &q || lv_file_name || &q || '
SIZE ' ||
                        km(lv_bytes), 0);
                if lv_increment_by <> 0 then
                        a_lin := wri('0', a_lin, ' autoextend on', 0);
                        a_lin := wri('0', a_lin, ' next ' ||
                                km(lv_increment_by), 0);
                        if lv_maxbytes > 2097152 * lv_db_block_size
then
                                a_lin := wri('2', a_lin, ' maxsize
unlimited',
                                        0);
                        else
                                a_lin := wri('2', a_lin, ' maxsize '
||
                                        km(lv_maxbytes), 0);
                        end if;
                end if;
        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;
                error_desc := 'log ' || to_char(lv_group#);
                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;
                        error_desc := 'logfile ' ||
substr(lv_member,1,100);
                        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 ' || km(lv_bytes),
0);
        end loop;
        close log_cursor;
        if prev_group# != 99999 then
                a_lin := wri('1', a_lin, ';', 1);
        end if;
        prev_thread# := 99999;
        open thread_cursor;
        loop
                fetch thread_cursor into
                        lv_thread#,
                        lv_group#,
                        lv_members,
                        lv_bytes;
                exit when thread_cursor%notfound;
                error_desc := 'thread ' || to_char(lv_thread#);
                if prev_thread# <> lv_thread# then
                        if prev_thread# <> 99999 then
                                a_lin := wri('1', a_lin, ';', 1);
                                a_lin := wri('1', a_lin,
                                        'ALTER DATABASE ENABLE THREAD
' ||
                                        to_char(lv_thread#) || ';',
1);
                        end if;
                        a_lin := wri('1', a_lin,
                                'ALTER DATABASE ADD LOGFILE THREAD '
||
                                to_char(lv_thread#), 1);
                        prev_thread# := lv_thread#;
                        prev_group# := 99999;
                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;
                        error_desc := 'logfile ' ||
substr(lv_member,1,100);
                        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 ' || km(lv_bytes),
0);
        end loop;
        close thread_cursor;
        if prev_thread# <> 99999 then
                a_lin := wri('1', a_lin, ';', 1);
                a_lin := wri('1', a_lin,
                        'ALTER DATABASE ENABLE THREAD ' ||
                        to_char(lv_thread#) || ';', 1);
                a_lin := wri('1', a_lin, 'rem', 1);
        end if;
        error_desc := 'tablespace_cursor';
        if ora8 then
                cursor1 := dbms_sql.open_cursor;
                stmnt := 'select tablespace_name,initial_extent,' ||
                       
'next_extent,min_extents,max_extents,pct_increase,' ||
                        'status,contents,min_extlen,logging' ||
                        ' from sys.dba_tablespaces' ||
                        ' where tablespace_name <> ' || &q || 'SYSTEM'
|| &q ||
                        ' order by 1';
                dbms_sql.parse(cursor1, stmnt, dbms_sql.native);
                dbms_sql.define_column(cursor1, 1, lv_tablespace_name,
30);
                dbms_sql.define_column(cursor1, 2, lv_initial_extent);
                dbms_sql.define_column(cursor1, 3, lv_next_extent);
                dbms_sql.define_column(cursor1, 4, lv_min_extents);
                dbms_sql.define_column(cursor1, 5, lv_max_extents);
                dbms_sql.define_column(cursor1, 6, lv_pct_increase);
                dbms_sql.define_column(cursor1, 7, lv_tablesp_status,
30);
                dbms_sql.define_column(cursor1, 8,
lv_tablesp_contents, 30);
                dbms_sql.define_column(cursor1, 9, lv_min_extlen);
                dbms_sql.define_column(cursor1, 10, lv_logging, 9);
                rows_processed := dbms_sql.execute(cursor1);
                loop
                        if dbms_sql.fetch_rows(cursor1) = 0 then
                                exit;
                        end if;
                        dbms_sql.column_value(cursor1, 1,
lv_tablespace_name);
                        dbms_sql.column_value(cursor1, 2,
lv_initial_extent);
                        dbms_sql.column_value(cursor1, 3,
lv_next_extent);
                        dbms_sql.column_value(cursor1, 4,
lv_min_extents);
                        dbms_sql.column_value(cursor1, 5,
lv_max_extents);
                        dbms_sql.column_value(cursor1, 6,
lv_pct_increase);
                        dbms_sql.column_value(cursor1, 7,
lv_tablesp_status);
                        dbms_sql.column_value(cursor1, 8,
lv_tablesp_contents);
                        dbms_sql.column_value(cursor1, 9,
lv_min_extlen);
                        dbms_sql.column_value(cursor1, 10,
lv_logging);
                        print_tablespace_lines;
                end loop;
                dbms_sql.close_cursor(cursor1);
        else
                lv_min_extlen := 0;
                lv_logging := 'LOGGING';
                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;
                        print_tablespace_lines;
                end loop;
                close tablespace_cursor;
        end if;
        error_desc := 'addtl roll';
        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;
                error_desc := 'rollback ' ||
substr(lv_segment_name,1,100);
                if lv_owner = 'PUBLIC' then
                        owner_name := ' PUBLIC ';
                else
                        owner_name := ' ';
                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 ' ||
                        km(lv_initial_extent), 0);
                a_lin := wri('2', a_lin, ' next ' ||
km(lv_next_extent), 0);
                a_lin := wri('2', a_lin, ' minextents ' ||
lv_min_extents, 0);
                if lv_max_extents > 999999 then
                        a_lin := wri('2', a_lin, ' maxextents
unlimited', 0);
                else
                        a_lin := wri('2', a_lin, ' maxextents ' ||
                                lv_max_extents, 0);
                end if;
                error_desc := 'optimal ' ||
substr(lv_segment_name,1,100);
                open optimal_cursor (lv_segment_name);
                fetch optimal_cursor into lv_optimal;
                if optimal_cursor%found then
                        if lv_optimal is not null then
                                a_lin := wri('2', a_lin, ' optimal '
||
                                        km(lv_optimal), 0);
                        end if;
                end if;
                close optimal_cursor;
                a_lin := wri('2', a_lin, ');', 1);
        end loop;
        close rollback_cursor;
        error_desc := 'commit';
        commit;
exception
        when others then
                rollback;
                raise_application_error(-20000,
                        'Unexpected error ' || to_char(SQLCODE) ||
chr(10) ||
                        sqlerrm || chr(10) ||
                        'After ' || error_desc || chr(10) ||
'Aborting...');
end;
/
!EOD
#
# Next, generate the actual DDL to create the database
#

sqlplus -s $USERPW >/dev/null <<!EOD
set echo off
set embedded off
set feedback off
set heading off
set linesize 80
set pagesize 0
set recsep off
set sqlprompt ""
set termout off
set time off
set timing off
set trimspool on
set verify 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, and prvtpool.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 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;
col parameter format a50
col value format a10
select 'rem ', parameter, ' :', value from sys.v_\$option; col value format a50
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 'set trimspool on' from dual;
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 '_at_\$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 _at_\$ORACLE_HOME/rdbms/admin required scripts' from dual;
select 'rem' from dual;
select 'commit;' from dual;
select 'rem' from dual;
select '_at_\$ORACLE_HOME/rdbms/admin/catproc.sql' from dual;
select 'rem _at_\$ORACLE_HOME/rdbms/admin/catparr.sql' from dual;
select '_at_\$ORACLE_HOME/rdbms/admin/dbmspool.sql' from dual;
select '_at_\$ORACLE_HOME/rdbms/admin/prvtpool.sql' from dual;
select 'rem' from dual;
select 'commit;' from dual;
select 'rem' from dual;
select 'connect system/manager' from dual;
select '_at_\$ORACLE_HOME/sqlplus/admin/pupbld.sql' from dual;
select '_at_\$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 ... "

vadolt_at_yahoo.com (vdolt) wrote in message news:<25903991.0201211055.671facab_at_posting.google.com>...

> I have inherited several existing databases. As part of recovery
> strategy
> I want to create script that allows me to recreate this databases from
> scratch.
> I can save init.ora file, but all of the existing configurations for
> tablespaces, users, etc. has to be extracted. I am not interested in
> the database schemas (tables, etc.), because I can get them with the
> export.
> Is there tool or Oracle command that would generate createdb script
> including
> tablespaces, rollback segments, redo logs, etc.?
> 
> Thank you
Received on Tue Jan 22 2002 - 03:14:26 CET

Original text of this message