Multiple Instances on One Server

[THoey]

Hi...

I have been given a new server to house all of my Oracle databases on. Description of the server:

Compaq DL 580
CPU: Quad 700 Mhz Pentium III
RAM: 1Gb
Storage: Four - 18.6Gb, RAID 5 configuration (48 Gb avail after configuration)
Network OS: Version: WIN NT 4.0, Service Pack: 6a
Oracle 8.1.7.1.5

I have four databases that I am going to migrate to this server. Three of these are just reporting databases. Data will be loaded into them using SQL*Loader and then the data will be reported using Crystal Reports. All this will be done in the middle of the night. The fourth database is a small employee payroll type database that will only have any activity every couple of weeks.

I looked in the the Oracle 8i DBA handbook and it suggests having like 22 HD's for a database. The lowest amount it recommends is 7. Are they talking physical disks or virtual disks? We don't have any server here with that many physical disks. Currently, this machine has been set to have two logical drives, C:\ with our standard server image (OS, Virus Protection, etc) and a D:\ completely available to me. Should I re-partition this out to a bunch of little drives? Since all the drives are on the same SCSI controller, I didn't know if there would be any benefit.

Also, I have done Oracle installations before and then altered the generated default database for my needs. Since I am going to have four separate instances on this machine, I need to create them manually. I looked up the CREATE DATABASE command in the DBA Handbook and the Oracle 8i Complete Reference. The first thing it says is "this command should only be used by experienced DBA's". Well, I am experienced, but not in creating multiple instances on one machine. Is there somewhere to get a copy of an example of this command, alter it for my needs and then run it to create each of my other instances? I will also need to make sure the Listener's and Services are started.

With the quad processors, is there any settings that I can use that will make benefit of this power?

I know this is asking a lot, but if someone can help with tips, documents to look at, web-pages, etc, it would be greatly appreciated.

Thanks in Advance... Terry M. Hoey

 

[lfoata]

Oracle recommends 7 drives to implement OFA for performance reasons (IO contention).
If you have two virtual file systems, try to split data and indexes, redologs and archive logs.
Creating one instance or 4 instances is all the same.
The thing to remember is that you may not need 4 listeners configured. Modify listener.ora to listen for all 4 databases if 3 are used during the night and 1 in the day.

For the syntax on create database, you should find examples in ORACLE_HOME/rdbms.
To use the multiple CPUs, look at the parameters that contain the word PARALLEL in your init(sid).ora file.

Also you will need to tune your databases according to what they are used for (transactional or informational). That is where you may need an experinenced DBA.
Too bad I.T. is not cash business

Luc Foata
Unix sysadmin, Oracle DBA

 

[THoey]

Thanks for the info...

I have free reign to take the remaining ~48 Gb and do with as necessary. Should I split it or should I leave it as one large drive. Like I mentioned, there is still only one controller running them, so I was thinking that no matter what, there is still the possibility for contention. Terry M. Hoey

 

[jimbopalmer]

the reason Oracle wants them to be seperate physical Drives is if their are disk errors, the recovery info is not with the data (gone). in the case of the read only databases you are not concerned with recovery as you have no data you save. The active database is at risk. The one thing you can't give for your heart's desire is your heart. - Lois McMaster Bujold

 

[MFGlobalDBA]

You are right about the one controller and the contension. Splitting the files across serveral subdirectories under your D drive is not going to buy you anything. You are in a raid 5 configuration, so OFA and all that other neat suff is gone. You are not relying on the Raid 5 configuration to handle your files.

However, I usually create one or two subdirecties just so that I can have multiple copies of my control files separate from each other. Not for performance or recovery, that is now controlled by Raid 5 (and not much you can do about that). I do this so that I (yes the DBA) does not do something as stupid as deleteing one of these by mistake. Not that I have ever done this. But, the controlfile is the one file that I am a bit anal about. I like to have multiple copies no matter how the landscape looks (meaning Raid whatever).

The previous post is correct about the listener. They may be no reason to have multiple listeners. I am sure you listener can handle 4 databases just fine. The only reason to have seperate listeners is so that you can shutdown one without affecting the others or to have it more mobile - meaining so you can pick everything up (for a particular datbase) and move it to another machine. In that case, I normally have one listener per instance and have that listener named for the instance.

As for the CREATE DATABASE, I edit that all the time. I also edit the sql.bsq that is executed when you execute this command. No, Oracle does not want you to do this (I don't generally tell them EVERYTHING I do). But there is nothing magical about that file. If I have a database that is going to have a lot of stored procedures, I go in and modify those clusters and objects that store this information. I edit various things for various types of databases. Have you ever created a new database and then loaded your application onto this database and found dozens or more of the SYSTEM or SYS objects in many extents. Drives me insane. So, I fix those things so that they won't be in extents.

Then again, I am a hot shot! I've been an Oracle DBA for 8 intensive years and 15 years in IT.

 

[THoey]

Thanks all for your suggestions. I looked for the CREATE SQL and can't find a "sample" or one that I can edit. I can wing it using the books I have, but if someone has a boiler plate version that I could have to start with, I would appreciate it. I can post my email address and then after receiving the file have the site management remove it.

If anyone is willing to send it to me, let me know and I will post it here...

Thanks again... Terry M. Hoey