Migrate MySQL to Oracle (1) _ MySQL

Migrate MySQL to Oracle (1) 1. Introduction

As you may imagine, migrating a database and using its applications is not a small matter. A lot of work is required to migrate the database platform and the applications that use it. In this two-part document, we will discuss all the steps around this process and ask yourself what you should pay attention, and how to do everything in a limited downtime window. Finally, your technology will be improved, whether through books and publications, forums, training or certification.

2. Evaluate the transplantation plan

Before you do anything, you will need to develop a plan or outline of what to do. I know, I know, and everyone does not like writing documents. But this is an important step, because in terms of technology, it will make you decide what to do, and in terms of management, it will describe the process step by step, therefore, they can see this complexity, consider the business impact, the cost of the necessary system, the resources required for this transplantation, and the overall time schedule.

What should this evaluation cover? First, you should determine which MySQL version you are using. View the features you are currently using, such as the data type (numeric, character, enumerated, and so on) and the storage engines you are using, such as MyISAM and Innodb. Think about what index types and additional features you are using. for example, you may have used Views and triggers in your database. Also, check the stored procedures in your database because these will need to be overwritten. We also need to carefully consider MySQL replication. If it is created for the entire database, it will be more inclined to use the Oracle backup database or replica uard feature, and if you use table replication and your my. the wildcard syntax in the cnf file is used to use the table, so it is more likely to use the Materialized view (previously called Snapshot) in Oracle.

In terms of Oracle, you will need to consider how your running version, MySQL rich data types and objects match in Oracle. Consider adding attributes automatically on the primary key. Although Oracle has a sequence, its sequence is slightly different from the automatic increase in MySQL. For example, it does not guarantee the continuity of numbers. Therefore, you may encounter problems with the Oracle cache sequence-based key values. Think about how this will affect your application behavior. Although it is not a direct part of the migration process, you still need to consider how you want to back up your Oracle system. MySQL mysqldump is suitable for the output tool, while RMAN certainly provides all the hot backup functions you may find in tools such as innobackup or ibbackup available in MySQL.

Record all of these findings in your assessment document and go as deep as possible. Now, the harder you work, the fewer problems you encounter in the subsequent porting process.

3. develop, transplant, and establish

A. preparation

Preparation involves creating your development environment. This includes applying for appropriate hardware, building a data center, installing Oracle software, and creating your startup database. Consider the optimal Oracle method when creating these, for example, using RAID 10 as your basic disk storage. Deploy your repeated records, allocate enough system and sysaux tablespaces, and so on. You will also consider character settings for your new database. Note that US7ASCII is restricted to European and accent characters. It is best to use WE8ISO8859P1. If you may use Asian characters in your database, take a look at Oracle's National Language Services (NLS) feature and consider multi-byte character sets. You also need to create tablespaces for data and indexes. Consider the size of your source object. On the file system, you can use "du-sm dir_name" in the data directory ". Set aside 25-50% space for future development.

B. database structure migration

In MySQL, you can use the following method to extract the database structure (which is a schema in Oracle:

$ Mysqldump -- no-data my_db_name> my_db_name.mysql

Because there are some differences in syntax, you cannot directly run this script in Oracle. All you have to do is rewrite these create table and create index statements by yourself. Obviously, if you have a large number of objects in your database, you need to consider using an automatic method for this work. We will discuss the Migration Workbench of Oracle in the second part of this article, but we recommend that you rewrite these objects one by one. This will also provide you with a sort directory for objects in the database, so that you can better understand them and Port them all. Developers should also be cautious in this process. By changing the syntax of tables and objects one by one, on the one hand, they will be updated, or changed to the Oracle syntax, on the other hand, they will truly browse these objects, so that you can consider the changes that may need to be made in their application code.

In the end, application changes will be a huge part of porting to a new database engine, no matter how diligent your developers are in writing independent database code. Therefore, this manual process will give you a good understanding of your complicated database.

4. Summary

Yes, porting a database application to a new platform. for example, it is not a simple task on Oracle, but it can be implemented, and through proper preparation, it can also be managed. Evaluate your current system properly before building any system and check and manage time, hardware, software, and personnel requirements before porting.

In the second part of this article, we will discuss how to port the actual data from the product and check the Migration Workbench of Oracle, which can save you time, discuss potential application problems and challenges. Then we will review the entire process and carefully plan the final migration of your product database and application.