How do I troubleshoot issues when I integrate Amazon RDS for Oracle with Amazon S3?

Short description

Before you transfer files between an Amazon RDS for Oracle DB instance and an Amazon S3 bucket, review the following conditions of your setup:

• The DB instance and the S3 bucket are in the same AWS Region.
• The DB instance has the required access to the S3 bucket.
• The DB instance is associated with an option group that includes the S3_INTEGRATION option.
• You upload files only from the Oracle directory object. This directory might be a user-created directory or a DATA_PUMP_DIR directory.
• If you use a user-created directory, then the user that performs the upload and download must have the required permissions on the directory. For more information, see Importing data into Oracle on Amazon RDS.
• If you copy data within subdirectories, specify the name of the subdirectory explicitly when you use the rdsadmin packages. You can't copy subdirectories that use the S3_INTEGRATION option.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshoot AWS CLI errors. Also, make sure that you're using the most recent AWS CLI version.

Be sure that the DB instance has the required access to the S3 bucket

Check if your DB instance has an AWS Identity and Access Management (IAM) role with the least permissions to upload and download from S3. Complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Select the DB instance that you want to check.
4. Choose the Connectivity & security tab.
5. Scroll down to the Manage IAM roles section to check whether you have an active IAM role that includes the following permissions
   S3:ListBucket
   S3:PutObject
   S3:GetObject

To check whether your DB instance has an IAM role with the least permissions, run the following AWS CLI command describe-db-instances:

aws rds describe-db-instances \--db-instance-identifier example-db-instance \--query 'DBInstances[*].[AssociatedRoles[*]]'

After you check whether the IAM role exists, take one of the following actions:

• If the IAM role exists but doesn't have the least permissions, then modify the role to include these permissions.
• If you don't have an IAM role with the required permissions, first create an IAM policy with the permissions required to transfer the files. Next, create a role for your Amazon RDS for Oracle DB instance and attach your policy to the role. Then, associate your IAM role with your Amazon RDS for Oracle DB instance. For more information, see RDS Custom for Oracle requirements and limitations. Be sure to add bucket level permissions to buckets and object level permissions to objects when you create the IAM policy.

If the issue with bucket permissions still persists, then modify the IAM role to grant full access to the S3 bucket:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": [
        "s3:*"
      ],
      "Resource": [
        "arn:aws:s3:::example-bucket/*",
        "arn:aws:s3:::example-bucket"
      ]
    }
  ]
}

Important: The IAM role is modified to grant full access only to test if the issue is caused by the improper setup of IAM permissions. After you resolve the issue, return the custom permissions to their previous state.

To upload files to a specific folder in Amazon S3, use the p_s3_prefix parameter in the rdsadmin.rdsadmin_s3_tasks.upload_to_s3 procedure. Be sure that you have the required permissions before you upload the files.

For example, to upload files to the example-folder folder to the S3 bucket example-bucket, attach the following IAM policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::example-bucket/example-folder/*"
      ]
    }
  ]
}

Be sure that the DB instance is associated with an option group that includes the S3_INTEGRATION option

To check whether you have an option group attached to your RDS DB instance, complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Choose the DB instance that you want to check.
4. Choose the Configuration tab.
5. Note the Option groups parameter.

If this option group isn't in the in-sync state and you try to add the S3_INTEGRATION option, then the option isn't added. It's a best practice to make sure that the option group is in the in-sync state.

To check if this option group is in in-sync state, run the following AWS CLI command:

aws rds describe-db-instances \--db-instance-identifier example-db-instance \
--query 'DBInstances[*].[OptionGroupMemberships[*]]'

If the option group isn't in the in-sync state, choose the Logs & events tab for your database, and then review the Recent events section. Identify the component that prevents the option group from being in-sync, and then remove this component from the database.

If the option group is in the in-sync state, then check that the S3_INTEGRATION option is included in the option group. For more information, see Listing the options and option settings for an option group. If the S3_INTEGRATION option isn't included in the option group, then add the option. For more information, see Adding the Amazon S3 integration option.

Note: If the S3_INTEGRATION option isn't added to your DB instance, then you receive the error "ORA-00904: "RDSADMIN"."RDSADMIN_S3_TASKS"."UPLOAD_TO_S3": invalid identifier";". This error shows you that the required procedures to upload and download aren't created on the DB instance.

You don't experience downtime when you add the S3_INTEGRATION option to Amazon RDS. If you plan to use the Apply Immediately option, then be sure that there aren't pending maintenance actions. If there are pending maintenance actions, then those actions are applied with S3 integration. For more information, see Maintaining a DB instance.

Be sure that you upload and download files only from the user-created Oracle directory object or DATA_PUMP_DIR directory

You can't access the Amazon S3 files directly in the database instance. To access these files, you must copy the files to a directory in the Amazon RDS host to access it. To upload and download files from the DB instance to an S3 bucket, complete the following steps:

1. Use the expdp command, the dbms_datapump API in the DATA_DUMP_DIR, or the custom directory to copy the export files in Amazon RDS to Amazon S3. Be sure to create the dump files.
2. Use the rdsadmin.rdsadmin_s3_tasks.upload_to_s3 procedure to upload files to S3. For more information, see Uploading files from your RDS for Oracle DB instance to an Amazon S3 bucket.
3. Use the rdsadmin. rdsadmin_s3_tasks.download_from_s3 procedure to download files from the S3 bucket to the DATA_PUMP_DIR folder or a user-created directory. For more information, see Downloading files from an Amazon S3 bucket to an Oracle DB instance.

View the log that's generated when you run the rdsadmin package upload or download procedure to monitor the status of the file transfer. To view these logs, complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Choose the DB instance that you want to view.
4. Choose the Logs & events tab.
5. Under the Logs section, enter the task ID returned by the procedure in the search filter.
6. Select the file that's returned.
7. Choose Watch to view the logs.
   -or-
   Choose Download to download the logs.

To read the log file generated during the upload/download process, run the following command:

SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-task-id.log'));

Note: Be sure to replace task-id with the task ID returned by the procedure.

Related information

Amazon S3 integration

Why is my Amazon RDS for Oracle DB instance using more storage than expected?