Migrate to Autonomous AI Database

Source : Oracle Database platform, such as Oracle Autonomous AI Database, Oracle RAC, or a standalone Oracle Database instance on Linux.

Target : Oracle Autonomous AI Database running on Oracle AI Database@Google Cloud.

• A bucket containing the data file(s) such Oracle Data Pump dump file.

• For Oracle Database, internet connectivity or PrivateLink using Oracle Interconnect for Google Cloud is supported.
• For Oracle AI Database@Google Cloud, internal connectivity within the same cloud region is supported by default.

• A Google Cloud Access Key and Secret (HMAC key) with bucket access permissions.

1. Create Google Cloud Storage Access Credentials
   1. In the Google Cloud Console, navigate to Cloud Storage.
   2. Select Settings from the left menu, and then select the Interoperability tab.
   3. Scroll down to the Access keys for your user account section, select the Create a key button to generate an Access key and Secret. Take note of the Access key and Secret as these are required to authenticate Oracle Database access.
2. Determine Object URL
   1. In the Google Cloud Console, navigate to Cloud Storage.
   2. From the left menu, select Buckets, and then select the target bucket that you are using.
   3. From the Bucket details page, locate the object. For example, Oracle Data Pump export file which you want to import.
   4. Construct the public object URL in the form.

      https://<bucket_name>.storage.googleapis.com/<object_name>

      For example:

      • Bucket name: exadbtest
      • Object name: emp.dmp
      • Object URL: https://exadbtest.storage.googleapis.com/emp.dmp
      

1. Create Oracle Credential
   1. Using the DBMS_CLOUD.CREATE_CREDENTIAL PL/SQL procedure, create a credential in the database that stores your GCS access key and secret:

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

      For example:

      BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'GCP_CRED', username => 'GOOGXXXXXXXXXXXX', password => 'xs9njBZ+hykXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ); END; /

   2. After running the PL/SQL procedure, the credential is stored and ready for use by DBMS_CLOUD. For more information about the parameters, see CREATE_CREDENTIAL Procedure.
2. Verify Access to Google Cloud Storage
   1. To ensure that the database can reach your Google Cloud Storage bucket, run the following:

      SELECT object_name FROM dbms_cloud.list_objects( credential_name => 'GCP_CRED', uri => 'https://exadbtest.storage.googleapis.com' );

   2. The output should list the object(s), confirming successful access. For example:

      OBJECT_NAME
      --------------------------------------------------------------------------------
      emp.dmp
      employee.csv

After creating the necessary credential for Google Cloud Storage, you can load or access data into Oracle Database using any of the following methods: DBMS_CLOUD.COPY_DATA procedure, Oracle Data Pump Import (impdp), DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE, the Data Studio Load tool (UI in Autonomous AI Database), or DBMS_CLOUD.CREATE_HYBRID_PART_TABLE for hybrid partitioned tables.

This step demonstrates DBMS_CLOUD.COPY_DATA as well as Oracle Data Pump Import (impdp).

1. Using DBMS_CLOUD.COPY_DATA Procedure
   1. Create a table in your database where you want to load the data.

      CREATE TABLE emp (id NUMBER, name VARCHAR2(64));

   2. Import data from the Google Cloud Storage bucket to your Autonomous AI Database.
   3. Specify the table name and the GCP credential name followed by the Google Cloud Storage object URL.
   4. Use the DBMS_CLOUD.COPY_DATA procedure to load data from your Google Cloud Storage bucket into a table. The file_uri_list parameter specifies the path to your files in Google Cloud Storage.

      BEGIN
      
        DBMS_CLOUD.COPY_DATA(
      
          table_name    => 'YOUR_TARGET_TABLE',
      
          credential_name => 'GCP_CRED',
      
          file_uri_list => 'https://exadbtest.storage.googleapis.com/employee.csv', -- Or a list of files
      
          format        => json_object('type' value 'CSV', 'skipheaders' value '1') -- Specify file format options
      
        );
      
      END;
      
      /

      For more information about the parameters, see COPY_DATA Procedure.

   5. Once you have successfully imported data from Google Cloud Storage to your Autonomous AI Database, you can run this statement and verify the data in your table.

      SELECT * FROM emp;

2. Import Data Using Data Pump

   The import command can be executed either on the VM cluster or on an external virtual machine with authorized SQL*Plus access to the target database.

   1. Create a parameter file imp_gcp.par with the following contents:

      directory=DATA_PUMP_DIR
      credential=GCP_CRED
      schemas=emp
      remap_tablespace=USERS:DATA
      dumpfile=https://exadbtest.storage.googleapis.com/emp.dmp

   2. Run the import from VM Cluster or Client. Ensure that Oracle client is installed and you can connect to the Autonomous AI Database:

      impdp userid=ADMIN@demo_db parfile=imp_gcp.par

   3. A successful run loads the data into your Oracle Database. Example of a successful completion message:

      Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed.

1. Enter a unique name in the Instance ID field for the Google Cloud Filestore instance. The ID must contain lowercase letters, numbers, and hyphens. It must start with a letter.
2. The Description field is optional.
3. From the Configure service tier section, select an appropriate service tier based on your migration workload requirements. From the Instance type section, choose your instance type. Instance type affects capacity, performance, scalability, durability, and cost.
4. Choose your Storage type based on your requirements.
5. After choosing your storage type, enter the capacity value in the Capacity field.
   For better performance and lower networking cost, place your instance in the same region as the VMs that will connect to it. Choice is permanent.

   1. Select the Capacity range that suits your file system.
   2. Configure your performance based on your workload and scale.
6. Select the Region and Zone where the Google Cloud Filestore instance will be created. This must align with the network location of the database hosts that will access the file share.
7. From the Set up connections section, select the network and address range that clients will use to access your instance.
   • From the VPC network dropdown list, select your VPC.
   • From the Allocated IP range section, choose the Use an automatically allocated IP range (recommended) option. This IP range will be used to create subnets.
8. From the Configure your file share section, enter your file share name in the field. Choice is permanent. You can use lowercase letters, numbers, and underscores. The file share name must start with a letter. Choose your Access control.
9. The Create labels, Tags sections are optional.
10. Review your information and select the Create button.