All Products
Search
Document Center

Migrate data from Oracle to PPAS without stopping services

Last Updated: Sep 11, 2019

DTS can help to migrate data from a local Oracle database to an RDS For PostgreSQL Plus Advanced Server. Meanwhile, the incremental migration feature offered by DTS enables data migration while Oracle is providing services. When Oracle and PPAS reach dynamic data synchronization, you can switch the services to PPAS, minimizing the downtime of applications dependent on Oracle.

This section describes how to use DTS to configure data migration tasks from Oracle to RDS For PostgreSQL Plus Advanced Server without stopping services.

Migration types

Migration from Oracle to RDS For PPAS supports structure migration, full migration, and incremental migration. The range supported during each migration phases are as follows:

  • Structure migration

    DTS will migrate the structure definition of the migrated object to the target instance. DTS currently supports the following objects for structure migration: table, view, synonym, trigger, stored procedure, storage function, package, and custom type.

  • Full migration

    DTS will migrate the existing data of the migrated source database object to the target instance.

    If you didn’t choose incremental migration and there is data written into the Oracle database during the migration, the incremental data is not necessarily migrated to PPAS. Therefore, to ensure migration data consistency, you should select off-peak hours to stop the services and perform migration if possible.

  • Incremental migration

    Incremental migration will synchronize the incremental changes data to the source Oracle instance during the migration to the target PPAS instance. Incremental migration will synchronize the incremental data to the Oracle instance during the migration to the target database so that the Oracle and target RDS PPAS instances enter the dynamic data synchronization process.

Limits

  • DDL operations are not supported during migration.
  • Materialized views cannot be migrated.
  • During structure migration, reverse indexes will be migrated to RDS For PPAS as normal indexes.
  • In structure migration, bitmap indexes will be migrated to RDS For PPAS as normal indexes.
  • In structure migration, partitioned indexes will be migrated to RDS For PPAS and independent indexes will be created on each partition.
  • Incremental migration only supports tables with primary keys or with non-null unique indexes.
  • Incremental migration does not support the long type

Permission requirements

When DTS is used for data migration from Oracle to RDS For PPAS, different migration types have different permission requirements for migration accounts of source and target databases:

Migration Type Structure Migration Full Migration Incremental Migration
Local Oracle Instance Schema Owner Schema Owner SYSDBA
Target RDS for PPAS Instance Schema Owner Schema Owner Schema Owner

Prerequisites

  1. The Oracle version should be 10g, 11g, or 12c.
  2. Oracle should enable supplement log and enable supplemental_log_data_pk and supplemental_log_data_ui.
  3. Oracle should enable archive log.

Data type mapping

The Oracle and RDS For PPAS data types are not in a one-to-one relationship, therefore DTS will map the data types during structure migration for the two database types. The Following table shows the mapping between the data types defined by DTS.

Oracle Data Type PPAS Data Type Supported by DTS?
varchar2(n [char/byte]) varchar2[(n)] Supported
nvarchar2[(n)] nvarchar2[(n)] Supported
char[(n [byte/char])] char[(n)] Supported
nchar[(n)]] nchar[(n)] Supported
number[(p[,s])] number[(p[,s])] Supported
float(p)] double precision Supported
long long Supported
date date Supported
binary_float real Supported
binary_double double precision Supported
timestamp[(fractional_seconds_precision)] timestamp[(fractional_seconds_precision)] Supported
timestamp[(fractional_seconds_precision)]with time zone timestamp[(fractional_seconds_precision)]with time zone Supported
timestamp[(fractional_seconds_precision)]with local time zone timestamp[(fractional_seconds_precision)]with time zone Supported
clob clob Supported
nclob nclob Supported
blob blob Supported
raw raw(size) Supported
long raw long raw Supported
bfile Not supported
interval year(year_precision) to month interval year to month Not supported [A2]
interval day(day_precision) to second[(fractional_seconds_precision)] interval day to second[(fractional_seconds_precision)] Not supported

RDS For PPAS does not support timestamp [(fractional_seconds_precision)] with local time zone, therefore DTS will convert it into UTC time zone during migration of such data type and store it to RDS For PPAS timestamp [(fractional_seconds_precision)] with time zone.

Preparations

The following describes in detail how to configure DTS migration tasks to migrate data from Oracle to RDS for PPAS.

Create a migration account

When configuring a migration task, you need to provide the migration account of the Oracle database and the target RDS instance. For permissions required for the migration account, see the Migration permission requirements section above.

If you haven’t created any migration accounts for your Oracle instance, you can refer to Oracle Grant Syntax Instructions to create a migration account meeting the requirements.

Creation and authorization operations of RDS For PPAS migration accounts are as follows:

  1. Create a migration account in RDS for PPAS by using the PostgreSQL client.
  2. Grant permissions to the migration account in RDS for PPAS.

Procedure

  1. Log on to the DTS Console with the Alibaba account owning the target RDS instance, click Create Migration Task on the top right of the console to start migration task configuration.
  2. Configure the connection information of the Oracle instance and target RDS instance.

    You need to set the following parameters:

    • Task name

      DTS generates a name for every task automatically. The task name is not required to be unique. You can modify the name as needed. A name indicating the specific services of the task is recommended to facilitate task identification.

    • Source instance information

      • Instance type: Select Self-built database with a public IP address.
      • Database type: Select Oracle.
      • Host name or IP address: Configure the address for accessing Oracle. The address must be in public access mode.
      • Port: the listener port of the Oracle instance.
      • SID: SID of the Oracle instance.
      • Database account: the access account to the Oracle instance.
      • Database password: the password of the above Oracle connection account.
    • Target instance information

      • Instance type: Select RDS instance.
      • RDS instance ID: Configure the instance ID of the target RDS instance to be migrated. DTS supports RDS instances in classic networks and VPC networks.
      • Account: the access account to the RDS instance.
      • Password: the password of the above RDS connection account.

      After you finish configuring the connection information, click Authorize Whitelist and Next to authorize entries in the whitelist.

      In this step, DTS will add the DTS server IP address to the whitelist of the target RDS instances to avoid migration failures because of the whitelist mechanism of the RDS instance.

  3. Configure the migration object and migration type.

    • Migration typeDTS supports structure migration, full migration, and incremental migration.

      If you need migration without stopping services, you can select Structure Migration + Full Migration + Incremental Migration as the migration type.

      If you only need full migration, you can select Structure Migration + Full Migration as the migration type.

    • Migration object

      You should select the object to be migrated. The migration object can be a database, a table or a column. By default, after the object is migrated to RDS For PPAS instance, the object name remains the same with that in the local Oracle instance. If the object you migrate has different names on the source and target instances, you need to use the object name mapping function provided by DTS. Details usage can be found in [Database Table Column Mapping].

  4. After migration object and migration type are configured, perform a pre-check.

    A pre-check will be performed before a migration task is formally started. Migration can be started only after the pre-check is passed.

    If the pre-check fails, check the failure details by clicking the button after the specific check items, rectify the faults accordingly, and perform a pre-check again.

    After rectification, select the task in the task list, and then re-start the pre-check.

  5. Purchase an instance and start the task.

    After the pre-check is passed, the interface of confirming purchase orders of migration instances is displayed. Select a desired specification based on your business stress and agree with the product terms of service. Then start the migration task.

    After the task is started, you can view the specific migration status and progress in the task list.

    Incremental migration is a process of dynamic synchronization. We recommend that you verify the business on the target database when the incremental migration achieves no latency. If the verification succeeds, you can stop the migration task and switch the business to the target database.

    So far, the data migration task configuration from a local Oracle database to the RDS for PPAS instance without stopping services has been complete.

Appendix: Pre-check content

Before DTS starts the migration, it will perform a pre-check. This section briefs the pre-check content for migration from Oracle to RDS For PPAS:

Pre-check Items Check Content Remarks
Source database connectivity check Check the connectivity between the DTS server and Oracle instance (1) Error in information filled in? If the information filled in contains errors, correct the information and re-run the pre-check
(2) Check whether the Oracle has enabled the listener port
Target database connectivity check Check the connectivity between the DTS server and target RDS For PPAS instance Check whether there are errors in the information filled in. If the information filled in contains errors, correct the information and re-run the pre-check
Source database version check Check the version of the Oracle instance, and whether it is supported by DTS DTS currently only supports 10g, 11g and 12c versions
Source database permission check Check whether the permissions of Oracle instance access accounts meet the requirements If the permission is not adequate, authorize permissions by referring to the permission requirements section above and re-run the pre-check
Source database permission check Check whether the permissions of RDS For PPAS instance access accounts meet the requirements If the permission is not adequate, authorize permissions by referring to the permission requirements section above and re-run the pre-check
Cognominal [A3]object check Check whether the object to be migrated has existed in the target RDS For PPAS If the check fails, delete the existing cognominal objects in the target database and re-run the pre-check
Cognominal object check on source end Check whether there are cognominal objects to be migrated to the same schema of the target instance If the check fails, refer to Database, Table and Column Mapping to rename the cognominal objects
Log mode check in source database Check whether the source database has enabled archive log If not, enable archive log and re-run the pre-check
Constraints integrity check Check whether the parent object of the object to be migrated is migrated If the check fails, you can modify the migration object and migrate the parent object dependent on, and re-run the pre-check
DBLINK check Check whether DBLINK exists in the source database If yes, you need to modify the object to be migrated and unselect DBLINK
Incremental topology conflict check Check whether there are other migration links for the object to be migrated If a conflicted link exists, you need to delete the conflicted link and re-run the pre-check
Field type check Check whether long data type fields exist in the table to be migrated If yes, the table is only available for full migration, instead of incremental migration
Primary key or unique non-null index check of the table Check whether the primary key or unique non-null key exists in the table to be migrated If yes, the table is only available for full migration, instead of incremental migration
Supplemental log check Check whether supplemental_log has been enabled If no, enable supplemental_log and re-run the pre-check