Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to use DTS for data migration between self-built Oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

prerequisite

The versions of the source and destination Oracle databases are 9i, 10g, 11g, 12c, 18c, or 19c.

To ensure compatibility, it is recommended that the versions of the source and target libraries should be the same, and do not migrate across versions.

The source Oracle database has Supplemental Logging enabled, and supplemental_log_data_pk and supplemental_log_data_ui are required. For more information, please see Supplemental Logging.

The source Oracle database has enabled ARCHIVELOG (archive mode), set a reasonable archive log retention period and allow access to archive logs. For more information, please see ARCHIVELOG.

The storage space used to create the database in the target Oracle must be greater than that occupied by the source Oracle database.

Matters needing attention

When performing full data migration, DTS will occupy certain read and write resources of the source database and the target database, which may lead to an increase in the load of the database. In the case of poor database performance, low specification or large amount of business (for example, the source database has a large number of slow SQL, there is no primary key table or the target inventory is deadlock, etc.), it may increase the pressure on the database and even cause the database service to be unavailable. Therefore, you need to evaluate the performance of the source and target libraries before performing data migration, and it is recommended that you perform data migration during the business trough (for example, the CPU load of the source and target databases is less than 30%).

If the source database does not have a primary key or unique constraint, and all fields are not unique, it may result in duplicate data in the target database.

For tasks that fail to migrate, DTS triggers automatic recovery. Be sure to end or release the task before you switch the business to the target instance, so as to prevent the source data from overwriting the data of the target instance after the task is automatically resumed.

Instance types supported by the source library

The Oracle database for data migration supports the following instance types:

Self-built database with public network IP

Self-built database on ECS

Self-built database accessed through direct connect / VPN gateway / intelligent gateway

This paper takes the self-built database on ECS as an example to introduce the configuration process, and the configuration process of other types of self-built Oracle database is similar to this case.

Cost description

Migration type link configuration fee public network traffic fee structure migration / full data migration is free. A fee will be charged when data is migrated out of Aliyun through the public network. For more information, please see Product pricing. Charge for incremental data migration. For more information, please see product pricing. Migration type description

Migration type description structural migration DTS migrates the structure definition of the migrated object to the target Oracle database. Currently, DTS supports tables, views, synonyms, triggers, stored procedures, stored functions, packages, and custom types.

Indicates that triggers are not compatible, and data inconsistencies may occur when the migration object contains triggers.

Full data migration DTS migrates all the stock data of the source Oracle database migration object to the target Oracle database.

Note: do not DDL the migrated object before the structural migration and full data migration are complete, otherwise the migration may fail.

Incremental data migration DTS polls and captures the redo log generated by the source Oracle database on the basis of full data migration, and synchronizes the incremental update data of the source Oracle database to the target Oracle database. Through incremental data migration, the migration of Oracle database can be completed smoothly without stopping the service of the application. Incremental data migration supports synchronous SQL operations

INSERT 、 UPDATE 、 DELETE

CREATE TABLE

Indicates that partitioned tables and tables containing functions are not supported.

ALTER TABLE 、 DROP TABLE 、 RENAME TABLE 、 CREATE INDEX 、 ADD INDEX

Permission requirements of database account

Migration data source structure migration full migration incremental data migration owner permissions of source Oracle database schema owner permissions of schema SYSDBA target Oracle database schema owner permissions schema owner permissions schema owner permissions

For more information on how to create and authorize Oracle database accounts, please see CREATE USER and GRANT.

Operation steps

Log in to the data transfer console.

On the left navigation bar, click data Migration.

At the top of the migration task list page, select the region to which the target cluster of the migration belongs.

Click create Migration Task in the upper right corner of the page.

Configure the source and target library information for the migration task.

Category configuration description Task name-DTS will automatically generate a task name. It is recommended to configure a name with business significance (no uniqueness requirement) to facilitate subsequent identification. The source database information instance type selects the self-built database on ECS. Instance region Select the region to which the ECS instance with the source Oracle database deployed belongs. ECS instance ID Select the ECS instance ID with the source Oracle database deployed. Select Oracle as the database type. Enter the service port of the source Oracle database. Instance Typ

Enter the database account of the source Oracle into the database account. For permission requirements, please see the permission requirements of the database account. Enter the database password into the corresponding password of the database account.

After the source database information is filled in, you can click the test connection after the database password to verify that the source database information is correct. If you fill in the source database information correctly, you will be prompted to pass the test; if prompted to fail the test, click the diagnosis after the test failed, and adjust the filled source database information according to the prompt.

The target database information instance type selects the self-built database on ECS. Instance region Select the region to which the ECS instance with the target Oracle database deployed belongs. ECS instance ID selects the ECS instance ID with the target Oracle database deployed. Select Oracle as the database type. Enter the service port of the target Oracle database. Instance Typ

Enter the database account of the target Oracle into the database account. For permission requirements, please see the permission requirements of the database account. Enter the database password into the corresponding password of the database account.

After the target library information is filled in, you can click the test connection after the database password to verify that the target library information is correct. If you fill in the target library information correctly, you will be prompted to pass the test; if prompted to fail the test, click the diagnosis after the test failed, and adjust the target library information according to the prompt.

Non-RAC instance: after selecting this item, you also need to fill in the SID information.

RAC instance: after selecting this item, you also need to fill in the ServiceName information.

Non-RAC instance: after selecting this item, you need to enter SID information.

RAC instance: after selecting this item, you need to enter ServiceName information.

When the configuration is complete, click the authorization whitelist in the lower right corner of the page and proceed to the next step.

Select the migration object and migration type.

Configuration description migration type

For a detailed description of the migration type, see the migration type description.

Migration object

Click the object to be migrated in the Migration object box, and then click

Move it to the selected object box.

Description

The granularity of migration object selection is library, table, column.

By default, after the migration is complete, the migration object name is the same as in the source Oracle database. If you need to change the name of the migrated object in the target Oracle database, you need to use the object name mapping feature, as shown in the library table column mapping.

If the object name mapping feature is used, it may cause the migration of other objects that depend on this object to fail.

If only full migration is required, check both structural migration and full data migration.

To ensure data consistency, do not write new data to the source Oracle database during full data migration.

If non-downtime migration is required, check structural migration, full data migration and incremental data migration at the same time.

Click the pre-check in the lower right corner of the page and start it.

Description

A pre-check is performed before the migration task is officially started. DTS can migrate data only if it passes a pre-check.

If the pre-check fails, click the

To view the details of the failure After the repair according to the prompt, re-check the pre-check.

After the pre-check passes, click next.

In the pop-up purchase configuration confirmation dialog box, select the link specification and check the data transfer (pay-per-usage) terms of service.

Click Buy and start, and the migration task begins.

Structure migration + total data migration

Do not end the migration task manually, or it may result in incomplete data. All you have to do is wait for the migration task to complete, and the migration task ends automatically.

Structure migration + total data migration + incremental data migration

The migration task does not end automatically, you need to end the migration task manually.

Please select an appropriate time to manually end the migration task, such as when the business is at a low ebb or when you are ready to switch the business to the target cluster.

Observe that when the progress of the migration task changes to an incremental migration and appears without delay, stop writing the source database for a few minutes, and the status of the incremental migration may show the delayed time.

After waiting for the incremental migration of the migration task to enter a delay-free state again, manually end the migration task.

This is the end of the introduction to "how to use DTS for data migration between self-built Oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.