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

[Max Clark]

Wow - This is kinda a loaded question. I do not have any experience with
Oracle on Suse - but I can say that Oracle on RedHat Enterprise (Note
the Enterprise) works great. Something that you want to be aware of, the
9i installer will want RedHat 3.x where the 10g installer will want
RedHat 4.0.

See below for more in depth responses to your questions:

HTH,
Max


Emerson, Tom wrote:
> 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 would definitely download the current installer files from the Oracle 
website. Things change quickly, and two years is ancient.

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

Stay away from RAW partitions, yes Oracle will be faster on a RAW disk, 
but honestly - if you needed that sort of performance this wouldn't be a 
"play with Oracle" and then migrate to it project. RAW partitions make 
administration and maintenance a nightmare because if you make a mistake 
you can wipe your data - plus filesystem based backups are not an option.

LVM/MD will work for you okay, again if you are are really looking for 
better performance invest the money for a Hardware Raid controller (SCSI).

Oracle tablespace is setup to allow you to partition your data across 
multiple directories. This will enable you to balance your file I/O 
across multiple spindles and separate the index from the data. Again if 
you have this kind of I/O this project would be slightly different so 
one directory on one virtual disk will probably work fine.

You won't see any benefit to reiserfs based on the nature of the system. 
When you create an extent Oracle will pre-create a binary file the size 
that you specify (think innodb). Because it is a binary file there is no 
metadata to track in a journal, so outside a quicker fsck a journal 
doesn't give benefit in speed. I stick with ext3 for this reason.

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

You can make sql dumps, do a cold backup of the files on the filesystem, 
or use the internal oracle backup utilities.

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

In oracle you have an instance (think database) that is identified by 
the SID, and within the instance you can have schemas. The best way to 
describe a schema would be a concept similar to branches in a cvs 
repository. You can create multiple virtual databases within the 
instance that you control access to based on username/password.

> 
> 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"

You can backup (see above) - and oracle will also keep an archive and 
redo log for the system. It's what you describe as a transaction log 
were you can "replay" events on the database.

> 
> [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]
> 
>