You can use data transmission service (DTS) to migrate data from a local database to apsaradb RDS for PPAS without stopping services. The migration process has no impact on the local Oracle database.
DTS Data Migration supports structural migration and full migration of PPAS.
DTS migrates the schemas of the required objects to the destination database. Currently DTS supports structural migration of objects such: tables, views, synonyms, triggers, stored procedure, stored functions, packages, custom types.
Full data migration
DTS migrates all data of the migration objects in a local database to the target instance. If data is written to the local Oracle database during migration, the incremental data may not be migrated to the RDS instance. Therefore, to ensure data consistency, we recommend that you full migration data during off-peak hours.
Migrating a PPAS local database to an RDS has the following restrictions.
- DDL operations that are performed during incremental data migration cannot be synchronized to the destination database.
- Migration of materialized views is not supported.
- When the structure is migrated, the reverse index is migrated to a normal index.
- When the structure is migrated, The Bitmap index is migrated to a normal index.
- When the structure is migrated, the partitioned index is migrated to an index that is created separately on each partition.
You have prepared the RDS instance. For more information, see Configure endpoints for an RDS for PPAS instance and Create databases and accounts for an RDS PPAS instance.
Before you migrate, create a migration account respectively in the local database and the RDS instance. Create the database to be migrated in the RDS instance. Grant the migration account the read and write permissions on the database to be migrated. The following table lists the permissions required for database accounts when different migration types are used.
|Database type||Schema migration||Full data migration|
|Local Oracle instance||Schema owner||Schema owner|
|PPAS instance on RDS||Schema owner||Schema owner|
- Create a migration account in the on-premises database through the PostgreSQL client.
CREATE USER username IDENTIFIED BY password
The following table lists the parameters of the function.
- username: the account to be created.
- password: the password for logging on to this account.
CREATE USER myuser IDENTIFIED BY mypassword
- Grant permissions to the migration account in the local database. The required permissions
are described in the table above.
GRANT privileges ON tablename TO username;
The following table lists the parameters of the function.
- privileges: the permissions granted to the account, such as SELECT, INSERT, and UPDATE. To grant ALL permissions to the account, use ALL.
- tablename: the table name. To grant the permissions for all tables to the account, use the wildcard (*).
- username: the account to which you want to grant permissions.
The scenarios are as follows:
GRANT ALL ON* TO myuser
- In RDS console click migrate databases, enter DTS from the shortcut menu.
- In the left-side navigation pane, click data Migration.
- In migration tasks right click create a migration task.
- Enter the task name, local database information, and target database information,
and click authorize the whitelist and go to the next step. From the shortcut menu.
- Task name: enter a default value for the task name.
- Source Database
- Instance type: The instance type of the local database. Select user-created database with a public IP address.
- Instance region: The region where the local database is located.
- Database Engine: the type of local database. Select Oracle.
- Hostname or IP address: The public endpoint of the on-premises database.
- Port: The public port of the local database.
- Instance type: indicates whether the local database is a RAC cluster.
- SID: The SID of the local database.
- Database account: The Migration account of the local database.
- Database password: the password of the migration account of the local database.
- Destination Database
- Instance type: The instance type of the cloud database, Select RDS instance.
- Instance region: The region where the cloud database is located.
- RDS instance ID: the ID of the destination RDS instance. Click the drop-down menu to automatically associate the RDS instance of the account currently logged in to the management console, click to select the required instance.
- Database name: the name of the target database.
- Database account: The Migration account of the RDS Database.
- Database password: the password of the RDS Database Migration account.
- Select migration type, and in objects to be migrated select the objects to be migrated, and then click >put the object you want to migrate into selected click pre-Check and start from the shortcut menu.
- When you select a structure migration, if the target RDS instance is in the database mydatabase, there is no Schema with the same name as the local database migration account, then DTS automatically creates a Schema with the same name, and the Owner of the Schema is the migration account.
- -In data migration, the data (structure) of the local database is copied to the target database without affecting the data (structure) of the local database.
- -During data migration, DDL operations are not supported and they may cause migration failures.
If you want to modify the name of the migrated object on the target database, you can selected to the right of the list, click edit to modify the name of the selected object.
- This step shows an example of a failed pre-check. If the pre-check is successful,
see step 8.
The system displays the pre-check results.
- Click detection result for failure after to view the failure Details. You can troubleshoot the failure based on the failure details.
- After troubleshooting, in migration tasks page, select the current migration task, click start from the shortcut menu.
- After pre-check is passed, click confirm. The Migration Task is automatically executed.
What to do next
Since the migration accounts have the read-write privileges, you need to delete the migration accounts in the local database and the RDS instance after data migration to ensure the security of the local database.