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

Short description

When you transfer files between an Amazon RDS for Oracle DB instance and an Amazon S3 bucket, verify the following conditions:

• 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're uploading files only from the Oracle directory object. This directory might be a user-created directory or a DATA_PUMP_DIR directory.
• For a user-created directory, the user that's performing the upload and download must have the required read and write privileges on the directory. For more information, see How do I manage user privileges and roles in my Amazon RDS for Oracle DB instance?
• If you're copying data within subdirectories, then specify the name of the subdirectory explicitly when you use the rdsadmin packages. You can't use the S3_INTEGRATION option to copy subdirectories.

Resolution

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 privileges to upload and download from S3:

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 Connectivity & security tab.
5. Scroll down to the Manage IAM roles section to check if you have an active IAM role with the following privileges:
   S3:ListBucket
   S3:PutObject
   S3:GetObject

You can also use the AWS Command Line Interface (AWS CLI) command describe-db-instances to check if your DB instance has an IAM role with these least privileges:

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

Note: If you receive errors when running AWS CLI commands, make sure that you're using the most recent version of the AWS CLI.

After you check if the IAM role exists, do either of the following based on your finding:

• If the IAM role exists but doesn't have these least privileges, then modify the role to include these permissions.
• If you don't have an IAM role with the required permissions, then create an IAM policy. Give this policy the permissions to transfer the files from the S3 bucket to Amazon RDS. Be sure to add bucket level permissions to buckets and object level permissions to objects when creating the IAM policy.

If you still have an issue with bucket permissions, 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: This modifies the IAM role to grant full access only to test if the issue results from IAM privileges. After you resolve the issue, adjust the custom privileges as needed.

To upload files to a specific folder in 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

Check if you have an option group attached to your RDS DB instance:

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're adding the S3_INTEGRATION option, then the option doesn't add successfully. Therefore, be 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 scroll down to the Recent events section. Identify the component that prevents the option group from being in-sync, and remove this component from the database.

If the option group is in the in-sync state, then check if 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 is not added to your DB instance, you get the error ORA-00904: "RDSADMIN"."RDSADMIN_S3_TASKS"."UPLOAD_TO_S3": invalid identifier". This error indicates that the required procedures to upload and download aren't created on the DB instance.

Adding the S3_INTEGRATION option to Amazon RDS doesn't result in downtime. If you want to use the Apply Immediately option, be sure that there are no pending maintenance actions. If there are any pending actions, then those actions take effect with Amazon S3 integration. For more information, see Maintaining a DB instance.

Be sure that you're uploading/downloading files only from the user-created Oracle directory object or DATA_PUMP_DIR directory

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

1. To copy the export files in Amazon RDS to S3, first create the dump files. To do this, use the expdp command or the dbms_datapump API in the DATA_DUMP_DIR or custom directory. Then, use the rdsadmin.rdsadmin_s3_tasks.upload_to_s3 procedure to upload files to S3. For information on the parameters of this procedure and example queries for uploading files, see Uploading files from an Oracle DB instance to an Amazon S3 bucket.
2. Use the rdsadmin. rdsadmin_s3_tasks.download_from_s3 procedure to download files from the S3 bucket to the DATA_PUMP_DIR folder or any user-created directory. For information on the parameters of this procedure and example queries for download files, see Downloading files from an Amazon S3 bucket to an Oracle DB instance.

To monitor the status of file transfer, view the log that's generated when you run the procedure to either upload or download the rdsadmin packages:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Choose the DB instance for which you want to view the logs.
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.

You can also run the following command to read the log file generated during the upload or download process:

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

Note: 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?