Multiple databases in ORACLE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » Multiple databases in ORACLE - How?

27 posts, Page 2 of 3

««1 2 3 »»

Multiple databases in ORACLE - How?

Rate Topic

Display Mode

Topic Options

Author

Message

PaulB-TheOneAndOnly

Posted Thursday, August 13, 2009 5:02 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 3,016, Visits: 4,466

Bruce W Cassidy (8/13/2009)

You can, of course, create multiple Oracle instances. There used to be a great deal of overhead in doing this, but with modern versions of Oracle that's far lower. The only real overhead is in the amount of system database area per instance.

Unfortunately statement above does not reflects reality.

Multiple instances DO add a lot of overhead no matter Ora version, anything in between 17 to 30 daemons running per instance plus all SGA/PGA instance specific memory segments.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #770569

Bruce W Cassidy

Posted Thursday, August 13, 2009 5:18 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

PaulB (8/13/2009)

Bruce W Cassidy (8/13/2009)

Yes and no. It's true you get a different SGA/PGA per instance, just as you do with the caches for SQL Server across different instances. However, Oracle uses shared code for the daemons (or Services in Windows parlance), so the overhead for the shared code isn't as big.

If you're creating Oracle instances on Windows, you can share one Oracle listener across all of the instances, so you just end up with a couple of services per instance.

Yes, there is overhead. But it's not as large an overhead as it used to be (thinking back to earlier versions of Oracle.)

Post #770577

PaulB-TheOneAndOnly

Posted Friday, August 14, 2009 4:48 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 3,016, Visits: 4,466

Bruce W Cassidy (8/13/2009)

Oracle uses shared code for the daemons (or Services in Windows parlance), so the overhead for the shared code isn't as big.

Sorry, on this one you are dead wrong man BigGrin

Providing all your instances point to the same ORACLE_HOME it's true that you are going to end up with a single set of binaries BUT -notice the uppercase but? - Oracle starts one PMON per instance, one SMON per instance, etc, etc, etc. therefore you end with as many as 30 daemons per instance.

Bruce W Cassidy (8/13/2009)

If you're creating Oracle instances on Windows, you can share one Oracle listener across all of the instances

You CAN... but you don't want to do it so pretty please allow at least one listener per instance.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #771375

Peng7

Posted Thursday, July 28, 2011 11:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 10:30 PM
Points: 6, Visits: 184

I know this topic is fairly old, but came upon it as we are having similar issue. Our issue is that we have 6 different plants running 6 different databases with identical database architecture. The difference is the data that resides in the database at each plant. We also use OS authentication for database logons and some users may have access to more than 1 database. I was tasked with coming up with a plan to consolidate the 6 databases into a single database.

The database and software we use were developed by a software vendor which uses synonyms for each user to access the schema. There are synonyms created for every object that exists for the schema owner (developed by software vendor). This caused additional maintenance for us, as we had to run a script to create synonyms each time a new user is created. Additionally, this way of accessing objects would prevent us from consolidating the 6 databases because of the "duplicate" synonym names that would be required to move each plant's objects into its own schema.

After researching for the past couple days (it took me awhile coming from SQL Server and still learning Oracle), I am thinking of dropping all the synonyms that are used to access the software vendor's objects and making use of a logon trigger to alter a user's session to another schema (permissions do not change). For example:

IF ... EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=PLANT1';
IF ... EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=PLANT2';
etc...

Here are some addtl articles:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:588045400346317188
http://www.oracle-base.com/articles/misc/SchemaOwnersAndApplicationUsers.php

Post #1150463

PaulB-TheOneAndOnly

Posted Friday, July 29, 2011 8:39 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 3,016, Visits: 4,466

Little confused... there are six Oracle "instances" each one running a "database" or there is one Oracle instance with six operational "schemas" each one of them equating one "database"?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #1151000

Peng7

Posted Friday, July 29, 2011 8:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 10:30 PM
Points: 6, Visits: 184

There are currently 6 different servers, 6 instances, and 6 databases. Each instance/database are on its own server. The task is to consolidate the 6 databases into 1 to reduce resources (maintenance, license fees, hardware associated costs, etc). Looking at the current, we are looking to create only 1 instance to connect to the consolidated database.

Post #1151009

PaulB-TheOneAndOnly

Posted Friday, July 29, 2011 10:13 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 3,016, Visits: 4,466

cmingmon (7/29/2011)

Got it.

The easiest way to go is to deploy each database on its private schema, probably having a set of tablespaces for each one of them. This solution avoids - or at least minimizes the need to "touch" the application side.

Your proposed solution may work indeed! Any chances of testing it? ... if it works for two "databases" it will work for six.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #1151078

vikingDBA

Posted Thursday, August 25, 2011 1:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:34 AM
Points: 189, Visits: 864

I don't know how crazy this is going to sound, but here goes:

Create one database, with one set of tables. In each table, put two additional columns, one for user and one for DatabaseID (I can't remember what the different databases stood for, so I'll use DatabaseID as the column name).

Add another table, so that each user has a list of DatabaseID's associated with their usernames (windows authenticated user names, USER in t-sql). Upon entering the program, they choose what DatabaseID to use (from the list that is associated with them), and assign that to @dbidtouse, for example, for use below.

Use row-level security with views to restrict their access to the data in the table and in the program, put an extra where clause on the select statement to get the data pertaining to only the DatabaseID they are interested in at the time.

SELECT * FROM vSomeTable WHERE DatabaseID=@dbidtouse

Grant select on the view to all users, but in the view:

CREATE VIEW vSomeTable
AS
SELECT * FROM vSomeTable WHERE tableUserName = USER

This would make it where they could never see any data other than their own, even with a select statement that is openended. (Don't give access to the underlying table, just the view.)

As stated above, the program would further restrict the results by putting the "WHERE DatabaseID=@dbidtouse" clause on the view's select statement to narrow it down to just the DatabaseID they are interested in.

As they want to delete DatabaseID, just delete rows in the underlying tables with user=USER and DatabaseID=databaseToDelete.

Oracle has the same thing as USER, can't remember what it is.

I think that would work in Oracle or SQL Server. Would that solve the problems?

Post #1165690

vikingDBA

Posted Thursday, August 25, 2011 1:36 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:34 AM
Points: 189, Visits: 864

OOOps! More craziness:

View should be:

CREATE VIEW vSomeTable
AS
SELECT * FROM SomeTable WHERE tableUserName = USER

(This select is from the underlying table, sorry).
Of course it would be better to list out the column names on the select in the view, and not list the USER and DatabaseID columns, hiding them from the users.

Post #1165707

PaulB-TheOneAndOnly

Posted Saturday, August 27, 2011 11:17 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 3,016, Visits: 4,466

vikingDBA (8/25/2011)

it really sounds crazy BigGrin

The fact that"it will work" doesn't mean that's the best solution.

Let me ask this, taking into consideration that you are running "n" databases on the same set of tables, obviously on the same instance.

Acme Corp, which data is in Database "n=3" wants the database recovered as it was yesterday at 3:15PM EST... how in the world are you going to recover it without trashing the other "n-1" databases running in your "shared" system? Cool

Nice try

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #1166610

« Prev Topic | Next Topic »

27 posts, Page 2 of 3

««1 2 3 »»

Permissions