Oracle Utilities for Server-Side Functions

Database administrators are quite often asked to perform some database server-side tasks which seem more closely aligned to the operating system rather than the database. That is okay since it is not that unusual nor to be avoided. However, one cannot then attempt to employ the same tools that are used when performing more database-oriented tasks. Therefore, rather than using the old standbys such as SQL*Plus or Oracle Enterprise Manager (OEM), the Oracle DBA will often have to utilize several database server-based command line utilities supplied by either Oracle and/or the host operating system vendor. Often the Oracle DBA's interaction with these database server command line utilities will be scripted and frequently scheduled, i.e. the script runs automatically via either the Oracle scheduler, native operating system job schedulers, and/or third party job schedulers. As such, the Oracle DBA must be aware of and comfortable with many commands executed outside of the database so as to report upon or assist with the control of it.

One of the most fundamental tasks is the initial creation of a database. And while this does not happen all that often, except for possibly development and test, it is nonetheless a significantly critical task as many of the database creation options chosen during this process will establish a fundamental performance expectation baseline. While many of these choices can easily be modified later, some cannot. So do not rush through this task for each major Oracle release adds more options and alternatives to this universe of possibilities.

A long time ago, most DBAs had collections of scripts for creating various types of databases for differing roles across the numerous Oracle versions. Below is an historical attempt to create a single database creation shell script, mkdb.sh. It shows just how complex it is to write and then maintain such a script for the various Oracle versions (this script does Oracle versions 7.X to 10.X).

#!/bin/sh

if [ $# -ne 4 ]

then

echo

echo Usage: mkdb.sh SID VERS CHAR_SET NAT_CHAR_SET

echo

exit 1

CHAR_SET=$3; export CHAR_SET

NAT_CHAR_SET=$4; export NAT_CHAR_SET

# Redirect all output to log file

LOG_FILE=$1.mkdb.log

exec 2>$LOG_FILE 1>&2

# Set default Oracle Instance database

. setdb.sh $1 $2

sqlplus "system/manager as sysdba" <<EOF

shutdown

EOF

if [ -d $ORACLE_BASE/admin/$ORACLE_SID ]; then rm -rf $ORACLE_BASE/admin/$ORACLE_SID; fi

if [ -d $ORACLE_BASE/oradata/$ORACLE_SID ]; then rm -rf $ORACLE_BASE/oradata/$ORACLE_SID; fi

if [ ! -d $ORACLE_BASE/admin ]; then mkdir $ORACLE_BASE/admin; fi

if [ ! -d $ORACLE_BASE/admin/$ORACLE_SID ]; then mkdir $ORACLE_BASE/admin/$ORACLE_SID; fi

if [ ! -d $ORACLE_BASE/admin/$ORACLE_SID/bdump ]; then mkdir $ORACLE_BASE/admin/$ORACLE_SID/bdump; fi

if [ ! -d $ORACLE_BASE/admin/$ORACLE_SID/cdump ]; then mkdir $ORACLE_BASE/admin/$ORACLE_SID/cdump; fi

if [ ! -d $ORACLE_BASE/admin/$ORACLE_SID/udump ]; then mkdir $ORACLE_BASE/admin/$ORACLE_SID/udump; fi

if [ ! -d $ORACLE_BASE/oradata ]; then mkdir $ORACLE_BASE/oradata; fi

if [ ! -d $ORACLE_BASE/oradata/$ORACLE_SID ]; then mkdir $ORACLE_BASE/oradata/$ORACLE_SID; fi

if [ ! -d $ORACLE_BASE/admin/$ORACLE_SID/create ]; then mkdir $ORACLE_BASE/admin/$ORACLE_SID/create; fi

if [ ! -d $ORACLE_BASE/admin/$ORACLE_SID/pfile ]; then mkdir $ORACLE_BASE/admin/$ORACLE_SID/pfile; fi

cp $0 $ORACLE_BASE/admin/$ORACLE_SID/create

initora=$ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora

dbs_initora=$ORACLE_HOME/dbs/init$ORACLE_SID.ora

### General INIT.ORA Parameters

echo # Oracle Initiailization Parameters > $initora

echo db_name = $ORACLE_SID >> $initora

echo compatible = $ORACLE_VERS >> $initora

echo control_files = $"$ORACLE_BASE/oradata/$ORACLE_SID/control_01.ctl", "$ORACLE_BASE/oradata/$ORACLE_SID/control_02.ctl"$ >> $initora

echo background_dump_dest = $ORACLE_BASE/admin/$ORACLE_SID/bdump >> $initora

echo user_dump_dest = $ORACLE_BASE/admin/$ORACLE_SID/udump >> $initora

echo core_dump_dest = $ORACLE_BASE/admin/$ORACLE_SID/cdump >> $initora

echo db_block_size = 4096 >> $initora

echo processes = 200 >> $initora

echo open_cursors = 200 >> $initora

echo cpu_count = 4 >> $initora

echo shared_pool_size = 67108864 >> $initora

echo log_buffer = 1048576 >> $initora

echo sort_area_size = 4194304 >> $initora

echo sort_area_retained_size = 4194304 >> $initora

echo remote_login_passwordfile = EXCLUSIVE >> $initora

echo db_file_multiblock_read_count = 16 >> $initora

echo job_queue_processes = 1 >> $initora

echo log_checkpoint_timeout = 0 >> $initora

echo log_checkpoint_interval = 99999 >> $initora

echo cursor_space_for_time = true >> $initora

echo audit_trail = none >> $initora

echo timed_statistics = true >> $initora

echo sql_trace = false >> $initora

echo HASH_AREA_SIZE = 4194304 >> $initora

###

if [ -f $dbs_initora -o -h $dbs_initora ]

then

rm -f $dbs_initora

ln -s $initora $dbs_initora

dbs_orapw=$ORACLE_HOME/dbs/orapw$ORACLE_SID

if [ -f $dbs_orapw -o -h $dbs_orapw ]

then

rm -f $dbs_orapw

orapwd FILE=$dbs_orapw PASSWORD=oracle ENTRIES=10

if [ "$ORACLE_VERS" = "10.1.0" -o "$ORACLE_VERS" = "10.2.0" ]

then

echo "### Running Oracle 10.1.0/10.2.0 database creation script ###"

### Version INIT.ORA Parameters

echo optimizer_features_enable = 10.1.0 >> $initora

echo db_cache_size = 16777216 >> $initora

echo db_keep_cache_size = 4194304 >> $initora

echo db_recycle_cache_size = 4194304 >> $initora

echo large_pool_size = 8388608 >> $initora

echo java_pool_size = 8388608 >> $initora

echo undo_management = AUTO >> $initora

echo undo_tablespace = UNDO >> $initora

echo dbwr_io_slaves = 2 >> $initora

echo session_cached_cursors = 4 >> $initora

echo statistics_level = typical >> $initora

echo timed_os_statistics = 0 >> $initora

echo trace_enabled = false >> $initora

echo star_transformation_enabled = true >> $initora

echo bitmap_merge_area_size = 4194304 >> $initora

echo PARALLEL_ADAPTIVE_MULTI_USER = TRUE >> $initora

echo PARALLEL_THREADS_PER_CPU = 4 >> $initora

echo QUERY_REWRITE_ENABLED = TRUE >> $initora

###

sqlplus "system/manager as sysdba" <<EOF

spool $ORACLE_BASE/admin/$ORACLE_SID/create/mkdb

startup nomount pfile="$initora"

set echo on

create database $ORACLE_SID

controlfile reuse

logfile '$ORACLE_BASE/oradata/$ORACLE_SID/redo_log01.dbf' size 16M reuse,

'$ORACLE_BASE/oradata/$ORACLE_SID/redo_log02.dbf' size 16M reuse,

'$ORACLE_BASE/oradata/$ORACLE_SID/redo_log03.dbf' size 16M reuse,

'$ORACLE_BASE/oradata/$ORACLE_SID/redo_log04.dbf' size 16M reuse

datafile '$ORACLE_BASE/oradata/$ORACLE_SID/system01.dbf' size 200M reuse

sysaux datafile '$ORACLE_BASE/oradata/$ORACLE_SID/sysaux01.dbf' size 100M reuse

default temporary tablespace temp

tempfile '$ORACLE_BASE/oradata/$ORACLE_SID/temp01.dbf' size 100M reuse

extent management local uniform size 64k

undo tablespace undo

datafile '$ORACLE_BASE/oradata/$ORACLE_SID/undo01.dbf' size 100M reuse

character set $CHAR_SET

national character set $NAT_CHAR_SET;

create tablespace USER_LOCAL

datafile '$ORACLE_BASE/oradata/$ORACLE_SID/user_local01.dbf' size 400M reuse

extent management local uniform size 64k;

alter user sys identified by mgr;

alter user system identified by manager;

grant sysdba, sysoper to sys, system;

set echo off

set termout off

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catblock.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

@$ORACLE_HOME/rdbms/admin/profload.sql

alter user outln account lock;

alter user dbsnmp account lock;

connect system/manager

@$ORACLE_HOME/rdbms/admin/catdbsyn.sql

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

@$ORACLE_HOME/sqlplus/admin/plustrce.sql

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

grant all on system.plan_table to public;

create public synonym plan_table for system.plan_table;

spool off

EOF

else if [ "$ORACLE_VERS" = "9.0.1" -o "$ORACLE_VERS" = "9.2.0" ]

then

echo "### Running Oracle 9.0.1/9.2.0 database creation script ###"

### Version INIT.ORA Parameters

echo optimizer_features_enable = $ORACLE_VERS >> $initora

echo db_cache_size = 16777216 >> $initora

echo db_keep_cache_size = 4194304 >> $initora

echo db_recycle_cache_size = 4194304 >> $initora

echo large_pool_size = 8388608 >> $initora

echo java_pool_size = 8388608 >> $initora

echo undo_management = AUTO >> $initora

Oracle Utilities for Server-Side Functions

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.