Information Technology | Databases » Database Migration, What is it and why is it important

Database Migration – What is it and why is it important? Posted by Sean Hull June 15, 2011 Migration in the context of enterprise and web-based applications means moving from one platform to another. Database Migrations are particularly complicated as you have all the challenges of changing your software platform, where some old features are missing, or behave differently and some new features are available and you’d like to take advantage of those. In the world of databases, some developers try to build database independent applications, especially using ORMs (object relational mappers). On the surface this seems like a great option, build your application to use only standard components and features, and then you can easily move to a different platform when requirements dictate. Unfortunately things are not quite that simple. Database independent applications necessarily shoot for the lowest common denominator of all of your database platforms, thus lowering the bar on what

high-performance features you might take advantage of on the platform you are currently using. Here are some scenarios: 1. Building an application which needs to support multiple database backends for customer sites 2. Building an application in dev and test for proof of concept May port to an alternate database in the future. 3. Don’t want to be locked into one vendor, but have plans for only one platform currently. These are all good reasons to think about features, and database platforms from the outset. For situation #1, you need to be most serious about cross-platform compatibility from the start. Build modules for each database platform, with platform specific code isolated in that module. If the particular feature you want to use is available only on one of the two platforms, the alternate platform will have to include its implementation of that feature in the 1 database specific module. Also by isolating all database specific interactions to one module, you have also put

boundaries around that code. If you choose to support another database platform in the future, you merely need to rewrite that database interaction module. For situation #2, you would use a similar tactic, but won’t necessarily have to implement all the routines in that module for the alternate platform. Just keep those features, and differences in mind during the development phase. Where possible document those differences, and comment code liberally. This will go a long way towards preparing you if you do decide to go for a different database backend. In situation #3, this may be more of a philosophical concern at this stage. Don’t get overly dragged down by this, as it’s hypothetical at this stage. Sometimes developers labor under this concern from previous bad experiences migrating to a new database platform. But to some degree this is the nature of the beast. Database platforms include a myriad of different features, datatypes, storage methods, and coding languages. In many

ways this is where their power lies. ---------------------------Migrating MySQL to Oracle Posted by Sean Hull January 1, 2008 This article is from 2006. MySQL has come a long way since then MySQL 55 is very robust and feature rich, matching Oracle in many different areas including datatypes, stored procedures and functions, high availability solutions, ACID compliance and MVCC, hotbackups, cold backups and dumps, full text and other index options, materialized views and much more. Here’s a high level mysql feature guide What really separates the two technologies is cultural. MySQL, rooted in the open source tradition is much more do-it-yourself, leaning towards roll your own solutions in many cases. Meanwhile Oracle provides named and proven paths to solve specific problems. You might also check out: Migrating MySQL to Oracle Guide which is a larger multi-part series & work in progress. 2 For some basics What is a Database Migration? Lastly these may be helpful – Migration

to MySQL – Limitations, Anomalies, Considerations & Strengths and also Oracle to MySQL Migration Considerations INTRODUCTION MySQL is a powerful database platform for many simple web-based applications. Some of it’s power and speed comes from it’s simplicity. MySQL is actually a database without proper transactions. What this means in terms of speed is dramatic However it also means you cannot rollback an update which encounters problems halfway through, and other sessions in the database will immediately see changes. There are many dramatic ramifications of this, as we’ll discuss later. Lastly there are limitations on dataset size Oracle can obviously handle tables of a terabyte and larger. However since MySQL implements a table as one file, filesize limits as well as internal data organization, and indexing can become major limitations as tables grow to the millions of rows and higher. When you begin to hit these limitations, whether in your application complexity, or the

size of your data, you may consider moving your data and application to the Oracle Database. There you will have a rich set of features both on the programming side with stored procedures, views, materialized views, triggers, and so on. You will also have support for tables and indexes of virtually limitless size, full transaction support, and even sophisticated High Availability features such as Advanced Replication, Data Guard, and even clustering with Oracle’s Real Application Clusters technology. With all these enticing features, and robustness, you’re eager to jump into Oracle. But don’t move so fast. There is a temendous amount of planning involved with both moving your data, and porting and testing your application on the new platform. Add to that Oracle licensing, and you’ll need some time to get there. MySQL vs Oracle – feature comparisons MySQL is a database fit for some types of applications. These tend to be smaller applications, or those which integrate

applications with less sophisticated needs than those running Oracle on the backend. 3 It makes sense at this point to go through a feature comparison, and see what features MySQL shares with Oracle. Here’s a more in depth feature comparison of MySQL and Oracle MySQL shares with Oracle good support for database access including ODBC and JDBC drivers, as well as access libraries for Perl, Python and PHP. MySQL and Oracle both support binary large objects, character, numeric, and date datatypes. They both have primary and unique keys, and as of 4.x with InnoDB, MySQL has foreign keys, and transactions including READ UNCOMMITED, READ COMMITED, REPEATABLE READ, and SERIALIZABLE. Both databases have sophisticated language and character set support MySQL can do table locking, and recently improved to include row-level locking. What’s more if you don’t need transactions, MyISAM tables are extremely fast. MySQL also includes a good data dump utility which you’ll see in action

below when we migrate to Oracle. And lastly both databases of course include good b-tree indexes, which no database could be without. There are, however, quite a number of features we find in Oracle as standard, which remain missing in MySQL. Until recently that included row-level locking, true transactions, and subqueries although as of 4.x those seem to be present However, those have been present, and core technologies in Oracle for years, with very stable and solid implementation, you’re sure to achieve dramatic performance on tpc benchmarks. Views are still absent in MySQL, though they may be around the corner with subqueries available now. Of course a lot of the high end Oracle features remain completely absent from MySQL, and may never be added. Features such as parallel query, and partitioned tables, which include a whole host of special features such as the ability to take one partition offline without impacting queries on the rest of the table. The indexing on partition

tables is sophisticated too, allowing partition elimination, and range scans on indexes of specific partitions. There are other large database features such as special functions for star queries. Oracle has terabyte databases in production, so this fact speaks for itself. MySQL still has a somewhat limited set of index types. For instance Oracle has reverse key, bitmap, and function based indexes, as well as index organized tables. These are all very powerful features for developers who are trying squeeze that last bit of performance out of complex SQL queries against large tables. Although MySQL does provide some index statistic collection, Oracle provides the full set of statistics, including histograms, and makes 4 great use of it inside the Cost Based Optimizer. These statistics allow Oracle to better determine the best method of getting the data for your query and putting it together for you with the least use of resources in tems of memory cache, and disk access. This is

really key for a database. When running large multi-user applications all of which are vying for data in different tables, you want to load just the data you need, and nothing more. Avoiding full table scans by using the proper index, and using various indexes in a join operation to pull fewer rows from the underlying tables means less disk I/O, which other processes are fighting for, and less use of cache, leaving more for other processes. MySQL still does not have privilege groups, called ROLES in Oracle. Oracle can also provide column level privilege control, called virtual private database and although we don’t see it used a lot in Oracle deployments, MySQL lacks this feature as well. MySQL does not have hotbackups which have been an integral part of Oracle for years. (There are hotbackups now – 2012 – in MySQL here’s a howto on rebuilding replication using hotbackups guide) In addition, Oracle’s RMAN has become a sophisticated piece of software, and grown to be very

stable, providing block level backups so only the data that changed can be included in subsequent backups. This makes nightly backups smaller overall It also aids tremendously during recovery, providing a lot of automation, and assistence, during those times when you need it most. MySQL’s method is to dump data, and further if you want to guarentee a point in time dump of your data, you have to lock all the tables in the database, potentially slowing down your application tremendously. Lastly MySQL does not have automatic or point in time recovery, a real limitation on heavy use production databases. MySQL also has some limitations on row sizes. MyISAM tables for instance, can have a maximum 64k of data per row, and InnoDB tables 8k per row. This does not include BLOB and TEXT types. MySQL does not include database links such as those found in Oracle allowing you to query a table from an external database inside a query in a local database. There is the federated storage engine, but

reports are that it’s rather buggy. DB Links can be useful for moving data between databases, and is key to implementing advanced replication in Oracle. Lastly, MySQL has had some database size limitations. In 322 it could only access 4GB of data, however, as of 3.23 the only limitation has been with your operating system, and the size 5 of files it can handle. On Linux with LFS or RaiserFS, this limitation is effectively eliminated However, Oracle still has incredibly sophisticated storage cababilities, allowing virtually unlimited datafiles to be associated with a tablespace, and thus virtually limitless sized tables. Updated note: In 5.5 and newer versions of MySQL there are no database size limitations Also with Innodb you can use global tablespaces or one tablespace per table depending on your configuration. With most databases sitting on RAID or SAN these days, you’re getting pretty much the same deal with both MySQL & Oracle storage-wise. Migration preparation So

you’ve seen what you can do with Oracle, and management has invested in licensing, and you’re now ready to get things setup in your development environment. Now is the time to really get up to speed with Oracle. This goes for both Database Administration knowledge, as well as developer and programmer knowledge. The latter requires that you know a lot about Oracle’s features, in particular those which are relevant to your application. The former requires you understanding DBA roles, managing database files, memory structures, backups, and so on and so forth. Thomas Kyte’s books are really excellent, and highly recommended. Check out “Expert One on One” on Wrox Press, and his newer title, “Effective Oracle by Design” which is on Oracle Press. He also has a website, http://asktomoraclecom Also check out Kevin Loney + Marlene Therault’s DBA Handbook on Oracle Press. Of course don’t forget to read the Oracle docs, which are quite good. Start with the concepts manual for

the version of Oracle you plan to go with. In planning a migration the first thing you should do is take a look at the number, and types of tables in your database. Do that in MySQL as follows: . This output is truncated, but serves as a useful example. You will see the tables, types, and a lot of other information about the tables you will be moving. You’ll next want to review the datatypes of your various tables. CHAR in MySQL maps to CHAR in Oracle, VARCHAR to VARCHAR2, and the various Large Object types to RAW 6 or BLOB in Oracle. DATE, DATETIME, and TIME map to Oracle’s DATE datatype, while TIMESTAMP and YEAR map to NUMBER. Lastly all of the various INT datatypes in MySQL map to NUMBER in Oracle and FLOAT, DOUBLE, REAL, and DECIMAL all map to FLOAT. Moving your data between MySQL and Oracle At this point we’re still presuming that you will be moving your data by hand. This is not because we are gluttons for punishment. It is more to illustrate important points Doing

things by hand goes over each and every detail of the process so you understand it. You’ll need to when things go wrong, as they inevitably will. So we’re discussing moving the schema, and then the data by hand for all tables, however you may end up following the instructions below for using the Oracle Migration Workbench, and then only doing one or two special tables by hand. Or you may decide to use Migration Workbench to build scripts for you as a starting point, and then agressively edit those for your particular needs. All these options are valid So at this point you need to dump data. If you want to load data with Oracle’s SQL*Loader, an easy format to start with is CSV or Comma Separated Values file. To dump one table named ‘t’ from database named ‘test’ use this bit of code. Note that we’ve broken things up into multiple lines to easily illustrate what’s happening with all those messy SED commands. You’re welcome to modify them for your needs but this works

as-is Note that ^V requires you to type ctrl-V and requires you to type ^I ctrl-I. Read your editor manual for details on inserting control characters into a file. Migration Workbench is Oracle’s recommended solution Oracle’s Migration Workbench is a Java-based GUI tool which runs on various Windows versions, and comes complete with wizards to help you perform your migration. The process is divided into three steps. First you capture your target database. This is done with a series of plugins which support various databases including of course MySQL. One plugin is available to handle 322 and 3.23 of MySQL and another one handles 4x versions of MySQL Capturing the source database is the same as the process we describe above manually of looking at your tables in mysql, and the columns, and indexes you are using. This is practical and feasible for a small 7 number of tables, however, with hundreds or even thousands of tables, Oracle Migration Workbench becomes more and more of a

necessity. Second, you migrate the source model to Oracle. This is the process where the Migration Workbench precreates all tables found in the source database, including columns of equivalent datatypes. We describe mappings of MySQL to Oracle datatypes above Note that Oracle does not have ENUM or an enumerated datatype per se, but it can still migrate this data, and does so to VARCHAR2 in Oracle. The third and last step is the review the scripts that the Migration Workbench has created, make any changes or modifications and then run them to move your data from your source MySQL database into your new Oracle database. One thing that is important to remember about a migration is that it will take a lot more time, and end up being a lot more complicated than you expected. I liked this about the documentation. They make it clear from the beginning that planning will be a tremendous help to you in estimating time, and delivering successfully within budget. The documentation is also very

thorough in it’s coverage of MySQL datatypes, and how they translate to Oracle datatypes, as described earlier in this article. And of course there is a strong emphasis on testing. The Migration Workbench provides customizable scripts which both document actions to be performed and provide a way for you to get your fingers into the works. Keep in mind while using the Migration Workbench that it is NOT all or nothing. You can use the Migration Workbench, and then edit the scripts to leave certain tables alone, or you can migrate them all, then drop the few you want to do by hand using the methods we describe above. Ultimately a mix of the two will probably serve your needs best, as there is always some amount of manual intervention you want to perform for certain tables. A migration between two databases is not a trivial undertaking. You have a lot of data, and an application which rely on it all being in the right format, with the right relationships. Moving to a new database, with a

larger feature set, slightly different syntax, and different ways of doing things takes time and attention, but in the end you’ll be up and running on a sophisticated, scalable, world class database platform. 8