Migrate to Autonomous AI Database

1. An Amazon S3 bucket contains a data file that you want to import, for example, data.txt. The sample file in this example has the following contents:

   
   Direct Sales
   Tele Sales
   Catalog
   Internet
   Partners

2. From AWS console, navigate to the AWS Management Console.
3. Grant access privileges to the AWS IAM user for the Amazon S3 bucket.
4. Create an access key for the user and obtain an object URL for the data file stored in the Amazon S3 bucket.

1. To access data from the Amazon S3 bucket, which is not publicly accessible, you must create a credential to authenticate with Amazon S3. Log in to <your PDB name> with the database user <your db user name> and run the following script to create credentials 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. List the objects that are visible to Autonomous AI Database by using the stored credential.

   
     SELECT *
     FROM DBMS_CLOUD.LIST_OBJECTS(
       credential_name => 'AWS_S3_CRED',
       location        => 'https://s3.amazonaws.com/mybucket/',
       prefix          => 'sample'
     );

1. Stage files in Amazon S3: Upload CSV, JSON, or Parquet files to the mapped Amazon S3 bucket using Amazon S3 File Gateway and obtain the Amazon S3 file URI path.
   Note
   
   An Amazon S3 file URI path uses the format s3://bucket-name/key-name.
2. Load data using DBMS_CLOUD.COPY_DATA:

   BEGIN
     DBMS_CLOUD.COPY_DATA(
       table_name      => 'SAMPLE',
       credential_name => 'AWS_S3_CRED',
       file_uri_list   => 'https://s3.amazonaws.com/mybucket/sample.csv',
       format          => json_object('delimiter' value ',')
     );
   END;
   /

For detailed information about the parameters, see COPY_DATA Procedure.

1. Run the following query to confirm ingestion and ensure data integrity.

   SELECT * FROM <Table_Name>;

1. Enable compression: Store files in compressed formats such as GZIP to reduce transfer costs.
2. Monitor performance: Use Autonomous AI Database monitoring tools and Amazon CloudWatch for end-to-end visibility.

Automation supports repeatability, and monitoring helps optimize throughput, latency, and costs.

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';