Migrate Oracle PeopleSoft to Amazon RDS Custom
Created by Gaurav Gupta (AWS)
Environment: Production | Source: Amazon EC2 | Target: Amazon RDS Custom |
R Type: Replatform | Workload: Oracle | Technologies: Migration; Infrastructure; Databases |
AWS services: Amazon RDS; Amazon S3; AWS Secrets Manager; Amazon EFS |
Summary
Oracle PeopleSoft is an enterprise resource planning (ERP) solution for enterprise-wide processes. PeopleSoft has a three-tier architecture: client, application, and database. PeopleSoft can be run on Amazon Relational Database Service (Amazon RDS). Now, you can also run PeopleSoft on Amazon RDS Custom, which provides access to the underlying operating system.
Amazon RDS Custom for Oracle is a managed database service for legacy, custom, and packaged applications that require access to the underlying operating system and database environment. When you migrate your Oracle database to Amazon RDS Custom, Amazon Web Services (AWS) can manage backup tasks and high availability, while you can focus on maintaining your PeopleSoft application and functionality. For key factors to consider for a migration, see Oracle database migration strategies in AWS Prescriptive Guidance.
This pattern focuses on the steps to migrate a PeopleSoft database on Amazon Elastic Compute Cloud (Amazon EC2) to Amazon RDS Custom by using an Oracle Recovery Manager (RMAN) backup. It uses an Amazon Elastic File System (Amazon EFS) shared file system between the EC2 instance and Amazon RDS Custom, although you can also use Amazon FSx or any shared drive. The pattern uses an RMAN full backup (sometimes referred to as a level 0 backup).
Prerequisites and limitations
Prerequisites
An Oracle version 19C source database that is running on Amazon EC2 with Oracle Linux 7, Oracle Linux 8, Red Hat Enterprise Linux (RHEL) 7, or RHEL 8. In the examples for this pattern, the source database name is FSDMO92, but this isn’t a requirement.
Note: You can also use this pattern with on-premises Oracle source databases. You must have the appropriate network connectivity between the on-premises network and a virtual private cloud (VPC).
A PeopleSoft 9.2 demo instance.
A single PeopleSoft application tier. However, you can adapt this pattern to work with multiple application tiers.
Amazon RDS Custom configured with at least 8 GB of swap space.
Limitations
This pattern doesn’t support the following configurations:
Setting the database ARCHIVE_LAG_TARGET parameter to a value outside the 60–7200 range
Disabling the DB instance log mode (NOARCHIVELOG)
Turning off the Amazon Elastic Block Store (Amazon EBS) optimized attribute of the EC2 instance
Modifying the original EBS volumes attached to the EC2 instance
Adding new EBS volumes or changing the volume type from gp2 to gp3
Changing the extension format for the LOG_ARCHIVE_FORMAT parameter (requires *.arc)
Multiplexing or changing the control file location and name (it has to be /rdsdbdata/db/*DBNAME*/controlfile/control-01.ctl)
For additional information about these and other unsupported configurations, see the Amazon RDS documentation.
Product versions
For Oracle Database versions and instance classes supported by Amazon RDS Custom, see Requirements and limitations for Amazon RDS Custom for Oracle.
Architecture
Target technology stack
Application Load Balancer
Amazon EFS
Amazon RDS Custom for Oracle
AWS Secrets Manager
Amazon Simple Storage Service (Amazon S3)
Target architecture
The following architecture diagram represents a PeopleSoft system running in a single Availability Zone on AWS. The application tier is accessed through an Application Load Balancer. Both the application and the databases are in private subnets, and the Amazon RDS Custom and Amazon EC2 database instance use an Amazon EFS shared file system to store and access the RMAN backup files. Amazon S3 is used for creating the custom RDS Oracle engine and for storing the redo logs metadata.
Tools
AWS services
Amazon RDS Custom for Oracle is a managed database service for legacy, custom, and packaged applications that require access to the underlying operating system and database environment. It automates database administration tasks, such as backups and high availability.
Amazon Elastic File System (Amazon EFS) helps you create and configure shared file systems in the AWS Cloud. This pattern uses an Amazon EFS shared file system to store and access the RMAN backup files.
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 to create the RDSADMIN and ADMIN users and to change the sys and system passwords.
Amazon Simple Storage Service (Amazon S3) is a cloud-based object storage service that helps you store, protect, and retrieve any amount of data.
Elastic Load Balancing (ELB) distributes incoming application or network traffic across multiple targets. For example, you can distribute traffic across Amazon Elastic Compute Cloud (Amazon EC2) instances, containers, and IP addresses in one or more Availability Zones. This pattern uses an Application Load Balancer.
Other tools
Best practices
For database initialization parameters, customize the standard pfile that’s provided by the Amazon RDS Custom DB instance for PeopleSoft instead of using the spfile from the Oracle source database. This is because white spaces and comments cause issues when creating read replicas in Amazon RDS Custom. For more information about database initialization parameters, see Oracle Support Note 1100831.1 (requires an Oracle Support account).
Amazon RDS Custom uses Oracle automatic memory management by default. If you want to use the Hugemem kernel, you can configure Amazon RDS Custom to use automatic shared memory management instead.
Leave the memory_max_target parameter enabled by default. The framework uses this in the background to create read replicas.
Enable Oracle Flashback Database. This feature is useful when reinstating the standby in failover (not switchover) testing scenarios.
Epics
| Task | Description | Skills required |
|---|
Create a backup script. | Create an RMAN backup script to back up the database to the Amazon EFS file system that you mounted (/efs in the following example). You can use the example code or run one of your existing RMAN scripts. #!/bin/bash
Dt=`date +'%Y%m%d-%H%M'`
BACKUP_LOG="rman-${ORACLE_SID}-$Dt"
export TAGDATE=`date +%Y%m%d%H%M`;
LOGPATH=/u01/scripts/logs
rman target / >> $LOGPATH/rman-${ORACLE_SID}-$Dt << EOF
SQL "ALTER SYSTEM SWITCH LOGFILE";
SQL "ALTER SESSION SET NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"";
RUN
{
ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 5G;
ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 5G;
BACKUP AS COMPRESSED BACKUPSET FULL DATABASE FORMAT '/efs/rman_backup/FSCM/%d_%T_%s_%p_FULL' ;
SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
BACKUP FORMAT '/efs/rman_backup/FSCM/%d_%T_%s_%p_ARCHIVE' ARCHIVELOG ALL DELETE ALL INPUT ;
BACKUP CURRENT CONTROLFILE FORMAT '/efs/rman_backup/FSCM/%d_%T_%s_%p_CONTROL';
}
EXIT;
EOF
| DBA |
Run the backup script. | To run the RMAN backup script, log in as the Oracle Home User, and run the script. $ chmod a+x rman_backup.sh
$ ./rman_backup.sh &
| DBA |
Check for errors and note the name of the backup file. | Check the RMAN log file for errors. If everything looks fine, list the backup of the control file by running the following command. RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
Note the name of the output file. List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12 Full 21.58M DISK 00:00:01 13-JUL-22
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20220713T150155
Piece Name: /efs/rman_backup/FSCM/FSDMO92_20220713_12_1_CONTROL
Control File Included: Ckp SCN: 16559159985898 Ckp time: 13-JUL-22
You will use the backup control file /efs/rman_backup/FSCM/FSDMO92_20220713_12_1_CONTROL when you restore the database on Amazon RDS Custom. | DBA |
| Task | Description | Skills required |
|---|
Shut down the application. | To shut down the source application tier, use the psadmin utility or the psadmin command line utility. To shut down the webserver, run the following command. psadmin -w shutdown -d "webserver domain name"
To shut down the application server, run the following command. psadmin -c shutdown -d "application server domain name"
To shut down the process scheduler, run the following command. psadmin -p stop -d "process scheduler domain name"
| DBA, PeopleSoft Administrator |
| Task | Description | Skills required |
|---|
Install the nfs-utils rpm package. | To install the nfs-utils rpm package, run the following command. $ yum install -y nfs-utils
| DBA |
Mount the EFS storage. | Get the Amazon EFS mount command from the Amazon EFS console page. Mount the EFS file system on the Amazon RDS instance by using a Network File System (NFS) client. sudo mount -t nfs4 -o nfsvers=4.1,rsize=1048576,wsize=1048576,hard,timeo=600,retrans=2,noresvport fs-xxxxxxxxxx.efs.eu-west-1.amazonaws.com:/ /efs
sudo mount -t nfs4 -o nfsvers=4.1,rsize=1048576,wsize=1048576,hard,timeo=600,retrans=2,noresvport fs-xxxxxxxxxx.efs.eu-west-1.amazonaws.com:/ /efs
| DBA |
| Task | Description | Skills required |
|---|
Pause automation mode. | You have to pause automation mode on your Amazon RDS Custom DB instance before you proceed with the next steps, to make sure that automation doesn’t interfere with the RMAN restore activity. You can pause the automation by using the AWS console or the AWS Command Line Interface (AWS CLI) command (make sure that you have configured the AWS CLI first). aws rds modify-db-instance \
--db-instance-identifier peoplesoft-fscm-92 \
--automation-mode all-paused \
--resume-full-automation-mode-minute 360 \
--region eu-west-1
When you specify the duration of the pause, make sure that you leave enough time for the RMAN restore. This depends on the size of the source database, so modify the 360 value accordingly. Also, make sure that the total time of the paused automation does not overlap with the backup or maintenance window of the database. | DBA |
Create and modify the parameter file for PeopleSoft | To create and modify the pfile for PeopleSoft, use the standard pfile created with the Amazon RDS Custom DB instance. Add the parameters you need for PeopleSoft. Switch to rds user rdsdb by running the following command. $ sudo su - rdsdb
Log in to SQL*Plus on the starter database, and create the pfile by running the following command. SQL> create pfile from spfile;
This creates the pfile in $ORACLE_HOME/dbs. Make a backup of this pfile. Edit the pfile to add or update PeopleSoft parameters. *._gby_hash_aggregation_enabled=false
*._unnest_subquery=false
*.nls_language='AMERICAN'
*.nls_length_semantics='CHAR'
*.nls_territory='AMERICA'
*.open_cursors=1000
*.db_files=1200
*.undo_tablespace='UNDOTBS1'
PeopleSoft related parameters can be found in Oracle Support Note 1100831.1. Remove the spfile reference from the pfile. *.spfile='/rdsdbbin/oracle/dbs/spfileFSDMO92.ora'
| DBA |
Drop the starter database. | To drop the existing Amazon RDS Custom database, use the following code. $ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount exclusive restrict;
SQL> drop database;
SQL> exit
| |
Restore the Amazon RDS Custom database from the backup. | Restore the database by using the following script. The script will first restore the control file and then restore the entire database from the backup pieces stored on the EFS mount. #!/bin/bash
Dt=`date +'%Y%m%d-%H%M'`
BACKUP_LOG="rman-${ORACLE_SID}-$Dt"
export TAGDATE=`date +%Y%m%d%H%M`;
LOGPATH=/rdsdbdata/scripts/logs
rman target / >> $LOGPATH/rman-${ORACLE_SID}-$Dt << EOF
restore controlfile from "/efs/rman_backup/FSCM/FSDMO92_20220713_12_1_CONTROL";
alter database mount;
run
{
set newname for database to '/rdsdbdata/db/FSDMO92_A/datafile/%f_%b';
SET NEWNAME FOR TEMPFILE 1 TO '/rdsdbdata/db/FSDMO92_A/datafile/%f_%b';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
RECOVER DATABASE;
}
EOF
sqlplus / as sysdba >> $LOGPATH/rman-${ORACLE_SID}-$Dt<<-EOF
ALTER DATABASE RENAME FILE '/u01/psoft/db/oradata/FSDMO92/redo01.log' TO '/rdsdbdata/db/FSDMO92_A/onlinelog/redo01.log';
ALTER DATABASE RENAME FILE '/u01/psoft/db/oradata/FSDMO92/redo02.log' TO '/rdsdbdata/db/FSDMO92_A/onlinelog/redo02.log';
ALTER DATABASE RENAME FILE '/u01/psoft/db/oradata/FSDMO92/redo03.log' TO '/rdsdbdata/db/FSDMO92_A/onlinelog/redo03.log';
alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 3;
alter database open resetlogs;
EXIT
EOF
| DBA |
| Task | Description | Skills required |
|---|
Retrieve the password from Secrets Manager. | You can perform this step by using the AWS console or the AWS CLI. The following steps show instructions for the console. Sign in to the AWS Management Console and open the Amazon RDS console. In the navigation pane, choose Databases, and then select the Amazon RDS database. Choose the Configuration tab, and note the resource ID for the instance. It will be in the format db-<ID> (for example, db-73GJNHLGDNZNDOXNWXSECUW6LE). Open the Secrets Manager console. Choose the secret that has the same name as do-not-delete-custom-<resource_id>, where resource-id refers to the resource ID that you noted in step 3. Choose Retrieve secret value. This password will be same for the sys, system, rdsadmin, and admin users.
| DBA |
Create the RDSADMIN user. | RDSADMIN is the database user for monitoring and orchestrating the Amazon RDS Custom DB instance. Because the starter database was dropped and the target database was restored from the source using RMAN, you must recreate this user after the restore operation to make sure that Amazon RDS Custom monitoring works as expected. You also must create a separate profile and tablespace for the RDSADMIN user.
Enter the following commands at a SQL prompt. SQL> set echo on feedback on serverout on
SQL> @?/rdbms/admin/utlpwdmg.sql
SQL> ALTER PROFILE DEFAULT
LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;
Create the profile RDSADMIN. SQL> set echo on feedback on serverout on
SQL> alter session set "_oracle_script"=true;
SQL> CREATE PROFILE RDSADMIN
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 86400/86400
PASSWORD_GRACE_TIME 604800/86400;
Create the RDSADMIN tablespace. SQL> CREATE BIGFILE TABLESPACE rdsadmin '/rdsdbdata/db/FSDMO92_A/datafile/rdsadmin.dbf' DATAFILE SIZE 7M
AUTOEXTEND ON NEXT 1m
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
Create the RDSADMIN user. Replace the RDSADMIN password with the password you obtained earlier from Secrets Manager. SQL> CREATE USER rdsadmin IDENTIFIED BY xxxxxxxxxx
DEFAULT TABLESPACE rdsadmin
TEMPORARY TABLESPACE TEMP
profile rdsadmin ;
Grant privileges to RDSADMIN. SQL> GRANT "CONNECT" TO RDSADMIN WITH ADMIN OPTION;
SQL> GRANT "RESOURCE" TO RDSADMIN WITH ADMIN OPTION;
SQL> GRANT "DBA" TO RDSADMIN;
SQL> GRANT "SELECT_CATALOG_ROLE" TO RDSADMIN WITH ADMIN OPTION;
SQL> GRANT ALTER SYSTEM TO RDSADMIN;
SQL> GRANT UNLIMITED TABLESPACE TO RDSADMIN;
SQL> GRANT SELECT ANY TABLE TO RDSADMIN;
SQL> GRANT ALTER DATABASE TO RDSADMIN;
SQL> GRANT ADMINISTER DATABASE TRIGGER TO RDSADMIN;
SQL> GRANT ANY OBJECT PRIVILEGE TO RDSADMIN WITH ADMIN OPTION;
SQL> GRANT INHERIT ANY PRIVILEGES TO RDSADMIN;
SQL> ALTER USER RDSADMIN DEFAULT ROLE ALL;
Set the SYS, SYSTEM, and DBSNMP user profiles to RDSADMIN.
SQL> set echo on feedback on serverout on
SQL> alter user SYS profile RDSADMIN;
SQL> alter user SYSTEM profile RDSADMIN;
SQL> alter user DBSNMP profile RDSADMIN;
| DBA |
Create the master user. | Because the starter database was dropped and the target database was restored from the source by using RMAN, you must recreate the master user. In this example, the master user name is admin. SQL> create user admin identified by <password>;
SQL> grant dba to admin
| DBA |
Change the system passwords. | Change the system passwords by using the password you retrieved from Secrets Manager. SQL> alter user sys identified by xxxxxxxxxxx;
SQL> alter user system identified by xxxxxxxxxx;
If you don’t change these passwords, Amazon RDS Custom displays the error message, "The database monitoring user or user credentials have changed." | DBA |
| Task | Description | Skills required |
|---|
Configure the tnsnames file. | To connect to the database from the application tier, configure the tnsnames.ora file so you can connect to the database from the application tier. In the following example, you can see that there is a soft link to the tnsnames.ora file, but the file is empty by default. $ cd /rdsdbbin/oracle/network/admin
$ ls -ltr
-rw-r--r-- 1 rdsdb database 1536 Feb 14 2018 shrept.lst
lrwxrwxrwx 1 rdsdb database 30 Apr 5 13:19 listener.ora -> /rdsdbdata/config/listener.ora
lrwxrwxrwx 1 rdsdb database 28 Apr 5 13:19 sqlnet.ora -> /rdsdbdata/config/sqlnet.ora
lrwxrwxrwx 1 rdsdb database 30 Apr 5 13:19 tnsnames.ora -> /rdsdbdata/config/tnsnames.ora
Create the tsnames.ora entry. Because of the way Amazon RDS automation parses the files, you have to make sure that the entry doesn’t contain any white spaces, comments, or extra lines. Otherwise, you might run into issues when using some of the APIs, such as create-db-instance-read-replica. Replace the port, host, and SID in accordance with your PeopleSoft database requirements. Use the following code as an example. $ vi tnsnames.ora
FSDMO92=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = FSDMO92)))
To confirm that the PeopleSoft database can be reached, run the following command. $ tnsping FSDMO92
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 14-JUL-2022 10:16:45
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/rdsdbbin/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = FSDMO92)))
OK (0 msec)
| DBA |
| Task | Description | Skills required |
|---|
Create the spfile softlink. | To create spfile in the location /rdsdbdata/admin/FSDMO92/pfile, run the following command. SQL> create spfile='/rdsdbdata/admin/FSDMO92/pfile/spfileFSDMO92.ora' from pfile;
Navigate to $ORACLE_HOME/dbs, and create a soft link for the spfile. ln -s '/rdsdbdata/admin/FSDMO92/pfile/spfileFSDMO92.ora' spfileFSDMO92.ora
After this file is created, you can shut down and start the database by using the spfile.
| DBA |
| Task | Description | Skills required |
|---|
Validate the schema, connections, and maintenance tasks. | To finalize the migration, perform the following tasks. Gather schema statistics. Ensure that the PeopleSoft application tier can connect to the new Amazon RDS Custom database. Set up your backup and maintenance schedules.
| DBA |
Related resources
