Oracle Instance

Clapton

http://www.arikaplan.com/oracle/ari111897b.html http://www.devx.com/dbzone/Article/20713 http://www.pcs-computing.com/support/oracle_mkdb.html         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. Install Oracle Create instance 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/ 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 Copy an init.ora file to the OS default file location. Edit it as appropriate. 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 Run cat scripts: ${ORACLE_HOME}/rdbms/admin/catalog.sql ${ORACLE_HOME}/rdbms/admin/catproc.sql ${ORACLE_HOME}/rdbms/admin/catexp.sql catdbsyn.sql (Oracle 7.3 and lower): Creates the data dictionary views ${ORACLE_HOME}/sqlplus/admin/pupbld.sql Oracle 8.05 and lower Creates product_user_profile table & prevents annoying errors. Except, I had to run it on my Linux 8i (8.1.5) box. helpins.sql (Oracle 8.05 and lower): Creates and populates help utility. Create second system based rollback segment: create rollback segment r0 tablespace system; alter rollback segment r0 online; Create tablespaces (Obviously you should size them per your needs) Rollback segment tablespace: create tablespace rbs datafile '/oracle/oradata/bite/rbs01.dbf' size 10m default storage (initial 128k next 128k pctincrease 0 maxextents unlimited) Index tablespace: create tablespace indexes datafile '/oracle/oradata/bite/index01.dbf' size 30m default storage (initial 50k next 50k pctincrease 0 maxextents unlimited); Temporary tablespace: create tablespace temp datafile '/oracle/oradata/bite/temp01.dbf' size 10m temporary default storage (initial 256k next 256k pctincrease 0 maxextents unlimited) Users tablespace: create tablespace users datafile '/oracle/oradata/bite/users01.dbf' size 50m default storage (initial 50k next 50k pctincrease 0 maxextents unlimited) Other tablespaces as local and OFA standards dictate. Create additional rollback segments in RBS tablespace 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; Edit init.ora file to add private rollback segments. rollback_segments = (r1, r2, r3, r4) Deactivate the second system based rbs. alter rollback segment r0 offline; Change sys/system passwords sys system outln dbsnmp Alter system's default tablespace alter user system quota 0 on system temporary tablespace TEMP; alter user sys temporary tablespace TEMP; Create example tables, if desired. ${ORACLE_HOME}/sqlplus/admin/pupbld.sql 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 Create users Constraints Default tablespace = USERS Default temporary tablespace = TEMP 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; Update Net8 configuration. Create/load database tables as needed.