<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Working with Oracle
»
Multiple databases in ORACLE - How?
27 posts, Page 3 of 3
««
«
1
2
3
Multiple databases in ORACLE - How?
Rate Topic
Display Mode
Topic Options
Author
Message
vikingDBA
vikingDBA
Posted Monday, August 29, 2011 6:07 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:19 AM
Points: 189,
Visits: 863
That was not stated as a requirement.
Post #1166872
jeremy.singer
jeremy.singer
Posted Tuesday, August 30, 2011 7:25 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 30, 2011 7:29 AM
Points: 5,
Visits: 7
While I would be inclined to use the light-weight solution of creating more schemas, oracle also allows you to create multiple independent databases on the same server. You can use an Oracle utility called DBCA (Database Creation Assistant) to do so. You can even use an existing database as a template for new databases.
Each database can be connected to separately via a different SID (SIDs usually are the same as the database name).
The advantage of the using separate databases is that, indeed, the usernames are unique within databases, but could be the same to users in other databases if that is a hangup for your scripts.
While schemas within a database can share tablespaces, storage between databases is not shared this way.
Database linking can be done between schemas in a single database or to schemas in another database.
Post #1167500
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Posted Friday, September 02, 2011 7:18 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 2,982,
Visits: 4,393
vikingDBA (8/29/2011)
That was not stated as a requirement.
Backup Recovery is not "stated as a requirement"?
Let me break some news for you, Database Recoverability is one of the five core responsibilities of a DBA therefore you must have to provide for it.
Nobody states "Data Integrity", "Consistent Performance", etc. - all of those are givens.
_____________________________________
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 #1169203
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Posted Friday, September 02, 2011 7:21 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 2,982,
Visits: 4,393
jeremy.singer (8/30/2011)
While I would be inclined to use the light-weight solution of creating more schemas, oracle also allows you to create multiple independent databases on the same server. You can use an Oracle utility called DBCA (Database Creation Assistant) to do so. You can even use an existing database as a template for new databases.
Each database can be connected to separately via a different SID (SIDs usually are the same as the database name).
The advantage of the using separate databases is that, indeed, the usernames are unique within databases, but could be the same to users in other databases if that is a hangup for your scripts.
While schemas within a database can share tablespaces, storage between databases is not shared this way.
Database linking can be done between schemas in a single database or to schemas in another database.
You are correct. Multiple instances was one of the alternatives.
It is good to note that in a multiple instance environment each new instance will start about 30 daemos therefore it is a good idea to do some capacity planning before going for this solution and find out there is not enough hardware to support it.
Hope this helps.
_____________________________________
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 #1169210
Jo Pattyn
Jo Pattyn
Posted Wednesday, January 25, 2012 1:25 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 1,329,
Visits: 4,296
Personally the road with multiple instances comes close to ms sql server databases. With schema's and tablespaces you're stuck when you need to recover more of them (as you reset the backup chain with open resetlogs)
Post #1241754
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Posted Friday, February 24, 2012 7:42 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 2,982,
Visits: 4,393
jeremy.singer (8/30/2011)
Database linking can be done between schemas in a single database or to schemas in another database.
Not correct.
DBLinks connect one Oracle instance to another Oracle instance - DBLinks have no use in between schemas of a single instance, fully qualifying the object names allows you to access objects on different schemas of the same 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 #1257416
Peng7
Peng7
Posted Monday, April 30, 2012 12:04 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 10:02 AM
Points: 6,
Visits: 183
I am not a fan of having multiple Oracle databases on the same server and will try to avoid it. As Paul B stated, it requires additional resources for each database added onto the server. Additionally, our task was consolidate into 1 database to minimize licensing costs, free up hardware for other uses, and minimize database maintenance.
To give an update, the 6 databases were successfully consolidated into a single database with 6 schemas/tablespaces back in November 2011. The users are still testing and have reported no technical/performance issues yet (related to the consolidated infrastracture). Actually, one issue we notified users prior to testing was related to merging database user accounts.
We support 2 types of database users, "regular" oracle users and "OS" authenticated users. The "regular" Oracle users are sometimes shared between multiple employees and are slowly being phased out, whereas the OS authenticated user is associated to a single employee.
The issue was with the "regular" Oracle users. For example, suppose user = User1 existed in Plant1 and Plant2 and the user had different permissions and were also associated to different employees (Employee1 used this acccount to login at Plant1, whereas Employee2 also used same account to login at Plant2). When consolidating into single database, I could not simply create User1 with same permission from Plant1 and Plant2 because an Employee would then be able to view data in another schema which they should not access to. Luckily, there were only a few of these types of accounts (around 20 or so). What we decided was to keep User1 from one of the plants and create a new user account for the other and notify the effected employee of their new user account.
Other issues in consolidation were data related changes. We had to update/insert some data changes into various tables to reflect the new server/database (i.e. dba_directories and other table data reference changes).
The downtime should be minimal because I created scripts to do the entire process. Once users give the OK, the script will be run on new production server which will import all the plants data, create necessary schemas/tablespaces, consolidate and merge current Oracle users/roles from the multiple production server to be replaced, etc.
Post #1292701
« Prev Topic
|
Next Topic »
27 posts, Page 3 of 3
««
«
1
2
3
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
