Automating Oracle Database Creation

Why?

I went through some time when, for some reason, I found myself creating lots of new Oracle databases on various systems. These databases were primarily on remote Solaris systems (because, as always, I don’t believe in running Oracle on Windows!).

The “obvious” way to create databases is with the Database Configuration Assistant (DBCA). However, I was unsatisfied with this approach for several reasons:

First, DBCA is a GUI tool and I only connect to the database server with SSH. To use DBCA, I ran a local X server and used X11 forwarding over SSH. Technically effective, but X over anything other than fast local network is barely usable.

Second, I wanted to provision databases that were as “lean and mean” as possible. The databases were usually for development or quick testing of different applications, and most applications didn’t depend on too much Oracle-specific functionality or advanced Oracle features. The databases that come out of DBCA always seemed a bit bloated to me. Furthermore, for applications that do use specific Oracle features (such as the embedded Java runtime, Streams, CDC, etc.), I want to know specifically what needs to be added to the base database to enable the functionality rather than just relying on a install-everything approach.

Finally, I believe anything you need to do server-side to deploy applications should be automated (or at least support the ability to automate the tasks). Creating the databases using the same automated script across my environments is much lower risk than remembering to click all the same settings in a GUI tool when I move through environments. Another aspect of this is that I was finding databases I created using DBCA on different systems tended to have variances in where various directories were created depending on how Oracle was installed. Over time I’ve come to like a particular scheme for organizing multiple databases on a single server, so by scripting the process I can go to any server that I’ve created databases on and know exactly where to find everything.

With all of that in mind, I went in search of the deep dark secrets of creating Oracle databases through PL/SQL instead of DBCA. This really boils down to three steps:

  1. Prepare to create the database
  2. Create the database
  3. Run post-creation scripts

Preparing to create the database really just involves making the directory structure you want and preparing the Oracle parameters file for the database you are going to create.

Then, creating the database is the big SQL statement to actually (duh!) create the database.

And finally, you need to run the SQL scripts to create the initial schema objects. This is also the first good opportunity to migrate the pfile to an spfile.

How?

The approach I took is to write a shell script that creates the directory structure and outputs the SQL and shell scripts to create the individual database (in the database’s admin directory so that the creation scripts used for a particular database are tucked away in that particular database’s directory structure for future reference).

The “creation script creator script” has some parameters you can change to indicate where Oracle is installed, and then of course the rest of the script builds paths based on how I normally set things up and like to see it organized. Very briefly, Oracle product is installed under /u01 and all of my data files go under /u02/oradata/database and recovery files go under /u02/orarecovery/database. I throw two control files under /u02 and stash one under /u01 on the theory that /u01 and /u02 should be different LUNs. Any other administrative stuff goes under /u01/app/oracle/admin/database.

The SID of the database you want to create is the only command-line parameter to the script. If you want anything else to be different, you need to edit the script ahead of time. If you don’t change the template for database creation and parameter file creation in the script, you’ll end up with a character set of AL32UTF8 and the database configured to use about 512MB of RAM on the system.

So without further ado, here’s the script I use:

#!/bin/sh

DB_SID=$1
DB_DOMAIN=mattwilson.org

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=${ORACLE_BASE}/product/10.2.0/db_1
ORACLE_ADMIN=${ORACLE_BASE}/admin/${DB_SID}

DATA_PRIMARY=/u02/oradata/${DB_SID}
DATA_SECONDARY=/u01/app/oracle/oradata/${DB_SID}
DATA_RECOVERY=/u02/orarecovery

# Create admin directories
mkdir -p ${ORACLE_ADMIN}
for x in adump bdump cdump udump scripts
do
        mkdir ${ORACLE_ADMIN}/${x}
done

# Create data directories
mkdir -p $DATA_PRIMARY
mkdir -p $DATA_SECONDARY
mkdir -p $DATA_RECOVERY

# Create init.ora file for instance
cat - > ${ORACLE_ADMIN}/scripts/init.ora << __EOF__
db_name = $DB_SID
db_domain = $DB_DOMAIN

db_block_size = 8192
undo_management = auto
undo_tablespace = undotbs1

control_files = (${DATA_PRIMARY}/${DB_SID}_ctrl_01.ctl,
                 ${DATA_PRIMARY}/${DB_SID}_ctrl_02.ctl,
                 ${DATA_SECONDARY}/${DB_SID}_ctrl_03.ctl)

background_dump_dest = ${ORACLE_ADMIN}/bdump
core_dump_dest = ${ORACLE_ADMIN}/cdump
user_dump_dest = ${ORACLE_ADMIN}/udump
audit_file_dest = ${ORACLE_ADMIN}/adump

db_recovery_file_dest = $DATA_RECOVERY
db_recovery_file_dest_size = 2147483648

sga_target = 402653184
__EOF__

# Create database creation script
cat - > ${ORACLE_ADMIN}/scripts/create.sql << __EOF__
connect / as sysdba
set echo on
spool ${ORACLE_ADMIN}/scripts/create.log

startup nomount pfile=${ORACLE_ADMIN}/scripts/init.ora;

CREATE DATABASE "${DB_SID}"
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET UTF8
DATAFILE '${DATA_PRIMARY}/system01.dbf'
        SIZE 128M
        AUTOEXTEND ON
        NEXT 128M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '${DATA_PRIMARY}/sysaux01.dbf'
        SIZE 128M
        AUTOEXTEND ON
        NEXT 128M MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '${DATA_PRIMARY}/undotbs01.dbf'
        SIZE 128M
        AUTOEXTEND ON
        NEXT 16M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
        TEMPFILE '${DATA_PRIMARY}/temp01.dbf'
        SIZE 32M
        AUTOEXTEND ON
        NEXT 8M MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS DATAFILE '${DATA_PRIMARY}/users01.dbf'
        SIZE 64M
        AUTOEXTEND ON
        NEXT 64M MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('${DATA_PRIMARY}/redo01.log') SIZE 64M,
        GROUP 2 ('${DATA_PRIMARY}/redo02.log') SIZE 64M,
        GROUP 3 ('${DATA_PRIMARY}/redo03.log') SIZE 64M;

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

connect system/manager
@?/sqlplus/admin/pupbld

connect / as sysdba
shutdown immediate;
connect / as sysdba
startup mount pfile=${ORACLE_ADMIN}/scripts/init.ora;
alter database archivelog;
alter database open;
create spfile='${ORACLE_HOME}/dbs/spfile${DB_SID}.ora'
        from pfile='${ORACLE_ADMIN}/scripts/init.ora';
shutdown immediate;
startup;

execute utl_recomp.recomp_serial();

exit;
__EOF__

# Create run script
cat - > ${ORACLE_ADMIN}/scripts/create.sh << __EOF__
#!/bin/sh
ORACLE_HOME=$ORACLE_HOME
ORACLE_SID=$DB_SID
export ORACLE_HOME ORACLE_SID
\$ORACLE_HOME/bin/sqlplus /nolog @create
__EOF__

chmod +x ${ORACLE_ADMIN}/scripts/create.sh

# All done!
echo -------------------------------------------------------------
echo Ready to run create database script.
echo Go to ${ORACLE_ADMIN}/scripts
echo Then run create.sh in that directory.
echo -------------------------------------------------------------

Just save that as something like create-setup-script.sh, make it executable, and you’re all set!

  1. Awesome … worked like a charm !!!

  2. I am unable to create database, Its says “database is not open” , Any help would be appreciable.

    Thank you!

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>