Setting Environment

Steps to Create a Database There are five steps to creating a useable database: three steps to create a database and tow additional steps to make it usable. 1. Set the operating system environment variables ORACLE_HOME, ORACLE_SID, PATH, and LD_LIBRARY_PATH. 2. Edit/Create an initsid.ora parameter file. 3. Execute the CREATE DATABASE command in SQL*Plus. 4. Run the required scripts catalog.sql and catproc.sql.

5. Run the script pupbld.sql.

1. Setting Environment Before a database is created, the UNIX environment must be configured and the Oracle9i server must have already been installed. Four environment variables need to be set: ORACLE_HOME, ORACLE_SID, PATH. ORACLE_HOME is the full path to the top directory in which the Oracle9i Server is installed. The directory for ORACLE_HOME should be supplied by the person who installed the server, usually the Unix administrator or the DBA. ORACLE_SID is a user definable name assigned to an instance of a database. The ORACLE_SID

(System identifier) is used by the operating system to distinguish different database instances running on the machine. PATH defines the directories the operating system searches to find executables, such as SQL*Plus. The Oracle9i executables are located in $ORACLE_HOME/bin and needs to be added to the PATH variable. Example: $ ORACLE_HOME=oraeng/scc; export ORACLE_HOME $ ORACLE_SID=db01; export ORACLE_SID

$ PATH=/usr/bin:/usr/ccs/bin:$ORACLE_HOME/bin; export PATH

2. Editing initsid.ora Edit/Create initsid.ora

The initsid.ora file, a user configured text file, is read each time the database starts. The parameters in the file initialize the database settings. The parameter settings in the initsid.ora file affect not only the database at startup but also how the database is created. When the Oracle9i server is installed a sample init.ora file is placed in $ORACLE_HOME/dbs. Keep this file as a backup and do not modify it, create a copy of the file containing the name of the ORACLE_SID. Example: $ cd $ORACLE_HOME/dbs

$ cp init.ora initdb01.ora

There are a few parameters that should be configured; these include db_name, control_files, background_dump_dest, user_dump_dest, core_dump_dest, and undo_management The parameters background_dump_dest, user_dump_dest, and core_dump_dest are set to the full path locations where trace files are to be placed: • core_dump_dest contains core dumps generated by the database • user_dump_dest contains user trace files • background_dump_dest contains trace files for the background processes and the alert.log. The db_name is the name of the database, which is used for a different purpose than ORACLE_SID. The ORACLE_SID is the name used to designate an instance of the database. Many times the db_name and ORACLE_SID are the same but it is not required. The control_files initialization parameter designates the full path and filename of each controlfile for the database. For the creation of the database, it identifies the controlfiles that need to be created. The undo_management initialization parameter determines whether the Oracle server automatically or the DBA manually handles undo data. Set undo_management to AUTO in the initialization file.

3. Creating the Database After setting the environment and configuring the initsid.ora the database can be created. An Oracle database is created by executing a CREATE DATABASE command. The CREATE DATABASE command, specifies the number and location of the logfiles, the location and size of the SYSTEM tablespace, UNDO tablespace, and TEMP tablespace

4. Running Scripts The scripts catalog.sql and catproc.sql, located in $ORACLE_HOME/rdbms/admin, must be

run after the database is created. The script catalog.sql creates the data dictionary views and catproc.sql creates the packages and procedures required to use PL/SQL.

5. Running pupbld.sql The script pupbld.sql, located in directory $ORACLE_HOME/sqlplus/admin, creates the Product User Profile table and related procedures. Running this script among other purposes, prevents a warning message each time a user connects to SQL*Plus. The script must be run as user SYSTEM