Migration Challenge 200+ GB to new host platform

We are planning a project to transfer our OLTP application from a system, which is aging, to a newer platform. The database associated with the application is approximately 200 GB in size. Tables range in size from 50 rows (state codes) to 100+ million. The majority is in the 10 to 50 million-row range. All tables are heavily indexed with multi-value keys. Along with completing the task of moving the data, we intend to use this as an opportunity to perform other maintenance tasks. The tables and indexes need work such as re-fragment, re-calculate extents, and compression of spaces, which can not be done with a methodology of a simple archive and restore. The archive and restore method would move the database just as it exists, and we want to clean it up along the way to the new platform. Constraints, which face the team, include such things as minimal downtime, no impact to the production system, day to day processing can not stop, and so on. Basically get this done while the current production system continues to do it's job. In doing this work without the archive and restore method, we have a plan which restores the data to a test system to be unloaded table by table and loaded to the new system. With the constraint that the current production system keeps working with its data changing every day, the challenge to know that the old and new systems are in sync at the end is huge. We have a skilled programmer who has studied the documentation associated with the logical logs and understands what is captured in them. The programmer tells me that the operations of insert and update will be not difficult to pull from the log tapes and decipher the transaction and then create SQL code to re-apply each transaction on the new system. It seems at this time that the delete operations are not as clear and easy to decipher. The programmer tells me that the delete operation appears to basically offset a number of bytes into the partition to a specific location and then writes to blank the information and make that page slot available for a new record. Daily our batch processing will delete many rows making it crucial to capture these actions along with the update and insert transactions. We are ignoring in the log files actions which occur on an index, we only want to capture data changes to send to the new platform. We know we could create a delete trigger on each table but, are concerned with the impact this would have on the system throughput and response time. I was hoping that people who have faced with this sort of system migration challenge would share knowledge and advice. Thanks in advance for your help. Eugene D. Kays Engineering Manager - Unix Platforms Harrah's Entertainment, Inc. ekays@harrahs.com

"Challenge" is right, eh? Enterprise replication might just be your best bet. Based on the fact that you're 24x7, I'd say you'll need to do the backup-on-old/restore-on-new, at least initially. Not knowing all the specifics of your environment, here's how I'd look at doing things: - Take a full backup of the existing system - Restore this backup on the new system - Establish enterprise replication between the two systems, and sync 'em up - Once synched, break the replication on the "new" system side (so that the old system is still live). If properly configured, the transactions on the old system will start queuing up. You'll need heaps and gobs of free disk space for this -- be warned! - Now do your dbexport on the new system. - Edit the SQL file generated by dbexport to fix your extent sizes, fragmentation, etc. (This is the hard part). Make sure you're leaving enough room for growth, and use low fillfactors on indexes. - I imagine you'll be wanting a different disk layout on the new system than you had on the old, so I'm guessing that you'll want to tear down and reinitialize the new instance (which needed to match the old for restore purposes) with a new layout. Otherwise just drop the database on the new system. - Do a dbimport using your new SQL file. - Assuming this worked (it might take a try or two, with syntax errors, etc.), re-synch the data using Enterprise replication. - Once the data is synched and verified, you can send the old system the way of the Model "T." I'm sure you'll need to customize this plan to suit your specific needs, but on electronic paper, it works. Hope this helps! - Tom Girsch tgirsch@iname.com Eugene Kays wrote in message <36f949ae@hwilkins.harrahs.com>... >We are planning a project to transfer our OLTP application from a system, >which is aging, to a newer platform. The database associated with the >application is >approximately 200 GB in size. Tables range in size from 50 rows (state >codes) to 100+ million. The majority is in the 10 to 50 million-row range. >All tables are heavily indexed with multi-value keys. > >Along with completing the task of moving the data, we intend to use this as >an >opportunity to perform other maintenance tasks. The tables and indexes need >work such as re-fragment, re-calculate extents, and compression of spaces, >which can not be done with a methodology of a simple archive and restore. >The archive and restore method would move the database just as it exists, >and we want to clean it up along the way to the new platform. > >Constraints, which face the team, include such things as minimal downtime, >no impact to the production system, day to day processing can not stop, and >so on. Basically get this done while the current production system continues >to do it's job. > >In doing this work without the archive and restore method, we have a plan >which restores the data to a test system to be unloaded table by table and >loaded to the new system. With the constraint that the current production >system keeps working with its data changing every day, the challenge to know >that the old and new systems are in sync at the end is huge. > >We have a skilled programmer who has studied the documentation associated >with the logical logs and understands what is captured in them. The >programmer tells me that the operations of insert and update will be not >difficult to pull from the log tapes and decipher the transaction and then >create SQL code to re-apply each transaction on the new system. It seems at >this time that the delete operations are not as clear and easy to decipher. >The programmer tells me that the delete operation appears to basically >offset a number of bytes into the partition to a specific location and then >writes to blank the information and make that page slot available for a new >record. > >Daily our batch processing will delete many rows making it crucial to >capture these actions along with the update and insert transactions. We are >ignoring in the log files actions which occur on an index, we only want to >capture data changes to send to the new platform. We know we could create a >delete trigger on each table but, are concerned with the impact this would >have on the system throughput and response time. > >I was hoping that people who have faced with this sort of system migration >challenge would share knowledge and advice. > >Thanks in advance for your help. > >Eugene D. Kays >Engineering Manager - Unix Platforms >Harrah's Entertainment, Inc. >ekays@harrahs.com > > >