AWS Database Blog

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

With the flexibility of Amazon Relational Database Service (Amazon RDS) Custom for Oracle, you can customize your database, underlying server, and operating system configurations to support the various requirements of your workloads. In this series, we discuss best practices and step-by-step instructions to perform common customizations on Amazon RDS Custom for Oracle without breaking the support perimeter:

  • Part 1 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 (this post) discusses more customization scenarios, such as managing database components, enabling the flashback database feature, enabling unified auditing, and configuring HugePages on an RDS Custom for Oracle instance

Manage Oracle database components on Amazon RDS Custom for Oracle

In this section, we discuss how to install new components and uninstall existing components in an RDS Custom for Oracle instance. The default provisioning of an RDS Custom for Oracle instance comes with only mandatory components for the Oracle database for that specific version. These options may vary depending on the database version.

For example, an RDS Custom for Oracle 19c instance comes with the following components:

SQL>  SELECT comp_id, comp_name, status FROM dba_registry;

COMP_ID                        COMP_NAME                                                    STATUS
------------------------------ ------------------------------------------------------------ -----------
CATALOG                        Oracle Database Catalog Views                                VALID
CATPROC                        Oracle Database Packages and Types                           VALID
RAC                            Oracle Real Application Clusters                             OPTION OFF
XDB                            Oracle XML Database                                          VALID

Install additional components on Amazon RDS Custom for Oracle

For certain workloads, additional database components such as JVM or Oracle Spatial may need to be installed on the database layer to meet various application or business requirements. Unlike Amazon RDS for Oracle, option groups are not available on Amazon RDS Custom for Oracle to configure additional database components. On Amazon RDS Custom for Oracle, the installation of Oracle components is done manually by running the respective deployment scripts for those components.

As an example, we have included the steps to install JVM and Oracle Text on a 19c version of an RDS Custom for Oracle instance. You can follow a similar approach for configuring other database components according to their installation instructions.

  1. Pause the RDS Custom automation.

Although installing most of the Oracle components doesn’t incur a downtime, it’s recommended to perform such maintenance activities during the maintenance window for the instance. Refer to the installation guide for the specific database component you are installing to verify if the installation would require a downtime.

  1. Identify the Amazon Elastic Compute Cloud (Amazon EC2) instance for the RDS Custom for Oracle instance and connect to it using SSH keys or AWS Systems Manager (refer to Connecting to your RDS Custom DB instance using AWS Systems Manager).
  2. Run the following query to check if the required Oracle component exists on the database:
rdsdb>sqlplus / as sysdba

 SQL>  SELECT comp_id, comp_name, status FROM dba_registry;

COMP_ID                        COMP_NAME                                                    STATUS
------------------------------ ------------------------------------------------------------ -----------
CATALOG                        Oracle Database Catalog Views                                VALID
CATPROC                        Oracle Database Packages and Types                           VALID
RAC                            Oracle Real Application Clusters                             OPTION OFF
XDB                            Oracle XML Database                                          VALID
  1. Follow Oracle’s prescribed steps to install the required Oracle components on Amazon RDS Custom for Oracle 19c.
    • The following code shows the installation of Oracle Text, which doesn’t require downtime, as per the instructions from Oracle Support Note MOS – 2591868.1: 
      SQL> connect SYS as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK
SQL> spool off
SQL> SELECT comp_id, comp_name, status FROM dba_registry;

COMP_ID                        COMP_NAME                                                    STATUS
------------------------------ ------------------------------------------------------------ -----------
CATALOG                        Oracle Database Catalog Views                                VALID
CATPROC                        Oracle Database Packages and Types                           VALID
RAC                            Oracle Real Application Clusters                             OPTION OFF
XDB                            Oracle XML Database                                          VALID
CONTEXT                        Oracle Text                                                  VALID
    • Next, we install Oracle JVM on an RDS Custom for Oracle instance as per the steps from MOS – 2149019.1, which again doesn’t require downtime:
    rdsdb> sqlplus / as sysdba
SQL> @?/javavm/install/initjvm.SQL
SQL> @?/javavm/install/initdbj.sql
SQL> @?/rdbms/admin/catjava.sql
SQL> @?/rdbms/admin/utlrp.sql
    1. After the installation of both of the components, run the first query again to confirm the successful installation of the Oracle component.

    For example, we installed JVM and Oracle Text, and the following query shows the status of their successful installation :

    SQL> SET wrap off
SQL> col comp_name format a20
SQL> SELECT comp_id, comp_name, status FROM dba_registry;

COMP_ID                        COMP_NAME                                                    STATUS
------------------------------ ------------------------------------------------------------ -----------
CATALOG                        Oracle Database Catalog Views                                VALID
CATPROC                        Oracle Database Packages and Types                           VALID
RAC                            Oracle Real Application Clusters                             OPTION OFF
JAVAVM                         JServer JAVA Virtual Machine                                 VALID
CATJAVA                        Oracle Database Java Packages                                VALID
XDB                            Oracle XML Database                                          VALID
CONTEXT                        Oracle Text                                                  VALID
7 rows selected.
    1. Resume the RDS Custom automation.

    Uninstall Oracle Database components on Amazon RDS Custom for Oracle

    In some situations, you may need to uninstall one of the installed Oracle Database components on an RDS Custom for Oracle instance. This may happen when the workload no longer uses a specific feature provided by the installed component. As in the case of installation, the uninstallation of the components is also performed manually by running the appropriate script listed in the instructions for the specific component.

    As an example, we will uninstall Oracle Text on an RDS Custom for Oracle 19c instance:

    1. Pause the RDS Custom automation.

    Although uninstalling most of the Oracle components doesn’t incur database downtime, it’s recommended do it during the maintenance window.

    1. Identify the EC2 instance for the RDS Custom for Oracle instance and connect to it using SSH keys or AWS Systems Manager.
    2. Run the following query to check if the required Oracle component exists on the database. Because we installed JVM and Oracle Text earlier, we find them in the output of the following query:
    rdsdb>sqlplus / as sysdba

SQL>  SELECT comp_id, comp_name, status FROM dba_registry;
COMP_ID                        COMP_NAME                                                    STATUS
------------------------------ ------------------------------------------------------------ -----------
CATALOG                        Oracle Database Catalog Views                                VALID
CATPROC                        Oracle Database Packages and Types                           VALID
RAC                            Oracle Real Application Clusters                             OPTION OFF
JAVAVM                         JServer JAVA Virtual Machine                                 VALID
CATJAVA                        Oracle Database Java Packages                                VALID
XDB                            Oracle XML Database                                          VALID
CONTEXT                        Oracle Text                                                  VALID
7 rows selected.
    1. Follow Oracle’s prescribed steps to uninstall the required Oracle components on Amazon RDS Custom for Oracle 19c. We have listed here the steps for uninstalling Oracle Text as per the documentation on MOS – 1666831.1. This can be run without downtime.
    SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL> SELECT comp_id, comp_name, status FROM dba_registry;
COMP_ID                        COMP_NAME                                                    STATUS
------------------------------ ------------------------------------------------------------ -----------
CATALOG                        Oracle Database Catalog Views                                VALID
CATPROC                        Oracle Database Packages and Types                           VALID
RAC                            Oracle Real Application Clusters                             OPTION OFF
JAVAVM                         JServer JAVA Virtual Machine                                 VALID
CATJAVA                        Oracle Database Java Packages                                VALID
XDB                            Oracle XML Database                                          VALID
6 rows selected.
    1. Resume the RDS Custom automation.

    Flashback database on Amazon RDS Custom for Oracle

    Many workloads use the Oracle flashback database feature and restore points as a fast fallback option to recover from failures such as a failed database or application upgrade, or logical corruption by rewinding the database to a target time in the past. In AWS, this can be achieved using point-in-time recovery from automated backups or using manual snapshots, which are features available for Amazon RDS for Oracle and Amazon RDS Custom for Oracle. However, these options may not be feasible for all workloads, because restoring from a snapshot backup creates a new RDS Custom for Oracle instance, and availability and performance SLAs could be impacted due to lazy loading. For more information about lazy loading, see Restoring from a snapshot.

    Although Amazon RDS for Oracle doesn’t support using the flashback database feature, you can customize an RDS Custom for Oracle instance to use the flashback database feature and restore points. This allows customers to continue using the flashback database feature as a fast fallback option when deploying or migrating Oracle database workloads to AWS. Additionally, when using RDS Custom for Oracle in a Data Guard environment for high availability using a managed read replica or as a self-managed standby database, you may want to leverage features which depend on the flashback database. This includes fast reinstatement of former primary after a failover operation as discussed in Build high availability for Amazon RDS Custom for Oracle using read replicas.

    It is important to be aware of general limitations of the flashback database feature before using this feature for your workloads. In this section, we describe the steps involved in configuring the flashback database feature and rewinding the database to a restore point on an RDS Custom for Oracle instance.

    Enable the flashback database feature and create a restore point

    Complete the following configuration steps:

    1. Create a directory under /rdsdbdata mount point for saving flashback logs on the EC2 instance hosting Amazon RDS Custom for Oracle:
    sudo su - rdsdb
mkdir /rdsdbdata/fra
    1. Set flashback database-related parameters to enable flashback on the database (refer to Configuring the Fast Recovery Area for details on setting these parameters):
    SQL> alter system set db_recovery_file_dest='/rdsdbdata/fra';
SQL> alter system set db_flashback_retention_target=60;
SQL> alter system set db_recovery_file_dest_size=20G;
SQL> alter database flashback on;

    Enabling the flashback database feature on the database with alter database flashback on allows you to flashback the database to a point in time within the flashback window using an SCN, time stamp, or restore point. If you only intend to use guaranteed restore points to flashback the database to a specific restore point, then enabling flashback on the database is not necessary. Refer to Using Flashback Database and Restore Points for details.

    1. For this example, we created a guaranteed restore point to demonstrate the functionality:
    SQL> create restore point test_1 guarantee flashback database;

Restore point created.

    Flashback the database

    When you flashback the database, it involves some downtime because the database needs to be mounted during the flashback operation.

    1. Pause the RDS Custom automation for this instance.
    2. Shut down the database using shutdown immediate and start it in mount mode:
    SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.4964E+10 bytes
Fixed Size                 12449288 bytes
Variable Size            1.2281E+10 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54657024 bytes
Database mounted.
    1. Flashback the database to the restore point:
    SQL> flashback database to restore point test_1;

Flashback complete.
    1. Open the database with the resetlogs option:
    SQL> alter database open resetlogs;
Database altered.
    1. Resume the RDS Custom automation for this instance.
    2. Verify the backups to confirm that there is an automated snapshot taken after resuming the automation and LatestRestorableTime is pointing to a time stamp after the automation resume time.

    Considerations in using the flashback database feature

    There are a few critical points you should keep in mind while working with the flashback database feature on an RDS Custom for Oracle instance:

    Unified auditing with Amazon RDS Custom for Oracle

    Oracle recommends using the unified auditing feature instead of standard auditing for auditing database activities. Unified auditing comes in two configurations: mixed mode and pure mode. Mixed mode, which is the default unified auditing mode, is intended to introduce unified auditing features and provide a transition from standard auditing. With mixed mode, you can use features of both standard auditing and unified auditing. Pure mode requires database binaries to be relinked with the uniaud_on option, and therefore isn’t supported in Amazon RDS for Oracle. However, you can enable pure mode unified auditing with Amazon RDS Custom for Oracle with the flexibility to relink Oracle binaries with OS access to the database host.

    Refer to Introduction to Auditing for more details on unified auditing and Security auditing in Amazon RDS for Oracle: Part 1 for auditing features in Amazon RDS for Oracle.

    Complete the following steps to enable pure mode unified auditing in an RDS Custom for Oracle instance:

    1. Pause the automation to verify that the customization doesn’t interfere with the RDS Custom automation framework.
    2. Identify the EC2 instance for the RDS Custom for Oracle instance and connect to it using SSH keys or AWS Systems Manager.
    3. Switch to the rdsdb user:
    ec2-user$> sudo su – rdsdb
    1. Shut down the database and listener processes and relink Oracle binaries to enable unified auditing:
    rdsdb>sqlplus "/ as sysdba"
SQL> SQL> select VALUE from v$option where parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

SQL>exit

rdsdb> ps -eaf|grep tns   to find the listener name

rdsdb>lsnrctl stop   for all listeners running on the host.

rdsdb>sqlplus "/ as sysdba" 

SQL>shutdown immediate

SQL>exit

rdsdb>cd $ORACLE_HOME/rdbms/lib

rdsdb>make -f ins_rdbms.mk uniaud_on ioracle

rdsdb>sqlplus "/ as sysdba"

SQL>startup

SQL> select VALUE from v$option where parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE
    1. Resume automation for the instance to enable the automation and monitoring framework of RDS Custom.

    Considerations in using pure mode unified auditing

    There are a few considerations when using pure mode unified auditing with Amazon RDS Custom for Oracle:

    • When an RDS Custom for Oracle instance undergoes a minor or major version upgrade with CEV changes, the Oracle Database binaries are replaced with a new version of binaries, which resets the unified auditing setting. Therefore, unified auditing must be re-enabled with the make command each time the instance undergoes a CEV change.
    • Additionally, when unified auditing records can’t be written to the database, they’re spilled over to *.bin files in AUDIT_FILE_DEST or $ORACLE_BASE/audit/$ORACLE_SID. This can cause space pressure on the /rdsdbbin mount point, which can lead to unavailability of the RDS Custom for Oracle instance. To avoid this, verify that there is sufficient space for unified audit records in the database. This issue is discussed in MOS Doc ID 2193721.1.

    HugePages on Amazon RDS Custom for Oracle

    HugePages is crucial for faster Oracle Database performance on Linux if you have an instance with large RAM and SGA. It is recommended to use HugePages on Amazon RDS Custom for Oracle for instances with 16GiB of RAM and above because it provides many benefits, such as less overhead for memory operations, less memory usage, no swapping of SGA components, and no kswapd operations. HugePages configuration for your RDS Custom for Oracle instance helps improve the stability and performance of the database layer, especially when the workload is memory intensive.

    As of this writing, Amazon RDS Custom for Oracle comes without HugePages configured with the default provisioning process. However, with the flexibility offered by Amazon RDS Custom for Oracle, we can customize the configuration to enable HugePages as required by the workload.

    Refer to MOS Notes Oracle Linux: HugePages on Oracle Linux 64-bit (Doc ID 361468.1) and Oracle Linux: HugePages What It Is… and What It Is Not… (Doc ID 361323.1) to learn more about using HugePages for Oracle databases. The following steps explain the process to enable HugePages on an RDS Custom for Oracle instance:

    1. Pause the automation to verify that the customization doesn’t interfere with the RDS Custom automation framework.
    2. Disable Automatic Memory Management (AMM). (Amazon RDS Custom for Oracle comes with AMM enabled by default, which is not supported by HugePages.)
    SQL> create pfile='/home/rdsdb/spfile_orig.par' from spfile;
    • Set the appropriate values in the spfile for memory-related parameters based on step a. In this example, we use Automatic Shared Memory Management and Automatic PGA Management:
    SQL>alter system set memory_target=0 scope=spfile;
SQL>alter system set memory_max_target=0 scope=spfile;
SQL>alter system set sga_target=20G scope=spfile;
SQL>alter system set sga_max_size=20G scope=spfile;
SQL>alter system set pga_aggregate_target =5G scope=spfile;
SQL>alter system set use_large_pages=TRUE scope=spfile;
    • Bounce the database for the parameters to take effect (although the use_large_pages parameter is set, HugePages will not be used for SGA because HugePages isn’t configured at the OS level):
    SQL>shutdown immediate
SQL>startup
    1. Identify the number of HugePages required to support current SGA configuration.

    The required number of HugePages can be identified as (SGA (in KB) / Hugepagesize) + 10, where Hugepagesize is 2048 from the output of grep Hugepagesize /proc/meminfo.

    For example, if SGA is 20GiB, then the number of HugePages = (20971520/2048)+10 = 10250.

    Oracle has published a shell script in MOS Note: Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1) to identify the setting of vm.hugetlb_pool to support the current SGA configuration. The following screenshot shows an example of executing the shell script for estimating the HugePages requirements.

    1. Configure the OS to support HugePages for Oracle SGA:
    • Set the memlock user limit in the /etc/security/limits.conf file. Set the value (in KB) slightly higher than SGA+PGA. For example, if SGA+PGA = 25 GB, you may set: Number or HugePages * Hugepagesize = minimum Memlock:
    rdsdb soft memlock  26214400
rdsdb hard memlock 26214400
    • Log in to the rdsdb OS user and verify the memlock setting:
    ulimit -l
    • Edit the file /etc/sysctl.conf and set the vm.nr_hugepages parameter:
    vm.nr_hugepages = 10242
    1. Identify the EC2 instance hosting the RDS Custom for Oracle instance:
    • On the Amazon RDS console, in the navigation pane, choose Databases, then choose the RDS Custom DB instance to which you want to connect.
    • Choose Configuration.
    • Note the resource ID value. For example, the resource ID might be db-ABCDEFGHIJKLMNOPQRS0123456.
    • On the Amazon EC2 console, in the navigation pane, choose Instances.
    • Find the name of your EC2 instance, and then choose the instance ID associated with it. For example, the EC2 instance ID might be i-abcdefghijklm01234.
    1. Reboot the EC2 instance.
    2. Verify the HugePages setting when the instance comes back online:
    Last login: Sun Jan 22 17:24:27 UTC 2023 on pts/0
[root@ip-10-0-2-83 ~]# grep HugePages /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:   10242
HugePages_Free:       27
HugePages_Rsvd:       26
HugePages_Surp:        0
    1. Verify the HugePages allocation for SGA from the alert.log messages for instance startup.

    The following screenshot shows the excerpts from alert.log indicating HugePages allocation.

    1. Resume RDS Custom automation if the instance hasn’t automatically resumed automation.

    Considerations in using HugePages

    There are a few considerations when using HugePages with Amazon RDS Custom for Oracle:

    • HugePages should be enabled on the primary instance and existing replica instances independently. However, if a replica is created from a primary instance after HugePages is enabled, the replica will inherit the OS settings from the primary. The database-level configurations such as disabling AMM need to be repeated on the replica.
    • Choose an instance of the same or higher memory configuration as the primary when creating a replica for a primary instance that has HugePages configured. This is to avoid potential memory errors due to inappropriate HugePages settings for lower memory configurations. The replica instance can later be scaled up or down with optimal HugePages settings. This is also the case when restoring an RDS Custom for Oracle instance from a manual snapshot or automated backup. The database-level configurations such as disabling AMM need to be repeated on the target instance after the scaling operation is complete with the correct HugePages settings at the OS level.
    • When scaling up an RDS Custom instance with HugePages enabled, the settings need to be adjusted after the scaling operation based on the memory availability on the new instance class. When scaling down an RDS Custom instance with HugePages enabled, HugePages settings need to be adjusted according to the memory configuration of the new instance before attempting the scale-down operation. The database-level configurations such as disabling AMM need to be repeated on the instance when the scaling operation is complete.

    Conclusion

    Amazon RDS Custom for Oracle allows you customize your database environment to help you meet the various requirements of the dependent application, in addition to offering the benefits of a managed service. In this post, we discussed how to use the flashback database feature and restore points, manage database features, enable unified auditing, and HugePages configuration. In Part 1 of this series, we discussed customizing the time zone and character set of the database. In Part 2, we covered 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.

    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 Toronto. 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 Software Development Manager 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.