Migrate Data with DBMS_CLOUD_IMPORT

Describes the steps to create an import task.

1. Create credentials for the source database. These credentials are used to securely authenticate and connect to the source system. For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'db1_cred',
       username        => '<username>',
       password        => '<password>'
     );
   END;
   /

   See CREATE_CREDENTIAL Procedure for more information.

2. Create an Import Task:

   After you create the credential, use the DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK procedure to create and start an import task. This procedure imports data from Oracle and supported non-Oracle databases, such as MySQL, PostgreSQL, and Amazon Redshift.

   When you run this procedure, specify the required connection details and credentials. You can also control what data is imported by using parameters such as schema_list or table_list.

   Oracle Database runs the import as an Oracle Scheduler job, which manages the operation and tracks progress. The job maintains the state of the import at the table level. If the import is interrupted, it automatically resumes by continuing from the last incomplete tables, rather than restarting the entire operation.

   The following examples show how to create import tasks for different source database types by providing the required connection details and credentials.

   Example: Creates an import task that connects to an Oracle source and migrates only the specified tables (for example, ADMIN.TABLE1 and ADMIN.TABLE2) into the target Autonomous AI Database.

   BEGIN
     DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK(
       task_name          => '<orcl_import_job>',
       hostname           => '<example1.oraclecloud.com>',
       port               => '<port>',
       service_name       => '<service_name>',
       ssl_server_cert_dn => '<cn=remote_db_cn_name>',
       directory_name     => '<walletdb1>',
       credential_name    => 'db1_cred',
       table_list         => '[{"schema_name": "admin", "table_name": "table1"},
                              {"schema_name": "admin",  "table_name": "table2"}]'
     );
   END;
   /

   Example: Creates an import task that connects to the source Oracle database using the specified connection details and credential, and migrates the entire ADMIN schema. Use schema_list when you want to migrate one or more schemas, instead of specifying individual tables.

   BEGIN
     DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK(
       task_name          => 'orcl_import_job',
       hostname           => '<remote_db_hostname>',
       port               => '<remote_db_port>',
       service_name       => '<remote_db_service_name>',
       ssl_server_cert_dn => '<cn=remote_db_cn_name>',
       directory_name     => '<walletdb>',
       credential_name    => 'db1_cred',
       schema_list        => '["admin"]'
     );
   END;
   /

   Note
   
   

   • To restrict the scope of the import:

     • Use schema_list to import one or more schemas.

     • Use table_list to import specific tables across multiple schemas.

   • The table_list parameter is supported only when the source database is Oracle and is not supported for non-Oracle databases.
   • When table_list is used:

     • Schema metadata for the associated schemas is imported as needed.

     • Data is imported only for the tables explicitly specified in table_list.

   • Do not specify overlapping objects in both schema_list and table_list; use only one option to select a given schema or table.

   Example: Creates an import task for a MySQL source by specifying gateway_params with db_type set to mysql, and identifies what to migrate using schema_list (as applicable for the gateway configuration).

   BEGIN
     DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK(
       task_name        => 'mysql_import_job',
       hostname         => '<mysql_hostname>',
       port             => '<mysql_port>',
       service_name     => '<mysql_service>',
       credential_name  => '<mysql_cred>',
       gateway_params   => JSON_OBJECT('db_type' VALUE 'mysql'),
       schema_list      => '["dg4odbc"]'
     );
   END;
   /

   Example: Creates a import task for a PostgreSQL source by specifying gateway_params with db_type set to postgres. The task connects to PostgreSQL and migrates data into the target Autonomous AI Database based on the task configuration.

   BEGIN
     DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK(
       task_name        => '<postgres_import_job>',
       hostname         => '<postgres_hostname>',
       port             => '<5432>',
       service_name     => '<postgres_serv>',
       credential_name  => '<postgres_cred>',
       gateway_params   => JSON_OBJECT('db_type' VALUE 'postgres')
     );
   END;
   /

   In these examples, hostname, port, and service_name identify the source database endpoint and are used by the task to establish connectivity (including creating the required database link). credential_name authenticates to the source database so the link can be created. For Oracle sources, use table_list to migrate specific tables, or schema_list to migrate one or more schemas. For non-Oracle databases, gateway_params is also required to specify the source database type (for example, MySQL or PostgreSQL) and enable the appropriate gateway-based connectivity for the import.

Describes how to suspend and resume an import operation.

BEGIN
  DBMS_CLOUD_IMPORT.SUSPEND_IMPORT_TASK(
    task_name => 'orcl_import_job'
 );
END;
/

BEGIN
  DBMS_CLOUD_IMPORT.RESUME_IMPORT_TASK(
    task_name => 'orcl_import_job'
  );
END;
/

After you start an import task, the scheduler job runs in the background and the Autonomous AI Database records progress at both the task and table levels.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'db1_cred',
    username        => '<username>',
    password        => '<password>'
  );
END;
/

IMPORT_TASK_NAME   SCHEMA_NAME   SCHEMA_OBJECT   STATUS
------------------ ------------- --------------- ----------
IMPORT_JOB1        SALES         ORDERS          SUCCEEDED
IMPORT_JOB1        SALES         CUSTOMERS       SUCCEEDED
IMPORT_JOB1        HR            EMPLOYEES       LOADING
IMPORT_JOB1        HR            DEPARTMENTS     FAILED

Describes the steps to drop an import task.

BEGIN
  DBMS_CLOUD_IMPORT.DROP_IMPORT_TASK(
    task_name => 'orcl_import_job'
  );
END;
/