Forum OpenACS Q&A: Improvements for OpenACS Oracle install docs

Request notifications

I recently installed of both Oracle 8.1.7.4 and PostgreSQL 7.2 on my new Debian box, to test and develop OpenACS. I'm not going to address installing Postgres here (it's a lot simpler than installing Oracle - particularly if you use the Debian package like I did!), but I'll post all the notes I came up with while installing Oracle.

Hopefully somebody with the proper DocBook-XML-foo (Vinod?) will take a look at this and re-use some of it in OpenACS Oracle install docs. A lot of the things I'm going to mention below are just tweaks nothing critical. But there are a small number of very important omissions from the docs! Particularly:

  • UTF8
  • Complete precise info on how to handle the glibc 2.1 issue. (No, my info below is not complete, but it's a decent start..)
  • My Oracle install notes follow:

Collapse
Posted by Andrew Piskorski on

Installing Oracle 8.1.7:

External Links:

  • Getting the patchset to upgrade to Oracle 8.1.7.4
  • The OpenACS Oracle install doc (and an older version)
  • Bruno Mattarollo's Oracle 8.1.7 install guide, including an old work around for glibc 2.2 issue. Note: Do not convert from US-ASCII to UTF-8 as he suggests, instead create the database in UTF-8 in the first place.
  • aD's old Installing Oracle 8.1.6 doc (old broken link), with lots of old c. 2000 and 2001 (useful?) comments.
  • Janine Sisk's 2002/01/30 post 2002/01/30 post on how Oracle's glibc-2.1.3-stubs.tar.gz patch makes installing the compatability libs unnecessary.
  • TODO: You can't back out an Oracle patchset, you have to reinstall. So test if 8.1.7.4 has the INSO filter Intermedia problems Janine Sisk mentioned, then if so try 8.1.7.3 or 8.1.7.2 (which is the earliest with the import fix)

    Unless I mention otherwise, all my examples below are from installing Oracle 8.1.7 and upgrading to 8.1.7.4 on a Debian GNU/Linux 3.0 (Woody) system running kernel 2.4.18, using these files from Oracle:

        93909 Jun 12  2001 glibc-2.1.3-stubs.tar.gz
    549867520 Jun 13  2001 oracle-linux81701.tar
    134494438 May 27  2002 oracle8-p2376472_8174_LINUX.zip
    

    As I write this, the latest OpenACS Oracle install doc is rev. 1.8.2.2 2002/11/24, but it may not be up on openacs.org - get it from CVS. It is mostly fine, but leaves out a few very important things (e.g., UTF8!) and could stand tweaking in others. Here are my tweaks:

    glibc 2.1 vs. 2.2 issues:

    Oracle 8.1.7 was built linked against glibc 2.1, not the 2.2 that most Linux distributions now use. Debian 2.2 (potato) and (I think) Red Hat 6.2 were the last versions to use glibc 2.1. This causes us extra work, but basically is no longer that big a deal.

    You may see reference to Red Hat glibc compatability libs, and the Jave JRE 1.18. Ignore these, you don't need them, at least not anymore.

    Janine Sisk said here that (on Red Hat, which version?) export LD_ASSUME_KERNEL=2.2.5 is definitely necessary, without it, the Oracle installer never comes up. (She also had to edit $ORACLE_HOME/bin/genclntsh, but I did not.) On the other hand, Ola Hansson said here that at least on Debian, there is no need for the LD_ASSUME_KERNEL=2.2.5

    I did use LD_ASSUME_KERNEL=2.2.5 at all times throught the Oracle install and database creation process.

    After the Oracle install process, so far I have not used LD_ASSUME_KERNEL at all. Oracle and AOLserver both run with no LD_ASSUME_KERNEL set anywhere. The OpenACS 4.6 core installed without problems. However, I have not yet (2002/12/13) tried Itermedia or the OpenACS Site-Wide-Search package.

    Linux kernel parameters for Oracle:

    TODO: Investigate Linux kernel parameters for Oracle. E.g., Ola Hansson mentioned that "the default value for SHMMAX, 32Mb, worked but is probably not enough for a production worthy database." Also, pawprint.net has a bunch of info on Linux kernel parameters, as well as something about using 22 mount points (/ora8/u01 through /ora/u22) for an Oracle install that might be worth looking into.

    Oracle environment variables:

    I do not recomend putting the Oracle environment variable settings into ~oracle/.bash_profile. These are system-wide settings that all users need, and which you want defined in one central place. Instead, put them into "/etc/profile-oracle.sh", and also add ORA_OWNER=oracle there as well. Then source /etc/profile-oracle.sh from any other script that needs the Oracle environment variable settings. E.g., you probably want all user shells to get those environment variables, so add this to "/etc/profile":

    for script in  /etc/profile-oracle.sh  /etc/profile-postgres.sh
    do
      if [ -e "$script" ]
      then
        . "$script"
      fi
    done
    

    Here's a snapshot of my actual /etc/profile-oracle.sh script:

    #
    # $Id: oracle.html,v 1.32 2002/12/13 05:52:04 andy Exp $
    #
    # Single central place for setting all Oracle environment variables.
    # Source this from /etc/profile.
    #
    # See also:
    #   http://openacs.org/doc/oracle.html
    #   http://openacs.org/doc/openacs-4/oracle.html
    #   http://www.greenpeace.org/~bruno/oracle.html
    #
    # --atp@piskorski.com, 2002/11/28 02:36 EST
    
    # Note that ORACLE_HOME and ORACLE_SID must also be specified in a
    # different fashion in /etc/oratab:
    
    ORACLE_BASE=/ora8/m01/app/oracle
    ORACLE_HOME=$ORACLE_BASE/product/8.1.7
    ORACLE_SID=ora8
    export ORACLE_BASE ORACLE_HOME ORACLE_SID
    
    ORACLE_TERM=vt100
    ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
    ORA_OWNER="oracle"
    export ORACLE_TERM ORA_NLS33 ORA_OWNER
    
    # It SHOULD be necessary to include ctx/lib in LD_LIBRART_PATH and perhaps
    # PATH as well, for Intermedia to work - but in fact it is not - why?
    # --atp@piskorski.com, 2001/08/19 18:23 EDT
    
    PATH=$PATH:$ORACLE_HOME/bin
    #LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:/lib:/usr/lib
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
    export PATH LD_LIBRARY_PATH
    

    Creating Oracle users/groups on Debian:

    On Debian, you usually don't want to use useradd and groupadd like you would on Solaris or any other unix system. Instead, use adduser and addgroup, which have much more sensible defaults.

    TODO: Add exact examples for the Oracle install stuff. (It's not that important though, the given comm useradd and groupadd commands work ok.)

    Fix root.sh:

    The OpenACS doc mentions how Oracle's stock root.sh does not work - this is true. However, the changes in the fixed pawprint.net version are actually very simple:

    $ diff root.sh root-pawprint.sh 
    11c11
    < ORACLE_HOME=/ora8/m01/app/oracle/product/8.1.7
    ---
    > ORACLE_HOME=/oracle/u01/app/oracle/product/8.1.7
    102c102
    < RMF=/bin/rm -f
    ---
    > RMF="/bin/rm -f"
    132c132
    < ORACLE_HOME=/ora8/m01/app/oracle/product/8.1.7
    ---
    > ORACLE_HOME=/oracle/u01/app/oracle/product/8.1.7
    156c156
    < RUID=`/usr/bin/id|$AWK -F\( '{print $2}'|$AWK -F\) '{print $1}`
    ---
    > RUID=`/usr/bin/id|$AWK -F\( '{print $2}'|$AWK -F\) '{print $1}'`
    177c177
    < $ECHO "\nThe following environment variables are set as:"| $TEE -a $LOG
    ---
    > $ECHO "The following environment variables are set as:"| $TEE -a $LOG
    200c200
    <   $ECHO "\nCreating ${ORATAB} file..."| $TEE -a $LOG
    ---
    >   $ECHO "Creating ${ORATAB} file..."| $TEE -a $LOG
    

    So, just make those changes, but using your correct value for ORACLE_HOME, and run it.

    Install Oracle glibc 2.1 stubs:

    After installing the Oracle 8.1.7 software but before creating a database, install glibc-2.1.3-stubs.tar.gz per Oracle's instructions. AKA, as the Oracle user (and with LD_ASSUME_KERNEL=2.2.5 set), do:

    $ cd $ORACLE_HOME
    $ tar xvfz glibc-2.1.3-stubs.tar.gz
    $ ./setup_stubs.sh

    Upgrade to Oracle 8.1.7.4:

    Next, after installing the Oracle software but still before creating a database, install the 8.1.7.4 patchset per Oracle's instructions. (When I did my intial Oracle install, I did not install the Oracle Installer, so I had to run the Installer out of my Oracle tarball in order to install this patchset. You'd probably be better off to just install the Installer at the beginning.)

    Re-install the Oracle glibc 2.1 stubs:

    Then after upgrading to 8.1.7.4, install the glibc-2.1.3-stubs.tar.gz again. It probably is sufficient to just to run setup_stubs.sh again, but I untarred everything again first too. But Janine Sisk discovered you definitely need to do it after installing the 8.1.7.4 patchset.

    Running dbassist (use UTF8 !):

    VERY IMPORTANT! Run dbassit to prepare to create a database, per the OpenACS instructions. But at the screen where you enter "ora8" for the "Global Database Name", also click "Change Character Set" and select "UTF8". I don't know what the significance of "Character Set" vs. "National Character Set" is, so I just picked UTF8 for both.

    On the screen with the "Checkpoint Interval" and "Checkpoint Timeout", it might be useful to Enable Archive Log now - it would need to be investigated. However, I leave it off and switch to Archivelog mode later manually, after creating the database.

    When running my 8.1.7.4 dbassist, some of the default values are different than mentioned in Vinod's Oracle isntall doc (rev. 1.8.2.2 2002/11/24). E.g., my defaults were Processes 150, Block Size 8192, both larger than in the install doc, so I kept them as is.

    I told dbassist to put my sqlora8.sh script in: $ORACLE_BASE/product/8.1.7/assistants/dbca/install/

    Put Oracle config files under CVS:

    At this point (TODO: Actually, I did some of it earlier, before fixing the bad root.sh script.), I like to put all my Oracle config files under CVS, before I start editing them:

    $ sudo find $ORACLE_BASE -type d -exec chmod g+s {} \;

    You probably also want to change the permissions on a bunch of these files and directories give the dba group write access, but I didn't record exactly what chmod commands I used. TODO: Create little script to repeatably do the chmod's.

    I use a -m commit comment of "Initial version of Oracle config files, no changes yet." for each of these:

    $ cd $ORACLE_BASE/product/8.1.7/network/admin/
    $ cvs import ora8-hostname/product/8.1.7/network/admin Oracle ora8-initial
    
    $ cd $ORACLE_BASE/product/8.1.7/hs/admin/
    $ cvs import ora8-hostname/product/8.1.7/hs/admin Oracle ora8-initial
    
    $ cd $ORACLE_BASE/product/8.1.7/assistants/dbca/install/
    $ cvs import ora8-hostname/product/8.1.7/assistants/dbca/install Oracle ora8-initial
    
    $ cd $ORACLE_BASE/admin/ora8/pfile/
    $ cvs importora8-hostname/admin/ora8/pfile Oracle ora8-initial
    

    TODO: Some of the those files (e.g., namesdrp.sql, namesini.sql, namesupg.sql) have CVS Header tags in them, so we might want to delete the first "$" in order to preserve the tag info as it came from Oracle. Bother with this?

    Now use the non-invasive method [note: as of 2002/12/13, that link does not yet exist] to stick the appropriate CVS directories into the existing Oracle installation:

    # Do all of this as user oracle:
    
    $ export ORA_CVS_TMP=/tmp/ora-cvs
    $ mkdir $ORA_CVS_TMP
    # oracle must be in the dba group for this chgrp to work:
    $ chgrp dba $ORA_CVS_TMP
    $ chmod g+s $ORA_CVS_TMP
    $ cvs co -d $ORA_CVS_TMP ora8-koudelka
    $ find $ORA_CVS_TMP -name CVS -prune -o -type f -print | xargs rm
    
    $ ./cp-cvs-etc.tcl $ORA_CVS_TMP/product/8.1.7/network/admin/            $ORACLE_BASE/product/8.1.7/network/admin/
    $ ./cp-cvs-etc.tcl $ORA_CVS_TMP/product/8.1.7/hs/admin/                 $ORACLE_BASE/product/8.1.7/hs/admin/
    $ ./cp-cvs-etc.tcl $ORA_CVS_TMP/admin/ora8/pfile/                       $ORACLE_BASE/admin/ora8/pfile/
    $ ./cp-cvs-etc.tcl $ORA_CVS_TMP/product/8.1.7/assistants/dbca/install/  $ORACLE_BASE/product/8.1.7/assistants/dbca/install/
    
    $ rm -rf $ORA_CVS_TMP
    

    Now do the same for some other directories we missed:

    The $ORACLE_HOME/bin/ directory has both binary files and shell scripts, a few of which we will need to modify. So let's put the shell scripts (only) under CVS. While we're at it, we'll put the $ORACLE_HOME/root.sh script under CVS as well:

    $ mkdir /tmp/atp/ora-home
    $ cp -p $ORACLE_HOME/{root,README}* /tmp/atp/ora-home/
    $ cd /tmp/atp/ora-home/
    cvs import -m "Initial version of Oracle config files, no changes yet." ora8-koudelka/product/8.1.7/ Oracle ora8-initial
    
    $ mkdir /tmp/atp/ora-bin
    $ cp -p $ORACLE_BASE/product/8.1.7/bin/{dbassist,debugproxy,deployejb,deploync,dropjava,ejbdescriptor,elogin,emwebsite,gatekeeper,genagtsh,genautab,genclntsh,genclntsh.nostub,genclntst,idl2ir,idl2java,irep,java2idl,java2iiop,java2rmi_iiop,jpub,jrelink.sh,loadjava,migprep,modifyprops,ncomp,netasst,netca,oadj,oadutil,ociconv,odma,oemapp,oidadmin,ojspc,owm,publish,publish_816,relink,remove,remove_816,sess_sh,sess_sh_816,sqlj,statusnc,symfind,trcfmt,vbdebug,vbj,vbj_convert,vbjc} /tmp/atp/ora-bin/
    $ cp -p $ORACLE_BASE/product/8.1.7/bin/{echodo,demodrop,demobld,owhat,pupbld,gensyslib,coraenv,mergelib,extractlib,dbhome,dbshut,helpins,oraenv,gennfgt,dbstart,gennttab,oerr} /tmp/atp/ora-bin/
    $ cd /tmp/atp/ora-bin/
    $ cvs import -m "Initial versions of Oracle config files or scripts, no changes yet." ora8-koudelka/product/8.1.7/bin Oracle ora8-initial
    
    # Do all of this as user oracle:
    
    export ORA_CVS_TMP=/tmp/ora-cvs
    mkdir $ORA_CVS_TMP
    # oracle must be in the dba group for this chgrp to work:
    chgrp dba $ORA_CVS_TMP
    chmod g+s $ORA_CVS_TMP
    cvs co -d $ORA_CVS_TMP ora8-koudelka
    find $ORA_CVS_TMP -name CVS -prune -o -type f -print | xargs rm
    
    ./cp-cvs-etc.tcl $ORA_CVS_TMP/product/8.1.7  $ORACLE_BASE/product/8.1.7
    ./cp-cvs-etc.tcl $ORA_CVS_TMP/ora8-koudelka/product/8.1.7/bin  $ORACLE_BASE/ora8-koudelka/product/8.1.7/bin
    

    # as oracle:
    cd $ORACLE_HOME/assistants/dbca/install/
    ./sqlora8.sh
    

    Oracle startup/shutdown scripts

    Do not overwrite $ORACLE_HOME/dbstart with the OpenACS version. It contains these lines:

    # Modified 6/1/2000: Fixed bug determining version of Oracle by bquinn@arsdigita.com
    
    # beg mod for linux fix
    VERSION=8.1
    # end mod
    if [ "$VERSION" = "8.1" ] 
    

    However, the OpenACS version is in fact much different than the version provided by Oracle - lot's of random changes. Looks like version skew here, perhaps the OpenACS version is from Oracle 8.1.6 or something. The dbstart that comes with Oracle 8.1.7.4 for Linux works just fine.

    The OpenACS /etc/init.d/oracle8i script is mostly ok. I've seen other slightly different versions, with some improvements. E.g., fixing the bizarre no-indentation formatting, and adding this extra little check at the top:

      if [ ! -f $ORACLE_HOME/bin/lsnrctl -o ! -d "$ORACLE_HOME" ]
        then
        echo "Oracle Net8: cannot start"
        exit
      fi
    

    But most importantly, change it to use "/etc/profile-oracle.sh", and change all occurrences of "ORA_HOME" to "ORACLE_HOME".

    However, OpenACS's two separate startlsnr and stoplsnr scripts in /etc/init.d/ are silly and non-standard. Instead, use "/etc/init.d/oracle8i-net8", which is constructed like a real init.d script:

    TODO: Add link to script here.

    Of course for servers, create the appropriate /etc/rc*.d/ symlinks to make Oracle automatically startup, as the OpenACS docs say. However, for testing databases running on my desktop and the like, I prefer to set Oracle to shut down automatically, but to only start up manually. For the rc*.d scripts, I prefer to use S94 and K06, as they seem to have worked fine on a Solaris server I use with many, many different services running.

    Which means that on a Debian server I do:

    $ sudo /usr/sbin/update-rc.d oracle8i      defaults 94 06
    $ sudo /usr/sbin/update-rc.d oracle8i-net8 defaults 94 06
    

    (Note "defaults 94 06" should be exactly equivalent to: "start 94 2 3 4 5 . stop 06 0 1 6 .")

    While on a Debian desktop where Oracle is only used for testing I do:

    sudo /usr/sbin/update-rc.d oracle8i      stop 06 0 1 6 .
    sudo /usr/sbin/update-rc.d oracle8i-net8 stop 06 0 1 6 .
    

    As far as I know, it is ok to use the exact same S94 and K06 numbers for both Oracle and the Listener.

Collapse
Posted by Tom Jackson on

Wow, that is a lot of great advice! I also changed the startup/shutdown numbers for the listener/oracle. I used 98/02 because nothing except the webservers depend on oracle. I also changed the defaults from the original doc because sshd was quitting before oracle. If something keeps oracle from shutting down, you can't login and fix it!

Collapse
Posted by Andrew Piskorski on

Intermedia 8.1.7.4 Linux problems:

Janine Sisk mentioned INSO filter Intermedia problems with Oracle 8.1.7.4 (only, not earlier 8.1.7 releases) under Linux.

When I tried using the OpenACS Site-Wide-Search with the Static-Pages package, Intermedia clearly wasn't working right. In sqlplus, doing exec sws_service.rebuild_index; gave me:

/ora8/m01/app/oracle/product/8.1.7/ctx/bin/ctxhx: relocation error: /ora8/m01/app/oracle/product/8.1.7/ctx/lib/libsc_ut.so: undefined symbol: stat

Turns out, that problem is easy to reproduce from the command line. Just do:

$ $ORACLE_HOME/ctx/bin/ctxhx mydoc.pdf mydoc.html

Patch 2525701 (readme) for Base Bug 2037255 appears to fix this problem.

I decided to put the patch files from p2037255_8174_LINUX.zip into "$ORACLE_BASE/patches/2525701/" so I wouldn't lose track of them.

When I ran patch.sh per Oracle's instructions. I got some rather disurbing errors:

ls: /ora8/m01/app/oracle/product/8.1.7/bin/ctxhx: No such file or directory
expr: syntax error
./patch.sh: [: -gt: unary operator expected
./patch.sh: [: -eq: unary operator expected
./patch.sh: [: 38168: unary operator expected
but it basically seemed to work.

After that, the libsc_ut.so: undefined symbol: stat went away. But oddly, running $ORACLE_HOME/ctx/bin/ctxhx from the command line. And Site-Wide-Search now seems to be indexing my PDF files (from the OpenACS Static-Pages package) fine, but doesn't seem to be indexing any of my HTML files at all. TODO: Why? What's going on here? --atp@piskorski.com, 2002/12/16 16:16 EST

Collapse
Posted by Tom Jackson on

So what is the advantage of 8.1.7.4 over 8.1.7.1, since there is no data yet in the db, should I re-install?

Also, I botched the removal of a tablespace and .dbf file by not using the cascade option. Now how do I remove the old file?

Collapse
Posted by Andrew Piskorski on
Tom, there is a long list of bugs in 8.1.7.0 that 8.1.7.4 fixes. The one in particular that bit me was an import bug that basically makes it impossible to import to a different schema. See this thread.

The 8.1.7.4 patch will convert an existing database. That worked fine for us on Solaris, but on Linux I've only installed the patch before creating a database. Since you have no data in your db, I'd probably blow it away and create it again from scratch after doing the patch, but it should work to upgrade in place, too.

For removing a tablespace, see this post, and/or some (already outdated, naturally) Oracle docs I put on my website.

Collapse
Posted by Jerry Asher on
I found tonight with an OpenACS 4.6 install on Oracle 8.1.7.4 and aol3.3ad13 that unless I have LD_ASSUME_KERNEL=2.2.5 in the aol environment that the install fails when loading the acs-content-repository SQLJ oracle regexp code.
Collapse
Posted by Tom Jackson on

At least one other thread http://openacs.org/forums/message-view?message_id=62375 talks about this. Any process using oracle software requires this.

Has this bit of advice not made it into the install docs yet?

Collapse
Posted by Andrew Piskorski on
It does not appear to always be necessary. At least, last time I installed OpenACS on Linux (on Debian 3.0), I definitely set LD_ASSUME_KERNEL during the Oracle install but not at any other time. OpenACS 4.6.x seemed to install fine. I haven't specifically checked, but I wasn't aware of any errors like those Jerry reported.

I also vaguely recall various older posts saying that you should only need LD_ASSUME_KERNEL during Oracle install, not at AOLserver runtime. But, I don't really know. Sounds like there may be a difference between Linux distributions here. I also don't know if there are any drawbacks to setting LD_ASSUME_KERNEL when you don't need it, either.

Collapse
Posted by Vinod Kurup on
I'm also unaware of the drawbacks of LD_ASSUME_KERNEL, but it's definitely in the latest docs, both in the Oracle install section and in the nsd-oracle script which sets up environment variables.
Collapse
Posted by Don Baccus on
It depends on which kernel you're using, and since Debian traditionally lags RH etc it may be that it's old enough that the environment variable isn't needed.

But if you're using Oracle with an up-to-date Linux install the Oracle docs insist on your needing it.

Collapse
12: Updated Oracle notes (response to 1)
Posted by Andrew Piskorski on
FYI, I've finally merged more than two years worth of updates and additions (50 cvs commits) into my public Oracle docs. Enhancements include Oracle 9.2.0.4 install (Linux server, Windows client), SQL tuning, better rollback and ORA-01555 info, export/import and NLS_LANG, csscan and character set conversion, database links, etc.
Collapse
Posted by Eduardo Pérez on
Could anybody put this docs instead of the current oracle 8.1.7 documentation at:
http://openacs.org/doc/current/oracle.html
or at least add a link so people can find it easier instead looking through forums.
Or is anybody still using oracle 8.1.7?
Collapse
Posted by Jade Rubick on
If someone posts a patch in bug-tracker to the source xml files for the documentation, I'm sure it will get in pretty quickly.
Collapse
Posted by Torben Brosten on
The link is added, in cvs to HEAD and oacs-5-1. It will show when the xml is re-processed.