AWS Database Blog

Make configuration changes to an Amazon RDS Custom for Oracle instance: Part 1

Amazon Relational Database Service (Amazon RDS) Custom is a managed database service that provides the flexibility to customize your database, underlying server, and operating system configurations to support applications that require more control than a typical managed RDS provides. Amazon RDS Custom for Oracle is built for legacy, custom, packaged applications and any application that requires access to the underlying OS and DB environment.

There are built-in features available to customize the environment of an RDS Custom for Oracle instance, such as creating and restoring snapshots to create a clone environment, creating read replicas to offload read-only workloads to a replica instance, modifying storage configurations, and changing the Custom Engine Version (CEV) to apply release updates or patches using the Amazon RDS Console or Amazon RDS API. You may also need to modify the default configuration of the database created by an RDS Custom for Oracle instance to meet various requirements of the dependent application, such as enabling additional database features or options, or to change database settings such as the time zone or character set. With shell access to the underlying Amazon Elastic Compute Cloud (Amazon EC2) instance as root user and sysdba access to the database, you can manually perform such customizations on the EC2 instance or on the database layer. AWS has introduced the support perimeter feature to notify you of changes that are made to the underlying database or OS that would impact the automation and monitoring framework of RDS Custom. It’s important to ensure that the customizations on your RDS for Oracle instance don’t break the support perimeter, which will force the instance to be in an unsupported-configuration state until the underlying unsupported configurations are fixed.

In this series of posts, we discuss best practices and step-by-step instructions to perform common customizations on RDS Custom for Oracle without breaking the support perimeter.

  • Part 1 (this post) covers customizing the time zone and character set of the database.
  • Part 2 discusses customizations such as changing the default block size, which requires the database to be recreated.
  • Part 3 discusses more customization scenarios, such as enabling the flashback database, modifying TNS configurations and database options, and best practices to apply patches in your RDS Custom for Oracle instance.

Customization workflow

Follow this workflow to make configuration changes to the RDS Custom for Oracle instance without breaking the support perimeter. We discuss each step in detail in this section.

Pause RDS Custom automation

Before starting to customize RDS Custom for Oracle DB instance, you need to pause the automation to ensure that your customizations don’t interfere with the RDS Custom automation and monitoring framework. You can pause the automation using either the Amazon RDS Console or the AWS Command Line Interface (AWS CLI). For instructions, refer to Pausing and resuming RDS Custom automation. Choose the duration for pausing the automation based on the time required to complete the maintenance activity. If you’re not sure about the duration of the activity, you can pause automation for 60 minutes and then extend it further before the pause period expires.

Identify the EC2 instance hosting the database

Follow these steps to identify the EC2 instance hosting your RDS Custom DB instance:

  1. On the Amazon RDS Console, in the navigation pane, choose Databases, then choose the RDS Custom DB instance to which you want to connect.
  2. Choose Configuration.
  3. Note the resource ID value. For example, the resource ID might be db-ABCDEFGHIJKLMNOPQRS0123456.
  4. On the Amazon EC2 Console, in the navigation pane, choose Instances.
  5. Find the name of your EC2 instance, and choose the instance ID associated with it. For example, the EC2 instance ID might be i-abcdefghijklm01234.

Connect to the EC2 instance

To perform the necessary customizations, you need to connect to the underlying EC2 instance using SSH keys or AWS Systems Manager (for instructions, refer to Connecting to your RDS Custom DB instance using AWS Systems Manager).

After you’re logged in as ec2-user, you can switch to the root user or rdsdb user who owns the Oracle database binaries:

ec2-user> sudo su –
or
ec2-user> sudo su – rdsdb

Verify the current configuration

Before you make any configuration changes, verify the existing configuration settings at the database or operating system layer, which can be compared with the settings post-customization to validate the changes. Depending on the type of changes, you may either use OS tools or queries against the database to verify the current settings.

Make required configuration changes

In this stage, make the necessary customizations on the instance using operating system utilities or a database client such as SQL*Plus.

Reboot the EC2 instance or bounce the database

Depending on the type of customization, you may need to reboot the EC2 instance or bounce the database for the changes to take effect.

Verify modified settings

In this stage, verify the configuration changes at the database or operating system layer by comparing the configuration settings prior to making the customization.

Resume automation

Resume automation for the instance to enable the automation and monitoring framework of RDS Custom. RDS Custom for Oracle automatically resumes the automation after the pause period is expired. However, you can also resume the automation manually from the Amazon RDS Console or using the API when the maintenance activity is complete.

Verify the RDS Custom automation framework

Upon resuming the automation after you finish the customization, the RDS Custom instance starts an automated backup, after which the status of the instance on the console changes to available if the steps are followed correctly. When the backup is complete, you can verify the Latest restore time in the maintenance and backup section of the Amazon RDS Console or using the AWS CLI, as follows:

aws rds describe-db-instances --db-instance-identifier demo-2-replica
"LatestRestorableTime": "2022-05-18T11:21:27+00:00"

You can also verify the latest snapshot created by the instance from the Amazon RDS Console (choose Automated backups, locate the database, and choose System snapshots) or using the AWS CLI as follows:

aws rds describe-db-snapshots --db-instance-identifier  demo-2-replica --query="max_by(DBSnapshots, &SnapshotCreateTime)"


    "DBSnapshotIdentifier": "rds:demo-2-replica-2022-05-18-11-16",
    "DBInstanceIdentifier": "demo-2-replica",
    "SnapshotCreateTime": "2022-05-18T11:17:33.417000+00:00",

In these examples, demo-2-replica is the instance identifier of the RDS Custom for Oracle instance.

If you see a latest automated snapshot created after the automation was resumed, the latest restorable time is pointing to a time after you paused the automation and it is advancing when you query it after 10 minutes, you can confirm that the instance is in healthy status.

In the following sections, we discuss step-by-step instructions to customize the time zone and character set of the database.

Change the time zone of the database

You can change the time zone of an RDS Custom for Oracle instance at the host level or at the database level.

Changing the time zone at the host level affects all date columns and values returned by functions such as SYSDATE, SYSTIMESTAMP, and CURRENT_DATE. When changing the time zone at the host level for an RDS Custom for Oracle instance that contains application data, the point-in-time recovery (PITR) of the instance can be impacted if you attempt to perform PITR to a time when the database has two System Change Numbers (SCNs) corresponding to a wall clock time, due to the time zone change.

Changing the time zone at the database level using the ALTER DATABASE command only affects certain data types (TIMESTAMP WITH LOCAL TIME ZONE) and timestamps of the messages written to the database alert.log and trace files. It doesn’t change the value returned by SYSDATE, SYSTIMESTAMP, or CURRENT_DATE functions. The database time zone functions as a time zone in which the values of the TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) data type are normalized to the current database time zone when they’re stored in the database. However, these stored values are converted into the session time zone on insert and retrieval, so the actual setting of the database time zone isn’t critical.

Depending on your specific use case, you may choose to change the time zone settings either at the database level, host level, or both. In this section, we review the step-by-step instructions for changing time zone settings at the host level and database level.

Change the time zone at the host level

It is not recommended to change the time zone configuration at host level manually as it can cause the instance to go to unsupported-configuration state. To change the time zone configuration at host level, you can leverage option groups in RDS Custom for Oracle. Refer to Working with option groups in RDS Custom for Oracle to learn about option groups and changing Oracle time zone to understand the process to be followed for changing the time zone setting at host level. It is important to review the Considerations for setting the time zone in RDS Custom for Oracle and Limitations for the time zone setting in RDS Custom for Oracle.

Change the time zone at the database level

By default, RDS Custom for Oracle instance is created with the database time zone as UTC (+00:00), which is the default time zone of the server operating system. In addition to changing the database time zone using the ALTER DATABASE command, you can also change the settings at the session level by setting the ORA_SDTZ environment variable or using the ALTER SESSION command to influence how the TIMESTAMP WITH LOCAL TIME ZONE column data is stored, retrieved, or converted. Refer to Setting the Session Time Zone for more details. Follow these steps to change time zone settings at the database level:

  1. Pause the automation for 30 minutes, connect to the EC2 instance, and switch to rdsdb user: 
    ec2-user$> sudo su – rdsdb
  2. Connect to the database and verify the current database time zone setting: 
    rdsdb$>sqlplus / as sysdba
SQL>  SELECT dbtimezone FROM DUAL;
DBTIME
------
+00:00
  3. Change the database time zone setting to the desired value. The following command alters the Database Time Zone to GMT+4: 
    SQL>ALTER DATABASE SET TIME_ZONE='+04:00';
Database altered.
  4. Restart the database and verify the dbtimezone setting (it’s necessary to bounce the database for the change to take effect): 
    rdsdb$ sqlplus / as sysdba
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.
Total System Global Area 1.2382E+10 bytes
Fixed Size                  8910200 bytes
Variable Size            6207569920 bytes
Database Buffers         6140461056 bytes
Redo Buffers               24641536 bytes
Database mounted.
Database opened.
SQL>  SELECT dbtimezone FROM DUAL;

DBTIME
------
+04:00
  5. Resume automation for the instance to enable the automation and monitoring framework of RDS Custom.
  6. Verify the backups to ensure that there is an automated snapshot taken after resuming the automation and LatestRestorableTime is pointing to a timestamp after the automation resume time.

Change database and national character sets

A character set determines what languages can be represented in the database. An Oracle database has two types of character sets: the database character set and the national character set. The database character set is used to determine what types of data can be used for identifiers, PL/SQL programs, and the data stored in CHAR, VARCHAR2, CLOB, and LONG columns. The national character set is used to store and interpret the data kept in NCHAR and NVARCHAR2 columns. For more information about character sets in Oracle Database, refer to Choosing a Character Set.

As of this writing, RDS Custom for Oracle creates the instance with the database character set as US7ASCII. However, to support specific languages in the database, you may need to change the database character set of the database created by RDS Custom for Oracle. If US7ASCII doesn’t meet your character set requirements, we recommend creating the database with Unicode UTF-8 universal character set (AL32UTF8). As a Unicode character set, AL32UTF8 supports storing most of the commonly spoken languages in the database. You can also choose any other character set for the database character set if you have a preference.

Similarly, the national character set of the database created by RDS Custom for Oracle is AL16UTF16 and is the recommended setting. If you need to change the national character set to 8-bit encoding of Unicode due to an application dependency, you can change it to UTF8. Currently, Oracle Database supports only UTF8 and AL16UTF16 for the national character set.

As of this writing, RDS Custom for Oracle doesn’t support choosing the database character set or national character set at the time of provisioning. However, you can change the database or national character set manually after the instance is provisioned, as discussed in this section. Please note the steps outlined here are for changing database and national character sets of the starter database immediately after provisioning and before it’s populated with application data. If you intend to change the database character set of an RDS Custom for Oracle instance that contains application data, you may need to use Database Migration Assistant for Unicode (DMU) or the export/import procedure as mentioned in Changing the Character Set After Database Creation. You may also use replication tools like Oracle GoldenGate or AWS Database Migration Service (AWS DMS) if you want to achieve such migrations with a reduced outage window.

Change the database character set (NLS_CHARACTERSET)

In the following example, we change the database character set to AL32UTF8. You can follow a similar method for changing the database character of an RDS Custom for Oracle database to any character set supported by an Oracle Database before it’s populated with application data.

  1. Pause the automation to ensure that the customization doesn’t interfere with the RDS Custom automation framework. These steps can be completed in less than 30 minutes. However, you can further extend the pause period if you think this activity needs more time to complete.
  2. Connect to the underlying EC2 instance and switch to rdsdb user: 
    ec2-user$> sudo su – rdsdb
  3. Verify the current database character set: 
    rdsdb$ sqlplus / as sysdba
SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME='NLS_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
US7ASCII

SQL>
  4. Change the database character set. The following command changes the database character set to AL32UTF8. You can replace AL32UTF8 with your desired character set. 
    SQL>shutdown immediate
SQL>startup restrict
SQL>ALTER DATABASE CHARACTER SET INTERNAL_CONVERT AL32UTF8;
SQL>shutdown immediate
SQL>startup
  5. Verify the character set change: 
    SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME='NLS_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
AL32UTF8
  6. Resume automation for the instance to enable the automation and monitoring framework of RDS Custom.
  7. Verify the backups to ensure that there is an automated snapshot taken after resuming the automation and LatestRestorableTime is pointing to a timestamp after the automation resume time.

Note: The new character set you specify in the ALTER DATABASE CHARACTER SET INTERNAL_CONVERT command must be a superset of the database current character set or the command will fail with the following error:

ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

If you want to choose a character set that isn’t a superset of the current database character set, you can recreate the database as discussed in Part 2 of this series.

Change the national character set (NLS_NCHAR_CHARACTERSET)

You can change the national character set of an RDS Custom for Oracle database from AL16UTF16 (default) to UTF8 using the ALTER DATABASE NATIONAL CHARACTER SET UTF8 command. However, the process can be complex due to NCHAR data existing in Oracle internal schemas, which will cause the command to fail with the following error:

ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR
or NVARCHAR2 data exists

The data from the tables containing the NCHAR data type can be exported or truncated and re-imported to achieve the task. Instead, it’s more straightforward to recreate the starter database with your desired character set configurations as discussed in Part 2 of this series.

In this section, we discuss step-by-step instructions to change the national character set of an RDS Custom for Oracle starter database from AL16UTF16 to UTF8 without recreating the database.

  1. Pause the automation to ensure that the customization doesn’t interfere with the RDS Custom automation framework. These steps can be completed in less than 60 minutes. However, you can further extend the pause period if you think this activity needs more time to complete.
  2. Connect to the underlying EC2 instance and switch to rdsdb user: 
    ec2-user$> sudo su – rdsdb
  3. Verify the current national character set: 
    rdsdb$ sqlplus / as sysdba
SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME= 'NLS_NCHAR_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
AL16UTF16
SQL>
  4. Run the following script to identify any tables containing NCHAR, NVARCHAR, or NCLOB columns in schemas other than SYS and SYSTEM: 
    set pages 1000 linesize 200
set serveroutput on
declare
  v_nchar_row_count  number;
  v_tables_with_nchar_data number := 0;
  v_tables_without_nchar_data number := 0;
begin
  for r in (select distinct owner, table_name
              from dba_tab_columns
             where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') 
	     and owner not in ('SYS','SYSTEM')
             order by owner, table_name
           ) loop
    execute immediate 'select count(*) from '||r.owner||'.'||r.table_name into v_nchar_row_count;
    if (v_nchar_row_count != 0) then
      dbms_output.put_line('found '||v_nchar_row_count||' rows in NCHAR table: '||r.owner||'.'||r.table_name);
      v_tables_with_nchar_data := v_tables_with_nchar_data + 1;
    else
      v_tables_without_nchar_data := v_tables_without_nchar_data + 1;
    end if; 
  end loop;
  dbms_output.put_line('total tables with NCHAR data: '||v_tables_with_nchar_data);
  dbms_output.put_line('total tables without NCHAR data: '||v_tables_without_nchar_data);
end;
/

    There are no concerns if there are tables without any data.

  5. Start up the database in restricted mode: 
    rdsdb$ sqlplus / as sysdba
SQL> shutdown immediate
SQL>startup restrict
  6. Export SYS objects (SYS.RADM_FPTM$ and SYS.RADM_FPTM_LOB$ ) containing NCHAR data. In these steps, we assume the RDS primary user ADMIN. You need to replace it with the actual RDS primary user if it’s not ADMIN in your case. 
    rdsdb$ sqlplus / as sysdba
SQL> create table ADMIN.RADM_FPTM$ as select * from SYS.RADM_FPTM$;
SQL>create table ADMIN.RADM_FPTM_LOB$ as select * from SYS.RADM_FPTM_LOB$;
SQL>create directory temp_dir as '/tmp';

-- Export the tables created in ADMIN schema.
expdp \'/ as sysdba\' directory=TEMP_DIR dumpfile=NCHAR.dmp LOGFILE=NCHAR.log tables=ADMIN.RADM_FPTM$,ADMIN.RADM_FPTM_LOB$

-- Drop tables in ADMIN schema and truncate SYS tables.

SQL>drop table ADMIN.RADM_FPTM$;
SQL>truncate table SYS.RADM_FPTM$;

SQL>drop table ADMIN.RADM_FPTM_LOB$;
SQL>truncate table SYS.RADM_FPTM_LOB$;
  7. Change the national character set to UTF8: 
    SQL> alter database national character set UTF8;
  8. Import the data back to the database: 
    impdp \'/ as sysdba\'  directory=TEMP_DIR dumpfile=NCHAR.dmp     LOGFILE=NCHAR_imp.log FULL=y TABLE_EXISTS_ACTION=TRUNCATE

SQL> insert into sys.RADM_FPTM$ (select * from ADMIN.RADM_FPTM$);
SQL>insert into sys.RADM_FPTM_LOB$ (select * from ADMIN.RADM_FPTM_LOB$);
SQL>commit;

In versions 12c and 19c only, SYS.RADM_FPTM$ and SYS.RADM_FPTM_LOB$ contain NCHAR data to be exported. If there are any additional tables that contain such data, the alter database national character set UTF8 command will fail with ORA-12717, with the list of tables containing NCHAR data listed in database alert.log. In that case, you can include those tables in the steps discussed here.

  1. Clean up the tables and directory objects: 
    rdsdb$ rm /tmp/NCHAR.dmp
rdsdb$ sqlplus / as sysdba
SQL>drop table ADMIN.RADM_FPTM$;
SQL>drop table ADMIN.RADM_FPTM_LOB$;
SQL>drop directory temp_dir;
  2. Bounce the database and the verify national character set: 
    SQL>shutdown immediate
SQL>startup
SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME= 'NLS_NCHAR_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
UTF8
  3. Resume automation for the instance to enable the automation and monitoring framework of RDS Custom.
  4. Verify the backups to ensure that there is an automated snapshot taken after resuming the automation and LatestRestorableTime is pointing to a timestamp after the automation resume time.

Conclusion

RDS Custom for Oracle allows you customize your database environment to meet various requirements of the dependent application in addition to offering the benefits of a managed service.

In this post, we discussed how to customize the character set and time zone of the database without breaking the support perimeter. In Part 2 of this series, we cover how to recreate the RDS Custom for Oracle database to change a few default configurations, such as database block size, which requires the database to be recreated. In Part 3, we discuss more customization scenarios, such as enabling a flashback database, modifying TNS configurations and database options, and best practices to apply patches on the RDS Custom for Oracle instance.

If you have any comments or questions, please leave them in the comments section.

About the authors

Jobin Joseph is a Senior Database Specialist Solution Architect based in Dubai. With a focus on relational database engines, he assists customers in migrating and modernizing their database workloads to AWS. He is an Oracle Certified Master with 20 years of experience with Oracle databases.

Nitin Saxena is a Senior Database Engineer in RDS DBS Managed Commercial Engines with Amazon Web Services. He focuses on services like RDS Oracle and RDS Custom for Oracle . He enjoys designing and developing new features on RDS Oracle and RDS Custom to solve customer problems.

Dwarka Rao is a Database Migration Specialist Solutions Architect with the Amazon Database Migration Accelerator team at Amazon Web Services. He helps customers and partners build highly scalable, available, and secure databases solutions on cloud migration projects from on-premises to AWS.