# Adding options to Oracle DB instances In Amazon RDS, an option is an additional feature. Following, you can find a description of options that you can add to Amazon RDS instances running the Oracle DB engine. **Topics** + [Overview of Oracle DB options](Appendix.Oracle.Options.overview.md) + [Amazon S3 integration](oracle-s3-integration.md) + [Oracle Application Express (APEX)](Appendix.Oracle.Options.APEX.md) + [Amazon EFS integration](oracle-efs-integration.md) + [Oracle Java virtual machine](oracle-options-java.md) + [Oracle Enterprise Manager](Oracle.Options.OEM.md) + [Oracle Label Security](Oracle.Options.OLS.md) + [Oracle Locator](Oracle.Options.Locator.md) + [Oracle native network encryption](Appendix.Oracle.Options.NetworkEncryption.md) + [Oracle OLAP](Oracle.Options.OLAP.md) + [Oracle Secure Sockets Layer](Appendix.Oracle.Options.SSL.md) + [Oracle Spatial](Oracle.Options.Spatial.md) + [Oracle SQLT](Oracle.Options.SQLT.md) + [Oracle Statspack](Appendix.Oracle.Options.Statspack.md) + [Oracle time zone](Appendix.Oracle.Options.Timezone.md) + [Oracle time zone file autoupgrade](Appendix.Oracle.Options.Timezone-file-autoupgrade.md) + [Oracle Transparent Data Encryption](Appendix.Oracle.Options.AdvSecurity.md) + [Oracle UTL\$1MAIL](Oracle.Options.UTLMAIL.md) + [Oracle XML DB](Appendix.Oracle.Options.XMLDB.md) # Overview of Oracle DB options To enable options for your Oracle database, add them to an option group, and then associate the option group with your DB instance. For more information, see [Working with option groups](USER_WorkingWithOptionGroups.md). **Topics** + [Summary of Oracle Database options](#Appendix.Oracle.Options.summary) + [Options supported for different editions](#Appendix.Oracle.Options.editions) + [Memory requirements for specific options](#Appendix.Oracle.Options.memory) ## Summary of Oracle Database options You can add the following options for Oracle DB instances. **** | Option | Option ID | | --- | --- | | [Amazon S3 integration](oracle-s3-integration.md) | `S3_INTEGRATION` | | [Oracle Application Express (APEX)](Appendix.Oracle.Options.APEX.md) | `APEX` `APEX-DEV` | | [Oracle Enterprise Manager](Oracle.Options.OEM.md) | `OEM` `OEM_AGENT` | | [Oracle Java virtual machine](oracle-options-java.md) | `JVM` | | [Oracle Label Security](Oracle.Options.OLS.md) | `OLS` | | [Oracle Locator](Oracle.Options.Locator.md) | `LOCATOR` | | [Oracle native network encryption](Appendix.Oracle.Options.NetworkEncryption.md) | `NATIVE_NETWORK_ENCRYPTION` | | [Oracle OLAP](Oracle.Options.OLAP.md) | `OLAP` | | [Oracle Secure Sockets Layer](Appendix.Oracle.Options.SSL.md) | `SSL` | | [Oracle Spatial](Oracle.Options.Spatial.md) | `SPATIAL` | | [Oracle SQLT](Oracle.Options.SQLT.md) | `SQLT` | | [Oracle Statspack](Appendix.Oracle.Options.Statspack.md) | `STATSPACK` | | [Oracle time zone](Appendix.Oracle.Options.Timezone.md) | `Timezone` | | [Oracle time zone file autoupgrade](Appendix.Oracle.Options.Timezone-file-autoupgrade.md) | `TIMEZONE_FILE_AUTOUPGRADE` | | [Oracle Transparent Data Encryption](Appendix.Oracle.Options.AdvSecurity.md) | `TDE` | | [Oracle UTL\$1MAIL](Oracle.Options.UTLMAIL.md) | `UTL_MAIL` | | [Oracle XML DB](Appendix.Oracle.Options.XMLDB.md) | `XMLDB` | ## Options supported for different editions RDS for Oracle prevents you from adding options to an edition if they aren't supported. To find out which RDS options are supported in different Oracle Database editions, use the command `aws rds describe-option-group-options`. The following example lists supported options for Oracle Database 19c Enterprise Edition. ``` aws rds describe-option-group-options \ --engine-name oracle-ee \ --major-engine-version 19 ``` For more information, see [describe-option-group-options](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-option-group-options.html) in the *AWS CLI Command Reference*. ## Memory requirements for specific options Some options require additional memory to run on your DB instance. For example, Oracle Enterprise Manager Database Control uses about 300 MB of RAM. If you enable this option for a small DB instance, you might encounter performance problems due to memory constraints. You can adjust the Oracle parameters so that the database requires less RAM. Alternatively, you can scale up to a larger DB instance. # Amazon S3 integration You can transfer files between your RDS for Oracle DB instance and an Amazon S3 bucket. You can use Amazon S3 integration with Oracle Database features such as Oracle Data Pump. For example, you can download Data Pump files from Amazon S3 to your RDS for Oracle DB instance. For more information, see [Importing data into Oracle on Amazon RDS](Oracle.Procedural.Importing.md). **Note** Your DB instance and your Amazon S3 bucket must be in the same AWS Region. **Topics** + [Configuring IAM permissions for RDS for Oracle integration with Amazon S3](oracle-s3-integration.preparing.md) + [Adding the Amazon S3 integration option](oracle-s3-integration.preparing.option-group.md) + [Transferring files between Amazon RDS for Oracle and an Amazon S3 bucket](oracle-s3-integration.using.md) + [Troubleshooting Amazon S3 integration](#oracle-s3-integration.troubleshooting) + [Removing the Amazon S3 integration option](oracle-s3-integration.removing.md) # Configuring IAM permissions for RDS for Oracle integration with Amazon S3 For RDS for Oracle to integrate with Amazon S3, your DB instance must have access to an Amazon S3 bucket. The Amazon VPC used by your DB instance doesn't need to provide access to the Amazon S3 endpoints. RDS for Oracle supports transferring files between a DB instance in one account and an Amazon S3 bucket in a different account. Where additional steps are required, they are noted in the following sections. **Topics** + [Step 1: Create an IAM policy for your Amazon RDS role](#oracle-s3-integration.preparing.policy) + [Step 2: (Optional) Create an IAM policy for your Amazon S3 bucket](#oracle-s3-integration.preparing.policy-bucket) + [Step 3: Create an IAM role for your DB instance and attach your policy](#oracle-s3-integration.preparing.role) + [Step 4: Associate your IAM role with your RDS for Oracle DB instance](#oracle-s3-integration.preparing.instance) ## Step 1: Create an IAM policy for your Amazon RDS role In this step, you create an AWS Identity and Access Management (IAM) policy with the permissions required to transfer files between your Amazon S3 bucket and your RDS DB instance. This step assumes that you have already created an S3 bucket. Before you create the policy, note the following pieces of information: + The Amazon Resource Name (ARN) for your bucket + The ARN for your AWS KMS key, if your bucket uses SSE-KMS or SSE-S3 encryption **Note** An RDS for Oracle DB instance can't access Amazon S3 buckets encrypted with SSE-C. For more information, see [Protecting data using server-side encryption](https://docs.aws.amazon.com/AmazonS3/latest/userguide/serv-side-encryption.html) in the *Amazon Simple Storage Service User Guide*. ### Console **To create an IAM policy to allow Amazon RDS to access your Amazon S3 bucket** 1. Open the [IAM Management Console](https://console.aws.amazon.com/iam/home?#home). 1. Under **Access management**, choose **Policies**. 1. Choose **Create Policy**. 1. On the **Visual editor** tab, choose **Choose a service**, and then choose **S3**. 1. For **Actions**, choose **Expand all**, and then choose the bucket permissions and object permissions required to transfer files from an Amazon S3 bucket to Amazon RDS. For example, do the following: + Expand **List**, and then select **ListBucket**. + Expand **Read**, and then select **GetObject**. + Expand **Write**, and then select **PutObject**, **DeleteObject**, **AbortMultipartUpload**, and **ListMultipartUploadParts**. The multipart upload permissions are required when uploading large files (100 MB or larger) to Amazon S3. + Expand **Permissions management**, and then select **PutObjectAcl**. This permission is necessary if you plan to upload files to a bucket owned by a different account, and this account needs full control of the bucket contents. *Object permissions* are permissions for object operations in Amazon S3. You must grant them for objects in a bucket, not the bucket itself. For more information, see [Permissions for object operations](https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-with-s3-actions.html#using-with-s3-actions-related-to-objects). 1. Choose **Resources**, and then do the following: 1. Choose **Specific**. 1. For **bucket**, choose **Add ARN**. Enter your bucket ARN. The bucket name is filled in automatically. Then choose **Add**. 1. If the **object** resource is shown, either choose **Add ARN** to add resources manually or choose **Any**. **Note** You can set **Amazon Resource Name (ARN)** to a more specific ARN value to allow Amazon RDS to access only specific files or folders in an Amazon S3 bucket. For more information about how to define an access policy for Amazon S3, see [Managing access permissions to your Amazon S3 resources](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-access-control.html). 1. (Optional) Choose **Add additional permissions** to add resources to the policy. For example, do the following: 1. If your bucket is encrypted with a custom KMS key, select **KMS** for the service. 1. For **Manual actions**, select the following: + **Encrypt** + **ReEncrypt from** and **ReEncrypt to** + **Decrypt** + **DescribeKey** + **GenerateDataKey** 1. For **Resources**, choose **Specific**. 1. For **key**, choose **Add ARN**. Enter the ARN of your custom key as the resource, and then choose **Add**. For more information, see [Protecting Data Using Server-Side Encryption with KMS keys Stored in AWS Key Management Service (SSE-KMS)](https://docs.aws.amazon.com/AmazonS3/latest/userguide/UsingKMSEncryption.html) in the *Amazon Simple Storage Service User Guide*. 1. If you want Amazon RDS to access to access other buckets, add the ARNs for these buckets. Optionally, you can also grant access to all buckets and objects in Amazon S3. 1. Choose **Next: Tags** and then **Next: Review**. 1. For **Name**, enter a name for your IAM policy, for example `rds-s3-integration-policy`. You use this name when you create an IAM role to associate with your DB instance. You can also add an optional **Description** value. 1. Choose **Create policy**. ### AWS CLI Create an AWS Identity and Access Management (IAM) policy that grants Amazon RDS access to an Amazon S3 bucket. After you create the policy, note the ARN of the policy. You need the ARN for a subsequent step. Include the appropriate actions in the policy based on the type of access required: + `GetObject` – Required to transfer files from an Amazon S3 bucket to Amazon RDS. + `ListBucket` – Required to transfer files from an Amazon S3 bucket to Amazon RDS. + `PutObject` – Required to transfer files from Amazon RDS to an Amazon S3 bucket. + `AbortMultipartUpload` – Required for multipart uploads when transferring large files (100 MB or larger) from Amazon RDS to an Amazon S3 bucket. + `ListMultipartUploadParts` – Required for multipart uploads when transferring large files (100 MB or larger) from Amazon RDS to an Amazon S3 bucket. The following AWS CLI command creates an IAM policy named `rds-s3-integration-policy` with these options. It grants access to a bucket named `amzn-s3-demo-bucket`. **Example** For Linux, macOS, or Unix: ``` aws iam create-policy \ --policy-name rds-s3-integration-policy \ --policy-document '{ "Version": "2012-10-17", "Statement": [ { "Sid": "s3integration", "Action": [ "s3:GetObject", "s3:ListBucket", "s3:PutObject", "s3:AbortMultipartUpload", "s3:ListMultipartUploadParts" ], "Effect": "Allow", "Resource": [ "arn:aws:s3:::amzn-s3-demo-bucket", "arn:aws:s3:::amzn-s3-demo-bucket/*" ] } ] }' ``` The following example includes permissions for custom KMS keys. ``` aws iam create-policy \ --policy-name rds-s3-integration-policy \ --policy-document '{ "Version": "2012-10-17", "Statement": [ { "Sid": "s3integration", "Action": [ "s3:GetObject", "s3:ListBucket", "s3:PutObject", "kms:Decrypt", "kms:Encrypt", "kms:ReEncrypt*", "kms:GenerateDataKey", "kms:DescribeKey", ], "Effect": "Allow", "Resource": [ "arn:aws:s3:::amzn-s3-demo-bucket", "arn:aws:s3:::amzn-s3-demo-bucket/*", "arn:aws:kms:::your-kms-arn" ] } ] }' ``` For Windows: ``` aws iam create-policy ^ --policy-name rds-s3-integration-policy ^ --policy-document '{ "Version": "2012-10-17", "Statement": [ { "Sid": "s3integration", "Action": [ "s3:GetObject", "s3:ListBucket", "s3:PutObject", "s3:AbortMultipartUpload", "s3:ListMultipartUploadParts" ], "Effect": "Allow", "Resource": [ "arn:aws:s3:::amzn-s3-demo-bucket", "arn:aws:s3:::amzn-s3-demo-bucket/*" ] } ] }' ``` The following example includes permissions for custom KMS keys. ``` aws iam create-policy ^ --policy-name rds-s3-integration-policy ^ --policy-document '{ "Version": "2012-10-17", "Statement": [ { "Sid": "s3integration", "Action": [ "s3:GetObject", "s3:ListBucket", "s3:PutObject", "kms:Decrypt", "kms:Encrypt", "kms:ReEncrypt", "kms:GenerateDataKey", "kms:DescribeKey", ], "Effect": "Allow", "Resource": [ "arn:aws:s3:::amzn-s3-demo-bucket", "arn:aws:s3:::amzn-s3-demo-bucket/*", "arn:aws:kms:::your-kms-arn" ] } ] }' ``` ## Step 2: (Optional) Create an IAM policy for your Amazon S3 bucket This step is necessary only in the following conditions: + You plan to upload files to an Amazon S3 bucket from one account (account A) and access them from a different account (account B). + Account B owns the bucket. + Account B needs full control of objects loaded into the bucket. If the preceding conditions don't apply to you, skip to [Step 3: Create an IAM role for your DB instance and attach your policy](#oracle-s3-integration.preparing.role). To create your bucket policy, make sure you have the following: + The account ID for account A + The user name for account A + The ARN value for the Amazon S3 bucket in account B ### Console **To create or edit a bucket policy** 1. Sign in to the AWS Management Console and open the Amazon S3 console at [https://console.aws.amazon.com/s3/](https://console.aws.amazon.com/s3/). 1. In the **Buckets** list, choose the name of the bucket that you want to create a bucket policy for or whose bucket policy you want to edit. 1. Choose **Permissions**. 1. Under **Bucket policy**, choose **Edit**. This opens the Edit bucket policy page. 1. On the **Edit bucket policy **page, explore **Policy examples** in the *Amazon S3 User Guide*, choose **Policy generator** to generate a policy automatically, or edit the JSON in the **Policy** section. If you choose **Policy generator**, the AWS Policy Generator opens in a new window: 1. On the **AWS Policy Generator** page, in **Select Type of Policy**, choose **S3 Bucket Policy**. 1. Add a statement by entering the information in the provided fields, and then choose **Add Statement**. Repeat for as many statements as you would like to add. For more information about these fields, see the [IAM JSON policy elements reference](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_elements.html) in the *IAM User Guide*. **Note** For convenience, the **Edit bucket policy** page displays the **Bucket ARN **(Amazon Resource Name) of the current bucket above the **Policy** text field. You can copy this ARN for use in the statements on the **AWS Policy Generator** page. 1. After you finish adding statements, choose **Generate Policy**. 1. Copy the generated policy text, choose **Close**, and return to the **Edit bucket policy** page in the Amazon S3 console. 1. In the **Policy** box, edit the existing policy or paste the bucket policy from the Policy generator. Make sure to resolve security warnings, errors, general warnings, and suggestions before you save your policy. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "ExamplePermissions", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789012:user/account-A-user" }, "Action": [ "s3:PutObject", "s3:PutObjectAcl" ], "Resource": [ "arn:aws:s3:::amzn-s3-demo-destination-bucket", "arn:aws:s3:::amzn-s3-demo-destination-bucket/*" ] } ] } ``` ------ 1. Choose **Save changes**, which returns you to the Bucket Permissions page. ## Step 3: Create an IAM role for your DB instance and attach your policy This step assumes that you have created the IAM policy in [Step 1: Create an IAM policy for your Amazon RDS role](#oracle-s3-integration.preparing.policy). In this step, you create a role for your RDS for Oracle DB instance and then attach your policy to the role. ### Console **To create an IAM role to allow Amazon RDS to access an Amazon S3 bucket** 1. Open the [IAM Management Console](https://console.aws.amazon.com/iam/home?#home). 1. In the navigation pane, choose **Roles**. 1. Choose **Create role**. 1. Choose **AWS service**. 1. For **Use cases for other AWS services:**, choose **RDS** and then **RDS – Add Role to Database**. Then choose **Next**. 1. For **Search** under **Permissions policies**, enter the name of the IAM policy you created in [Step 1: Create an IAM policy for your Amazon RDS role](#oracle-s3-integration.preparing.policy), and select the policy when it appears in the list. Then choose **Next**. 1. For **Role name**, enter a name for your IAM role, for example, `rds-s3-integration-role`. You can also add an optional **Description** value. 1. Choose **Create role**. ### AWS CLI **To create a role and attach your policy to it** 1. Create an IAM role that Amazon RDS can assume on your behalf to access your Amazon S3 buckets. We recommend using the [https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn) and [https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount) global condition context keys in resource-based trust relationships to limit the service's permissions to a specific resource. This is the most effective way to protect against the [confused deputy problem](https://docs.aws.amazon.com/IAM/latest/UserGuide/confused-deputy.html). You might use both global condition context keys and have the `aws:SourceArn` value contain the account ID. In this case, the `aws:SourceAccount` value and the account in the `aws:SourceArn` value must use the same account ID when used in the same statement. + Use `aws:SourceArn` if you want cross-service access for a single resource. + Use `aws:SourceAccount` if you want to allow any resource in that account to be associated with the cross-service use. In the trust relationship, make sure to use the `aws:SourceArn` global condition context key with the full Amazon Resource Name (ARN) of the resources accessing the role. The following AWS CLI command creates the role named `rds-s3-integration-role` for this purpose. **Example** For Linux, macOS, or Unix: ``` aws iam create-role \ --role-name rds-s3-integration-role \ --assume-role-policy-document '{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "aws:SourceAccount": "my_account_ID", "aws:SourceArn": "arn:aws:rds:Region:my_account_ID:db:dbname" } } } ] }' ``` For Windows: ``` aws iam create-role ^ --role-name rds-s3-integration-role ^ --assume-role-policy-document '{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "aws:SourceAccount": "my_account_ID", "aws:SourceArn": "arn:aws:rds:Region:my_account_ID:db:dbname" } } } ] }' ``` For more information, see [Creating a role to delegate permissions to an IAM user](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-user.html) in the *IAM User Guide*. 1. After the role is created, note the ARN of the role. You need the ARN for a subsequent step. 1. Attach the policy you created to the role you created. The following AWS CLI command attaches the policy to the role named `rds-s3-integration-role`. **Example** For Linux, macOS, or Unix: ``` aws iam attach-role-policy \ --policy-arn your-policy-arn \ --role-name rds-s3-integration-role ``` For Windows: ``` aws iam attach-role-policy ^ --policy-arn your-policy-arn ^ --role-name rds-s3-integration-role ``` Replace `your-policy-arn` with the policy ARN that you noted in a previous step. ## Step 4: Associate your IAM role with your RDS for Oracle DB instance The last step in configuring permissions for Amazon S3 integration is associating your IAM role with your DB instance. Note the following requirements: + You must have access to an IAM role with the required Amazon S3 permissions policy attached to it. + You can only associate one IAM role with your RDS for Oracle DB instance at a time. + Your DB instance must be in the **Available** state. ### Console **To associate your IAM role with your RDS for Oracle DB instance** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Databases** from the navigation pane. 1. Choose the RDS for Oracle DB instance name to display its details. 1. On the **Connectivity & security** tab, scroll down to the **Manage IAM roles** section at the bottom of the page. 1. For **Add IAM roles to this instance**, choose the role that you created in [Step 3: Create an IAM role for your DB instance and attach your policy](#oracle-s3-integration.preparing.role). 1. For **Feature**, choose **S3\$1INTEGRATION**. ![\[Add S3_INTEGRATION role\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/ora-s3-integration-role.png) 1. Choose **Add role**. ### AWS CLI The following AWS CLI command adds the role to an Oracle DB instance named `mydbinstance`. **Example** For Linux, macOS, or Unix: ``` aws rds add-role-to-db-instance \ --db-instance-identifier mydbinstance \ --feature-name S3_INTEGRATION \ --role-arn your-role-arn ``` For Windows: ``` aws rds add-role-to-db-instance ^ --db-instance-identifier mydbinstance ^ --feature-name S3_INTEGRATION ^ --role-arn your-role-arn ``` Replace `your-role-arn` with the role ARN that you noted in a previous step. `S3_INTEGRATION` must be specified for the `--feature-name` option. # Adding the Amazon S3 integration option To integrate Amazon RDS for Oracle with Amazon S3, your DB instance must be associated with an option group that includes the `S3_INTEGRATION` option. ## Console **To configure an option group for Amazon S3 integration** 1. Create a new option group or identify an existing option group to which you can add the `S3_INTEGRATION` option. For information about creating an option group, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the `S3_INTEGRATION` option to the option group. For information about adding an option to an option group, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Create a new RDS for Oracle DB instance and associate the option group with it, or modify an RDS for Oracle DB instance to associate the option group with it. For information about creating a DB instance, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). For information about modifying a DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## AWS CLI **To configure an option group for Amazon S3 integration** 1. Create a new option group or identify an existing option group to which you can add the `S3_INTEGRATION` option. For information about creating an option group, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the `S3_INTEGRATION` option to the option group. For example, the following AWS CLI command adds the `S3_INTEGRATION` option to an option group named **myoptiongroup**. **Example** For Linux, macOS, or Unix: ``` aws rds add-option-to-option-group \ --option-group-name myoptiongroup \ --options OptionName=S3_INTEGRATION,OptionVersion=1.0 ``` For Windows: ``` aws rds add-option-to-option-group ^ --option-group-name myoptiongroup ^ --options OptionName=S3_INTEGRATION,OptionVersion=1.0 ``` 1. Create a new RDS for Oracle DB instance and associate the option group with it, or modify an RDS for Oracle DB instance to associate the option group with it. For information about creating a DB instance, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). For information about modifying an RDS for Oracle DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Transferring files between Amazon RDS for Oracle and an Amazon S3 bucket To transfer files between an RDS for Oracle DB instance and an Amazon S3 bucket, you can use the Amazon RDS package `rdsadmin_s3_tasks`. You can compress files with GZIP when uploading them, and decompress them when downloading. **Topics** + [Requirements and limitations for file transfers](#oracle-s3-integration.using.reqs) + [Uploading files from your RDS for Oracle DB instance to an Amazon S3 bucket](#oracle-s3-integration.using.upload) + [Downloading files from an Amazon S3 bucket to an Oracle DB instance](#oracle-s3-integration.using.download) + [Monitoring the status of a file transfer](#oracle-s3-integration.using.task-status) ## Requirements and limitations for file transfers Before transferring files between your DB instance and an Amazon S3 bucket, note the following: + The `rdsadmin_s3_tasks` package transfers files located in a single directory. You can't include subdirectories in a transfer. + The maximum object size in an Amazon S3 bucket is 5 TB. + Tasks created by `rdsadmin_s3_tasks` run asynchronously. + You can upload files from the Data Pump directory, such as `DATA_PUMP_DIR`, or any user-created directory. You can't upload files from a directory used by Oracle background processes, such as the `adump`, `bdump`, or `trace` directories. + The download limit is 2000 files per procedure call for `download_from_s3`. If you need to download more than 2000 files from Amazon S3, split your download into separate actions, with no more than 2000 files per procedure call. + If a file exists in your download folder, and you attempt to download a file with the same name, `download_from_s3` skips the download. To remove a file from the download directory, use the PL/SQL procedure [UTL\$1FILE.FREMOVE](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/UTL_FILE.html#GUID-09B09C2A-2C21-4F70-BF04-D0EEA7B59CAF). ## Uploading files from your RDS for Oracle DB instance to an Amazon S3 bucket To upload files from your DB instance to an Amazon S3 bucket, use the procedure `rdsadmin.rdsadmin_s3_tasks.upload_to_s3`. For example, you can upload Oracle Recovery Manager (RMAN) backup files or Oracle Data Pump files. For more information about working with objects, see [Amazon Simple Storage Service User Guide](https://docs.aws.amazon.com/AmazonS3/latest/dev/UsingObjects.html). For more information about performing RMAN backups, see [Performing common RMAN tasks for Oracle DB instances](Appendix.Oracle.CommonDBATasks.RMAN.md). The `rdsadmin.rdsadmin_s3_tasks.upload_to_s3` procedure has the following parameters. **** | Parameter name | Data type | Default | Required | Description | | --- | --- | --- | --- | --- | | `p_bucket_name` | VARCHAR2 | – | required | The name of the Amazon S3 bucket to upload files to. | | `p_directory_name` | VARCHAR2 | – | required | The name of the Oracle directory object to upload files from. The directory can be any user-created directory object or the Data Pump directory, such as `DATA_PUMP_DIR`. You can't upload files from a directory used by background processes, such as `adump`, `bdump`, and `trace`. You can only upload files from the specified directory. You can't upload files in subdirectories in the specified directory. | | `p_s3_prefix` | VARCHAR2 | – | required | An Amazon S3 file name prefix that files are uploaded to. An empty prefix uploads all files to the top level in the specified Amazon S3 bucket and doesn't add a prefix to the file names. For example, if the prefix is `folder_1/oradb`, files are uploaded to `folder_1`. In this case, the `oradb` prefix is added to each file. | | `p_prefix` | VARCHAR2 | – | required | A file name prefix that file names must match to be uploaded. An empty prefix uploads all files in the specified directory. | | `p_compression_level` | NUMBER | `0` | optional | The level of GZIP compression. Valid values range from `0` to `9`: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-s3-integration.using.html) | | `p_bucket_owner_full_control` | VARCHAR2 | – | optional | The access control setting for the bucket. The only valid values are null or `FULL_CONTROL`. This setting is required only if you upload files from one account (account A) into a bucket owned by a different account (account B), and account B needs full control of the files. | The return value for the `rdsadmin.rdsadmin_s3_tasks.upload_to_s3` procedure is a task ID. The following example uploads all of the files in the `DATA_PUMP_DIR` directory to the Amazon S3 bucket named *amzn-s3-demo-bucket*. The files aren't compressed. ``` SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3( p_bucket_name => 'amzn-s3-demo-bucket', p_prefix => '', p_s3_prefix => '', p_directory_name => 'DATA_PUMP_DIR') AS TASK_ID FROM DUAL; ``` The following example uploads all of the files with the prefix `db` in the `DATA_PUMP_DIR` directory to the Amazon S3 bucket named `amzn-s3-demo-bucket`. Amazon RDS applies the highest level of GZIP compression to the files. ``` SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3( p_bucket_name => 'amzn-s3-demo-bucket', p_prefix => 'db', p_s3_prefix => '', p_directory_name => 'DATA_PUMP_DIR', p_compression_level => 9) AS TASK_ID FROM DUAL; ``` The following example uploads all of the files in the `DATA_PUMP_DIR` directory to the Amazon S3 bucket named `amzn-s3-demo-bucket`. The files are uploaded to a `dbfiles` folder. In this example, the GZIP compression level is *1*, which is the fastest level of compression. ``` SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3( p_bucket_name => 'amzn-s3-demo-bucket', p_prefix => '', p_s3_prefix => 'dbfiles/', p_directory_name => 'DATA_PUMP_DIR', p_compression_level => 1) AS TASK_ID FROM DUAL; ``` The following example uploads all of the files in the `DATA_PUMP_DIR` directory to the Amazon S3 bucket named `amzn-s3-demo-bucket`. The files are uploaded to a `dbfiles` folder and `ora` is added to the beginning of each file name. No compression is applied. ``` SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3( p_bucket_name => 'amzn-s3-demo-bucket', p_prefix => '', p_s3_prefix => 'dbfiles/ora', p_directory_name => 'DATA_PUMP_DIR') AS TASK_ID FROM DUAL; ``` The following example assumes that the command is run in account A, but account B requires full control of the bucket contents. The command `rdsadmin_s3_tasks.upload_to_s3` transfers all files in the `DATA_PUMP_DIR` directory to the bucket named `s3bucketOwnedByAccountB`. Access control is set to `FULL_CONTROL` so that account B can access the files in the bucket. The GZIP compression level is *6*, which balances speed and file size. ``` SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3( p_bucket_name => 's3bucketOwnedByAccountB', p_prefix => '', p_s3_prefix => '', p_directory_name => 'DATA_PUMP_DIR', p_bucket_owner_full_control => 'FULL_CONTROL', p_compression_level => 6) AS TASK_ID FROM DUAL; ``` In each example, the `SELECT` statement returns the ID of the task in a `VARCHAR2` data type. You can view the result by displaying the task's output file. ``` SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-task-id.log')); ``` Replace *`task-id`* with the task ID returned by the procedure. **Note** Tasks are executed asynchronously. ## Downloading files from an Amazon S3 bucket to an Oracle DB instance To download files from an Amazon S3 bucket to an RDS for Oracle instance, use the Amazon RDS procedure `rdsadmin.rdsadmin_s3_tasks.download_from_s3`. The `download_from_s3` procedure has the following parameters. **** | Parameter name | Data type | Default | Required | Description | | --- | --- | --- | --- | --- | | `p_bucket_name` | VARCHAR2 | – | Required | The name of the Amazon S3 bucket to download files from. | | `p_directory_name` | VARCHAR2 | – | Required | The name of the Oracle directory object to download files to. The directory can be any user-created directory object or the Data Pump directory, such as `DATA_PUMP_DIR`. | | `p_error_on_zero_downloads` | VARCHAR2 | FALSE | Optional | A flag that determines whether the task raises an error when no objects in the Amazon S3 bucket match the prefix. If this parameter is not set or set to FALSE (default), the task prints a message that no objects were found, but doesn't raise an exception or fail. If this parameter is TRUE, the task raises an exception and fails. Examples of prefix specifications that can fail match tests are spaces in prefixes, as in `' import/test9.log'`, and case mismatches, as in `test9.log` and `test9.LOG`. | | `p_s3_prefix` | VARCHAR2 | – | Required | A file name prefix that file names must match to be downloaded. An empty prefix downloads all of the top level files in the specified Amazon S3 bucket, but not the files in folders in the bucket. The procedure downloads Amazon S3 objects only from the first level folder that matches the prefix. Nested directory structures matching the specified prefix are not downloaded. For example, suppose that an Amazon S3 bucket has the folder structure `folder_1/folder_2/folder_3`. You specify the `'folder_1/folder_2/'` prefix. In this case, only the files in `folder_2` are downloaded, not the files in `folder_1` or `folder_3`. If, instead, you specify the `'folder_1/folder_2'` prefix, all files in `folder_1` that match the `'folder_2'` prefix are downloaded, and no files in `folder_2` are downloaded. | | `p_decompression_format` | VARCHAR2 | – | Optional | The decompression format. Valid values are `NONE` for no decompression and `GZIP` for decompression. | The return value for the `rdsadmin.rdsadmin_s3_tasks.download_from_s3` procedure is a task ID. The following example downloads all files in the Amazon S3 bucket named `amzn-s3-demo-bucket` to the `DATA_PUMP_DIR` directory. The files aren't compressed, so no decompression is applied. ``` SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3( p_bucket_name => 'amzn-s3-demo-bucket', p_directory_name => 'DATA_PUMP_DIR') AS TASK_ID FROM DUAL; ``` The following example downloads all of the files with the prefix `db` in the Amazon S3 bucket named `amzn-s3-demo-bucket` to the `DATA_PUMP_DIR` directory. The files are compressed with GZIP, so decompression is applied. The parameter `p_error_on_zero_downloads` turns on prefix error checking, so if the prefix doesn't match any files in the bucket, the task raises and exception and fails. ``` SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3( p_bucket_name => 'amzn-s3-demo-bucket', p_s3_prefix => 'db', p_directory_name => 'DATA_PUMP_DIR', p_decompression_format => 'GZIP', p_error_on_zero_downloads => 'TRUE') AS TASK_ID FROM DUAL; ``` The following example downloads all of the files in the folder `myfolder/` in the Amazon S3 bucket named `amzn-s3-demo-bucket` to the `DATA_PUMP_DIR` directory. Use the `p_s3_prefix` parameter to specify the Amazon S3 folder. The uploaded files are compressed with GZIP, but aren't decompressed during the download. ``` SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3( p_bucket_name => 'amzn-s3-demo-bucket', p_s3_prefix => 'myfolder/', p_directory_name => 'DATA_PUMP_DIR', p_decompression_format => 'NONE') AS TASK_ID FROM DUAL; ``` The following example downloads the file `mydumpfile.dmp` in the Amazon S3 bucket named `amzn-s3-demo-bucket` to the `DATA_PUMP_DIR` directory. No decompression is applied. ``` SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3( p_bucket_name => 'amzn-s3-demo-bucket', p_s3_prefix => 'mydumpfile.dmp', p_directory_name => 'DATA_PUMP_DIR') AS TASK_ID FROM DUAL; ``` In each example, the `SELECT` statement returns the ID of the task in a `VARCHAR2` data type. You can view the result by displaying the task's output file. ``` SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-task-id.log')); ``` Replace *`task-id`* with the task ID returned by the procedure. **Note** Tasks are executed asynchronously. You can use the `UTL_FILE.FREMOVE` Oracle procedure to remove files from a directory. For more information, see [FREMOVE procedure](https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70924) in the Oracle documentation. ## Monitoring the status of a file transfer File transfer tasks publish Amazon RDS events when they start and when they complete. The event message contains the task ID for the file transfer. For information about viewing events, see [Viewing Amazon RDS events](USER_ListEvents.md). You can view the status of an ongoing task in a bdump file. The bdump files are located in the `/rdsdbdata/log/trace` directory. Each bdump file name is in the following format. ``` dbtask-task-id.log ``` Replace `task-id` with the ID of the task that you want to monitor. **Note** Tasks are executed asynchronously. You can use the `rdsadmin.rds_file_util.read_text_file` stored procedure to view the contents of bdump files. For example, the following query returns the contents of the `dbtask-1234567890123-1234.log` bdump file. ``` SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-1234567890123-1234.log')); ``` The following sample shows the log file for a failed transfer. ``` TASK_ID -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1234567890123-1234 TEXT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2023-04-17 18:21:33.993 UTC [INFO ] File #1: Uploading the file /rdsdbdata/datapump/A123B4CDEF567890G1234567890H1234/sample.dmp to Amazon S3 with bucket name amzn-s3-demo-bucket and key sample.dmp. 2023-04-17 18:21:34.188 UTC [ERROR] RDS doesn't have permission to write to Amazon S3 bucket name amzn-s3-demo-bucket and key sample.dmp. 2023-04-17 18:21:34.189 UTC [INFO ] The task failed. ``` ## Troubleshooting Amazon S3 integration For troubleshooting tips, see the AWS re:Post article [How do troubleshoot issues when I integrate Amazon RDS for Oracle with Amazon S3?](https://repost.aws/en/knowledge-center/rds-oracle-s3-integration). # Removing the Amazon S3 integration option You can remove Amazon S3 integration option from a DB instance. To remove the Amazon S3 integration option from a DB instance, do one of the following: + To remove the Amazon S3 integration option from multiple DB instances, remove the `S3_INTEGRATION` option from the option group to which the DB instances belong. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + To remove the Amazon S3 integration option from a single DB instance, modify the instance and specify a different option group that doesn't include the `S3_INTEGRATION` option. You can specify the default (empty) option group or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Oracle Application Express (APEX) Amazon RDS supports Oracle Application Express (APEX) through the use of the `APEX` and `APEX-DEV` options. You can deploy Oracle APEX as a runtime environment or as a full development environment for web-based applications. Using Oracle APEX, you can build applications entirely within the web browser. For more information, see [Oracle application Express](https://apex.oracle.com/) in the Oracle documentation. **Topics** + [Oracle APEX components](#Appendix.Oracle.Options.APEX.components) + [Requirements and limitations](Appendix.Oracle.Options.APEX.Requirements.md) + [Setting up Oracle APEX and Oracle Rest Data Services (ORDS)](Appendix.Oracle.Options.APEX.settingUp.md) + [Configuring Oracle Rest Data Services (ORDS)](Appendix.Oracle.Options.APEX.ORDSConf.md) + [Upgrading and removing Oracle APEX](Appendix.Oracle.Options.APEX.UpgradeandRemove.md) ## Oracle APEX components Oracle APEX consists of the following main components: + A *repository* that stores the metadata for Oracle APEX applications and components. The repository consists of tables, indexes, and other objects that are installed in your Amazon RDS DB instance. + A *listener* that manages HTTP communications with Oracle APEX clients. The listener resides on a separate host such as an Amazon EC2 instance, an on-premises server at your company, or your desktop computer. The listener accepts incoming connections from web browsers, forwards them to the Amazon RDS DB instance for processing, and then sends results from the repository back to the browsers. RDS for Oracle supports the following types of listeners: + For Oracle APEX version 5.0 and later, use Oracle REST Data Services (ORDS) version 19.1 and higher. We recommend that you use the latest supported version of Oracle APEX and ORDS. This documentation describes older versions for backwards compatibility only. + For Oracle APEX version 4.1.1, you can use Oracle APEX Listener version 1.1.4. + You can use Oracle HTTP Server and `mod_plsql` listeners. **Note** Amazon RDS doesn't support the Oracle XML DB HTTP server with the embedded PL/SQL gateway as a listener for Oracle APEX. In general, Oracle recommends against using the embedded PL/SQL gateway for applications that run on the internet. For more information about these listener types, see [About choosing a web listener](https://docs.oracle.com/database/apex-5.1/HTMIG/choosing-web-listener.htm#HTMIG29321) in the Oracle documentation. When you add the `APEX` and `APEX-DEV` options to your RDS for Oracle DB instance, Amazon RDS installs the Oracle APEX repository only. Install your listener on a separate host. # Requirements and limitations The following topic lists the requirements and limitations for Oracle APEX and ORDS. ## Oracle APEX version requirements The `APEX` option uses storage on the DB instance class for your DB instance. Following are the supported versions and approximate storage requirements for Oracle APEX. **** | Oracle APEX version | Storage requirements | Supported Oracle Database versions | Notes | | --- | --- | --- | --- | | Oracle APEX version 24.2.v1 | 114 MiB | All | This version includes patch 37885097: PSE BUNDLE FOR APEX 24.2 (PSES ON TOP OF 24.2.0), PATCH\$1VERSION 4. | | Oracle APEX version 24.1.v1 | 112 MiB | All | This version includes patch 36695709: PSE BUNDLE FOR APEX 24.1 (PSES ON TOP OF 24.1.0), PATCH\$1VERSION 3. If you need exactly the same APEX images version to install on your EC2 instance, download patch 37544819: 24.1.3 PSE BUNDLE FOR APEX 24.1 (PSES ON TOP OF 24.1.0). | | Oracle APEX version 23.2.v1 | 110 MiB | All | This version includes patch 35895964: PSE BUNDLE FOR APEX 23.2 (PSES ON TOP OF 23.2.0), PATCH\$1VERSION 6. If you need exactly the same APEX images version to install on your EC2 instance, download patch 37593125: 23.2.6 PSE BUNDLE FOR APEX 23.2 (PSES ON TOP OF 23.2.0). | | Oracle APEX version 23.1.v1 | 106 MiB | All | This version includes patch 35283657: PSE BUNDLE FOR APEX 23.1 (PSES ON TOP OF 23.1.0), PATCH\$1VERSION 2. | | Oracle APEX version 22.2.v1 | 106 MiB | All | This version includes patch 34628174: PSE BUNDLE FOR APEX 22.2 (PSES ON TOP OF 22.2.0), PATCH\$1VERSION 4. | | Oracle APEX version 22.1.v1 | 124 MiB | All | This version includes patch 34020981: PSE BUNDLE FOR APEX 22.1 (PSES ON TOP OF 22.1.0), PATCH\$1VERSION 6. | | Oracle APEX version 21.2.v1 | 125 MiB | All | This version includes patch 33420059: PSE BUNDLE FOR APEX 21.2 (PSES ON TOP OF 21.2.0), PATCH\$1VERSION 8. | | Oracle APEX version 21.1.v1 | 125 MiB | All | This version includes patch 32598392: PSE BUNDLE FOR APEX 21.1, PATCH\$1VERSION 3. | | Oracle APEX version 20.2.v1 | 148 MiB | All except Oracle Database 21c | This version includes patch 32006852: PSE BUNDLE FOR APEX 20.2, PATCH\$1VERSION 2020.11.12. You can see the patch number and date by running the following query: 
SELECT PATCH_VERSION, PATCH_NUMBER 
FROM   APEX_PATCHES;
| | Oracle APEX version 20.1.v1 | 173 MiB | All except Oracle Database 21c | This version includes patch 30990551: PSE BUNDLE FOR APEX 20.1, PATCH\$1VERSION 2020.07.15. | | Oracle APEX version 19.2.v1 | 149 MiB | All except Oracle Database 21c | | | Oracle APEX version 19.1.v1 | 148 MiB | All except Oracle Database 21c | | For downloadable Oracle APEX .zip files, see [ Oracle APEX Prior Release Archives ](https://www.oracle.com/tools/downloads/apex-all-archives-downloads.html) on the Oracle website. ## Oracle APEX and ORDS prerequisites Note the following prerequisites for using Oracle APEX and ORDS: + Your system must use the Java Runtime Environment (JRE). + Your Oracle client installation must include the following: + SQL\$1Plus or SQL Developer for administration tasks + Oracle Net Services for configuring connections to your RDS for Oracle DB instance ## Oracle APEX limitations You can't modify the `APEX_version` user account, which is managed by Amazon RDS. Thus, you can't apply database profiles or enforce password rules on this user. The profiles and password settings for `APEX_version` are predefined by Oracle and AWS and are designed to meet the security requirements for Amazon RDS. # Setting up Oracle APEX and Oracle Rest Data Services (ORDS) The following topic lists the steps required to set up Oracle APEX and ORDS **Topics** + [Adding the APEX and APEX-DEV options to your DB instance](#Appendix.Oracle.Options.APEX.Add) + [Unlocking the public user account on your DB instance](#Appendix.Oracle.Options.APEX.PublicUser) + [Configuring RESTful services for Oracle APEX](#Appendix.Oracle.Options.APEX.ConfigureRESTful) + [Preparing to install ORDS on a separate host](#Appendix.Oracle.Options.APEX.ORDS.ords-setup) + [Setting up Oracle APEX listener](#Appendix.Oracle.Options.APEX.Listener) ## Adding the APEX and APEX-DEV options to your DB instance To add the `APEX` and `APEX-DEV` options to your RDS for Oracle DB instance, do the following: 1. Create a new option group, or copy or modify an existing option group. 1. Add the `APEX` and `APEX-DEV` options to the option group. 1. Associate the option group with your DB instance. When you add the `APEX` and `APEX-DEV` options, a brief outage occurs while your DB instance is automatically restarted. **Note** `APEX_MAIL` is available when the `APEX` option is installed. The execute privilege for the `APEX_MAIL` package is granted to `PUBLIC` so you don't need the APEX administrative account to use it. **To add the APEX and APEX-DEV options to a DB instance** 1. Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine**, choose the Oracle edition that you want to use. The `APEX` and `APEX-DEV` options are supported on all editions. 1. For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the options to the option group. If you want to deploy only the Oracle APEX runtime environment, add only the `APEX` option. To deploy the full development environment, add both the `APEX` and `APEX-DEV` options. For **Version**, choose the version of Oracle APEX that you want to use. **Important** If you add the `APEX` or `APEX-DEV` options to an existing option group that is already attached to one or more DB instances, a brief outage occurs. During this outage, all the DB instances are automatically restarted. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Apply the option group to a new or existing DB instance: + For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. When you add the `APEX` or `APEX-DEV` options to an existing DB instance, a brief outage occurs while your DB instance is automatically restarted. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Unlocking the public user account on your DB instance After you have installed the `APEX` or `APEX-DEV` options your DB instance, make sure to do the following: 1. Change the password for the `APEX_PUBLIC_USER` account. 1. Unlock the account. You can do this by using the Oracle SQL\$1Plus command line utility. Connect to your DB instance as the master user, and issue the following commands. Replace `new_password` with a password of your choice. ``` 1. ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password; 2. ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK; ``` ## Configuring RESTful services for Oracle APEX To configure RESTful services in Oracle APEX (not needed for Oracle APEX 4.1.1.V1), use SQL\$1Plus to connect to your DB instance as the master user. After you do this, run the `rdsadmin.rdsadmin_run_apex_rest_config` stored procedure. When you run the stored procedure, you provide passwords for the following users: + `APEX_LISTENER` + `APEX_REST_PUBLIC_USER` The stored procedure runs the `apex_rest_config.sql` script, which creates new database accounts for these users. **Note** Configuration isn't required for Oracle APEX version 4.1.1.v1. For this Oracle APEX version only, you don't need to run the stored procedure. The following command runs the stored procedure. ``` 1. EXEC rdsadmin.rdsadmin_run_apex_rest_config('apex_listener_password', 'apex_rest_public_user_password'); ``` ## Preparing to install ORDS on a separate host Install ORDS on a separate host such as an Amazon EC2 instance, an on-premises server at your company, or your desktop computer. The examples in this section,assume that your host runs Linux and is named `myapexhost.example.com`. Before you can install ORDS, you need to create a nonprivileged OS user, and then download and unzip the Oracle APEX installation file. **To prepare for ORDS installation** 1. Log in to `myapexhost.example.com` as `root`. 1. Create a nonprivileged OS user to own the listener installation. The following command creates a new user named *apexuser*. ``` useradd -d /home/apexuser apexuser ``` The following command assigns a password to the new user. ``` passwd apexuser; ``` 1. Log in to `myapexhost.example.com` as `apexuser`, and download the Oracle APEX installation file from Oracle to your `/home/apexuser` directory: + [http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html](http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html) + [Oracle application Express prior release archives](http://www.oracle.com/technetwork/developer-tools/apex/downloads/all-archives-099381.html) 1. Unzip the file in the `/home/apexuser` directory. ``` unzip apex_version.zip ``` After you unzip the file, there is an `apex` directory in the `/home/apexuser` directory. 1. While you are still logged into `myapexhost.example.com` as `apexuser`, download the Oracle REST Data Services file from Oracle to your `/home/apexuser` directory: [http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html](http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html). ## Setting up Oracle APEX listener **Note** Oracle APEX Listener is deprecated. Amazon RDS for Oracle continues to support Oracle APEX version 4.1.1 and Oracle APEX Listener version 1.1.4. We recommend that you use the latest supported versions of Oracle APEX and ORDS. Install Oracle APEX Listener on a separate host such as an Amazon EC2 instance, an on-premises server at your company, or your desktop computer. We assume that the name of your host is `myapexhost.example.com`, and that your host is running Linux. ### Preparing to install Oracle APEX listener Before you can install Oracle APEX Listener, you need to create a nonprivileged OS user, and then download and unzip the Oracle APEX installation file. **To prepare for Oracle APEX listener installation** 1. Log in to `myapexhost.example.com` as `root`. 1. Create a nonprivileged OS user to own the listener installation. The following command creates a new user named *apexuser*. ``` useradd -d /home/apexuser apexuser ``` The following command assigns a password to the new user. ``` passwd apexuser; ``` 1. Log in to `myapexhost.example.com` as `apexuser`, and download the Oracle APEX installation file from Oracle to your `/home/apexuser` directory: + [http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html](http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html) + [Oracle application Express prior release archives](http://www.oracle.com/technetwork/developer-tools/apex/downloads/all-archives-099381.html) 1. Unzip the file in the `/home/apexuser` directory. ``` unzip apex_.zip ``` After you unzip the file, there is an `apex` directory in the `/home/apexuser` directory. 1. While you are still logged into `myapexhost.example.com` as `apexuser`, download the Oracle APEX Listener file from Oracle to your `/home/apexuser` directory. #### Installing and configuring Oracle APEX listener Before you can use Oracle APEX, you need to download the `apex.war` file, use Java to install Oracle APEX Listener, and then start the listener. **To install and configure Oracle APEX listener** 1. Create a new directory based on Oracle APEX Listener and open the listener file. Run the following code: ``` mkdir /home/apexuser/apexlistener cd /home/apexuser/apexlistener unzip ../apex_listener.version.zip ``` 1. Run the following code. ``` java -Dapex.home=./apex -Dapex.images=/home/apexuser/apex/images -Dapex.erase -jar ./apex.war ``` 1. Enter information for the program prompts following: + The APEX Listener Administrator user name. The default is *adminlistener*. + A password for the APEX Listener Administrator. + The APEX Listener Manager user name. The default is *managerlistener*. + A password for the APEX Listener Administrator. The program prints a URL that you need to complete the configuration, as follows. ``` INFO: Please complete configuration at: http://localhost:8080/apex/listenerConfigure Database is not yet configured ``` 1. Leave Oracle APEX Listener running so that you can use Oracle Application Express. When you have finished this configuration procedure, you can run the listener in the background. 1. From your web browser, go to the URL provided by the Oracle APEX Listener program. The Oracle Application Express Listener administration window appears. Enter the following information: + **Username** – `APEX_PUBLIC_USER` + **Password** – the password for *APEX\$1PUBLIC\$1USER*. This password is the one that you specified earlier when you configured the Oracle APEX repository. For more information, see [Unlocking the public user account on your DB instance](#Appendix.Oracle.Options.APEX.PublicUser). + **Connection type** – Basic + **Hostname** – the endpoint of your Amazon RDS DB instance, such as `mydb.f9rbfa893tft.us-east-1.rds.amazonaws.com`. + **Port** – 1521 + **SID** – the name of the database on your Amazon RDS DB instance, such as `mydb`. 1. Choose **Apply**. The Oracle APEX administration window appears. 1. Set a password for the Oracle APEX `admin` user. To do this, use SQL\$1Plus to connect to your DB instance as the master user, and then run the following commands. ``` 1. EXEC rdsadmin.rdsadmin_util.grant_apex_admin_role; 2. grant APEX_ADMINISTRATOR_ROLE to master; 3. @/home/apexuser/apex/apxchpwd.sql ``` Replace `master` with your master user name. When the `apxchpwd.sql` script prompts you, enter a new `admin` password. 1. Return to the Oracle APEX administration window in your browser and choose **Administration**. Next, choose **Application Express Internal Administration**. When you are prompted for credentials, enter the following information: + **User name** – `admin` + **Password** – the password you set using the `apxchpwd.sql` script Choose **Login**, and then set a new password for the `admin` user. Your listener is now ready for use. # Configuring Oracle Rest Data Services (ORDS) The following topic lists the configuration options for ORDS 21 and 22: **Topics** + [Installing and configuring ORDS 21 and lower](#Appendix.Oracle.Options.APEX.ORDS) + [Installing and configuring ORDS 22 and higher](#Appendix.Oracle.Options.APEX.ORDS22) ## Installing and configuring ORDS 21 and lower You are now ready to install and configure Oracle Rest Data Services (ORDS) for use with Oracle APEX. For Oracle APEX version 5.0 and later, use ORDS versions 19.1 to 21. To learn how to install ORDS 22 and higher, see [Installing and configuring ORDS 22 and higher](#Appendix.Oracle.Options.APEX.ORDS22). Install the listener on a separate host such as an Amazon EC2 instance, an on-premises server at your company, or your desktop computer. For the examples in this section, we assume that the name of your host is `myapexhost.example.com`, and that your host is running Linux. **To install and configure ORDS 21 and lower for use with Oracle APEX** 1. Go to [Oracle REST data services](https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads-212.html), and examine the Readme. Make sure that you have the required version of Java installed. 1. Create a new directory for your ORDS installation. ``` mkdir /home/apexuser/ORDS cd /home/apexuser/ORDS ``` 1. Download the file `ords.version.number.zip` from [Oracle REST data services](https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads-212.html). 1. Unzip the file into the `/home/apexuser/ORDS` directory. 1. If you're installing ORDS in a multitenant database, add the following line to the file `/home/apexuser/ORDS/params/ords_params.properties`: ``` pdb.disable.lockdown=false ``` 1. Grant the master user the required privileges to install ORDS. After the options for Oracle APEX are installed, give the master user the required privileges to install the ORDS schema. You can do this by connecting to the database and running the following commands. Replace `MASTER_USER` with the uppercase name of your master user. **Important** When you enter the user name, use uppercase unless you created the user with a case-sensitive identifier. For example, if you run `CREATE USER myuser` or `CREATE USER MYUSER`, the data dictionary stores `MYUSER`. However, if you use double quotes in `CREATE USER "MyUser"`, the data dictionary stores `MyUser`. For more information, see [Granting SELECT or EXECUTE privileges to SYS objects](Appendix.Oracle.CommonDBATasks.TransferPrivileges.md). ``` exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ROLE_PRIVS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONS_COLUMNS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONSTRAINTS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_OBJECTS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_PROCEDURES', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TAB_COLUMNS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TABLES', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_VIEWS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('WPIUTL', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SESSION', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_UTILITY', 'MASTER_USER', 'EXECUTE', true); ``` **Note** These commands apply to ORDS version 19.1 and later. 1. Install the ORDS schema using the downloaded ords.war file. ``` java -jar ords.war install advanced ``` The program prompts you for the following information. The default values are in brackets. For more information, see [Introduction to Oracle REST data services](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/20.2/aelig/installing-REST-data-services.html#GUID-6F7B4E61-B730-4E73-80B8-F53299123730) in the Oracle documentation. + Enter the location to store configuration data: Enter */home/apexuser/ORDS*. This is the location of the ORDS configuration files. + Specify the database connection type to use. Enter number for [1] Basic [2] TNS [3] Custom URL [1]: Choose the desired connection type. + Enter the name of the database server [localhost]: *DB\$1instance\$1endpoint* Choose the default or enter the correct value. + Enter the database listener port [1521]: *DB\$1instance\$1port* Choose the default or enter the correct value. + Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Choose `2` to specify the database SID. + Database SID [xe] Choose the default or enter the correct value. + Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: Choose `1`. This step creates the Oracle REST Data Services proxy user named ORDS\$1PUBLIC\$1USER. + Enter the database password for ORDS\$1PUBLIC\$1USER: Enter the password, and then confirm it. + Requires to login with administrator privileges to verify Oracle REST Data Services schema. Enter the administrator user name: *master\$1user* Enter the database password for *master\$1user*: *master\$1user\$1password* Confirm the password: *master\$1user\$1password* **Note** Specify a password other than the prompt shown here as a security best practice. + Enter the default tablespace for ORDS\$1METADATA [SYSAUX]. Enter the temporary tablespace for ORDS\$1METADATA [TEMP]. Enter the default tablespace for ORDS\$1PUBLIC\$1USER [USERS]. Enter the temporary tablespace for ORDS\$1PUBLIC\$1USER [TEMP]. + Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step. If you're using Oracle Application Express or migrating from mod\$1plsql, you must enter 1 [1]. Choose the default. + Enter the PL/SQL Gateway database user name [APEX\$1PUBLIC\$1USER] Choose the default. + Enter the database password for APEX\$1PUBLIC\$1USER: Enter the password, and then confirm it. + Enter 1 to specify passwords for Application Express RESTful Services database users (APEX\$1LISTENER, APEX\$1REST\$1PUBLIC\$1USER) or 2 to skip this step [1]: Choose `2` for APEX 4.1.1.V1; choose `1` for all other APEX versions. + [Not needed for APEX 4.1.1.v1] Database password for APEX\$1LISTENER Enter the password (if required), and then confirm it. + [Not needed for APEX 4.1.1.v1] Database password for APEX\$1REST\$1PUBLIC\$1USER Enter the password (if required), and then confirm it. + Enter a number to select a feature to enable: Enter `1` to enable all features: SQL Developer Web, REST Enabled SQL, and Database API. + Enter 1 if you wish to start in standalone mode or 2 to exit [1]: Enter `1`. + Enter the APEX static resources location: If you unzipped APEX installation files into `/home/apexuser`, enter `/home/apexuser/apex/images`. Otherwise, enter `unzip_path/apex/images`, where *unzip\$1path* is the directory where you unzipped the file. + Enter 1 if using HTTP or 2 if using HTTPS [1]: If you enter `1`, specify the HTTP port. If you enter `2`, specify the HTTPS port and the SSL host name. The HTTPS option prompts you to specify how you will provide the certificate: + Enter `1` to use the self-signed certificate. + Enter `2` to provide your own certificate. If you enter `2`, specify the path for the SSL certificate and the path for the SSL certificate private key. 1. Set a password for the APEX `admin` user. To do this, use SQL\$1Plus to connect to your DB instance as the master user, and then run the following commands. ``` 1. EXEC rdsadmin.rdsadmin_util.grant_apex_admin_role; 2. grant APEX_ADMINISTRATOR_ROLE to master; 3. @/home/apexuser/apex/apxchpwd.sql ``` Replace `master` with your master user name. When the `apxchpwd.sql` script prompts you, enter a new `admin` password. 1. Start the ORDS listener. Run the following code. ``` java -jar ords.war ``` The first time you start ORDS, you are prompted to provide the location of the APEX Static resources. This images folder is located in the `/apex/images` directory in the installation directory for APEX. 1. Return to the Oracle APEX administration window in your browser and choose **Administration**. Next, choose **Application Express Internal Administration**. When you are prompted for credentials, enter the following information: + **User name** – `admin` + **Password** – the password you set using the `apxchpwd.sql` script Choose **Login**, and then set a new password for the `admin` user. Your listener is now ready for use. ## Installing and configuring ORDS 22 and higher You are now ready to install and configure Oracle Rest Data Services (ORDS) for use with Oracle APEX. For the examples in this section, we assume that the name of your separate host is `myapexhost.example.com`, and that your host is running Linux. The instructions for ORDS 22 differ from the instructions for previous releases. **To install and configure ORDS 22 and higher for use with Oracle APEX** 1. Go to [Oracle REST data services](http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html), and examine the Readme for the ORDS version that you plan to download. Make sure that you have the required version of Java installed. 1. Create a new directory for your ORDS installation. ``` mkdir /home/apexuser/ORDS cd /home/apexuser/ORDS ``` 1. Download the file `ords.version.number.zip` or `ords-latest.zip` from [Oracle REST data services](http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html). 1. Unzip the file into the `/home/apexuser/ORDS` directory. 1. Grant the master user the required privileges to install ORDS. After the `APEX` option is installed, give the master user the required privileges to install the ORDS schema. You can do this by logging in to the database and running the following commands. Replace `MASTER_USER` with the uppercase name of your master user. **Important** When you enter the user name, use uppercase unless you created the user with a case-sensitive identifier. For example, if you run `CREATE USER myuser` or `CREATE USER MYUSER`, the data dictionary stores `MYUSER`. However, if you use double quotes in `CREATE USER "MyUser"`, the data dictionary stores `MyUser`. For more information, see [Granting SELECT or EXECUTE privileges to SYS objects](Appendix.Oracle.CommonDBATasks.TransferPrivileges.md). ``` exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ROLE_PRIVS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONS_COLUMNS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONSTRAINTS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_OBJECTS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_PROCEDURES', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TAB_COLUMNS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TABLES', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('USER_VIEWS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('WPIUTL', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SESSION', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_UTILITY', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOB', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_ASSERT', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_OUTPUT', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SCHEDULER', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('HTP', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('OWA', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('WPG_DOCLOAD', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_CRYPTO', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_METADATA', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SQL', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('UTL_SMTP', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_NETWORK_ACL_ADMIN', 'MASTER_USER', 'EXECUTE', true); exec rdsadmin.rdsadmin_util.grant_sys_object('SESSION_PRIVS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_USERS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_NETWORK_ACL_PRIVILEGES', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_NETWORK_ACLS', 'MASTER_USER', 'SELECT', true); exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_REGISTRY', 'MASTER_USER', 'SELECT', true); ``` **Note** The preceding commands apply to ORDS 22 and later. 1. Install the ORDS schema using the downloaded `ords` script. Specify directories to contain configuration files and log files. Oracle Corporation recommends not placing these directories inside the directory that contains the ORDS product software. ``` mkdir -p /home/apexuser/ords_config /home/apexuser/ords_logs /home/apexuser/ORDS/bin/ords \ --config /home/apexuser/ords_config \ install --interactive --log-folder /home/apexuser/ords_logs ``` For DB instances running the container database (CDB) architecture, use ORDS 23.3 or higher and pass the `--pdb-skip-disable-lockdown` argument when installing ORDS. ``` /home/apexuser/ORDS/bin/ords \ --config /home/apexuser/ords_config \ install --interactive --log-folder /home/apexuser/ords_logs --pdb-skip-disable-lockdown ``` The program prompts you for the following information. The default values are in brackets. For more information, see [Introduction to Oracle REST data services](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/20.2/aelig/installing-REST-data-services.html#GUID-6F7B4E61-B730-4E73-80B8-F53299123730) in the Oracle documentation. + `Choose the type of installation:` Choose **2** to install ORDS schemas in the database and create a database connection pool in the local ORDS configuration files. + `Specify the database connection type to use. Enter number for [1] Basic [2] TNS [3] Custom URL:` Choose the desired connection type. This example assumes that you choose **1**. + `Enter the name of the database server [localhost]:` ***DB\$1instance\$1endpoint*** Choose the default or enter the correct value. + `Enter the database listener port [1521]:` ***DB\$1instance\$1port*** Choose the default **1521** or enter the correct value. + `Enter the database service name [orcl]:` Enter the database name used by your RDS for Oracle DB instance. + `Provide database user name with administrator privileges` Enter the master user name for your RDS for Oracle DB instance. + `Enter the database password for [username]:` Enter the master user password for your RDS for Oracle DB instance. + `Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]:` + `Enter the temporary tablespace for ORDS_METADATA [TEMP]. Enter the default tablespace for ORDS_PUBLIC_USER [USERS]. Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP].` + `Enter a number to select additional feature(s) to enable [1]:` + `Enter a number to configure and start ORDS in standalone mode [1]: ` Choose **2** to skip starting ORDS immediately in standalone mode. + `Enter a number to select the protocol [1] HTTP` + `Enter the HTTP port [8080]:` + `Enter the APEX static resources location:` Enter the path to the Oracle APEX installation files (`/home/apexuser/apex/images`). 1. Set a password for the Oracle APEX `admin` user. To do this, use SQL\$1Plus to connect to your DB instance as the master user, and then run the following commands. ``` 1. EXEC rdsadmin.rdsadmin_util.grant_apex_admin_role; 2. grant APEX_ADMINISTRATOR_ROLE to master; 3. @/home/apexuser/apex/apxchpwd.sql ``` Replace `master` with your master user name. When the `apxchpwd.sql` script prompts you, enter a new `admin` password. 1. Run ORDS in standalone mode using the `ords` script with the `serve` command. For production deployments, consider using supported Java EE application servers such as Apache Tomcat or Oracle WebLogic Server. For more information, see [Deploying and Monitoring Oracle REST Data Services](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.1/ordig/deploying-and-monitoring-oracle-rest-data-services.html#GUID-6791F5DF-AC67-4885-BFFA-B80964C17EC9) in the Oracle Database documentation. ``` /home/apexuser/ORDS/bin/ords \ --config /home/apexuser/ords_config serve \ --port 8193 \ --apex-images /home/apexuser/apex/images ``` If ORDS is running but unable to access the Oracle APEX installation, you might see the following error, particularly on non-CDB instances. ``` The procedure named apex_admin could not be accessed, it may not be declared, or the user executing this request may not have been granted execute privilege on the procedure, or a function specified by security.requestValidationFunction configuration property has prevented access. ``` To fix this error, change the request validation function used by ORDS by running the `ords` script with the `config` command. By default, ORDS uses the `ords_util.authorize_plsql_gateway` procedure, which is only supported on CDB instances. For non-CDB instances, you can change this procedure to the `wwv_flow_epg_include_modules.authorize` package. See the Oracle Database documentation and Oracle Support for best practices on configuring the proper request validation function for your use case. 1. Return to the Oracle APEX administration window in your browser and choose **Administration**. Next, choose **Application Express Internal Administration**. When you are prompted for credentials, enter the following information: + **User name** – `admin` + **Password** – the password you set using the `apxchpwd.sql` script Choose **Login**, and then set a new password for the `admin` user. Your listener is now ready for use. # Upgrading and removing Oracle APEX To upgrade or remove Oracle APEX, follow the instructions in this topic: **Topics** + [Upgrading the Oracle APEX version](#Appendix.Oracle.Options.APEX.Upgrade) + [Removing the APEX and APEX-DEV options](#Appendix.Oracle.Options.APEX.Remove) ## Upgrading the Oracle APEX version **Important** Back up your DB instance before you upgrade Oracle APEX. For more information, see [Creating a DB snapshot for a Single-AZ DB instance for Amazon RDS](USER_CreateSnapshot.md) and [Testing an Oracle DB upgrade](USER_UpgradeDBInstance.Oracle.UpgradeTesting.md). To upgrade Oracle APEX with your DB instance, do the following: + Create a new option group for the upgraded version of your DB instance. + Add the upgraded versions of the `APEX` and `APEX-DEV` options to the new option group. Be sure to include any other options that your DB instance uses. For more information, see [Option group considerations](USER_UpgradeDBInstance.Oracle.OGPG.md#USER_UpgradeDBInstance.Oracle.OGPG.OG). + When you upgrade your DB instance, specify the new option group for your upgraded DB instance. After you upgrade your version of Oracle APEX, the Oracle APEX schema for the previous version might still exist in your database. If you don't need it anymore, you can drop the old Oracle APEX schema from your database after you upgrade. If you upgrade the Oracle APEX version and RESTful services were not configured in the previous Oracle APEX version, we recommend that you configure RESTful services. For more information, see [Configuring RESTful services for Oracle APEX](Appendix.Oracle.Options.APEX.settingUp.md#Appendix.Oracle.Options.APEX.ConfigureRESTful). In some cases when you plan to do a major version upgrade of your DB instance, you might find that you're using an Oracle APEX version that isn't compatible with your target database version. In these cases, you can upgrade your version of Oracle APEX before you upgrade your DB instance. Upgrading Oracle APEX first can reduce the amount of time that it takes to upgrade your DB instance. **Note** After upgrading Oracle APEX, install and configure a listener for use with the upgraded version. For instructions, see [Setting up Oracle APEX listener](Appendix.Oracle.Options.APEX.settingUp.md#Appendix.Oracle.Options.APEX.Listener). ## Removing the APEX and APEX-DEV options You can remove the `APEX` and `APEX-DEV` options from a DB instance. To remove these options from your DB instance, do one of the following: + To remove the `APEX` and `APEX-DEV` options from multiple DB instances, remove the options from the option group they belong to. This change affects all DB instances that use the option group. When you remove the options from an option group that is attached to multiple DB instances, a brief outage occurs while the DB instances are restarted. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + To remove the `APEX` and `APEX-DEV` options from a single DB instance, modify the DB instance and specify a different option group that doesn't include these options. You can specify the default (empty) option group, or a different custom option group. When you remove the options, a brief outage occurs while your DB instance is automatically restarted. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). When you remove the `APEX` and `APEX-DEV` options from a DB instance, the APEX schema is removed from your database. # Amazon EFS integration Amazon Elastic File System (Amazon EFS) provides serverless, fully elastic file storage so that you can share file data without provisioning or managing storage capacity and performance. With Amazon EFS, you can create a file system and then mount it in your VPC through the NFS versions 4.0 and 4.1 (NFSv4) protocol. Then you can use the EFS file system like any other POSIX-compliant file system. For general information, see [What is Amazon Elastic File System?](https://docs.aws.amazon.com/efs/latest/ug/whatisefs.html) and the AWS blog [Integrate Amazon RDS for Oracle with Amazon EFS](https://aws.amazon.com//blogs/database/integrate-amazon-rds-for-oracle-with-amazon-efs/). **Topics** + [Overview of Amazon EFS integration](#oracle-efs-integration.overview) + [Configuring network permissions for RDS for Oracle integration with Amazon EFS](oracle-efs-integration.network.md) + [Configuring IAM permissions for RDS for Oracle integration with Amazon EFS](oracle-efs-integration.iam.md) + [Adding the EFS\$1INTEGRATION option](oracle-efs-integration.adding.md) + [Configuring Amazon EFS file system permissions](oracle-efs-integration.file-system.md) + [Transferring files between RDS for Oracle and an Amazon EFS file system](oracle-efs-integration.transferring.md) + [Removing the EFS\$1INTEGRATION option](oracle-efs-integration.removing.md) + [Troubleshooting Amazon EFS integration](oracle-efs-integration.troubleshooting.md) ## Overview of Amazon EFS integration With Amazon EFS, you can transfer files between your RDS for Oracle DB instance and an EFS file system. For example, you can use EFS to support the following use cases: + Share a file system between applications and multiple database servers. + Create a shared directory for migration-related files, including transportable tablespace data files. For more information, see [Migrating using Oracle transportable tablespaces](oracle-migrating-tts.md). + Store and share archived redo log files without allocating additional storage space on the server. + Use Oracle Database utilities such as `UTL_FILE` to read and write files. ### Advantages to Amazon EFS integration When you choose an EFS file system over alternative data transfer solutions, you get the following benefits: + You can transfer Oracle Data Pump files between Amazon EFS and your RDS for Oracle DB instance. You don’t need to copy these files locally because Data Pump imports directly from the EFS file system. For more information, see [Importing data into Oracle on Amazon RDS](Oracle.Procedural.Importing.md). + Data migration is faster than using a database link. + You avoid allocating storage space on your RDS for Oracle DB instance to hold the files. + An EFS file systems can automatically scale storage without requiring you to provision it. + Amazon EFS integration has no minimum fees or setup costs. You pay only for what you use. + Amazon EFS integration supports two forms of encryption: encryption of data in transit and encryption at rest. Encryption of data in transit is enabled by default using TLS version 1.2. You can enable encryption of data at rest when creating an Amazon EFS file system. For more information, see [Encrypting data at rest](https://docs.aws.amazon.com/efs/latest/ug/encryption-at-rest.html) in the *Amazon Elastic File System User Guide*. ### Requirements for Amazon EFS integration Make sure that you meet the following requirements: + Your database must run database version 19.0.0.0.ru-2022-07.rur-2022-07.r1 or higher. + Your DB instance and your EFS file system must be in the same AWS Region, VPC, and AWS account. RDS for Oracle doesn't support cross-account and cross-Region access for EFS. + Your VPC must have both **DNS Resolution** and **DNS Hostnames** enabled. For more information, see [DNS attributes in your VPC](https://docs.aws.amazon.com/vpc/latest/userguide/vpc-dns.html#vpc-dns-support) in the *Amazon Virtual Private Cloud User Guide*. + If you use a DNS name in the `mount` command, make sure your VPC configured to use the DNS server provided by Amazon. Custom DNS servers aren't supported. + You must use non-RDS solutions to back up your EFS file system. RDS for Oracle doesn't support automated backups or manual DB snapshots of an EFS file system. For more information, see [Backing up your Amazon EFS file systems](https://docs.aws.amazon.com/efs/latest/ug/efs-backup-solutions.html). # Configuring network permissions for RDS for Oracle integration with Amazon EFS For RDS for Oracle to integrate with Amazon EFS, make sure that your DB instance has network access to an EFS file system. For more information, see [Controlling network access to Amazon EFS file systems for NFS clients](https://docs.aws.amazon.com/efs/latest/ug/NFS-access-control-efs.html) in the *Amazon Elastic File System User Guide*. **Topics** + [Controlling network access with security groups](#oracle-efs-integration.network.inst-access) + [Controlling network access with file system policies](#oracle-efs-integration.network.file-system-policy) ## Controlling network access with security groups You can control your DB instance access to EFS file systems using network layer security mechanisms such as VPC security groups. To allow access to an EFS file system for your DB instance, make sure that your EFS file system meets the following requirements: + An EFS mount target exists in every Availability Zone used by an RDS for Oracle DB instance. An *EFS mount target* provides an IP address for an NFSv4 endpoint at which you can mount an EFS file system. You mount your file system using its DNS name, which resolves to the IP address of the EFS mount target in the used by the Availability Zone of your DB instance. You can configure DB instances in different AZs to use the same EFS file system. For Multi-AZ, you need a mount point for each AZ in your deployment. You might need to move a DB instance to a different AZ. For these reasons, we recommend that you create an EFS mount point in each AZ in your VPC. By default, when you create a new EFS file system using the console, RDS creates mount targets for all AZs. + A security group is attached to the mount target. + The security group has an inbound rule to allow the network subnet or security group of the RDS for Oracle DB instance on TCP/2049 (Type NFS). For more information, see [Creating Amazon EFS file systems](https://docs.aws.amazon.com/efs/latest/ug/creating-using-create-fs.html#configure-efs-network-access) and [Creating and managing EFS mount targets and security groups](https://docs.aws.amazon.com/efs/latest/ug/accessing-fs.html) in the *Amazon Elastic File System User Guide*. ## Controlling network access with file system policies Amazon EFS integration with RDS for Oracle works with the default (empty) EFS file system policy. The default policy doesn't use IAM to authenticate. Instead, it grants full access to any anonymous client that can connect to the file system using a mount target. The default policy is in effect whenever a user-configured file system policy isn't in effect, including at file system creation. For more information, see [Default EFS file system policy](https://docs.aws.amazon.com/efs/latest/ug/iam-access-control-nfs-efs.html#default-filesystempolicy) in the *Amazon Elastic File System User Guide*. To strengthen access to your EFS file system for all clients, including RDS for Oracle, you can configure IAM permissions. In this approach, you create a file system policy. For more information, see [Creating file system policies](https://docs.aws.amazon.com/efs/latest/ug/create-file-system-policy.html) in the *Amazon Elastic File System User Guide*. # Configuring IAM permissions for RDS for Oracle integration with Amazon EFS By default, Amazon EFS integration feature doesn't use an IAM role: the `USE_IAM_ROLE` option setting is `FALSE`. To integrate RDS for Oracle with Amazon EFS and an IAM role, your DB instance must have IAM permissions to access an Amazon EFS file system. **Topics** + [Step 1: Create an IAM role for your DB instance and attach your policy](#oracle-efs-integration.iam.role) + [Step 2: Create a file system policy for your Amazon EFS file system](#oracle-efs-integration.iam.policy) + [Step 3: Associate your IAM role with your RDS for Oracle DB instance](#oracle-efs-integration.iam.instance) ## Step 1: Create an IAM role for your DB instance and attach your policy In this step, you create a role for your RDS for Oracle DB instance to allow Amazon RDS to access your EFS file system. ### Console **To create an IAM role to allow Amazon RDS access to an EFS file system** 1. Open the [IAM Management Console](https://console.aws.amazon.com/iam/home?#home). 1. In the navigation pane, choose **Roles**. 1. Choose **Create role**. 1. For **AWS service**, choose **RDS**. 1. For **Select your use case**, choose **RDS – Add Role to Database**. 1. Choose **Next**. 1. Don't add any permissions policies. Choose **Next**. 1. Set **Role name** to a name for your IAM role, for example `rds-efs-integration-role`. You can also add an optional **Description** value. 1. Choose **Create role**. ### AWS CLI To limit the service's permissions to a specific resource, we recommend using the [https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn) and [https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount) global condition context keys in resource-based trust relationships. This is the most effective way to protect against the [confused deputy problem](https://docs.aws.amazon.com/IAM/latest/UserGuide/confused-deputy.html). You might use both global condition context keys and have the `aws:SourceArn` value contain the account ID. In this case, the `aws:SourceAccount` value and the account in the `aws:SourceArn` value must use the same account ID when used in the same statement. + Use `aws:SourceArn` if you want cross-service access for a single resource. + Use `aws:SourceAccount` if you want to allow any resource in that account to be associated with the cross-service use. In the trust relationship, make sure to use the `aws:SourceArn` global condition context key with the full Amazon Resource Name (ARN) of the resources accessing the role. The following AWS CLI command creates the role named `rds-efs-integration-role` for this purpose. **Example** For Linux, macOS, or Unix: ``` aws iam create-role \ --role-name rds-efs-integration-role \ --assume-role-policy-document '{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "aws:SourceAccount": my_account_ID, "aws:SourceArn": "arn:aws:rds:Region:my_account_ID:db:dbname" } } } ] }' ``` For Windows: ``` aws iam create-role ^ --role-name rds-efs-integration-role ^ --assume-role-policy-document '{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "aws:SourceAccount": my_account_ID, "aws:SourceArn": "arn:aws:rds:Region:my_account_ID:db:dbname" } } } ] }' ``` For more information, see [Creating a role to delegate permissions to an IAM user](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-user.html) in the *IAM User Guide*. ## Step 2: Create a file system policy for your Amazon EFS file system In this step, you create a file system policy for your EFS file system. **To create or edit an EFS file system policy** 1. Open the [EFS Management Console](https://console.aws.amazon.com/efs/home?#home). 1. Choose **File Systems**. 1. On the **File systems** page, choose the file system that you want to edit or create a file system policy for. The details page for that file system is displayed. 1. Choose the **File system policy** tab. If the policy is empty, then the default EFS file system policy is in use. For more information, see [Default EFS file system policy](https://docs.aws.amazon.com/efs/latest/ug/iam-access-control-nfs-efs.html#default-filesystempolicy ) in the *Amazon Elastic File System User Guide*. 1. Choose **Edit**. The **File system policy** page appears. 1. In **Policy editor**, enter a policy such as the following, and then choose **Save**. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Id": "ExamplePolicy01", "Statement": [ { "Sid": "ExampleStatement01", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789012:role/rds-efs-integration-role" }, "Action": [ "elasticfilesystem:ClientMount", "elasticfilesystem:ClientWrite", "elasticfilesystem:ClientRootAccess" ], "Resource": "arn:aws:elasticfilesystem:us-east-1:123456789012:file-system/fs-1234567890abcdef0" } ] } ``` ------ ## Step 3: Associate your IAM role with your RDS for Oracle DB instance In this step, you associate your IAM role with your DB instance. Be aware of the following requirements: + You must have access to an IAM role with the required Amazon EFS permissions policy attached to it. + You can associate only one IAM role with your RDS for Oracle DB instance at a time. + The status of your instance must be **Available**. For more information, see [Identity and access management for Amazon EFS](https://docs.aws.amazon.com/efs/latest/ug/auth-and-access-control.html) in the *Amazon Elastic File System User Guide*. ### Console **To associate your IAM role with your RDS for Oracle DB instance** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Databases**. 1. If your database instance is unavailable, choose **Actions** and then **Start**. When the instance status shows **Started**, go to the next step. 1. Choose the Oracle DB instance name to display its details. 1. On the **Connectivity & security** tab, scroll down to the **Manage IAM roles** section at the bottom of the page. 1. Choose the role to add in the **Add IAM roles to this instance** section. 1. For **Feature**, choose **EFS\$1INTEGRATION**. 1. Choose **Add role**. ### AWS CLI The following AWS CLI command adds the role to an Oracle DB instance named `mydbinstance`. **Example** For Linux, macOS, or Unix: ``` aws rds add-role-to-db-instance \ --db-instance-identifier mydbinstance \ --feature-name EFS_INTEGRATION \ --role-arn your-role-arn ``` For Windows: ``` aws rds add-role-to-db-instance ^ --db-instance-identifier mydbinstance ^ --feature-name EFS_INTEGRATION ^ --role-arn your-role-arn ``` Replace `your-role-arn` with the role ARN that you noted in a previous step. `EFS_INTEGRATION` must be specified for the `--feature-name` option. # Adding the EFS\$1INTEGRATION option To integrate Amazon RDS for Oracle with Amazon EFS, your DB instance must be associated with an option group that includes the `EFS_INTEGRATION` option. Multiple Oracle DB instances that belong to the same option group share the same EFS file system. Different DB instances can access the same data, but access can be divided by using different Oracle directories. For more information see [Transferring files between RDS for Oracle and an Amazon EFS file system](oracle-efs-integration.transferring.md). ## Console **To configure an option group for Amazon EFS integration** 1. Create a new option group or identify an existing option group to which you can add the `EFS_INTEGRATION` option. For information about creating an option group, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the `EFS_INTEGRATION` option to the option group. You need to specify the `EFS_ID` file system ID and set the `USE_IAM_ROLE` flag. For more information, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Associate the option group with your DB instance in either of the following ways: + Create a new Oracle DB instance and associate the option group with it. For information about creating a DB instance, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + Modify an Oracle DB instance to associate the option group with it. For information about modifying an Oracle DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## AWS CLI **To configure an option group for EFS integration** 1. Create a new option group or identify an existing option group to which you can add the `EFS_INTEGRATION` option. For information about creating an option group, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the `EFS_INTEGRATION` option to the option group. For example, the following AWS CLI command adds the `EFS_INTEGRATION` option to an option group named **myoptiongroup**. **Example** For Linux, macOS, or Unix: ``` aws rds add-option-to-option-group \ --option-group-name myoptiongroup \ --options "OptionName=EFS_INTEGRATION,OptionSettings=\ [{Name=EFS_ID,Value=fs-1234567890abcdef0},{Name=USE_IAM_ROLE,Value=TRUE}]" ``` For Windows: ``` aws rds add-option-to-option-group ^ --option-group-name myoptiongroup ^ --options "OptionName=EFS_INTEGRATION,OptionSettings=^ [{Name=EFS_ID,Value=fs-1234567890abcdef0},{Name=USE_IAM_ROLE,Value=TRUE}]" ``` 1. Associate the option group with your DB instance in either of the following ways: + Create a new Oracle DB instance and associate the option group with it. For information about creating a DB instance, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + Modify an Oracle DB instance to associate the option group with it. For information about modifying an Oracle DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Configuring Amazon EFS file system permissions By default, only the root user (UID `0`) has read, write, and execute permissions for a newly created EFS file system. For other users to modify the file system, the root user must explicitly grant them access. The user for the RDS for Oracle DB instance is in the `others` category. For more information, see [Working with users, groups, and permissions at the Network File System (NFS) Level](https://docs.aws.amazon.com/efs/latest/ug/accessing-fs-nfs-permissions.html) in the *Amazon Elastic File System User Guide*. To allow your RDS for Oracle DB instance to read and write files on an EFS file system, do the following: + Mount an EFS file system locally on your Amazon EC2 or on-premises instance. + Configure fine grain permissions. For example, to grant `other` users permissions to write to the EFS file system root, run `chmod 777` on this directory. For more information, see [Example Amazon EFS file system use cases and permissions](https://docs.aws.amazon.com/efs/latest/ug/accessing-fs-nfs-permissions.html#accessing-fs-nfs-permissions-ex-scenarios) in the *Amazon Elastic File System User Guide*. # Transferring files between RDS for Oracle and an Amazon EFS file system To transfer files between an RDS for Oracle instance and an Amazon EFS file system, create at least one Oracle directory and configure EFS file system permissions to control DB instance access. **Topics** + [Creating an Oracle directory](#oracle-efs-integration.transferring.od) + [Transferring data to and from an EFS file system: examples](#oracle-efs-integration.transferring.upload) ## Creating an Oracle directory To create an Oracle directory, use the procedure `rdsadmin.rdsadmin_util.create_directory_efs`. The procedure has the following parameters. **** | Parameter name | Data type | Default | Required | Description | | --- | --- | --- | --- | --- | | `p_directory_name` | VARCHAR2 | – | Yes | The name of the Oracle directory. | | `p_path_on_efs` | VARCHAR2 | – | Yes | The path on the EFS file system. The prefix of the path name uses the pattern `/rdsefs-fsid/`, where *fsid* is a placeholder for your EFS file system ID. For example, if your EFS file system is named `fs-1234567890abcdef0`, and you create a subdirectory on this file system named `mydir`, you could specify the following value: 
/rdsefs-fs-1234567890abcdef0/mydir
| Assume that you create a subdirectory named `/datapump1` on the EFS file system `fs-1234567890abcdef0`. The following example creates an Oracle directory `DATA_PUMP_DIR_EFS` that points to the `/datapump1` directory on the EFS file system. The file system path value for the `p_path_on_efs` parameter is prefixed with the string `/rdsefs-`. ``` BEGIN rdsadmin.rdsadmin_util.create_directory_efs( p_directory_name => 'DATA_PUMP_DIR_EFS', p_path_on_efs => '/rdsefs-fs-1234567890abcdef0/datapump1'); END; / ``` ## Transferring data to and from an EFS file system: examples The following example uses Oracle Data Pump to export the table named `MY_TABLE` to file `datapump.dmp`. This file resides on an EFS file system. ``` DECLARE v_hdnl NUMBER; BEGIN v_hdnl := DBMS_DATAPUMP.OPEN(operation => 'EXPORT', job_mode => 'TABLE', job_name=>null); DBMS_DATAPUMP.ADD_FILE( handle => v_hdnl, filename => 'datapump.dmp', directory => 'DATA_PUMP_DIR_EFS', filetype => dbms_datapump.ku$_file_type_dump_file); DBMS_DATAPUMP.ADD_FILE( handle => v_hdnl, filename => 'datapump-exp.log', directory => 'DATA_PUMP_DIR_EFS', filetype => dbms_datapump.ku$_file_type_log_file); DBMS_DATAPUMP.METADATA_FILTER(v_hdnl,'NAME_EXPR','IN (''MY_TABLE'')'); DBMS_DATAPUMP.START_JOB(v_hdnl); END; / ``` The following example uses Oracle Data Pump to import the table named `MY_TABLE` from file `datapump.dmp`. This file resides on an EFS file system. ``` DECLARE v_hdnl NUMBER; BEGIN v_hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'TABLE', job_name => null); DBMS_DATAPUMP.ADD_FILE( handle => v_hdnl, filename => 'datapump.dmp', directory => 'DATA_PUMP_DIR_EFS', filetype => dbms_datapump.ku$_file_type_dump_file ); DBMS_DATAPUMP.ADD_FILE( handle => v_hdnl, filename => 'datapump-imp.log', directory => 'DATA_PUMP_DIR_EFS', filetype => dbms_datapump.ku$_file_type_log_file); DBMS_DATAPUMP.METADATA_FILTER(v_hdnl,'NAME_EXPR','IN (''MY_TABLE'')'); DBMS_DATAPUMP.START_JOB(v_hdnl); END; / ``` For more information, see [Importing data into Oracle on Amazon RDS](Oracle.Procedural.Importing.md). # Removing the EFS\$1INTEGRATION option The steps for removing the `EFS_INTEGRATION` option depend on whether you're removing the option from multiple DB instances or a single instance. | Number of DB instances | Action | Related information | | --- | --- | --- | | Multiple | Remove the EFS\$1INTEGRATION option from the option group to which the DB instances belong. This change affects all instances that use the option group. | [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption) | | Single | Modify the DB instance and specify a different option group that doesn't include the EFS\$1INTEGRATION option. You can specify the default (empty) option group or a different custom option group. | [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md) | After you remove the `EFS_INTEGRATION` option, you can optionally delete the EFS file system that was connected to your DB instances. # Troubleshooting Amazon EFS integration Your RDS for Oracle DB instance monitors the connectivity to an Amazon EFS file system. When monitoring detects an issue, it might try to correct the issue and publish an event in the RDS console. For more information, see [Viewing Amazon RDS events](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ListEvents.html). Use the information in this section to help you diagnose and fix common issues when you work with Amazon EFS integration. | Notification | Description | Action | | --- | --- | --- | | `The EFS for RDS Oracle instance instance_name isn't available on the primary host. NFS port 2049 of your EFS isn't reachable.` | The DB instance can't communicate with the EFS file system. | Make sure of the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-efs-integration.troubleshooting.html) | | `The EFS isn't reachable.` | An error occurred during the installation of the `EFS_INTEGRATION` option. | Make sure of the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-efs-integration.troubleshooting.html) | | `The associated role with your DB instance wasn't found.` | An error occurred during the installation of the `EFS_INTEGRATION` option. | Make sure that you associated an IAM role with your RDS for Oracle DB instance. | | `The associated role with your DB instance wasn't found.` | An error occurred during the installation of the `EFS_INTEGRATION` option. RDS for Oracle was restored from a DB snapshot with the `USE_IAM_ROLE` option setting of `TRUE`. | Make sure that you associated an IAM role with your RDS for Oracle DB instance. | | `The associated role with your DB instance wasn't found.` | An error occurred during the installation of the `EFS_INTEGRATION` option. RDS for Oracle was created from an all-in-one CloudFormation template with the `USE_IAM_ROLE` option setting of `TRUE`. | As a workaround, complete the following steps: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-efs-integration.troubleshooting.html) | | `PLS-00302: component 'CREATE_DIRECTORY_EFS' must be declared` | This error can occur when you're using a version of RDS for Oracle that doesn't support Amazon EFS. | Make sure that you are using RDS for Oracle DB instance version 19.0.0.0.ru-2022-07.rur-2022-07.r1 or higher. | | `Read access of your EFS is denied. Check your file system policy.` | Your DB instance can't read the EFS file system. | Make sure that your EFS file system allows read access through the IAM role or on the EFS file system level. | | N/A | Your DB instance can't write to the EFS file system. | Take the following steps: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-efs-integration.troubleshooting.html) | # Oracle Java virtual machine Amazon RDS supports Oracle Java Virtual Machine (JVM) through the use of the `JVM` option. Oracle Java provides a SQL schema and functions that facilitate Oracle Java features in an Oracle database. For more information, see [ Introduction to Java in Oracle database](https://docs.oracle.com/database/121/JJDEV/chone.htm) in the Oracle documentation. You can use Oracle JVM with all versions of Oracle Database 21c (21.0.0) and Oracle Database 19c (19.0.0). ## Considerations for Oracle JVM Java implementation in Amazon RDS has a limited set of permissions. The master user is granted the `RDS_JAVA_ADMIN` role, which grants a subset of the privileges granted by the `JAVA_ADMIN` role. To list the privileges granted to the `RDS_JAVA_ADMIN` role, run the following query on your DB instance: ``` SELECT * FROM dba_java_policy WHERE grantee IN ('RDS_JAVA_ADMIN', 'PUBLIC') AND enabled = 'ENABLED' ORDER BY type_name, name, grantee; ``` ## Prerequisites for Oracle JVM The following are prerequisites for using Oracle Java: + Your DB instance must be of a large enough class. Oracle Java isn't supported for the db.t3.small DB instance classes. For more information, see [DB instance classes](Concepts.DBInstanceClass.md). + Your DB instance must have **Auto Minor Version Upgrade** enabled. This option enables your DB instance to receive minor DB engine version upgrades automatically when they become available. Amazon RDS uses this option to update your DB instance to the latest Oracle Patch Set Update (PSU) or Release Update (RU). For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Best practices for Oracle JVM The following are best practices for using Oracle Java: + For maximum security, use the `JVM` option with Secure Sockets Layer (SSL). For more information, see [Oracle Secure Sockets Layer](Appendix.Oracle.Options.SSL.md). + Configure your DB instance to restrict network access. For more information, see [Scenarios for accessing a DB instance in a VPC](USER_VPC.Scenarios.md) and [Working with a DB instance in a VPC](USER_VPC.WorkingWithRDSInstanceinaVPC.md). + Update the configuration of your HTTPS endpoints to support TLSv1.2 if you meet the following conditions: + You use Oracle Java Virtual Machine (JVM) to connect an HTTPS endpoint over TLSv1 or TLSv1.1 protocols. + Your endpoint doesn't support the TLSv1.2 protocol. + You haven't applied the April 2021 release update to your Oracle DB. By updating your endpoint configuration, you ensure that the connectivity of the JVM to the HTTPS endpoint will continue to work. For more information about TLS changes in the Oracle JRE and JDK, see [Oracle JRE and JDK Cryptographic Roadmap](https://java.com/en/jre-jdk-cryptoroadmap.html). ## Adding the Oracle JVM option The following is the general process for adding the `JVM` option to a DB instance: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. 1. Associate the option group with the DB instance. There is a brief outage while the `JVM` option is added. After you add the option, you don't need to restart your DB instance. As soon as the option group is active, Oracle Java is available. **Note** During this outage, password verification functions are disabled briefly. You can also expect to see events related to password verification functions during the outage. Password verification functions are enabled again before the Oracle DB instance is available. **To add the JVM option to a DB instance** 1. Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: + For **Engine**, choose the DB engine used by the DB instance (**oracle-ee**, **oracle-se**, **oracle-se1**, or **oracle-se2**). + For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **JVM** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Apply the option group to a new or existing DB instance: + For a new DB instance, apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). 1. Grant the required permissions to users. The Amazon RDS master user has the permissions to use the `JVM` option by default. If other users require these permissions, connect to the DB instance as the master user in a SQL client and grant the permissions to the users. The following example grants the permissions to use the `JVM` option to the `test_proc` user. ``` create user test_proc identified by password; CALL dbms_java.grant_permission('TEST_PROC', 'oracle.aurora.security.JServerPermission', 'LoadClassInPackage.*', ''); ``` **Note** Specify a password other than the prompt shown here as a security best practice. After the user is granted the permissions, the following query should return output. ``` select * from dba_java_policy where grantee='TEST_PROC'; ``` **Note** The Oracle user name is case-sensitive, and it usually has all uppercase characters. ## Removing the Oracle JVM option You can remove the `JVM` option from a DB instance. There is a brief outage while the option is removed. After you remove the `JVM` option, you don't need to restart your DB instance. **Warning** Removing the `JVM` option can result in data loss if the DB instance is using data types that were enabled as part of the option. Back up your data before proceeding. For more information, see [Backing up, restoring, and exporting data](CHAP_CommonTasks.BackupRestore.md). To remove the `JVM` option from a DB instance, do one of the following: + Remove the `JVM` option from the option group it belongs to. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + Modify the DB instance and specify a different option group that doesn't include the `JVM` option. This change affects a single DB instance. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Oracle Enterprise Manager Amazon RDS supports Oracle Enterprise Manager (OEM). OEM is the Oracle product line for integrated management of enterprise information technology. Amazon RDS supports OEM on Oracle Database 19c non-CDBs or CDBs. The following table describes the supported OEM options. **** | Option | Option ID | Supported OEM releases | | --- | --- | --- | | [OEM Database Express](Appendix.Oracle.Options.OEM_DBControl.md) | `OEM` | OEM Database Express 19c | | [OEM Management Agent](Oracle.Options.OEMAgent.md) | `OEM_AGENT` | OEM Cloud Control for 13c | **Note** You can use OEM Database or OEM Management Agent, but not both. # Oracle Enterprise Manager Database Express Amazon RDS supports Oracle Enterprise Manager Database Express (EM Express) through the use of the OEM option. Amazon RDS supports EM Express for Oracle Database 19c using the CDB or non-CDB architecture. EM Express is a web-based database management tool included in your database and is only available when it is open. It supports key performance management and basic database administration functions. For more information, see [Introduction to Oracle Enterprise Manager Database Express](https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/getting-started-with-database-administration.html#GUID-BA75AD46-D22E-4914-A31E-C395CD6A2BBA) in the Oracle Database documentation. **Note** EM Express isn't supported on the db.t3.small DB instance class. For more information about DB instance classes, see [RDS for Oracle DB instance classes](Oracle.Concepts.InstanceClasses.md). ## OEM option settings Amazon RDS supports the following settings for the OEM option. **** | Option setting | Valid values | Description | | --- | --- | --- | | **Port** | An integer value | The port on the RDS for Oracle DB instance that listens for EM Express. The default is 5500. | | **Security Groups** | — | A security group that has access to **Port**. | ## Step 1: Adding the OEM option The general process for adding the OEM option to a DB instance is the following: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. 1. Associate the option group with your DB instance. When you add the OEM option, a brief outage occurs while your DB instance is automatically restarted. **To add the OEM option to a DB instance** 1. Determine the option group you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine** choose the oracle edition for your DB instance. 1. For **Major engine version** choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the OEM option to the option group, and configure the option settings. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). For more information about each setting, see [OEM option settings](#Appendix.Oracle.Options.OEM_DBControl.Options). **Note** If you add the OEM option to an existing option group that is already attached to one or more DB instances, a brief outage occurs while all the DB instances are automatically restarted. 1. Apply the option group to a new or existing DB instance: + For a new DB instance, apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, apply the option group by modifying the instance and attaching the new option group. When you add the OEM option, a brief outage occurs while your DB instance is automatically restarted. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). **Note** You can also use the AWS CLI to add the OEM option. For examples, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). ## Step 2: (CDB only) Unlocking the DBSNMP user account If your DB instance uses the CDB architecture, you must log in to EM Express as `DBSNMP`. in a CDB, `DBSNMP` is a common user. By default, this account is locked. If your DB instance doesn't use the CDB architecture, skip this step. **To unlock the DBSNMP user account in a CDB instance** 1. In SQL\$1Plus or another Oracle SQL application, log in to your DB instance as your master user. 1. Run the following stored procedure to unlock the `DBSNMP` account: ``` 1. EXEC rdsadmin.rdsadmin_util.reset_oem_agent_password('new_password'); ``` If you receive an error stating that the procedure doesn't exist, reboot your CDB instance to install it automatically. For more information, see [Rebooting a DB instance](USER_RebootInstance.md). ## Step 3: Accessing EM Express through your browser When you access EM Express from your web browser, a login window appears that prompts you for a user name and password. **To access EM Express through your browser** 1. Identify the endpoint and EM Express port for your Amazon RDS DB instance. For information about finding the endpoint for your Amazon RDS DB instance, see [Finding the endpoint of your RDS for Oracle DB instance](USER_Endpoint.md). 1. Enter a URL in your browser locator bar using the following format. ``` https://endpoint.rds.amazonaws.com:port/em ``` For example, if the endpoint for your Amazon RDS DB instance is `mydb.a1bcde234fgh.us-east-1.rds.amazonaws.com`, and your EM Express port is `1158`, then use the following URL to access EM Express. ``` 1. https://mydb.f9rbfa893tft.us-east-1.rds.amazonaws.com:1158/em ``` 1. When prompted for your login details, do one of the following actions, depending on your database architecture: **Your database is a non-CDB.** Type the master user name and master password for your DB instance. **Your database is a CDB.** Enter `DBSNMP` for the user and the `DBSNMP` password. Leave the `Container` field empty. ## Modifying OEM Database settings After you enable OEM Database, you can modify the Security Groups setting for the option. You can't modify the OEM port number after you have associated the option group with a DB instance. To change the OEM port number for a DB instance, do the following: 1. Create a new option group. 1. Add the OEM option with the new port number to the new option group. 1. Remove the existing option group from the DB instance. 1. Add the new option group to the DB instance. For more information about how to modify option settings, see [Modifying an option setting](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.ModifyOption). For more information about each setting, see [OEM option settings](#Appendix.Oracle.Options.OEM_DBControl.Options). ## Running OEM Database Express tasks You can use Amazon RDS procedures to run certain OEM Database Express tasks. By running these procedures, you can do the tasks listed following. **Note** OEM Database Express tasks run asynchronously. **Topics** + [Switching the website front end for OEM Database Express to Adobe Flash](#Appendix.Oracle.Options.OEM_DBControl.DBTasks.FrontEndToFlash) + [Switching the website front end for OEM Database Express to Oracle JET](#Appendix.Oracle.Options.OEM_DBControl.DBTasks.FrontEndToOracleJET) ### Switching the website front end for OEM Database Express to Adobe Flash **Note** This task is available only for Oracle Database 19c non-CDBs. Starting with Oracle Database 19c, Oracle has deprecated the former OEM Database Express user interface, which was based on Adobe Flash. Instead, OEM Database Express now uses an interface built with Oracle JET. If you experience difficulties with the new interface, you can switch back to the deprecated Flash-based interface. Difficulties you might experience with the new interface include being stuck on a `Loading` screen after logging in to OEM Database Express. You might also miss certain features that were present in the Flash-based version of OEM Database Express. To switch the OEM Database Express website front end to Adobe Flash, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_tasks.em_express_frontend_to_flash`. This procedure is equivalent to the `execemx emx` SQL command. Security best practices discourage the use of Adobe Flash. Although you can revert to the Flash-based OEM Database Express, we recommend the use of the JET-based OEM Database Express websites if possible. If you revert to using Adobe Flash and want to switch back to using Oracle JET, use the `rdsadmin.rdsadmin_oem_tasks.em_express_frontend_to_jet` procedure. After an Oracle database upgrade, a newer version of Oracle JET might resolve JET-related issues in OEM Database Express. For more information about switching to Oracle JET, see [Switching the website front end for OEM Database Express to Oracle JET](#Appendix.Oracle.Options.OEM_DBControl.DBTasks.FrontEndToOracleJET). **Note** Running this task from the source DB instance for a read replica also causes the read replica to switch its OEM Database Express website front ends to Adobe Flash. The following procedure invocation creates a task to switch the OEM Database Express website to Adobe Flash and returns the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_tasks.em_express_frontend_to_flash() as TASK_ID from DUAL; ``` You can view the result by displaying the task's output file. ``` SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-task-id.log')); ``` Replace *`task-id`* with the task ID returned by the procedure. For more information about the Amazon RDS procedure `rdsadmin.rds_file_util.read_text_file`, see [Reading files in a DB instance directory](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.ReadingFiles) You can also view the contents of the task's output file in the AWS Management Console by searching the log entries in the **Logs & events** section for the `task-id`. ### Switching the website front end for OEM Database Express to Oracle JET **Note** This task is available only for Oracle Database 19c non-CDBs. To switch the OEM Database Express website front end to Oracle JET, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_tasks.em_express_frontend_to_jet`. This procedure is equivalent to the `execemx omx` SQL command. By default, the OEM Database Express websites for Oracle DB instances running 19c or later use Oracle JET. If you used the `rdsadmin.rdsadmin_oem_tasks.em_express_frontend_to_flash` procedure to switch the OEM Database Express website front end to Adobe Flash, you can switch back to Oracle JET. To do this, use the `rdsadmin.rdsadmin_oem_tasks.em_express_frontend_to_jet` procedure. For more information about switching to Adobe Flash, see [Switching the website front end for OEM Database Express to Adobe Flash](#Appendix.Oracle.Options.OEM_DBControl.DBTasks.FrontEndToFlash). **Note** Running this task from the source DB instance for a read replica also causes the read replica to switch its OEM Database Express website front ends to Oracle JET. The following procedure invocation creates a task to switch the OEM Database Express website to Oracle JET and returns the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_tasks.em_express_frontend_to_jet() as TASK_ID from DUAL; ``` You can view the result by displaying the task's output file. ``` SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-task-id.log')); ``` Replace *`task-id`* with the task ID returned by the procedure. For more information about the Amazon RDS procedure `rdsadmin.rds_file_util.read_text_file`, see [Reading files in a DB instance directory](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.ReadingFiles) You can also view the contents of the task's output file in the AWS Management Console by searching the log entries in the **Logs & events** section for the `task-id`. ## Removing the OEM Database option You can remove the OEM option from a DB instance. When you remove the OEM option, a brief outage occurs while your instance is automatically restarted. Therefore, after you remove the OEM option, you don't need to restart your DB instance. To remove the OEM option from a DB instance, do one of the following: + Remove the OEM option from the option group it belongs to. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + Modify the DB instance and specify a different option group that doesn't include the OEM option. This change affects a single DB instance. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Oracle Management Agent for Enterprise Manager Cloud Control Oracle Enterprise Manager (OEM) Management Agent is a software component that monitors targets running on hosts and communicates that information to the middle-tier Oracle Management Service (OMS). Amazon RDS supports Management Agent through the use of the `OEM_AGENT` option. For more information, see [Overview of Oracle Enterprise Manager cloud control 12c](http://docs.oracle.com/cd/E24628_01/doc.121/e25353/overview.htm) and [Overview of Oracle Enterprise Manager cloud control 13c](http://docs.oracle.com/cd/E63000_01/EMCON/overview.htm#EMCON109) in the Oracle documentation. **Topics** + [Requirements for Management Agent](#Oracle.Options.OEMAgent.PreReqs) + [OMS host communication prerequisites](#Oracle.Options.OEMAgent.PreReqs.host) + [Limitations for Management Agent](#Oracle.Options.OEMAgent.limitations) + [Option settings for Management Agent](#Oracle.Options.OEMAgent.Options) + [Enabling the Management Agent option for your DB instance](#Oracle.Options.OEMAgent.Enable) + [Removing the Management Agent option](#Oracle.Options.OEMAgent.Remove) + [Performing database tasks with the Management Agent](#Oracle.Options.OEMAgent.DBTasks) ## Requirements for Management Agent Following are general requirements for using Management Agent: + You can use either the CDB or non-CDB architecture. + You must use an Oracle Management Service (OMS) that is configured to connect to your DB instance. Note the following OMS requirements: + Management Agent version 24.1.0.0.v1 requires OMS version 24.1. + Management Agent versions 13.5.0.0.v2 and 13.5.0.0.v3 require OMS version 13.5.0.23 or 24.1. + Management Agent version 13.5.0.0.v1 requires OMS version 13.5.0.0 or 24.1. + Management Agent versions 13.4.0.9.v1 and 13.4.0.9.v2 require OMS version 13.4.0.9 or later and patch 32198287. + In most cases, you must configure your VPC to allow connections from OMS to your DB instance. If you aren't familiar with Amazon Virtual Private Cloud (Amazon VPC), we recommend that you complete the steps in [Tutorial: Create a VPC for use with a DB instance (IPv4 only)](CHAP_Tutorials.WebServerDB.CreateVPC.md) before continuing. + You can use Management Agent with Oracle Enterprise Manager Cloud Control for 12c and 13c. Ensure that you have sufficient storage space for your OEM release: + At least 8.5 GiB for OEM 24ai Release 1 + At least 8.5 GiB for OEM 13c Release 5 + At least 8.5 GiB for OEM 13c Release 4 + At least 8.5 GiB for OEM 13c Release 3 + At least 5.5 GiB for OEM 13c Release 2 + At least 4.5 GiB for OEM 13c Release 1 + At least 2.5 GiB for OEM 12c + If you're using Management Agent versions `OEM_AGENT 13.2.0.0.v3` and `13.3.0.0.v2`, and if you want to use TCPS connectivity, follow the instructions in [Configuring third party CA certificates for communication with target databases](https://docs.oracle.com/cd/E73210_01/EMSEC/GUID-8337AD48-1A32-4CD5-84F3-256FAE93D043.htm#EMSEC15996) in the Oracle documentation. Also, update the JDK on your OMS by following the instructions in the Oracle document with the Oracle Doc ID 2241358.1. This step ensures that OMS supports all the cipher suites that the database supports. **Note** TCPS connectivity between the Management Agent and the DB instance is supported for Management Agent `OEM_AGENT 13.2.0.0.v3`, `13.3.0.0.v2`, `13.4.0.9.v1`, and higher versions. ## OMS host communication prerequisites Make sure that your OMS host and your Amazon RDS DB instance can communicate. Do the following: + To connect from the Management Agent to your OMS host when your OMS host is behind a firewall, add the IP addresses of your DB instances to the firewall. Make sure the firewall for the OMS allows the following network traffic: From the OMS host to your DB instance Configure a one-way firewall rule that allows traffic from the OMS host to the database listener port (default 1521) and the OEM Agent port (default 3872). From your DB instance to the OMS host Configure a one-way firewall rule that allows traffic from the DB instance to the OMS HTTP port (default 4903). + To connect from your OMS to the Management Agent, if your OMS has a publicly resolvable host name, add the OMS address to a security group. Your security group must have inbound rules that allow access to the DB listener port and the Management Agent port. For an example of creating a security and adding inbound rules, see [Tutorial: Create a VPC for use with a DB instance (IPv4 only)](CHAP_Tutorials.WebServerDB.CreateVPC.md). + To connect from your OMS to the Management Agent, if your OMS doesn't have a publicly resolvable host name, use one of the following: + If your OMS is hosted on an Amazon Elastic Compute Cloud (Amazon EC2) instance in a private VPC, you can set up VPC peering to connect from OMS to Management Agent. For more information, see [A DB instance in a VPC accessed by an EC2 instance in a different VPC](USER_VPC.Scenarios.md#USER_VPC.Scenario3). + If your OMS is hosted on-premises, you can set up a VPN connection to allow access from OMS to Management Agent. For more information, see [A DB instance in a VPC accessed by a client application through the internet](USER_VPC.Scenarios.md#USER_VPC.Scenario4) or [VPN connections](https://docs.aws.amazon.com/vpc/latest/userguide/vpn-connections.html). + To connect OEM Management Agent version 13.5.0.0 (v1-v3) or 24.1.0.0.v1 to a 24.1 OMS host, set `MINIMUM_TLS_VERSION` to use the TLS 1.2 protocol `TLSv1.2` in your configuration options. ## Limitations for Management Agent Following are some limitations to using Management Agent: + You can't provide custom Oracle Management Agent images. + Administrative tasks such as job execution and database patching, that require host credentials, aren't supported. + Host metrics and the process list aren't guaranteed to reflect the actual system state. Thus, you shouldn't use OEM to monitor the root file system or mount point file system. For more information about monitoring the operating system, see [Monitoring OS metrics with Enhanced Monitoring](USER_Monitoring.OS.md). + Autodiscovery isn't supported. You must manually add database targets. + OMS module availability depends on your database edition. For example, the database performance diagnosis and tuning module is only available for Oracle Database Enterprise Edition. + Management Agent consumes additional memory and computing resources. If you experience performance problems after enabling the `OEM_AGENT` option, we recommend that you scale up to a larger DB instance class. For more information, see [DB instance classes](Concepts.DBInstanceClass.md) and [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). + The user running the `OEM_AGENT` on the Amazon RDS host doesn't have operating system access to the alert log. Thus, you can't collect metrics for `DB Alert Log` and `DB Alert Log Error Status` in OEM. ## Option settings for Management Agent Amazon RDS supports the following settings for the Management Agent option. | Option setting | Required | Valid values | Description | | --- | --- | --- | --- | | **Version** (`AGENT_VERSION`) | Yes | `24.1.0.0.v1` `13.5.0.0.v3` `13.5.0.0.v2` `13.5.0.0.v1` `13.4.0.9.v2` `13.4.0.9.v1` `13.3.0.0.v2` `13.3.0.0.v1` `13.2.0.0.v3` `13.2.0.0.v2` `13.2.0.0.v1` `13.1.0.0.v1` | The version of the Management Agent software. The minimum supported version is `13.1.0.0.v1`. The AWS CLI option name is `OptionVersion`. In the AWS GovCloud (US) Regions, 13.1 versions aren't available. | | **Port** (`AGENT_PORT`) | Yes | An integer value | The port on the DB instance that listens for the OMS host. The default is 3872. Your OMS host must belong to a security group that has access to this port. The AWS CLI option name is `Port`. | | **Security Groups** | Yes | Existing security groups | A security group that has access to **Port**. Your OMS host must belong to this security group. The AWS CLI option name is `VpcSecurityGroupMemberships` or `DBSecurityGroupMemberships`. | | **OMS\$1HOST** | Yes | A string value, for example *my.example.oms* | The publicly accessible host name or IP address of the OMS. The AWS CLI option name is `OMS_HOST`. | | **OMS\$1PORT** | Yes | An integer value | The HTTPS upload port on the OMS Host that listens for the Management Agent. To determine the HTTPS upload port, connect to the OMS host, and run the following command (which requires the `SYSMAN` password): emctl status oms -details The AWS CLI option name is `OMS_PORT`. | | **AGENT\$1REGISTRATION\$1PASSWORD** | Yes | A string value | The password that the Management Agent uses to authenticate itself with the OMS. We recommend that you create a persistent password in your OMS before enabling the `OEM_AGENT` option. With a persistent password you can share a single Management Agent option group among multiple Amazon RDS databases. The AWS CLI option name is `AGENT_REGISTRATION_PASSWORD`. | | **ALLOW\$1TLS\$1ONLY** | No | `true`, `false` (default) | A value that configures the OEM Agent to support only the `TLSv1` protocol while the agent listens as a server. This setting is no longer supported. Management Agent versions 13.1.0.0.v1 and higher support Transport Layer Security (TLS) by default. | | **MINIMUM\$1TLS\$1VERSION** | No | `TLSv1` (default), `TLSv1.2` | A value that specifies the minimum TLS version supported by the OEM Agent while the agent listens as a server. Desupported agent versions only support the `TLSv1` setting. To connect 13.5.0.0 (v1-v3) or 24.1.0.0.v1 to a 24.1 OMS host, set this to `TLSv1.2`. | | **TLS\$1CIPHER\$1SUITE** | No | See [Option settings for Management Agent](#Oracle.Options.OEMAgent.Options). | A value that specifies the TLS cipher suite used by the OEM Agent while the agent listens as a server. | The following table lists the TLS cipher suites that the Management Agent option supports. | Cipher suite | Agent version supported | FedRAMP compliant | | --- | --- | --- | | TLS\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA | All | No | | TLS\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 | 13.1.0.0.v1 and higher | No | | TLS\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA | 13.2.0.0.v3 and higher | No | | TLS\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA256 | 13.2.0.0.v3 and higher | No | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA | 13.2.0.0.v3 and higher | Yes | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA | 13.2.0.0.v3 and higher | Yes | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 | 13.2.0.0.v3 and higher | Yes | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | 13.2.0.0.v3 and higher | Yes | | TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 | 13.4.0.9.v1 and higher | Yes | | TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | 13.4.0.9.v1 and higher | Yes | ### Certificate compatibility with cipher suites RDS for Oracle supports both RSA and Elliptic Curve Digital Signature Algorithm (ECDSA) certificates. When you configure the OEM Agent option for your DB instance, you must ensure that the cipher suites you specify in the `TLS_CIPHER_SUITE` option setting are compatible with the certificate type used by your DB instance. The following table shows the compatibility between certificate types and cipher suites: | Certificate type | Compatible cipher suites | Incompatible cipher suites | | --- | --- | --- | | RSA certificates (rds-ca-2019, rds-ca-rsa2048-g1, rds-ca-rsa4096-g1) | TLS\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA TLS\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 TLS\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA TLS\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA256 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | | ECDSA certificates (rds-ca-ecc384-g1) | TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | TLS\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA TLS\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 TLS\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA TLS\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA256 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | When you specify a cipher suite in the `TLS_CIPHER_SUITE` option setting, make sure it is compatible with the certificate type used by your DB instance. If you attempt to associate an option group with an OEM Agent option that contains a cipher suite incompatible with the certificate type of a DB instance, the operation will fail with an error message indicating the incompatibility. ## Enabling the Management Agent option for your DB instance To enable the Management Agent option, use the following steps: **Topics** + [Step 1: Adding the Management Agent option to your DB instance](#Oracle.Options.OEMAgent.Add) + [Step 2: Unlocking the DBSNMP user account](#Oracle.Options.OEMAgent.DBSNMP) + [Step 3: Adding your targets to the Management Agent console](#Oracle.Options.OEMAgent.Using) ### Step 1: Adding the Management Agent option to your DB instance To add the Management Agent option to your DB instance, do the following: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. 1. Associate the option group with the DB instance. If you encounter errors, check [My Oracle Support](https://support.oracle.com/) documents for information about resolving specific problems. After you add the Management Agent option, you don't need to restart your DB instance. As soon as the option group is active, the OEM Agent is active. If your OMS host is using an untrusted third-party certificate, Amazon RDS returns the following error. ``` You successfully installed the OEM_AGENT option. Your OMS host is using an untrusted third party certificate. Configure your OMS host with the trusted certificates from your third party. ``` If this error is returned, the Management Agent option isn't enabled until the problem is corrected. For information about correcting the problem, see the My Oracle Support document [2202569.1](https://support.oracle.com/epmos/faces/DocContentDisplay?id=2202569.1). #### Console **To add the Management Agent option to your DB instance** 1. Determine the option group you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine** choose the oracle edition for your DB instance. 1. For **Major engine version** choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **OEM\$1AGENT** option to the option group, and configure the option settings. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). For more information about each setting, see [Option settings for Management Agent](#Oracle.Options.OEMAgent.Options). 1. Apply the option group to a new or existing DB instance: + For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). #### AWS CLI The following example uses the AWS CLI [add-option-to-option-group](https://docs.aws.amazon.com/cli/latest/reference/rds/add-option-to-option-group.html) command to add the `OEM_AGENT` option to an option group called `myoptiongroup`. For Linux, macOS, or Unix: ``` aws rds add-option-to-option-group \ --option-group-name "myoptiongroup" \ --options OptionName=OEM_AGENT,OptionVersion=13.1.0.0.v1,Port=3872,VpcSecurityGroupMemberships=sg-1234567890,OptionSettings=[{Name=OMS_HOST,Value=my.example.oms},{Name=OMS_PORT,Value=4903},{Name=AGENT_REGISTRATION_PASSWORD,Value=password}] \ --apply-immediately ``` For Windows: ``` aws rds add-option-to-option-group ^ --option-group-name "myoptiongroup" ^ --options OptionName=OEM_AGENT,OptionVersion=13.1.0.0.v1,Port=3872,VpcSecurityGroupMemberships=sg-1234567890,OptionSettings=[{Name=OMS_HOST,Value=my.example.oms},{Name=OMS_PORT,Value=4903},{Name=AGENT_REGISTRATION_PASSWORD,Value=password}] ^ --apply-immediately ``` ### Step 2: Unlocking the DBSNMP user account The Management Agent uses the `DBSNMP` user account to connect to the database and report issues to Oracle Enterprise Manager. In a CDB, `DBSNMP` is a common user. This user account is necessary for both the Management Agent and OEM Database Express. By default, this account is locked. The procedure for unlocking this account differs depending on whether your database uses the non-CDB or CDB architecture. **To unlock the DBSNMP user account** 1. In SQL\$1Plus or another Oracle SQL application, log in to your DB instance as your master user. 1. Do either of the following actions, depending on the database architecture: **Your database is a non-CDB.** Run the following SQL statement: ``` 1. ALTER USER dbsnmp IDENTIFIED BY new_password ACCOUNT UNLOCK; ``` **Your database is a CDB.** Run the following stored procedure to unlock the `DBSNMP` account: ``` 1. EXEC rdsadmin.rdsadmin_util.reset_oem_agent_password('new_password'); ``` If you receive an error stating that the procedure doesn't exist, reboot your CDB instance to install it automatically. For more information, see [Rebooting a DB instance](USER_RebootInstance.md). ### Step 3: Adding your targets to the Management Agent console To add a DB instance as a target, make sure you know the endpoint and port. For information about finding the endpoint for your Amazon RDS DB instance, see [Finding the endpoint of your RDS for Oracle DB instance](USER_Endpoint.md). If your database uses the CDB architecture, then add the `CDB$ROOT` container separately as a target. **To add targets to the Management Agent console** 1. In your OMS console, choose **Setup**, **Add Target**, **Add Targets Manually**. 1. Choose **Add Targets Declaratively by Specifying Target Monitoring Properties**. 1. For **Target Type**, choose **Database Instance**. 1. For **Monitoring Agent**, choose the agent with the identifier that is the same as your RDS DB instance identifier. 1. Choose **Add Manually**. 1. Enter the endpoint for your Amazon RDS DB instance, or choose it from the host name list. Make sure that the specified host name matches the endpoint of the Amazon RDS DB instance. 1. Specify the following database properties: + For **Target name**, enter a name. + For **Database system name**, enter a name. + For **Monitor username**, enter **dbsnmp**. + For **Monitor password**, enter the password from [Step 2: Unlocking the DBSNMP user account](#Oracle.Options.OEMAgent.DBSNMP). + For **Role**, enter **normal**. + For **Oracle home path**, enter **/oracle**. + For **Listener Machine name**, the agent identifier already appears. + For **Port**, enter the database port. The RDS default port is 1521. + For **Database name**, enter the name of your database. If your database is a CDB, this name is `RDSCDB`. 1. Choose **Test Connection**. 1. Choose **Next**. The target database appears in your list of monitored resources. ## Removing the Management Agent option You can remove the OEM Agent from a DB instance. After you remove the OEM Agent, you don't need to restart your DB instance. To remove the OEM Agent from a DB instance, do one of the following: + Remove the OEM Agent option from the option group it belongs to. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + Modify the DB instance and specify a different option group that doesn't include the OEM Agent option. This change affects a single DB instance. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Performing database tasks with the Management Agent You can use Amazon RDS procedures to run certain EMCTL commands on the Management Agent. By running these procedures, you can do the tasks listed following. **Note** Tasks are executed asynchronously. **Topics** + [Securing the Management Agent](#Oracle.Options.OEMAgent.DBTasks.SecureAgent) + [Getting the status of the Management Agent](#Oracle.Options.OEMAgent.DBTasks.GetAgentStatus) + [Restarting the Management Agent](#Oracle.Options.OEMAgent.DBTasks.RestartAgent) + [Listing the targets monitored by the Management Agent](#Oracle.Options.OEMAgent.DBTasks.ListTargets) + [Listing the collection threads monitored by the Management Agent](#Oracle.Options.OEMAgent.DBTasks.ListCollectionThreads) + [Clearing the Management Agent state](#Oracle.Options.OEMAgent.DBTasks.ClearState) + [Making the Management Agent upload its OMS](#Oracle.Options.OEMAgent.DBTasks.ForceUploadOMS) + [Pinging the OMS](#Oracle.Options.OEMAgent.DBTasks.PingOMS) + [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus) ### Securing the Management Agent To secure the Management Agent, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_agent_tasks.secure_oem_agent`. This procedure is equivalent to running the `emctl secure agent` command. The following procedure creates a task to secure the Management Agent and returns the ID of the task: ``` SELECT rdsadmin.rdsadmin_oem_agent_tasks.secure_oem_agent as TASK_ID from DUAL; ``` To display the output file for the task and view the result, see [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus). ### Getting the status of the Management Agent To get the status of the Management Agent, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_agent_tasks.get_status_oem_agent`. This procedure is equivalent to the `emctl status agent` command. The following procedure creates a task to get the Management Agent's status and returns the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_agent_tasks.get_status_oem_agent() as TASK_ID from DUAL; ``` To display the output file for the task and view the result, see [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus). ### Restarting the Management Agent To restart the Management Agent, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_agent_tasks.restart_oem_agent`. This procedure is equivalent to running the `emctl stop agent` and `emctl start agent` commands. The following procedure creates a task to restart the Management Agent and returns the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_agent_tasks.restart_oem_agent as TASK_ID from DUAL; ``` To display the output file for the task and view the result, see [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus). ### Listing the targets monitored by the Management Agent To list the targets monitored by the Management Agent, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_agent_tasks.list_targets_oem_agent`. This procedure is equivalent to running the `emctl config agent listtargets` command. The following procedure creates a task to list the targets monitored by the Management Agent and returns the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_agent_tasks.list_targets_oem_agent as TASK_ID from DUAL; ``` To display the output file for the task and view the result, see [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus). ### Listing the collection threads monitored by the Management Agent To list of all the running, ready, and scheduled collection threads monitored by the Management Agent, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_agent_tasks.list_clxn_threads_oem_agent`. This procedure is equivalent to the `emctl status agent scheduler` command. The following procedure creates a task to list the collection threads and returns the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_agent_tasks.list_clxn_threads_oem_agent() as TASK_ID from DUAL; ``` To display the output file for the task and view the result, see [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus). ### Clearing the Management Agent state To clear the Management Agent's state, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_agent_tasks.clearstate_oem_agent`. This procedure is equivalent to running the `emctl clearstate agent` command. The following procedure creates a task that clears the Management Agent's state and returns the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_agent_tasks.clearstate_oem_agent() as TASK_ID from DUAL; ``` To display the output file for the task and view the result, see [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus). ### Making the Management Agent upload its OMS To make the Management Agent upload the Oracle Management Server (OMS) associated with it, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_agent_tasks.upload_oem_agent`. This procedure is equivalent to running the `emclt upload agent` command. The following procedure creates a task that makes the Management Agent upload its associated OMS and return the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_agent_tasks.upload_oem_agent() as TASK_ID from DUAL; ``` To display the output file for the task and view the result, see [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus). ### Pinging the OMS To ping the Management Agent's OMS, run the Amazon RDS procedure `rdsadmin.rdsadmin_oem_agent_tasks.ping_oms_oem_agent`. This procedure is equivalent to running the `emctl pingOMS` command. The following procedure creates a task that pings the Management Agent's OMS and returns the ID of the task. ``` SELECT rdsadmin.rdsadmin_oem_agent_tasks.ping_oms_oem_agent() as TASK_ID from DUAL; ``` To display the output file for the task and view the result, see [Viewing the status of an ongoing task](#Oracle.Options.OEMAgent.DBTasks.ViewTaskStatus). ### Viewing the status of an ongoing task You can view the status of an ongoing task in a bdump file. The bdump files are located in the `/rdsdbdata/log/trace` directory. Each bdump file name is in the following format. ``` dbtask-task-id.log ``` When you want to monitor a task, replace `task-id` with the ID of the task that you want to monitor. To view the contents of bdump files, run the Amazon RDS procedure `rdsadmin.rds_file_util.read_text_file`. The following query returns the contents of the `dbtask-1546988886389-2444.log` bdump file. ``` SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-1546988886389-2444.log')); ``` For more information about the Amazon RDS procedure `rdsadmin.rds_file_util.read_text_file`, see [Reading files in a DB instance directory](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.ReadingFiles). # Oracle Label Security Amazon RDS supports Oracle Label Security for the Enterprise Edition of Oracle Database through the use of the OLS option. Most database security controls access at the object level. Oracle Label Security provides fine-grained control of access to individual table rows. For example, you can use Label Security to enforce regulatory compliance with a policy-based administration model. You can use Label Security policies to control access to sensitive data, and restrict access to only users with the appropriate clearance level. For more information, see [Introduction to Oracle Label Security](https://docs.oracle.com/database/121/OLSAG/intro.htm#OLSAG001) in the Oracle documentation. **Topics** + [Requirements for Oracle Label Security](#Oracle.Options.OLS.PreReqs) + [Considerations when using Oracle Label Security](#Oracle.Options.OLS.Using) + [Adding the Oracle Label Security option](#Oracle.Options.OLS.Add) + [Troubleshooting](#Oracle.Options.OLS.Troubleshooting) ## Requirements for Oracle Label Security Familiarize yourself with the following requirements for Oracle Label Security: + Your DB instance must use the Bring Your Own License model. For more information, see [RDS for Oracle licensing options](Oracle.Concepts.Licensing.md). + You must have a valid license for Oracle Enterprise Edition with Software Update License and Support. + Your Oracle license must include the Label Security option. ## Considerations when using Oracle Label Security To use Oracle Label Security, you create policies that control access to specific rows in your tables. For more information, see [Creating an Oracle Label Security policy](https://docs.oracle.com/database/121/OLSAG/getstrtd.htm#OLSAG3096) in the Oracle documentation. Consider the following: + Oracle Label Security is a permanent and persistent option. Because the option is permanent, you can't remove it from an option group. If you add Oracle Label Security to an option group and associate it with your DB instance, you can later associate a different option group with your DB instance, but this group must also contain the Oracle Label Security option. + When you work with Label Security, you perform all actions as the `LBAC_DBA` role. The master user for your DB instance is granted the `LBAC_DBA` role. You can grant the `LBAC_DBA` role to other users so that they can administer Label Security policies. + Make sure to grant access to the `OLS_ENFORCEMENT` package to any new users who require access to Oracle Label Security. To grant access to the `OLS_ENFORCEMENT` package, connect to the DB instance as the master user and run the following SQL statement: ``` GRANT ALL ON LBACSYS.OLS_ENFORCEMENT TO username; ``` + You can configure Label Security through Oracle Enterprise Manager (OEM) Cloud Control. Amazon RDS supports OEM Cloud Control through the Management Agent option. For more information, see [Oracle Management Agent for Enterprise Manager Cloud Control](Oracle.Options.OEMAgent.md). ## Adding the Oracle Label Security option The general process for adding the Oracle Label Security option to a DB instance is the following: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. **Important** Oracle Label Security is a permanent and persistent option. 1. Associate the option group with the DB instance. After you add the Label Security option, as soon as the option group is active, Label Security is active. **To add the label security option to a DB instance** 1. Determine the option group you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine**, choose **oracle-ee**. 1. For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **OLS** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). **Important** If you add Label Security to an existing option group that is already attached to one or more DB instances, all the DB instances are restarted. 1. Apply the option group to a new or existing DB instance: + For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. When you add the Label Security option to an existing DB instance, a brief outage occurs while your DB instance is automatically restarted. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Troubleshooting The following are issues you might encounter when you use Oracle Label Security. **** | Issue | Troubleshooting suggestions | | --- | --- | | When you try to create a policy, you see an error message similar to the following: `insufficient authorization for the SYSDBA package`. | A known issue with Oracle's Label Security feature prevents users with usernames of 16 or 24 characters from running Label Security commands. You can create a new user with a different number of characters, grant LBAC\$1DBA to the new user, log in as the new user, and run the OLS commands as the new user. For additional information, contact Oracle Support. | # Oracle Locator Amazon RDS supports Oracle Locator through the use of the `LOCATOR` option. Oracle Locator provides capabilities that are typically required to support internet and wireless service-based applications and partner-based GIS solutions. Oracle Locator is a limited subset of Oracle Spatial. For more information, see [Oracle Locator](https://docs.oracle.com/database/121/SPATL/sdo_locator.htm#SPATL340) in the Oracle documentation. **Important** If you use Oracle Locator, Amazon RDS automatically updates your DB instance to the latest Oracle PSU if there are security vulnerabilities with a Common Vulnerability Scoring System (CVSS) score of 9\$1 or other announced security vulnerabilities. ## Supported database releases for Oracle Locator RDS for Oracle supports Oracle Locator for Oracle Database 19c. Oracle Locator isn't supported for Oracle Database 21c, but its functionality is available in the Oracle Spatial option. Formerly, the Spatial option required additional licenses. Oracle Locator represented a subset of Oracle Spatial features and didn't require additional licenses. In 2019, Oracle announced that all Oracle Spatial features were included in the Enterprise Edition and Standard Edition 2 licenses without additional cost. Consequently, the Oracle Spatial option no longer required additional licensing. For more information, see [Machine Learning, Spatial and Graph - No License Required\$1](https://blogs.oracle.com/database/post/machine-learning-spatial-and-graph-no-license-required) in the Oracle Database Insider blog. ## Prerequisites for Oracle Locator The following are prerequisites for using Oracle Locator: + Your DB instance must be of sufficient class. Oracle Locator is not supported for the db.t3.small DB instance classes. For more information, see [RDS for Oracle DB instance classes](Oracle.Concepts.InstanceClasses.md). + Your DB instance must have **Auto Minor Version Upgrade** enabled. This option enables your DB instance to receive minor DB engine version upgrades automatically when they become available and is required for any options that install the Oracle Java Virtual Machine (JVM). Amazon RDS uses this option to update your DB instance to the latest Oracle Patch Set Update (PSU) or Release Update (RU). For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Best practices for Oracle Locator The following are best practices for using Oracle Locator: + For maximum security, use the `LOCATOR` option with Secure Sockets Layer (SSL). For more information, see [Oracle Secure Sockets Layer](Appendix.Oracle.Options.SSL.md). + Configure your DB instance to restrict access to your DB instance. For more information, see [Scenarios for accessing a DB instance in a VPC](USER_VPC.Scenarios.md) and [Working with a DB instance in a VPC](USER_VPC.WorkingWithRDSInstanceinaVPC.md). ## Adding the Oracle Locator option The following is the general process for adding the `LOCATOR` option to a DB instance: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. 1. Associate the option group with the DB instance. If Oracle Java Virtual Machine (JVM) is *not* installed on the DB instance, there is a brief outage while the `LOCATOR` option is added. There is no outage if Oracle Java Virtual Machine (JVM) is already installed on the DB instance. After you add the option, you don't need to restart your DB instance. As soon as the option group is active, Oracle Locator is available. **Note** During this outage, password verification functions are disabled briefly. You can also expect to see events related to password verification functions during the outage. Password verification functions are enabled again before the Oracle DB instance is available. **To add the `LOCATOR` option to a DB instance** 1. Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine**, choose the oracle edition for your DB instance. 1. For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **LOCATOR** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Apply the option group to a new or existing DB instance: + For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Using Oracle Locator After you enable the Oracle Locator option, you can begin using it. You should only use Oracle Locator features. Don't use any Oracle Spatial features unless you have a license for Oracle Spatial. For a list of features that are supported for Oracle Locator, see [Features Included with Locator](https://docs.oracle.com/database/121/SPATL/sdo_locator.htm#GUID-EC6DEA23-8FD7-4109-A0C1-93C0CE3D6FF2__CFACCEEG) in the Oracle documentation. For a list of features that are not supported for Oracle Locator, see [Features Not Included with Locator](https://docs.oracle.com/database/121/SPATL/sdo_locator.htm#GUID-EC6DEA23-8FD7-4109-A0C1-93C0CE3D6FF2__CFABACEA) in the Oracle documentation. ## Removing the Oracle Locator option After you drop all objects that use data types provided by the `LOCATOR` option, you can remove the option from a DB instance. If Oracle Java Virtual Machine (JVM) is *not* installed on the DB instance, there is a brief outage while the `LOCATOR` option is removed. There is no outage if Oracle Java Virtual Machine (JVM) is already installed on the DB instance. After you remove the `LOCATOR` option, you don't need to restart your DB instance. **To drop the `LOCATOR` option** 1. Back up your data. **Warning** If the instance uses data types that were enabled as part of the option, and if you remove the `LOCATOR` option, you can lose data. For more information, see [Backing up, restoring, and exporting data](CHAP_CommonTasks.BackupRestore.md). 1. Check whether any existing objects reference data types or features of the `LOCATOR` option. If `LOCATOR` options exist, the instance can get stuck when applying the new option group that doesn't have the `LOCATOR` option. You can identify the objects by using the following queries: ``` SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME, BYTES/1024/1024 mbytes FROM DBA_SEGMENTS WHERE SEGMENT_TYPE LIKE '%TABLE%' AND (OWNER, SEGMENT_NAME) IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_TAB_COLUMNS WHERE DATA_TYPE='SDO_GEOMETRY' AND OWNER <> 'MDSYS') ORDER BY 1,2,3,4; SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE DATA_TYPE = 'SDO_GEOMETRY' AND OWNER <> 'MDSYS' ORDER BY 1,2,3; ``` 1. Drop any objects that reference data types or features of the `LOCATOR` option. 1. Do one of the following: + Remove the `LOCATOR` option from the option group it belongs to. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + Modify the DB instance and specify a different option group that doesn't include the `LOCATOR` option. This change affects a single DB instance. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Oracle native network encryption Amazon RDS supports Oracle native network encryption (NNE). With the `NATIVE_NETWORK_ENCRYPTION` option, you can encrypt data as it moves to and from a DB instance. Amazon RDS supports NNE for all editions of Oracle Database. A detailed discussion of Oracle native network encryption is beyond the scope of this guide, but you should understand the strengths and weaknesses of each algorithm and key before you decide on a solution for your deployment. For information about the algorithms and keys that are available through Oracle native network encryption, see [Configuring network data encryption](http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/security/network_encrypt/ntwrkencrypt.htm) in the Oracle documentation. For more information about AWS security, see the [AWS security center](http://aws.amazon.com/security). **Note** You can use Native Network Encryption or Secure Sockets Layer, but not both. For more information, see [Oracle Secure Sockets Layer](Appendix.Oracle.Options.SSL.md). **Topics** + [NATIVE\$1NETWORK\$1ENCRYPTION option settings](Oracle.Options.NNE.Options.md) + [Adding the NATIVE\$1NETWORK\$1ENCRYPTION option](Oracle.Options.NNE.Add.md) + [Setting NNE values in the sqlnet.ora](Oracle.Options.NNE.Using.md) + [Modifying NATIVE\$1NETWORK\$1ENCRYPTION option settings](Oracle.Options.NNE.ModifySettings.md) + [Removing the NATIVE\$1NETWORK\$1ENCRYPTION option](Oracle.Options.NNE.Remove.md) # NATIVE\$1NETWORK\$1ENCRYPTION option settings You can specify encryption requirements on both the server and the client. The DB instance can act as a client when, for example, it uses a database link to connect to another database. You might want to avoid forcing encryption on the server side. For example, you might not want to force all client communications to use encryption because the server requires it. In this case, you can force encryption on the client side using the `SQLNET.*CLIENT` options. Amazon RDS supports the following settings for the `NATIVE_NETWORK_ENCRYPTION` option. **Note** When you use commas to separate values for an option setting, don't put a space after the comma. **** | Option setting | Valid values | Default values | Description | | --- | --- | --- | --- | | `SQLNET.ALLOW_WEAK_CRYPTO_CLIENTS` | `TRUE`, `FALSE` | `TRUE` | The behavior of the server when a client using a non-secure cipher attempts to connect to the database. If `TRUE`, clients can connect even if they aren't patched with the July 2021 PSU. If the setting is `FALSE`, clients can connect to the database only when they are patched with the July 2021 PSU. Before you set `SQLNET.ALLOW_WEAK_CRYPTO_CLIENTS` to `FALSE`, make sure that the following conditions are met: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Options.NNE.Options.html) | | `SQLNET.ALLOW_WEAK_CRYPTO` | `TRUE`, `FALSE` | `TRUE` | The behavior of the server when a client using a non-secure cipher attempts to connect to the database. The following ciphers are considered not secure: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Options.NNE.Options.html) If the setting is `TRUE`, clients can connect when they use the preceding non-secure ciphers. If the setting is `FALSE`, the database prevents clients from connecting when they use the preceding non-secure ciphers. Before you set `SQLNET.ALLOW_WEAK_CRYPTO` to `FALSE`, make sure that the following conditions are met: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Options.NNE.Options.html) | | `SQLNET.CRYPTO_CHECKSUM_CLIENT` | `Accepted`, `Rejected`, `Requested`, `Required` | `Requested` | The data integrity behavior when a DB instance connects to the client, or a server acting as a client. When a DB instance uses a database link, it acts as a client. `Requested` indicates that the client doesn't require the DB instance to perform a checksum. | | `SQLNET.CRYPTO_CHECKSUM_SERVER` | `Accepted`, `Rejected`, `Requested`, `Required` | `Requested` | The data integrity behavior when a client, or a server acting as a client, connects to the DB instance. When a DB instance uses a database link, it acts as a client. `Requested` indicates that the DB instance doesn't require the client to perform a checksum. | | `SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT` | `SHA256`, `SHA384`, `SHA512`, `SHA1`, `MD5` | `SHA256`, `SHA384`, `SHA512` | A list of checksum algorithms. You can specify either one value or a comma-separated list of values. If you use a comma, don't insert a space after the comma; otherwise, you receive an `InvalidParameterValue` error. This parameter and `SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER `must have a common cipher. | | `SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER` | `SHA256`, `SHA384`, `SHA512`, `SHA1`, `MD5` | `SHA256`, `SHA384`, `SHA512`, `SHA1`, `MD5` | A list of checksum algorithms. You can specify either one value or a comma-separated list of values. If you use a comma, don't insert a space after the comma; otherwise, you receive an `InvalidParameterValue` error. This parameter and `SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT` must have a common cipher. | | `SQLNET.ENCRYPTION_CLIENT` | `Accepted`, `Rejected`, `Requested`, `Required` | `Requested` | The encryption behavior of the client when a client, or a server acting as a client, connects to the DB instance. When a DB instance uses a database link, it acts as a client. `Requested` indicates that the client does not require traffic from the server to be encrypted. | | `SQLNET.ENCRYPTION_SERVER` | `Accepted`, `Rejected`, `Requested`, `Required` | `Requested` | The encryption behavior of the server when a client, or a server acting as a client, connects to the DB instance. When a DB instance uses a database link, it acts as a client. `Requested` indicates that the DB instance does not require traffic from the client to be encrypted. | | `SQLNET.ENCRYPTION_TYPES_CLIENT` | `RC4_256`, `AES256`, `AES192`, `3DES168`, `RC4_128`, `AES128`, `3DES112`, `RC4_56`, `DES`, `RC4_40`, `DES40` | `RC4_256`, `AES256`, `AES192`, `3DES168`, `RC4_128`, `AES128`, `3DES112`, `RC4_56`, `DES`, `RC4_40`, `DES40` | A list of encryption algorithms used by the client. The client attempts to decrypt the server input by trying each algorithm in order, proceeding until an algorithm succeeds or the end of the list is reached. Amazon RDS uses the following default list from Oracle. RDS starts with `RC4_256` and proceeds down the list in order. You can change the order or limit the algorithms that the DB instance will accept. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Options.NNE.Options.html) You can specify either one value or a comma-separated list of values. If you a comma, don't insert a space after the comma; otherwise, you receive an `InvalidParameterValue` error. This parameter and `SQLNET.SQLNET.ENCRYPTION_TYPES_SERVER` must have a common cipher. | | `SQLNET.ENCRYPTION_TYPES_SERVER` | `RC4_256`, `AES256`, `AES192`, `3DES168`, `RC4_128`, `AES128`, `3DES112`, `RC4_56`, `DES`, `RC4_40`, `DES40` | `RC4_256`, `AES256`, `AES192`, `3DES168`, `RC4_128`, `AES128`, `3DES112`, `RC4_56`, `DES`, `RC4_40`, `DES40` | A list of encryption algorithms used by the DB instance. The DB instance uses each algorithm, in order, to attempt to decrypt the client input until an algorithm succeeds or until the end of the list is reached. Amazon RDS uses the following default list from Oracle. You can change the order or limit the algorithms that the client will accept. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Options.NNE.Options.html) You can specify either one value or a comma-separated list of values. If you a comma, don't insert a space after the comma; otherwise, you receive an `InvalidParameterValue` error. This parameter and `SQLNET.SQLNET.ENCRYPTION_TYPES_SERVER` must have a common cipher. | # Adding the NATIVE\$1NETWORK\$1ENCRYPTION option The general process for adding the `NATIVE_NETWORK_ENCRYPTION` option to a DB instance is the following: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. 1. Associate the option group with the DB instance. When the option group is active, NNE is active. **To add the NATIVE\$1NETWORK\$1ENCRYPTION option to a DB instance using the AWS Management Console** 1. For **Engine**, choose the Oracle edition that you want to use. NNE is supported on all editions. 1. For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **NATIVE\$1NETWORK\$1ENCRYPTION** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). **Note** After you add the **NATIVE\$1NETWORK\$1ENCRYPTION** option, you don't need to restart your DB instances. As soon as the option group is active, NNE is active. 1. Apply the option group to a new or existing DB instance: + For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. After you add the **NATIVE\$1NETWORK\$1ENCRYPTION** option, you don't need to restart your DB instance. As soon as the option group is active, NNE is active. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Setting NNE values in the sqlnet.ora With Oracle native network encryption, you can set network encryption on the server side and client side. The client is the computer used to connect to the DB instance. You can specify the following client settings in the sqlnet.ora: + `SQLNET.ALLOW_WEAK_CRYPTO` + `SQLNET.ALLOW_WEAK_CRYPTO_CLIENTS` + `SQLNET.CRYPTO_CHECKSUM_CLIENT` + `SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT` + `SQLNET.ENCRYPTION_CLIENT` + `SQLNET.ENCRYPTION_TYPES_CLIENT` For information, see [Configuring network data encryption and integrity for Oracle servers and clients](http://docs.oracle.com/cd/E11882_01/network.112/e40393/asoconfg.htm) in the Oracle documentation. Sometimes, the DB instance rejects a connection request from an application. For example, a rejection can occur when the encryption algorithms on the client and on the server don't match. To test Oracle native network encryption, add the following lines to the sqlnet.ora file on the client: ``` DIAG_ADR_ENABLED=off TRACE_DIRECTORY_CLIENT=/tmp TRACE_FILE_CLIENT=nettrace TRACE_LEVEL_CLIENT=16 ``` When a connection is attempted, the preceding lines generate a trace file on the client called `/tmp/nettrace*`. The trace file contains information about the connection. For more information about connection-related issues when you are using Oracle Native Network Encryption, see [About negotiating encryption and integrity](http://docs.oracle.com/cd/E11882_01/network.112/e40393/asoconfg.htm#autoId12) in the Oracle Database documentation. # Modifying NATIVE\$1NETWORK\$1ENCRYPTION option settings After you enable the `NATIVE_NETWORK_ENCRYPTION` option, you can modify its settings. Currently, you can modify `NATIVE_NETWORK_ENCRYPTION` option settings only with the AWS CLI or RDS API. You can't use the console. The following example modifies two settings in the option. ``` aws rds add-option-to-option-group \ --option-group-name my-option-group \ --options "OptionName=NATIVE_NETWORK_ENCRYPTION,OptionSettings=[{Name=SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER,Value=SHA256},{Name=SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER,Value=SHA256}]" \ --apply-immediately ``` To learn how to modify option settings using the CLI, see [AWS CLI](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.ModifyOption.CLI). For more information about each setting, see [NATIVE\$1NETWORK\$1ENCRYPTION option settings](Oracle.Options.NNE.Options.md). **Topics** + [Modifying CRYPTO\$1CHECKSUM\$1\$1 values](#Oracle.Options.NNE.ModifySettings.checksum) + [Modifying ALLOW\$1WEAK\$1CRYPTO\$1 settings](#Oracle.Options.NNE.ModifySettings.encryption) ## Modifying CRYPTO\$1CHECKSUM\$1\$1 values If you modify **NATIVE\$1NETWORK\$1ENCRYPTION** option settings, make sure that the following option settings have at least one common cipher: + `SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER` + `SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT` The following example shows a scenario in which you modify `SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER`. The configuration is valid because the `CRYPTO_CHECKSUM_TYPES_CLIENT` and `CRYPTO_CHECKSUM_TYPES_SERVER` both use `SHA256`. | Option setting | Values before modification | Values after modification | | --- | --- | --- | | `SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT` | `SHA256`, `SHA384`, `SHA512` | No change | | `SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER` | `SHA256`, `SHA384`, `SHA512`, `SHA1`, `MD5` | SHA1,MD5,SHA256 | For another example, assume that you want to modify `SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER` from its default setting to `SHA1,MD5`. In this case, make sure you set `SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT` to `SHA1` or `MD5`. These algorithms aren't included in the default values for `SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT`. ## Modifying ALLOW\$1WEAK\$1CRYPTO\$1 settings To set the `SQLNET.ALLOW_WEAK_CRYPTO*` options from the default value to `FALSE`, make sure that the following conditions are met: + `SQLNET.ENCRYPTION_TYPES_SERVER` and `SQLNET.ENCRYPTION_TYPES_CLIENT` have one matching secure encryption method. A method is considered secure if it's not `DES`, `3DES`, or `RC4` (all key lengths). + `SQLNET.CHECKSUM_TYPES_SERVER` and `SQLNET.CHECKSUM_TYPES_CLIENT` have one matching secure checksumming method. A method is considered secure if it's not `MD5`. + The client is patched with the July 2021 PSU. If the client isn't patched, the client loses the connection and receives the `ORA-12269` error. The following example shows sample NNE settings. Assume that you want to set `SQLNET.ENCRYPTION_TYPES_SERVER` and `SQLNET.ENCRYPTION_TYPES_CLIENT` to FALSE, thereby blocking non-secure connections. The checksum option settings meet the prerequisites because they both have `SHA256`. However, `SQLNET.ENCRYPTION_TYPES_CLIENT` and `SQLNET.ENCRYPTION_TYPES_SERVER` use the `DES`, `3DES`, and `RC4` encryption methods, which are non-secure. Therefore, to set the `SQLNET.ALLOW_WEAK_CRYPTO*` options to `FALSE`, first set `SQLNET.ENCRYPTION_TYPES_SERVER` and `SQLNET.ENCRYPTION_TYPES_CLIENT` to a secure encryption method such as `AES256`. | Option setting | Values | | --- | --- | | `SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT` | `SHA256`, `SHA384`, `SHA512` | | `SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER` | SHA1,MD5,SHA256 | | `SQLNET.ENCRYPTION_TYPES_CLIENT` | `RC4_256`, `3DES168`, `DES40` | | `SQLNET.ENCRYPTION_TYPES_SERVER` | `RC4_256`, `3DES168`, `DES40` | # Removing the NATIVE\$1NETWORK\$1ENCRYPTION option You can remove NNE from a DB instance. To remove the `NATIVE_NETWORK_ENCRYPTION` option from a DB instance, do one of the following: + To remove the option from multiple DB instances, remove the `NATIVE_NETWORK_ENCRYPTION` option from the option group they belong to. This change affects all DB instances that use the option group. After you remove the `NATIVE_NETWORK_ENCRYPTION` option, you don't need to restart your DB instances. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + To remove the option from a single DB instance, modify the DB instance and specify a different option group that doesn't include the `NATIVE_NETWORK_ENCRYPTION` option. You can specify the default (empty) option group, or a different custom option group. After you remove the `NATIVE_NETWORK_ENCRYPTION` option, you don't need to restart your DB instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Oracle OLAP Amazon RDS supports Oracle OLAP through the use of the `OLAP` option. This option provides On-line Analytical Processing (OLAP) for Oracle DB instances. You can use Oracle OLAP to analyze large amounts of data by creating dimensional objects and cubes in accordance with the OLAP standard. For more information, see [the Oracle documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/olaug/index.html). **Important** If you use Oracle OLAP, Amazon RDS automatically updates your DB instance to the latest Oracle PSU if there are security vulnerabilities with a Common Vulnerability Scoring System (CVSS) score of 9\$1 or other announced security vulnerabilities. Amazon RDS supports Oracle OLAP for the Enterprise Edition of Oracle Database 19c and higher. ## Prerequisites for Oracle OLAP The following are prerequisites for using Oracle OLAP: + You must have an Oracle OLAP license from Oracle. For more information, see [Licensing Information](https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4) in the Oracle documentation. + Your DB instance must be of a sufficient instance class. Oracle OLAP isn't supported for the db.t3.small DB instance classes. For more information, see [RDS for Oracle DB instance classes](Oracle.Concepts.InstanceClasses.md). + Your DB instance must have **Auto Minor Version Upgrade** enabled. This option enables your DB instance to receive minor DB engine version upgrades automatically when they become available and is required for any options that install the Oracle Java Virtual Machine (JVM). Amazon RDS uses this option to update your DB instance to the latest Oracle Patch Set Update (PSU) or Release Update (RU). For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). + Your DB instance must not have a user named `OLAPSYS`. If it does, the OLAP option installation fails. ## Best practices for Oracle OLAP The following are best practices for using Oracle OLAP: + For maximum security, use the `OLAP` option with Secure Sockets Layer (SSL). For more information, see [Oracle Secure Sockets Layer](Appendix.Oracle.Options.SSL.md). + Configure your DB instance to restrict access to your DB instance. For more information, see [Scenarios for accessing a DB instance in a VPC](USER_VPC.Scenarios.md) and [Working with a DB instance in a VPC](USER_VPC.WorkingWithRDSInstanceinaVPC.md). ## Adding the Oracle OLAP option The following is the general process for adding the `OLAP` option to a DB instance: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. 1. Associate the option group with the DB instance. If Oracle Java Virtual Machine (JVM) is *not* installed on the DB instance, there is a brief outage while the `OLAP` option is added. There is no outage if Oracle Java Virtual Machine (JVM) is already installed on the DB instance. After you add the option, you don't need to restart your DB instance. As soon as the option group is active, Oracle OLAP is available. **To add the OLAP option to a DB instance** 1. Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: + For **Engine**, choose the Oracle edition for your DB instance. + For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **OLAP** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Apply the option group to a new or existing DB instance: + For a new DB instance, apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Using Oracle OLAP After you enable the Oracle OLAP option, you can begin using it. For a list of features that are supported for Oracle OLAP, see [the Oracle documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/olaug/overview.html#GUID-E2056FE4-C623-4D29-B7D8-C4762F941966). ## Removing the Oracle OLAP option After you drop all objects that use data types provided by the `OLAP` option, you can remove the option from a DB instance. If Oracle Java Virtual Machine (JVM) is *not* installed on the DB instance, there is a brief outage while the `OLAP` option is removed. There is no outage if Oracle Java Virtual Machine (JVM) is already installed on the DB instance. After you remove the `OLAP` option, you don't need to restart your DB instance. **To drop the `OLAP` option** 1. Back up your data. **Warning** If the instance uses data types that were enabled as part of the option, and if you remove the `OLAP` option, you can lose data. For more information, see [Backing up, restoring, and exporting data](CHAP_CommonTasks.BackupRestore.md). 1. Check whether any existing objects reference data types or features of the `OLAP` option. 1. Drop any objects that reference data types or features of the `OLAP` option. 1. Do one of the following: + Remove the `OLAP` option from the option group it belongs to. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + Modify the DB instance and specify a different option group that doesn't include the `OLAP` option. This change affects a single DB instance. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Oracle Secure Sockets Layer To enable SSL encryption for an RDS for Oracle DB instance, add the Oracle SSL option to the option group associated with the DB instance. Amazon RDS uses a second port, as required by Oracle, for SSL connections. This approach allows both clear text and SSL-encrypted communication to occur at the same time between a DB instance and SQL\$1Plus. For example, you can use the port with clear text communication to communicate with other resources inside a VPC while using the port with SSL-encrypted communication to communicate with resources outside the VPC. **Note** You can use either SSL or Native Network Encryption (NNE) on the same RDS for Oracle DB instance, but not both. If you use SSL encryption, make sure to turn off any other connection encryption. For more information, see [Oracle native network encryption](Appendix.Oracle.Options.NetworkEncryption.md). SSL/TLS and NNE are no longer part of Oracle Advanced Security. In RDS for Oracle, you can use SSL encryption with all licensed editions of the following database versions: + Oracle Database 21c (21.0.0) + Oracle Database 19c (19.0.0) **Topics** + [TLS versions for the Oracle SSL option](#Appendix.Oracle.Options.SSL.TLS) + [Cipher suites for the Oracle SSL option](#Appendix.Oracle.Options.SSL.CipherSuites) + [FIPS support](#Appendix.Oracle.Options.SSL.FIPS) + [Certificate compatibility with cipher suites](#Appendix.Oracle.Options.SSL.CertificateCompatibility) + [Adding the SSL option](Appendix.Oracle.Options.SSL.OptionGroup.md) + [Configuring SQL\$1Plus to use SSL with an RDS for Oracle DB instance](Appendix.Oracle.Options.SSL.ClientConfiguration.md) + [Connecting to an RDS for Oracle DB instance using SSL](Appendix.Oracle.Options.SSL.Connecting.md) + [Setting up an SSL connection over JDBC](Appendix.Oracle.Options.SSL.JDBC.md) + [Enforcing a DN match with an SSL connection](Appendix.Oracle.Options.SSL.DNMatch.md) + [Troubleshooting SSL connections](Appendix.Oracle.Options.SSL.troubleshooting.md) ## TLS versions for the Oracle SSL option Amazon RDS for Oracle supports Transport Layer Security (TLS) versions 1.0 and 1.2. When you add a new Oracle SSL option, set `SQLNET.SSL_VERSION` explicitly to a valid value. The following values are allowed for this option setting: + `"1.0"` – Clients can connect to the DB instance using TLS version 1.0 only. For existing Oracle SSL options, `SQLNET.SSL_VERSION` is set to `"1.0"` automatically. You can change the setting if necessary. + `"1.2"` – Clients can connect to the DB instance using TLS 1.2 only. + `"1.2 or 1.0"` – Clients can connect to the DB instance using either TLS 1.2 or 1.0. ## Cipher suites for the Oracle SSL option Amazon RDS for Oracle supports multiple SSL cipher suites. By default, the Oracle SSL option is configured to use the `SSL_RSA_WITH_AES_256_CBC_SHA` cipher suite. To specify a different cipher suite to use over SSL connections, use the `SQLNET.CIPHER_SUITE` option setting. You can specify multiple values for `SQLNET.CIPHER_SUITE`. This technique is useful if you have database links between your DB instances and decide to update your cipher suites. The following table summarizes SSL support for RDS for Oracle in all editions of Oracle Database 19c and 21c. | Cipher suite (SQLNET.CIPHER\$1SUITE) | TLS version support (SQLNET.SSL\$1VERSION) | FIPS support | FedRAMP compliant | | --- | --- | --- | --- | | SSL\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA (default) | 1.0 and 1.2 | Yes | No | | SSL\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA256 | 1.2 | Yes | No | | SSL\$1RSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 | 1.2 | Yes | No | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 | 1.2 | Yes | Yes | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1GCM\$1SHA256 | 1.2 | Yes | Yes | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | 1.2 | Yes | Yes | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 | 1.2 | Yes | Yes | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA | 1.2 | Yes | Yes | | TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA | 1.2 | Yes | Yes | | TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 | 1.2 | Yes | Yes | | TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | 1.2 | Yes | Yes | ## FIPS support RDS for Oracle allows you to use the Federal Information Processing Standard (FIPS) standard for 140-2. FIPS 140-2 is a United States government standard that defines cryptographic module security requirements. You turn on the FIPS standard by setting `FIPS.SSLFIPS_140` to `TRUE` for the Oracle SSL option. When FIPS 140-2 is configured for SSL, the cryptographic libraries encrypt data between the client and the RDS for Oracle DB instance. Clients must use the cipher suite that is FIPS-compliant. When establishing a connection, the client and RDS for Oracle DB instance negotiate which cipher suite to use when transmitting messages back and forth. The table in [Cipher suites for the Oracle SSL option](#Appendix.Oracle.Options.SSL.CipherSuites) shows the FIPS-compliant SSL cipher suites for each TLS version. For more information, see [Oracle database FIPS 140-2 settings](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/oracle-database-fips-140-settings.html#GUID-DDBEB3F9-B216-44BB-8C18-43B5E468CBBB) in the Oracle Database documentation. ## Certificate compatibility with cipher suites RDS for Oracle supports both RSA and Elliptic Curve Digital Signature Algorithm (ECDSA) certificates. When you configure SSL for your DB instance, you must ensure that the cipher suites you specify in the `SQLNET.CIPHER_SUITE` option setting are compatible with the certificate type used by your DB instance. The following table shows the compatibility between certificate types and cipher suites: | Certificate type | Compatible cipher suites | Incompatible cipher suites | | --- | --- | --- | | RSA certificates (rds-ca-2019, rds-ca-rsa2048-g1, rds-ca-rsa4096-g1) | SSL\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA SSL\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA256 SSL\$1RSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1GCM\$1SHA256 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA | TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | | ECDSA certificates (rds-ca-ecc384-g1) | TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 TLS\$1ECDHE\$1ECDSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 | SSL\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA SSL\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA256 SSL\$1RSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1GCM\$1SHA384 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1GCM\$1SHA256 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA384 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA256 TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1256\$1CBC\$1SHA TLS\$1ECDHE\$1RSA\$1WITH\$1AES\$1128\$1CBC\$1SHA | When you specify multiple cipher suites in the `SQLNET.CIPHER_SUITE` option setting, make sure to include at least one cipher suite that is compatible with the certificate type used by your DB instance. If you're using an option group with multiple DB instances that have different certificate types, include at least one cipher suite for each certificate type. If you attempt to associate an option group with an SSL option that contains only cipher suites incompatible with the certificate type of a DB instance, the operation will fail with an error message indicating the incompatibility. # Adding the SSL option To use SSL, your RDS for Oracle DB instance must be associated with an option group that includes the `SSL` option. ## Console **To add the SSL option to an option group** 1. Create a new option group or identify an existing option group to which you can add the `SSL` option. For information about creating an option group, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the `SSL` option to the option group. If you want to use only FIPS-verified cipher suites for SSL connections, set the option `FIPS.SSLFIPS_140` to `TRUE`. For information about the FIPS standard, see [FIPS support](Appendix.Oracle.Options.SSL.md#Appendix.Oracle.Options.SSL.FIPS). For information about adding an option to an option group, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Create a new RDS for Oracle DB instance and associate the option group with it, or modify an RDS for Oracle DB instance to associate the option group with it. For information about creating an DB instance, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). For information about modifying an DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## AWS CLI **To add the SSL option to an option group** 1. Create a new option group or identify an existing option group to which you can add the `SSL` option. For information about creating an option group, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the `SSL` option to the option group. Specify the following option settings: + `Port` – The SSL port number + `VpcSecurityGroupMemberships` – The VPC security group for which the option is enabled + `SQLNET.SSL_VERSION` – The TLS version that client can use to connect to the DB instance For example, the following AWS CLI command adds the `SSL` option to an option group named `ora-option-group`. **Example** For Linux, macOS, or Unix: ``` aws rds add-option-to-option-group --option-group-name ora-option-group \ --options 'OptionName=SSL,Port=2484,VpcSecurityGroupMemberships="sg-68184619",OptionSettings=[{Name=SQLNET.SSL_VERSION,Value=1.0}]' ``` For Windows: ``` aws rds add-option-to-option-group --option-group-name ora-option-group ^ --options 'OptionName=SSL,Port=2484,VpcSecurityGroupMemberships="sg-68184619",OptionSettings=[{Name=SQLNET.SSL_VERSION,Value=1.0}]' ``` 1. Create a new RDS for Oracle DB instance and associate the option group with it, or modify an RDS for Oracle DB instance to associate the option group with it. For information about creating an DB instance, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). For information about modifying an DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Configuring SQL\$1Plus to use SSL with an RDS for Oracle DB instance Before you can connect to an RDS for Oracle DB instance that uses the Oracle SSL option, you must configure SQL\$1Plus before connecting. **Note** To allow access to the DB instance from the appropriate clients, ensure that your security groups are configured correctly. For more information, see [Controlling access with security groups](Overview.RDSSecurityGroups.md). Also, these instructions are for SQL\$1Plus and other clients that directly use an Oracle home. For JDBC connections, see [Setting up an SSL connection over JDBC](Appendix.Oracle.Options.SSL.JDBC.md). **To configure SQL\$1Plus to use SSL to connect to an RDS for Oracle DB instance** 1. Set the `ORACLE_HOME` environment variable to the location of your Oracle home directory. The path to your Oracle home directory depends on your installation. The following example sets the `ORACLE_HOME` environment variable. ``` prompt>export ORACLE_HOME=/home/user/app/user/product/19.0.0/dbhome_1 ``` For information about setting Oracle environment variables, see [SQL\$1Plus environment variables](http://docs.oracle.com/database/121/SQPUG/ch_two.htm#SQPUG331) in the Oracle documentation, and also see the Oracle installation guide for your operating system. 1. Append `$ORACLE_HOME/lib` to the `LD_LIBRARY_PATH` environment variable. The following is an example that sets the LD\$1LIBRARY\$1PATH environment variable. ``` prompt>export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib ``` 1. Create a directory for the Oracle wallet at `$ORACLE_HOME/ssl_wallet`. The following is an example that creates the Oracle wallet directory. ``` prompt>mkdir $ORACLE_HOME/ssl_wallet ``` 1. Download the certificate bundle .pem file that works for all AWS Regions and put the file in the ssl\$1wallet directory. For information, see [Using SSL/TLS to encrypt a connection to a DB instance or cluster ](UsingWithRDS.SSL.md). 1. In the `$ORACLE_HOME/network/admin` directory, modify or create the `tnsnames.ora` file and include the following entry. ``` net_service_name = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS) (HOST = endpoint) (PORT = ssl_port_number) ) ) (CONNECT_DATA = (SID = database_name) ) (SECURITY = (SSL_SERVER_CERT_DN = "C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=endpoint") ) ) ``` 1. In the same directory, modify or create the sqlnet.ora file and include the following parameters. **Note** To communicate with entities over a TLS secured connection, Oracle requires a wallet with the necessary certificates for authentication. You can use Oracle's ORAPKI utility to create and maintain Oracle wallets, as shown in step 7. For more information, see [Setting up Oracle wallet using ORAPKI](https://docs.oracle.com/cd/E92519_02/pt856pbr3/eng/pt/tsvt/task_SettingUpOracleWalletUsingORAPKI.html) in the Oracle documentation. ``` WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = $ORACLE_HOME/ssl_wallet))) SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 1.0 SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA) SSL_SERVER_DN_MATCH = ON ``` **Note** You can set `SSL_VERSION` to a higher value if your DB instance supports it. 1. Run the following command to create the Oracle wallet. ``` prompt>orapki wallet create -wallet $ORACLE_HOME/ssl_wallet -auto_login_only ``` 1. Extract each certificate in the .pem bundle file into a separate .pem file using an OS utility. 1. Add each certificate to your wallet using separate `orapki` commands, replacing `certificate-pem-file` with the absolute file name of the .pem file. ``` prompt>orapki wallet add -wallet $ORACLE_HOME/ssl_wallet -trusted_cert -cert certificate-pem-file -auto_login_only ``` For more information, see [Rotating your SSL/TLS certificate](UsingWithRDS.SSL-certificate-rotation.md). # Connecting to an RDS for Oracle DB instance using SSL After you configure SQL\$1Plus to use SSL as described previously, you can connect to the RDS for Oracle DB instance with the SSL option. Optionally, you can first export the `TNS_ADMIN` value that points to the directory that contains the tnsnames.ora and sqlnet.ora files. Doing so ensures that SQL\$1Plus can find these files consistently. The following example exports the `TNS_ADMIN` value. ``` export TNS_ADMIN=${ORACLE_HOME}/network/admin ``` Connect to the DB instance. For example, you can connect using SQL\$1Plus and a ** in a tnsnames.ora file. ``` sqlplus mydbuser@net_service_name ``` You can also connect to the DB instance using SQL\$1Plus without using a tnsnames.ora file by using the following command. ``` sqlplus 'mydbuser@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = endpoint) (PORT = ssl_port_number))(CONNECT_DATA = (SID = database_name)))' ``` You can also connect to the RDS for Oracle DB instance without using SSL. For example, the following command connects to the DB instance through the clear text port without SSL encryption. ``` sqlplus 'mydbuser@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = endpoint) (PORT = port_number))(CONNECT_DATA = (SID = database_name)))' ``` If you want to close Transmission Control Protocol (TCP) port access, create a security group with no IP address ingresses and add it to the instance. This addition closes connections over the TCP port, while still allowing connections over the SSL port that are specified from IP addresses within the range permitted by the SSL option security group. # Setting up an SSL connection over JDBC To use an SSL connection over JDBC, you must create a keystore, trust the Amazon RDS root CA certificate, and use the code snippet specified following. To create the keystore in JKS format, you can use the following command. For more information about creating the keystore, see the [Creating a keystore](https://docs.oracle.com/cd/E35822_01/server.740/es_admin/src/tadm_ssl_jetty_keystore.html) in the Oracle documentation. For reference information, see [keytool](https://docs.oracle.com/javase/8/docs/technotes/tools/windows/keytool.html) in the *Java Platform, Standard Edition Tools Reference*. ``` keytool -genkey -alias client -validity 365 -keyalg RSA -keystore clientkeystore ``` Take the following steps to trust the Amazon RDS root CA certificate. **To trust the Amazon RDS root CA certificate** 1. Download the certificate bundle .pem file that works for all AWS Regions and put the file in the ssl\$1wallet directory. For information about downloading certificates, see [Using SSL/TLS to encrypt a connection to a DB instance or cluster ](UsingWithRDS.SSL.md). 1. Extract each certificate in the .pem file into a separate file using an OS utility. 1. Convert each certificate to .der format using a separate `openssl` command, replacing *certificate-pem-file* with the name of the certificate .pem file (without the .pem extension). ``` openssl x509 -outform der -in certificate-pem-file.pem -out certificate-pem-file.der ``` 1. Import each certificate into the keystore using the following command. ``` keytool -import -alias rds-root -keystore clientkeystore.jks -file certificate-pem-file.der ``` For more information, see [Rotating your SSL/TLS certificate](UsingWithRDS.SSL-certificate-rotation.md). 1. Confirm that the key store was created successfully. ``` keytool -list -v -keystore clientkeystore.jks ``` Enter the keystore password when you are prompted for it. The following code example shows how to set up the SSL connection using JDBC. ``` import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class OracleSslConnectionTest { private static final String DB_SERVER_NAME = "dns-name-provided-by-amazon-rds"; private static final Integer SSL_PORT = "ssl-option-port-configured-in-option-group"; private static final String DB_SID = "oracle-sid"; private static final String DB_USER = "user-name"; private static final String DB_PASSWORD = "password"; // This key store has only the prod root ca. private static final String KEY_STORE_FILE_PATH = "file-path-to-keystore"; private static final String KEY_STORE_PASS = "keystore-password"; public static void main(String[] args) throws SQLException { final Properties properties = new Properties(); final String connectionString = String.format( "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=%s)(PORT=%d))(CONNECT_DATA=(SID=%s)))", DB_SERVER_NAME, SSL_PORT, DB_SID); properties.put("user", DB_USER); properties.put("password", DB_PASSWORD); properties.put("oracle.jdbc.J2EE13Compliant", "true"); properties.put("javax.net.ssl.trustStore", KEY_STORE_FILE_PATH); properties.put("javax.net.ssl.trustStoreType", "JKS"); properties.put("javax.net.ssl.trustStorePassword", KEY_STORE_PASS); final Connection connection = DriverManager.getConnection(connectionString, properties); // If no exception, that means handshake has passed, and an SSL connection can be opened } } ``` **Note** Specify a password other than the prompt shown here as a security best practice. # Enforcing a DN match with an SSL connection You can use the Oracle parameter `SSL_SERVER_DN_MATCH` to enforce that the distinguished name (DN) for the database server matches its service name. If you enforce the match verifications, then SSL ensures that the certificate is from the server. If you don't enforce the match verification, then SSL performs the check but allows the connection, regardless if there is a match. If you do not enforce the match, you allow the server to potentially fake its identity. To enforce DN matching, add the DN match property and use the connection string specified below. Add the property to the client connection to enforce DN matching. ``` properties.put("oracle.net.ssl_server_dn_match", "TRUE"); ``` Use the following connection string to enforce DN matching when using SSL. ``` final String connectionString = String.format( "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=%s)(PORT=%d))" + "(CONNECT_DATA=(SID=%s))" + "(SECURITY = (SSL_SERVER_CERT_DN = \"C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=%s\")))", DB_SERVER_NAME, SSL_PORT, DB_SID, DB_SERVER_NAME); ``` # Troubleshooting SSL connections You might query your database and receive the `ORA-28860` error. ``` ORA-28860: Fatal SSL error 28860. 00000 - "Fatal SSL error" *Cause: An error occurred during the SSL connection to the peer. It is likely that this side sent data which the peer rejected. *Action: Enable tracing to determine the exact cause of this error. ``` This error occurs when the client attempts to connect using a version of TLS that the server doesn't support. To avoid this error, edit the sqlnet.ora and set `SSL_VERSION` to the correct TLS version. For more information, see [Oracle Support Document 2748438.1](https://support.oracle.com/epmos/faces/DocumentDisplay?id=2748438.1) in My Oracle Support. # Oracle Spatial Amazon RDS supports Oracle Spatial through the use of the `SPATIAL` option. Oracle Spatial provides a SQL schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial data in an Oracle database. For more information, see [Spatial Concepts](http://docs.oracle.com/database/121/SPATL/spatial-concepts.htm#SPATL010) in the Oracle documentation. Amazon RDS supports Oracle Spatial in all editions of all supported releases. ## How Spatial Patch Bundles (SPBs) work Every quarter, RDS for Oracle releases new minor engine versions for every supported major engine. A Release Update (RU) engine version incorporates bug fixes from Oracle by including the RU patches for the specified quarter. A Spatial Patch Bundle (SPB) engine version contains RU patches plus patches specific to Oracle Spatial. For example, 19.0.0.0.ru-2025-01.spb-1.r1 is a minor engine version that contains the RU patches in engine version 19.0.0.0.ru-2025-01.rur-2025-01.r1 plus Spatial patches. SPBs are supported only for Oracle Database 19c. SPBs function in the same way as RUs, although they are named differently. An RU uses the naming format 19.0.0.0.ru-2025-01.rur-2025-01.r1. An SPB name includes the text "spb," as in 19.0.0.0.ru-2025-01.spb-1.r1. Typically, an SPB is released 2–3 weeks after its corresponding quarterly RU. For example, 19.0.0.0.ru-2025-01.spb-1.r1 is released after 19.0.0.0.ru-2025-01.rur-2025-01.r1. RDS for Oracle has separate paths for automatic minor version upgrades of RUs and SPBs. If your DB instance uses an RU, then RDS automatically upgrades your instance to an RU. If your DB instance uses an SPB, then RDS upgrades your instance to an SPB. For more information about RUs and SPBs, see [Oracle minor version upgrades](USER_UpgradeDBInstance.Oracle.Minor.md). For a list of supported RUs and SPBs for Oracle Database 19c, see [Amazon RDS for Oracle Database 19c (19.0.0.0)](https://docs.aws.amazon.com/AmazonRDS/latest/OracleReleaseNotes/oracle-version-19-0.html) in *Amazon RDS for Oracle Release Notes*. ## Prerequisites for Oracle Spatial The following are prerequisites for using Oracle Spatial: + Make sure that your DB instance is of a sufficient instance class. Oracle Spatial isn't supported for the db.t3.small DB instance classes. For more information, see [RDS for Oracle DB instance classes](Oracle.Concepts.InstanceClasses.md). + Make sure that your DB instance has **Auto Minor Version Upgrade** enabled. This option enables your DB instance to receive minor DB engine version upgrades automatically when they become available and is required for any options that install the Oracle Java Virtual Machine (JVM). Amazon RDS uses this option to update your DB instance to the latest Oracle Patch Set Update (PSU) or Release Update (RU). For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Best practices for Oracle Spatial The following are best practices for using Oracle Spatial: + For maximum security, use the `SPATIAL` option with Secure Sockets Layer (SSL). For more information, see [Oracle Secure Sockets Layer](Appendix.Oracle.Options.SSL.md). + Configure your DB instance to restrict access to your DB instance. For more information, see [Scenarios for accessing a DB instance in a VPC](USER_VPC.Scenarios.md) and [Working with a DB instance in a VPC](USER_VPC.WorkingWithRDSInstanceinaVPC.md). ## Adding the Oracle Spatial option The following is the general process for adding the `SPATIAL` option to a DB instance: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. 1. Associate the option group with the DB instance. If Oracle Java Virtual Machine (JVM) is *not* installed on the DB instance, there is a brief outage while the `SPATIAL` option is added. There is no outage if Oracle Java Virtual Machine (JVM) is already installed on the DB instance. After you add the option, you don't need to restart your DB instance. As soon as the option group is active, Oracle Spatial is available. **Note** During this outage, password verification functions are disabled briefly. You can also expect to see events related to password verification functions during the outage. Password verification functions are enabled again before the Oracle DB instance is available. **To add the `SPATIAL` option to a DB instance** 1. Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine**, choose the Oracle edition for your DB instance. 1. For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **SPATIAL** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Apply the option group to a new or existing DB instance: + For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Removing the Oracle Spatial option After you drop all objects that use data types provided by the `SPATIAL` option, you can drop the option from a DB instance. If Oracle Java Virtual Machine (JVM) is *not* installed on the DB instance, there is a brief outage while the `SPATIAL` option is removed. There is no outage if Oracle Java Virtual Machine (JVM) is already installed on the DB instance. After you remove the `SPATIAL` option, you don't need to restart your DB instance. **To drop the `SPATIAL` option** 1. Back up your data. **Warning** If the instance uses data types that were enabled as part of the option, and if you remove the `SPATIAL` option, you can lose data. For more information, see [Backing up, restoring, and exporting data](CHAP_CommonTasks.BackupRestore.md). 1. Check whether any existing objects reference data types or features of the `SPATIAL` option. If `SPATIAL` options exist, the instance can get stuck when applying the new option group that doesn't have the `SPATIAL` option. You can identify the objects by using the following queries: ``` SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME, BYTES/1024/1024 mbytes FROM DBA_SEGMENTS WHERE SEGMENT_TYPE LIKE '%TABLE%' AND (OWNER, SEGMENT_NAME) IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_TAB_COLUMNS WHERE DATA_TYPE='SDO_GEOMETRY' AND OWNER <> 'MDSYS') ORDER BY 1,2,3,4; SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE DATA_TYPE = 'SDO_GEOMETRY' AND OWNER <> 'MDSYS' ORDER BY 1,2,3; ``` 1. Drop any objects that reference data types or features of the `SPATIAL` option. 1. Do one of the following: + Remove the `SPATIAL` option from the option group it belongs to. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + Modify the DB instance and specify a different option group that doesn't include the `SPATIAL` option. This change affects a single DB instance. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Oracle SQLT Amazon RDS supports Oracle SQLTXPLAIN (SQLT) through the use of the SQLT option. You can use SQLT with any edition of Oracle Database 19c and higher. The Oracle `EXPLAIN PLAN` statement can determine the execution plan of a SQL statement. It can verify whether the Oracle optimizer chooses a certain execution plan, such as a nested loops join. It also helps you understand the optimizer's decisions, such as why it chose a nested loops join over a hash join. So `EXPLAIN PLAN` helps you understand the statement's performance. SQLT is an Oracle utility that produces a report. The report includes object statistics, object metadata, optimizer-related initialization parameters, and other information that a database administrator can use to tune a SQL statement for optimal performance. SQLT produces an HTML report with hyperlinks to all of the sections in the report. Unlike Automatic Workload Repository or Statspack reports, SQLT works on individual SQL statements. SQLT is a collection of SQL, PL/SQL, and SQL\$1Plus files that collect, store, and display performance data. Following are the supported Oracle versions for each SQLT version. **** | SQLT version | Oracle Database 21c | Oracle Database 19c | | --- | --- | --- | | 2018-07-25.v1 | Supported | Supported | | 2018-03-31.v1 | Not supported | Not supported | | 2016-04-29.v1 | Not supported | Not supported | To download SQLT and access instructions for using it: + Log in to your My Oracle Support account, and open the following documents: + To download SQLT: [Document 215187.1](https://support.oracle.com/epmos/faces/DocumentDisplay?id=215187.1) + For SQLT usage instructions: [Document 1614107.1](https://support.oracle.com/epmos/faces/DocumentDisplay?id=1614107.1) + For frequently asked questions about SQLT: [Document 1454160.1](https://support.oracle.com/epmos/faces/DocumentDisplay?id=1454160.1) + For information about reading SQLT output: [Document 1456176.1](https://support.oracle.com/epmos/main/downloadattachmentprocessor?parent=DOCUMENT&sourceId=1456176.1&attachid=1456176.1:58&clickstream=yes) + For interpreting the Main report: [Document 1922234.1](https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=215187.1&id=1922234.1) Amazon RDS doesn't support the following SQLT methods: + `XPLORE` + `XHUME` ## Prerequisites for SQLT The following are prerequisites for using SQLT: + You must remove users and roles that are required by SQLT, if they exist. The SQLT option creates the following users and roles on a DB instance: + `SQLTXPLAIN` user + `SQLTXADMIN` user + `SQLT_USER_ROLE` role If your DB instance has any of these users or roles, log in to the DB instance using a SQL client, and drop them using the following statements: ``` DROP USER SQLTXPLAIN CASCADE; DROP USER SQLTXADMIN CASCADE; DROP ROLE SQLT_USER_ROLE CASCADE; ``` + You must remove tablespaces that are required by SQLT, if they exist. The SQLT option creates the following tablespaces on a DB instance: + `RDS_SQLT_TS` + `RDS_TEMP_SQLT_TS` If your DB instance has these tablespaces, log in to the DB instance using a SQL client, and drop them. ## SQLT option settings SQLT can work with licensed features that are provided by the Oracle Tuning Pack and the Oracle Diagnostics Pack. The Oracle Tuning Pack includes the SQL Tuning Advisor, and the Oracle Diagnostics Pack includes the Automatic Workload Repository. The SQLT settings enable or disable access to these features from SQLT. Amazon RDS supports the following settings for the SQLT option. **** | Option setting | Valid values | Default value | Description | | --- | --- | --- | --- | | `LICENSE_PACK` | `T`, `D`, `N` | `N` | The Oracle Management Packs that you want to access with SQLT. Enter one of the following values: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Options.SQLT.html) Amazon RDS does not provide licenses for these Oracle Management Packs. If you indicate that you want to use a pack that is not included in your DB instance, you can use SQLT with the DB instance. However, SQLT can't access the pack, and the SQLT report doesn't include the data for the pack. For example, if you specify `T`, but the DB instance doesn't include the Oracle Tuning Pack, SQLT works on the DB instance, but the report it generates doesn't contain data related to the Oracle Tuning Pack. | | `VERSION` | `2016-04-29.v1` `2018-03-31.v1` `2018-07-25.v1` | `2016-04-29.v1` | The version of SQLT that you want to install. For Oracle Database 19c and 21c, the only supported version is `2018-07-25.v1`. This version is the default for these releases. | ## Adding the SQLT option The following is the general process for adding the SQLT option to a DB instance: 1. Create a new option group, or copy or modify an existing option group. 1. Add the SQLT option to the option group. 1. Associate the option group with the DB instance. After you add the SQLT option, as soon as the option group is active, SQLT is active. **To add the SQLT option to a DB instance** 1. Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine**, choose the Oracle edition that you want to use. The SQLT option is supported on all editions. 1. For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **SQLT** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Apply the option group to a new or existing DB instance: + For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). 1. (Optional) Verify the SQLT installation on each DB instance with the SQLT option. 1. Use a SQL client to connect to the DB instance as the master user. For information about connecting to an Oracle DB instance using a SQL client, see [Connecting to your Oracle DB instance](USER_ConnectToOracleInstance.md). 1. Run the following query: ``` SELECT sqltxplain.sqlt$a.get_param('tool_version') sqlt_version FROM DUAL; ``` The query returns the current version of the SQLT option on Amazon RDS. `12.1.160429` is an example of a version of SQLT that is available on Amazon RDS. 1. Change the passwords of the users that are created by the SQLT option. 1. Use a SQL client to connect to the DB instance as the master user. 1. Run the following SQL statement to change the password for the `SQLTXADMIN` user: ``` ALTER USER SQLTXADMIN IDENTIFIED BY new_password ACCOUNT UNLOCK; ``` **Note** Specify a password other than the prompt shown here as a security best practice. 1. Run the following SQL statement to change the password for the `SQLTXPLAIN` user: ``` ALTER USER SQLTXPLAIN IDENTIFIED BY new_password ACCOUNT UNLOCK; ``` **Note** Specify a password other than the prompt shown here as a security best practice. **Note** Upgrading SQLT requires uninstalling an older version of SQLT and then installing the new version. So, all SQLT metadata can be lost when you upgrade SQLT. A major version upgrade of a database also uninstalls and re-installs SQLT. An example of a major version upgrade is an upgrade from Oracle Database 19c to Oracle Database 21c. ## Using SQLT SQLT works with the Oracle SQL\$1Plus utility. **To use SQLT** 1. Download the SQLT .zip file from [Document 215187.1](https://support.oracle.com/epmos/faces/DocumentDisplay?id=215187.1) on the My Oracle Support site. **Note** You can't download SQLT 12.1.160429 from the My Oracle Support site. Oracle has deprecated this older version. 1. Unzip the SQLT .zip file. 1. From a command prompt, change to the `sqlt/run` directory on your file system. 1. From the command prompt, open SQL\$1Plus, and connect to the DB instance as the master user. For information about connecting to a DB instance using SQL\$1Plus, see [Connecting to your Oracle DB instance](USER_ConnectToOracleInstance.md). 1. Get the SQL ID of a SQL statement: ``` SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT='sql_statement'; ``` Your output is similar to the following: ``` SQL_ID ------------- chvsmttqjzjkn ``` 1. Analyze a SQL statement with SQLT: ``` START sqltxtract.sql sql_id sqltxplain_user_password ``` For example, for the SQL ID `chvsmttqjzjkn`, enter the following: ``` START sqltxtract.sql chvsmttqjzjkn sqltxplain_user_password ``` SQLT generates the HTML report and related resources as a .zip file in the directory from which the SQLT command was run. 1. (Optional) To enable application users to diagnose SQL statements with SQLT, grant `SQLT_USER_ROLE` to each application user with the following statement: ``` GRANT SQLT_USER_ROLE TO application_user_name; ``` **Note** Oracle does not recommend running SQLT with the `SYS` user or with users that have the `DBA` role. It is a best practice to run SQLT diagnostics using the application user's account, by granting `SQLT_USER_ROLE` to the application user. ## Upgrading the SQLT option With Amazon RDS for Oracle, you can upgrade the SQLT option from your existing version to a higher version. To upgrade the SQLT option, complete steps 1–3 in [Using SQLT](#Oracle.Options.SQLT.Using) for the new version of SQLT. Also, if you granted privileges for the previous version of SQLT in step 7 of that section, grant the privileges again for the new SQLT version. Upgrading the SQLT option results in the loss of the older SQLT version's metadata. The older SQLT version's schema and related objects are dropped, and the newer version of SQLT is installed. For more information about the changes in the latest SQLT version, see [Document 1614201.1](https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=215187.1&id=1614201.1) on the My Oracle Support site. **Note** Version downgrades are not supported. ## Modifying SQLT settings After you enable SQLT, you can modify the `LICENSE_PACK` and `VERSION` settings for the option. For more information about how to modify option settings, see [Modifying an option setting](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.ModifyOption). For more information about each setting, see [SQLT option settings](#Oracle.Options.SQLT.Options). ## Removing the SQLT option You can remove SQLT from a DB instance. To remove SQLT from a DB instance, do one of the following: + To remove SQLT from multiple DB instances, remove the SQLT option from the option group to which the DB instances belong. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + To remove SQLT from a single DB instance, modify the DB instance and specify a different option group that doesn't include the SQLT option. You can specify the default (empty) option group or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Oracle Statspack The Oracle Statspack option installs and enables the Oracle Statspack performance statistics feature. Oracle Statspack is a collection of SQL, PL/SQL, and SQL\$1Plus scripts that collect, store, and display performance data. For information about using Oracle Statspack, see [Oracle Statspack](http://docs.oracle.com/cd/E13160_01/wli/docs10gr3/dbtuning/statsApdx.html) in the Oracle documentation. **Note** Oracle Statspack is no longer supported by Oracle and has been replaced by the more advanced Automatic Workload Repository (AWR). AWR is available only for Oracle Enterprise Edition customers who have purchased the Diagnostics Pack. You can use Oracle Statspack with any Oracle DB engine on Amazon RDS. You can't run Oracle Statspack on Amazon RDS read replicas. ## Setting up Oracle Statspack To run Statspack scripts, you must add the Statspack option. **To set up Oracle Statspack** 1. In a SQL client, log in to the Oracle DB with an administrative account. 1. Do either of the following actions, depending on whether Statspack is installed: + If Statspack is installed, and the `PERFSTAT` account is associated with Statspack, skip to Step 4. + If Statspack is not installed, and the `PERFSTAT` account exists, drop the account as follows: ``` DROP USER PERFSTAT CASCADE; ``` Otherwise, attempting to add the Statspack option generates an error and `RDS-Event-0058`. 1. Add the Statspack option to an option group. See [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). Amazon RDS automatically installs the Statspack scripts on the DB instance and then sets up the `PERFSTAT` account. 1. Reset the password using the following SQL statement, replacing *pwd* with your new password: ``` ALTER USER PERFSTAT IDENTIFIED BY pwd ACCOUNT UNLOCK; ``` You can log in using the `PERFSTAT` user account and run the Statspack scripts. 1. Grant the `CREATE JOB` privilege to the `PERFSTAT` account using the following statement: ``` GRANT CREATE JOB TO PERFSTAT; ``` 1. Ensure that idle wait events in the `PERFSTAT.STATS$IDLE_EVENT` table are populated. Because of Oracle Bug 28523746, the idle wait events in `PERFSTAT.STATS$IDLE_EVENT` may not be populated. To ensure all idle events are available, run the following statement: ``` INSERT INTO PERFSTAT.STATS$IDLE_EVENT (EVENT) SELECT NAME FROM V$EVENT_NAME WHERE WAIT_CLASS='Idle' MINUS SELECT EVENT FROM PERFSTAT.STATS$IDLE_EVENT; COMMIT; ``` ## Generating Statspack reports A Statspack report compares two snapshots. **To generate Statspack reports** 1. In a SQL client, log in to the Oracle DB with the `PERFSTAT` account. 1. Create a snapshot using either of the following techniques: + Create a Statspack snapshot manually. + Create a job that takes a Statspack snapshot after a given time interval. For example, the following job creates a Statspack snapshot every hour: ``` VARIABLE jn NUMBER; exec dbms_job.submit(:jn, 'statspack.snap;',SYSDATE,'TRUNC(SYSDATE+1/24,''HH24'')'); COMMIT; ``` 1. View the snapshots using the following query: ``` SELECT SNAP_ID, SNAP_TIME FROM STATS$SNAPSHOT ORDER BY 1; ``` 1. Run the Amazon RDS procedure `rdsadmin.rds_run_spreport`, replacing *begin\$1snap* and *end\$1snap* with the snapshot IDs: ``` exec rdsadmin.rds_run_spreport(begin_snap,end_snap); ``` For example, the following command creates a report based on the interval between Statspack snapshots 1 and 2: ``` exec rdsadmin.rds_run_spreport(1,2); ``` The file name of the Statspack report includes the number of the two snapshots. For example, a report file created using Statspack snapshots 1 and 2 would be named `ORCL_spreport_1_2.lst`. 1. Monitor the output for errors. Oracle Statspack performs checks before running the report. Therefore, you could also see error messages in the command output. For example, you might try to generate a report based on an invalid range, where the beginning Statspack snapshot value is larger than the ending value. In this case, the output shows the error message, but the DB engine does not generate an error file. ``` exec rdsadmin.rds_run_spreport(2,1); * ERROR at line 1: ORA-20000: Invalid snapshot IDs. Find valid ones in perfstat.stats$snapshot. ``` If you use an invalid number a Statspack snapshot, the output shows an error. For example, if you try to generate a report for snapshots 1 and 50, but snapshot 50 doesn't exist, the output shows an error. ``` exec rdsadmin.rds_run_spreport(1,50); * ERROR at line 1: ORA-20000: Could not find both snapshot IDs ``` 1. (Optional) To retrieve the report, call the trace file procedures, as explained in [Working with Oracle trace files](USER_LogAccess.Concepts.Oracle.md#USER_LogAccess.Concepts.Oracle.WorkingWithTracefiles). Alternatively, download the Statspack report from the RDS console. Go to the **Log** section of the DB instance details and choose **Download**. The following example shows `trace/ORCL_spreport_1_2.lst` ![\[Show a list of Oracle log files in the RDS console. The following trace file is circled: trace/ORCL_spreport_1_2.lst.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/statspack1.png) If an error occurs while generating a report, the DB engine uses the same naming conventions as for a report but with an extension of `.err`. For example, if an error occurred while creating a report using Statspack snapshots 1 and 7, the report file would be named `ORCL_spreport_1_7.err`. You can download the error report using the same techniques as for a standard Snapshot report. ## Removing Statspack snapshots To remove a range of Oracle Statspack snapshots, use the following command: ``` exec statspack.purge(begin snap, end snap); ``` # Oracle time zone To change the system time zone used by your Oracle DB instance, use the time zone option. For example, you might change the time zone of a DB instance to be compatible with an on-premises environment, or a legacy application. The time zone option changes the time zone at the host level. Changing the time zone impacts all date columns and values, including `SYSDATE` and `SYSTIMESTAMP`. The time zone option differs from the `rdsadmin_util.alter_db_time_zone` command. The `alter_db_time_zone` command changes the time zone only for certain data types. The time zone option changes the time zone for all date columns and values. For more information about `alter_db_time_zone`, see [Setting the database time zone](Appendix.Oracle.CommonDBATasks.TimeZoneSupport.md). For more information about upgrade considerations, see [Time zone considerations](USER_UpgradeDBInstance.Oracle.OGPG.md#USER_UpgradeDBInstance.Oracle.OGPG.DST). ## Restrictions for setting the time zone The time zone option is a permanent and persistent option. Therefore, you can't do the following: + Remove the option from an option group after you add the time zone option. + Remove the option group from a DB instance after you add the group. + Modify the time zone setting of the option to a different time zone. ## Recommendations for setting the time zone Before you add the time zone option to your production database, we strongly recommend that you do the following: + Take a snapshot of your DB instance. If you accidentally set the time zone incorrectly, you must recover your DB instance to its previous time zone setting. For more information, see [Creating a DB snapshot for a Single-AZ DB instance for Amazon RDS](USER_CreateSnapshot.md). + Add the time zone option to a test DB instance. Adding the time zone option can cause problems with tables that use the system date to add dates or times. We recommend that you analyze your data and applications on the test instance. This way you can assess the impact of changing the time zone on your production instance. If your DB instance uses the default option group, then follow these steps: 1. Take a snapshot of your DB instance. 1. Add the time zone option to your DB instance. If your DB instance currently uses a nondefault option group, then follow these steps: 1. Take a snapshot of your DB instance. 1. Create a new option group. 1. Add the time zone option to it, along with all other options that are currently associated with the existing option group. This prevents the existing options from being uninstalled while enabling the time zone option. 1. Add the option group to your DB instance. ## Time zone option settings Amazon RDS supports the following settings for the time zone option. **** | Option setting | Valid values | Description | | --- | --- | --- | | `TIME_ZONE` | One of the available time zones. For the full list, see [Available time zones](#Appendix.Oracle.Options.Timezone.Zones). | The new time zone for your DB instance. | ## Adding the time zone option Complete the following steps to add the time zone option to your DB instance: 1. (Recommended) Take a snapshot of your DB instance. 1. Do one of the following tasks: + Create a new option group from scratch. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). + Copy an existing option group using the AWS CLI or API. For more information, see [Copying an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Copy). + Reuse an existing non-default option group. A best practice is to use an option group that isn't currently associated with any DB instances or snapshots. 1. Add the new option to the option group from the preceding step. 1. If the option group that is currently associated with your DB instance has options enabled, add these options to your new option group. This strategy prevents the existing options from being uninstalled while enabling the new option. 1. Add the new option group to your DB instance. When you add the time zone option, a brief outage occurs while your DB instance is automatically restarted. ### Console **To add the time zone option to an option group and associate it with a DB instance** 1. In the RDS console, choose **Option groups.** 1. Choose the name of the option group to which you want to add the option. 1. Choose **Add option**. 1. For **Option name**, choose **Timezone**, and then configure the option settings. 1. Associate the option group with a new or existing DB instance: + For a new DB instance, apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, apply the option group by modifying the instance and attaching the new option group. When you add the new option to an existing DB instance, a brief outage occurs while your DB instance is automatically restarted. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ### AWS CLI The following example uses the AWS CLI [add-option-to-option-group](https://docs.aws.amazon.com/cli/latest/reference/rds/add-option-to-option-group.html) command to add the `Timezone` option and the `TIME_ZONE` option setting to an option group called `myoptiongroup`. The time zone is set to `Africa/Cairo`. For Linux, macOS, or Unix: ``` aws rds add-option-to-option-group \ --option-group-name "myoptiongroup" \ --options "OptionName=Timezone,OptionSettings=[{Name=TIME_ZONE,Value=Africa/Cairo}]" \ --apply-immediately ``` For Windows: ``` aws rds add-option-to-option-group ^ --option-group-name "myoptiongroup" ^ --options "OptionName=Timezone,OptionSettings=[{Name=TIME_ZONE,Value=Africa/Cairo}]" ^ --apply-immediately ``` ## Modifying time zone settings The time zone option is a permanent and persistent option. You can't remove the option from an option group after you add it. You can't remove the option group from a DB instance after you add it. You can't modify the time zone setting of the option to a different time zone. If you set the time zone incorrectly, restore a snapshot of your DB instance from before you added the time zone option. ## Removing the time zone option The time zone option is a permanent and persistent option. You can't remove the option from an option group after you add it. You can't remove the option group from a DB instance after you add it. To remove the time zone option, restore a snapshot of your DB instance from before you added the time zone option. ## Available time zones You can use the following values for the time zone option. **** | Zone | Time zone | | --- | --- | | Africa | Africa/Cairo, Africa/Casablanca, Africa/Harare, Africa/Lagos, Africa/Luanda, Africa/Monrovia, Africa/Nairobi, Africa/Tripoli, Africa/Windhoek | | America | America/Araguaina, America/Argentina/Buenos\$1Aires, America/Asuncion, America/Bogota, America/Caracas, America/Chicago, America/Chihuahua, America/Cuiaba, America/Denver, America/Detroit, America/Fortaleza, America/Godthab, America/Guatemala, America/Halifax, America/Lima, America/Los\$1Angeles, America/Manaus, America/Matamoros, America/Mexico\$1City, America/Monterrey, America/Montevideo, America/New\$1York, America/Phoenix, America/Santiago, America/Sao\$1Paulo, America/Tijuana, America/Toronto | | Asia | Asia/Amman, Asia/Ashgabat, Asia/Baghdad, Asia/Baku, Asia/Bangkok, Asia/Beirut, Asia/Calcutta, Asia/Damascus, Asia/Dhaka, Asia/Hong\$1Kong, Asia/Irkutsk, Asia/Jakarta, Asia/Jerusalem, Asia/Kabul, Asia/Karachi, Asia/Kathmandu, Asia/Kolkata, Asia/Krasnoyarsk, Asia/Magadan, Asia/Manila, Asia/Muscat, Asia/Novosibirsk, Asia/Rangoon, Asia/Riyadh, Asia/Seoul, Asia/Shanghai, Asia/Singapore, Asia/Taipei, Asia/Tehran, Asia/Tokyo, Asia/Ulaanbaatar, Asia/Vladivostok, Asia/Yakutsk, Asia/Yerevan | | Atlantic | Atlantic/Azores, Atlantic/Cape\$1Verde | | Australia | Australia/Adelaide, Australia/Brisbane, Australia/Darwin, Australia/Eucla, Australia/Hobart, Australia/Lord\$1Howe, Australia/Perth, Australia/Sydney | | Brazil | Brazil/DeNoronha, Brazil/East | | Canada | Canada/Newfoundland, Canada/Saskatchewan | | Etc | Etc/GMT-3 | | Europe | Europe/Amsterdam, Europe/Athens, Europe/Berlin, Europe/Dublin, Europe/Helsinki, Europe/Kaliningrad, Europe/London, Europe/Madrid, Europe/Moscow, Europe/Paris, Europe/Prague, Europe/Rome, Europe/Sarajevo | | Pacific | Pacific/Apia, Pacific/Auckland, Pacific/Chatham, Pacific/Fiji, Pacific/Guam, Pacific/Honolulu, Pacific/Kiritimati, Pacific/Marquesas, Pacific/Samoa, Pacific/Tongatapu, Pacific/Wake | | US | US/Alaska, US/Central, US/East-Indiana, US/Eastern, US/Pacific | | UTC | UTC | # Oracle time zone file autoupgrade With the `TIMEZONE_FILE_AUTOUPGRADE` option, you can upgrade the current time zone file to the latest version on your RDS for Oracle DB instance. **Topics** + [Overview of Oracle time zone files](Appendix.Oracle.Options.Timezone-file-autoupgrade.tz-overview.md) + [Strategies for updating your time zone file](Appendix.Oracle.Options.Timezone-file-autoupgrade.strategies.md) + [Downtime during the time zone file update](Appendix.Oracle.Options.Timezone-file-autoupgrade.considerations.md) + [Preparing to update the time zone file](Appendix.Oracle.Options.Timezone-file-autoupgrade.preparing.md) + [Adding the time zone file autoupgrade option](Appendix.Oracle.Options.Timezone-file-autoupgrade.adding.md) + [Checking your data after the update of the time zone file](Appendix.Oracle.Options.Timezone-file-autoupgrade.checking.md) # Overview of Oracle time zone files An Oracle Database *time zone file* stores the following information: + Offset from Coordinated Universal Time (UTC) + Transition times for Daylight Saving Time (DST) + Abbreviations for standard time and DST Oracle Database supplies multiple versions of time zone files. When you create an Oracle database in an on-premises environment, you choose the time zone file version. For more information , see [Choosing a Time Zone File](https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html#GUID-805AB986-DE12-4FEA-AF56-5AABCD2132DF) in the *Oracle Database Globalization Support Guide*. If the rules change for DST, Oracle publishes new time zone files. Oracle releases these new time zone files independently of the schedule for quarterly Release Updates (RUs) and Release Update Revisions (RURs). The time zone files reside on the database host in the directory `$ORACLE_HOME/oracore/zoneinfo/`. The time zone file names use the format DSTv*version*, as in DSTv35. ## How the time zone file affects data transfer In Oracle Database, the `TIMESTAMP WITH TIME ZONE` data type stores time stamp and time zone data. Data with the `TIMESTAMP WITH TIME ZONE` data type uses the rules in the associated time zone file version. Thus, existing `TIMESTAMP WITH TIME ZONE` data is affected when you update the time zone file. Problems can occur when you transfer data between databases that use different versions of the time zone file. For example, if you import data from a source database with a higher time zone file version than the target database, the database issues the `ORA-39405` error. Previously, you had to work around this error by using either of the following techniques: + Create an RDS for Oracle DB instance with the desired time zone file, export data from your source database, and then import it into the new database. + Use AWS DMS or logical replication to migrate your data. ## Automatic updates using the TIMEZONE\$1FILE\$1AUTOUPGRADE option When the option group attached to your RDS for Oracle DB instance includes the `TIMEZONE_FILE_AUTOUPGRADE` option, RDS updates your time zone files automatically. By ensuring that your Oracle databases use the same time zone file version, you avoid time-consuming manual techniques when you move data between different environments. The `TIMEZONE_FILE_AUTOUPGRADE` option is supported for both container databases (CDBs) and non-CDBs. When you add the `TIMEZONE_FILE_AUTOUPGRADE` option to your option group, you can choose whether to add the option immediately or during the maintenance window. After your DB instance applies the new option, RDS checks whether it can install a newer DSTv*version* file. The target DSTv*version* depends on the following: + The minor engine version that your DB instance is currently running + The minor engine version to which you want to upgrade your DB instance For example, your current time zone file version might be DSTv33. When RDS applies the update to your option group, it might determine that DSTv34 is currently available on your DB instance file system. RDS will then update your time zone file to DSTv34 automatically. To find the available DST versions in the supported RDS release updates, look at the patches in [Release notes for Amazon Relational Database Service (Amazon RDS) for Oracle](https://docs.aws.amazon.com/AmazonRDS/latest/OracleReleaseNotes/Welcome.html). For example, [version 19.0.0.0.ru-2022-10.rur-2022-10.r1](https://docs.aws.amazon.com/AmazonRDS/latest/OracleReleaseNotes/oracle-version-19-0.html#oracle-version-RU-RUR.19.0.0.0.ru-2022-10.rur-2022-10.r1) lists patch 34533061: RDBMS - DSTV39 UPDATE - TZDATA2022C. # Strategies for updating your time zone file Upgrading your DB engine and adding the `TIMEZONE_FILE_AUTOUPGRADE` option to an option group are separate operations. Adding the `TIMEZONE_FILE_AUTOUPGRADE` option initiates the update of your time zone file if a more current one is available. You run the following commands (only relevant options are shown) either immediately or at the next maintenance window: + Upgrade your DB engine only using the following RDS CLI command: ``` modify-db-instance --engine-version name ... ``` + Add the `TIMEZONE_FILE_AUTOUPGRADE` option only using the following CLI command: ``` add-option-to-option-group --option-group-name name --options OptionName=TIMEZONE_FILE_AUTOUPGRADE ... ``` + Upgrade your DB engine and add a new option group to your instance using the following CLI command: ``` modify-db-instance --engine-version name --option-group-name name ... ``` Your update strategy depends on whether you want to upgrade your database and time zone file together or perform just one of these operations. Keep in mind that if you update your option group and then upgrade your DB engine in separate API operations, it's possible for a time zone file update to be currently in progress when you upgrade your DB engine. The examples in this section assume the following: + You have not yet added `TIMEZONE_FILE_AUTOUPGRADE` to the option group currently associated with your DB instance. + Your DB instance uses database version 19.0.0.0.ru-2019-07.rur-2019-07.r1 and time zone file DSTv33. + Your DB instance file system includes file DSTv34. + Release update 19.0.0.0.ru-2022-10.rur-2022-10.r1 includes DSTv35. To update your time zone file, you can use the following strategies. **Topics** + [Update the time zone file without upgrading the engine](#Appendix.Oracle.Options.Timezone-file-autoupgrade.strategies.no-upgrade) + [Upgrade the time zone file and DB engine version](#Appendix.Oracle.Options.Timezone-file-autoupgrade.strategies.upgrade) + [Upgrade your DB engine version without updating the time zone file](#Appendix.Oracle.Options.Timezone-file-autoupgrade.strategies.upgrade-only) ## Update the time zone file without upgrading the engine In this scenario, your database is using DSTv33, but DSTv34 is available on your DB instance file system. You want to update the time zone file used by your DB instance from DSTv33 to DSTv34, but you don't want to upgrade your engine to a new minor version, which includes DSTv35. In an `add-option-to-option-group` command, add `TIMEZONE_FILE_AUTOUPGRADE` to the option group used by your DB instance. Specify whether to add the option immediately or defer it to the maintenance window. After applying the `TIMEZONE_FILE_AUTOUPGRADE` option, RDS does the following: 1. Checks for a new DST version. 1. Determines that DSTv34 is available on the file system. 1. Updates the time zone file immediately. ## Upgrade the time zone file and DB engine version In this scenario, your database is using DSTv33, but DSTv34 is available on your DB instance file system. You want to upgrade your DB engine to minor version 19.0.0.0.ru-2022-10.rur-2022-10.r1, which includes DSTv35, and update your time zone file to DSTv35 during the engine upgrade. Thus, your goal is to skip DSTv34 and update your time zone files directly to DSTv35. To upgrade the engine and time zone file together, run `modify-db-instance` with the `--option-group-name` and `--engine-version` options. You can run the command immediately or defer it to maintenance window. `In --option-group-name`, specify an option group that includes the `TIMEZONE_FILE_AUTOUPGRADE` option. For example: ``` aws rds modify-db-instance --db-instance-identifier my-instance \ --engine-version new-version \ ----option-group-name og-with-timezone-file-autoupgrade \ --apply-immediately ``` RDS begins upgrading your engine to 19.0.0.0.ru-2022-10.rur-2022-10.r1. After applying the `TIMEZONE_FILE_AUTOUPGRADE` option, RDS checks for a new DST version, sees that DSTv35 is available in 19.0.0.0.ru-2022-10.rur-2022-10.r1, and immediately starts the update to DSTv35. To upgrade your engine immediately and then upgrade your a timezone file, perform the operations in sequence: 1. Upgrade your DB engine only using the following CLI command: ``` aws rds modify-db-instance \ --db-instance-identifier my-instance \ --engine-version new-version \ --apply-immediately ``` 1. Add the `TIMEZONE_FILE_AUTOUPGRADE` option to the option group attached to your instance using the following CLI command: ``` aws rds add-option-to-option-group \ --option-group-name og-in-use-by-your-instance \ --options OptionName=TIMEZONE_FILE_AUTOUPGRADE \ --apply-immediately ``` ## Upgrade your DB engine version without updating the time zone file In this scenario, your database is using DSTv33, but DSTv34 is available on your DB instance file system. You want to upgrade your DB engine to version 19.0.0.0.ru-2022-10.rur-2022-10.r1, which includes DSTv35, but retain time zone file DSTv33. You might choose this strategy for the following reasons: + Your data doesn't use the `TIMESTAMP WITH TIME ZONE` data type. + Your data uses the `TIMESTAMP WITH TIME ZONE` data type, but your data is not affected by the time zone changes. + You want to postpone updating the time zone file because you can't tolerate the extra downtime. Your strategy depends on which of the following possibilities are true: + Your DB instance isn't associated with an option group that includes `TIMEZONE_FILE_AUTOUPGRADE`. In your `modify-db-instance` command, don't specify a new option group so that RDS doesn't update your time zone file. + Your DB instance is currently associated with an option group that includes `TIMEZONE_FILE_AUTOUPGRADE`. Within a single `modify-db-instance` command, associate your DB instance with an option group that doesn't include `TIMEZONE_FILE_AUTOUPGRADE` and upgrade your DB engine to 19.0.0.0.ru-2022-10.rur-2022-10.r1. # Downtime during the time zone file update When RDS updates your time zone file, existing data that uses `TIMESTAMP WITH TIME ZONE` might change. In this case, your primary consideration is downtime. **Warning** If you add the `TIMEZONE_FILE_AUTOUPGRADE` option, your engine upgrade might have prolonged downtime. Updating time zone data for a large database might take hours or even days. The length of the time zone file update depends on factors such as the following: + The amount of `TIMESTAMP WITH TIME ZONE` data in your database + The DB instance configuration + The DB instance class + The storage configuration + The database configuration + The database parameter settings Additional downtime can occur when you do the following: + Add the option to the option group when the DB instance uses an outdated time zone file + Upgrade the Oracle database engine when the new engine version contains a new version of the time zone file **Note** During the time zone file update, RDS for Oracle calls `PURGE DBA_RECYCLEBIN`. # Preparing to update the time zone file A time zone file upgrade has two separate phases: prepare and upgrade. While not required, we strongly recommend that you perform the prepare step. In this step, you find out which data will be affected by running the PL/SQL procedure `DBMS_DST.FIND_AFFECTED_TABLES`. For more information about the prepare window, see [Upgrading the Time Zone File and Timestamp with Time Zone Data](https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html#GUID-B0ACDB2E-4B49-4EB4-B4CC-9260DAE1567A) in the Oracle Database documentation. **To prepare to update the time zone file** 1. Connect to your Oracle database using a SQL client. 1. Determine the current timezone file version used. ``` SELECT * FROM V$TIMEZONE_FILE; ``` 1. Determine the latest timezone file version available on your DB instance. ``` SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION FROM DUAL; ``` 1. Determine the total size of tables that have columns of type `TIMESTAMP WITH LOCAL TIME ZONE` or `TIMESTAMP WITH TIME ZONE`. ``` SELECT SUM(BYTES)/1024/1024/1024 "Total_size_w_TSTZ_columns_GB" FROM DBA_SEGMENTS WHERE SEGMENT_TYPE LIKE 'TABLE%' AND (OWNER, SEGMENT_NAME) IN (SELECT OWNER, TABLE_NAME FROM DBA_TAB_COLUMNS WHERE DATA_TYPE LIKE 'TIMESTAMP%TIME ZONE'); ``` 1. Determine the names and sizes of segments that have columns of type `TIMESTAMP WITH LOCAL TIME ZONE` or `TIMESTAMP WITH TIME ZONE`. ``` SELECT OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024/1024 "SEGMENT_SIZE_W_TSTZ_COLUMNS_GB" FROM DBA_SEGMENTS WHERE SEGMENT_TYPE LIKE 'TABLE%' AND (OWNER, SEGMENT_NAME) IN (SELECT OWNER, TABLE_NAME FROM DBA_TAB_COLUMNS WHERE DATA_TYPE LIKE 'TIMESTAMP%TIME ZONE') GROUP BY OWNER, SEGMENT_NAME; ``` 1. Run the prepare step. + The procedure `DBMS_DST.CREATE_AFFECTED_TABLE` creates a table to store any affected data. You pass the name of this table to the `DBMS_DST.FIND_AFFECTED_TABLES` procedure. For more information, see [CREATE\$1AFFECTED\$1TABLE Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_DST.html#GUID-C53BAABA-914A-404C-9CD5-823257BE0B00) in the Oracle Database documentation. + This procedure `CREATE_ERROR_TABLE` creates a table to log errors. For more information, see [CREATE\$1ERROR\$1TABLE Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_DST.html#GUID-6A7EA024-B02D-4486-B1D6-EF6ABF5DE507) in the Oracle Database documentation. The following example creates the affected data and error tables, and finds all affected tables. ``` EXEC DBMS_DST.CREATE_ERROR_TABLE('my_error_table') EXEC DBMS_DST.CREATE_AFFECTED_TABLE('my_affected_table') EXEC DBMS_DST.BEGIN_PREPARE(new_version); EXEC DBMS_DST.FIND_AFFECTED_TABLES('my_affected_table', TRUE, 'my_error_table'); EXEC DBMS_DST.END_PREPARE; SELECT * FROM my_affected_table; SELECT * FROM my_error_table; ``` 1. Query the affected and error tables. ``` SELECT * FROM my_affected_table; SELECT * FROM my_error_table; ``` # Adding the time zone file autoupgrade option When you add the option to an option group, the option group is in one of the following states: + An existing option group is currently attached to at least one DB instance. When you add the option, all DB instances that use this option group automatically restart. This causes a brief outage. + An existing option group is not attached to any DB instance. You plan to add the option and then associate the existing option group with existing DB instances or with a new DB instance. + You create a new option group and add the option. You plan to associate the new option group with existing DB instances or with a new DB instance. ## Console **To add the time zone file autoupgrade option to a DB instance** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Option groups**. 1. Determine the option group you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine** choose the Oracle Database edition for your DB instance. 1. For **Major engine version** choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Choose the option group that you want to modify, and then choose **Add option**. 1. In the **Add option** window, do the following: 1. Choose **TIMEZONE\$1FILE\$1AUTOUPGRADE**. 1. To enable the option on all associated DB instances as soon as you add it, for **Apply Immediately**, choose **Yes**. If you choose **No** (the default), the option is enabled for each associated DB instance during its next maintenance window. 1. When the settings are as you want them, choose **Add option**. ## AWS CLI The following example uses the AWS CLI [add-option-to-option-group](https://docs.aws.amazon.com/cli/latest/reference/rds/add-option-to-option-group.html) command to add the `TIMEZONE_FILE_AUTOUPGRADE` option to an option group called `myoptiongroup`. For Linux, macOS, or Unix: ``` aws rds add-option-to-option-group \ --option-group-name "myoptiongroup" \ --options "OptionName=TIMEZONE_FILE_AUTOUPGRADE" \ --apply-immediately ``` For Windows: ``` aws rds add-option-to-option-group ^ --option-group-name "myoptiongroup" ^ --options "OptionName=TIMEZONE_FILE_AUTOUPGRADE" ^ --apply-immediately ``` # Checking your data after the update of the time zone file We recommend that you check your data after you update the time zone file. During the prepare step, RDS for Oracle automatically creates the following tables: + `rdsadmin.rds_dst_affected_tables` – Lists the tables that contain data affected by the update + `rdsadmin.rds_dst_error_table` – Lists the errors generated during the update These tables are independent of any tables that you create in the prepare window. To see the results of the update, query the tables as follows. ``` SELECT * FROM rdsadmin.rds_dst_affected_tables; SELECT * FROM rdsadmin.rds_dst_error_table; ``` For more information about the schema for the affected data and error tables, see [FIND\$1AFFECTED\$1TABLES Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_DST.html#GUID-1F977505-671C-4D5B-8570-86956F136199) in the Oracle documentation. # Oracle Transparent Data Encryption Amazon RDS supports Oracle Transparent Data Encryption (TDE), a feature of the Oracle Advanced Security option available in Oracle Enterprise Edition. This feature automatically encrypts data before it is written to storage and automatically decrypts data when the data is read from storage. This option is only supported for the Bring Your Own License (BYOL) model. TDE is useful in scenarios where you need to encrypt sensitive data in case data files and backups are obtained by a third party. TDE is also useful when you need to comply with security-related regulations. A detailed explanation about TDE in Oracle Database is beyond the scope of this guide. For information, see the following Oracle Database resources: + [Introduction to Transparent Data Encryption](https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/introduction-to-transparent-data-encryption.html#GUID-62AA9447-FDCD-4A4C-B563-32DE04D55952) in the Oracle Database documentation + [Oracle advanced security](https://www.oracle.com/security/database-security/) in the Oracle Database documentation + [Oracle advanced security Transparent Data Encryption best practices](https://www.oracle.com/br/a/tech/docs/technical-resources/twp-transparent-data-encryption-bestpractices.pdf), which is an Oracle whitepaper For more information about using TDE with RDS for Oracle, see the following blogs: + [Oracle Database Encryption Options on Amazon RDS](https://aws.amazon.com/blogs/apn/oracle-database-encryption-options-on-amazon-rds/) + [Migrate a cross-account TDE-enabled Amazon RDS for Oracle DB instance with reduced downtime using AWS DMS](https://aws.amazon.com/blogs/database/migrate-a-cross-account-tde-enabled-amazon-rds-for-oracle-db-instance-with-reduced-downtime-using-aws-dms/) ## TDE encryption modes Oracle Transparent Data Encryption supports two encryption modes: TDE tablespace encryption and TDE column encryption. TDE tablespace encryption is used to encrypt entire application tables. TDE column encryption is used to encrypt individual data elements that contain sensitive data. You can also apply a hybrid encryption solution that uses both TDE tablespace and column encryption. **Note** Amazon RDS manages the Oracle Wallet and TDE master key for the DB instance. You do not need to set the encryption key using the command `ALTER SYSTEM set encryption key`. After you enable the `TDE` option, you can check the status of the Oracle Wallet by using the following command: ``` SELECT * FROM v$encryption_wallet; ``` To create an encrypted tablespace, use the following command: ``` CREATE TABLESPACE encrypt_ts ENCRYPTION DEFAULT STORAGE (ENCRYPT); ``` To specify the encryption algorithm, use the following command: ``` CREATE TABLESPACE encrypt_ts ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT); ``` The previous statements for encrypting a tablespace are the same as you would use on an on-premises Oracle database. ## Restrictions for the TDE option The TDE option is permanent and persistent. After you associate your DB instance with an option group that has the TDE option enabled, you can't do the following actions: + Disable the `TDE` option in the currently associated option group. + Associate your DB instance with a different option group that doesn't include the `TDE` option. + Share a DB snapshot that uses the `TDE` option. For more information about sharing DB snapshots, see [Sharing a DB snapshot for Amazon RDS](USER_ShareSnapshot.md). For more information about persistent and permanent options, see [Persistent and permanent options](USER_WorkingWithOptionGroups.md#Overview.OptionGroups.Permanent). ## Determining whether your DB instance is using TDE You might want to determine whether your DB instance is associated with an option group that has the `TDE` option enabled. To view the option group that a DB instance is associated with, use the RDS console, the [describe-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-instances.html) AWS CLI command, or the API operation [DescribeDBInstances](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBInstances.html). ## Adding the TDE option To add the `TDE` option to your DB instance, complete the following steps: 1. (Recommended) Take a snapshot of your DB instance. 1. Do one of the following tasks: + Create a new option group from scratch. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). + Copy an existing option group using the AWS CLI or API. For more information, see [Copying an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Copy). + Reuse an existing non-default option group. A best practice is to use an option group that isn't currently associated with any DB instances or snapshots. 1. Add the new option to the option group from the preceding step. 1. If the option group that is currently associated with your DB instance has options enabled, add these options to your new option group. This strategy prevents the existing options from being uninstalled while enabling the new option. 1. Add the new option group to your DB instance. ### Console **To add the TDE option to an option group and associate it with your DB instance** 1. In the RDS console, choose **Option groups.** 1. Choose the name of the option group to which you want to add the option. 1. Choose **Add option**. 1. For **Option name**, choose **TDE**, and then configure the option settings. 1. Choose **Add option**. **Important** If you add the **TDE** option to an option group that is currently attached to one or more DB instances, a brief outage occurs while all the DB instances are automatically restarted. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Associate the option group with a new or existing DB instance: + For a new DB instance, apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, apply the option group by modifying the instance and attaching the new option group. The DB instance doesn't restart as part of this operation. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ### AWS CLI In the following example, you use the AWS CLI [add-option-to-option-group](https://docs.aws.amazon.com/cli/latest/reference/rds/add-option-to-option-group.html) command to add the `TDE` option to an option group called `myoptiongroup`. For more information, see [Getting started: Flink 1.13.2 ](https://docs.aws.amazon.com/managed-flink/latest/java/earlier.html#getting-started-1-13). For Linux, macOS, or Unix: ``` aws rds add-option-to-option-group \ --option-group-name "myoptiongroup" \ --options "OptionName=TDE" \ --apply-immediately ``` For Windows: ``` aws rds add-option-to-option-group ^ --option-group-name "myoptiongroup" ^ --options "OptionName=TDE" ^ --apply-immediately ``` ## Copying your data to a DB instance that doesn't include the TDE option You can't remove the TDE option from a DB instance or associate it with an option group that doesn't include the TDE option. To migrate your data to an instance that doesn't include the TDE option, do the following: 1. Decrypt the data on your DB instance. 1. Copy the data to a new DB instance that is not associated with an option group that has `TDE` enabled. 1. Delete your original DB instance. You can use the same name for the new instance as the previous DB instance. ## Considerations when using TDE with Oracle Data Pump You can use Oracle Data Pump to import or export encrypted dump files. Amazon RDS supports the password encryption mode `(ENCRYPTION_MODE=PASSWORD)` for Oracle Data Pump. Amazon RDS does not support transparent encryption mode `(ENCRYPTION_MODE=TRANSPARENT)` for Oracle Data Pump. For more information, see [Importing using Oracle Data Pump](Oracle.Procedural.Importing.DataPump.md). # Oracle UTL\$1MAIL Amazon RDS supports Oracle UTL\$1MAIL through the use of the UTL\$1MAIL option and SMTP servers. You can send email directly from your database by using the UTL\$1MAIL package. Amazon RDS supports UTL\$1MAIL for the following versions of Oracle: + Oracle Database 21c (21.0.0.0), all versions + Oracle Database 19c (19.0.0.0), all versions The following are some limitations to using UTL\$1MAIL: + UTL\$1MAIL does not support Transport Layer Security (TLS) and therefore emails are not encrypted. To connect securely to remote SSL/TLS resources by creating and uploading custom Oracle wallets, follow the instructions in [Configuring UTL\$1HTTP access using certificates and an Oracle wallet](Oracle.Concepts.ONA.md). The specific certificates that are required for your wallet vary by service. For AWS services, these can typically be found in the [Amazon trust services repository](https://www.amazontrust.com/repository/). + UTL\$1MAIL does not support authentication with SMTP servers. + You can only send a single attachment in an email. + You can't send attachments larger than 32 K. + You can only use ASCII and Extended Binary Coded Decimal Interchange Code (EBCDIC) character encodings. + SMTP port (25) is throttled based on the elastic network interface owner's policies. When you enable UTL\$1MAIL, only the master user for your DB instance is granted the execute privilege. If necessary, the master user can grant the execute privilege to other users so that they can use UTL\$1MAIL. **Important** We recommend that you enable Oracle's built-in auditing feature to track the use of UTL\$1MAIL procedures. ## Prerequisites for Oracle UTL\$1MAIL The following are prerequisites for using Oracle UTL\$1MAIL: + One or more SMTP servers, and the corresponding IP addresses or public or private Domain Name Server (DNS) names. For more information about private DNS names resolved through a custom DNS server, see [Setting up a custom DNS server](Appendix.Oracle.CommonDBATasks.System.md#Appendix.Oracle.CommonDBATasks.CustomDNS). ## Adding the Oracle UTL\$1MAIL option The general process for adding the Oracle UTL\$1MAIL option to a DB instance is the following: 1. Create a new option group, or copy or modify an existing option group. 1. Add the option to the option group. 1. Associate the option group with the DB instance. After you add the UTL\$1MAIL option, as soon as the option group is active, UTL\$1MAIL is active. **To add the UTL\$1MAIL option to a DB instance** 1. Determine the option group you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings: 1. For **Engine**, choose the edition of Oracle you want to use. 1. For **Major engine version**, choose the version of your DB instance. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create). 1. Add the **UTL\$1MAIL** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). 1. Apply the option group to a new or existing DB instance: + For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Using Oracle UTL\$1MAIL After you enable the UTL\$1MAIL option, you must configure the SMTP server before you can begin using it. You configure the SMTP server by setting the SMTP\$1OUT\$1SERVER parameter to a valid IP address or public DNS name. For the SMTP\$1OUT\$1SERVER parameter, you can specify a comma-separated list of the addresses of multiple servers. If the first server is unavailable, UTL\$1MAIL tries the next server, and so on. You can set the default SMTP\$1OUT\$1SERVER for a DB instance by using a [DB parameter group](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html). You can set the SMTP\$1OUT\$1SERVER parameter for a session by running the following code on your database on your DB instance. ``` 1. ALTER SESSION SET smtp_out_server = mailserver.domain.com:25; ``` After the UTL\$1MAIL option is enabled, and your SMTP\$1OUT\$1SERVER is configured, you can send mail by using the `SEND` procedure. For more information, see [UTL\$1MAIL](http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_mail.htm#BABFJJBD) in the Oracle documentation. ## Removing the Oracle UTL\$1MAIL option You can remove Oracle UTL\$1MAIL from a DB instance. To remove UTL\$1MAIL from a DB instance, do one of the following: + To remove UTL\$1MAIL from multiple DB instances, remove the UTL\$1MAIL option from the option group they belong to. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption). + To remove UTL\$1MAIL from a single DB instance, modify the DB instance and specify a different option group that doesn't include the UTL\$1MAIL option. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ## Troubleshooting The following are issues you might encounter when you use UTL\$1MAIL with Amazon RDS. + Throttling. SMTP port (25) is throttled based on the elastic network interface owner's policies. If you can successfully send email by using UTL\$1MAIL, and you see the error `ORA-29278: SMTP transient error: 421 Service not available`, you are possibly being throttled. If you experience throttling with email delivery, we recommend that you implement a backoff algorithm. For more information about backoff algorithms, see [Error retries and exponential backoff in AWS](https://docs.aws.amazon.com/general/latest/gr/api-retries.html) and [How to handle a "throttling – Maximum sending rate exceeded" error](https://aws.amazon.com/blogs/ses/how-to-handle-a-throttling-maximum-sending-rate-exceeded-error/). You can request that this throttle be removed. For more information, see [How do I remove the throttle on port 25 from my EC2 instance?](https://aws.amazon.com/premiumsupport/knowledge-center/ec2-port-25-throttle/). # Oracle XML DB Oracle XML DB adds native XML support to your DB instance. With XML DB, you can store and retrieve structured or unstructured XML and relational data. The XML DB protocol server isn't supported on RDS for Oracle. XML DB is preinstalled on Oracle Database 12c and higher. Thus, you don't need to use an option group to explicitly install XML DB as an additional feature. To learn how to configure and use XML DB, see [Oracle XML DB Developer's Guide](https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/) in the Oracle Database documentation.