Hi there fellow Oracle people,
I am hoping that
some of you can help me and/or provide details etc. for my situation I find
myself in. So if you bear with me I will describe the
situation.
I currently
support an Oracle 9i (just moved from 7.3.4), windows, tuxedo, client server
application for a utilities company in
Australia.
I have now been
thrown in the deep end on a new system for a very larg bank which is building a
complete new system.
The main spec I am
dealing with in regards to the database is that there can be one or more of the
applications running and each one can feed (identical data) to one or more
oracle 9i databases. It's basically just logging details of transactions, with a
rate of up at least 700/sec
I believe they want
, for redundancy purposes, two application servers sending out 4 streams to 4
oracle instances each, as it can never be allowed to stop
running.
It will be running
on HP-ux (which I am new to) and a SAN (which I'm also new to). I have to come
up with the Oracle requirements, database,tablespace,table designs, sizing
etc.
There is basically 3
layers to the database, very short term(vst), short term(st) and long
term.(lt)
The data is
inserted into the VST tables (partitioned on something like 30 minute time slots
with a rolling around on themselves after a certain period of time. The VST
partitions are loaded to the ST tables (partitioned on 2hr time slots) and kept
for 7 to 14 days. Finally this data is digested to the LT tables and kept for 3
months. So the insertion rate into the VST & ST is very high.
Partitions will obviously only be read from, to move the data down a level, when
writing is taking place in another partition.
I have been told
that there are
many similar projects/systems in the States on this size,
configuration, transaction rate etc.
So I am hoping to
not to have to reinvent the wheel, also as I have not much experience on the
HP-ux/SAN platform. If anyone has gone through a similar exercise and has
documentation, guidelines etc. I would love to be able to get copies to work
from.
Any help on Oracle
details related to this project would be very very welcome.
Things
like.....
- SAN Implementation in regards to Oracle, tablespace placement, config etc.
- Do I need to worry about the SAN setup (done by system engineers) for Oracle, stripe size etc.
- High insert activity into partitioned tables on SAN and choices of Oracle parameters, table parameters (max trans etc.)
- Backups on many databases that can never be broughtdown. Use of RMAN on hp-ux & SAN. Is RMAN even the way to go?
- redo & undo (rollback) requirements, location, design
- Running in Log archive mode, best location on the SAN for the logs.
- Table design, use Locally managed tablespaces but not ASM.
- Indexes... parameters, location
- HP-ux and Oracle any special guidlines
- Database design templates for HP projects
- any other global HP templates/doco etc. regarding Oracle database implementations
- post implementation support guidlines, sys op instructions etc.
- partitioned tables - best practices for continual inserting, creating at one end, dropping at the other.
- Dropping partitions, how indexes affected
- Best
way to insert hundreds of rows a second into one end of a table partition .
Use pl/sql to dump a hundred at a time compared to individual inserts ??
- <any thing I have missed>
I would love to hear
back from any one that thinks they can provide some tried principals, papers,
details, guidelines etc. on any of the above details.
If you are in
Australia then I can even visit if needed.
Many
thanks
Ian
Biddell
