OracleSetup

RT on Oracle

RT 3.8.2 and newer are much easier to install on Oracle. 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.

Connection problems and TWO_TASK environment variable

Since RT 3.8.2 you don't need to set TWO_TASK or other environment variables.

However, 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";

If you see this problem on RT 3.8.2 or newer then please report your case with relevant details using MailingLists.

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) (remark: that didn't work for me, it does when set to AMERICAN_AMERICA.WE8MSWIN1252 which is also my database charset!, see also: faq.htm http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm)

If you see this problem on RT 3.8.2 or newer then please report your case with relevant details using MailingLists.

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

Since RT 3.8.3 you can drop DB by dropping RT's user with all his object in the database:

DROP USER rt_user CASCADE

For older versions of RT it can be trickier cuz 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!

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.