A shell script to manually create an Oracle 9 database
This shell script is not perfect but, I have used it on several occasions to manually install an Oracle database for development purposes on Red Hata Advanced Server 2.1 ( RHAS 2.1 ). I am only putting it herein the hope that it may save someone the trouble if they ever need to write one. The Oracle install is by no means perfect, persoannly, I hate the Oracle installer, it just seems to go wrong whenever you least expect it.
If you do use this script I would appreciate you letting me know of any improvments or just the fact that you found it useful.
#!/bin/bash echo echo " This script will eventually be sufficient to create an oracle database after you have used the Oracle installer. I have written it to replace "dbca" due to numerous problems that I had with it. The database that it creates is not meant to be for production but I am sure that there are people out there who could modify it for that purpose. This is for development only." echo #exit 0 if [ $# -ne 1 ]; then echo echo " You are not using this script correctly" echo " Usage: $0 \$ORACLE_SID" echo exit 1 fi if [ -z $ORACLE_HOME ]; then echo echo "Please check your environment variables and try again." echo exit 1 fi USER_NAME=$(id -un) if [ $USER_NAME != "oracle" ]; then echo echo " You must be logged in as Oracle for this script to work. You are currently logged in as $USER_NAME." echo exit 1 fi echo echo " 1. I am about to remove/create an entire database. Please be very careful what you do here. 2. This script does not do anything fancy like checking your disks for available space etc. 3. This script has been written to be edited by YOU for YOUR install. You can then try it and re-run it as many times as you want to get your database the way you want it. It is not ready to run as it is. 4. The main editing will be required to sort out where you are to store the data. " echo echo -n "Please enter y or n to continue. Select n if you are unsure: "; read CONFIRMATION if [ $CONFIRMATION != y ]; then exit 0 fi ORACLE_SID=$1 ORATAB_ENTRY=` cat /etc/oratab | grep "${ORACLE_SID}\:${ORACLE_HOME}\:N"` INITORACLE_SID=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora DATA=/opt/oracle/oradata DEV_SDA02=${DATA}/scsi01 DEV_SDA03=$DATA/scsi02 #DEV_SDA05=$DATA/scsi03 #DEV_SDA06=$DATA/scsi04 DEV_HDD1=${DATA}/ide01 #DEV_HDD2=$DATA/ide02 DEV_HDD5=${DATA}/u01 #DEV_HDD6=$DATA/u02 CONTROL1=${DEV_HDD5}/${ORACLE_SID}/control01.ctl CONTROL2=${DEV_HDD1}/${ORACLE_SID}/control02.ctl CONTROL3=${DEV_SDA02}/${ORACLE_SID}/control03.ctl # We need to have at least two redo log groups consisting # of two or more members. Each member in a group must be # on a seperate disk. # Since I have trhee dsiks I am going to use 3 groups with # three members. Please see the Admin guide for more info on # why I am doing this. # Group 1 REDO01a=${DEV_HDD5}/${ORACLE_SID}/redo01a.log REDO01b=${DEV_HDD1}/${ORACLE_SID}/redo01b.log REDO01c=${DEV_SDA02}/${ORACLE_SID}/redo01c.log # Group 2 REDO02a=${DEV_HDD5}/${ORACLE_SID}/redo02a.log REDO02b=${DEV_HDD1}/${ORACLE_SID}/redo02b.log REDO02c=${DEV_SDA02}/${ORACLE_SID}/redo02c.log # Group 3 REDO03a=${DEV_HDD5}/${ORACLE_SID}/redo03a.log REDO03b=${DEV_HDD1}/${ORACLE_SID}/redo03b.log REDO03c=${DEV_SDA02}/${ORACLE_SID}/redo03c.log # These are the archive locations REDO_LOG_ARCHIVE01=${DATA}/${DEV_HDB05}/REDO_LOG_ARCHIVE01 REDO_LOG_ARCHIVE02=${DATA}/${DEV_HDD01}/REDO_LOG_ARCHIVE02 REDO_LOG_ARCHIVE03=${DATA}/${DEV_SDA03}/REDO_LOG_ARCHIVE03 SYSTEM_FILE=${DEV_SDA02}/${ORACLE_SID}/system01.dbf TEMP_FILE=${DEV_SDA02}/${ORACLE_SID}/temp01.dbf UNDOTBS_FILE=${DEV_SDA02}/${ORACLE_SID}/undotbs01.dbf MAIN_FILE=${DEV_SDA02}/${ORACLE_SID}/main01.dbf INDEX_FILE=${DEV_SDA02}/${ORACLE_SID}/indx01.dbf USERS_FILE=${DEV_SDA02}/${ORACLE_SID}/users01.dbf CATALOG_SCRIPT=${ORACLE_HOME}/rdbms/admin/catalog.sql; CATPROC_SCRIPT=${ORACLE_HOME}/rdbms/admin/catproc.sql; BACKGROUND_DUM_DEST=/opt/oracle/admin/${ORACLE_SID}/bdump CORE_DUMP_DEST=/opt/oracle/admin/${ORACLE_SID}/cdump USER_DUMP_DEST=/opt/oracle/admin/${ORACLE_SID}/udump # The redo size will determine how big the archived # logs are and will be very dependant on how long you want # the recovery procedures to take and how much usage your # database gets. # I am using small values here to enable testing of this script. REDO_SIZE="100K" #SYSTEM_FILE_SIZE="600M" SYSTEM_FILE_SIZE="400M" #TEMP_FILE_SIZE="400M" TEMP_FILE_SIZE="100M" #MAIN_FILE_SIZE="600M" MAIN_FILE_SIZE="400M" #INDEX_FILE_SIZW="400M" INDEX_FILE_SIZW="100M" #UNDOTBS_FILE_SIZE="200M" UNDOTBS_FILE_SIZE="100M" #USERS_FILE_SIZE="400M" USERS_FILE_SIZE="100M" FILES=" $CONTROL1 $CONTROL2 $CONTROL3 $REDO01a $REDO01b $REDO01c $REDO02a $REDO02b $REDO02c $REDO03a $REDO03b $REDO03c $REDO_LOG_ARCHIVE01 $REDO_LOG_ARCHIVE02 $REDO_LOG_ARCHIVE03 $SYSTEM_FILE $TEMP_FILE $UNDOTBS_FILE $INDEX_FILE $USERS_FILE " FILES2=" $BACKGROUND_DUM_DEST $CORE_DUMP_DEST $USER_DUMP_DEST " echo " $FILES We have now decided in the above where all the files etc are meant to go so now I am going to create the directories if they do not already exist." echo echo -n "Hit x to stop any other key to continue: " read CONTINUE echo if [ $CONTINUE = x ]; then exit 1 fi for directory in `echo ${FILES}` do if [ ! -d $(dirname $directory) ]; then mkdir -p $(dirname $directory); echo echo "I have just created the following directory." echo " $directory" echo sleep 1 fi done for directory in `echo ${FILES2}` do if [ ! -d $directory ]; then mkdir -p $directory; echo echo "I have just created the following directory." echo " $directory" echo sleep 1 fi done if [ -z $ORATAB_ENTRY ]; then echo echo "We are now updating your /etc/oratab file." echo echo "# The entry below was entered by the script $0" << /etc/oratab echo "${ORACLE_SID}:${ORACLE_HOME}:N" << /etc/oratab echo fi PATH=$ORACLE_HOME/bin:/usr/bin:/bin ORAPWD=/opt/oracle/product/9.2.0/dbs/orapw${ORACLE_SID} if [ -f $ORAPWD ]; then rm $ORAPWD fi echo echo Creating $ORAPWD orapwd file=$ORAPWD password=change_on_install entries=20 echo echo Done echo echo echo " We need to shut down the instance before we remove the dbf files. I am about to check if you have an instance running or not. If you have and the control files are missing for it then you will need to kill the instance first." echo sqlplus /nolog<<SHUTDOWN connect / as sysdba shutdown SHUTDOWN COUNTER=0 until [ -z "$(ps -ax | grep "ora_...._${ORACLE_SID}")" ]; do let COUNTER=COUNTER+1 echo "Working......" sleep 1 if [ $COUNTER -eq 20 ]; then echo echo "I was unable to stop the current Oracle instance" echo "Please try to stop it yourself and then try again." echo PROCESS_TO_KILL=$( ps ax | grep ora_...._${ORACLE_SID} | awk ' { FS = " " ; print $1 } ' ); for processes in $( echo $PROCESS_TO_KILL ) do echo "The following processes appear to be running." echo $processes done fi done for file in `echo ${FILES}` do if [ -e $file ]; then echo Removing: "$file" rm -rf $file echo fi done if [ -f $INITORACLE_SID ]; then rm -f $INITORACLE_SID fi touch $INITORACLE_SID cat > $INITORACLE_SID<<ENDOFINITORACLE_SID ############################################################################## # HARRY JACKSON ############################################################################## ########################################### # Cache and I/O ########################################### db_block_size=8192 db_cache_size=33554432 db_file_multiblock_read_count=16 db_files = 80 ########################################### # Cursors and Library Cache ########################################### open_cursors=300 ########################################### # Database Identification ########################################### db_domain="" db_name=${ORACLE_SID} ########################################### # Diagnostics and Statistics ########################################### background_dump_dest=$BACKGROUND_DUM_DEST core_dump_dest=$CORE_DUMP_DEST timed_statistics=TRUE user_dump_dest=$USER_DUMP_DEST ########################################### # File Configuration ########################################### control_files=("$CONTROL1", "$CONTROL2", "$CONTROL3") ########################################### # Instance Identification ########################################### instance_name=${ORACLE_SID} ########################################### # Job Queues ########################################### job_queue_processes=10 ########################################### # MTS ########################################### dispatchers="(ADDRESS=(PROTOCOL=TCP) (SERVICE=${ORACLE_SID}lXDB))(DISPATCHERS=2)" max_dispatchers=2 # How many servers to start at startup. I am not expecting much usage so # 1 should be fine. shared_servers=1 # Allow 1 shered server for every ten connections max_shared_servers=200 # I have only put this here so that I can use it. shared_server_sessions=2000 ########################################### # Miscellaneous ########################################### aq_tm_processes=1 compatible=9.2.0 ########################################### # Optimizer ########################################### hash_join_enabled=TRUE query_rewrite_enabled=FALSE star_transformation_enabled=FALSE ########################################### # Pools ########################################### java_pool_size=104857600 # large_pool_size=16777216 # shared_pool_size=104857600 # ########################################### # Processes and Sessions ########################################### processes=150 log_checkpoint_interval = 10000 log_buffer = 32768 parallel_max_servers = 5 ########################################### # Redo Log and Recovery ########################################### # We set the archive destinations to three seperate disks log_archive_destination_1 = 'LOCATION=${DATA}/${DEV_HDB05}/${REDO_LOG_ARCHIVE01}' log_archive_destination_2 = 'LOCATION=${DATA}/${DEV_HDD01}/${REDO_LOG_ARCHIVE02}' log_archive_destination_3 = 'LOCATION=${DATA}/${DEV_SDA03}/${REDO_LOG_ARCHIVE03}' # This setting stipulates how many archive destinations that # must be writable for Oracle to continue archiving. log_archive_min_succeed_dest=2 # This set the name of the archive file %t is the thread and %s is the log number log_archive_format=arch%t_%s.arc # This starts the archiving on starting the database. log_archive_start=true # With this set Oracle will write a checksum to the header of the online redo log # when it gets archived it will then check the two checksums to ensure that there # has been no corruption db_block_checksum=true fast_start_mttr_target=300 ########################################### # Security and Auditing ########################################### remote_login_passwordfile=EXCLUSIVE ########################################### # Sort, Hash Joins, Bitmap Indexes ########################################### pga_aggregate_target=25165824 sort_area_size=524288 ########################################### # System Managed Undo and Rollback Segments ########################################### undo_management=AUTO undo_retention=10800 #undo_tablespace=UNDOTBS1 ENDOFINITORACLE_SID sqlplus -s /nolog<<CREATE_DATABASE connect / as sysdba startup nomount CREATE DATABASE $ORACLE_SID USER SYS IDENTIFIED BY testing USER SYSTEM IDENTIFIED BY testing LOGFILE GROUP 1 ('$REDO01a', '$REDO01b', '$REDO01c') SIZE $REDO_SIZE, GROUP 2 ('$REDO02a', '$REDO02b', '$REDO02c') SIZE $REDO_SIZE, GROUP 3 ('$REDO03a', '$REDO03b', '$REDO03c') SIZE $REDO_SIZE MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 -- DATAFILE '$SYSTEM_FILE' SIZE $SYSTEM_FILE_SIZE REUSE EXTENT MANAGEMENT LOCAL -- DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '$TEMP_FILE' SIZE $TEMP_FILE_SIZE REUSE -- UNDO TABLESPACE undotbs DATAFILE '$UNDOTBS_FILE' SIZE $UNDOTBS_FILE_SIZE REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED; CREATE_DATABASE sqlplus -s /nolog<<CREATE_TABLESPACE connect / as sysdba CREATE TABLESPACE users LOGGING DATAFILE '$USERS_FILE' SIZE $USERS_FILE_SIZE REUSE AUTOEXTEND ON NEXT 4000K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; -- create a tablespace for indexes, separate from user tablespace CREATE TABLESPACE indx LOGGING DATAFILE '$INDEX_FILE' SIZE $INDEX_FILE_SIZW REUSE AUTOEXTEND ON NEXT 4000K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; CREATE TABLESPACE main LOGGING DATAFILE '$MAIN_FILE' SIZE $MAIN_FILE_SIZE REUSE AUTOEXTEND ON NEXT 4000K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; CREATE_TABLESPACE sqlplus -s /nolog<<CREATE_DICTIONARY connect / as sysdba @$CATALOG_SCRIPT; @$CATPROC_SCRIPT; CREATE_DICTIONARY # You will note here that the rman user is going to be # using a recovery catalog contained in the database just created. # This is not recommended its just that I have limited resources # on this machine. sqlplus -s /nolog<<CREATE_RMAN_USER connect / as sysdba CREATE TABLESPACE rman_tablespace DATAFILE '/opt/oracle/oradata/rman_tablespace01.dbf' SIZE 50M AUTOEXTEND ON NEXT 4000K; CREATE USER rman IDENTIFIED BY rman DEFAULT TABLESPACE rman_tablespace QUOTA UNLIMITED ON rman_tablespace; GRANT CONNECT, RECOVERY_CATALOG_OWNER, SYSDBA TO rman; CREATE_RMAN_USER sqlplus -s /nolog<<POST_CREATIONS connect / as sysdba ALTER DATABASE FORCE LOGGING; POST_CREATIONS # There are a lot more thinga that need to be done now. We could for instance # create our recovery catalog. /opt/oracle/product/9.2.0/bin/rman RCVCAT rman/rman@mynewdb.world<<RMAN_CREATE_CATALOG CREATE CATALOG; RMAN_CREATE_CATALOG