Dear blog readers,
I’m working on a small story about database consolidation and interested to learn what are success and failures that others are going through. While we have our own experience at Pythian, I find it interesting to learn about what others are going through. If you have enough details, it would be nice to see your feedback along those lines.
1. Why consolidation project started – targets?
2. What were expectations / success criteria and how they were set?
3. What was the scope of the consolidation project.
4. Expected time-frame and whether you are done by now.
5. Was the project considered successful? Goals met (see item 2)?
6. What were the measurements before and after? Were there any?
7. Issues faced and how they were solved or worked around.
….
Interesting facts like platform, number of databases, versions, consolidation strategy and etc.
….
Sharing your experience here would be beneficial for the community at large. Besides, don’t you want to win a book?
I myself contributed the first chapter to this book but the rest of the authors are really awesome! ;-)
To win the book, you need to share your experience and provide details. Addressing the items I mentioned would be great but if you don’t have the whole picture, you might miss some of it so just tell us your story. Of course just few sentences won’t qualify you for a story teller so I’ll use 1000 characters as a guideline threshold to qualify for a draw but I won’t follow it blindly — insights into your consolidation project is what counts!
Don’t forget to enter a proper email address (remember that it’s not shared) when entering a comment so that I can follow up in case you get the prize.
Oh… and there is a deadline! You get time until tomorrow (at the time of writing) – 11:59pm EST 18-Jan-2011. Feel free to share even later but the prize will be gone by then!
Thanks in advance for all your comments!
Alex
8 Comments. Leave new
Consolidated our Hyperion and DW dbs into one instance.
1(Why): Limited number of lpars on our p-series. Each instance then needs maintenance, temp, undo, redo, online backups, DR-allocation and so on. All those costly on resources. Since Hyperion accesses the DW anyway, a cross-schema access is much faster and much less troublesome than a db-link access.
Consolidating means the backup window is shared, the redo archiving and recovery in DR can be simplified and processes can be merged. And we do not have the infra-structure to run these as multiple RAC instances, which would be the other option.
2(Expectations and success criteria): Ensure that Hyperion processing would not be affected by DW processing other than in memory and CPU resources, which can be easily apportioned if needed using execution profiles. Ensure that any contention at I/O level was minimized.
3(Scope): Hyperion and DW production and DR databases in same instance, with a potential for further consolidation of other databases that share DW data.
4(Expected timeframe and completion): Project started mid-2008 with completion before start of 2009, finished on time.
5(Was it successful): Yes, all goals met. Statspack and later AWR show no contention for any resources.
6(Measurements): Statspack used on both development and test instances to gauge performance impact and execution profile of both databases. This was then used to ensure that peak periods in normal daily execution didn’t clash with each other. Mostly to ensure that at a major DW table read time in Hyperion, DW wouldn’t be writing in those tables. In other words: no conflicting concurrent access to any common tables.
7(Issues faced): Temp contention resolved by assigning a specific temp tablespace to Hyperion use. All Hyperion tablespaces assigned to a different set of disks (LUN) in the SAN. Undo is currently shared, statspack and AWR show no contention there.
Hyperion requires specific init.ora parameters to be set for optimum performance. These can be set at session level, so we have a login trigger that detects Hyperion users and performs an “ALTER SESSION SET =” for all those. This same triger also does a couple of other minor security tasks related to roles and default schemas. No public synonyms are used anywhere to ensure no possibility of tables of the same name being accidentaly overwritten.
HTH
Thanks mate! You are the first one to enter the draw!
[…] want to mention that my experiment on collecting database consolidation stories was a *relative* success — we’ve got slump of participants and I can announce the book […]
Not sure if this counts as a consolidation project, with us it is an ongoing effort:
1. We are trying to save on processor (EE+DP+TP) licenses and therefore cramming as many database instances as will fit on a single host. Consolidating by putting more stuff into a single database is not an option.
2. No strict criteria… but our goal is about 15 instances per server. Preferably only one Oracle version on every host.
3. Currently… 6 hosts, about 70 databases.
4. This will likely go on forever. Whenever hosts are replaced or new versions are released we re-think the landscape. Trying to fit all our requirements while keeping migration efforts to a minimum.
5. At the moment it is satisfactory but not great. One of the 6 hosts above is in the process of being replaced, things will be fine once that is achieved.
6. No real measurements. Things working without customers reporting performance problems (due to lack of resources) counts as a success.
7. We have recently made a lot of effort to standardize things so that moving a database between hosts takes as little work as possible, in particularly involves no client-side changes. This was a complete success.
Every now and then an application comes along which grows fast and therefore demands a lot more CPU/memory for its database than expected. Either combine it with smaller / less used instances on the same server or (in exceptional cases) put them on a host of their own. Once, we built an additional archive schema to keep productive tables small and usable (no partitioning option).
So far, things mostly work out to our and the users’ satisfaction.
Best regards
Ingrid
Thanks for your story GiantPanda.
I have consolidated databases sitting on there own servers into a 2 clusters/Grids : One 6 node for OLTP ( Sun T5240 T-Grid and 2nd a 3 node M-Series cluster ( M-GRID )for OLAP kind of applications.
Version : 11gR2 Cluster used SCAN
On the 6 node cluster , 2 nodes are for Individual DAtabase one for Dev one for test , rest for Dev RAC and Test RAC 2 each dedicated.
Each cluster can be failed over to another, mean if for some reason T-Grid goes down , I can bring all the databases on to M-Grid and Vice-versa.
As I use SCAN , I can move around databases without effecting the clients.
Thanks for sharing Ravi.
I know I am not going to win the book. Well, I am glad to share my experiences. In a big database consolidation project, we reduced the database number from 6k+ to about 1K. Not only the number, but also the version, we decreased oracle versions from 10+(from 7.x to 11g) to 2 main versions(10g,11g). Although there are tons of problems during the process, I think it is a great success both for the company and DBA. It’s much easier from operation perspective. Of course, there are side affects. The DBA positions are reduced after the consolidation.