Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Multiple databases in ORACLE - How? Expand / Collapse
Author
Message
Posted Monday, August 29, 2011 6:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:25 PM
Points: 191, Visits: 895
That was not stated as a requirement.
Post #1166872
Posted Tuesday, August 30, 2011 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, September 2, 2011 7:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Friday, September 2, 2011 7:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Wednesday, January 25, 2012 1:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 1,391, Visits: 6,351
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
Posted Friday, February 24, 2012 7:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Monday, April 30, 2012 12:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:41 PM
Points: 12, Visits: 215
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 »

Add to briefcase «««123

Permissions Expand / Collapse