You're not currently signed in. Sign in.

To use RT with Oracle, you need a recent version 3 release of RT. See README.Oracle in the distribution for the main installation. In particular, the --with-db-database option should be set to a known SID in your local tns listener config.

If you have connection problems, you should make sure that Oracle is available from the required place via DBI, using a command like; 

TWO_TASK=FOO perl -MDBI -le 'DBI->connect("DBI:Oracle:", "rt3", "rt3")'

You will need to make sure that the TWO_TASK environment variable is set appropriately for RT. If you are running RT as an application server (eg, FastCGI mode), then in the script that you start RT with, export that environment variable as normal. Otherwise, if you are running it with mod_perl, then you can either set it in your httpd.conf; 

PerlSetEnv TWO_TASK  FOO

Or in etc/RT_SiteConfig.pm in your RT installation tree; 

$ENV{TWO_TASK}="LIVE";

Encoding problem with mod_perl2

If there is problem with accents on the web pages for text from the database, it might be needed to add the following environnement variables to apache startup script: 

export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
export NLS_NCHAR="AL32UTF8"

(for RedHat El5, it can be added to /etc/sysconfig/httpd which is sourced by /etc/init.d/httpd)

Debugging the schema deployment

If you have problems deploying the schema, you can run the deployment script manually; 

TWO_TASK=FOO perl sbin/rt-setup-database --dba=rt3 --dba-password=rt3 --action init

Unlike the other databases, dropping the tables to start again is a more complex operation than issuing "DROP DATABASE FOO". You need to individually drop all components. In the RT contrib directory (http://download.bestpractical.com/pub/rt/contrib/3.0/), there is a script called nuke-schema.pl which will do this for you.

Debugging Operational Problems

Oracle is designed to be a highly concurrent database, and as such AUTO_INCREMENT ID columns are shunned in favour of these things called "Sequences", which are ID generators that can serve multiple connections independantly without all that expensive IPC locking.

If you get insert problems when you stick tickets into a database (for instance), it is possible that your sequences are out of whack. In this case, run the fix-sequences.pl script in the contrib directory. This will have the side-effect of incrementing all of the sequences by 1, due to the way that Oracle sequences work.

To see the Oracle errors in the error log, it may be necessary to turn on DBI debugging, by setting DBI_TRACE=1 in the environment.

Resources

  • orafaq.com - One of the best places on the net to find Oracle info!

Raw Notes: Oracle Setup

This from the rt-users mailing list: 

From: 	Myers, Adam <AMyers@abacus-us.com>
To: 	rt-users@lists.bestpractical.com
Subject: 	RE: [rt-users] oracle setup
Date: 	Wed, 26 Oct 2005 08:58:08 -0600	
Craig,

I am running Oracle 9g. What I did was use the Oracle.schema file to
create the database. Then followed the instruction in the Oracle.README
file (all provided in the RT package).  Then run the configure command
with all the options for your database (make sure to use
--with-db-type=Oracle).  When running 'initialize-database', I commented
out line 136 that calls insert_schema() since I already created the
schema. The database should be initialized fine. The command I used for
this is: $ /pathto/rt/sbin/rt-setup-database --action init --dba
password --prompt-for-dba-password.

As for seeing 'Preferences' on the GUI, make sure the group of people
you want to have this option has the right 'ModifySelf'.  That will
toggle the 'Preferences' link.

** Adam

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Jesse
Vincent
Sent: Wednesday, October 26, 2005 7:15 AM
To: Craig Stevenson
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] oracle setup




On Tue, Oct 25, 2005 at 05:10:11PM -0500, Craig Stevenson wrote:
> Hi:
> 
> I have been working for a few days on getting RT installed on my
system.
> 
> My system: redhat, oracle 10g, latest perl, and latest apache
> 
> The install scripts seem to balk on the oracle stuff.
> --- I would appreciate any pointers incorporating Oracle to play with 
> RT ---
> 
> I can drop and schema using sqlplus.  I cannot run the 
> rt-setup-database on 'acl' or 'insert' option options.  I can do 
> partial inserts of the tables.

How does setting up the tables fail?  What happens if you create the
tables by hand?

Gap in Tickets Numbering

I've found in my RT setup (RT 3.4.5 & Fast CGI & Oracle 10g) that for some unknown reason there is sometimes a gap between the number of two tickets. If I restart the apache server, then there is a gap of 20 between the old ticket and the new one.

On my oracle server, I have changed all the sequences by replacing the 'CACHE 20' by 'NOCACHE' and now it is ok. Don't know why I have this, I have no problems with Oracle Sequences in my other softs.

Bruno K.

"About Oracle Sequence Gaps"

Oracle sequences retrieve numbers into memory on demand. If you are caching sequence numbers (e.g. cache 20), Oracle reads twenty numbers into memory at a time and hands them out on demand. When the cache is empty and another number is needed, another cache read is run.

Oracle never reuses sequence numbers (unless you wrap all the way around the sequence). Once requested, that number is gone. If a user gets a number and rolls back their transaction, the next sequence get reads a later number. Also, if you have numbers cached in memory and the instance stops (say, to take a backup), all cached numbers are also gone.

NOCACHE will reduce the chance of gaps, but not eliminate it.

Pinning the sequences into the SGA will also reduce this problem

- doug

Script to make sequences NOCACHE

Save the block below to a file ( AlterSequences.sql ) then execute it. It will select all the user Sequences and create a new script in a temp file with the syntax to make the sequences NOCACHE, then execute that temp script.

--- Cut Here AlterSequences.sql --- 

SET TERM      OFF 
-- TERM = ON will display on terminal screen (OFF = show in LOG only)

SET ECHO      OFF
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.

SET TRIMOUT   ON 
-- TRIMOUT = ON will remove trailing spaces from output

SET TRIMSPOOL ON 
-- TRIMSPOOL = ON will remove trailing spaces from spooled output

SET HEADING   OFF 
-- HEADING = OFF will hide column headings

SET FEEDBACK  OFF 
-- FEEDBACK = ON will count rows returned

SET PAUSE     OFF 
-- PAUSE = ON .. press return at end of each page

SET PAGESIZE  0   
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)

SET LINESIZE  132
-- LINESIZE = width of page (80 is typical)

SET VERIFY    OFF 
-- VERIFY = ON will show before and after substitution variables

-- Start spooling to a log file
SPOOL tempAlterSeq.sql

select 'ALTER SEQUENCE ' || sequence_name || ' NOCACHE;'
  from user_sequences;

SPOOL OFF
@tempAlterSeq.sql
EXIT

--- End Cut ---

How to run that script? If you are running Oracle XE on Ubuntu, the setup would be something like: 

sudo su oracle -
. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh 
# consider adding that line to the oracle users ~/.bashrc file
touch AlterSequences.sql
nano AlterSequences.sql
# Paste in the script from above
# Save, Exit
sqlplus rt @ AlterSequences.sql
# Enter your RT password

Done.

Clean up tempAlterSeq.sql after a successful run.