Analysis of cross-platform database migration methods

1. Migration Method Introduction:

Exp/imp:

This is also the most commonly used and simplest method. It is generally based on the application's owner level for export and import.

Operation Method: Create the owner and tablespace in the new database, stop the application of the old database, and execute exp user/pwd owner = XXX file = exp_xxx.dmp log = exp_xxx.log buffer = 6000000 in the old database, upload the dmp file to the new database and run imp user/pwd fromuser = XXX touser = XXX file = exp_xxx.dmp log = imp_xxx.log ignore = y in the new database.

Advantages and disadvantages: it can be used across platforms. The disadvantage is that the downtime is long, and the downtime is from exp to network transmission to new database, plus imp time.

Storage migration:

In this case, data files, control files, log files, and spfiles are all stored (normally bare devices), and we can directly mount the storage to a new machine, then start the database on the new machine.

Operation Method: Upload the pfile of the old library (because there is a spfile link pointing to the bare device), tnsnames. ora, listener. ora, and password file to the corresponding location of the new library. Switch the storage to the new machine, or copy the data file using the file copy or dd method to start the database.

Advantages and disadvantages: the migration method is very simple. The main task is to work as a host engineer. dba only needs to work with each other. The downtime is the time when the database is used, the storage is switched, and the database is started. The disadvantage is that the new and old databases are all on the same platform and are of the same database version.

Use data guard for Migration:

We can not only use dg for disaster tolerance, but also use physical dg for migration.

Operation Method: visible region. Note that after switch over, you can remove the dg and remove the log_archive_dest_2, FAL_SERVER, FAL_CLIENT, and standby_file_management parameters. In addition, note that if rman is used for dg, add tempfile manually.

Advantages and disadvantages: The downtime is short, and the downtime is the switch over time. Disadvantage: the host must be dual and the storage must be dual.

Use rman for Migration:

Rman is suitable for cross-file system migration, just like different file systems on the platform.

1. Upload the archive log and recover the archive: recover database until sequence = the archive serial number thread = 1;

2. open resetlogs: RMAN> SQL 'alter database open resetlogs ';

3. alter tablespace temp add tempfile 'xxxxxx' size XXM reuse;

Advantages and disadvantages: Cross-File System with less downtime. The disadvantage is that you always need to pay attention to this archived log, and there should be no less recover!

II. Introduction to migration styles:

1. Direct ing. What is the original one? The original one cannot be copied. For such a rule, if the data source field and the target field length or accuracy do not match, pay special attention to whether the ing can be realized directly or some simple operations are required.

2. Field Calculation: The target field obtained by mathematical calculation of one or more fields of the data source. This rule is generally applicable to numeric fields.

3. for conversion by reference, one or more fields of the data source are usually used as keys to search for specific values in an associated array, and only unique values can be obtained. This associated array is suitable and most common to be implemented using the Hash algorithm. Before the ETL starts, it loads the memory, which greatly improves the performance.

4. string processing: You can often obtain specific information from a string field of the data source, such as the ID card number. In addition, there are often numeric values in the form of strings. String operations include type conversion and string truncation. However, the randomness of character type fields also causes the risk of dirty data. Therefore, when processing such rules, you must add Exception Processing.

5. null value determination. Processing of null values is a common problem in the Data Warehouse. Is it used as dirty data or as a specific dimension member? This may depend on the application and further exploration is required. However, for fields that may have NULL values, do not use the "direct ing" rule type. NULL values must be determined, currently, we recommend that you convert it to a specific value.

6. For date conversion, the date value in the data warehouse generally has a specific date value, which is different from the expression method of the date type value. For example, an 8-bit integer 20040801 is used to represent the date. In the data source, such fields are basically of the date type. Therefore, for such rules, common functions are required to convert a date to an 8-bit date value and a 6-bit month value.

7. Date calculation. Based on the date, we usually calculate the daily, monthly, and duration values. Generally, the date calculation functions provided by the database are based on the date type. If a specific type is used to represent the date in the data warehouse, you must have a set of date calculation functions.

8. Aggregation calculation. For measurement fields in fact tables, they are usually obtained by using clustering functions for one or more fields of the data source. These aggregation functions are in the SQL standard, including sum, count, avg, min, max.

9. the predefined values. The difference between these rules and the preceding rules is that they do not depend on the data source field and take a fixed or system-dependent value for the target field.

1. Use LiquiBase for database migration and Reconstruction
2. PHP migrates data from Oracle to Mysql instances
3. SQL Server 2008 migration query plan