Migrate to Autonomous AI Database

• Source: An Oracle AI Database platform, such as Autonomous AI Database, Oracle RAC, or a standalone Oracle AI Database instance on Linux.
• Target: Autonomous AI Database running on Oracle AI Database@Azure.

• A container that contains the data files, such as a Data Pump dump file.

• For Oracle AI Database@Azure: The service supports internal connectivity within the same Azure region and virtual network by default, often by using private endpoints for performance and security.
• For on premises or other OCI based sources: Use internet connectivity or a private link by using Oracle Interconnect for Microsoft Azure, if applicable.

• An Azure Storage account name and access key with permissions to access the container.
• Alternatively, use an Azure service principal for improved security in supported setups.

To grant the required roles to access data in Azure Blob Storage, see the Assign an Azure role for access to blob data documentation. For example, you can assign the roles by using the Azure CLI, as shown in the following example:

# replace the placeholder values in brackets with your own values
az role assignment create \
    --role "Storage Blob Data Contributor" \
    --assignee <email> \
    --scope "/subscriptions/<subscription>/resourceGroups/<resource-group>/providers/Microsoft.Storage/storageAccounts/<storage-account>/blobServices/default/containers/<container>"

1. Open your storage account page.
2. Select Access keys under Security + networking.
3. Select Show keys.
4. Copy the Storage account name and Key information. Store them in a secure location. Then, you can select the Hide button.
   Note
   
   You need these keys when you run the CREATE_CREDENTIAL command on the database.
   

To obtain the direct URL of the file stored in your Azure Blob Storage container for importing into Oracle AI Database, complete the following steps:

1. In the Azure portal, open your storage account.
2. Select Containers in the left menu.
3. Select the target container.
4. Select the specific object or file.
5. Copy the value shown as Object URL.
   Note
   
   You need this URL for the HOST parameter in the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE command.
   

1. From the Azure portal, navigate to Storage accounts. and then select your account from the list.
2. From the left menu, expand the Security + networking section, and then select Networking.
3. Select the Private endpoints tab and select your private endpoint from the list.
4. From the left menu, expand the Settings section and then select DNS configuration.
5. From the Network interface section, obtain the FQDN and IP address information. Record these information as they are required while creating Private DNS Zone in OCI.

1. From the Azure portal, navigate to your resource. From the Overview section, select the Go to OCI link.

1. From the OCI console, select Networking and then select Virtual cloud networks.
2. From the list, select your Virtual Cloud Network (VCN) that you are using.
3. From the Details tab, select the DNS Resolver link.
4. From the Private resolvers page, select the Details tab and then select the Default private view link.
5. From the Private views page, select the Private zones tab and then select the Create zone button. For example, blob.core.windows.net.
6. After creating a private zone, select the Records tab and then add a record. For example, demoforblob. Ensure that the record points to the actual IP address of Azure Blob Storage.
7. Publish the changes.
8. Update the Network Security Group (NSG) in OCI to allow traffic from the VPC where the Azure Blob Storage resides.
9. After you complete the configuration, the fully qualified domain name from the OCI database resolves to the Azure Blob Storage endpoint.

1. Ensure that you have a successful connection from an Oracle AI Database client or tool to Autonomous AI Database. For more information, see Connect - Autonomous AI Database (Serverless).
2. Make sure that all outbound connections to the target host comply with the private endpoint egress rules and are limited by those rules.

   ALTER DATABASE PROPERTY SET ROUTE_OUTBOUND_CONNECTIONS =  ‘ENFORCE_PRIVATE_ENDPOINT’;

3. Add the Azure Blob Storage FQDN to the Access Control List (ACL).
4. Use the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure to add an Access Control Entry (ACE) for the network host. Below is an example to replace it with your Azure Blob Storage endpoint from above (FQDN).

   
   BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(  
   
   host => 'demoforblob.blob.core.windows.net',  
   
   ace  =>  xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),
   
   principal_name => 'ADMIN',  
   
   principal_type => xs_acl.ptype_db));
   
   END;

5. Run the appropriate DBMS_CLOUD.CREATE_CREDENTIAL statement (or similar procedure) to register credentials for Azure Blob Storage access. Make sure to provide the Access Key from above in the credential parameters.

   
   BEGIN
       DBMS_CLOUD.CREATE_CREDENTIAL(
           credential_name => '<Credential_Name>',
           username => '<Storage_Account_Name>',
           password => '<Key>'
       );
   END;

   Replace the placeholders with your values:

   • <Credential_Name>: A name for the credential in the database.
   • <Storage_Account_Name>: Your Azure Storage account name.
   • <Key>: The access key for the Azure Storage account.

1. To verify that your Oracle AI Database can access the Azure Blob Storage container, list the objects inside it by using the LIST_OBJECTS function in the DBMS_CLOUD package.
2. Use the credential name that you created in step 1 and the container location URL that you obtained previously.
3. Make sure to omit the specific object name at the end of the URL.

   select * from dbms_cloud.list_objects('AZURECRED','https://demoforblob.blob.core.windows.net/demoblob/');

1. To import the data, use the COPY_DATA procedure of the DBMS_CLOUD package.

   create table mytab (id number, name VARCHAR2(64), city VARCHAR2(64));

2. Run the following procedure to load data from a file in Azure Blob Storage into a table in your Oracle AI Database:

   
   BEGIN
         DBMS_CLOUD.COPY_DATA(
             table_name => 'mytab',
             credential_name => 'AZURECRED',
             file_uri_list => 'https://demoforblob.blob.core.windows.net/demoblob/data.csv',
             format => json_object('delimiter' value ',')
         );
     END;

3. Run the following query to retrieve all columns and all rows from the table named MYTAB:

   select * from mytab;

4. Run the following procedure to import the data from .txt file:

   BEGIN
       DBMS_CLOUD.COPY_DATA(
           table_name => 'mydata',
           credential_name => 'AZURECRED',
           file_uri_list => 'https://demoforblob.blob.core.windows.net/demoblob/data.txt',
           format => json_object('delimiter' value ',')
       );
   END;

5. Run the following procedure to import the data using .dmp file.

   
   # data pump parameter file: impdemo.par
   directory=DATA_PUMP_DIR
   credential=AZURECRED
   schemas=demouser
   dumpfile=https://demoforblob.blob.core.windows.net/demoblob/demouser.dmp
   logfile=demoimp.log
   
   # data pump import commandimpdp userid=ADMIN@adb_high parfile=impdemo.par

1. After you import data by using Data Pump into Autonomous AI Database, you can check the files that the import creates in DATA_PUMP_DIR on the database server:

   select object_name from DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') order by last_modified desc;

2. Copy the log file directly to Azure Blob Storage and download it from there:

   BEGIN
       DBMS_CLOUD.PUT_OBJECT(
           'AZURECRED',
           'https://demoforblob.blob.core.windows.net/demoblob/logs/',
           'DATA_PUMP_DIR',
           'demoimp.log');
   END;

3. If you keep files in Azure Blob Storage and want to query them as tables in Oracle AI Database, use the DBMS_CLOUD.CREATE_EXTERNAL_TABLE procedure to define an external table that references the Azure Blob Storage location.

   
   BEGIN
         DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
             table_name       => 'exttab',
             credential_name  => 'AZURECRED',
             file_uri_list    => 'https://demoforblob.blob.core.windows.net/demoblob/data.csv',
             column_list      => 'id NUMBER, name VARCHAR2(64)',
             format           => json_object('delimiter' value ',')
         );
     END;

4. Run the following query to retrieve all columns and all rows from the table :

   select * from exttab;