[SGVLUG] Are there any oracle gurus on this list?

[Emerson, Tom]

How easy (or difficult) is it to (a) install Oracle on linux, and (b) actually USE oracle?

[ok, bad open ended question, I know, so I'll explain a bit...]

We have a legacy database on an HP3000 [two-level hierarchical is the easiest/fastest way to describe the structure -- see below where I start talking about how to create a database] This database will be migrated to Oracle [decision already made, so cannot easily suggest postgres or mysql, then again, not sure I'd want to quite yet...] but that puts me in an "interesting" position: I pretty much have to take some time (on my own) to "fiddle with oracle" so that when they do finally switch, I won't find myself "out of a contract" because I can no longer provide the needed skills, hence the somewhat open-ended question.

A little background: while the current DB is somewhat proprietary, it does have an ODBC interface which provides a basic level of SQL style access.  I already know there will be differences in the /specifics/ of some SQL statements -- such as

     select () from lefttable left outer join righttable on key
 vs
     select () from lefttable, righttable where lefttable.key = righttable.key (+)

[ok, who was passing out the LSD the day the devs thought of that syntax?]  I presume I'll run into similar "gotchas", but back to the questions:  Installing

  -- some time ago [couple of years by now] I got a promotional CD of "linux is hot, oracle on linux is cool" or some such -- part of their "unbreakable" campaign I believe.  Is that likely to be a "current enough" version to be of benefit, or should I burn up the DSL line and pull a few more .iso's?

  -- I read through some of their vendor-specific installation pages; seems I *may* need to [or would certainly benefit from] upgrade SuSE to 9.3 at the very least.  (something about direct access I/O being better supported and/or flaky in earlier versions...)  which leads me to:

     -- HOW should I install it?  The system I will likely be putting this on uses lvm, and I generally format partitions using reiser -- I saw that Oracle likes to use "raw" partitions as they have their own drivers (which work with the aforementioned direct access I/O routines) to "manage" the internal structure of the database.  Would there be a conflict here or not?  what about RAID (md) devices?  what advantages or disadvantages are there to letting the OS manage the storage (i.e., keep the database as a series of files in a directory rather than it's own entire partition)

     -- HOW would I back it up?  I presume there are utilities, but now this is getting into the "using" side of the question, so:

USING Oracle:

  -- aside from the fact you have nearly infinite "fine-grain-control" over how oracle deals with the underlying storage, just how difficult is it to define a "database", or is that even the right term?  (I hear the word "schema" a lot...)

Note: in the world of the HP3000, a "database" is a related collection of "datasets", which are essentially identical to "tables" in SQL terms, but with somewhat limited key and index specifications.  "stored procedures", triggers, and similar items simply do not exist /in the database proper/ -- all that is managed by the applications using the database.  Some limited "referential integrity" is enforced -- you can define a "master" set which will essentially have a unique "primary key", and can then relate that to one or more "detail" sets.  These sets are restricted only in the fact that for any "key" value, a corresponding master record MUST exist, however multiple records can share the same "key".  Also, while a detail set can have multiple "keys" [up to 16 I believe), there is no direct provision for a concatenated key -- if you have one key for "order number", a second key for "line number", and want a concatenated key of order+line number, you have to (as the programmer) physically populate a third field with the concatenated value -- and nothing but your own programming skill stops you from putting different "data" in the so-called concatenated key.

[wow -- that went longer than expected...]

  -- backing up: I'm "used to" a couple of methods of backing up a database [on the HP, naturally...]  either storing the underlying files (requires exclusive access to the files, which is not a problem for this application as it runs "business hours", not 24x7) or by "dumping" the data in some reloadable format [i.e., as a series of "insert" statements in a text file, though binary elements might be problematic...]  A third way, which I've not used much, is to maintain some form of "transaction log" -- usually a chronological list of every insert, update, and delete statement.  This can be replayed as needed to essentially re-create the database, but depending on how your application works and how volitale the data is, you may end up taking longer to "replay" the transactions than it would to restore a "snapshot"

[ok, enough for now -- I'm sure responses will generate more questions -- if it gets too diversionary we can probably move it to the "devsig" forum]