Setting Up the Database Environment

About Setting up the Database Environment

You need to set up the database environment before creating the database and upgrading it.

This task explains how to set up the database environment:

On UNIX
  1. We recommend that you create a shell script .cshrc when you use C-shell, or .profile when you use B-shell for each database to properly set all environment variables needed to access the database. Enter these commands:

% su - oracle

% cd /oracle

% vi .cshrc_<SID> | .profile_<SID>

Where <SID> is the ORACLE_SID, such as MYDB.

  1. Make sure your shell script (.cshrc_<SID>) contains the lines provided in this table:

    On UNIX

    umask 002

    setenv ORACLE_BASE /oracle

    setenv ORACLE_VER 10.2.0.2.0

    setenv ORACLE_HOME ${ORACLE_BASE}/${ORACLE_VER}

    setenv ORACLE_SID <SID>

    setenv LD_LIBRARY_PATH ${ORACLE_HOME}/lib: \

    ${ORACLE_HOME}/ctx/lib:${LD_LIBRARY_PATH}

    setenv PATH .:${ORACLE_HOME}/bin: \

    ${ORACLE_HOME}/ctx/bin:${PATH}

    setenv CLASSPATH ${ORACLE_HOME}/jlib: \

    ${ORACLE_HOME}/product/jlib:${CLASSPATH}

    setenv EDS_HOME /ds/R17/<aix_a>/resources/sdm/exploreDB/oracle

    setenv SQLLDRCMD ${ORACLE_HOME}/bin/sqlldr

    setenv SQLPLUSCMD ${ORACLE_HOME}/bin/sqlplus

    setenv EPC_DISABLED true

    setenv NLS_LANG <language>_<territory>.<charset>

    setenv ORA_NLS33 ${ORACLE_HOME}/ocommon/nls/admin/data

  1. For HP-UX, set SHLIB_PATH instead of LD_LIBRARY_PATH.

  2. For AIX, set LIBPATH instead of LD_LIBRARY_PATH. You must include /usr/lib:/lib in $LIBPATH, because these directories are not searched by default once $LIBPATH environment variable is set. An example for setting NLS_LANG is:

setenv NLS_LANG american_america.UTF8

  1. ORA_NLS33 environment variable is needed when creating a database with a storage character set other than US7ASCII. Else, it is optional.

  2. SQL*PLUS supports a site profile (glogin.sql), a SQL*PLUS command file created by the database administrator. SQL*PLUS executes this command file whenever a user starts SQL*PLUS, and SQL*PLUS establishes the oracle connection. The site profile allows the DBA to set up SQL*PLUS environment defaults for all users at a particular site.

  3. SQL*PLUS also supports a user profile (login.sql), executed after the site profile.

We recommend that you do not use SQL*PLUS site (glogin.sql) and user (login.sql) profiles with CES Database Server. These profiles might have SQL statements and SQL*PLUS commands. The output of these SQL statements, or commands might affect the output of the SQL*PLUS session opened by the CES Database Server, thus causing problems.

On Windows
  1. We recommend that you create a batch file to properly set all environment variables needed to access the database.

  2. Go to the drive where you have Oracle installed

    > cd \oracle

    Create a batch file with the entries mentioned in this table:

    On Windows
    set ORACLE_BASE=<DRIVE>:\Oracle

    set ORACLE_VER=10.2.0.2.0

    set ORACLE_HOME=%ORACLE_BASE%\%ORACLE_VER%

    set ORACLE_SID=<ORACLE_SID>

    set PATH=.;%PATH%

    set EDS_HOME=<drive>:\ds\R17\<aix_a>\resources\sdm\exploreDB\oracle

    set SQLLDRCMD=%ORACLE_HOME%\bin\sqlldr

    set SQLPLUSCMD=%ORACLE_HOME%\bin\sqlplus

    set EPC_DISABLED=true

    set NLS_LANG=<language>_<territory>.<charset>

    set ORA_NLS33=%ORACLE_HOME%\ocommon\nls\admin\data

An example for setting NLS_LANG is as follows:

set NLS_LANG=american_america.UTF8

ORA_NLS33 environment variable is optional if a database is created using the storage character set of US7ASCII. ORA_NLS33 environment variable is required if a database is not created using the storage character set of US7ASCII.

Distributing Strategies for Oracle Database Files

Distributing files across disk drives ensures optimum performance by separating files that Oracle needs to access parallely. This table recommends the data files to be located on disks when different numbers of disks are used.

Total Number of Disk Drives Disk Drive Number Database Files
5 1

users1.dbf, control1.ctl, system.dbf, tools1.dbf, redo1.dbf, redo2.dbf, redo3.dbf. redo4.dbf

2

cisdb1.dbf, control2.dbf

3

cisindex1.dbf, control3.ctl

4

undo1.dbf

5

arch/, bdump/, cdump/, udump/, temp1.dbf, cisdb_rot1.dbf

4 1

users1.dbf, control1.ctl, system.dbf, tools1.dbf, redo1.dbf, redo2.dbf, redo3.dbf, redo4.dbf

2

cisdb1.dbf, control2.dbf

3

cisindex1.dbf, control3.ctl

4

undo1.dbf, arch/, bdump/. cdump/, udump/, temp1.dbf,cisdb_rot1.dbf

3 1

users1.dbf, control1.dbf, system.dbf, tools1.dbf, redo1.dbf, redo2.dbf, redo3.dbf, redo4.dbf, cisdb_rot1.dbf

2

cisdb1.dbf, control2.ctl, temp1.dbf

3

cisindex1.dbf, control3.ctl, arch/, bdump/, cdump/, udump/

2 1

users1.dbf, control1.dbf, control2.dbf, system.dbf, tools1.dbf, redo1.dbf, redo2.dbf, redo3.dbf, redo4.dbf, undo1.dbf, cisindex1.dbf

2

cisdb1.dbf, control3.ctl, temp1.dbf, arch/, bdump/, cdump/, udump/, cisdb_rot1.dbf

The recommendations made in the table reflect the following basic considerations. This approach can be followed both for UNIX and Windows:

  • Spreading files across several physical disk drives reduces disk I/O contention.
  • Reducing disk I/O contention ensures maximum database throughput and benefit from oracle's read-ahead operations.
  • The more disk heads moving at one time, faster is the database fetch.
  • Index files should be on a separate physical disk drive from the database data files.
  • High-volume, high-activity files should be on separate high-speed physical disk drives.
  • The preceding approach is for UNIX systems, you can follow the same for Windows also.
  • When you install or create your database on UNIX systems, you create symbolic links that point from the /<ORACLE_DATA>/<ORACLE_SID>directory to the actual location of each file on separate physical drives.