Oracle 19c Repository Database Configuration for OEM 13.4 - Oracle Enterprise Manager Cloud on Oracle Linux 8.2 - Part 1

January 11, 2021  

Hi All,

Hardware Pre-requisites:

Virtual Machine with the following configuration:

vCPU 6 Nos.

RAM 10-12GB 

100 GB Storage

Software Pre-requisites:

Download all the files including .bin in same directory. No need to unzip the downloaded software.

As the install user who will be installing the product, set the execute permission for the .bin file.

Example: chmod +x em13400_linux64.bin

At the CDB level:

SGA_TARGET should be at least 3GB

SHARED_POOL_SIZE should be at least 1GB

There should be at least 3 redo log groups, each at least 300MB

At the PDB level:

After setting this, restart the database.

Note: as per MOS note #2635383.1, disable the following parameters for the installation and enable then after the installation is finished. This is to avoid the “Check if all adaptive features parameters are unset” error.

13.4: Installing/Upgrading OMS to 13.4 Fails With Error: Check if all adaptive features parameters are unset (Doc ID 2635383.1)

CAUSE

For improved SQL performance all the adaptive features parameters should be unset prior to the Install/Upgrade.

SOLUTION

Pre Installion/Upgrade

Case A) If the Management Repository is using Oracle Database 12.1.0.2.0 and patch 22652097 has NOT been applied, ensure that you disable the optimizer adaptive features by connecting to the database as SYSDBA and running the following:

SQL> alter system set optimizer_adaptive_features=false scope=both

To verify that the changes have taken effect, run the following command:

show parameter adaptive;

Case B) If the Management Repository is using Oracle Database 12.1.0.2 with patch 22652097 applied or the Management Repository is using Oracle Database 12.2, 18.x or 19.x, you need to do the following:

1) As SYSDBA execute the following statements in the repository DB:

alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both sid='*';

alter system set "_optimizer_strans_adaptive_pruning" = FALSE scope=both sid='*';

alter system set "_px_adaptive_dist_method" = OFF scope=both sid='*';

alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both sid='*';

alter system set "_optimizer_dsdir_usage_control" = 0 scope=both sid='*';

alter system set "_optimizer_use_feedback" = FALSE scope=both sid='*';

alter system set "_optimizer_gather_feedback" = FALSE scope=both sid='*';

alter system set "_optimizer_performance_feedback" = OFF scope=both sid='*';

2) Restart the repository database.

Retry the Install/Upgrade once the settings have taken effect.

Post Installation/Upgrade:

Case A) If the Management Repository is using Oracle Database 12.1.0.2.0 and optimizer adaptive features was disabled prior to the Install/Upgrade, enable it back:

SQL> alter system set optimizer_adaptive_features=true scope=both

Case B) After installing Enterprise Manager Cloud Control 13c Release 4, if the Management Repository is using Oracle Database 12.2, 18.x or 19.x, Oracle recommends to reset the optimizer adaptive features parameters by doing the following:

Connect to the database as SYSDBA and run the following commands:

alter system reset "_optimizer_nlj_hj_adaptive_join" scope=both sid='*';

alter system reset "_optimizer_strans_adaptive_pruning" scope=both sid='*';

alter system reset "_px_adaptive_dist_method" scope=both sid='*';

alter system reset "_sql_plan_directive_mgmt_control" scope=both sid='*';

alter system reset "_optimizer_dsdir_usage_control" scope=both sid='*';

alter system reset "_optimizer_use_feedback" scope=both sid='*';

alter system reset "_optimizer_gather_feedback" scope=both sid='*';

alter system reset "_optimizer_performance_feedback" scope=both sid='*';

Cheers!

Ramesh.