Datacentre Support Reference Guides

Visit our online shop for ALL your parts, components, laptops, electronics and gadgets
 

Oracle: Instance creation checklist

 
 
 

For all of you people out there that are really dbas, forgive me. This is a work in progress. I'm currently reading the Oracle 8i Handbook and I need a place to stash all the things they tell me to do when creating databases. Hopefully, once I'm done, this'll be a pretty complete document. In the mean time, it's what it is.

Later - much later

Well, studying has progressed. I'm to the point where I'm verifying/troubleshooting the checklist. If anyone has suggestions/comments/concerns, please send 'em. Please realize that I built this checklist on a Pentium 200 with one disk drive, so the details below aren't what would match a production environment. It's not the sizes or the specific environment that I'm after - more the steps that should be taken for most database creation exercises.

  1. Install Oracle
  2. Create instance
    1. Create the appropriate directory structure for your environment. Using the Oracle installed defaults, I have: 
      ls -ld /oracle/admin/${ORACLE_SID}/*

drwxr-xr-x   2 oracle   oinstall     1024 Mar 20 20:49 /oracle/admin/BITE/adhoc/

drwxr-xr-x   2 oracle   oinstall     1024 Mar 20 20:49 /oracle/admin/BITE/arch/

drwxr-xr-x   2 oracle   oinstall     1024 Mar 21 12:39 /oracle/admin/BITE/bdump/

drwxr-xr-x   2 oracle   oinstall     1024 Mar 20 20:49 /oracle/admin/BITE/cdump/

drwxr-xr-x   2 oracle   oinstall     1024 Mar 21 12:39 /oracle/admin/BITE/create/

drwxr-xr-x   2 oracle   oinstall     1024 Mar 20 20:49 /oracle/admin/BITE/exp/

drwxr-xr-x   2 oracle   oinstall     1024 Mar 21 12:36 /oracle/admin/BITE/pfile/

drwxr-xr-x   2 oracle   oinstall     1024 Mar 20 20:49 /oracle/admin/BITE/udump/
    2. Update the user's environment (Oracle?). Either .kshrc, .bashrc, or .cshrc 
      egrep -i "ora|nls" ~/.bashrc

ORACLE_HOME="/oracle/product/8.1.5"

ORACLE_BASE="/oracle"

ORACLE_SID=BITE

ORACLE_TERM=vt100

LD_LIBRARY_PATH=${ORACLE_HOME}/lib

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

NLS_LANG="american"

export SQLPATH=${HOME}:${HOME}/orabin

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

export ORACLE_HOME ORACLE_BASE ORACLE_SID ORACLE_TERM

export LD_LIBRARY_PATH ORA_NLS33 NLS_LANG
    3. Copy an init.ora file to the OS default file location. Edit it as appropriate.
    4. Copy, create, edit, or otherwise enter manually, the create database statement 
      create database "BITE"

        maxdatafiles 254

        maxlogfiles 32

        character set US7ASCII

        national character set US7ASCII

datafile '/oracle/oradata/bite/system01.dbf' size 200m

logfile '/oracle/oradata/bite/redo01.log' size 500k,

        '/oracle/oradata/bite/redo02.log' size 500k,

        '/oracle/oradata/bite/redo03.log' size 500k,

        '/oracle/oradata/bite/redo04.log' size 500k
  3. Run cat scripts:
    1. ${ORACLE_HOME}/rdbms/admin/catalog.sql
    2. ${ORACLE_HOME}/rdbms/admin/catproc.sql
    3. ${ORACLE_HOME}/rdbms/admin/catexp.sql
    4. catdbsyn.sql (Oracle 7.3 and lower): Creates the data dictionary views
    5. ${ORACLE_HOME}/sqlplus/admin/pupbld.sql
      1. Oracle 8.05 and lower
      2. Creates product_user_profile table & prevents annoying errors.
      3. Except, I had to run it on my Linux 8i (8.1.5) box.
    6. helpins.sql (Oracle 8.05 and lower): Creates and populates help utility.

  4. Create second system based rollback segment:

    create rollback segment r0
    tablespace system;
    alter rollback segment r0 online;

  5. Create tablespaces (Obviously you should size them per your needs)
    1. Rollback segment tablespace: 
      create tablespace rbs

datafile '/oracle/oradata/bite/rbs01.dbf' size 10m

default storage (initial 128k next 128k pctincrease 0 maxextents unlimited)
    2. Index tablespace: 
      create tablespace indexes

datafile '/oracle/oradata/bite/index01.dbf' size 30m

default storage (initial 50k next 50k pctincrease 0 maxextents unlimited);
    3. Temporary tablespace: 
      create tablespace temp

datafile '/oracle/oradata/bite/temp01.dbf' size 10m

temporary

default storage (initial 256k next 256k pctincrease 0 maxextents unlimited)
    4. Users tablespace: 
      create tablespace users

datafile '/oracle/oradata/bite/users01.dbf' size 50m

default storage (initial 50k next 50k pctincrease 0 maxextents unlimited)
    5. Other tablespaces as local and OFA standards dictate.

  6. Create additional rollback segments in RBS tablespace
    1. Commands: 
      

create rollback segment r1

tablespace RBS;

alter rollback segment r1 online;



create rollback segment r2

tablespace RBS;

alter rollback segment r2 online;



create rollback segment r3

tablespace RBS;

alter rollback segment r3 online;



create rollback segment r4

tablespace RBS;

alter rollback segment r4 online;
    2. Edit init.ora file to add private rollback segments. 
      rollback_segments = (r1, r2, r3, r4)
  7. Deactivate the second system based rbs.

    alter rollback segment r0 offline;

  8. Change sys/system passwords
    1. sys
    2. system
    3. outln
    4. dbsnmp

  9. Alter system's default tablespace

    alter user system 

   quota 0 on system

   temporary tablespace TEMP;

alter user sys temporary tablespace TEMP;
  10. Create example tables, if desired.
    1. ${ORACLE_HOME}/sqlplus/admin/pupbld.sql
    2. Lock the user accounts if not actively using them. 
      set heading off

spool /tmp/biteme.sql

select 'alter user '||username||' account lock;'

from dba_users

where username in ('ADAMS','BLAKE','CLARK','JONES','SCOTT');

spool off

@/tmp/biteme
  11. Create users
    1. Constraints
      1. Default tablespace = USERS
      2. Default temporary tablespace = TEMP
    2. Example: 
      create user dkoleary

identified by ${passwd}

default tablespace users

quota unlimited on users

temporary tablespace temp

quota unlimited on temp;



grant resource, dba, connect to dkoleary;
  12. Update Net8 configuration.
  13. Create/load database tables as needed.