How to get files in directories on RDS Oracle through Amazon S3 Integration

Scenarios

• You’d like to get files, e.g., log files, dump files…in the directories on RDS for Oracle.

You may use database link along with UTL_FILE package to transfer files in directories between Oracle RDS instances. However, if you want to get the files out from RDS directories, Amazon S3 Integration may be a solution.

Here’ s the step-by-step I referenced AWS document to get files from RDS Oracle directories through Amazon S3 integration.

Step-by-step

1. Create an AWS IAM policy with the required permissions to download files from RDS to S3 bucket.
2. Create an IAM role, attach the created policy to it, and then attach the role to your Oracle RDS instances.
3. Associate the IAM role with your RDS.
4. Add the Amazon S3 integration option.

5. Upload files in the RDS directory to S3 bucket.

6. Download files from S3 bucket.

1. Create an AWS IAM policy with the required permissions to download files from RDS to S3 bucket.

Open the IAM Management Console, under Access management, choose Policies and then Choose Create policy.

On the Visual editor tab, choose Choose a service, find S3 Service, and then choose it.

Choose the bucket permissions and object permissions required to download files from an RDS to S3 bucket.

• Expand List, and then choose ListBucket.
• Expand Write, and then choose PutObject.

Choose Resources, and then choose Add ARN for bucket to a specific S3 bucket you use to put the files from RDS.

Fill in the bucket name, and the ARN for bucket will be filled in automatically.

You can choose Any to grant permissions to any object in the bucket.

Choose Next: Tags and then Next: Review.

Enter a name for your policy. Description is optional. And then Choose Create Policy.

2. Create an IAM role, attach the created policy to it, and then attach the role to your Oracle RDS instances.

Under Access Management, choose Roles. And then choose Create role.

From the type of trusted entity, choose AWS service, and then choose RDS.

For Select your use case, choose RDS-Add Role to Database. And then Choose Next: Permissions.

Search the policy just created from previous section. And then choose Next: Tags and then Next: Review.

Enter a name for Role Name. And then choose Create role.

3. Associate the IAM role with your RDS.

Open Amazon RDS console and then Choose Databases from the navigation pane. Choose the Database instance, on the Connectivity & security tab, in the Manage IAM roles section, choose the role created from previous section. For Feature, choose S3_INTEGRATION. And then choose Add role.

4. Add the Amazon S3 integration option.

Choose Option groups from the navigation pane, choose the option groups that you want to add S3 integration option. From the Options section, choose Add option.

5. Upload files in the RDS directory to S3 bucket.

The following example uploads the files with prefix expdpin the myrdsdirectory directory to the S3 bucket named mys3bucket.

SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
p_bucket_name => 'mys3bucket',
p_prefix => 'expdp',
p_s3_prefix => '',
p_directory_name => 'myrdsdirectory')
AS TASK_ID FROM DUAL;

You can view the result by the task’s log file. Replace the task-id with your task id.

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

6. Download files from S3 bucket.

Open Amazon S3 console, choose Buckets from the navigation pane. From the buckets choose the buckets you put the files. On the Objects tab, you will see the objects from RDS directory. You may select the files and then choose Actions, choose Download to download the files.