Links

Tidbits of Oracle goodness
The rule of Four
Concerning rollback segments, it is usually recommended that you follow the rule of four when planning your database. Take the max number of concurrent transactions and divide it by four. Round this number up to the nearest mulitple of 4. There should be a minimum of 8 rollback segments with a maximum of 50.

For example: concurrent transactions = 117
117 / 4 = 29.25 --> round up to 32 rollback segments

Why isn't my job running?
I found the answer on Jonathan Lewis's fantastic site, but I will give you the nutshell version. You submit a job using dbms_job.submit. You run it with dbms_job.run. Everything works great! You pat yourself on the back for escaping all those quotes correctly. The next morning you check on your database, and the job did not run! Aargh. Well, make sure that you have the job_queue_processes>0. Lewis has a great page on this here.

dbora replacement for Linux
Here is a dbora file that works with chkconfig to ease the startup and shutdown administration. This was originally taken from here but I tweaked it a bit.

#!/bin/sh
#
# dbora   This scripts starts and shuts down the oracle databas
#
# chkconfig: 345 99 10
# description: This script calls the dbstart script to start Oracle
#              and dbshut to stop it
# processname: oracle*
# config: /etc/oratab
#                                                                               
# Source function library.
. /etc/rc.d/init.d/functions

ORA_HOME=/u01/app/oracle/product/9.2.0
ORA_OWNER=oracle

case "$1" in

'start')
        if [ ! -f $ORA_HOME/bin/dbstart ]; then
           echo Sorry... the $ORA_HOME/bin/dbstart script is missing
           echo Cannot start Oracle
           exit
        fi
        su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
        ;;

'stop')
        if [ ! -f $ORA_HOME/bin/dbshut ]; then
           echo Sorry... the $ORA_HOME/bin/dbshut script is missing
           echo Cannot shutdown Oracle
           exit
        fi
        su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
        ;;
esac
Installing Oracle 9iAS (Release 2) on Redhat 7.3
As we all know, Oracle 9iAS(R2) is not certified (and therefore not supported) on RedHat 7.3 by Oracle. Even so, it was relatively painless to install with only 1 workaround. This has been documented on other sites.
  • Start the Install - All should go fine until the Linking phase
  • You will get an error;"Error in invoking target install of makefile $ORACLE_HOME/sqlplus/lib/ins_sqlplus.mk" - Do not respond to the message yet
  • Open up another shell
  • Verify that $ORACLE_HOME is set correctly (and not pointing to a DB home if you have one)
  • Edit $ORACLE_HOME/bin/genclntsh and remove the line that says LD_SELF_CONTAINED. (I have both made it's value an empty string and removed the line with identical results)
  • Execute the newly edited file (It creates $ORACLE_HOME/lib/libclntsh.so
  • Press retry on the error message
This works for the 'Lite' version as well as the Enterprise version

Installing 9i(9.2.0.1) on Redhat 7.3
This is pretty straight forward, with almost no problems. BUT, there is one...
Error in invoking target install of makefile /opt/oracle/product/9.2.0/ctx/lib/ins_ctx.mk

Edit $ORACLE_HOME/ctx/lib/env_ctx.mk and go to "INSO_LINK =", add a "$(LDLIBFLAG)dl" to the line and save it.
Press retry on the Install msgbox. (This was taken from OraFaq)

Change Archivelog mode on RAC
  • Shutdown all but one node
  • Change the CLUSTER_DATABASE parameter to FALSE for that sid:
    alter system set CLUSTER_DATABASE=FALSE scope=spfile sid='MySID';
  • Shutdown database
  • Startup mount database
  • alter database archivelog (or noarchivelog)
  • Change CLUSTER_DATABASE back to TRUE
  • Shutdown
  • Startup your nodes
Remember to take a new backup

Logon, logoff, startup, and shutdown triggers
Basic table and triggers I use to track logons and logoffs: 
create table events (
   eventuser varchar2(30), 
   event varchar2(20), 
   eventtime date default sysdate,
   notes varchar2(100)
) nologging tablespace tools
/
create or replace trigger tr_logon after logon on database
begin
   insert into events (eventuser,event) values (ora_login_user,ora_sysevent);
end;
/
create or replace trigger tr_logoff before logoff on database
begin
   insert into events (eventuser,event) values (ora_login_user,ora_sysevent);
end;
/
create or replace trigger tr_startup after startup on database
begin
   insert into events (eventuser,event) values (ora_login_user,ora_sysevent);
end;
/
create or replace trigger tr_shutdown before shutdown on database
begin
   insert into events (eventuser,event) values (ora_login_user,ora_sysevent);
end;
/