Migrate Oracle E-Business Suite to Amazon RDS Custom
Created by Simon Cunningham (AWS), Jaydeep Nandy (AWS), Nitin Saxena (AWS), and Vishnu Vinnakota (AWS)
Environment: Production | Source: Amazon EC2 or on premises | Target: Amazon RDS Custom |
R Type: Replatform | Workload: Oracle | Technologies: Migration; Databases; Infrastructure |
AWS services: Amazon EFS; Amazon RDS; AWS Secrets Manager |
Summary
Oracle E-Business Suite is an Enterprise Resource Planning (ERP) solution for automating enterprise-wide processes such as financials, human resources, supply chains, and manufacturing. It has a three-tier architecture: client, application, and database. Previously, you had to run your Oracle E-Business Suite database on a self-managed Amazon Elastic Compute Cloud (Amazon EC2) instance, but you can now benefit from Amazon Relational Database Service (Amazon RDS) Custom.
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 and operations while making it possible for you, as a database administrator, to access and customize your database environment and operating system. When you migrate your Oracle database to Amazon RDS Custom, Amazon Web Services (AWS) takes care of heavy lifting such as backup tasks and ensuring high availability, while you can focus on maintaining your Oracle E-Business Suite 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 standalone Oracle database on Amazon EC2 to Amazon RDS Custom by using an Oracle Recovery Manager (RMAN) backup and an Amazon Elastic File System (Amazon EFS) shared file system between the EC2 instance and Amazon RDS Custom. The pattern uses an RMAN full backup (which is sometimes referred to as a level 0 backup). For simplicity, it uses a cold backup where the application is shut down and the database is mounted and not open. (You can also use Oracle Data Guard or RMAN duplication for backup. However, this pattern doesn’t cover those options.)
For information about architecting Oracle E-Business Suite on AWS for high availability and disaster recovery, see the pattern Set up an HA/DR architecture for Oracle E-Business Suite on Amazon RDS Custom with an active standby database.
Note: This pattern provides links to Oracle support notes. You need an Oracle Support account to access these documents.
Prerequisites and limitations
Prerequisites
An Oracle version 12.1.0.2 or 19c (minimum 19.3) source database that is running on Amazon EC2 with Oracle Linux 7 or Red Hat Enterprise Linux (RHEL) version 7.x. This pattern assumes that the source database name is VIS and that the additional container database name for Oracle 19c is VISCDB, but you can use other names.
Note: You can also use this pattern with on-premises Oracle source databases, as long as you have the appropriate network connectivity between the on-premises network and Amazon Virtual Private Cloud (Amazon VPC).
An Oracle E-Business Suite version 12.2.x application (vision instance). This procedure has been tested on version 12.2.11.
A single Oracle E-Business Suite application tier. However, you can adapt this pattern to work with multiple application tiers.
For Oracle 12.1.0.2, Amazon RDS Custom configured with at least 16 GB of swap space. Otherwise, the 12c Examples CD displays a warning. (Oracle 19c doesn’t require the Examples CD, as mentioned later in this document.)
Complete the following steps before you start your migration:
On the Amazon RDS console, create an Amazon RDS Custom for Oracle DB instance with the database name VIS (or your source database name). For instructions, see Working with Amazon RDS Custom in the AWS documentation and the Amazon RDS Custom for Oracle – New Control Capabilities in Database Environment blog post. This ensures that the database name is set to the same name as the source database. (If left blank, the EC2 instance and database name will be set to ORCL.) Make sure that you create your custom engine version (CEV) with the patches that have been applied to the source at a minimum. For more information, see Preparing to create a CEV in the Amazon RDS documentation.
Note for Oracle 19c: Currently, for Oracle 19c, the Amazon RDS container database name can be customized. The default is RDSCDB. Make sure to create the RDS Custom Oracle instance with same system ID (SID) as on the source EC2 instance. For example, in this pattern, the Oracle 19c SID is assumed to be VISCDB on the source instance. Therefor, the target Oracle 19c SID on Amazon RDS Custom should also be VISCDB.
Configure the Amazon RDS Custom DB instance with enough storage, vCPU, and memory to match the Amazon EC2 source database. To do this, you can match the Amazon EC2 instance types based on vCPU and memory.
Create an Amazon EFS file system and mount it on the Amazon EC2 and Amazon RDS Custom instances. For instructions, see the Integrate Amazon RDS Custom for Oracle with Amazon EFS blog post. This pattern assumes that you have mounted the Amazon EFS volume on /RMAN on both the source Amazon EC2 and target Amazon RDS Custom DB instances, and that network connectivity is possible between the source and target. You can also use the same method by using Amazon FSx or any shared drive.
Assumptions
This pattern assumes that your application and database are using logical hostnames, which reduce the number of migration steps. You can adjust these steps to use physical hostnames, but logical hostnames reduce the complexity of the migration process. For information about the advantages of using logical hostnames, see the following support notes:
For 12c, Oracle Support Note 2246690.1
For 19c, Oracle Support Note 2617788.1
This pattern doesn’t cover the Oracle 12c to 19c upgrade scenario, and focuses on migrating the same version of the Oracle database running on Amazon EC2 to Amazon RDS Custom for Oracle.
Amazon RDS Custom for Oracle supports Oracle Home customization. (Oracle Home stores the Oracle binaries.) You can change the default path of /rdsdbbin/oracle to a path that you specify, such as /d01/oracle/VIS/19c. For simplicity, the instructions in this pattern assume the default path /rdsdbbin/oracle.
Limitations
This pattern doesn’t support the following features and 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 EBS-optimized attribute of the EC2 instance
Modifying the original Amazon Elastic Block Store (Amazon EBS) volumes attached to the EC2 instance
Adding new EBS volumes or changing the volume type from gp2 to gp3
Support for the TNS ifile
Changing the control_file location and name (it must be /rdsdbdata/db/VISCDB_A/controlfile/control-01.ctl, where VISCDB is the CDB name)
For additional information about these and other unsupported configurations, see Fixing unsupported configurations in the Amazon RDS documentation.
Product versions
For Oracle Database versions and instance classes supported by Amazon RDS Custom, see Availability and requirements for Amazon RDS Custom for Oracle.
Architecture
The following architecture diagram represents an Oracle E-Business Suite 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 tier uses an Amazon EFS shared file system to store and access the RMAN backup files.
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 and operations while making it possible for you, as a database administrator, to access and customize your database environment and operating system.
Amazon Elastic File System (Amazon EFS) is a simple, serverless, elastic file system for adding and removing files with no need for management or provisioning. This pattern uses an Amazon EFS shared file system to store and access the RMAN backup files.
AWS Secrets Manager is an AWS managed service that enables you to easily rotate, manage, and retrieve database credentials, API keys, and other secret information. Amazon RDS Custom stores the key pair and database user credentials in Secrets Manager upon database creation. 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.
Other tools
Best practices
Use logical hostnames. This significantly reduces the number of post-clone scripts that you have to run. For more information, see Oracle Support Note 2246690.1.
Amazon RDS Custom uses Oracle Automatic Memory Management (AMM) by default. If you want to use the hugemem kernel, you can configure Amazon RDS Custom to use Automatic Shared Memory Management (ASMM) instead.
Leave the memory_max_target parameter enabled by default. The framework uses this parameter in the background to create read replicas.
Enable Oracle Flashback Database. This feature is useful in failover (not switchover) testing scenarios to reinstate the standby.
For database initialization parameters, customize the standard PFILE that’s provided by the Amazon RDS Custom DB instance for Oracle E-Business Suite 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 396009.1.
In the following Epics section, we’ve provided separate instructions for Oracle 12.1.0.2 and 19c, where the details differ.
Epics
| Task | Description | Skills required |
|---|
Shut down the application. | To shut down the source application, use these commands: $ su - applmgr
$ cd $INST_TOP/admin/scripts
$ ./adstpall.sh
| DBA |
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
| DBA |
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. | DBA |
| Task | Description | Skills required |
|---|
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. | DBA |
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
| DBA |
| Task | Description | Skills required |
|---|
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
| DBA |
Run the backup script. | Change permissions, log in as Oracle user, and run the script: $ chmod 755 FullRMANColdBackup.sh
$ ./FullRMANColdBackup.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. 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. | DBA |
| Task | Description | Skills required |
|---|
Change the hosts file and set the hostname. | Note: The commands in this section must be run as root user. 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
| DBA |
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. | DBA |
Install the VNC server. | Note: You can omit this step for Oracle 19c because the Examples CD is no longer required; see Oracle Support Note 2782085.1. 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
| DBA |
Install the 12c Examples CD. | Note: You can omit this step for Oracle 19c because the Examples CD is no longer required; see Oracle Support Note 2782085.1. 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. | 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 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. | DBA |
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
| DBA |
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
| DBA |
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. Important: Make sure that there are no comments in the parameters that you add. Comments will cause issues with the automation, such as creating read replicas and issuing point-in-time recoveries (PITRs). 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: Don't alter the values provided by the Amazon RDS Custom PFILE for control_files and db_unique_name. Amazon RDS expects these values. Deviating from them will cause issues if you try to create a read replica in the future. Amazon RDS Custom uses Automatic Memory Management (AMM) by default. If you want to use hugemem, you can configure Amazon RDS Custom to use Automatic Shared Memory Management (ASMM). Leave the memory_max_target parameter enabled by default. The Amazon RDS framework uses this in the background to create read replicas.
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. Important: Make sure that there are no comments in the parameters that you add. If there are comments, they will cause issues with the automation, such as creating read replicas and issuing point-in-time recoveries (PITRs). 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: Don't alter the values provided by the Amazon RDS Custom PFILE for control_files and db_unique_name. Amazon RDS expects these values. Deviating from them will cause issues if you try to create a read replica in the future. Amazon RDS Custom uses Automatic Memory Management (AMM) by default. If you want to use hugemem, you can configure Amazon RDS Custom to use Automatic Shared Memory Management (ASMM). Leave the memory_max_target parameter enabled by default. The Amazon RDS framework uses this in the background to create read replicas.
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/
| DBA |
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 &
| DBA |
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
| DBA |
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;
Note: If you receive the error ORA-00392: log xx of thread 1 is being cleared, operation not allowed, follow the steps in the Troubleshooting section for ORA-00392. 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.
Note: If you receive an ORA-01624 error when trying to drop the log files, see the Troubleshooting section. 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
| DBA |
| Task | Description | Skills required |
|---|
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. | DBA |
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. Note: RDSADMIN has a prefix of C## in Oracle 19c. This is because the database parameter common_user_prefix is set to C##. RDSADMIN has no prefix in Oracle 12.1.0.2. 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
| 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 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.
| DBA |
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;
Change the EBS_SYSTEM passwords.
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. | DBA |
| Task | Description | Skills required |
|---|
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)))
Note: There should be no extra lines in the file. If you don't remove the lines, you might encounter issues when you create a read replica in the future. The creation of a read replica might fail with the error message: Activity threw exception: HostManagerException: Unable to successfully call restrictReplication on any hosts. 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
| DBA |
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.
| DBA |
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>
Note: If your database directories have changed, follow the instructions in Oracle Support Note 2525754.1. | DBA |
| Task | Description | Skills required |
|---|
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. Note: You should edit the environment file only in Oracle 12.1.0.2, because the default home for 19c is $ORACLE_HOME/network/admin, which is the same as the default for Amazon RDS Custom. 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
Note: Every time you run Autoconfig, you must repeat this step to make sure that the correct TNS ifiles are being used. (12.1.0.2 only). 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>. Note: Do not update the s_db_tnsadmin context variable. Leave it as <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
| DBA |
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
Note: For Oracle 19c, you do not have to load the CDB environment in .bash_profile. This is because the default ORACLE_HOME is set to the default path $ORACLE_HOME/network/admin, which is the default home of the rdsdb (Oracle home) user. | DBA |
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: Check if the PDB is open, and open it if required:
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
| DBA |
| Task | Description | Skills required |
|---|
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;
| DBA |
Validate schema, connections, and maintenance tasks. | To finalize the migration, you must perform the following tasks at a minimum. Run FS_CLONE to synchronize the patch file system. Gather schema statistics. Ensure that external interfaces and systems can connect to the new Amazon RDS Custom database. Set up your backups and maintenance schedules. Verify that AD Online Patching (ADOP) is working as expected by issuing a cutover to switch the file systems.
| DBA |
Troubleshooting
| Issue | Solution |
|---|
You receive an ORA-01624 error when you try to drop the log files. | If you receive an ORA-01624 error when you try to drop the log files, follow these steps. Issue the following command and wait until the status of the log files you want to drop is INACTIVE. For more information about the status codes in V$log, see the Oracle documentation. Here’s an example command and its output: SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
5 UNUSED
6 UNUSED
6 rows selected.
In this example, log file 1 is ACTIVE, so you have to force a log file switch three times to ensure that the first new log file you added earlier has a status of CURRENT: SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Wait until all the log files that you want to drop are INACTIVE, as in the following example, and then run the DROP LOGFILE command. SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
6 rows selected.
|
You receive an ORA-00392 error when you open the database with resetlogs. | If you receive the error ORA-00392: log xx of thread 1 is being cleared, operation not allowed, run the following command (replace xx with the log file number), and then rerun the open resetlogs command: SQL> alter database clear logfile group xx;
SQL> alter database open resetlogs;
|
You have trouble connecting to the application using Sysadmin or application user. | To confirm the issue, run the following SQL query: SQL> select dbms_java.get_jdk_version() from dual;
select dbms_java.get_jdk_version() from dual
ERROR at line 1:
ORA-29548: Java system class reported: release of Java system classes in the
database (19.0.0.0.220719 1.8) does not match that of the oracle executable (19.0.0.0.0 1.8)
Root cause: The source database was applied with multiple patches, but the Amazon RDS Custom DB_HOME is a new installation, or the CEV did not include all of the patches because you didn’t use the necessary RSU patches, such as OJVM, when you created the CEV. To validate this, check if the source patch details are listed on $ORACLE_HOME/sqlpath, $ORACLE_HOME/.patch_storage, and opatch – lsinventory. Reference: datapatch -verbose Fails with Error :" Patch xxxxxx: Archived Patch Directory Is Empty" (Doc ID 2235541.1) Fix: Copy the missing patch-related files from the source ($ORACLE_HOME/sqlpatch/) to Amazon RDS Custom ($ORACLE_HOME/sqlpatch/) , and then rerun ./datapatch -verbose. For example: -bash-4.2$ cp -rp 18793246 20204035 20887355 22098146 22731026 $ORACLE_HOME/sqlpatch/
Alternatively, you can use a workaround by running the following command on the CDB and PDB: @?/javavm/install/update_javavm_db.sql
Then run the following command on the PDB: sql> alter session set container=vis;
@?/javavm/install/update_javavm_db.sql
Now run the test again: SQL> select dbms_java.get_jdk_version() from dual;
|
Related resources
Additional information
Maintenance operations
Patching Oracle E-Business Suite database home with new patches
As the bin volume (/rdsdbbin) is an out-of-place upgrade, the contents of the bin volume are dropped during the CEV upgrade. Therefore, you have to create a copy of the appsutil directory before you perform any upgrades by using CEV.
On the source Amazon RDS Custom instance, before you upgrade the CEV, take a backup of $ORACLE_HOME/appsutil.
Note: This example uses an NFS volume. However, you can use a copy to Amazon Simple Storage Service (Amazon S3) instead.
1. Make a directory to store appsutil on the source Amazon RDS Custom instance:
$ mkdir /RMAN/appsutil.preupgrade
2. Tar and copy to the Amazon EFS volume:
$ tar cvf /RMAN/appsutil.preupgrade appsutil
3. Verify that the tar file exists:
$ bash-4.2$ ls -l /RMAN/appsutil.preupgrade
-rw-rw-r-- 1 rdsdb rdsdb 622981120 Feb 8 20:16 appsutil.tar
4. Upgrade to the latest CEV (prerequisite CEV is already created) by following the instructions in Upgrading an RDS Custom DB instance in the Amazon RDS documentation).
You can also patch directly by using OPATCH. See the Requirements and considerations for RDS Custom for Oracle Upgrades section of the Amazon RDS documentation.
Note: The IP address of the host machine does not change during the CEV patching process. This process performs an out-of-place upgrade, and during startup a new bin volume is attached on the same instance.
