Oracle to MySQL Migration - doc.ispirer.com

1 Copyright to MySQL MigrationStoredProcedures,Packages,Trigg ers,ScriptsandApplicationsWhite PaperMarch2009, Ispirer Systems todescribe the factorsthat impactmigrating databasesandapplications fromOracle to factors willbedetailed,as wellastools andmethodologies tohelp achieve a higher very truethat the Sun MySQL databasecan dramatically reduce the databaseTotalCostofOwnership(TCO)foracom pany bylowering license, hardwareand largest risk in moving tothe MySQLplatformis the riskand complexity ofmigrating business logic from Oracle ,particularlywhen existing applications makesignificantuseofPL/SQLprocedures,tri ggers, packagesand Oracle specific Migration from Oracle to MySQL can be troublesome, time consuming, and , proven methodologies and tools canreduce the cost andtime requiredand cansignificantly mitigate thehelp ofthe Migration product SQLWays, amigration canbe assessed, planned,and properly automated.

2 Withthe proper use ofautomated tools anda strong project management process in place, companies can incursavings ofover 70%compared to MySQL , automated Migration becomes a very attractive Oracle database provides veryadvancedcapabilities to develop applicationlogic thatresides entirely inside the databaseusing PL/SQL storedprocedures, functions,packagesand PL/SQLis an easy-to-useand powerfulprocedural extensionto SQLthatis stronglyrecommended by Oracle for performance most applications, useofPL/SQLnaturallyleads to asignificantlylarge numberof procedures, packages, and , although having some similar functionality, does not makeuse , PL/SQLoffersmany non-ANSI compliant features,includingfeatureswhichareonlyfo undinOracle. TheseOracle specific featuresinclude: Packages-shared package variables, built-in packages %TYPE, %ROWTYPE, exceptions Object-orientedfeatures: object types, functions,and collections Business intelligence and XML features etcCopyright MySQLmigrationcanbe a verychallengingprocess,particularlyifOra clespecific features are inuse,suchas theonesdescribed ,such amigrationcouldberelativelyeasy and would be the case ifthe targetdatabase contained a relativelysmall amount oftables andsimple business vary fromprojecttoproject,itis important to fulfill purpose of the assessmentisto definethescope, feasibility, cost and risk associated inmigrating from an Oracle databasetoa MySQL based ,you need todefinethe typesofdatabaseobjectsandhowmanyofthemyo u willneed to are items such as the following.

3 Tables Views Procedures Functions Packages Triggers Sequences, you need to convertPL/SQL code (procedures,packages, functionsand triggers) orview/queries containing Oracle specific SQL syntax, youhave toinvestigate what featuresare used and definethe number oftheir items that need to beaccounted for are: Non-ANSI compatibleSQL functions, operators andstatements Results sets Cursor loops Exceptions Temp tables Object types andfunctions CollectionsCopyright DynamicSQL Built-in packages OLAP functions XML functions youhave finished theexamination,itisbesttoselect MySQLequivalentsorsolutionsto replace Oracle specificfunctionality. You canfindtypical solutions in AssessmentBesides schema and server-side business logicconversion, you may also need tomodifySQLstatements within vital toassess howmuchofthis work will needto be doneto complete the ,youhavetocheck whatdatabaseAPIis usedinyourapplications toaccesstheOracle important to notehowmanyapplicationsource files containOraclespecific code and therefore need tobemodifiedto use astandardAPIlikeODBC, JDBC, toaccess Oracle , butsomeapplicationsmay use anativeAPIlikeOracleOCIor Pro*C/C++.

4 Collectingall astandardAPI,suchasusingODBC/JDBC drivers,significantchangesmayneed to be made to example DECODE functionsorlegacyleftouter joinsyntax(*)will need tobe modified. Itisrecommended to estimate the numberofnative SQL touse anativeAPIlikeOracleOCI, you willneed tocompletelyredesign the databaseaccess codeto use the MySQL API or ToolsItisimportant tounderstand howmuch useismade ofspecific database features. How bestis a featureuse assessment performed?Start firstbycalculating thenumber of tables, procedures,views thetablebelow. For a more detailed analysis, you can use Ispirer s SQLWays product to collectcomprehensive following is a sample assessment:DatabaseNumberTables350 Views280 Procedures420 Functions135 Triggers50 Packages10 DatabaseDetailsBLOBs37 Outer joins155 Ref cursors89 Exceptions450 Temp tables34 Copyright filesOuter joins190 SQL functions356 Result sets47 MigrationApproachAutomated conversionBased on the assessment results, you can thendevelop the Migration plan.

5 Ifyou havedozensofprocedures, you mightconsider a manual conversion, butif hundreds orthousands of procedures need tobe migrated,itis besttoexamine cost and risk associated with the conversion project depends on the scope to notethat thecostand riskare alsoimpacted bythe diversity andfrequencyofOracle featuresin useinthedatabase and more Oraclefeatures inuse,the more complex and costly the , the more Oraclefeatures inuse,the more automated tools could help achieve and DDL Migration CostMigration of dataand DDL (Schema)objects are typically done veryeasily, as there aremany toolson the market that can assist you with this DataandDDLmigrationinvolvesconversionof Datatypes Constraints (primary and foreign keys, uniqueconstraints,NULL, defaults etc) Data transfer IndexesAlthough there are syntax differencesinOracleand MySQLDDL statements, both havesimilar datatypes (character,number,date, time,LOBs) and allowyouspecifying similarintegrity DDL/Datamigration estimation:DatabaseTables<100 tablesLOBs10 columnsMaxtable rows<10 MMax table size<300 MbMigrationProcessAssessment2-8 hrsMySQL configuration4-16 hrsAutomated transfer2-4 hrsTesting,configurationchange, next iteration4-12 hrsTotal time12-40 hrsCopyright , or less than$500 Due to automation, thecostofmigratingDDL and dataisnot directlyproportional to thenumber of tables anddata size.

6 For example, the cost ofmigration for 100 and300 tablescan be similar in the cost, if the tableshave similar structure and data oftables and their size increase, you mayneed to spend more time toproperly configure the MySQL database, tune datatransfer,and focus on thingslikeindexcreation Mitigation for Typical DDLandData MigrationTypical ,itispossible torunthefull database transferinevaluation mode, review data, and runapplications connectedto thenewMySQL database:This is general database transferinevaluation transfer errors, compare tables structures,thenumber of rowsinOracle and test datain all or representative tables usingSQL tools suchas OracleSQL*Plus, MySQLQ ueryBrowser,or themysqlcommandline and testthe target applicationconnectedto MySQLC hallengingDataMigrationsAlthough,ingener al,data/DDLmigrationisrelativelyeasycomp aredwithbusinesslogicconversion,there are some conditions thatcommonlyincrease the complexity of adata/DDLmigration: LargevolumesofdataIf you needto migrate large volumes of data,you may need more effort to large amount of datamayimpactthe migrationprocess,particularlyinterms of the timeittakes tocomplete the Migration .

7 In order tomitigate the time required to complete themigration, you might execute themigration in a concurrent fashion. Thisincreases thecomplexity of the , the transfer of large volume ofdatacan complicateerror handling,as you now cannotafford tore-run afull migrationifjust a few tables project might benefit from tools thatallowforbulkinsertoptions,as tools thatissue acommit after each row may be nolonger be aviable option. Minimal downtimeIn some mission-critical environments, you havetoensure downtimeiskept to meet these requirements, youmustproperlydesign the Migration process to dothingssuch asrun data transfer concurrently, ortransfer statictables out of the will needtobe used toreducedowntime. StrictperformancerequirementsCopyright environments have verystrict performance requirements for to MySQL ,itisimperative requires you tospend more time on database design and tuning, aswell as performingtest migrationsinorderto test post Migration Mitigation for Challenging DDLandDataMigrationChallengingdatamigrat ionscannotbecompleted with Proof-of-Concept Migration needs to beexecuted inorder to ensure requirements can ,the followingmigrationprocess is recommended forcomplex datamigration projects.

8 Proof-of-Conceptmigrationtocheckfeasibil ityofrequirements Test Migration to fullyemulate production Migration andrun comprehensive testing ProductionmigrationCost of BusinessLogic ConversionIfyourdatabase contains a dozen proceduresand triggers,itiseasytomanuallyrewritethemto MySQLSP ofprocedures and triggers, manualconversion is quite expensive. Youhave toconsider howanautomatedtool canassist cost ofmanual conversion is directly proportional to the numberoflines of code youneed to convert. Onthe other hand, automated tools canlimitthe cost, and make themigrationofevenamillionlinesofcode veryreasonable the lines of codetobe converted, automated conversion of business logicusing atool like SQLWays can cost7-10times less thanmanual diversity and frequency of Oracle specific features definesthe complexity of businesslogicmigrationand level ofautomationthat tools can effective automation,our experts feel thata Migration tool suchas SQLW aysmustbeable to convert over95% of thebusiness sample server side business logic migrationprojectestimationlooks as follows.

9 DatabaseStored procedures1000 Triggers300 Functions250 Packages10 (50 procedure per package)Manualconversion5,000 hrs (~30 man-months)Labor cost$50,000-$250,000 (dependingoncountry)Automated conversionAssessment, solutionsdiscussion16-40 hrsIterative conversion,analysis40-80 hrsTesting40-80 hrsTotal time96-200 hrsTools costless than$5,000-$10,000If you compare DDL/Data and business logic Migration , you can see thatthelatter canmake up to 95% ofthe total project to MySQLmigration ConversionIf there are many lines of code toconvert, and a wide diversity of database features fromOracle areused,theconversion can impose significant risk, soyouneed to take severalimportantsteps to mitigateit. ExperienceThe staff responsible for the Migration projectshould havedeveloper and administrativeskills andexperiencebothfor both Oracleand MySQL need toclearlyunderstandthe scope,challenges, tasks andstages to implement Migration successfully.

10 ComprehensiveAssessmentAttheinitialstage , youhave toperform acomprehensive assessmentof thedatabases youwant ,youwill know whatspecificfunctionalityyou needto convert,whatsolutions you willuse toreplace non-ANSI compliantOracle need todetermine if there is a solution forevery feature in use. Some Oraclefeaturesare not easyto map toa similar equivalentinMySQL,soyou may need to redesign somefunctionality. Proof-of-Concept onFull CodeBaseAutomated tools like SQLWays makeitpossible toeasilyrun conversions on the full codebase atthe beginningofmigration suggest doing this as part ofany complexmigration,as thiswill helpexposepotentialbottlenecksandbetterc larify thepercentofautomation orsuccess factor themigration tool can ,itwillmake youconfident thatconversionof heavy PL/SQLcodeis feasibleat low cost. UseAutomated Migrationas much aspossibleIn addition to its highcost,manual Migration reducesthe visibility of bottlenecks at earlystages, whichmay resultinthe need to redesign the then increases theeffort and the cost ofthe Migration even comparison,automated tools allow the conversion to be executedrepeatedly for lowcost,but with high levels of feedback.