Working with option groups
Some DB engines offer additional features that make it easier to manage data and databases, and to provide additional security for your database. Amazon RDS uses option groups to enable and configure these features. An option group can specify features, called options, that are available for a particular Amazon RDS DB instance. Options can have settings that specify how the option works. When you associate a DB instance with an option group, the specified options and option settings are enabled for that DB instance.
Amazon RDS supports options for the following database engines:
Database engine | Relevant documentation |
---|---|
|
|
|
|
|
|
|
|
|
|
|
PostgreSQL does not use options and option groups. PostgreSQL uses extensions and modules to provide additional features. For more information, see Supported PostgreSQL extension versions. |
Option groups overview
Amazon RDS provides an empty default option group for each new DB instance. You can't modify or delete this default option group, but any new option group that you create derives its settings from the default option group. To apply an option to a DB instance, you must do the following:
-
Create a new option group, or copy or modify an existing option group.
-
Add one or more options to the option group.
-
Associate the option group with the DB instance.
To associate an option group with a DB instance, modify the DB instance. For more information, see Modifying an Amazon RDS DB instance.
Both DB instances and DB snapshots can be associated with an option group. In some cases, you might restore from a DB snapshot or perform a point-in-time restore for a DB instance. In these cases, the option group associated with the DB snapshot or DB instance is, by default, associated with the restored DB instance. You can associate a different option group with a restored DB instance. However, the new option group must contain any persistent or permanent options that were included in the original option group. Persistent and permanent options are described following.
Options require additional memory to run on a DB instance. Thus, you might need to launch a larger instance to use them, depending on your current use of 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 or out-of-memory errors.
Persistent and permanent options
Two types of options, persistent and permanent, require special consideration when you add them to an option group.
Persistent options can't be removed from an option group while DB instances are associated with the option group. An example of a persistent option is the TDE option for Microsoft SQL Server transparent data encryption (TDE). You must disassociate all DB instances from the option group before a persistent option can be removed from the option group. In some cases, you might restore or perform a point-in-time restore from a DB snapshot. In these cases, if the option group associated with that DB snapshot contains a persistent option, you can only associate the restored DB instance with that option group.
Permanent options, such as the TDE option for Oracle Advanced Security TDE, can never be removed from an option group. You can change the option group of a DB instance that is using the permanent option. However, the option group associated with the DB instance must include the same permanent option. In some cases, you might restore or perform a point-in-time restore from a DB snapshot. In these cases, if the option group associated with that DB snapshot contains a permanent option, you can only associate the restored DB instance with an option group with that permanent option.
For Oracle DB instances, you can copy shared DB snapshots that have the options Timezone
or OLS
(or both). To do so, specify a target option group that includes these options when you copy the DB snapshot. The OLS option is permanent
and persistent only for Oracle DB instances running Oracle version 12.2 or higher. For more information about these options,
see Oracle time zone and
Oracle Label Security.
VPC considerations
The option group associated with the DB instance is linked to the DB instance's VPC. This means that you can't use the option group assigned to a DB instance if you try to restore the instance to a different VPC. If you restore a DB instance to a different VPC, you can do one of the following:
Assign the default option group to the DB instance.
Assign an option group that is linked to that VPC.
Create a new option group and assign it to the DB instance.
With persistent or permanent options, such as Oracle TDE, you must create a new option group. This option group must include the persistent or permanent option when restoring a DB instance into a different VPC.
Option settings control the behavior of an option. For example, the Oracle Advanced
Security option NATIVE_NETWORK_ENCRYPTION
has a setting
that you can use to specify the encryption algorithm for network traffic to and from the
DB instance. Some options settings are optimized for use with Amazon RDS and cannot be
changed.
Mutually exclusive options
Some options are mutually exclusive. You can use one or the other, but not both at the same time. The following options are mutually exclusive:
Creating an option group
You can create a new option group that derives its settings from the default option group. You then add one or more options to the new option group. Or, if you already have an existing option group, you can copy that option group with all of its options to a new option group. For more information, see Copying an option group.
After you create a new option group, it has no options. To learn how to add options to the option group, see Adding an option to an option group. After you have added the options you want, you can then associate the option group with a DB instance. This way, the options become available on the DB instance. For information about associating an option group with a DB instance, see the documentation for your engine in Working with option groups.
One way of creating an option group is by using the AWS Management Console.
To create a new option group by using the console
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose Create group.
-
In the Create option group window, do the following:
-
For Name, type a name for the option group that is unique within your AWS account. The name can contain only letters, digits, and hyphens.
-
For Description, type a brief description of the option group. The description is used for display purposes.
-
For Engine, choose the DB engine that you want.
-
For Major engine version, choose the major version of the DB engine that you want.
-
-
To continue, choose Create. To cancel the operation instead, choose Cancel.
To create an option group, use the AWS CLI
create-option-group
command with the following required parameters.
-
--option-group-name
-
--engine-name
-
--major-engine-version
-
--option-group-description
Example
The following example creates an option group named testoptiongroup
,
which is associated with the Oracle Enterprise Edition DB engine.
The description is enclosed in quotation marks.
For Linux, macOS, or Unix:
aws rds create-option-group \ --option-group-name
testoptiongroup
\ --engine-nameoracle-ee
\ --major-engine-version19
\ --option-group-description "Test option group for Oracle Database 19c EE
"
For Windows:
aws rds create-option-group ^ --option-group-name
testoptiongroup
^ --engine-nameoracle-ee
^- --major-engine-version19
^ --option-group-description "Test option group for Oracle Database 19c EE
"
To create an option group, call the Amazon RDS API CreateOptionGroup
operation.
Include the following parameters:
-
OptionGroupName
-
EngineName
-
MajorEngineVersion
-
OptionGroupDescription
Copying an option group
You can use the AWS CLI or the Amazon RDS API copy an option group. Copying an option group can be convenient. An example is when you have an existing option group and want to include most of its custom parameters and values in a new option group. You can also make a copy of an option group that you use in production and then modify the copy to test other option settings.
Note
Currently, you can't copy an option group to a different AWS Region.
To copy an option group, use the AWS CLI copy-option-group command. Include the following required options:
-
--source-option-group-identifier
-
--target-option-group-identifier
-
--target-option-group-description
Example
The following example creates an option group named
new-option-group
, which is a local copy of the option group
my-option-group
.
For Linux, macOS, or Unix:
aws rds copy-option-group \ --source-option-group-identifier
my-option-group
\ --target-option-group-identifiernew-option-group
\ --target-option-group-description "My new option group
"
For Windows:
aws rds copy-option-group ^ --source-option-group-identifier
my-option-group
^ --target-option-group-identifiernew-option-group
^ --target-option-group-description "My new option group
"
To copy an option group, call the Amazon RDS API CopyOptionGroup operation. Include the following required parameters.
-
SourceOptionGroupIdentifier
-
TargetOptionGroupIdentifier
-
TargetOptionGroupDescription
Adding an option to an option group
You can add an option to an existing option group. After you have added the options you want, you can then associate the option group with a DB instance so that the options become available on the DB instance. For information about associating an option group with a DB instance, see the documentation for your specific DB engine listed at Working with option groups.
Option group changes must be applied immediately in two cases:
-
When you add an option that adds or updates a port value, such as the
OEM
option. -
When you add or remove an option group with an option that includes a port value.
In these cases, choose the Apply Immediately option in the
console. Or you can include the --apply-immediately
option when using the
AWS CLI or set the ApplyImmediately
parameter to true
when
using the Amazon RDS API. Options that don't include port values can be applied immediately,
or can be applied during the next maintenance window for the DB instance.
Note
If you specify a security group as a value for an option in an option group,
manage the security group by modifying the option group. You can't change or
remove this security group by modifying a DB instance. Also, the security group
doesn't appear in the DB instance details in the AWS Management Console or in the output for
the AWS CLI command describe-db-instances
.
You can use the AWS Management Console to add an option to an option group.
To add an option to an option group by using the console
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group that you want to modify, and then choose Add option.
-
In the Add option window, do the following:
-
Choose the option that you want to add. You might need to provide additional values, depending on the option that you select. For example, when you choose the
OEM
option, you must also type a port value and specify a security group. -
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.
-
-
When the settings are as you want them, choose Add option.
To add an option to an option group, run the AWS CLI add-option-to-option-group command with the option that you
want to add. To enable the new option immediately on all associated DB
instances, include the --apply-immediately
parameter. By
default, the option is enabled for each associated DB instance during
its next maintenance window. Include the following required parameter:
--option-group-name
Example
The following example adds the Timezone
option, with the
America/Los_Angeles
setting, to an option group named
testoptiongroup
and immediately enables it.
For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --option-group-name
testoptiongroup
\ --options "OptionName=Timezone,OptionSettings=[{Name=TIME_ZONE,Value=America/Los_Angeles}]
" \ --apply-immediately
For Windows:
aws rds add-option-to-option-group ^ --option-group-name
testoptiongroup
^ --options "OptionName=Timezone,OptionSettings=[{Name=TIME_ZONE,Value=America/Los_Angeles}]
" ^ --apply-immediately
Command output is similar to the following:
...{ "OptionName": "Timezone", "OptionDescription": "Change time zone", "Persistent": true, "Permanent": false, "OptionSettings": [ { "Name": "TIME_ZONE", "Value": "America/Los_Angeles", "DefaultValue": "UTC", "Description": "Specifies the timezone the user wants to change the system time to", "ApplyType": "DYNAMIC", "DataType": "STRING", "AllowedValues": "Africa/Cairo,...", "IsModifiable": true, "IsCollection": false } ], "DBSecurityGroupMemberships": [], "VpcSecurityGroupMemberships": [] }...
Example
The following example adds the Oracle OEM option to an option group. It also specifies a custom port and a pair of Amazon EC2 VPC security groups to use for that port.
For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --option-group-name
testoptiongroup
\ --optionsOptionName=OEM,Port=
\ --apply-immediately5500
,VpcSecurityGroupMemberships="sg-test1,sg-test2
"
For Windows:
aws rds add-option-to-option-group ^ --option-group-name
testoptiongroup
^ --optionsOptionName=OEM,Port=
^ --apply-immediately5500
,VpcSecurityGroupMemberships="sg-test1,sg-test2
"
Command output is similar to the following:
OPTIONGROUP False oracle-ee 19 arn:aws:rds:us-east-1:1234567890:og:testoptiongroup Test Option Group testoptiongroup vpc-test OPTIONS Oracle 12c EM Express OEM False False 5500 VPCSECURITYGROUPMEMBERSHIPS active sg-test1 VPCSECURITYGROUPMEMBERSHIPS active sg-test2
Example
The following example adds the Oracle option
NATIVE_NETWORK_ENCRYPTION
to an option group and specifies
the option settings. If no option settings are specified, default values are
used.
For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --option-group-name
testoptiongroup
\ --options '[{"OptionSettings":[{"Name":"SQLNET.ENCRYPTION_SERVER","Value":"REQUIRED
"},{"Name":"SQLNET.ENCRYPTION_TYPES_SERVER","Value":"AES256,AES192,DES
"}],"OptionName":"NATIVE_NETWORK_ENCRYPTION"}]' \ --apply-immediately
For Windows:
aws rds add-option-to-option-group ^ --option-group-name
testoptiongroup
^ --options "OptionSettings"=[{"Name"="SQLNET.ENCRYPTION_SERVER","Value"="REQUIRED
"},{"Name"="SQLNET.ENCRYPTION_TYPES_SERVER","Value"="AES256\,AES192\,DES
"}],"OptionName"="NATIVE_NETWORK_ENCRYPTION" ^ --apply-immediately
Command output is similar to the following:
...{ "OptionName": "NATIVE_NETWORK_ENCRYPTION", "OptionDescription": "Native Network Encryption", "Persistent": false, "Permanent": false, "OptionSettings": [ { "Name": "SQLNET.ENCRYPTION_TYPES_SERVER", "Value": "AES256,AES192,DES", "DefaultValue": "RC4_256,AES256,AES192,3DES168,RC4_128,AES128,3DES112,RC4_56,DES,RC4_40,DES40", "Description": "Specifies list of encryption algorithms in order of intended use", "ApplyType": "STATIC", "DataType": "STRING", "AllowedValues": "RC4_256,AES256,AES192,3DES168,RC4_128,AES128,3DES112,RC4_56,DES,RC4_40,DES40", "IsModifiable": true, "IsCollection": true }, { "Name": "SQLNET.ENCRYPTION_SERVER", "Value": "REQUIRED", "DefaultValue": "REQUESTED", "Description": "Specifies the desired encryption behavior", "ApplyType": "STATIC", "DataType": "STRING", "AllowedValues": "ACCEPTED,REJECTED,REQUESTED,REQUIRED", "IsModifiable": true, "IsCollection": false },...
To add an option to an option group using the Amazon RDS API, call the ModifyOptionGroup operation with the option that you
want to add. To enable the new option immediately on all associated DB
instances, include the ApplyImmediately
parameter and set it to true
. By
default, the option is enabled for each associated DB instance during
its next maintenance window. Include the following required parameter:
OptionGroupName
Listing the options and option settings for an option group
You can list all the options and option settings for an option group.
You can use the AWS Management Console to list all of the options and option settings for an option group.
To list the options and option settings for an option group
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the name of the option group to display its details. The options and option settings in the option group are listed.
To list the options and option settings for an option group, use the AWS CLI
describe-option-groups
command. Specify the name of the
option group whose options and settings you want to view. If you don't
specify an option group name, all option groups are described.
Example
The following example lists the options and option settings for all option groups.
aws rds describe-option-groups
Example
The following example lists the options and option settings for an option
group named testoptiongroup
.
aws rds describe-option-groups --option-group-name
testoptiongroup
To list the options and option settings for an option group, use the Amazon RDS API
DescribeOptionGroups
operation. Specify the name of the
option group whose options and settings you want to view. If you don't
specify an option group name, all option groups are described.
Modifying an option setting
After you have added an option that has modifiable option settings, you can modify the settings at any time. If you change options or option settings in an option group, those changes are applied to all DB instances that are associated with that option group. For more information on what settings are available for the various options, see the documentation for your engine in Working with option groups.
Option group changes must be applied immediately in two cases:
-
When you add an option that adds or updates a port value, such as the
OEM
option. -
When you add or remove an option group with an option that includes a port value.
In these cases, choose the Apply Immediately option in the
console. Or you can include the --apply-immediately
option when using the
AWS CLI or set the ApplyImmediately
parameter to true
when
using the RDS API. Options that don't include port values can be applied immediately, or
can be applied during the next maintenance window for the DB instance.
Note
If you specify a security group as a value for an option in an option group, you
manage the security group by modifying the option group. You can't change or remove this security
group by modifying a DB instance. Also, the security group doesn't appear in the DB
instance details in the AWS Management Console or in the output for the AWS CLI command
describe-db-instances
.
You can use the AWS Management Console to modify an option setting.
To modify an option setting by using the console
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Select the option group whose option that you want to modify, and then choose Modify option.
-
In the Modify option window, from Installed Options, choose the option whose setting you want to modify. Make the changes that you want.
-
To enable the option 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.
-
When the settings are as you want them, choose Modify Option.
To modify an option setting, use the AWS CLI add-option-to-option-group
command with the option group
and option that you want to modify. By default, the option is enabled for each associated DB
instance during its next maintenance window. To apply the change immediately to all
associated DB instances, include the --apply-immediately
parameter. To modify an option setting, use the --settings
argument.
Example
The following example modifies the port that the Oracle Enterprise
Manager Database Control (OEM) uses in an option group named
testoptiongroup
and immediately applies the change.
For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --option-group-name testoptiongroup \ --options OptionName=OEM,Port=
5432
,DBSecurityGroupMemberships=default
\ --apply-immediately
For Windows:
aws rds add-option-to-option-group ^ --option-group-name testoptiongroup ^ --options OptionName=OEM,Port=
5432
,DBSecurityGroupMemberships=default
^ --apply-immediately
Command output is similar to the following:
OPTIONGROUP False oracle-ee 19 arn:aws:rds:us-east-1:1234567890:og:testoptiongroup Test Option Group testoptiongroup OPTIONS Oracle 12c EM Express OEM False False 5432 DBSECURITYGROUPMEMBERSHIPS default authorized
Example
The following example modifies the Oracle option
NATIVE_NETWORK_ENCRYPTION
and changes the option settings.
For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --option-group-name testoptiongroup \ --options '[{"OptionSettings":[{"Name":"SQLNET.ENCRYPTION_SERVER","Value":"
REQUIRED
"},{"Name":"SQLNET.ENCRYPTION_TYPES_SERVER","Value":"AES256,AES192,DES,RC4_256
"}],"OptionName":"NATIVE_NETWORK_ENCRYPTION"}]' \ --apply-immediately
For Windows:
aws rds add-option-to-option-group ^ --option-group-name testoptiongroup ^ --options "OptionSettings"=[{"Name"="SQLNET.ENCRYPTION_SERVER","Value"="
REQUIRED
"},{"Name"="SQLNET.ENCRYPTION_TYPES_SERVER","Value"="AES256\,AES192\,DES\,RC4_256
"}],"OptionName"="NATIVE_NETWORK_ENCRYPTION" ^ --apply-immediately
Command output is similar to the following:
OPTIONGROUP False oracle-ee 19 arn:aws:rds:us-east-1:1234567890:og:testoptiongroup Test Option Group testoptiongroup OPTIONS Oracle Advanced Security - Native Network Encryption NATIVE_NETWORK_ENCRYPTION False False OPTIONSETTINGS RC4_256,AES256,AES192,3DES168,RC4_128,AES128,3DES112,RC4_56,DES,RC4_40,DES40 STATIC STRING RC4_256,AES256,AES192,3DES168,RC4_128,AES128,3DES112,RC4_56,DES,RC4_40,DES40 Specifies list of encryption algorithms in order of intended use True True SQLNET.ENCRYPTION_TYPES_SERVER AES256,AES192,DES,RC4_256 OPTIONSETTINGS ACCEPTED,REJECTED,REQUESTED,REQUIRED STATIC STRING REQUESTED Specifies the desired encryption behavior False True SQLNET.ENCRYPTION_SERVER REQUIRED OPTIONSETTINGS SHA1,MD5 STATIC STRING SHA1,MD5 Specifies list of checksumming algorithms in order of intended use True True SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER SHA1,MD5 OPTIONSETTINGS ACCEPTED,REJECTED,REQUESTED,REQUIRED STATIC STRING REQUESTED Specifies the desired data integrity behavior False True SQLNET.CRYPTO_CHECKSUM_SERVER REQUESTED
To modify an option setting, use the Amazon RDS API ModifyOptionGroup
command with the option group
and option that you want to modify. By default, the option is enabled for each associated DB
instance during its next maintenance window. To apply the change immediately to all
associated DB instances, include the ApplyImmediately
parameter and set it to true
.
Removing an option from an option group
Some options can be removed from an option group, and some cannot. A persistent option cannot be removed from an option group until all DB instances associated with that option group are disassociated. A permanent option can never be removed from an option group. For more information about what options are removable, see the documentation for your specific engine listed at Working with option groups.
If you remove all options from an option group, Amazon RDS doesn't delete the option group. DB instances that are associated with the empty option group continue to be associated with it; they just won't have any active options. Alternatively, to remove all options from a DB instance, you can associate the DB instance with the default (empty) option group.
You can use the AWS Management Console to remove an option from an option group.
To remove an option from an option group by using the console
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Select the option group whose option you want to remove, and then choose Delete option.
-
In the Delete option window, do the following:
-
Select the check box for the option that you want to delete.
-
For the deletion to take effect as soon as you make it, for Apply immediately, choose Yes. If you choose No (the default), the option is deleted for each associated DB instance during its next maintenance window.
-
-
When the settings are as you want them, choose Yes, Delete.
To remove an option from an option group, use the AWS CLI
remove-option-from-option-group
command with the option
that you want to delete. By default, the option is
removed from each associated DB instance during its next maintenance window.
To apply the change immediately, include the --apply-immediately
parameter.
Example
The following example removes the Oracle Enterprise Manager Database
Control (OEM) option from an option group named testoptiongroup
and immediately applies the change.
For Linux, macOS, or Unix:
aws rds remove-option-from-option-group \ --option-group-name
testoptiongroup
\ --optionsOEM
\ --apply-immediately
For Windows:
aws rds remove-option-from-option-group ^ --option-group-name
testoptiongroup
^ --optionsOEM
^ --apply-immediately
Command output is similar to the following:
OPTIONGROUP testoptiongroup oracle-ee 19 Test option group
To remove an option from an option group, use the Amazon RDS API
ModifyOptionGroup
action.
By default, the option is removed from each associated DB instance during its next maintenance window.
To apply the change immediately, include the ApplyImmediately
parameter and set it to true
.
Include the following parameters:
-
OptionGroupName
-
OptionsToRemove.OptionName
Deleting an option group
You can delete an option group only if it meets the following criteria:
-
It is not associated with any Amazon RDS resource. An option group can be associated with a DB instance, a manual DB snapshot, or an automated DB snapshot.
-
It is not a default option group.
To identify the option groups used by your DB instances and DB snapshots, you can use the following CLI commands:
aws rds describe-db-instances \ --query 'DBInstances[*].[DBInstanceIdentifier,OptionGroupMemberships[].OptionGroupName]' aws rds describe-db-snapshots | jq -r '.DBSnapshots[] | "\(.DBInstanceIdentifier),\(.OptionGroupName)"' | sort | uniq
If you try to delete an option group that is associated with an RDS resource, an error like the following is returned.
An error occurred (InvalidOptionGroupStateFault) when calling the DeleteOptionGroup operation: The option group 'optionGroupName' cannot be deleted because it is in use.
To find the Amazon RDS resources associated with an option group
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the name of the option group to show its details.
-
Check the Associated Instances and Snapshots section for the associated Amazon RDS resources.
If a DB instance is associated with the option group, modify the DB instance to use a different option group. For more information, see Modifying an Amazon RDS DB instance.
If a manual DB snapshot is associated with the option group, modify the DB snapshot to
use a different option group. You can do so using the AWS CLI modify-db-snapshot
command.
Note
You can't modify the option group of an automated DB snapshot.
One way of deleting an option group is by using the AWS Management Console.
To delete an option group by using the console
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group.
-
Choose Delete group.
-
On the confirmation page, choose Delete to finish deleting the option group, or choose Cancel to cancel the deletion.
To delete an option group, use the AWS CLI
delete-option-group
command with the following required parameter.
-
--option-group-name
Example
The following example deletes an option group named testoptiongroup
.
For Linux, macOS, or Unix:
aws rds delete-option-group \ --option-group-name
testoptiongroup
For Windows:
aws rds delete-option-group ^ --option-group-name
testoptiongroup
To delete an option group, call the Amazon RDS API DeleteOptionGroup
operation.
Include the following parameter:
-
OptionGroupName