Reduce database patching downtime in Amazon RDS Custom for Oracle using Oracle Data Guard Standby-First Patch Apply

Most enterprise customers have high uptime requirements for their mission-critical applications. Their businesses can’t afford application downtime and have requirements for minimal or near-zero downtime patching ability for their application and databases. The infrastructure used by the backend Oracle databases for those business-critical applications must be able to support these high uptime needs. When migrating such workloads to AWS, customers also want to modernize and reduce the operational overhead by moving their databases to managed database service offerings like Amazon Relational Database Service (Amazon RDS) for Oracle or Amazon RDS Custom for Oracle.

Amazon RDS Custom is a managed database service for legacy, custom, and packaged applications that require access to the underlying OS and DB environment. Amazon RDS Custom for Oracle provides the flexibility to customize your database, underlying server, and operating system configurations to support applications that require such customizations. A custom engine version (CEV) for Amazon RDS Custom for Oracle is a binary volume snapshot of a database engine and specific Amazon Machine Image (AMI). You store your database installation files in Amazon Simple Storage Service (Amazon S3). Amazon RDS Custom uses the installation files to create your CEV for you. To do a minor version upgrade on your RDS Custom DB instance, you have to create a new CEV with the quarterly release update (RU) patches and then modify the RDS Custom instance to use a new CEV. This type of approach requires a complete downtime for upgrading the instance to the next minor version.

In this post, we show how database administrators and data architects can use the Oracle Data Guard Standby-First Patch Apply method to apply quarterly RUs in RDS Custom for Oracle instances to reduce patching downtime. We also discuss the best practices and considerations while using this approach in Amazon RDS Custom.

Solution overview

Oracle Data Guard Standby-First Patch Apply provides support for different database home software between a primary database and its physical standby databases for the purpose of applying and validating Oracle patches on your standby and primary instance one at a time with minimal risk to the primary database. This helps reduce patching downtime.

Oracle database quarterly RUs contains database patches and Java patches (JVM). If Oracle database has the JVM option enabled, then JVM patches need to be applied. Oracle doesn’t categorize JVM patches (per patch readme for the last six quarterly RUs) as Data Guard Standby First Installable. In the following sections, we discuss the scenarios of applying the quarterly RUs to the database both with and without JVM enabled.

In scenario 1, when the JVM option isn’t enabled in the database, the high-level steps include:

Pause RDS Custom automation.
Apply quarterly RUs using Data Guard Standby-First Patch Apply, where downtime is equivalent to the time it takes to switchover.
Resume RDS Custom automation.

In scenario 2, when the JVM option is enabled in the database, the high-level steps include:

Pause RDS Custom automation.
Refer to the Oracle patch readme and if the JVM patches aren’t standby-first installable, then take a full downtime of both primary and secondary and apply the quarterly RUs.
Resume RDS Custom automation.

Considerations when using Data Guard Standby-First Patch Apply

Consider the following when using Data Guard Standby-First Patch Apply:

Refer to Oracle Data Guard Best Practices for configuring, tuning, and troubleshooting Data Guard deployment in Amazon RDS Custom.
The process described in this post is applicable only for those patches that Oracle categorizes as Data Guard Standby-First Installable, which is mentioned in the patch readme.
Refer to Oracle Doc 2217053.1 for considerations on when to choose Data Guard Standby-First Patch Apply.
The purpose of this post is to demonstrate the use of Data Guard Standby-First Patch Apply in RDS Custom for applicable patches and not to test the functionality of the Data Guard Standby-First feature.
When using Data Guard Standby-First Patch Apply, you’re responsible for validating patches for this feature and also testing the behavior of your application, especially during the data patch. During the testing for this blog post, the data patch was applied with database in open mode as per the instructions in the patch readme.
The process described in this post has been tested on RDS Custom for Oracle 19c database instances.
This is a manual patching process; you have to apply Oracle quarterly RUs on the existing ORACLE_HOME so that RDS Custom automation doesn’t break.
With this manual method, our control plane is unaware of the patch level on the database instance. Therefore, if you later decide to go for patching via minor version patching by creating a new CEV, you have to work on any one-off patches and also remember what Oracle RU version you’re on.
As a best practice, we recommend taking a manual snapshot prior to starting this patching process. You can use this snapshot as a fallback strategy if you run into any issues and need to fall back to the time prior to starting this activity. Amazon RDS Custom pauses the upload of archived redo logs when the automation is set to ALL_PAUSED. The same PITR considerations apply here as described in PITR considerations for RDS Custom for Oracle.
Because the patches are applied using the Oracle native opatch process, you can also use the opatch rollback command if you have to revert back the patches. Refer to the Oracle patch readme for more details on the rollback process.
When a Data Guard role transition occurs as part of the switchover process, the primary endpoint changes. Therefore, to achieve seamless client connectivity, you can use two methods:
- TNS aliases – This method is for applications that use tnsnames. You can define two role-based database services to support both read/write and read-only workloads. For example, demo_rw is the read/write service, always on, and run on the primary database. demo_ro is the read-only service, which runs on the standby database that has been opened for read-only. Refer to Oracle Doc ID 1429223.1 for details on how to configure client failover for your Data Guard connection using database services. Create a database trigger to start these services when the database is restarted.
- DNS CNAME – In this method, you can create a private hosted zone in Amazon Route 53 and create a CNAME for the primary RDS Custom endpoint. When a Data Guard switchover occurs, you can update the CNAME with the endpoint details of the new primary. This way, you can avoid making changes at the application level when the endpoint connection changes.

Prerequisites

To follow along with this post, complete the following prerequisites. In this example we have created 19c Oracle database with April and July 2021 RU’s, a similar approach can be used for other versions as well.

Create an RDS Custom for Oracle DB instance with 19c April 2021 RU and no JVM installed. For this post, let’s call this demodb1_a. For instructions, see Working with RDS Custom for Oracle.
Create a read replica for the demodb1_a instance using the replica API. The replica will be created in mounted mode. Let’s call this demodb1_b. For instructions, refer to Working with read replicas for RDS Custom for Oracle.
Create another RDS Custom for Oracle DB instance with 19c April 2021 RU along with JVM patches, called demodb2_a.
Create a read replica for the demodb2_a instance using the replica API. The replica will be created in mounted mode. Let’s call this demodb2_b.
Data Guard configuration can be managed by Data Guard broker.

Note: This solution involves the creation and utilization of new AWS resources. Therefore, it will incur costs on your account. Refer to AWS Pricing for more information.

Scenario 1: Apply 19c July 2021 RU to the DB instance that doesn’t have JVM installed

In this test scenario, we upgrade the demodb1 RDS Custom database instance from 19c April 2021 RU to 19c July 2021 RU. The demodb1 instance doesn’t have any JVM patches, and so we use the standby-first patch apply method to upgrade this instance. With this approach, you can reduce the patching downtime significantly; the actual downtime is equal to the time it takes for the switchover process.

Before starting to customize your Amazon RDS Custom for Oracle, you must pause the automation to ensure that your customizations don’t interfere with the RDS Custom automation framework. You can pause the automation either using the Amazon RDS console or the AWS Command Line Interface (AWS CLI). For more information, refer to Pausing and resuming RDS Custom automation.

Pause automation on the RDS Custom instance demodb1_a and its replica demodb1_b using the following AWS CLI command:

aws rds modify-db-instance --db-instance-identifier custom-ORCL --automation-mode all-paused  --resume-full-automation-mode-minute 120 --region us-east-2

To connect the database, you need to connect to the underlying Amazon Elastic Compute Cloud (Amazon EC2) instance using SSH keys or AWS Systems Manager (see Connecting to your RDS Custom DB instance using AWS Systems Manager).
Once you’re logged in as ec2-user, you can switch to rdsdb user, which owns database binaries.
Then you should be able to connect to the instance with sysdba privileges:
```
$sudo su - rdsdb
$sqlplus / as sysdba
```
Next, you retrieve the database passwords for sys/system and rds_dataguard users for Amazon RDS Custom for Oracle from AWS Secrets Manager.
On the Amazon RDS console, in the navigation pane, choose Databases, and then choose the primary database.
Choose Configuration and note the resource ID (it will be in the format db-R4ZMRQ7YJQGY7EL7I3AUVVRGVA).
On the Secrets Manager console, choose the secret that has the same name as do-not-delete-custom-<resource_id>-xxxx (sys user) and do-not-delete-custom-<resource_id>-xxxx-dg (for rds_dataguard)
Choose Retrieve secret value and note the password for database users.

Stop the listener on the standby instance:

-bash-4.2$ lsnrctl stop l_demodb1_001
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2021 07:03:57

Copyright (c) 1991, 2020, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=x.x.0.253)))
The command completed successfully

Stop the listener for Data Guard on the standby instance:

bash-4.2$ lsnrctl stop l_demodb1_dg

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2021 07:03:51

Copyright (c) 1991, 2020, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1140)(HOST=x.x.0.253)))
The command completed successfully

Shut down the standby instance:

-bash-4.2$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Nov 17 10:53:45 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect RDS_DATAGUARD/dg_12345$@demodb1_b;
Connected to "DEMODB1_B"
Connected as SYSDG.
DGMGRL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
DGMGRL> exit

Run opatch prereq to ensure there are no conflicts. Refer to the patch readme to ensure the patch is Standby-First Patch Installable.

bash-4.2$unzip p32545013_190000_Linux-x86-64.zip
bash-4.2$ cd 32545013 
bash-4.2$ pwd
/tmp/32545013
bash-4.2$ export PATH=$ORACLE_HOME/OPatch:$PATH
bash-4.2$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.23

Install the patch by running the following command on your standby instance (don’t run the datapatch at this point):

bash-4.2$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /rdsdbbin/oracle
Central Inventory : /rdsdbbin/oraInventory
from : /rdsdbbin/oracle/oraInst.loc
OPatch version : 12.2.0.1.23
OUI version : 12.2.0.7.0

Restart the standby instance after the patch has been applied to the standby database ORACLE_HOME:

DGMGRL> connect RDS_DATAGUARD/dg_12345$@demodb1_b;
Connected to an idle instance.
Connected as SYSDG.
DGMGRL> startup mount
Connected to "DEMODB1_B"
ORACLE instance started.
Database mounted.

The Data Guard broker will automatically restart the media recovery.

Verify the patch has been successfully installed by running the following command on the standby instance:
```
opatch lsinventory
```
Start the database listener on the standby instance:
```
bash-4.2$ lsnrctl start l_demodb1_001
```
Start the Data Guard listener on the standby instance:
```
bash-4.2$ lsnrctl start l_demodb1_dg
```
Downtime starts here, which is equivalent to the Data Guard instance switchover time.

Validate the standby database to see if it’s ready for switchover, then switch over the primary to the standby database:

Connect to standby 
-bash-4.2$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Nov 17 10:37:02 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect RDS_DATAGUARD/dg_12345$@demodb1_b;
Connected to "DEMODB1_b"
Connected as SYSDG.

***Validate the status
DGMGRL> validate database verbose demodb1_b
  Database Role:     Physical standby database
  Primary Database:  demodb1_b

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)
  
  Flashback Database Status:
    demodb1_b:  Off
    demodb1_a:  Off
  Capacity Information:
    Database  Instances        Threads
    demodb1_b    1                1
    demodb1_a    1                1
  Managed by Clusterware:
    demodb1_b:  NO
    demodb1_a:  NO

    Validating static connect identifier for the primary database demodb1_a...
    The static connect identifier allows for a connection to database "demodb1_a".

  Temporary Tablespace File Information:
    demodb1_b TEMP Files:  1
    demodb1_a TEMP Files:  1

  Data file Online Move in Progress:
    demodb1_b:  No
    demodb1_a:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 0 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:  Yes
    Gap Status:    No Gap
    Transport Lag:  0 seconds (computed 0 seconds ago)
    Transport Status:  Success

Connect to Primary database: 
DGMGRL> connect RDS_DATAGUARD/dg_12345$@demodb1_a;
Connected to "DEMODB1_A"
Connected as SYSDG.

Switchover to standby database

DGMGRL> switchover to demodb1_b
Performing switchover NOW, please wait...
Operation requires a connection to database "demodb1_b"
Connecting ...
Connected to "DEMODB_B"
Connected as SYSDG.
New primary database "demodb1_b" is opening...
Operation requires start up of instance "DEMODB" on database "demodb1_a"
Starting instance "DEMODB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "DEMODB1_A"
Database mounted.
Connected to "DEMODB1_A"
Switchover succeeded, new primary is "demodb1_b"
DGMGRL>
Downtime ends here.

Repeat steps 9–17 to apply the patch on the new standby (old primary) instance.
Connect to the primary database, which is in open and read/write mode and run datapatch. Note the following:
1. Follow the steps in your patch readme.
2. Load the modified SQL files into the database.
3. Run datapatch to complete the post-install SQL deployment for the patch being installed. Apply datapatch on the primary site.
```
% cd $ORACLE_HOME/OPatch
% ./datapatch -verbose
```
After all the activities are complete, we can resume automation on the RDS Custom instances by running the following AWS CLI command:
```
aws rds  modify-db-instance --db-instance-identifier flex-instance-s --automation-mode full --region us-west-2
```

Scenario 2: Apply 19c July 2021 RU to the DB instance that has JVM installed

In this second scenario, we upgrade the demodb2 RDS Custom database instance, which has JVM enabled, from 19c April 2021 RU to 19c July 2021 RU. Oracle releases the quarterly updates as a combo patch that includes database patches and JVM patches. The database patches are generally Standby-First Patch Installable, but Oracle doesn’t categorize the JVM patches as Standby-First Installable, so it’s not recommended to use the Standby-First Patch Apply method when the JVM option is enabled. The downtime in this case is longer because the primary and standby should be down while applying these JVM patches. Refer to Oracle Doc ID 2217053.1 for more details.

Pause automation on the RDS Custom instance demodb2_a and its replica demodb2_b with the following AWS CLI command:

aws rds modify-db-instance --db-instance-identifier custom-ORCL --automation-mode all-paused  --resume-full-automation-mode-minute 120 --region us-east-2

Connect to the underlying EC2 instance using SSH keys or Systems Manager.
After you’re logged in as ec2-user, switch to rdsdb user, which owns database binaries.
Connect to the instance with sysdba privileges:
```
$sudo su - rdsdb
$sqlplus / as sysdba
```
Next, you retrieve the database passwords for sys/system and rds_dataguard users for Amazon RDS Custom for Oracle from Secrets Manager.
On the Amazon RDS console, in the navigation pane, choose Databases, and then choose the primary database .
Choose Configuration and note the resource ID (it will be in the format db-R4ZMRQ7YJQGY7EL7I3AUVVRGVA).
On the Secrets Manager console, choose the secret that has the same name as do-not-delete-custom-<resource_id>-xxxx (sys user) and do-not-delete-custom-<resource_id>-xxxx-dg (for rds_dataguard).
Choose Retrieve secret value and note the password for the database users.

Stop the listener on the standby instance:

-bash-4.2$ lsnrctl stop l_demodb2_001
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2021 07:03:57

Copyright (c) 1991, 2020, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=x.x.0.253)))
The command completed successfully

Stop the listener for Data Guard on the standby instance:

bash-4.2$ lsnrctl stop l_demodb2_dg

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2021 07:03:51

Copyright (c) 1991, 2020, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1140)(HOST=x.x.0.253)))
The command completed successfully

Shut down the standby instance:

-bash-4.2$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Nov 17 10:53:45 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect RDS_DATAGUARD/dg_12345$@demodb2_b;
Connected to "DEMODB2_B"
Connected as SYSDG.
DGMGRL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
DGMGRL> exit

Run opatch prereq to ensure there are no conflicts. (Refer to the steps in your patch readme.)
In the following example, we show the JVM patch (32876380); you can apply the database patch using opatch in a similar fashion:

bash-4.2$unzip p32876380_190000_Linux-x86-64.zip 
bash-4.2$ cd 32876380 
bash-4.2$ pwd
/tmp/32876380
bash-4.2$ export PATH=$ORACLE_HOME/OPatch:$PATH
bash-4.2$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.23

Install the patch by running the following command on your standby instance (don’t run the datapatch at this point).
In the following example, we show the JVM patch (32876380); you can apply the database patch using opatch in a similar fashion:

-bash-4.2$ pwd
/tmp/32876380
-bash-4.2$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /rdsdbbin/oracle
Central Inventory : /rdsdbbin/oraInventory
from : /rdsdbbin/oracle/oraInst.loc
OPatch version : 12.2.0.1.27
OUI version : 12.2.0.7.0

Verify whether the patch has been successfully installed by running the following command:
```
opatch lsinventory
```

Shut down the primary instance and listener running on the server (downtime begins at this point).

-bash-4.2$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Nov 17 10:53:45 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect RDS_DATAGUARD/dg_12345$@demodb2_a;
Connected to "DEMODB2_A"
Connected as SYSDG.
DGMGRL> shutdown immediate

bash-4.2$ lsnrctl start l_demodb2_dg

Repeat steps 9–14 to apply the patch on the primary instance.
Verify whether the patch has been successfully installed by running the following command:
```
opatch lsinventory
```

Start the primary database and listener:

DGMGRL> connect RDS_DATAGUARD/dg_12345$@demodb2_a;
Connected to an idle instance.
Connected as SYSDG.
DGMGRL> startup open
Connected to "DEMODB2_A"

Start the database listener on the primary instance (downtime ends at this point):
```
$ lsnrctl start l_demodb2_001
```
Start the Data Guard listener on the primary instance:
```
$ lsnrctl start l_demodb2_dg
```
Connect to the host where your primary database is, which is in open and in read/write mode, and run the datapatch utility. Note the following:
1. Follow the steps in your patch readme.
2. Load the modified SQL files into the database.
3. Run datapatch to complete the post-install SQL deployment for the patch being installed. Apply datapatch on the primary site.
```
% cd $ORACLE_HOME/OPatch
% ./datapatch -verbose
```
Start the standby database and listener.

Resume automation on RDS Custom instances with the following AWS CLI command:

aws rds  modify-db-instance --db-instance-identifier flex-instance-s --automation-mode full --region us-west-2

Oracle Data Guard Standby-First Patch Apply doesn’t require any additional license; it works with Oracle Data Guard or Active Data Guard, which is supported only on Oracle Enterprise Edition. Oracle Active Data Guard is an additional licensing option on top of Oracle Enterprise Edition. Refer to Permitted Features, Options, and Management Packs by Oracle Database Offering for more details. There is no extra cost from an AWS perspective for using Oracle Data Guard Standby-First Patch Apply in replicas. You can use Oracle Data Guard Standby-First Patch Apply on RDS Custom instances in all commercial AWS Regions that support Amazon RDS Custom for Oracle. Oracle Data Guard Standby-First Patch Apply is supported on all database versions that are supported by Oracle and supported on Amazon RDS Custom, which includes versions 12c, 18c, and 19c.

Summary

In this post, we showed how you can use Oracle’s Data Guard Standby-First Patch Apply method for applying quarterly release updates or one-off updates in an RDS Custom for Oracle instance to reduce the patching downtime. We also went through some of the best practices to follow while using this approach in Amazon RDS Custom for Oracle, and reviewed pricing and licensing considerations when using this method in Amazon RDS Custom for Oracle. Share your thoughts in the comments section.

To learn more about Amazon RDS Custom, refer to the following resources:

About the authors

Yamuna Palasamudram is a Senior Database Specialist Solutions Architect with Amazon Web Services. She works with AWS RDS team, focusing on commercial database engines like Oracle. She enjoys working with customers to help design, deploy, and optimize relational database workloads on AWS.

Manash Kalita is an Amazon Web Services Senior Database Specialist Solutions Architect for ASEAN, having extensive experience in Enterprise Cloud Architecture.

AWS Database Blog