SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple databases in ORACLE - How?


Multiple databases in ORACLE - How?

Author
Message
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5473 Visits: 4639
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.
Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1301 Visits: 1033
PaulB (8/13/2009)
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.

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.)

PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5473 Visits: 4639
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 :-D

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.
Peng7
Peng7
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 396
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:::Tongue11_QUESTION_ID:588045400346317188
http://www.oracle-base.com/articles/misc/SchemaOwnersAndApplicationUsers.php
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5473 Visits: 4639
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.
Peng7
Peng7
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 396
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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5473 Visits: 4639
cmingmon (7/29/2011)
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.


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.
vikingDBA
vikingDBA
SSC-Addicted
SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)

Group: General Forum Members
Points: 428 Visits: 929
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?
vikingDBA
vikingDBA
SSC-Addicted
SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)

Group: General Forum Members
Points: 428 Visits: 929
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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5473 Visits: 4639
vikingDBA (8/25/2011)
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).


w00t it really sounds crazy :-D

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 :-D

_____________________________________
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search