Transition roles for an Oracle PeopleSoft application on Amazon RDS Custom for Oracle
Created by sampath kathirvel (AWS)
Environment: Production | Technologies: Databases; Infrastructure | Workload: Oracle |
AWS services: Amazon RDS |
Summary
To run the Oracle PeopleSoft enterprise resource planning (ERP) solution on Amazon Web Services (AWS), you can use Amazon Relational Database Service (Amazon RDS) or Amazon RDS Custom for Oracle, which supports legacy, custom, and packaged applications that require access to the underlying operating system (OS) and database environment. For key factors to consider when planning a migration, see Oracle database migration strategies in AWS Prescriptive Guidance.
This pattern focuses on the steps to perform an Oracle Data Guard switchover, or role transition, for a PeopleSoft application database running on Amazon RDS Custom as the primary database with a read replica database. The pattern includes steps to configure fast-start failover (FSFO). During this process, the databases in the Oracle Data Guard configuration continue to function in their new roles. Typical use cases for Oracle Data Guard switchover are disaster recovery (DR) drills, scheduled maintenance activities on databases, and Standby-First Patch Apply rolling patches. For more information, see the blog post Reduce database patching downtime in Amazon RDS Custom.
Prerequisites and limitations
Prerequisites
Limitations
Product versions
Architecture
Technology stack
Target architecture
The following diagram shows an Amazon RDS Custom DB instance and an Amazon RDS Custom read replica. Oracle Data Guard provides role transition during failover for DR.
For a representative architecture using Oracle PeopleSoft on AWS, see Set up a highly available PeopleSoft architecture on AWS.
AWS services
Amazon RDS Custom for Oracle is a managed database service for legacy, custom, and packaged applications that require access to the underlying OS and database environment.
AWS Secrets Manager helps you replace hardcoded credentials in your code, including passwords, with an API call to Secrets Manager to retrieve the secret programmatically. In this pattern, you retrieve the database user passwords from Secrets Manager for RDS_DATAGUARD with the secret name do-not-delete-rds-custom-+<<RDS Resource ID>>+-dg.
Other services
Best practices
For your production deployment, we recommend launching the observer instance in a third Availability Zone, separate from the primary and read replica nodes.
Epics
| Task | Description | Skills required |
|---|
Pause database automation for both the primary and the replica. | Although the RDS Custom automation framework doesn't interfere with the role transition process, it's a good practice to pause automation during Oracle Data Guard switchover. To pause and resume RDS Custom database automation, follow the instructions at Pausing and resuming RDS Custom automation. | Cloud administrator, DBA |
Check the Oracle Data Guard status. | To check the Oracle Data Guard status, log in to the primary database. This pattern includes code for using a multitenant container database (CDB) or a non-CDB instance. Non-CDB -bash-4.2$ dgmgrl RDS_DATAGUARD@RDS_CUSTOM_ORCL_A
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Nov 28 20:55:50 2022
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.
Password:
Connected to "ORCL_A"
Connected as SYSDG.
DGMGRL> show configuration
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
orcl_a - Primary database
orcl_d - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 59 seconds ago)
DGMGRL>
CDB CDB-bash-4.2$ dgmgrl C##RDS_DATAGUARD@RDS_CUSTOM_RDSCDB_A
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 18 06:13:07 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Password:
Connected to "RDSCDB_A"
Connected as SYSDG.
DGMGRL> show configuration
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
rdscdb_a - Primary database
rdscdb_b - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 52 seconds ago)
DGMGRL>
| DBA |
Verify the instance role. | Open the AWS Management Console, and navigate to the Amazon RDS console. In the database’s Replication section, on the Connectivity & security tab, verify the instance role for the primary and replica. The primary role should match the Oracle Data Guard primary database, and the replica role should match the Oracle Data Guard physical standby database. | Cloud administrator, DBA |
Perform the switchover. | To perform the switchover, connect to DGMGRL from the primary node. Non-CDB DGMGRL> switchover to orcl_d;
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl_d"
Connecting ...
Connected to "ORCL_D"
Connected as SYSDG.
New primary database "orcl_d" is opening...
Operation requires start up of instance "ORCL" on database "orcl_a"
Starting instance "ORCL"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORCL_A"
Database mounted.
Database opened.
Connected to "ORCL_A"
Switchover succeeded, new primary is "orcl_d"
DGMGRL>
CDB DGMGRL> switchover to rdscdb_b
Performing switchover NOW, please wait...
New primary database "rdscdb_b" is opening...
Operation requires start up of instance "RDSCDB" on database "rdscdb_a"
Starting instance "RDSCDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "RDSCDB_A"
Database mounted.
Database opened.
Connected to "RDSCDB_A"
Switchover succeeded, new primary is "rdscdb_b"
| DBA |
Verify the Oracle Data Guard connection. | After switchover, verify the Oracle Data Guard connection from the primary node to DGMGRL. Non-CDB DGMGRL> show configuration;
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
orcl_d - Primary database
orcl_a - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 60 seconds ago)
DGMGRL>
DGMGRL> show configuration lag;
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
orcl_d - Primary database
orcl_a - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 44 seconds ago)
DGMGRL>
CDB DGMGRL> show configuration
DGMGRL> show configuration
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
rdscdb_b - Primary database
rdscdb_a - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 52 seconds ago)
DGMGRL>
DGMGRL> show configuration lag
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
rdscdb_b - Primary database
rdscdb_a - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 53 seconds ago)
DGMGRL>
| DBA |
Verify the instance role on the Amazon RDS console. | After you perform the role switch, the Amazon RDS console shows the new roles under the Replication section on the Connectivity & Security tab under Databases. It might take a few minutes for Replication state to update from empty to Replicating. | DBA |
| Task | Description | Skills required |
|---|
Reset the switchover. | Set the switchover back to the primary node. | DBA |
Install and start the observer. | An observer process is a DGMGRL client component, typically running in a different machine from the primary and standby databases. The ORACLE HOME installation for the observer can be an Oracle Client Administrator installation, or you can install either Oracle Database Enterprise Edition or Personal Edition. For more information about observer installation for your database release, see Installing and Starting the Observer. To configure high availability for the observer process, you might want to do the following: Enable EC2 instance automatic recovery for the EC2 instance running your observer. You need to automate the observer startup process as part of the OS startup. Deploy an observer in the EC2 instance and configure an Amazon EC2 Auto Scaling group with size one (1). In the event of EC2 instance failure, the automatic scaling group automatically spins up another EC2 instance.
For Oracle 12c Release 2 and later, you can deploy up to three observers. One observer is the primary observer, and the rest are backup observers. When the primary observer fails, one of the backup observers takes the primary role. | DBA |
Connect to DGMGRL from the observer host. | The observer host is configured with tnsnames.ora entries for primary and standby database connectivity. You can enable FSFO with maximum performance protection mode as long as data loss is within the FastStartFailoverLagLimit configuration (value in seconds), However, you must use maximum availability protection mode to work to achieve zero data loss (RPO=0). Non-CDB DGMGRL> show configuration;
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
orcl_a - Primary database
orcl_d - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 58 seconds ago)
DGMGRL> show configuration lag
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
orcl_a - Primary database
orcl_d - Physical standby database
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 5 seconds ago)
DGMGRL>
CDB -bash-4.2$ dgmgrl C##RDS_DATAGUARD@RDS_CUSTOM_RDSCDB_A
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 18 06:55:09 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Password:
Connected to "RDSCDB_A"
Connected as SYSDG.
DGMGRL> show configuration
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
rdscdb_a - Primary database
rdscdb_b - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 18 seconds ago)
DGMGRL>
| DBA |
Modify the standby database to be the failover target. | Connect from either the primary node or the observer node to one standby database. (Although your onfiguration could have mulitiple standby databases, you need to connect to only one at this time.) Non-CDB DGMGRL> edit database orcl_a set property FastStartFailoverTarget='orcl_d';
Property "faststartfailovertarget" updated
DGMGRL> edit database orcl_d set property FastStartFailoverTarget='orcl_a';
Property "faststartfailovertarget" updated
DGMGRL> show database orcl_a FastStartFailoverTarget;
FastStartFailoverTarget = 'orcl_d'
DGMGRL> show database orcl_d FastStartFailoverTarget;
FastStartFailoverTarget = 'orcl_a'
DGMGRL>
CDB DGMGRL> edit database orcl_a set property FastStartFailoverTarget='rdscdb_b';
Object "orcl_a" was not found
DGMGRL> edit database rdscdb_a set property FastStartFailoverTarget='rdscdb_b';
Property "faststartfailovertarget" updated
DGMGRL> edit database rdscdb_b set property FastStartFailoverTarget='rdscdb_a';
Property "faststartfailovertarget" updated
DGMGRL> show database rdscdb_a FastStartFailoverTarget;
FastStartFailoverTarget = 'rdscdb_b'
DGMGRL> show database rdscdb_b FastStartFailoverTarget;
FastStartFailoverTarget = 'rdscdb_a'
DGMGRL>
| DBA |
Configure FastStartFailoverThreshold for the connection to DGMGRL. | The default value is 30 seconds in Oracle 19c, and the minimum value is 6 seconds. A lower value can potentially shorten the recovery time objective (RTO) during the failover. A higher value helps reduce the chance of unnecessary failover transient errors on the primary database. The RDS Custom for Oracle automation framework monitors database health and performs corrective actions every few seconds. Therefore, we recommend setting FastStartFailoverThreshold to a value higher than 10 seconds. The following example configures the threshold value at 35 seconds. Non-CBD or CDB DGMGRL> edit configuration set property FastStartFailoverThreshold=35;
Property "faststartfailoverthreshold" updated
DGMGRL> show configuration FastStartFailoverThreshold;
FastStartFailoverThreshold = '35'
DGMGRL>
| DBA |
Enable FSFO by connecting to DGMGRL from the primary or observer node. | If the database doesn’t have Flashback Database enabled, the warning message ORA-16827 appears. The optional flashback database helps automatically reinstate failed primary databases to a point in time before failover if the FastStartFailoverAutoReinstate configuration property is set to TRUE (which is the default). Non-CDB DGMGRL> enable fast_start failover;
Warning: ORA-16827: Flashback Database is disabled
Enabled in Zero Data Loss Mode.
DGMGRL>
DGMGRL> show configuration
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
orcl_a - Primary database
Warning: ORA-16819: fast-start failover observer not started
orcl_d - (*) Physical standby database
Warning: ORA-16819: fast-start failover observer not started
Fast-Start Failover: Enabled in Zero Data Loss Mode
Configuration Status:
WARNING (status updated 29 seconds ago)
DGMGRL>
CDB DGMGRL> enable fast_start failover;
Warning: ORA-16827: Flashback Database is disabled
Enabled in Zero Data Loss Mode.
DGMGRL> show configuration;
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
rdscdb_a - Primary database
Warning: ORA-16819: fast-start failover observer not started
rdscdb_b - (*) Physical standby database
Fast-Start Failover: Enabled in Zero Data Loss Mode
Configuration Status:
WARNING (status updated 11 seconds ago)
DGMGRL>
| DBA |
Start the observer for FSFO monitoring, and verify the status. | You can start the observer before or after you enable FSFO. If FSFO is already enabled, the observer immediately begins monitoring the status and connections to the primary and target standby databases. If FSFO is not enabled, the observer doesn’t start monitoring until after FSFO is enabled. When you start the observer, the primary DB configuration will be displayed without any error messages, as evidenced by the previous show configuration command. Non-CDB DGMGRL> start observer;
[W000 2022-12-01T06:16:51.271+00:00] FSFO target standby is orcl_d
Observer 'ip-10-0-1-89' started
[W000 2022-12-01T06:16:51.352+00:00] Observer trace level is set to USER
DGMGRL> show configuration
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
orcl_a - Primary database
orcl_d - (*) Physical standby database
Fast-Start Failover: Enabled in Zero Data Loss Mode
Configuration Status:
SUCCESS (status updated 56 seconds ago)
DGMGRL>
DGMGRL> show observer
Configuration - rds_dg
Primary: orcl_a
Active Target: orcl_d
Observer "ip-10-0-1-89" - Master
Host Name: ip-10-0-1-89
Last Ping to Primary: 1 second ago
Last Ping to Target: 1 second ago
DGMGRL>
CDB DGMGRL> start observer;
Succeeded in opening the observer file "/home/oracle/fsfo_ip-10-0-1-56.dat".
[W000 2023-01-18T07:31:32.589+00:00] FSFO target standby is rdscdb_b
Observer 'ip-10-0-1-56' started
The observer log file is '/home/oracle/observer_ip-10-0-1-56.log'.
DGMGRL> show configuration
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
rdscdb_a - Primary database
rdscdb_b - (*) Physical standby database
Fast-Start Failover: Enabled in Zero Data Loss Mode
Configuration Status:
SUCCESS (status updated 12 seconds ago)
DGMGRL>
DGMGRL> show observer;
Configuration - rds_dg
Primary: rdscdb_a
Active Target: rdscdb_b
Observer "ip-10-0-1-56" - Master
Host Name: ip-10-0-1-56
Last Ping to Primary: 1 second ago
Last Ping to Target: 2 seconds ago
DGMGRL>
| DBA |
Verify the failover. | In this scenario, a failover test can be performed by manually stopping the primary EC2 instance. Before stopping the EC2 instance, use the tail command to monitor the observer log file based on your configuration. Use DGMGRL to log in to standby database orcl_d with user RDS_DATAGUARD, and check the Oracle Data Guard status. It should show that orcl_d is the new primary database. Note: In this failover testing scenario, orcl_d is the non-CDB database. Before failover, the flashback database has been enabled on orcl_a. After the former primary database returns online and starts in MOUNT state, the observer reinstates it into a new standby database. The reinstated database acts as the FSFO target for the new primary database. You can verify the details in observer logs. DGMGRL> show configuration
Configuration - rds_dg
Protection Mode: MaxAvailability
Members:
orcl_d - Primary database
Warning: ORA-16824: multiple warnings, including fast-start failover-related warnings, detected for the database
orcl_a - (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: Enabled in Zero Data Loss Mode
Configuration Status:
WARNING (status updated 25 seconds ago)
DGMGRL>
The following shows example output in observer.log. $ tail -f /tmp/observer.log
Unable to connect to database using rds_custom_orcl_a
[W000 2023-01-18T07:50:32.589+00:00] Primary database cannot be reached.
[W000 2023-01-18T07:50:32.589+00:00] Fast-Start Failover threshold has expired.
[W000 2023-01-18T07:50:32.590+00:00] Try to connect to the standby.
[W000 2023-01-18T07:50:32.590+00:00] Making a last connection attempt to primary database before proceeding with Fast-Start Failover.
[W000 2023-01-18T07:50:32.591+00:00] Check if the standby is ready for failover.
[S002 2023-01-18T07:50:32.591+00:00] Fast-Start Failover started...
2023-01-18T07:50:32.591+00:00
Initiating Fast-Start Failover to database "orcl_d"...
[S002 2023-01-18T07:50:32.592+00:00] Initiating Fast-start Failover.
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl_d"
2023-01-18T07:55:32.101+00:00
[S002 2023-01-18T07:55:32.591+00:00] Fast-Start Failover finished...
[W000 2023-01-18T07:55:32.591+00:00] Failover succeeded. Restart pinging.
[W000 2023-01-18T07:55:32.603+00:00] Primary database has changed to orcl_d.
[W000 2023-01-18T07:55:33.618+00:00] Try to connect to the primary.
[W000 2023-01-18T07:55:33.622+00:00] Try to connect to the primary rds_custom_orcl_d.
[W000 2023-01-18T07:55:33.634+00:00] The standby orcl_a needs to be reinstated
[W000 2023-01-18T07:55:33.654+00:00] Try to connect to the new standby orcl_a.
[W000 2023-01-18T07:55:33.654+00:00] Connection to the primary restored!
[W000 2023-01-18T07:55:35.654+00:00] Disconnecting from database rds_custom_orcl_d.
[W000 2023-01-18T07:55:57.701+00:00] Try to connect to the new standby orcl_a.
ORA-12170: TNS:Connect timeout occurred
| DBA |
| Task | Description | Skills required |
|---|
Create and start the service in the primary database. | You can avoid application configuration changes during a role transition by using a TNS entry that contains both the primary and standby database endpoints in the configuration. You can define two role-based database services to support both read/write and read-only workloads. In the following example, orcl_rw is the read/write service that’s active on the primary database. orcl_ro is the read-only service and is active on the standby database that has been opened in read-only mode. SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
SQL> exec dbms_service.create_service('orcl_rw','orcl_rw');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.create_service('orcl_ro','orcl_ro');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.start_service('orcl_rw');
PL/SQL procedure successfully completed.
SQL>
| DBA |
Start the service in the standby database. | To start the service in the read-only standby database, use the following code. SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ ONLY WITH APPLY
SQL> exec dbms_service.start_service('orcl_ro');
PL/SQL procedure successfully completed.
SQL>
| DBA |
Automate starting the service when the primary DB is restarted. | To automatically start the service in the primary database when it’s restarted, use the following code. SQL> CREATE OR REPLACE TRIGGER TrgDgServices after startup on database
DECLARE
db_role VARCHAR(30);
db_open_mode VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE;
IF db_role = 'PRIMARY' THEN
DBMS_SERV 2 ICE.START_SERVICE('orcl_rw');
END IF;
IF db_role = 'PHYSICAL STANDBY' AND db_open_mode LIKE 'READ ONLY%' THEN
DBMS_SERVICE.START_SERVICE('orcl_ro');
END IF;
END;
/
Trigger created.
SQL>
| DBA |
Configure a connection between the read/write and read-only databases. | You can use the following application-configuration example for the read/write and read-only connection. ORCL_RW = (DESCRIPTION =
(CONNECT_TIMEOUT= 120)(RETRY_COUNT=20)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=3)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=devpsftdb.******.us-west-2.rds.amazonaws.com)(PORT=1521))
(ADDRESS = (PROTOCOL = TCP)(HOST=psftread.******.us-west-2.rds.amazonaws.com)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME = orcl_rw))
)
ORCL_RO = (DESCRIPTION =
(CONNECT_TIMEOUT= 120)(RETRY_COUNT=20)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=3)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=devpsftdb.******.us-west-2.rds.amazonaws.com)(PORT=1521))
(ADDRESS = (PROTOCOL = TCP)(HOST=psftread.******.us-west-2.rds.amazonaws.com)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME = orcl_ro))
)
| DBA |
Related resources
