Home » RDBMS Server » Server Administration » database script
| database script [message #365963] |
Wed, 02 August 2000 09:39  |
Kevin McInerney
Messages: 7
Registered: July 2000
|
Junior Member |
|
|
Hi,
does anyone know of any way to create a database script for an existing database?. I've been amending the database using Enterprise Manager and would like to be able to create a script which I could just run on another server to create the same database setup quickly.
many thanks,
Kevin
|
|
|
|
| Re: database script [message #366003 is a reply to message #365963] |
Mon, 11 September 2000 16:08  |
Karine Bouis-Towe
Messages: 1
Registered: September 2000
|
Junior Member |
|
|
#***************************************************************************
#
# 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) 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@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 ' ||
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 06:54:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
