Urgent - How to implement a two-databases-on-one-host architecture?

[zxmgh]

We need to create two production databases for two different store, they
will have the same load. We only has one physical db box running HP-UX 11.x. We
have three plans, we want to know which way is the best from performance
standpoint, and which way is the worst from performance standpoint.

1. Create two db instances on the box, create one database/schema on each
instance.

2. Create one db instance on the box, create two databases/schemas on the
instance.

3. Logical partition the box into two logical machines. Create one instance on
each logical machine, and then create one database/schema on each instance.

How difficult to implement Plan 3? We may not have enough time to implement it
if it is too complicated.

Between Plan1 and Plan2, which one is better?

Thanks

[dbasan]

Before you do them RTM and know the relation between an Instance/database/schema.

1. OK
2. Not possible
3. mmm

[zxmgh]

dbasan,

Can I ask you why Plan 2 is not possible, from db standpoint, I just need to create two schemas under one instance and these two schemas can be called by different programs and kind of seperate.

By the way, we saw Oracle recommends the following kernel parameters values for
Oracle 10g installation on HP-UX, I would like to know if these kernel
parameters values are for single instance on one box or it can also be used for
multiple instances on one box.

ksi_alloc_max (nproc*8)
executable_stack 0
max_thread_proc 1024
maxdsiz 1073741824 (1 GB)
maxdsiz_64bit 2147483648 (2 GB)
maxssiz 134217728 (128 MB)
maxssiz_64bit 1073741824 (1 GB)
maxswapchunks 16384
maxuprc ((nproc*9)/10)
msgmap (msgtql+2)
msgmni (nproc)
msgseg 32767
msgtql (nproc)
ncsize (ninode+1024)
nfile (15*nproc+2048)
nflocks (nproc)
ninode (8*nproc+2048)
nkthread (((nproc*7)/4)+16)
nproc 4096
semmap (semmni+2)
semmni (nproc)
semmns (semmni*2)
semmnu (nproc-4)
semvmx 32767
shmmax The size of physical memory or 1073741824 (0X40000000),
whichever is greater.

[dbasan]

2. Create one db instance on the box, create two databases/schemas on the
instance.

NOT POSSIBLE, 2 on 1

dbasan,

Can I ask you why Plan 2 is not possible, from db standpoint, I just need to create two schemas under one instance and these two schemas can be called by different programs and kind of seperate.

Now you got the point.

By the way, we saw Oracle recommends the following kernel parameters values for
Oracle 10g installation on HP-UX, I would like to know if these kernel
parameters values are for single instance on one box or it can also be used for
multiple instances on one box.

One box with limitations on the memory consumed collectively by all instances to the kernal parameters set.

[gandolf989]

You can create as many schemas as you like in any instance. Remeber, a database, instance and sid are the same thing, You can have more than one Oracle home per server, each oracle home can have one or more databases, each database is likely to have many schemas, a username in Oracle is called a schema because it owns data. and finally don't be afraid to read the concepts manual at tahiti.oracle.com.

[marist89]

Remeber, a database, instance and sid are the same thing

No. An instance is a the processes and memory that run a database. A database is a collection of files. A single database can have multiple instances operating on it.

each oracle home can have one or more databases,

or none.

[marist89]

1. Create two db instances on the box, create one database/schema on each instance.

Possible. I would use this approach if I thought that one or both of my databases had the possibility of tremendous growth. IMHO, moving an entire database from one host to a new host is much simpler and faster than moving just one schema. Other Oracle Guru's (such as Tom Kyte) would frown on this setup claiming that there is a lot of overhead to running two instances. Personally, I don't follow that train of thought. I'd rather have the ability to move the entire database, tune each accordingly, have smaller pieces to upgrade, and be able to employ a different backup strategy for each.

2. Create one db instance on the box, create two databases/schemas on the instance.

As you already know, this is not technically possible. However, I think you're trying to say two schemas on the same database instance. Other Oracle Guru's suggest this method. Personally, I look at what type of growth I'd be expecting in each schema and if I had enough resources to run it for at least 5 years before I stuck two important schemas in one database. Kernel parameters will be important, understand what they do and how each Oracle parameter affects the kernel parameters (especially semephores).

3. Logical partition the box into two logical machines. Create one instance on
each logical machine, and then create one database/schema on each instance.

errr, overkill IMHO, unless you're dealing with licensing issues, and even then I'd be looking at multiple OH with two databases.