Multiple copies of database running...

[mrmac228]

I don't know whether this is valid or not but I want to set up a database server that will serve several independent clients.

That is I will have several identical systems running the same software that need to connect to independent database instances on a general database server.

What I was thinking was that on the db server I'd install the software in /u01 and then for each system that needs to connect I'd have a seperate /host1/u02/oradata..., /host2/u02/oradata structure. Where host1 and host2 are clients that connect to 5 identical/replica databases each.

The reason for this configuration is to allow independent testing of aspects of the software in parallel on each of the client hosts.

I'd then be able to set up separate listeners, each controlling connection to the 5 databases, on different ports.

The questions I have are:

Is this achievable?
What problems might I have (init*.ora files is one that may be a problem)?

Thanks in advance.

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[engineer2100]

I think you can implement Real Application Cluster

Regards
Engi

 

[dsanchez2]

What you want is achievable. Each of the instances will need:

(1) A SID to identify the instance. The SID that you want to connect to is given by the ORACLE_SID environment variable. For example your SIDs can be: HOST1, HOST2, etc.

(2) Each SID will need its own init*.ora file. Where "*" is the SID of the instance. For example: initHOST1.ora

(3) For each SID a directory where the database files are created. Like you suggested this can be /host1/u02/oradata ..., /host2/u02/oradata

You will not need to start separate listeners for each of your instances. A single listener will handle access to all the instances.

You may be able to achive something similar with a single instance and separate schemas and each schema having is own table-space. This method would consume less resources.

The above assumes that you are runing the same version of ORACLE for all your instances. If you are not, you will also need to create independent ORACLE_HOMEs.

 

[mrmac228]

Thanks for the replies.

Engi does RAC cost more? If so we have no additional budget for it.

I think I may not have been clear enough in my original post, apologies.

Each host needs to access 5 individual databases e.g. DB1, DB2, DB3, DB4, DB5. So host1 is running applications that connect to 5 separate databases and there will possibly be up to 4 hosts requiring access to their own separate sets of 5 dbs.

Having a seperate listener for each will allow me to control access from the individual hosts and be able shutdown db instances on a per host basis.

It will indeed be the same version of Oracle.

I hope this is slightly clearer.

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[sbix]

Give a look to the Oracle 9i Net Services Reference Guide, which explains how to configure multiple listener.
When you have multiple listener you can stop them using lnsrctl STOP lsnr_name

 

[mrmac228]

Thanks sbix,

Yeah I know, its the same on Oracle 8 too.

It was really the idea of multiple identical dbs I'm not too sure about.



I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[sbix]

Indeed ... these DBs have to maintain exact 5 copies of the data??? Are they only queried and never updated???
Probably you can have 5 listener for only one DB, so you can stop a listener on a different port for a group of clients ... I don't know if I got the problem ... :-\\\

 

[mrmac228]

I'm obviously not doing a good job of explaing the problem.

Currently we have one system where application software connects to five different Oracle dbs(on the same system).

Now, the management want 5 more systems that do the same thing but don't want to splash out on more Oracle licenses (4xCPU E420s=£40k Oracle license). If we had the money we could just build all five boxes the same (including oracle databases) and give them to the 5 groups of testers and they could do there own thing (so the data won't be the same anyway as they are independent).

But to save money they want 5 servers for the application and 1 server to contain the 5 sets of 5 databases for each application server.

Have I explained any better?

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[sbix]

Do you really wish 25 DBs running on a server????
Do these application use the same Oracle users?
Why don't use 5 DBs with 5 Tablespaces groups (one per set)
with 5 different groups of user?
In this way you can put OFFLINE tablespace of groups of user, instead of shut down different listeners

 

[mrmac228]

Although there will be 25 dbs, the load is note huge from the application servers.

They do use the same users, is this going to be a big problem?

Would I be able to, for example, change the schema on a particular database 'set' without impacting the others by having 5DBs with 5 Tablespace groups?

Cheers.

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[engineer2100]

Let me try to put his problem in my terms

mrmac has 4 Host Systems ( 4 Application Boxes ) running few applications.
mrmac also has 5 Databases running on a single Box.
Now he wants the Applications running on each of those 4 Application Boxes to access these 5 Databases running on the Single Database Server err.. Box

Is this possible?. If yes how and what problems could he be facing?

HTH..

Regards
Engi

Hope I have spoken your mind mrmac?

 

[sbix]

Yes ... it's a problem because my idea was to have only 5 DBs with 5 Tablespace group each (one per user group).
In my idea you should only replicate the 5 users schema in different tablespaces, granting each user in a different tablespace.
In this way you could also spare a lot of space by creating public synonyms for static tables, packages, functions and so on which leads to a good advantage in term of performances.
25 different DBs means a lot of problems in "where to put what" (DB files, redo log files, archive log files) and a lot of contentions for resources, plus a very high context switching with a good risk of SGA paging.

 

[mrmac228]

Performance is not really an issue here, the systems will be used for purely functional testing, if its slow and resource hungry then so be it. But as I hope I said, the boxes will be used for parallel testing and that the dbs should be independent.

The databases themselves do not need to be huge either, my idea of having all db files (redo, archive etc.) under /host1/u02/oradata.. may not be efficient but would it work?

Thanks for the assist Engi!

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[sbix]

You can have all the DBs you like in a system, if the machine survives ...
A good idea is have them of different system users, so nobody can destroy another users's DB.
This means you have sw installed by the oinstall user and DBs files owned by the various users
By setting a unique TNS_ADMIN directory you can have a centralized listener.ora file splitted in several listeners

 

[mrmac228]

Thats good information, but can I group the databases themselves such that they are independent, manageable and work?

To go back to what Engi was saying, the single oracle system would be like *many* independent Database servers.

i.e. host1 connects to host1:db1 with user1/password, host1:db2 with user2/password etc.
host2 connects to host2:db1 with user1/password, host2:db2 with user2/password etc.

sbix,Engi I do appreciate your help here it is the only way I'll be able to understand whether I can do what I'm thinking of doing.

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[sbix]

Yes each DB is completely undependent by each other.
The only dependance is the software is used by all of them, which means only you can't dismount the filesystem containing Oracle software while at least one DB is active ...
You will have 25 SIDs (i.e DB01, DB02 ... DB25) or
5 different groups of 5 SID (DB01,DB02,.. DB05) onto 5 different servers .... each application group will address the relative DB group in in its own tnsnames.ora, possibly with only the name they need.

 

[mrmac228]

Now that sounds like what I'm after. Now for the painful bit, could you possibly detail what I need to do to get the db server configured as you suggest.

I know its a lot to ask but I would appreciate it.

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[sbix]

If you are speaking about Oracle on Unix the manual says to create an "oracle" user as part of the group "oinstall" (primary group "oinstall&quot.
Create a group "dba" and set it as "set group" for user "oracle".
If you wish to create some Unix user which will have to be granted as OSOPER, create some more Unix user in the "dba" group.
Then you will create some users one per DB, each user will be part of "oinstall" group.
With oracle user perform a Custom Installation of Oracle sw on a specific filesystem on which "oracle" user will have read-write-execute rights.
You will have only one ORACLE_HOME for all the users, there will be a directory $ORACLE_HOME/admin/$SID for each oracle user.
You will have to setup the various "$HOME/.profile" to recall the "oracle" .profile

 

[sem]

I can not suggest a solution, because I'm sure that Oracle is licensed not per CPU, but rather per Instance*CPU, thus in any case this construct would violate your license agreement. Does it make sense or my understanding of Oracle's licensing policy is incorrect?

Regards, Dima

http://seminihin.narod.ru

 

[mrmac228]

As far as I'm aware the licensing is per CPU only (at least the license we have purchased is).

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams