Impact of two databases sharing an oracle instance

Printable View

11-03-2005, 03:41 AM
nirupam

Impact of two schemas sharing an oracle instance

Sorry guys I didn't put my question correclty

As of now I have two seperate databases (DB1, which is Oracle 8.1.7.4 & DB2, which is Orcale 8.1.7.2) on a single Solaris box. Lets assume that DB1 has schema S1 and DB2 has schema S2

now we want to move to a new Solaris box which has a single database instacne (9i) and put both S1 & S2 in it (i.e. an oracle database with two schemas, right?)

what will be the impact on performance, capacity? what areas should we consider to avoid any issues?

Regards
Nirupam
11-03-2005, 04:54 AM
davey23uk

two databases cannot share the same instance, it is impossible

You need to work out what you really have got there
11-03-2005, 06:00 AM
mrchrispy

I think you 2 applications sharing the same database.....yes/no/maybe?

Dont go there.
11-03-2005, 07:19 AM
robertbalmer

Quote:

Originally Posted by mrchrispy

I think you 2 applications sharing the same database.....yes/no/maybe?

Dont go there.

English please !!!
11-03-2005, 08:06 AM
mrchrispy

Quote:

Originally Posted by mrchrispy

I think you 2 applications sharing the same database.....yes/no/maybe?

Dont go there.

sorry, typo on my part.
I think hes asking about 2 applications sharing the same database and what are the implications. It would be possible but its hardly worth the stress
11-03-2005, 08:18 AM
davey23uk

why? a single database takes up less resources than 2 databases.

Twi separate schemas in a single database is the cleanest option
11-03-2005, 09:02 AM
nirupam

Sorry guys I didn't put my question correclty

As of now I have two seperate databases (DB1, which is Oracle 8.1.7.4 & DB2, which is Orcale 8.1.7.2) on a single Solaris box. Lets assume that DB1 has schema S1 and DB2 has schema S2

now we want to move to a new Solaris box which has a single database instacne (9i) and put both S1 & S2 in it (i.e. an oracle database with two schemas, right?)

what will be the impact on performance, capacity? what areas should we consider to avoid any issues?
11-03-2005, 09:23 AM
davey23uk

right, ok two schemas in the same database. You just have to watch the one application isnt so crap that it hogs all the resources on the database.

The normal tuning principles apply, just configure your memory correct, spread out your i/o across all available devices and things should be ok
11-03-2005, 09:38 AM
nirupam

thanks,

Following are the areas I can think of, please let me know your views

1) they will share the SGA, which means
A) The database buffer cache holds copies of data blocks read from data files of both the schemas.
B) redo entries of both the schemas will share the redo log buffer
C) Common shared pool for both the schemas
D) common Large Pool which is an optional memory area to provide large memory allocations for sessions, backup and restore operations etc

2) Program Global Area (PGA) will not be shared? Am I right?

3) The background processes will be shared? Like DBWR (Data Base Writer), LGWR (Log Writer),SMON (System Monitor),PMON (Process Monitor), ARCH (Archiver), RECO (Recovery),USER, CKPT (Check Point), Lock Manager Server (LMS), Queue Monitor (QMN), Dispatcher, Server. Do you know if they are the single thread processes or can handle multiple threads? It looks like only DBWR (Data Base Writer, ARCH (Archiver) and Queue Monitor (QMN) can spawn multiple threads as DBWR0, DBWR1 etc
Do you see any impact due to this sharing?

4) ARCH process will be shared by both schemas so the archive/backup should be schedule properly to avoid overlapping

5) To avoid disk I/O issues, the data and index tablespaces (for each schema) will be created on separate disk blocks. I guess, the DB data files will be on SAN

6) Rollback segment will be shared. it will make restoration complicated

7) SGA would need more physical memory (RAM) to avoid paging (swapping the pages from disk)

8) Maintenance in one database might have impact on the availability of the second schema

9) shared temporary table space which is used for sorting might have impact if sqls on both schemas need sorting at same time

10) any impact if both the schemas uses DBLink to other databases? One in a single instance both will share the Net*8 right?

Overall, my interest is to figure out the affected areas if we move two related schemas (which are in separate database instances as of now) to a single database instance.
11-03-2005, 10:04 AM
mrchrispy

Quote:

Originally Posted by davey23uk

why? a single database takes up less resources than 2 databases.

Twi separate schemas in a single database is the cleanest option

I know where your coming from but if they are 2 entirely different applications (with different business owners, business functions and such) I thinks it best to split them. Having them on the same server is bad enough (e.g. if one instance is burning up CPU) but if they are sharing the instance it'll be all the more difficult to isolate any problems.

I just don't think its worth the hassle doing this with two production systems.
11-03-2005, 10:10 AM
robertbalmer

if one schema is your metadata and SMALL...cache it
if its a pre-live/load schema I would do nologging on tbs's
AND keep undo_tbs as a non-system tbspace

I work with 4 schemas in one db. No problems the past 4 years
11-03-2005, 10:35 AM
davey23uk

Quote:

Originally Posted by mrchrispy

I know where your coming from but if they are 2 entirely different applications (with different business owners, business functions and such) I thinks it best to split them. Having them on the same server is bad enough (e.g. if one instance is burning up CPU) but if they are sharing the instance it'll be all the more difficult to isolate any problems.

I just don't think its worth the hassle doing this with two production systems.

fair enough, judgement call needs to be made for each case then, but Id certinaly like to start with trying to put things together, my database here has 140 schemas in it without problem really, works for me but may not for others
11-03-2005, 03:04 PM
KenEwald

Here's my general rule: Consolidate to share, Distribute to manage.

There's nothing better than having the data you need to share under the same memory (cache) structures.

Taking a system (application in your case) down that doesn't need to go down is bad design.

Do these applications have the same uptime and recovery requirements?

If you need to bounce the instance for an init param change, you'll take both "applications" down.

Any instance change, will mean that you need to test both effected applications, yadda, yadda, yadda

I'm all for consolidation and I don't believe in throwing hardware at a problem. But sometimes a little extra hardware may save you a lot of headaches down the road.
11-04-2005, 10:21 AM
nirupam

Quote:

Originally Posted by mrchrispy

I know where your coming from but if they are 2 entirely different applications (with different business owners, business functions and such) I thinks it best to split them. Having them on the same server is bad enough (e.g. if one instance is burning up CPU) but if they are sharing the instance it'll be all the more difficult to isolate any problems.

I just don't think its worth the hassle doing this with two production systems.

both the applications are pretty much related and the databases uses DB Link to access each other's data
11-04-2005, 11:20 AM
roadwarriorDBA

Quote:

Originally Posted by nirupam

both the applications are pretty much related and the databases uses DB Link to access each other's data

That sounds like they should be one Database instance then. Look at Oracle Application Servers Meta Data Repository - It installs 22 schemas just for that.
11-04-2005, 11:52 AM
mrchrispy

Quote:

Originally Posted by nirupam

both the applications are pretty much related and the databases uses DB Link to access each other's data

If thats the case then is sounds like sharing an instance is the logical approach, I was thinking worst case (as I tend to do :rolleyes:).