Oracle

by Andrew Piskorski Last Revised: 18 March 2005

Internal Links (a rough Table of Contents):

External Links:

Contrary to what you might expect, our notes on installing Oracle are at the end of this document.

CVS:

Note that we have checked some of our Oracle config files into CVS. See our CVS documents, and also our Oracle install notes.

File Locations:

[TODO: These reference to example unix boxes, "Brain" and "Pinky", but the details will be different for your Oracle installation. Make the files listed here links to the real files on your own Oracle installation:]

Note: On Brain, $ORACLE_BASE is /ora8/m01/app/oracle, $ORACLE_HOME is /ora8/m01/app/oracle/product/8.1.7

Important Oracle Files - on Brain:

Alert Log and Trace Files: Config Files:

Important Oracle Files - on Pinky:

Alert Log and Trace Files: Config Files:

Database files, online redo logs, archived redo logs, by physical disk volume:

Queries to show current locations:

-- Show all tablespaces and their database files:
column file_name        format a60
column tablespace_name  format a15
select
  f.tablespace_name,  t.status
  ,f.file_name
from dba_data_files f, dba_tablespaces t
where t.tablespace_name = f.tablespace_name 
order by f.file_name, f.tablespace_name ;

-- Show all online redo logs:
column member  format a60
select group#, member  from v$logfile  order by member ;

-- Show all archived redo log destinations:
-- (Change in the init.ora file.)
column destination  format a60
select  dest_id, status, reopen_secs, destination
from v$archive_dest  order by destination ;

[TODO: The following is just an example, it will not be correct for your Oracle installation. Put your own info here:]

TODO: Warning: Much of the info below is very old and outdated. It may even date from when we were still running Oracle on Capital the ancient Solaris box: --atp@piskorski.com, 2004/11/29 12:38 EST

Files under /web :

Note that this is a symlink: /ora8 -> /web/ora8

/ora8/m01/app/oracle/

/ora8/m01/app/oracle/oradata/ora8/ /ora8/m02/oradata/ora8/

Files under /home :

/home/ora8/app/oracle/oradata/ora8/

Files under /mirror00/oracle-backups

Note that this is a symlink: /ora-backups -> /mirror00/oracle-backups

These files are created by /usr/local/adm/bin/ora-rman-hb.sh

Files under /web/oracle-backups

The export files are created by /usr/local/adm/bin/ora-export.sh

The arch files are pointed to in /web/ora8/m01/app/oracle/admin/ora8/pfile/initora8.ora and /usr/local/adm/bin/ora-rman-hb.sh

Files under /mirror01 (tablespaces)

Oracle tablespace files are put here, because mirror01 is not backed up to tape every night. In the event of failure, the Oracle database would be rebuilt from the Oracle export files and archived redo logs.

/mirror01/ora8/m12/oradata/ora8/

/mirror01/ora8/m04/ /mirror01/ora8/m02/oradata/ora8/

Oracle Support and Contacts:

Obtaining support:

To obtain support from Oracle, you will need a so-called CSI #. Ours (mysite) is [TODO: fill yours in].

The next step is to go to http://www.oracle.com/support/metalink/index.html and click the "register" link to set up an account with their system. Since we are under RSL's CSI#, the MetaLink registration pages will tell you to contact Alan Shein (see also below) at RSL to approve you; do so.

Once you have a MetaLink account, log in, and you can search their bug database, technical libraries and forums to try to obtain an answer to your question/problem. If this doesn't help, create a TAR for your problem. Even if you plan to call them, create the TAR first. They won't be too helpful on the phone unless you do this. In Don's one experience with this, he got an email response to my problem within a couple of hours of submitting the TAR. If you want to call them after creating the TAR, the number is 407-240-8900.

Over the last few years I have twice had my Metalink account deleted - with no warning - and been forced to re-register using a different username. Alan Shein says Metalink accounts are automatically de-activated somehow after a certain amount of non-use. (Which is bizarre, but apparently that's how Oracle set it up.) So log in periodically and do something with your account, or you'll have to create a new one.

Contacts:

[TODO: Add your DBAs, Oracle sales rep, or etc.]

Starting and Connecting to Different Oracle Instances:

An Oracle "SID", (e.g., "ora8") is a name for a single Oracle instance. We normally have /etc/profile source /etc/profile-oracle.sh and set the ORACLE_SID environment variable to point to the default instance on the machine.

If you want to connect to a different instance with sqlplus, you can use the "@" syntax, e.g.:

$ sqlplus user@ora8b
However, that will work only if the Oracle listener is running, and only if the instance is already running.

So, what if the listener isn't running or isn't configured properly? And how do you manualy start up multiple Oracle instances? Basically, how do you control what Oracle instance you connect to when you start sqlplus or svrmgrl? The best and most reliable way I know of is to set ORACLE_SID on the command line like so: 

$ ORACLE_SID=ora8b sqlplus

$ ORACLE_SID=ora8c svrmgrl
Note that there is no semicolon in there, so the new ORACLE_SID will be set only for the running sqlplus process, not in the shell from which you started sqlplus.

Creating a new Oracle User and Tablespace:

Here's how we create a new Oracle 8i user (plus a matching tablespace and other stuff), for use with ACS or whatever else: 

$ svrmgrl
SVRMGR> connect internal
Connected.

SVRMGR> create tablespace NAME
datafile '/ora8/m02/oradata/ora8/NAME01.dbf'
size 50M  autoextend on  next 10M  maxsize 500M
extent management local
--uniform size 1M
autoallocate ;

SVRMGR> create user NAME identified by PASSWORD
          default tablespace NAME temporary tablespace tmp
          quota unlimited on NAME ;
SVRMGR> grant connect, resource, ctxapp, javasyspriv, query rewrite to NAME ;
SVRMGR> grant create materialized view to NAME ;
SVRMGR> grant select_catalog_role to NAME;
SVRMGR> revoke unlimited tablespace from NAME ;
SVRMGR> alter user NAME quota unlimited on NAME ;

SVRMGR> exit

And here's a virtually identical example, which I used in Oracle 9.2.0.1: 

$ sqlplus '/as sysdba'

create tablespace NAME
  datafile '/ora9/m02/oradata/ora9/NAME_01.dbf'
  size 200M  autoextend on  next 200M  maxsize 2000M
  extent management local
  --uniform size 1M
  autoallocate ;

create user NAME identified by PASSWORD
  default tablespace NAME temporary tablespace temp
  quota unlimited on NAME ;
grant connect, resource, ctxapp, javasyspriv, query rewrite to NAME ;
grant create materialized view to NAME ;
grant select_catalog_role to NAME;
revoke unlimited tablespace from NAME ;
alter user NAME quota unlimited on NAME ;

alter tablespace NAME add datafile
  '/ora9/m02/oradata/ora9/NAME_02.dbf'
  size 200M  autoextend on  next 200M  maxsize 2000M ;

Note that in the old days, the ACS install docs used to suggest creating the tablespace like below. Don't do this, because locally managed tablespaces are better: 

SVRMGR> create tablespace NAME datafile
          '/ora8/m02/oradata/ora8/NAME01.dbf' size 50m autoextend on
          default storage (pctincrease 1) ;

A note on locally managed extent size: The default is 1 MB. You probably only want something small like 64k for special purposes, like the TEMP tablespace. In theory, you should really have three different tablespaces, one each for small, medium, and large objects. But if you don't want to bother with that and aren't worried about space fragmentation, just use autoallocate instead.

This OpenACS thread also talks more about Oracle tablespaces.

TODO: You have to decide what to use for the extent size - 64k isn't necessarily the best choice. Put info here on how to decide on the size, or whether to use autoallocate

Deleting an Oracle User or All Objects in a User's Schema:

Delete an Oracle User:

If for some reason you want to drop an Oracle user, for example, as a convenient way to totally delete all the tables and everything else in that user's schema, simply do:

SVRMGR> drop user NAME cascade;
Then you can do create user, etc. again, as above.

Don't Delete a User, Just Delete His Objects:

Most of the time, (e.g., when importing a Production database to a Dev user) you don't really want to delet the user, you just want to delete everything that user owns so you can re-populate his schema from scratch. If you delete and re-create the user, you will lose all persmissions and other settings - so you don't want to do that.

There are many little scripts out on the net to help do this, e.g.: 1, 2, 3, 4, 5, 6.

But actually, it turns out this is very easy. This query will give you a list of drop commands which you can then paste into sqlplus: 

set linesize 120
set pagesize 1000
select 'drop ' || object_type || ' ' || object_name
  || decode(object_type,
       'CLUSTER', ' including tables cascade constraints;',
       'TABLE', ' cascade constraints;',  ';')
from user_objects
where object_type in (
  'CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM', 'FUNCTION',
  'PROCEDURE', 'PACKAGE'
);
We have a script to do this for you. To drop all objects in a schema, just log into sqlplus as that Oracle user, and do:
SQL> @drop-all.sql
Just in case, that script only allows you to drop the objects if the Oracle user's name ends in either 'dev' or 'stag'. AKA, you probably only want to run that script as a Development or Staging Oracle user, never ever as Production.

Alternately, you could drop the user but first auto-generate a script to re-create the new user's permissions and other sessions exactly the same as the old user's. That might be usefull in some circumstances. TODO: I saw a script to do this somewhere, but I couldn't find it again.

Dropping a Tablespace:

Dropping a tablespace is straightforward, but if it has grown very large, with a large number of extents (anything over 1000 or so, up through hundreds of thousands), it may take a long time. See this thread on dropping a huge TEMP tablespace for more detail.

There are other sequences of commands that probably work ok, but these should be safest/best. To drop tablespace FOO, which happens to use only one datafile named FOO_01.dbf, do: 

SVRMGR> alter tablespace FOO offline normal ;
SVRMGR> alter database datafile '/ora8/m02/oradata/ora8/FOO_01.dbf' offline;
SVRMGR> alter database datafile '/ora8/m02/oradata/ora8/FOO_01.dbf' offline drop;
SVRMGR> drop tablespace FOO including contents;
$ rm /ora8/m02/oradata/ora8/FOO_01.dbf
Be very, very careful you delete the right file!

Sizing Your TEMP Tablespace:

You should create a locally managed temporary tablespace, not a dictionary managed tablespace. The dropping a huge TEMP tablespace OpenACS thead mentioned above has links to additional useful information.

Here's how we initially created ours: 

create temporary tablespace TMP
tempfile '/ora8/m01/app/oracle/oradata/ora8/tmp01.dbf'
size 100M  autoextend on  next 10M  maxsize 500M
extent management local
-- size should be same as your sort_area_size init param
uniform size 64K ;
As our database grew, it turned out that the 500 MB max size for TMP wasn't enough. We'd consistently get "ORA-01652: unable to extend temp segment by 16 in tablespace TMP" errors when running dbms_stats.gather_schema_stats.

To increase the size of the existing tempfile (rather than adding a second one), we did: 

alter database tempfile '/ora8/m01/app/oracle/oradata/ora8/tmp01.dbf'
  resize 1000M;
alter database tempfile '/ora8/m01/app/oracle/oradata/ora8/tmp01.dbf'
  autoextend on  next 200M  maxsize 2000M ;

Adding More Rollback Space:

If you get "ORA-01555: snapshot too old" errors while doing long-running queries (selects, not updates), then you probably need to increase the size of all your rollback segments. There's more to it than that and there are other reasons for getting ORA-01555, and you should read about it first, but basically, remember that rollback segments are extended only by writes (inserts, updates, deletes) may extend the size of a rollback segment, but the segment will never extend because some other select transaction needs its rollback data. Instead, the select will fail with ORA-01555.

These Ask Tom articles are particularly good: one, two, three. Metalink note 62005.1 "Creating, Optimizing, and Understanding Rollback Segments" is also useful.

The way Oracle rollback works is particularly non-intuitive (and due to Oracle's overwriting storage manager is much more complicated than the equivalent in PostgreSQL with its non-overwriting storage manager), but the key point you must understand is, as Tom Kyte says:

"The probability of an ORA-1555 is directly proportional to the size of your smallest rbs."
Tom Kyte's Expert One-on-One Oracle book has a clear explanation of this in "Chapter 5: Redo and Rollback". (Page 187 in the 2001 Wrox Press printing.)

Roughly what happens is Oracle process #2 starts modifying stuff, and generating rollback. It doesn't need the rollback, only other processes need it. Say it generates 50 MB of rollback. If allowed to, it will happily extend a 4 MB rollback segment to 50 MB. But once it commits, it is done with that rollback segment. If we told Oracle that the extent's "optimal" size is only 4 MB, Oracle will happily shrink it back down to 4 MB (sometime; I'm not sure when). Also, since #1 has committed, process #3 may come along and re-use that same extent, writing its rollback to it.

Now, we also have process #1, which started a really slow query two hours ago, long before #2 did anything. Thus, it needs the old data from before #2's modifications, but due to Oracle shrinking that segment back down to 4 MB, or due to some other process overwriting it, that rollback data is now gone. *BOOM*, process #1 fails with an "ORA-01555: snapshot too old" error.

Basically, rollback is generated by writers, but needed by readers, and Oracle does not know what rollback the readers actually need until they need it. Oracle cannot actively preserve the rollback that the readers need, so it the DBA needs to make sure that all the rollback segments are large enough that the rollback will happen to still be there when the readers need it. Failing to do that will result in ORA-01555 errors. (It is, however, not the only possible cause of ORA-01555 errors. There are two others, "fetching across commits" and "delayed block cleanout".)

First some queries I used to check things:

-- List all database files and their tablespaces:
select  file_name, tablespace_name, status
  ,bytes   /1000000  as MB
  ,maxbytes/1000000  as MB_max
from dba_data_files ;

-- List all tablespaces and some settings:
select  tablespace_name, status, contents, extent_management
  ,next_extent /1000000  as next_ext_MB
  ,min_extents  as min_ext
  ,max_extents  as max_ext
  ,pct_increase as pct_inc
from dba_tablespaces ;

-- List all rollback segments:
column segment_name     format a12
column tablespace_name  format a12
select 
   r.segment_name       
  ,r.status     
  ,s.extents
  ,s.bytes /1000000  as MB
  ,r.owner              
  ,r.tablespace_name
  ,r.initial_extent /1000000  as init_ext_MB
  ,r.next_extent    /1000000  as next_ext_MB
  ,r.min_extents  as min_ext
  ,r.max_extents  as max_ext
from dba_rollback_segs r, dba_segments s
where s.segment_type = 'ROLLBACK'
  and r.segment_name = s.segment_name 
  --and r.status = 'ONLINE'  and r.tablespace_name = 'RBS'
;

-- For each rollback segment, show average size and number of times
-- extended:
column name     format a12
select  n.name
  ,s.extents
  ,s.optsize /1000000  as optsize_MB
  ,s.extends
  ,s.aveactive /1000000  as aveact_MB
  ,s.aveshrink /1000000  as aveshr_MB
from v$rollstat s, v$rollname n
where n.usn = s.usn ;

-- What statements are executing in each rollback segment?
--   Trezzo pg. 477 ("Oracle PL/SQL Tips and Techniques", 1999, ISBN
--   0-07-882439-9.)  File: 9_23.sql
select a.name, b.xacts tr, c.sid, c.serial#, c.username, d.sql_text
from v$rollname a, v$rollstat b, v$session c, v$sqltext d, v$transaction e
where  a.usn            = b.usn
  and  b.usn            = e.xidusn
  and  c.taddr          = e.addr
  and  c.sql_address    = d.address
  and  c.sql_hash_value = d.hash_value
order by a.name, c.sid, d.piece ;

-- Misc.:
select segment_name, blocks  from dba_segments  where segment_type = 'ROLLBACK';
select sessions_highwater from v$license;
select class, count  from v$waitstat  where class like '%undo%';

Add More Rollback - March 2005 Example:

Oracle 8.1.7.4 is running on our Linux server. Once again, this instance seems to have been set up using mostly not-so-smart default values. Let's fix it.

TODO: Tom Kyte seems to recommend placing only one rollback segment in each tablespace, and thus managing rollback segment space via the tablespaces. I do not do that below, but we might want to consider it in the future. --atp@piskorski.com, 2005/03/18 22:32 EST

TODO: I created 10 rollback segments below. I believe this will be enough for our needs, but how to verify that after the fact, check for contention and etc.? --atp@piskorski.com, 2005/03/18 22:59 EST

Create the new rollback segments (March 2005):

create tablespace rbs2
datafile '/ora8/m01/app/oracle/oradata/ora8/rbs2_01.dbf'
size 200M  autoextend on  next 200M  maxsize 2000M
extent management local  uniform size 1M ;

alter tablespace rbs2 add datafile
  '/ora8/m01/app/oracle/oradata/ora8/rbs2_02.dbf'
  size 200M  autoextend on  next 200M  maxsize 2000M ;
alter tablespace rbs2 add datafile
  '/ora8/m01/app/oracle/oradata/ora8/rbs2_03.dbf'
  size 200M  autoextend on  next 200M  maxsize 2000M ;

-- Create 10 rollback segments 01 - 10, each like this:
create public rollback segment rbs2_01 tablespace rbs2 storage (
  INITIAL 5M  NEXT 5M  MINEXTENTS 2  MAXEXTENTS 800 );

-- Put all 10 online:
alter rollback segment rbs2_01 online;
...
alter rollback segment rbs2_10 online;

If while putting the new rollback segments online you got an error like "ORA-01599: failed to acquire rollback segment (39), cache space is full (currently has (36) entries)" don't worry, there's a (configurable) upper limit on how many rollback segments you can have online at once, and you hit hit. Just take some of the old rollback segments offline first (see below), and then finish putting the new ones online.

Drop the old rollback segments (March 2005):

Note that you can do everything here while Oracle is up and running. However, if you have a long running query using a rollback segment, that segment cannot be taken offline until the query finishes. Your e.g. "alter rollback segment rbs4 offline;" statement below will not fail, but the queries above will show that it is still online and in use. This is ok, just wait for it to complete. 

alter rollback segment rbs0  offline;
...
alter rollback segment rbs28 offline;

alter tablespace rbs offline normal ;
alter database datafile '/ora8/m01/app/oracle/oradata/ora8/rbs01.dbf' offline;
alter database datafile '/ora8/m01/app/oracle/oradata/ora8/rbs02.dbf' offline;
alter database datafile '/ora8/m01/app/oracle/oradata/ora8/rbs01.dbf' offline drop;
alter database datafile '/ora8/m01/app/oracle/oradata/ora8/rbs02.dbf' offline drop;

drop rollback segment rbs0 ;
...
drop rollback segment rbs28;

-- This may work, or may fail with "ORA-01549: tablespace not empty":
drop tablespace rbs;

-- Should have 0 tables in the tablespace:
select count(*)  from dba_all_tables  where tablespace_name = 'RBS' ;

-- Should have no objects in the tablespace:
column owner        format a20
column object_type  format a30
column object_name  format a40
select   o.owner  ,o.object_name  ,o.object_type
from sys_objects s  ,dba_objects o  ,dba_data_files df
where df.file_id = s.header_file
  and o.object_id = s.object_id
  and df.tablespace_name = 'RBS' ;

-- If above looks ok, go ahead and do this:
drop tablespace rbs including contents;

# Now delete the actual Unix files:
sudo rm /ora8/m01/app/oracle/oradata/ora8/rbs0[12].dbf

Add More Rollback - Nov. 2002 Example:

On our old Sun E250 server running Solaris and Oracle 8i, originally we had 29 rollback segments in the RBS dictionary managed tablespace. Each segment had an "optsize" of only aboue 4 MB, so they would always shrink back down to that size. Tues. 2002/11/05, I replaced these with 20 larger rollback segments. Here's how I did it:

Create the new rollback segments (Nov. 2002):

I decided to create 20 new 25 MB rollback segments, 10 in one tablespace and 10 in another, with space in each tablespace for the rollback segments to grow. Why two tablespace? No compelling reason, but at first I was thinking about putting one of them on a different disk, and even on the same disk, I figured it might be a bit easier to manage. 

create tablespace rbs2
datafile '/ora8/m01/app/oracle/oradata/ora8/rbs2_01.dbf'
-- Initial size is useable space + 64k for LMT overheard, so
-- 250*1024k +64k = size 256065k
size 256065k  autoextend on  next 1024k  maxsize 1024064k
extent management local  uniform size 1M ;

-- Create 10 rollback segments 01 - 10, each like this:
create public rollback segment rbs2_01 tablespace rbs2 storage (
  INITIAL 1M   NEXT 1M  OPTIMAL 100M
  MINEXTENTS 25  MAXEXTENTS 200
);

-- Put all 10 online:
alter rollback segment rbs2_01 online;
...
alter rollback segment rbs2_10 online;

Drop the old rollback segments (Nov. 2002):

alter rollback segment rbs0 offline;
...
alter rollback segment rbs25 offline;

alter tablespace rbs offline normal ;
alter database datafile '/ora8/m01/app/oracle/oradata/ora8/rbs01.dbf' offline;
alter database datafile '/ora8/m01/app/oracle/oradata/ora8/rbs01.dbf' offline drop;

drop rollback segment rbs0 ;
...
drop rollback segment rbs25;

-- This will probably fail with a "ORA-01549: tablespace not empty",
-- it's normal:
drop tablespace rbs;

-- Should have 0 tables in the tablespace:
select count(*)  from dba_all_tables  where tablespace_name = 'RBS' ;

-- Should have no objects in the tablespace:
column owner        format a20
column object_type  format a30
column object_name  format a40
select   o.owner  ,o.object_name  ,o.object_type
from sys_objects s  ,dba_objects o  ,dba_data_files df
where df.file_id = s.header_file
  and o.object_id = s.object_id
  and df.tablespace_name = 'RBS' ;

drop tablespace rbs including contents;
commit;

You're done!

Making existing rollback segments bigger (Nov. 2002):

Later, we saw that some of the rollback segments we created above had auto-extended to 55 MB or so. This means that in some cases, it will be possible to get an "ORA-01555: snapshot too old" error. To avoid this potential problem, you can simply enlarge all the rollback segments we created before:

TODO: When actually try this, verify that these instructions are correct: --atp@piskorski.com, 2002/12/05 11:45 EST

For each of our 20 rollback segments, do: 

alter rollback segment rbs1_01 storage (
  --INITIAL 1M   NEXT 1M  MINEXTENTS 25
  OPTIMAL 200M  MAXEXTENTS 300
);
alter rollback segment rbs1_01 shrink to 100 M;

Moving database files to a new location:

Moving any tablespace other than SYSTEM is pretty simple, but SYSTEM is trickier - Tom Kyte explains.

It is often more practical to simply place a unix symlink pointing from the old to the new database file locations. However, if you need or want to have Oracle actually know about the new real file locations, here are some examples of how I think we should move the non-system database files: TODO: Note that these commands are untested: --atp@piskorski.com, 2003/01/05 22:31 EST 

alter tablespace CAPITAL offline normal ;
sudo cp -p /ora8/m02/oradata/ora8/capital01.dbf /ora8/m04/oradata/ora8/
alter tablespace CAPITAL rename datafile
  '/ora8/m02/oradata/ora8/capital01.dbf' to
  '/ora8/m04/oradata/ora8/FOO_01.dbf' ;
alter tablespace CAPITAL online ;

alter tablespace DDR_PROD offline normal ;
sudo cp -p /ora8/m02/oradata/ora8/ddr_prod*.dbf /ora8/m04/oradata/ora8/
alter tablespace DDR_PROD rename datafile
  '/ora8/m02/oradata/ora8/ddr_prod_01.dbf' to
  '/ora8/m04/oradata/ora8/ddr_prod_01.dbf' ;
alter tablespace DDR_PROD rename datafile
  '/ora8/m02/oradata/ora8/ddr_prod_02.dbf' to
  '/ora8/m04/oradata/ora8/ddr_prod_02.dbf' ;
alter tablespace DDR_PROD online ;

alter tablespace DRSYS offline normal ;
sudo cp -p /ora8/m01/app/oracle/oradata/ora8/drsys01.dbf /ora8/m04/oradata/ora8/
alter tablespace DRSYS rename datafile
  '/ora8/m01/app/oracle/oradata/ora8/drsys01.dbf' to
  '/ora8/m04/oradata/ora8/drsys01.dbf' ;
alter tablespace DRSYS online ;

Export and Import (9i and 8i):

Be careful with character sets and NLS_LANG:

See also Metalink Note 227332.1, NLS considerations in Import/Export

The Export and Import utilities are standard Oracle client programs just like sqlplus or an OCI database driver, and thus are subject to all the exact same issues regarding character sets and the NLS_LANG environment variable!

Typically what you want to do, is always set NLS_LANG to the character set of the database you export from, for both the export and the import. That way, Oracle will automatically do any necessary character set conversion when importing into the new database, once, and will not do any conversions at all prior to that. So for example, if you're exporting from a AL32UTF8 database to a UTF8 database, (on Unix) do this both before running exp and before running imp: 

$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
Better yet, automaticaly set NLS_LANG to whatever the database is using. In a Bourne-shell script, this will do the job: 
NLS_LANG=`$ORACLE_HOME/bin/sqlplus -S $ORA_USERNAME/$ORA_PASSWORD <<EOS
set feed off linesize 500 pages 0
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
exit
EOS`
export NLS_LANG
TODO: Better to simply "unset NLS_LANG", thus making sure it is not set at all? --atp@piskorski.com, 2005/03/13 04:52 EST

Nightly Export:

We use our /usr/local/adm/bin/ora-export.sh script to do nightly exports. (TODO: Add a link to the actual script.) See also our Backups document.

Weekly Import from ddr_prod to ddr_dev:

We might want to also periodically import from ddr_prod to ddr_dev, so that we'll have a recent copy of the real Production data for developing with.

Note: When I wrote this section, we used our nightly ora-export.sh script to do full database exports. However, we've long since switched to doing a separate export for each Oracle user. --atp@piskorski.com, 2005/03/13 04:30 EST

Since we export the entire database every night, we might just want to use that full database export in order to import the ddr_prod stuff to ddr_dev. But since that export was done by the SYSTEM user, we would need to first do (only once, this is permanent):

grant IMP_FULL_DATABASE to ddr_dev ;
Using the nightly /web/oracle-backups/export/ora8-system-full-Sun.dmp.gz.* export does work and I've tried it. However, for simplicity, we'll just do a separate export of ddr_prod and then import from that, using this script:
/home/mysite/bin/ora-prod-to-dev.sh
It basically does these steps: 
SQL> @/home/mysite/sql/drop-all.sql
$ exp ddr_prod/PASSWORD consistent=Y owner=ddr_prod file=/tmp/ora-ddr-prod.dmp
$ imp ddr_dev/PASSWORD rows=y show=n ignore=n fromuser=ddr_prod touser=ddr_dev file=/tmp/ora-ddr-prod.dmp
SQL> exec dbms_stats.gather_schema_stats ('ddr_dev', 10, cascade => true)

(There is a trade off here. On the one hand, exporting ddr_prod takes extra time. But to use the nightly full export, we have to cat the pieces together, gunzip the file, then do let the import read through the extra unnecessary data. I have not timed the two, but since the import is the slowest step, so probably there is nothing much to be gained by instead using the full export. And not using the full export is certainly simpler. --atp@piskorski.com, 2002/09/23 00:15 EDT)

Import is kind of slow:

See also this OpenACS thread for more info on that, and on other export/import issues and options in general.

LOBs can cause trouble:

Warning: If you want to export/import a schema with LOBs in it, be carefull! No matter what Oracle user you too, the LOBs tend to end up back in the same tablespace they were originally created in, which you do not want. To check if you already have LOBs in the wrong tablespaces, use a query something like this: 

column owner            format a20
column tablespace_name  format a20
column segment_type     format a15
column segment_name     format a30
select
  owner
  ,tablespace_name
  ,segment_type
  ,bytes
  ,segment_name
from dba_segments
where owner not like '%SYS'
  and owner not like 'SYS%'
  and owner not in ('OUTLN', 'AURORA$JIS$UTILITY$', 'OSE$HTTP$ADMIN')
  and owner != tablespace_name
order by owner, tablespace_name, segment_name ;

Use Oracle 8.1.7.2 or later:

We upgraded our Oracle from 8.1.7.0 to 8.1.7.4 to fix export IMP-00015 import bug which prevents primary key and unique constraints from being created properly when exporting from user A and importing to user B. (The bug was fixed in 8.1.7.2. See also an OpenACS BBoard thread on this. Here are a bunch more links on metalink.oracle.com about that bug: one, two, three, four. And here's Oracle's list of bugs fixed in all 8.1.x patch sets.

Not-Export - Other ways to get data in and out of Oracle:

External Links:

Oracle User Permissions:

See also the Oracle docs on permissions concepts, and the SQL grant and revoke statements.

What we often want to be able to do, is say, "Grant user A read-only acess to everything in user B's schema." Astonishingly, Oracle apparently provides no way to do that.

So what we do instead, is run a script to grant the 'select' privilege on each of user B's tables and views. For an example of such a script, see [TODO: Add links to these scripts:] perm-set.sql, and also the perm.grant_on_all procedure it uses.

Tom Kyte's fancy who_am_i and who_called_me pl/sql functions dynamically figure out the name of the current and calling pl/sql block. But if all you want to know is "What Oracle user am I?", simply do:

select username from user_users

SQL Tuning:

Autotrace:

For autotrace to work, as Philip's Tuning chapter says, for each Oracle user that you want to use autotrace with you need to create the PLAN_TABLE table by starting up sqlplus and doing:
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Try one or more of these autotrace settings in Sqlplus: 
set timing on
set autotrace on explain stat
set autotrace traceonly explain stat

tkprof and trace files:

Autotrace is useful, but to see details on what Oracle really did with the query plan, you need to generate a trace file and then run it through tkprof. First issue one of these statements from Sqlplus (or PL/SQL): 
alter session set sql_trace true;
dbms_session.set_sql_trace(TRUE);
Then find the trace file you just generated (it will be one of these files: "$ORACLE_BASE/admin/*/udump/*.trc"), and run Oracle's tkprof command-line utility to generate a human-readable report: 
$ tkprof $trace_file $output_file explain=$username/$password
TODO: When using a dblink to a remote Oracle instance, it's supposed to be possible dbms_session.set_sql_trace() to get the remote instance to also generate a trace file for its part of the query, but i never got that to work. --atp@piskorski.com, 2005/02/23 13:21 EST

Lessons learned the hard way:

There are many strange and wonderful ways to produce unintended consequences with innocent-looking queries. It is the intent of this section to record our own experiences of this kind, to avoid repeating them.

ARCHIVELOG Mode:

We should always be running any important Oracle database in ARCHIVELOG mode, so that we can do hotbackups, etc. Turning on ARCHIVELOG mode is actually pretty easy. Here's how I did it, 2002/06/17:

First edit our $ORACLE_BASE/admin/ora8/pfile/initora8.ora startup file. You should have lines like this: 

log_archive_start = true
log_archive_dest_1 = "location=/ora8/m01/app/oracle/admin/ora8/arch"
log_archive_format = arch_%t_%s.arc

Note that in the log_archive_dest_1 = "location=/directory/file-pattern" above, pattern includes:

Then you need to shutdown the instance, switch to ARCHIVELOG mode, and then tell Oracle to start automatically archiving logs: 

$ svrmgrl

SVRMGR> connect internal
SVRMGR> archive log list
SVRMGR> select name from v$archived_log;
SVRMGR> shutdown immediate;

SVRMGR> startup mount;
SVRMGR> alter database archivelog;
SVRMGR> archive log start;
SVRMGR> archive log list
SVRMGR> select name from v$archived_log;
SVRMGR> alter database open;

SVRMGR> shutdown immediate;
SVRMGR> startup;

In theory, Oracle should be ready to go after the 'alter database open' above. That last shutdown and startup was just to make me feel better. You could also skip the 'alter database open' and just do the shutdown and startup.

Also, here are some useful queries to show current online archive log related settings: 

-- Show archive log settings set from init.ora:
column name   format a30
column value  format a20
select 
   v.name  ,v.value
  ,v.isses_modifiable  ,v.issys_modifiable
from v$parameter v
where v.name like 'log_archive%'
order by v.name ;

-- Archive log destination info:
column destination  format a40
select  dest_id  ,status  ,reopen_secs  ,destination
from v$archive_dest ;

See also these other useful Oracle views for displaying archived redo log information.

Help, old archived redo logs are filling up my disk!

Note: archivelog files older than two days are deleted nightly by our /usr/local/adm/bin/ora-rman-hb.sh job that runs nightly at 23:00.

If you're running out of disk space, you probably want to move your archive log location to a disk with more space - see below.

But, unless you do something (like running a nightly script to delete too-old logs), the archived logs will just keep piling up indefinitely. To delete them right now, do: 

$ #ARCHIVED_LOGS_DIR="$ORACLE_BASE/admin/ora8/arch"  ;# old location
$ ARCHIVED_LOGS_DIR="/ora-backups/arch/"
$ cd $ARCHIVED_LOGS_DIR

  # List all logs more than 4 days old:
$ find . \( -mtime +4 -a -name "*.arc" \) -ls | sort -k 9,10
  # DELETE all logs more than 4 days old:
$ find . \( -mtime +4 -a -name "*.arc" \) -exec rm {} \;

Note, in this case do not do the above find commands like this: 

  # BAD: If finds no old files, will list entire directory!:
$ find . \( -mtime +4 -a -name "*.arc" \) -print | xargs ls -l | sort -k 9,10
  # BAD: If find no old files, will DELETE entire directory!:
$ find . \( -mtime +4 -a -name "*.arc" \) -print | sudo xargs rm

Moving the Archived Redo Logs to a different disk:

Our old archive log destination had a lot of other stuff on the disk, and was running out of space. Moing it to a new, bigger disk was pretty simple.

First, make the new directory: 

mkdir /web/oracle-backups/arch
chown oracle:dba /web/oracle-backups/arch

Then edit our $ORACLE_BASE/admin/ora8/pfile/initora8.ora startup file, adding or editing these lines: 

##log_archive_dest_2 = "location=/ora-backups/arch REOPEN=120"
log_archive_dest_2 = "location=/web/oracle-backups/arch REOPEN=120"
log_archive_dest_state_1 = "DEFER"
log_archive_dest_state_2 = "ENABLE"

Then simply shutdown and restart the Oracle instance, and you're ready to go. Make sure that no imminent scheduled jobs will be needing the database. 

svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> startup
SVRMGR> exit
restart-aolserver outpost-dev &
restart-aolserver outpost-prod &

Make sure you change your nightly hot backup scripts to look in the new archive log locations. Edit file /usr/local/adm/bin/ora-rman-hb.sh 

##ARCHIVED_LOGS_DIR="/ora-backups/arch/"
ARCHIVED_LOGS_DIR="/web/oracle-backups/arch/"

and copy over the accumulated archivelog files... 

mv /mirror00/oracle-backups/arch/* /web/oracle-backups/arch &
chown oracle:dba /web/oracle-backups/arch/*.arc
Finally, start a new archivelog file to verify that the process is working properly. 
svrmgrl
SVRMGR> connect internal
SVRMGR> alter system archive log current
SVRMGR> exit 
ls -l /web/oracle-backups/arch  (there should be a new arc file here)
ls -l /mirror00/oracle-backups/arch  (and nothing here)
rmdir /mirror00/oracle-backups/arch

Note that we left our old log_archive_dest_1 location in initora8.ora, but disabled it with the log_archive_dest_state_1 = "DEFER". From my reading of the Oracle docs, I believe it will never be used at all, unless and until we manually enable it again.

What I really wanted to do, was have Oracle normally use only the new log_archive_dest_2, but switch over to the old log_archive_dest_1 if something goes wrong with 2 (e.g., if it runs out of space). Unfortunately, as far as I can tell from the docs, it is impossible to configure Oracle that way. If both destinations are enabled, Oracle will always log all archive logs to both destinations.

Alternatively, just make a symlink

An alternative approach would be to move the arc files and make a symlink to the new location:

- shut down Oracle
- copy arc files from /mirror00/oracle-backups/arch to /web/oracle-backups/arch

mv /mirror00/oracle-backups/arch /web/oracle-backups/arch
- make a symlink in /mirror00/oracle-backups
ln -s /web/oracle-backups/arch /mirror00/oracle-backups/arch
- start up Oracle

Moving a Control File:

You should definitely make sure you relocate some of your control files copies to different disk volumes. 

$ svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate

$ cp -p /ora8/m01/app/oracle/oradata/ora8/control03.ctl /home/ora8/app/oracle/oradata/ora8/control03.ctl
$ sudo chown oracle:dba /home/ora8/app/oracle/oradata/ora8/control03.ctl

Edit the control_files line in your init.ora config file to match:

control_files = ("/ora8/m01/app/oracle/oradata/ora8/control01.ctl", "/ora8/m01/app/oracle/oradata/ora8/control02.ctl", "/home/ora8/app/oracle/oradata/ora8/control03.ctl")

Then start Oracle up again, verify that Oracle really isn't using the old copy of control03.ctl by checking that it's modification time is older than the rest of the control files, then finally it: 

SVRMGR> startup
SVRMGR> exit
$ ls -lt /ora8/m01/app/oracle/oradata/ora8/*.ctl /home/ora8/app/oracle/oradata/ora8/*.ctl
$ rm /ora8/m01/app/oracle/oradata/ora8/control03.ctl

Adding a Second Redo Log Member to Each Group:

You should definitely make sure you multiplex your online redo logs, by adding redo log members. For this, you do not need to shut down Oracle. Simply do, e.g.: 

$ svrmgrl
SVRMGR> connect internal
SVRMGR> select group#, member from v$logfile;

GROUP# MEMBER                                      
------ --------------------------------------------
     1 /ora8/m01/app/oracle/oradata/ora8/redo01.log
     2 /ora8/m01/app/oracle/oradata/ora8/redo02.log
     3 /ora8/m01/app/oracle/oradata/ora8/redo03.log

SVRMGR> alter database add logfile member '/home/ora8/app/oracle/oradata/ora8/redo01b' to group 1;
SVRMGR> alter database add logfile member '/home/ora8/app/oracle/oradata/ora8/redo02b' to group 2;
SVRMGR> alter database add logfile member '/home/ora8/app/oracle/oradata/ora8/redo03b' to group 3;

SVRMGR> select group#, member from v$logfile;

GROUP# MEMBER
------ --------------------------------------------
     1 /ora8/m01/app/oracle/oradata/ora8/redo01.log
     2 /ora8/m01/app/oracle/oradata/ora8/redo02.log
     3 /ora8/m01/app/oracle/oradata/ora8/redo03.log
     1 /home/ora8/app/oracle/oradata/ora8/redo01b  
     2 /home/ora8/app/oracle/oradata/ora8/redo02b  
     3 /home/ora8/app/oracle/oradata/ora8/redo03b

Note that above, the directories /ora8/m01/ and /home/ora8/ happen to be be on two entirely separate disks. This is in fact the whole point of having multiple member files in each log group. Oracle writes to all the redo files in each redo log group, so by having two copies, each on a different disk, we are better protected against disk crashes or corruption.

Renaming a Redo Log Member:

Oops, we just forgot the .log on the end of the new redo log filenames above. This is ok, everything still works. But if we are really annoyed by this, we can rename them, but we have to shut down Oracle in order to do it. TODO: Actually try it and verify that this is correct. E.g.: 

$ svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate

$ cd /home/ora8/app/oracle/oradata/ora8/
$ mv redo01b redo01b.log ; mv redo02b redo02b.log ; mv redo03b redo03b.log

SVRMGR> startup mount

SVRMGR>
alter database rename file 
   '/home/ora8/app/oracle/oradata/ora8/redo01b'
  ,'/home/ora8/app/oracle/oradata/ora8/redo02b'
  ,'/home/ora8/app/oracle/oradata/ora8/redo03b'
TO
   '/home/ora8/app/oracle/oradata/ora8/redo01b.log'
  ,'/home/ora8/app/oracle/oradata/ora8/redo02b.log'
  ,'/home/ora8/app/oracle/oradata/ora8/redo03b.log'
;

SVRMGR> shutdown immediate
SVRMGR> startup

Resizing Your Online Redo Logs:

Actually, you can't resize them. What you do, is create new redo log groups with a larger size, then drop the old ones. It's actually easy, no need to shutdown Oracle or anything. E.g.: 

SVRMGR> alter database add logfile group 4 (
  '/ora8/m01/app/oracle/oradata/ora8/redo-4a.log',
 '/home/ora8/app/oracle/oradata/ora8/redo-4b.log'
) SIZE 200M;

SVRMGR> alter database add logfile group 5 (
  '/ora8/m01/app/oracle/oradata/ora8/redo-5a.log',
 '/home/ora8/app/oracle/oradata/ora8/redo-5b.log'
) SIZE 200M;

SVRMGR> alter database add logfile group 6 (
  '/ora8/m01/app/oracle/oradata/ora8/redo-6a.log',
 '/home/ora8/app/oracle/oradata/ora8/redo-6b.log'
) SIZE 200M;

SVRMGR> alter database drop logfile group 1;
SVRMGR> alter database drop logfile group 2;
SVRMGR> alter database drop logfile group 3;

If the logfile group you're trying to drop is currently in use, you can make Oracle switch to a different one with:

SVRMGR> alter system switch logfile;

Note that once your redo logs are bigger, you're likely to see "errors" like this in your alert log: 

Wed Jan  1 07:21:16 2003
Thread 1 advanced to log sequence 12594
  Current log# 4 seq# 12594 mem# 0: /ora8/m01/app/oracle/oradata/ora8/redo-4a.log
  Current log# 4 seq# 12594 mem# 1: /home/ora8/app/oracle/oradata/ora8/redo-4b.log
Wed Jan  1 07:21:16 2003
ARCH: Beginning to archive log# 6 seq# 12593
Wed Jan  1 07:21:16 2003
ARC1: Beginning to archive log# 6 seq# 12593
ARC1: Failed to archive log# 6 seq# 12593
Wed Jan  1 07:21:17 2003
ARCH: Completed archiving log# 6 seq# 12593
In actuality, that's not an error at all, just an annoying warning message that you can't do much about. As Tom Kyte explains here, it is a side effect of having more than one archiver process running. Above, the ARC1 process tried to archive redo log seq# 12593 which the ARCH process was already in the middle of archiving.

Other Oracle Configuration Changes to Consider:

Converting an Oracle Database from ASCII7 to UTF8:

First check what character sets your Oracle database is using: 
select * from nls_database_parameters
where parameter in ('NLS_CHARACTERSET' ,'NLS_NCHAR_CHARACTERSET');
Oracle's 8i character set FAQ links to the their 8i Database Character Set Migration doc, which includes how to use the csscan Character Set Scanner Utility, as do the 9i Character Set Scanner docs. And (as mentioned in this UTF8 OpenACS thread), Metalink Note 66320.1 also gives a bunch of information on how to convert your database character set. See also Metalink Note 158577.1 NLS_LANG Explained.

Metalink Note 13854.1 explains how the dump SQL function can be used to help debug character set problems, e.g.:

Advanced DUMP usage for character data:

Character conversion can be tested using the SQL 'dump', 'chr' and 'convert' functions by specifying byte values as defined by the character set encoding scheme. This testing technique has the advantage that a terminal configured for a given encoding scheme is not necessary, for example:

SQL> select dump(convert(chr(231),'TARGET_CHAR_SET','SOURCE_CHAR_SET')) from dual;

Set Up csscan:

You have to do this only once, before running csscan for the first time:

The Oracle docs caution that csscan could take up a lot of space. In practice, I've only seen it use a trivial 1 MB or so, but if you want to be conservative, give it its own tablespace like so:

Now start up sqlplus as the Oracle "system" user, and run the csminst.sql script: 

# For 8i:
$ sqlplus system/PASSWORD
# For 9i:
$ sqlplus "system/pg8rules as sysdba"
# Same in both 8i and 9i::
SQL> start $ORACLE_HOME/rdbms/admin/csminst.sql

Running csscan:

It's easy, and pretty fast. Just run this from the command line:
$ORACLE_HOME/bin/csscan system/PASSWORD tochar=utf8 tonchar=utf8 full=y suppress=200 array=1024000 process=1

$ORACLE_HOME/bin/csscan system/PASSWORD fromchar=WE8ISO8859P1 fromnchar=WE8ISO8859P1 tochar=utf8 tonchar=utf8 full=n user=ddr_prod table=ddr_pattern_stuff suppress=200 array=1024000 process=1 log=scan-pattern-stuff-from-we8iso8859p1-20041130

That will write out three scan.* files with results into the current directory.

Actually Convert the Database:

The safest way to convert is export all the data from your ASCII7 database, and import it into a new UTF8 database, and that can be made to work no matter what. However, all those exports and imports could take a long time on a large database, so we want to avoid that if possible.

If csscan reports that you don't have any errors at all, neither "exceptional" nor "convertable", then you might as well go ahead and flip the database character set right now: 

shutdown transactional;
-- Do full cold backup now.
startup mount;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set UTF8;
-- Optional, depends on what you want:
--alter database national character set AL16UTF16;
shutdown immediate;
startup;
-- Not in Oracle docs, but sometimes you seem to need this:
alter system disable restricted session;
But if csscan does report problems, don't do that yet. Read more from the Oracle docs, you definitely may still be able to avoid a full export/import.

Database Links, UTF-8, and ORA-24365:

Getting "ORA-24365: error in character conversion" accross a database link connecting and 8i to a 9i database? (It's a bug in 8i.) Metalink Note 237593.1 explains what's going on, and gives lots of detail on how to fix it.

Basically, Oracle has several different Unicode character sets. Apparently, Oracle's "AL32UTF8" is UTF-8 proper, but only 9i and newer support it. Oracle's "UTF8" is actually CESU-8, and this version is supported by both 8i and 9i.

In 9i, AL32UTF8 is the default Unicode character set, but if you want to have an 8i database talk to 9i over a dblink, the 9i database must not use AL32UTF8 - use the older UTF8 instead. Note that it is not necessary for both databases to use UTF8 (although you probably do want to anyway), the 8i database can use any character set you want.

You can easily check what character sets different versions of Oracle support like this: 

select value from v$nls_valid_values
where parameter = 'CHARACTERSET'  and value like '%UTF%'
order by value ;

From Metalink Note 140014.1, it looks like exactly the same problem exists for AL16UTF16, which is the default National Character Set in 9i and 10g. However, an 8i patch to support AL16UTF16 exists for that, and we don't use any National Character Set stuff anyway.

If you have an existing 9i AL32UTF8 database and want to change it to UTF8 so it can interoperate with 8i, definitely read that Matalink note above. Run csscan like this:

$ORACLE_HOME/bin/csscan system/PASSWORD full=y fromchar=AL32UTF8 tochar=UTF8 capture=Y array=1000000 process=1 log=scan-al32-to-utf8
If and only if that reports a completely clean bill of health, then go ahead and alter the database character set as explained above, but rather than the usual: 
alter database character set UTF8;
Use this line instead: 
alter database character set internal_use UTF8;
But if csscan reports any Convertible or Exceptional strings at all, don't do that, go back and read the rest of that Metalink note.

Intermedia:

The sws_service.rebuild_index job occasionally breaks:

The ACS 4.2 Site-Wide-Search package works by periodically running the sws_service.rebuild_index Oracle job to update the Intermedia full-text index. Occasionally though, the Intermedia index seems to get into some state where this update job can no longer work.

I don't really know what the true root cause is, but you will find an ORA-1631 error in the alert log, something like this: 

Sat Feb  1 05:10:46 2003
 ORA-1631: max # extents 249 reached in table MYUSER.DR$SWS_SRCH_CTS_DS_IIDX$I 
Sat Feb  1 05:10:52 2003
Errors in file /ora8/m01/app/oracle/admin/ora8/bdump/ora8_snp0_22661.trc:
ORA-12012: error on auto execute of job 26
ORA-29863: warning in the execution of ODCIINDEXCREATE routine
ORA-06512: at "MYUSER.SWS_SERVICE", line 375
ORA-06512: at line 1
(In some older cases, I've seen only the ORA-1631 given in the alert log, without any of the error information, which might be an 8.1.7.0 vs. 8.1.7.4 version difference.)

If you have the Monitoring package installed at /monitor, go to URL "/monitor/cassandracle/jobs/all-jobs". You'll see that the sws_service.rebuild_index job has many errors. Note that Job Id; we'll assume here in this example that it is 26.

The corrective action is simply to log into sqlplus and do this. The alter index step is very slow - don't be alarmed: 

SQL> alter index sws_srch_cts_ds_iidx rebuild parameters ('replace');
SQL> exec dbms_job.broken (26, FALSE)
SQL> commit;

So it seems that the normal alter index sws_srch_cts_ds_iidx rebuild online parameters ('sync'); that the SWS package runs gradually wastes space until the table hits some limit, and then running 'replace' instead of 'sync' fixes things.

TODO: It would probably be a good idea to periodically (e.g., weekly) rebuild the index with 'replace' rather than 'sync'.

Intermedia library path problems:

On our Solaris box, Oracle Intermedia had some sort of library path config problem - the Inso filter stuff in $ORACLE_HOME/ctx/lib doesn't get found properly. You can see my my original plea for help (also here, old broken link) on web/db for more info. However, I successfully worked around this problem by simply symlinking everything in $ORACLE_HOME/ctx/lib to /usr/lib, with the following little script: 

#!/usr/local/bin/tclsh
set source_dir {/ora8/m01/app/oracle/product/8.1.7/ctx/lib}
set target_dir {/usr/lib}
foreach f  [split [exec ls -1 $source_dir]] {
   exec ln -s "$source_dir/$f" "$target_dir/$f"

--atp@piskorski.com, 2001/08/21 06:46 EDT

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 into "$ORACLE_BASE/patches/2525701/" so I wouldn't lose track of them. On capital, the patch zip file is:

/mirror01/oracle-software/p2037255_8174_LINUX.zip

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

Runaway Queries, Deadlocks, and Other Problems:

There are a bunch of SQL queries in useful-misc.sql that should help. See the queries that show who's holding locks, etc.

For example, if you see an Oracle process taking up lots of CPU or disk IO, you can startup sqlplus and use these queries to figure out what's going on: 

-- Unix process and Oracle session info.  This is fast.
set linesize 180
select
   p.spid  -- The UNIX PID
  ,s.sid  ,s.serial# 
  ,p.username  as os_user
  ,s.username  ,s.status
  ,p.terminal  ,p.program
from   v$session s  ,v$process p
where p.addr = s.paddr
  --and p.spid in (4656)
  --and upper(s.username) like 'USERNAME%'
order by s.username ,p.spid ,s.sid ,s.serial# ;

-- What SQL is running?  Kind of slow.
set linesize 180
select
   s.username
  ,s.sid  ,s.serial# 
  ,sql.sql_text
from  v$session s, v$sqltext sql
where sql.address    = s.sql_address
  and sql.hash_value = s.sql_hash_value
  --and upper(s.username) like 'USERNAME%'
order by s.username ,s.sid ,s.serial# ,sql.piece ;

Then you can start up svrmgrl, do connect internal, and then kill the runaway session with:

alter system kill session 'SID,SERIAL#';

Can't Connect to Oracle via Net8:

There are various things that could cause this. Here's one we experienced:

$ORACLE_HOME/network/admin/tnsnames.ora must be world readable. If it is not, you won't be able to connect to Oracle over Net8! (You will be able to use Bequeath connections, however.)

To help debug, try:

$ tnsping ora8

See also the sections in our 9i install notes, Initial Configuration for the Oracle Listener and Fix the Oracle Listener.

Redundancy, Failover, Etc.:

External Links:

Database Links between Oracle instances:

Database links are basically the same in both Oracle 8.1.7.4 and 9.2.0.4. Here are some docs:

Examples of creating and using "private fixed user" db links: 

-- From sqlplus in the ora9 instance:
create database link dblink_atp_8i connect to atp_8i identified by PASSWORD using 'ora8';
select count(*) from user_tables@dblink_atp_8i ;

-- From sqlplus in the ora8 instance:
create database link xfcus_prod connect to xfcus_prod identified by PASSWORD using 'ora9';
select count(*) from user_tables@xf;

Tuning Oracle Instance Parameters:

You may want to tune various instance parameters for memory usage, etc., especially if you're running more than one instance on the same machine.

TODO: Look into this and add more info. --atp@piskorski.com, 2003/05/20 03:48 EDT

Deleting an Oracle Database:

At least with Oracle 9i, you can use the "Database Creation Assistant" $ORACLE_HOME/bin/dbca to drop your database (see also here). That would seem to be the easiest way, however, when I tried it it just seemed to hang indefinitely. For 8i, dbassist also offers a "Delete a database" option, but I never tried it at all. So, the manual way, which actually works:

Dropping a Database says, "To drop a database, remove its datafiles, redo log files, and all other associated files (control files, parameter files, archived log files). To view the names of the database's datafiles and redo log files, query the data dictionary views V$DATAFILE and V$LOGFILE."

Of course, if the reason you're deleting a database is that creating it didn't work right and you were never able to start it up at all, you won't be able to query those views. But if you have another instance you installed with similar parameters, running these querys there should give a very good idea of where stuff is located: 

select name from V$DATAFILE;
select member from V$LOGFILE;
select name from V$CONTROLFILE;

Example - Deleting 8i Database "ora8c":

In my case, to delete database "ora8c", I just removed these files and directories: 
$ORACLE_BASE/oradata/ora8c/
$ORACLE_HOME/dbs/orapwora8c
$ORACLE_HOME/dbs/initora8c.ora
$ORACLE_HOME/assistants/dbca/install-c
And then edited the following files, removing all reference to "ora8c": 
/etc/oratab
$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/tnsnames.ora

Example - Deleting 9.2.0.4 Database "ora9", 2004/11/24:

My first pass at creating this test database didn't turn out quite the way I wanted, so I decided to just junk it completely and start over. (Note that this test was not in Archivelog mode.) First check some settings: 
$ sqlplus '/as sysdba'

SQL> select name from V$DATAFILE;
NAME
-----------------------------------------------
/ora9/m01/app/oracle/oradata/ora9/system01.dbf
/ora9/m01/app/oracle/oradata/ora9/undotbs01.dbf
/ora9/m01/app/oracle/oradata/ora9/cwmlite01.dbf
/ora9/m01/app/oracle/oradata/ora9/drsys01.dbf
/ora9/m01/app/oracle/oradata/ora9/example01.dbf
/ora9/m01/app/oracle/oradata/ora9/indx01.dbf
/ora9/m01/app/oracle/oradata/ora9/odm01.dbf
/ora9/m01/app/oracle/oradata/ora9/tools01.dbf
/ora9/m01/app/oracle/oradata/ora9/users01.dbf
/ora9/m01/app/oracle/oradata/ora9/xdb01.dbf

SQL> select member from V$LOGFILE;
MEMBER
--------------------------------------------
/ora9/m01/app/oracle/oradata/ora9/redo03.log
/ora9/m01/app/oracle/oradata/ora9/redo02.log
/ora9/m01/app/oracle/oradata/ora9/redo01.log

SQL> select name from V$CONTROLFILE;
NAME
-----------------------------------------------
/ora9/m01/app/oracle/oradata/ora9/control01.ctl
/ora9/m01/app/oracle/oradata/ora9/control02.ctl
/ora9/m01/app/oracle/oradata/ora9/control03.ctl

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

$ echo $ORACLE_BASE $ORACLE_HOME
/ora9/m01/app/oracle /ora9/m01/app/oracle/product/9.2.0
Based on the results of the queries above, delete these files: 
$ rm -rf $ORACLE_BASE/oradata/ora9/
$ rm -rf $ORACLE_BASE/admin/ora9/
$ rm $ORACLE_HOME/dbs/{orapwora9,spfileora9.ora,lkORA9}
And edit just this file, removing all reference to "ora9": 
/etc/oratab
Note that I didn't edit "$ORACLE_HOME/network/admin/listener.ora" or "$ORACLE_HOME/network/admin/tnsnames.ora", so I probably left some references to ora9 around, but I'm going to re-create it anyway so that seems ok.

Installing Oracle 8.1.7:

External Links:

The content of much of this section (at least as of 2002-12-13) is also on the OpenACS BBoard.

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

Important: These are not full install instructions. They are intended to complement and modify the OpenACS Oracle 8.7.1 install docs, not stand alone. 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 throughoutt 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 Intermedia 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: profile-oracle.sh,v 1.1 2002/12/05 19:33:56 atp 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 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:

Note that you can't back out an Oracle patchset, you have to reinstall, or restore from a backup.

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 !):

To run dbassist you need to log in as Oracle and have X-Windows connections properly forwarded to your display. This is annoying to try to accomplish using su or sudo. It is easiest to simply ssh to your own machine and log in as oracle (you must know oracle's unix password); ssh handles all the X forwarding automatically.

If you are running dbassist again to install a second independent Oracle database and instance on the same machine, at first it seemed to me that you must do unset ORACLE_SID before running dbassist. Otherwise, dbassist will just hang there and never start up properly. However, that isn't really true, as I have now successfully started with ORACLE_SID set. The real problem is that the Oracle jre process goes into an infinite loop sucking up all cpu, and dbassist stalls. To fix, simply kill -9 the jre process before starting dbassist again. It is, however, still probably a good idea to unset ORACLE_SID.

Therefore, to run dbassist, do this: 

ssh -X oracle@localhost
unset ORACLE_SID
export LD_ASSUME_KERNEL=2.2.5
# See what Oracle environment variables are set:
env | grep ORA
dbassist &

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.

Note that even we're using the "save information to a shell script" option, dbassist itself still changes a whole bunch of other stuff, rather than more intelligently leaving that all to scripts it generates. In addition to generating the database creation shell scripts, dbassist seems to:

That's pretty ugly, but it all points out that you could not just replace dbassist with a script without first tracking down and replicating all these other annoying but necessary side effects of running dbassist.

dbassist does not seem to put the database creation scripts where you tell it. E.g, I say to put them in "$ORACLE_HOME/assistants/dbca/install-c" but instead dbassist sticks them in "$ORACLE_HOME/assistants/dbca/", which is a bit messy. You can move these, but you may have to edit the sqlora8c.sh script as well, because sometimes dbassist hard-codes the full pathnames there. If you only installing one Oracle database on this machine, it probably doesn't matter, but if you're installing multiple databases, you probably want to organize that stuff a bit.

Note that The install scripts dbassist writes out are simple and stupid. When I installed a second ("ora8c") database and instance on the same machine, the 14 new install scripts:

sqlora8.sh, ora8alterTablespace.sh, ora8context.sh, ora8drsys.sh, ora8iMedia.sh, ora8java.sh, ora8ordinst.sh, ora8replicate.sh, ora8run.sh, ora8run1.sh, ora8run2.sh, ora8spatial1.sh, ora8sqlplus.sh, ora8timeseries.sh
were all exactly the same as the old scripts for my old instance, except for a bunch of hard-coded ORACLE_SID and file path names.

I personally find it obnoxious that dbassist sticks the database name (e.g., "ora8d") onto the front of all the database creation scripts, so I just make sure they're in their own directory (e.g., "$ORACLE_HOME/assistants/dbca/install-ora8d/"), and use this ora-inst-mv.tcl Tcl script (TODO: add link) to automatically rename them, even though it isn't really necessary.

TODO: dbassist kind of sucks anyway - it uses some Java crap that often doesn't want to run at all, it can't be automated, it doesn't really do what you want, etc. May be better just to take the above generated scripts, refactor them into something more maintainable and useful, and then never use dbassist at all. However, see above, this is not quite as simple as just spitting out the scripts above.

When you run e.g. SQLora8d.sh to create your database, it's probably a good idea to save all the output, in case there are any errors you want to refer to later. Redirecting both stdout and stderr should work fine, but I took the easy way out and just ran the whole thing in an Emacs shell buffer, then saved the output to a file.

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, I put all my Oracle config files under CVS, before I started editing them. (This is not strictly necessary, and since CVS sucks it is a bit annoying; but you may find it useful anyway.)

$ 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 import ora8-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 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

Fix dbshut:

The stock $ORACLE_HOME/bin/dbshut script shipped with Oracle 8.1.7 will work, but it has two separate design flaws which may eventually cause Oracle to crash and possibly corrupt data during unix shutdown. They're easy to fix. See more info in the OpenACS thread Oracle /etc/init.d scripts should use shutdown immediate. (The exact same problems are also present with 9i.)

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 - lots 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.

Installing Oracle 9.2.0.4:

External Links:

Here's how I installed Oracle 9.2.0.4 on some machines running Red Hat Linux 7.3 (plus newer custom kernel, etc. etc.), c. 2004/11/01. These boxes already had Oracle 8i installed, in a fashion at least roughly similar to that described above - and these instructions do assume that in some cases!

Note that unlike my Oracle 8.1.7 install notes above, this section is intended to be a complete recipe for getting a basic (not necessarily Production grade) Oracle 9i system up and running.

TODO: Currently the OpenACS docs still describe only 8i, nothing for 9i or 10g. If they ever update their install docs, take a look and compare to what I have here. --atp@piskorski.com, 2004/11/29 15:34 EST

First there's a bunch of setup tasks you should do before you even install any software.

Linux kernel settings:

Oracle 9i requires varies Linux kernel settings. Here's how I checked them on Pinky: 
# SEMMSL SEMMNS SEMOPM SEMMNI
$ cat /proc/sys/kernel/sem
250     32000   32      128
$ cat /proc/sys/kernel/shmall
2097152
$ cat /proc/sys/kernel/shmmax
33554432
$ cat /proc/sys/kernel/shmmax
33554432
$ cat /proc/sys/kernel/shmmni
4096
$ cat /proc/sys/kernel/shmall
2097152
$ cat /proc/sys/fs/file-max
399360
$ cat /proc/sys/net/ipv4/ip_local_port_range
32768	61000

And here are all those values, showing Oracle's recommended minimum, the old Pinky value, and the new value I chagned it to on Pinky:

What Oracle min value Pinky old Pinky new
semmsl 100 250 -
semmns 256 32,000 -
semopm 100 32 100
semmni 100 128 -
shmmni 100 4,096 -
shmseg 4,096 ? -
shmvmx 32,767 ? -
shmmin 1 ? -
shmall 2,097,152 2,097,152 -
shmmax 2,147,483,648 33,554,432 2,147,483,648
fs/file-max 65,536 399,360 -
ulimit -u 16,384 7,168 16,384
ulimit -n 65,536 1,024 65,536
ip_local_port_range 1,024 to 65,000 32,768 to 61,000 1,024 to 65,000
I changed those values by doing this. To take effect right now: 
$ sudo /bin/su -c "echo 250 32000 100 128 > /proc/sys/kernel/sem"
$ sudo /bin/su -c "echo '1024^3 *2' | bc  > /proc/sys/kernel/shmmax"
$ sudo /bin/su -c "echo 1024 65000        > /proc/sys/net/ipv4/ip_local_port_range"
To take effect on every reboot, I added these settings to "/etc/sysctl.conf": 
kernel.shmmax = 2147483648
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000

TODO: The Oracle Linux docs are confused, they also say to do this, but while there is a ulimit command on Solaris, there is no such command on Linux: --atp@piskorski.com, 2004/10/31 23:48 EST 

$ #sudo ulimit -n 65536
$ #sudo ulimit -u 16384

Setup Unix Users and Config Files:

Ok, that takes care of kernel settings. Now to create users and edit config files.

First create the Oracle user and groups. Note that above, long ago, we used user "oracle" for 8i, so here we're going to use a different user, "ora9", but the same "dba" and "oinstall" groups. (That seemed reasonable to me, but there's no secret to it. You can use a different arrangement of unix users and groups if you want.) 

$ sudo /usr/sbin/groupadd ora9
$ sudo /usr/sbin/useradd -u 1099 -g dba -G oinstall,ora9 -m ora9
$ sudo passwd ora9
$ sudo ln -s /home/ora9/ /ora9

Edit "/etc/group", and add user "ora9" to the "dba" group. Add yourself to the dba group too.

Now setup each of these files properly: TODO: Define here what constitutes "properly", as these settings and scripts are very important:

Note that Oracle provides a /usr/local/bin/oraenv script to help interactively set environment variables, but I've never used it, nor really looked closely at what it might be good for. --atp@piskorski.com, 2004/11/24 17:12 EST

If you added yourself to the dba group, you probably want to re-login now so that it actually takes effect. (TODO: How the heck do you have Unix updated your current group memberships to match what's in /etc/groups without logging out and in again?)

Install the Oracle Software:

Alright, now we're actually ready to installe Oracle! On Pinky, the downloaded 9i install tarballs are in: /data/nobackup/oracle9i/

To use, first copy them elsewhere and then uncompress them. This will create the Disk1, Disk2, and Disk3 subdirectories: 

$ gunzip ship_9204_linux_*.gz
$ cpio -idmv < ship_9204_linux_disk1.cpio 
$ cpio -idmv < ship_9204_linux_disk2.cpio 
$ cpio -idmv < ship_9204_linux_disk3.cpio

Warning: The Oracle installer is a Java program, it must use X-Windows, and it is incredibly, intolerably, mind-meltingly slow when run remotely over a DSL connection. Fortunately, you can avoid that by using VNC, like this: 

# In shell 1 on my Linux desktop:
$ ssh -L 5910:localhost:5900 -p2020 remote.server.com
# In shell 2 on my Linux desktop:
$ vncviewer -encodings hextile localhost:10

Log in as the new "ora9" user now. Use one of these two ways: 

$ ssh -X ora9@localhost
$ su - ora9
(TODO: Usually, using ssh above is most reliably, and it works fine on my desktop. But on Pinky, the ora9 password doesn't work with ssh, but the same password works just fine with the su command above! Huh? --atp@piskorski.com, 2004/10/28 20:27 EDT)

Now as user ora9, start up the Oracle installer:

$ ./Disk1/runInstaller
In theory, you should be able to both install the Oracle software and create a database all in one fell swoop, but when I tried to do that, the Installer mysteriously died part way through, leaving the software uninstalled. (Also, the OpenACS Oracle 8.1.x install docs always recommended creating your first database separately anyway, in order to better control certain settings, etc.)

Therefore, pick Install Software Only.

When you run Oracle's root.sh script, it will prompt you before overwriting these 8i files, so just in case, go back them up now: 

$ cd /usr/local/bin/
$ sudo cp -p dbhome  dbhome.8i  
$ sudo cp -p oraenv  oraenv.8i
$ sudo cp -p coraenv coraenv.8i
Good, now all the 9i software should be sucessfully installed, but we still have to fix some problems with it:

Add some version control:

At this point, I didn't feel like messing with CVS, but I did want some form of version so I just shoved various scripts and config files into RCS. (Ugly, yes. I really should try out Gnu Arch sometime soon...)

Basically, any Oracle-related file that I edit, I first put under version control. Since we haven't yet created a database, right now this includes only all the Unix scripts listed above, plus also "$ORACLE_HOME/bin/dbshut", which we're going to edit next.

There will be more files you'll want to put under version control later.

Fix dbshut:

Just as with 8i, Oracle's stock $ORACLE_HOME/bin/dbshut script is buggy. (In fact, the script is almost but not quite identical to the 8i version.) Add the same fixes (and then commit them to your version control system). TODO: Add actual patch here.

Now you're done installing the Oracle software.

Create and configure a database:

Still as user ora9, run the Database Creation Assistant (formerly known as "dbassist"):
$ $ORACLE_HOME/bin/dbca
Here are the the settings I chose in dbca, when creating a test database on my Linux desktop, Nov. 2004:

If you save a template, it is easy to use dbca to recreate (perhaps after deleting the entire database) either this or any similar database anytime you want. TODO: There is probably some command-line way to do that which dbca is hiding from us? Where and how?

You should now be able to connect to the new database via sqlplus. (Since you are in the dba group, you can use your own unix user for this, and Oracle will not ask you for any password.) 

$ sqlplus '/as sysdba'
dbca leaves the new database up and running, so you may want to try doing "shutdown immediate" and then "startup" in sqlplus. Make sure that works.

Strangely, dbca wrote out the init.ora file in the usual $ORACLE_BASE/admin/ora9/pfile/ location, but named it "initora9.ora.10242004165813" rather than the "initora9.ora" that Oracle's own scripts expect. Simply rename (or copy) it: 

$ cd $ORACLE_BASE/admin/ora9/pfile/
$ sudo cp -p initora9.ora.10242004165813 initora9.ora

Just as with 8i, Oracle 9i's dbstart script (and probably other utilities as well) looks for the init.ora file in a different place than dbca puts it, so install a symlink:

$ sudo -u ora9 ln -s $ORACLE_BASE/admin/ora9/pfile/initora9.ora $ORACLE_HOME/dbs/initora9.ora

Check that the entries in /etc/oratab are correct. For a real server, you want "Y" to auto-start when the box boots. For your desktop, you probably want "N" in there.

Initial Configuration for the Oracle Listener:

At this point, note that we have no Listener config files (tnsnames.ora, listener.ora, sqlnet.ora) in "$ORACLE_HOME/network/admin/" at all. So we'll run Oracle's Net Configuration Assistant to create basic ones. This is easy:

As the ora9 unix user, run $ORACLE_HOME/bin/netca (it needs X-Windows). Now from the top level of that GUI, we're going to run through it three times, to create each of the three config files:

Some of those settings likely aren't really right, and even if they were, we may want to add additional ones to talk to other Oracle instances, etc. We'll fix this later, below.

(Note that netca starts up the Listener and leaves it running.)

Put more files under version control:

If you haven't already, put each of these files under some form of version control:

Activate startup scripts:

We already installed the new oracle-rdbms startup script above. Activate it now, and then turn off any old obsolete startup scripts you might have: 
$ sudo /sbin/chkconfig --add oracle-rdbms

$ sudo /sbin/chkconfig --del oracle8i
$ sudo /sbin/chkconfig --del oracle8i-net8
$ sudo /sbin/chkconfig --del listener8i

$ sudo rm -f /etc/init.d/{oracle8i,oracle8i-net8,listener8i}
Note that the new oracle-rdbms script is designed work for both 8i and 9i, even if you're running one instance of each on the same box.

Fix the Oracle Listener:

On rare occasions, Oracle's Net Services Administration and Reference Guides can be useful.

Remember that the tnsnames.ora file is much like "/etc/hosts", it is how the Oracle client looks up the names of Oracle services. (It is necessary for accessing remote Oracle services. For Oracle services on the same box, it is optional.) Thus on each box using the Oracle client software, give tnsnames.ora names for all Oracle instances, both local and remote, which we want to connect to from this box.

The listener.ora file, on the other hand, is the config file for the Listener, which accepts incoming connections for Oracle services running on this box, only. Therefore, it should have settings only for Oracle services actually running on this box.

After changing settings, restart the Listener and verify that everything works as it should. If you like, you can use the startup script we installed to restart the Listener: 

$ sudo /etc/init.d/oracle-rdbms -9 -d 0 -l 1 restart
On Pinky, I set things up so that all of these succeed: 
# Connect to 9i instance on Pinky:
$ tnsping ora9
$ tnsping ora9.pinky
$ tnsping ora9.pinky.mysite.com
$ sqlplus USER/PASSWORD
$ sqlplus USER/PASSWORD@ora9
$ sqlplus USER/PASSWORD@ora9.pinky

# Connect to 8i instance on Brain:
$ tnsping ora8.brain
$ tnsping ora8.brain.mysite.com
$ sqlplus USER/PASSWORD@ora8.brain

# Connect to 8i instance on Pinky (normally off):
$ tnsping ora8.pinky
If you want other machines to be able to connect to the 9i instance you just installed here, you will need to edite their $ORACLE_HOME/network/admin/tnsnames.ora config files too.

TODO: Add links to our actual working config files above.

Create Oracle Users and Tablespaces:

What you do here depends on your applications, but you certainly will want to create one or more Oracle users (aka, schemas) and tablespaces.

Your Database is Not Production Worthy:

You now have a working Oracle instance installed, but in no way should you consider it Production ready! It is still running with Archivelog mode off, it doesn't have duplicate control files, it's online redo logs are not multiplexed, you haven't yet set up any backup procedures whatsoever, it's init.ora settings, rollback segments, and redo log sizes all probably need to be tweaked, etc., etc.

Installing Oracle 9.2.0.1 Client Software on Windows XP:

Download the 9.2.0.1 for Windows x86-32 RDBMS stuff as usual, create subdirectories Disk1, Disk2, and Disk3, unzip the three *.zip files into each directory, and run the installer (Disk1/setup.exe).

Note that the mere 193 MB "Runtime" client software does not include the Oracle OLE DB stuff (which some software needs), nor does it include SQL Loader! It probably would have been better to just install the "Administrator" bundle of the Oracle Client, but I ended up first installing "Runtime", then going back, selecting "Custom", and adding these:

Answer all the installer's questions, and tell it to set up a name/connection for the Oracle instance you just created on Linux (e.g., "ora9.myhostname.mydomainname.com").

Start up sqlplus, and verify that you can connect to the Linux Oracle instance. Note that for this to work, both the RDBMS and the Listener must be up and running on the Linux box.

TODO: This could use fleshing out, I glossed over some of the specific steps above. But it's also really not that hard, you'll figure it out. --atp@piskorski.com, 2004/11/04 12:19 EST

Oracle seems to install its files with rather odd file permissions, and Oracle does not report file permission problems correctly. So if you get a TNS failure when trying to connect to a remote database - especially if you are not logged into Windows as the same user we installed the Oracle client sofware - check the permissions on the relevent files. For example, I have seen failures because I (bizarrely) did not even have read access to this file:

C:\oracle\ora92\network\ADMIN\tnsnames.ora
(To fix file permissions, you will, of course, need to turn off the moronic default "Use simple file sharing". It's in Windows Explorer; menu Tools, Folder Options, View tab; "Advanced Settings".)

Upgrading a Database from 8i to 9i:

External Links:

At work, we had a rather substantial Oracle 8.1.7.4 database, on a Red Hat 7.3 based Linux box. Unless I mention otherwise, all my examples in this section are based on upgrading that database to Oracle 9.2.0.4.

TODO: Actually work on this upgrade. --atp@piskorski.com, 2005/02/23 13:26 EST

atp@piskorski.com
 		$Id: oracle.html,v 1.12 2005/04/03 20:48:07 andy Exp $