Migrate Oracle E-Business Suite to Amazon RDS Custom

Shut down the application.

To shut down the source application, use these commands: 

$ su - applmgr
$ cd $INST_TOP/admin/scripts
$ ./adstpall.sh 

Create the .zip file.

Create the appsutil.zip file on the source application tier. You will use this file later to configure the Amazon RDS Custom database node.

$ perl $AD_TOP/bin/admkappsutil.pl

Copy the .zip file to Amazon EFS.

Copy appsutil.zip from $INST_TOP/admin/out to your shared Amazon EFS volume (/RMAN/appsutil). You can transfer the file manually by using secure copy (SCP) or another transfer mechanism.

Pre-clone the database tier on Amazon EC2.

Log in as Oracle user and run:

$ cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
$ perl adpreclone.pl dbTier

Check the log file generated to confirm that the operation completed successfully.

Copy appsutil.zip to the shared Amazon EFS file system.

Create a tar backup and copy $ORACLE_HOME/appsutil to the shared Amazon EFS file system (for example, /RMAN/appsutil):

$ cd $ORACLE_HOME
$ tar cvf sourceappsutil.tar appsutil
$ cp sourceappsutil.tar /RMAN/appsutil 

Create a backup script.

Perform an RMAN full backup of the source database to the shared Amazon EFS file system.

For simplicity, this pattern performs a cold RMAN backup. However, you can modify these steps to perform a hot RMAN backup with Oracle Data Guard to reduce downtime.

1. Start up the source Amazon EC2 database in mount mode:

$ sqlplus / as sysdba
$ SQL> shutdown immediate
$ SQL> startup mount

2. Create an RMAN backup script (use one of the following examples, depending on your version of Oracle, or run one of your existing RMAN scripts) to back up the database to the Amazon EFS file system that you mounted (/RMAN in this example).

For Oracle 12.1.0.2:

$ vi FullRMANColdBackup.sh
#!/bin/bash
. /home/oracle/.bash_profile

export ORACLE_SID=VIS
export ORACLE_HOME=/d01/oracle/VIS/12.1.0
export DATE=$(date +%y-%m-%d_%H%M%S)

rman target / log=/RMAN/VISDB_${DATE}.log << EOF
run
{
allocate channel ch1 device type disk format '/RMAN/visdb_full_bkp_%u';
allocate channel ch2 device type disk format '/RMAN/visdb_full_bkp_%u';
crosscheck backup;
delete noprompt obsolete;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
backup archivelog all;
release channel ch1;
release channel ch2;
}
EOF

For Oracle 19c:

$ vi FullRMANColdBackup.sh
#!/bin/bash
. /home/oracle/.bash_profile

export ORACLE_SID=VISCDB
export ORACLE_HOME=/d01/oracle/VIS/19c
export DATE=$(date +%y-%m-%d_%H%M%S)

rman target / log=/RMAN/VISDB_${DATE}.log << EOF
run
{
allocate channel ch1 device type disk format '/RMAN/visdb_full_bkp_%u';
allocate channel ch2 device type disk format '/RMAN/visdb_full_bkp_%u';
crosscheck backup;
delete noprompt obsolete;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
backup archivelog all;
backup current controlfile format '/RMAN/cntrl.bak';
release channel ch1;
release channel ch2;
}
EOF

Run the backup script.

Change permissions, log in as Oracle user,  and run the script:

$  chmod 755 FullRMANColdBackup.sh
$ ./FullRMANColdBackup.sh

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. Note the name of the output file.

For Oracle 12.1.0.2:

RMAN> connect target /
 
RMAN> list backup of controlfile;
 
BS Key   Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9        Full    1.11M      DISK        00:00:04     23-APR-22      
         BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: TAG20220423T121011
         Piece Name: /RMAN/visdb_full_bkp_100rlsbt
  Control File Included: Ckp SCN: 12204595396727    Ckp time: 23-APR-22

You will use the backup file /RMAN/visdb_full_bkp_100rlsbt later, when you restore the database on Amazon RDS Custom.

For Oracle 19c:

RMAN> connect target /
 
RMAN> list backup of controlfile;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38      Full    17.92M     DISK        00:00:01     25-NOV-22      
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20221125T095014
        Piece Name: /RMAN/cntrl.bak
  Control File Included: Ckp SCN: 12204620188873   Ckp time: 23-NOV-22

You will use the backup file /RMAN/cntrl.bak later, when you restore the database on Amazon RDS Custom.

Change the hosts file and set the hostname.

1. Edit the /etc/hosts file on the Amazon RDS Custom DB instance. A simple way to do this is to copy the database and application host entries from the source Amazon EC2 database hosts file.

<IP-address> OEBS-app01.localdomain OEBS-app01 OEBS-app01log.localdomain OEBS-app01log
<IP-address> OEBS-db01.localdomain  OEBS-db01 OEBS-db01log.localdomain OEBS-db01log

where <IP-address> is the database node IP address, which you should replace with the Amazon RDS Custom IP address. The logical hostnames are appended with *log.

2. Change the database hostname by running the hostnamectl command: 

$ sudo hostnamectl set-hostname --static persistent-hostname

For example:

$ sudo hostnamectl set-hostname --static OEBS-db01log

For additional information, see the Knowledge Center article on assigning static hostnames.

3. Restart the Amazon RDS Custom DB instance.  Don’t worry about shutting down the database, because you will be dropping it in a later step.

$ reboot

4. When the Amazon RDS Custom DB instance comes back up, log in and verify that the hostname has changed: 

$ hostname
oebs-db01

Install the Oracle E-Business Suite software.

Install the Oracle E-Business Suite recommended RPMs to the Oracle home location on the Amazon RDS Custom DB instance. For details, see Oracle Support Note #1330701.1. The following is a partial list. The RPM list changes for each release, so check to make sure that all the required RPMs are installed.

As root user, run:

$ sudo yum -y update
$ sudo yum install -y elfutils-libelf-devel*
$ sudo yum install -y libXp-1.0.2-2.1*.i686
$ sudo yum install -y libXp-1.0.2-2.1*
$ sudo yum install -y compat-libstdc++-*

Verify that all of the required patches are installed before proceeding with the next step.

Install the VNC server.

For Oracle 12.1.0.2:

Install the VNC server and its dependent desktop packages. This is a requirement for installing the 12c Examples CD in the next step.

1. As root user, run:

$ sudo yum install -y tigervnc-server
$ sudo yum install -y *kde*
$ sudo yum install -y *xorg*

2. Start the VNC server for rdsdb user, and set the password for VNC:

$ su - rdsdb
$ vncserver :1
$ vncpassword

Install the 12c Examples CD.

For Oracle 12.1.0.2:

1. Download the installation files from https://edelivery.oracle.com/.  For Oracle E-Business Suite 12.2.11 – Oracle Database 12c Release 1 (12.1.0.2), look for Examples for Linux x86-64 V100102-01.zip.

2. Create a directory to store the Examples CD:

$ mkdir /RMAN/12cexamples

3. Copy the Examples CD .zip file to this directory by using the transfer mechanism of your choice (for example, SCP):

V100102-01.zip 

4. Change ownership to rdsdb:

$ chown -R rdsdb:rdsdb /RMAN/12cexamples

5. As the rdsdb user, unzip the file:

$ unzip V10010201.zip 

6. Connect from a client that has access to the VNC client and Amazon RDS Custom. Make sure that you have the necessary network connectivity and firewall ports open to allow access for VNC. For example, a VNC server that’s running on display :1 will need port 5901 opening on the security group that is associated with the Amazon RDS Custom EC2 host.

7. Change to the directory where you copied the Examples CD:

$ cd /RMAN/12cexamples/examples

8. Run the installer.  Make sure to verify the location of the oraInst.loc file. 

./runInstaller -invPtrLoc /rdsdbbin/oracle.12.1.custom.r1.EE.1/oraInst.loc

9. Use the following parameters during the installation of the Examples CD:  

Skip Software Update Downloads
Select Oracle Home 12.1.0.2
(Oracle Base = /rdsdbbin)
(Software Location = /rdsdbbin/oracle/12.1.custom.r1.EE.1)

10. The installation program includes five steps with prompts.  Follow the steps until installation is complete. 

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 activity.

Pause the automation by using the following 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 VIS \
--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.

Drop the starter database.

Drop the existing Amazon RDS Custom database. 

As the Oracle home user, run the following commands. (The default user is rdsdb, unless you customized it.)

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount restrict;
SQL> alter database mount;
SQL> drop database;
SQL> exit

Create directories to store the database files.

For Oracle 12.1.0.2:

Create directories for the database, control file, datafiles, and online log. Use the parent directory of the control_files parameter in the previous command (in this case, VIS_A). Run the following commands as the Oracle home user (by default, rdsdb).

$ mkdir -p /rdsdbdata/db/VIS_A/controlfile
$ mkdir -p /rdsdbdata/db/VIS_A/datafile
$ mkdir -p /rdsdbdata/db/VIS_A/onlinelog

For Oracle 19c:

Create directories for the database, control file, datafiles, and online log. Use the parent directory of the control_files parameter in the previous command (in this case, VISCDB_A). Run the following commands as the Oracle home user (by default, rdsdb).

$ mkdir -p /rdsdbdata/db/cdb/VISCDB_A/controlfile
$ mkdir -p /rdsdbdata/db/cdb/VISCDB_A/datafile
$ mkdir -p /rdsdbdata/db/cdb/VISCDB_A/onlinelog
$ mkdir -p /rdsdbdata/db/cdb/VISCDB_A/onlinelog/arch
$ mkdir /rdsdbdata/db/pdb/VISCDB_A

Create and modify the parameter file for Oracle E-Business Suite.

In this step, you won’t copy the server parameter file (SPFILE) from the source database. Instead, you'll use the standard parameter file (PFILE) created with the Amazon RDS Custom DB instance and add the parameters that you need for Oracle E-Business Suite.

When you drop the database, Amazon RDS automation creates a backup of the init.ora file, which is associated with the Amazon RDS Custom database. This file is called oracle_pfile and is located in /rdsdbdata/config.

For Oracle 12.1.0.2:

1. Copy /rdsdbdata/config/oracle_pfile to $ORACLE_HOME.

$ cp /rdsdbdata/config/oracle_pfile $ORACLE_HOME/dbs/initVIS.ora

2. Edit the initVIS.ora file on the Amazon RDS Custom DB instance. Validate all the parameters on the source and add any parameters as needed. For details, see Oracle Support Note 396009.1.

3. Add parameters similar to the following to the initVIS.ora file, based on your requirements:

*.workarea_size_policy='AUTO'
*.plsql_code_type='INTERPRETED'
*.cursor_sharing='EXACT'
*._b_tree_bitmap_plans=FALSE
*.session_cached_cursors=500
*.optimizer_adaptive_features=false
*.optimizer_secure_view_merging=false
*.SQL92_SECURITY=TRUE
*.temp_undo_enabled=true
_system_trig_enabled = TRUE
nls_language = american
nls_territory = america
nls_numeric_characters = ".,"
nls_comp = binary
nls_sort = binary
nls_date_format = DD-MON-RR
nls_length_semantics = BYTE
aq_tm_processes = 1
_sort_elimination_cost_ratio =5
_like_with_bind_as_equality = TRUE
_fast_full_scan_enabled = FALSE
_b_tree_bitmap_plans = FALSE
optimizer_secure_view_merging = FALSE
_optimizer_autostats_job = FALSE
parallel_max_servers = 8 
parallel_min_servers = 0
parallel_degree_policy = MANUAL
sec_case_sensitive_logon = FALSE
compatible = 12.1.0
o7_dictionary_accessibility = FALSE
utl_file_dir =/tmp

4. Amend the following. The values will be dependent on your source system, so revise them based on your current setup.

*.open_cursors=500
*.undo_tablespace='APPS_UNDOTS1

5. Remove the SPFILE reference.

*.spfile='/rdsdbbin/oracle/dbs/spfileVIS.ora'

Notes:

6. Confirm that there are no issues with the initVIS.ora file by running the startup nomount command:

SQL> startup nomount pfile=/rdsdbbin/oracle/dbs/initVIS.ora;
SQL> create spfile='/rdsdbdata/admin/VIS/pfile/spfileVIS.ora' from pfile;
SQL> exit

7. Create a symbolic link for SPFILE.

$ ln -s /rdsdbdata/admin/VIS/pfile/spfileVIS.ora $ORACLE_HOME/dbs/

For Oracle 19c:

1. Copy /rdsdbdata/config/oracle_pfile to $ORACLE_HOME.

$ cp /rdsdbdata/config/oracle_pfile $ORACLE_HOME/dbs/initVISCDB.ora

2. Edit the initVISCDB.ora file on the Amazon RDS Custom DB instance. Validate all the parameters on the source and add any parameters as needed. For details, see Oracle Support Note 396009.1.

3. Add parameters similar to the following to the initVISCDB.ora file, based on your requirements.

*.instance_name=VISCDB 
*.sec_case_sensitive_logon= FALSE  
*.result_cache_max_size = 600M
*.optimizer_adaptive_plans =TRUE
*.optimizer_adaptive_statistics = FALSE
*.pga_aggregate_limit = 0 
*.temp_undo_enabled = FALSE
*._pdb_name_case_sensitive = TRUE
*.event='10946 trace name context forever, level 8454144' 
*.workarea_size_policy='AUTO'
*.plsql_code_type='INTERPRETED'
*.cursor_sharing='EXACT'
*._b_tree_bitmap_plans=FALSE
*.session_cached_cursors=500
*.optimizer_secure_view_merging=false
*.SQL92_SECURITY=TRUE
_system_trig_enabled = TRUE
nls_language = american
nls_territory = america
nls_numeric_characters = ".,"
nls_comp = binary
nls_sort = binary
nls_date_format = DD-MON-RR
nls_length_semantics = BYTE
aq_tm_processes = 1
_sort_elimination_cost_ratio =5
_like_with_bind_as_equality = TRUE
_fast_full_scan_enabled = FALSE
_b_tree_bitmap_plans = FALSE
optimizer_secure_view_merging = FALSE
_optimizer_autostats_job = FALSE
parallel_max_servers = 8 
parallel_min_servers = 0
parallel_degree_policy = MANUAL

4. Amend the following. The values will depend on your source system, so revise them based on your current setup.

*.open_cursors=500
*.undo_tablespace='UNDOTBS1'

5. Remove the SPFILE reference:

*.spfile='/rdsdbbin/oracle/dbs/spfileVISCDB.ora'

Notes:

6. Confirm that there are no issues with the initVISCDB.ora file by running the startup nomount command:

SQL> startup nomount pfile=/rdsdbbin/oracle/dbs/initVISCDB.ora;
SQL> create spfile='/rdsdbdata/admin/VISCDB/pfile/spfileVISCDB.ora' from pfile;
SQL> exit

7. Create a symbolic link for SPFILE.

$ ln -s /rdsdbdata/admin/VISCDB/pfile/spfileVISCDB.ora $ORACLE_HOME/dbs/

Restore the Amazon RDS Custom database from the backup.

For Oracle 12.1.0.2:

1. Restore the control file by using the backup file that you captured on the source earlier:

RMAN> connect target /
RMAN> RESTORE CONTROLFILE FROM '/RMAN/visdb_full_bkp_100rlsbt';

Starting restore at 10-APR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
 
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/rdsdbdata/db/VIS_A/controlfile/control-01.ctl
Finished restore at 10-APR-22

2. Catalog the backup pieces, so you can issue an RMAN restore:

RMAN> alter database mount;
RMAN> catalog start with '/RMAN/visdb';  

3. Create a script to restore the database:

$ vi restore.sh
rman target / log=/home/rdsdb/rman.log << EOF
run
{
set newname for database to '/rdsdbdata/db/VIS_A/datafile/%b';
restore database;
switch datafile all; 
switch tempfile all;
}
EOF

4. Restore the source to the target Amazon RDS Custom database. You must change the permissions of the script to allow running it, and then run the restore.sh script to restore the database.

$ chmod 755 restore.sh
$ nohup ./restore.sh & 

For Oracle 19c:

1. Restore the control file by using the backup file that you captured on the source earlier:

RMAN> connect target /
RMAN> RESTORE CONTROLFILE FROM '/RMAN/cntrl.bak';
Starting restore at 07-JUN-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/rdsdbdata/db/cdb/VISCDB_A/controlfile/control-01.ctl
Finished restore at 07-JUN-23

2. Catalog the backup pieces, so you can issue an RMAN restore:

RMAN> alter database mount;
RMAN> catalog start with '/RMAN/visdb';  

If you experience issues with the start with command, you can add the backup pieces individually; for example: 

RMAN> catalog backuppiece '/RMAN/visdb_full_bkp_1d1e507m';

and then repeat the command for each backup piece.

3. Create a script to restore the database. Amend the pluggable database name based on your requirements. Allocate parallel channels based on the number of vCPUs available to speed up the restore process.

$ vi restore.sh
rman target / log=/home/rdsdb/rmancdb.log << EOF
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
.... .... ....
allocate channel c<N> type disk;
set newname for database to '/rdsdbdata/db/cdb/VISCDB_A/datafile/%b';
set newname for database root to '/rdsdbdata/db/cdb/VISCDB_A/datafile/%f_%b';
set newname for database "PDB$SEED" to '/rdsdbdata/db/cdb/pdbseed/%f_%b';
set newname for pluggable database VIS to '/rdsdbdata/db/pdb/VISCDB_A/%f_%b';
restore database;
switch datafile all; 
switch tempfile all;
release channel c1;
release channel c2;
release channel c3;
.... .... ....
release channel c<N>;
}
EOF

4. Restore the source to the target Amazon RDS Custom database. You must change the permissions of the script to allow running it, and then run the restore.sh script to restore the database.

$ chmod 755 restore.sh
$ nohup ./restore.sh & 

Check log files for issues.

For Oracle 12.1.0.2:

1. Confirm that there are no issues by reviewing the rman.log file:

$ cat /home/rdsdb/rman.log

2. Confirm the path of the log files registered in the control file:

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/d01/oracle/VIS/data/log1.dbf
/d01/oracle/VIS/data/log2.dbf
/d01/oracle/VIS/data/log3.dbf

3. Rename the log files to match the file path of the target. Replace the path to match the output from the previous step:

SQL> ALTER DATABASE RENAME FILE '/d01/oracle/VIS/data/log1.dbf' TO '/rdsdbdata/db/VIS_A/onlinelog/log1.dbf';
SQL> ALTER DATABASE RENAME FILE '/d01/oracle/VIS/data/log2.dbf' TO '/rdsdbdata/db/VIS_A/onlinelog/log2.dbf';
SQL> ALTER DATABASE RENAME FILE '/d01/oracle/VIS/data/log3.dbf' TO '/rdsdbdata/db/VIS_A/onlinelog/log3.dbf';

For Oracle 19c:

1. Confirm that there are no issues by reviewing the rmancdb.log file:

$ cat /home/rdsdb/rmancdb.log

2. Confirm the path of the log files registered in the control file:

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/d01/oracle/VIS/oradata/VISCDB/redo03.log
/d01/oracle/VIS/oradata/VISCDB/redo02.log
/d01/oracle/VIS/oradata/VISCDB/redo01.log

3. Rename the log files to match the file path of the target. Replace the path to match the output from the previous step:

SQL> ALTER DATABASE RENAME FILE '/d01/oracle/VIS/oradata/VISCDB/redo01.log' TO '/rdsdbdata/db/cdb/VISCDB_A/onlinelog/log1.dbf';
SQL> ALTER DATABASE RENAME FILE '/d01/oracle/VIS/oradata/VISCDB/redo02.log' TO '/rdsdbdata/db/cdb/VISCDB_A/onlinelog/log2.dbf';
SQL> ALTER DATABASE RENAME FILE '/d01/oracle/VIS/oradata/VISCDB/redo03.log' TO '/rdsdbdata/db/cdb/VISCDB_A/onlinelog/log3.dbf';

4. Confirm the path, the status of the log files, and the group number that is registered in the control file:

SQL> column REDOLOG_FILE_NAME format a50
SQL> SELECT a.GROUP#, a.status, b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

GROUP# STATUS REDOLOG_FILE_NAME                            SIZE_MB
1 CURRENT  /rdsdbdata/db/cdb/VISCDB_A/onlinelog/log1.dbf   512
2 INACTIVE /rdsdbdata/db/cdb/VISCDB_A/onlinelog/log2.dbf   512
3 INACTIVE /rdsdbdata/db/cdb/VISCDB_A/onlinelog/log3.dbf   512

Confirm that you can open the Amazon RDS Custom database, and create OMF log files.

Amazon RDS Custom for Oracle uses Oracle Managed Files (OMF) to simplify operations. You can promote read replicas to standalone instances, but you must first create the log files by using OMF. This is to ensure that the correct path is used when the instance is promoted. For more information about how to promote read replicas, see the Amazon RDS documentation. Failure to use OMF files can potentially cause problems when you try to promote read replicas.

1. Open the database with resetlogs:

SQL> alter database open resetlogs;

2. Confirm that the database is open:

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

3. Create the OMF log files. Change the group numbers, number of groups, and size depending on your requirements by using the output from the previous logfile query. The following example starts at group 4 and adds three groups for simplicity.

SQL> alter database add logfile group 4 size 512M;
Database altered.
SQL> alter database add logfile group 5 size 512M;
Database altered.
SQL> alter database add logfile group 6 size 512M;
Database altered.

4. Drop the previous non-OMF files. Here’s an example that you can customize based on your requirements and the output from the query in earlier steps:

SQL> alter database drop logfile group 1;
System altered.
SQL> alter database drop logfile group 2;
System altered.
SQL> alter database drop logfile group 3;
System altered.

5. Confirm that you can see the OMF files that were created. (The directory path varies for Oracle 12.1.0.2 and 19c, but the concept is the same.)

SQL> select member from v$logfile;

MEMBER
-----------------------------------------------------
/rdsdbdata/db/cdb/VISCDB_A/onlinelog/o1_mf_4_ksrbslny_.log
/rdsdbdata/db/cdb/VISCDB_A/onlinelog/o1_mf_5_ksrchw0k_.log
/rdsdbdata/db/cdb/VISCDB_A/onlinelog/o1_mf_6_ksrcnl9v_.log

6. Restart the database and confirm that SPFILE is in use by the instance:

SQL> shutdown immediate
SQL> startup
SQL> show parameter spfile

For Oracle 12.1.0.2, this query returns:

spfile   /rdsdbbin/oracle/dbs/spfileVIS.ora

For Oracle 19c, the query returns:

spfile    /rdsdbbin/oracle/dbs/spfileVISCDB.ora

7. For Oracle 19c only, check the status of the container database, and open it if required:

SQL> show pdbs
CON_ID CON_NAME              OPEN MODE   RESTRICTED
---------- ------------------- ---------- 
         2 PDB$SEED           READ ONLY  NO
         3 VIS                MOUNTED    NO

SQL> alter session set container=VIS;
Session altered.

SQL> alter database open;
Database altered.

SQL> alter database save state;
Database altered.

SQL> show pdbs
    CON_ID CON_NAME          OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- -----
         3 VIS                            READ WRITE NO

SQL> exit

8. Delete the init.ora file from $ORACLE_HOME/dbs, because you are not using the PFILE:

$ cd $ORACLE_HOME/dbs

For Oracle 12.1.0.2, use the command:

$ pwd
/rdsdbbin/oracle/dbs
$ rm initVIS.ora

For Oracle 19c, use the command:

$ pwd
/rdsdbbin/oracle/dbs
$ rm initVISCDB.ora

Retrieve passwords from Secrets Manager.

You can perform these steps in the console or by using the AWS CLI. The following steps provide instructions for the console.

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

2. In the navigation pane, choose Databases, and then select the Amazon RDS database.

3. Choose Configuration, and note the resource ID for the instance (it will be in the format: db-WZ4WLCK6AOQ6TJGZKMGRCDCI3Y).

4. Open the AWS Secrets Manager console at https://console.aws.amazon.com/secretsmanager/.

5. Choose the secret that has the same name as do-not-delete-custom-<resource_id>, where resource-id refers to the ID for the instance that you noted in step 3.

6. Choose Retrieve secret value.

Create the RDSADMIN user.

RDSADMIN is a monitoring and orchestrator database user in the Amazon RDS Custom DB instance. Because the starter database was dropped and the target database was restored from the source by using RMAN, you must recreate this user after the restore operation to make sure that Amazon RDS Custom monitoring works as expected. You also have to create a separate profile and tablespace for the RDSADMIN user. The instructions differ slightly for Oracle 12.1.0.2 and 19c.

For Oracle 12.1.0.2:

1. 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;

2. Create the profile RDSADMIN:

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;

3. 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;

4. Create the RDSADMIN tablespace:

SQL> create bigfile tablespace rdsadmin datafile size 7M
autoextend on next 1m
Logging online permanent blocksize 8192 extent management local autoallocate default nocompress segment space management auto;

5. 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 ;

6. Grant privileges to RDSADMIN:

SQL> grant select on sys.v_$instance to rdsadmin;
SQL> grant select on sys.v_$archived_log to rdsadmin;
SQL> grant select on sys.v_$database to rdsadmin;
SQL> grant select on sys.v_$database_incarnation to rdsadmin;
SQL> grant select on dba_users to rdsadmin;
SQL> grant alter system to rdsadmin;
SQL> grant alter database to rdsadmin;
SQL> grant connect to rdsadmin with admin option;
SQL> grant resource to rdsadmin with admin option;
SQL> alter user rdsadmin account unlock identified by xxxxxxxxxxx;
SQL> @?/rdbms/admin/userlock.sql
SQL> @?/rdbms/admin/utlrp.sql

For Oracle 19c:

1. 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;

2. Create the profile RDSADMIN.

SQL> create profile C##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;

3. Set the SYS, SYSTEM, and DBSNMP user profiles to RDSADMIN:

SQL> alter user SYS profile C##RDSADMIN;
SQL> alter user SYSTEM profile C##RDSADMIN;
SQL> alter user DBSNMP profile C##RDSADMIN;

4. Create the RDSADMIN tablespace:

SQL> create bigfile tablespace rdsadmin datafile size 7M
autoextend on next 1m
Logging online permanent blocksize 8192 extent management local autoallocate default nocompress segment space management auto;

5. Create the RDSADMIN user. Replace the RDSADMIN password with the password you obtained earlier from Secrets Manager.

SQL> create user C##rdsadmin identified by xxxxxxxxxx
profile C##rdsadmin container=all;

6. Grant privileges to RDSADMIN:

SQL> grant select on sys.v_$instance to c##rdsadmin;
SQL> grant select on sys.v_$archived_log to c##rdsadmin;
SQL> grant select on sys.v_$database to c##rdsadmin;
SQL> grant select on sys.v_$database_incarnation to c##rdsadmin;
SQL> grant select on dba_users to c##rdsadmin;
SQL> grant alter system to C##rdsadmin;
SQL> grant alter database to C##rdsadmin;
SQL> grant connect to C##rdsadmin with admin option;
SQL> grant resource to C##rdsadmin with admin option;
SQL> alter user C##rdsadmin account unlock identified by xxxxxxxxxxx;
SQL> @?/rdbms/admin/userlock.sql
SQL> @?/rdbms/admin/utlrp.sql

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 username is admin.

For Oracle 12.1.0.2:

SQL> create user admin identified by <password>;
SQL> grant dba to admin

For Oracle 19c:

SQL> alter session set container=VIS;

Session altered.

SQL> create user admin identified by <password>;

User created.

SQL> grant dba to admin;

Grant succeeded.

Change the super user passwords.

1. Change the system passwords by using the password you retrieved from Secrets Manager.

For Oracle 12.1.0.2:

SQL> alter user sys identified by xxxxxxxxxxx;
SQL> alter user system identified by xxxxxxxxxx;

For Oracle 19c:

SQL> alter user sys identified by xxxxxxxxxxx container=all;
SQL> alter user system identified by xxxxxxxxxx container=all;

For Oracle 12.1.0.2:

SQL> alter user ebs_system identified by xxxxxxxxxx; 

For Oracle 19c:

For this version, you also have to connect to the container database, to update the EBS_SYSTEM password there.

SQL> alter session set container=vis;
SQL> alter user ebs_system identified by xxxxxxxxxx; 

SQL> exit;

If you don’t change these passwords, Amazon RDS Custom displays the error message: The database monitoring user or user credentials have changed.

Create the directories required for Oracle E-Business Suite.

1. On the Amazon RDS Custom Oracle database, run the following script as the Oracle home user, to create the 9idata directory in $ORACLE_HOME/nls/data/9idata. This directory is required for Oracle E-Business Suite.

perl $ORACLE_HOME/nls/data/old/cr9idata.pl

Ignore the ORA_NLS10 message, because you will create the context-enabled environment in later steps.

2. Copy the appsutil.tar file, which you created earlier from the shared Amazon EFS file system, and untar it on the Amazon RDS Custom Oracle home directory.  This creates the appsutil directory in the $ORACLE_HOME directory.

$ cd /RMAN/appsutil
$ cp sourceappsutil.tar $ORACLE_HOME
$ cd $ORACLE_HOME
$ tar xvf sourceappsutil.tar appsutil

3. Copy the appsutil.zip file, which you saved on the Amazon EFS shared file system earlier.  This was the file you created on the application tier.

As the rdsdb user on the Amazon RDS Custom DB instance:

$ cp /RMAN/appsutil/appsutil.zip $ORACLE_HOME
$ cd $ORACLE_HOME

4. Unzip the appsutil.zip file to create the appsutil directory and subdirectories in the Oracle home directory:

$ unzip -o appsutil.zip

The -o option means that some of the files will be overwritten.

Configure the tsanames.ora and sqlnet.ora files.

You have to configure the tnsnames.ora file so you can connect to the database with the Autoconfig tool. In the following example, you can see that the tnsnames.ora file is softlinked, but the file is empty by default.

$ cd $ORACLE_HOME/network/admin
$ ls -ltr
-rw-r--r-- 1 rdsdb database 373 Oct 31  2013 shrept.lst
lrwxrwxrwx 1 rdsdb database  30 Feb   9 17:17 listener.ora -> /rdsdbdata/config/listener.ora
lrwxrwxrwx 1 rdsdb database  28 Feb   9 17:17 sqlnet.ora -> /rdsdbdata/config/sqlnet.ora
lrwxrwxrwx 1 rdsdb database  30 Feb   9 17:17 tnsnames.ora -> /rdsdbdata/config/tnsnames.ora

1. Create the tnsnames.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. Use the following as an example.    

2. Replace the port, host, and SID in accordance with your requirements:

$ vi tnsnames.ora 
VIS=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=xx.xx.xx.xx)))(CONNECT_DATA=(SID=VIS)(SERVER=DEDICATED)))

3. Confirm that the database can be reached:

$ tnsping vis
OK (0 msec)

4. For Oracle 19c only, update the sqlnet.ora file. Failure to do this will result in the error ORA-01017: invalid username/password; logon denied when you try to connect to the database. Edit sqlnet.ora in $ORACLE_HOME/network/admin to match the following:

NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10
SQLNET.INBOUND_CONNECT_TIMEOUT =60
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
HTTPS_SSL_VERSION=undetermined

5. Test connectivity:

$ sqlplus apps/****@vis

Configure the database.

Now that you have tested the connectivity to the database, you can configure the database with the appsutil utility to create the context-enabled environment.

For Oracle 12.1.0.2:

1. Run the following commands:

$ cd $ORACLE_HOME/appsutil/bin
$ perl adbldxml.pl appsuser=apps
Enter Hostname of Database server: oebs-db01
Enter Port of Database server: 1521
Enter SID of Database server: VIS
Enter Database Service Name: VIS
Enter the value for Display Variable: :1
The context file has been created at:
/rdsdbbin/oracle/appsutil/VIS_oebs-db01.xml

2. Create oraInst.loc from root user:

$ vi /etc/oraInst.loc
inventory_loc=/rdsdbbin/oracle.12.1.custom.r1.EE.1/oraInventory   
inst_group=database

3. Clone the context file to set the logical hostname by using the context file you created in the previous step. As the rdsdb user, run:

$ cd $ORACLE_HOME/appsutil/clone/bin
$ perl adclonectx.pl \
contextfile=[ORACLE_HOME]/appsutil/[current context file] \
template=[ORACLE_HOME]/appsutil/template/adxdbctx.tmp

where oebs-db01log refers to the logical hostname. For example:

$ perl adclonectx.pl \
contextfile=/rdsdbbin/oracle.12.1.custom.r1.EE.1/appsutil/VIS_oebs-db01.xml \
template=/rdsdbbin/oracle/appsutil/template/adxdbctx.tmp
Target System Hostname (virtual or normal) [oebs-db01] : oebs-db01log
Target System Base Directory : /rdsdbbin/oracle
Target Instance is RAC (y/n) [n] : n
 
Target System Database SID : VIS
 
Oracle OS User [rdsdb] : 
Oracle OS Group [rdsdb] : database
Role separation is supported y/n [n] ? : n
Target System utl_file_dir Directory List : /tmp
Number of DATA_TOP's on the Target System [1] : 
 
Target System DATA_TOP Directory 1 [/rdsdbbin/oracle/data] : /rdsdbdata/db/VIS_A/datafile/
 
Target System RDBMS ORACLE_HOME Directory [/rdsdbbin/oracle/12.1.0] : /rdsdbbin/oracle
 
Do you want to preserve the Display [:1] (y/n)  : y

Do you want the target system to have the same port values as the source system (y/n) [y] ? : y
The new database context file has been created :
   /rdsdbbin/oracle.12.1.custom.r1.EE.1/appsutil/clone/bin/VIS_oebs-db01log.xml
contextfile=/rdsdbbin/oracle.12.1.custom.r1.EE.1/appsutil/clone/bin/VIS_oebs-db01log.xml

For Oracle 19c:

1. Run the following commands:

$ cd $ORACLE_HOME/appsutil/bin
$ perl adbldxml.pl appsuser=apps
Enter Hostname of Database server: oebs-db01
Enter Port of Database server: 1521
Enter SID of Database server: VIS
Enter the database listener name:L_VISCDB_001
Enter the value for Display Variable: :1
The context file has been created at:
/rdsdbbin/oracle/appsutil/VIS_oebs-db01.xml

2. Create oraInst.loc from root user:

$ vi /etc/oraInst.loc
inventory_loc=/rdsdbbin/oracle/oraInventory   
inst_group=database

3. Clone the context file to set the logical hostname by using the context file you created in the previous step. As the rdsdb user, run:

$ cd $ORACLE_HOME/appsutil/clone/bin
$ perl adclonectx.pl \
contextfile=[ORACLE_HOME]/appsutil/[current context file] \
template=[ORACLE_HOME]/appsutil/template/adxdbctx.tmp

where oebs-db01log refers to the logical hostname. For example:

$ perl adclonectx.pl \
contextfile=/rdsdbbin/oracle/appsutil/VIS_oebs-db01.xml \
template=/rdsdbbin/oracle/appsutil/template/adxdbctx.tmp
Target System Hostname (virtual or normal) [oebs-db01] : oebs-db01log
Target System Base Directory : /rdsdbbin/oracle
Target Instance is RAC (y/n) [n] : n
Target System CDB Name : VISCDB 
Target System PDB Name : VIS
Oracle OS User [oracle] : rdsdb
Oracle OS Group [dba] : database
Role separation is supported y/n [n] ? : n
Number of DATA_TOP's on the Target System [2] : 
Target System DATA_TOP Directory 1 [/d01/oracle/VISCDB] : /rdsdbdata/db/pdb/VISCDB_A
Target System DATA_TOP Directory 2 [/d01/oracle/data] : /rdsdbdata/db/pdb/VISCDB_A/datafile
Specify value for OSBACKUPDBA group [database] : 
Specify value for OSDGDBA group [database] : 
Specify value for OSKMDBA group [database] : 
Specify value for OSRACDBA group [database] : 
Target System RDBMS ORACLE_HOME Directory [/d01/oracle/19.0.0] : /rdsdbbin/oracle
Do you want to preserve the Display [:1] (y/n)  : y
Do you want the target system to have the same port values as the source system (y/n) [y] ? : y
Validating if the source port numbers are available on the target system.. 
Complete port information available at /rdsdbbin/oracle/appsutil/clone/bin/out/VIS_oebs-db01log/portpool.lst
New context path and file name [VIS_oebs-db01log.xml] : /rdsdbbin/oracle/appsutil/VIS_oebs-db01log.xml
Do you want to overwrite it (y/n) [n] ? : y
Replacing /rdsdbbin/oracle/appsutil/VIS_oebs-db01log.xml file.
The new database context file has been created :
contextfile=/rdsdbbin/oracle/appsutil/VIS_oebs-db01log.xml
Check Clone Context logfile /rdsdbbin/oracle/appsutil/clone/bin/CloneContext_0609141428.log for details.

Install ETCC and run Autoconfig.

1. Install the Oracle E-Business Suite Technology Codelevel Checker (ETCC). 

Download patch 17537119 from My Oracle Support, and follow the instructions in README.txt. You will create a directory called etcc in the $ORACLE_HOME directory, unzip the patch to create a script called checkMTpatch.sh, and then run the script to check the patch versions.

2. Run the Autoconfig utility, and pass the new logical hostname context file.

For Oracle 12.1.0.2:

cd $ORACLE_HOME/appsutil/bin
$ ./adconfig.sh contextfile=/rdsdbbin/oracle.12.1.custom.r1.EE.1/appsutil/clone/bin/VIS_oebs-db01log.xml

For Oracle 19c:

Autoconfig expects the listener name to match CDBNAME. Therefore, the backed up original listener configuration file will use L_<CDBNAME>_001 temporarily.

$ lsnrctl stop L_VISCDB_001
$ cp -rp /rdsdbdata/config/listener.ora /rdsdbdata/config/listener.ora_orig
$ vi /rdsdbdata/config/listener.ora
:%s/L_VISCDB_001/VISCDB/g
$ lsnrctl start VISCDB
$ cd /rdsdbbin/oracle/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/rdsdbbin/oracle.19.custom.r1.EE-CDB.1

Oracle Home being passed: /rdsdbbin/oracle

$ echo $ORACLE_HOME
/rdsdbbin/oracle.19.custom.r1.EE-CDB.1 
$ export ORACLE_SID=VISCDB
$ cd $ORACLE_HOME/appsutil/bin
$ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -cdbsid=VISCDB -pdbsid=VIS -appsuser=apps -dbport=1521 -servicetype=onpremise

Enter the APPS Password: <apps password>

Enter the CDB SYSTEM Password:<password from secrets manager>

Configure the TNS entries for Amazon RDS Custom and Oracle E-Business Suite.

Autoconfig generates the TNS ifiles in the default locations. For Oracle 12.1.0.2 (which is a non-CDB) and for Oracle19c PDB the default location is $ORACLE_HOME/network/admin/$<CONTEXT_NAME>. The CDB for Oracle 19c uses the default $ORACLE_HOME/network/admin/, as defined by $TNS_ADMIN in the environment files that are generated when you ran Autoconfig in the previous steps.

For Oracle 12.1.0.2 and 19c CDB, you won’t use these because the tnsnames.ora and listener.ora files generated by Autoconfig do not adhere to the Amazon RDS requirements, such as no white spaces or comments. Instead, you use the generic files provided with the Amazon RDS Custom database to ensure compliance with what the system is expecting and to reduce the margin for error. 

For example, Amazon RDS Custom expects the following naming format:

L_<INSTANCE_NAME>_001

For Oracle 12.1.0.2 this would be:

L_VIS_001

For Oracle 19c, this would be:

L_VISCDB_001

Here’s an example of the listener.ora file that you will be using. This was generated when you created the Amazon RDS Custom database. At this point, you haven’t made any changes to this file and you will be leaving it as the default.

For Oracle 12.1.0.2:

$ cd $ORACLE_HOME/network/admin
$ cat listener.ora
ADR_BASE_L_VIS_001=/rdsdbdata/log/
SID_LIST_L_VIS_001=(SID_LIST = (SID_DESC = (SID_NAME = VIS)(GLOBAL_DBNAME = VIS) (ORACLE_HOME = /rdsdbbin/oracle)))
L_VIS_001=(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = xx.xx.xx.xx))) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 127.0.0.1))))
SUBSCRIBE_FOR_NODE_DOWN_EVENT_L_VIS_001=OFF

For Oracle 19c: Restore the original listener.ora file with the listener name L_<INSTANCE_NAME>_001.

$ cd $ORACLE_HOME/network/admin

$ cp -rp /rdsdbdata/config/listener.ora /rdsdbdata/config/listener.ora_autoconfig
$ cp -rp /rdsdbdata/config/listener.ora_orig /rdsdbdata/config/listener.ora

$ cat listener.ora
SUBSCRIBE_FOR_NODE_DOWN_EVENT_L_VISCDB_001=OFF
ADR_BASE_L_VISCDB_001=/rdsdbdata/log/
USE_SID_AS_SERVICE_L_VISCDB_001=ON
L_VISCDB_001=(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = xx.xx.xx.xx))) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 127.0.0.1))))
SID_LIST_L_VISCDB_001=(SID_LIST = (SID_DESC = (SID_NAME = VISCDB)(GLOBAL_DBNAME = VISCDB) (ORACLE_HOME = /rdsdbbin/oracle)))

Start the listener L_<INSTANCE_NAME>_001 for standard Amazon RDS operations:

$ lsnrctl stop
$ lsnrctl start L_VISCDB_001

For Oracle 12.1.0.2:

Edit the Oracle E-Business Suite environment file to change the $TNS_ADMIN path to use the Amazon RDS Custom generic TNS ifiles. The environment file was created when you ran Autoconfig earlier. Edit the TNS_ADMIN variable by removing the <CONTEXT_NAME> postfix.  

For example, in Oracle 12.1.0.2, edit the file:

$ vi $ORACLE_HOME/VIS_oebs-db01log.env

Change the path from: 

TNS_ADMIN=”/rdsdbbin/oracle/network/admin/VIS_oebs-db01log”
export TNS_ADMIN

to:

TNS_ADMIN=”/rdsdbbin/oracle/network/admin”
export TNS_ADMIN

For Oracle 19c:

1. Change the value for the database tier context variable s_cdb_tnsadmin to <ORACLE_HOME>/network/admin instead of <ORACLE_HOME>/network/admin/<CONTEXT_NAME>.

$ . $ORACLE_HOME/VIS_oebs-db01log.env
$ vi $CONTEXT_FILE

2. Save the changes you made to the value of s_cdb_tnsadmin.

The values for s_db_tnsadmin and s_cdb_tnsadmin should look similar to the following, with the PDB name as VIS and the database node logical name as oebs-db01log.

$ grep -i tns_admin $CONTEXT_FILE
         <TNS_ADMIN oa_var="s_db_tnsadmin">/rdsdbbin/oracle/network/admin/VIS_oebs-db01log</TNS_ADMIN>
         <CDB_TNS_ADMIN oa_var="s_cdb_tnsadmin">/rdsdbbin/oracle/network/admin</CDB_TNS_ADMIN>

3. Run Autoconfig on the database tier:

$ . $ORACLE_HOME/VISCDB_oebs-db01log.env
$ export ORACLE_PDB_SID=VIS
$ sqlplus "/ as sysdba" @$ORACLE_HOME/appsutil/admin/adgrants.sql APPS
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql

$ . $ORACLE_HOME/VIS_oebs-db01log.env
$ echo $ORACLE_SID
VIS

$ cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
$ ./adautocfg.sh

Set the environment for the rdsdb user.

Skip this step for Oracle 19c.

For Oracle 12.1.0.2:

Now that you have completed Autoconfig and TNS entries, you need to load the environment file by setting it in the rdsdb user’s profile.

Update .bash_profile to call the Oracle E-Business Suite database .env file. You need to update the profile to ensure that the environment is loaded. This environment file was created when you ran Autoconfig earlier.

The following example environment file is created when you run Autoconfig:

. /rdsdbbin/oracle/VIS_oebs-db01log.env

As the rdsdb user:

cd $HOME
vi .bash_profile 
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORACLE_HOME}/ctx/lib
export SHLIB_PATH=${ORACLE_HOME}/lib
export PATH=$PATH:${ORACLE_HOME}/bin
alias sql=’rlwrap -c sqlplus / as sysdba’
. ${ORACLE_HOME}/VIS_oebs-db01log.env

Configure the application and database for Amazon RDS Custom.

Complete the first two steps for both Oracle 12.1.0.2 and 19c. The subsequent steps differ for each version.

1. On the application tier, edit /etc/hosts and change the IP address for the database to the Amazon RDS Custom IP address:

xx.xx.xx.xx OEBS-db01.localdomain  OEBS-db01 OEBS-db01log.localdomain OEBS-db01log

Because you are using logical hostnames, you can replace the database node almost seamlessly.

2. On the Amazon RDS Custom DB instance, add or amend the security group that is assigned to the source EC2 instance to reflect the Amazon RDS Custom DB instance, to ensure that the application can access the node.

For Oracle 12.1.0.2:

3. Run Autoconfig. As application owner (for example, applmgr), run:

$ cd $INST_TOP/admin/scripts
$ ./adautocfg.sh 
AutoConfig completed successfully.

4. Verify the fnd_nodes entries:

SQL> select node_name from apps.fnd_nodes
NODE_NAME
--------------------------------------------------------------------------------
AUTHENTICATION
OEBS-APP01LOG
OEBS-DB01LOG

5. Confirm that you can log in, and start the application:

  $  ./adstrtal.sh

For Oracle 19c:

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 VIS				  MOUNTED


SQL>  alter session set container=vis;

SQL> alter database open;

SQL> alter database save state;

2. Test connectivity as apps:

SQL> sqlplus apps/****@vis 

3. Run Autoconfig on the database tier:

$ . $ORACLE_HOME/VIS_oebs-db01log.env
$ echo $ORACLE_SID
VIS

$ cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
$ ./adautocfg.sh

4. Run Autoconfig on the application tier as the application owner (for example, applmgr):

$ cd $INST_TOP/admin/scripts
$ ./adautocfg.sh 
AutoConfig completed successfully.

5. Verify the fnd_nodes entries:

SQL> select node_name from apps.fnd_nodes
NODE_NAME
--------------------------------------------------------------------------------
AUTHENTICATION
OEBS-APP01LOG
OEBS-DB01LOG

6. Start the application:

 $  ./adstrtal.sh 

Resume automation to confirm that it works.

Resume automation by using the following AWS CLI command:

aws rds modify-db-instance \
     --db-instance-identifier vis \
     --automation-mode full \

The database is now managed by Amazon RDS Custom. For example, if the listener or database goes down, the Amazon RDS Custom agent will restart them. To test this, run commands such as the following.

Stop listener example:

-bash-4.2$ lsnrctl stop vis

Shutdown database example:

SQL> shutdown immediate;

Validate schema, connections, and maintenance tasks.

To finalize the migration, you must perform the following tasks at a minimum.