Oracle Instance Parameters and Aurora MySQL Parameter Groups - Oracle to Aurora MySQL Migration Playbook
Oracle UsageMySQL Usage
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle Instance Parameters and Aurora MySQL Parameter Groups

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

One star feature compatibility

N/A

N/A

Use cluster and database cluster parameters.

Oracle Usage

You can configure Oracle instance and database-level parameters using the ALTER SYSTEM command. You can configure certain parameters dynamically and take immediate effect while other parameters require an instance restart.

  • All Oracle instance and database-level parameters are stored in a binary file known as the Server Parameter file (SPFILE).

  • The binary SPFILE can be exported to a text file using the following command:

    CREATE PFILE = 'my_init.ora'
FROM SPFILE = 's_params.ora';

When you modify parameters, you can choose the persistence of the changed values with one of the three following options:

  • Make the change applicable only after a restart by specifying scope=spfile.

  • Make the change dynamically, but not persistent , after a restart by specifying scope=memory.

  • Make the change both dynamically and persistent by specifying scope=both.

Examples

Use the ALTER SYSTEM SET command to configure a value for an Oracle parameter.

ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE SCOPE=BOTH;

For more information, see Initialization Parameters and Changing Parameter Values in a Parameter File in the Oracle documentation.

MySQL Usage

When you run MySQL databases as Amazon Aurora clusters, you can use parameter groups to change the cluster-level and database-level parameters.

Most of the MySQL parameters are configurable in an Amazon Aurora MySQL cluster, but some are disabled and cannot be modified. Since Amazon Aurora clusters restrict access to the underlying operating system, modification to MySQL parameters must be made using Parameter Groups.

Amazon Aurora is a cluster of database instances and, as a direct result, some of the MySQL parameters apply to the entire cluster while other parameters apply only to a particular database instance.

Aurora MySQL parameter class Controlled through

Cluster-level parameters

Single cluster parameter group for each Amazon Aurora cluster

Managed using cluster parameter groups.

Consider the following example:

aurora_load_from_s3_role,
default_password_lifetime,
default_storage_engine

Database instance-level parameters

Every instance in an Amazon Aurora cluster can be associated with a unique database parameter group

Managed using database parameter groups.

Consider the following example:

autocommit,
connect_timeout,
innodb_change_buffer_max_size

Examples

Create and Configure a New Parameter Group

Follow these steps to create and configure an Amazon Aurora database and cluster parameter groups:

  1. Log in to the Amazon Management Console, choose RDS, and then choose Databases.

  2. Choose Parameter groups, and choose Create parameter group.

    Note

    You can’t edit the default parameter group. Create a custom parameter group to apply changes to your Amazon Aurora cluster and its database instances.

  3. For Parameter group family, choose aurora-mysql5.7.

  4. For Type, choose DB parameter group.

  5. Choose Create.

Modify an Existing Parameter Group

  1. Log in to the Amazon Management Console, choose RDS, and then choose Databases.

  2. Choose Parameter groups, and choose the name of the parameter to edit.

  3. For Parameter group actions, choose Edit.

  4. Change parameter values and choose Save changes.

For more information, see Server System Variables in the MySQL documentation.