Migrate to Autonomous AI Database (Dedicated)

To access data from Amazon S3 that is not publicly accessible, use a credential to authenticate with Amazon S3.

Connect to <your_PDB_name> as the database user <your_database_user_name>, and run the following script to create a credential based on the access key ID and secret access key for the IAM user <your_IAM_user>:

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

For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.

1. Upload CSV, JSON, or Parquet files into the mapped Amazon S3 bucket, and obtain the file URI path.
2. An Amazon S3 URI path uses the following format:

   https://s3.<region>.amazonaws.com/<bucket-name>/<path_to_file>

3. Use DBMS_CLOUD.COPY_DATA to load data directly into Oracle Autonomous AI Database tables:

   
   BEGIN
     DBMS_CLOUD.COPY_DATA(
       table_name      => '<Table_Name>',
       credential_name => 'AWS_S3_CRED',
       file_uri_list   => 'https://s3.<region>.amazonaws.com/<bucket-name>/sample.csv',
       format          => json_object('delimiter' VALUE ',')
     );
   END;
   /

For detailed information about the parameters, see COPY_DATA Procedure.

Run queries to confirm ingestion and data integrity:

SELECT *
FROM <Table_Name>;

Use automation for repeatable loading operations.

1. Store files in compressed formats, such as GZIP, to reduce transfer costs.
2. Use Oracle Autonomous AI Database monitoring tools and Amazon CloudWatch to monitor throughput, latency, and cost.

1. Add the EFS host to the Access Control List (ACL) to allow the database to connect and resolve the EFS endpoint by using the following PL/SQL script:

   BEGIN
     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
       host => 'fs-0586ab576856f1d00.efs.us-east-1.amazonaws.com',
       ace  => xs$ace_type(
         privilege_list => xs_name_list('connect','resolve'),
         principal_name => 'ADMIN',
         principal_type => xs_acl.ptype_db
       )
     );
   END;
   /
   COMMIT;

2. Verify connectivity:

   SELECT utl_inaddr.get_host_address(
            'fs-0586ab576856f1d00.efs.us-east-1.amazonaws.com'
          ) AS ip_address
   FROM dual;

3. The output will return the IP address of your EFS mount point.

1. Define a directory object in the database that points to the EFS file system path:

   CREATE DIRECTORY EFS_DIR AS 'efs';

1. Attach the EFS mount to the database using NFSv4:

   BEGIN
     DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM(
       file_system_name     => 'AWS_EFS',
       file_system_location => 'fs-0586ab576856f1d00.efs.us-east-1.amazonaws.com:/',
       directory_name       => 'EFS_DIR',
       description          => 'AWS EFS attached via NFSv4',
       params               => JSON_OBJECT('nfs_version' VALUE 4)
     );
   END;
   /

1. Confirm that the directory is correctly mapped to the Amazon Elastic File System mount path:

   SELECT directory_name, directory_path
   FROM   dba_directories
   WHERE  directory_name = 'EFS_DIR';

1. Write a File:

   DECLARE
     f UTL_FILE.FILE_TYPE;
   BEGIN
     f := UTL_FILE.FOPEN('EFS_DIR', 'test_file.txt', 'w');
     UTL_FILE.PUT_LINE(f, 'Hello from ADB-D to EFS!');
     UTL_FILE.FCLOSE(f);
   END;
   /

2. Read the File Back:

   DECLARE
     f   UTL_FILE.FILE_TYPE;
     txt VARCHAR2(32767);
   BEGIN
     f := UTL_FILE.FOPEN('EFS_DIR', 'test_file.txt', 'r');
     UTL_FILE.GET_LINE(f, txt);
     DBMS_OUTPUT.PUT_LINE('Read from EFS: ' || txt);
     UTL_FILE.FCLOSE(f);
   END;
   /

1. Use Oracle Data Pump (expdp) to export the schema directly to the Amazon Elastic File System directory.

   expdp admin/DBatAWS_1234@demofspdb_tp schemas=TEST directory=EFS_DIR dumpfile=test.dmp logfile=test.log

2. Verify exported files:

   SELECT OBJECT_NAME
   FROM   DBMS_CLOUD.LIST_FILES('EFS_DIR');

1. Once the schema dump files are available on Amazon Elastic File System, you can import directly into the target database:

   impdp admin/DBatAWS_1234@demofspdb_tp schemas=TEST directory=EFS_DIR dumpfile=test.dmp logfile=import_test.log

2. Verify imported objects:

   SELECT table_name
   FROM   user_tables
   WHERE  owner = 'TEST';