July 29, 2011 at 8:55 am
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.
July 29, 2011 at 10:13 am
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.August 25, 2011 at 1:15 pm
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?
August 25, 2011 at 1:36 pm
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.
August 27, 2011 at 11:17 am
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 😀
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? 😎
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.August 29, 2011 at 6:07 am
That was not stated as a requirement.
August 30, 2011 at 7:25 am
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.
September 2, 2011 at 7:18 am
vikingDBA (8/29/2011)
That was not stated as a requirement.
Backup Recovery is not "stated as a requirement"? :w00t:
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.September 2, 2011 at 7:21 am
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.January 25, 2012 at 1:25 pm
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)
February 24, 2012 at 7:42 am
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.April 30, 2012 at 12:04 pm
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.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply