Migrating Data Warehouse Solutions from Oracle to non-Oracle Databases

1. A Jade Global White Paper Migrating Data Warehouse Solutions from Oracle to non-Oracle Databases A step-by-step How-To Guide By Manoj Machiwal, Consulting Manager w w w . j a d e g l o b a l . c o m

2. 1 TABLE OF CONTENTS Preparing A Migration Plan02 Before You Migrate 04 Capturing the Source Database 05 Creating and Customizing the Oracle Model 05 Migrating the Database 06 Testing the Oracle Database 06 Deploying the Oracle Database 07 Case Study 10 w w w . j a d e g l o b a l . c o m

3. 2 Though there are many standardized data warehouse solutions in the industry today, many organizations want to migrate their data warehouses to Oracle based ones primarily because of its widely accepted user base and superb support. This whitepaper is a step-by-step guide for migrating non-Oracle database solutions to Oracle ones. Preparing a Migration Plan The first step in a migration process is to identify databases that are to be migrated and applications that access the database. This step must go hand-in-hand with evaluating the business requirements that make use of these databases and also defining the test criteria.  Determining the requirements of Migration Project Listed below are some key questions that need to be answered to better assess the scope of the project, What third-party databases are being migrating? What are their versions? What is the character set of the third-party database? What source applications would be affected by migrating this third-party database to an Oracle database? What is the third-party application language and its version? Will the applications be re-written or modifiedto work with an Oracle database? - - - - - -  Analyzing the Operational Requirements This step identifies whether the migrating database is simple or complex.The table below serves as a guideline for distinguishing between simple and complex scenarios. w w w . j a d e g l o b a l . c o m

4. 3 Complex Large Database (> 1TB) Data warehouse Large applications (with more than 100 forms and reports) Distributed deployment Larger business users (> 50) High availability requirement Simple Small Database (< 1 TB) Simple OLTP Database Small applications(with few forms and reports) Centralized deployment Less business users (< 50) Average availability (during business hours) Once anenvironmental scenario is identified, its operational requirements can be easily identified and taken into account while developing the project plan. The new environment may also need additional resources such as new hardware or added system resources and these have to be accounted for. A backup schedule for the new environment also needs to be determined.  Define the testing and acceptance criteria  Analyzing the application Determine whether changes to the application are required to make them run effectively on the destination database. Identify the number of connections to the database that are in the application and modify these connections to use the Oracle database. Identify the embedded SQL statements that should change in the application before it can be tested against the Oracle database. - - - A summation of all these steps should eventually lead to a well-defined project plan for the migration. w w w . j a d e g l o b a l . c o m

5. 4 Before you migrate Zeroing on the Migration Technology Tool Evaluation - Identifying migration tools that match the project requirements closely is very important. A proof of concept for each of these tools is recommended. The following tools can automate the Migration process, SQL Developer Data warehouse builder Custom Scripts, SQL Loader Heterogeneous Services o o o o Decision Factor - Migration tools can be selected by rating them on factors listed below. Efficiency Data Loss Data type mapping Effort involved o o o o A ratings comparison would serve as a guideline for identifying the right tool. w w w . j a d e g l o b a l . c o m

6. 5 Capturing the Source Database Before migratinga third-party database, information from the database must be extracted. This information is a representation of the structure of the source database called the Source Model. The process of extracting the information from the database is called capturing the source database. Capturing a source database involves creating a source Model ER Diagram, list of objects, type of objects, data types and all other database environment information. Most of these tasks can be automated by using a migration tool. A model can be created reverse engineering the data model in the source database. The appropriate Oracle model can be determined by identifying all the external interfaces to the database. Creating and Customizing the Oracle Model Before migrating to a third-party database, an Oracle Model must be created. The Oracle Model is a representation of the destination database structure. To obtain best results from the migrated database, an Oracle Model can be customized to use specific Oracle database features such as multiple tablespaces. Based on the Oracle database version, correct storage and database initialization parameters should be identified at this point. For better database performance, best practices such as collecting statistics, defining portioning strategy, defining indexing strategy and index monitoring scripts should be implemented. w w w . j a d e g l o b a l . c o m

7. 6 Migrating the Database To migratethe source database, a customized Oracle data model must be created by implementing the steps for capturing the source database. Oracle recommends that the databases should be migrated in the following order: Migrate tablespaces, users, and user tables Migrate data Migrate the remaining schema objects - - - If no migration tools are being used, then all migration scripts can be created using SQL loader, or PL SQL scripts. These in turn can use external file features to read the source data from flat files. Programs should be written to convert data in the source database to a flat file format. Should this be not done, heterogonous services in the oracle database should be defined which can directly connect to the source database. All migration scripts should also be tested for performance as well. Testing the Oracle Database During the testing phase, the application and the Oracle database are tested to ensure, Migrated data is complete and accurate Applications function in the same way as the source database Both the source and Oracle databases are producing the same results All applications and the Oracle database meet the operational and performance requirements - - - - w w w . j a d e g l o b a l . c o m

8. 7 A collection of unit and system tests from the original application may already exist whichcan be used to test the Oracle database. These tests should run in the same way as the tests against the source database. This section focuses only on the database testing. However, regardless of added features, it should be ensured that the application connects to the Oracle database and that the SQL statements it issues produces the correct results. Testing Methodology Many constraints shape the style and amount of testing that can be performed on a database. Testing can contain one or all of the following, Simple data validation (Records count in source and destination database) Full life cycle testing and addressing of individual units System and acceptance testing - - - Deploying the Oracle Database Rollout Strategies The strategy for migratinga third-party database to an Oracle database must take into consideration the users and the type of business that may be affected during the transition period. For example, the Big Bang approach can be used when enough systems aren’t available to run the source database and Oracle database simultaneously. On the other hand a phased approach can be taken to ensure that the system is operating in the user environment correctly before it is released to the general user population. w w w . j a d e g l o b a l . c o m

9. 8 Phased Approach Using the phased approach, groups of users are migrated at different times. The selected users should represent a cross-section of the complete user-base. This approach allows profiling users as they are introduced to the Oracle database. The system can be reconfigured such that only selected users are affected by the migration and unscheduled outages only affect a small percentage of the user population. This approach may affect the users that are being migrated. However, because the number of users islimited, support services are not overloaded with issues. The phased approach allows us to debug scalability issues as the number of migrated users increases. However, using this approach may mean that data must be migrated to and from legacy systems during the migration process. The application architecture should also support a phased approach. Big Bang Approach Using the Big Bang approach, all the users are migrated at the same time. This approach may cause schedule outages during the time you are removing the old system, migrating the data, deploying the Oracle system, and testing that the system is operating correctly. This approach relies on testing the database on the same scale as the original database. It has the advantage of minimal data conversion and synchronization with the original database because that database is switched off. The disadvantage with this approach is that it can be labor intensive and disruptive for business activities due to the switch-over period needed to install the Oracle database and perform the other migration project tasks. w w w . j a d e g l o b a l . c o m

10. 9 Parallel Approach Using the Parallel approach, both the source and destination Oracle databases are maintained simultaneously. To ensure that the application behaves the same way in the production environment for the source and destination databases, data is entered in boththe databases and data results are analyzed. The advantage of this approach is, if problems occur in the destination database, users can continue using the source database. The disadvantage of the parallel approach is that running and maintaining boththe source and the destination database may require more resources and hardware than other approaches. w w w . j a d e g l o b a l . c o m

11. 10 Customer Case Study Industry – Manufacturing Customer Annual Revenue – 3000M Our clients were running their large data warehousing system on IBM redbrick and wished to migrate this to oracle 11g. As part of this migration they wanted to achieve, Hardware migration to a new version of HP-UX Data warehouse migration to oracle 11g from redbrick An easy to scale Oracle 11g environment that allowed higher transaction levels without affecting performance levels Mitigating the risk of rare skilled workers for redbrick - - - - Many tools were evaluated and finally custom scripts were selected for migration, which utilized external files and PL-SQL.We had extracted data from the source database in the form of flat files and used them to load data into the destination database using External files and PL-SQL. The parallel approach method was used for deployment considering the large user base and critical business requirements. This led to better testing on parallel systems as audit reports were generated and compared with both oracle and redbrick system to make sure the migration was successful. w w w . j a d e g l o b a l . c o m

12. 11 About the Author Manoj Machiwal is a Consulting Manager at Jade Global. He has over 11 years of experience in Oracle DBA and Oracle Apps DBA. Manoj is a specialist in Upgrades, Migrations, High Availability and Performance, Datawarehouse architecting and Proactive Monitoring. Manoj has worked for some major names in the industry including Solyndra, Orbotech, Gallo Wineries, SiliconStorage Technology (SST), Telegent Systems, Riverbed Technology, Cisco Systems and Merrill Lynch. He is also an Oracle Certified Professional DBA . Manoj can be reached at manoj.machiwal@jadeglobal.comand on +1-408-899-7217. w w w . j a d e g l o b a l . c o m

13. 12 Jade Global, Inc. Worldwide Headquarters 1731 Technology Drive Suite 350 San Jose, CA 95110 www.jadeglobal.com Northeast 1900 West Park Drive Suite# 280 Westborough, MA 01581 Pune, India E-Space IT Park, A-3, 2nd Floor 102 D/E, Wadgaon Sheri Pune-Nagar Road, Pune 411014, India New Delhi, India JMD Regent Square M.G. Road, Level 6 Gurgaon, Haryana 122002, India Worldwide Inquiries +1-408-899-7200 pr@jadeglobal.com Copyright © 2011. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchant ability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. w w w . j a d e g l o b a l . c o m