Drop SQL Profile

Step-1: Check below SQL Profile: SYS_SQLPROF_7cd65e0000

SQL> select name from dba_sql_profiles;


SQL> select NAME,CATEGORY,TYPE,STATUS,TASK_ID,TASK_EXEC_NAME from dba_sql_profiles where NAME like '%7cd65e0000%';


Step-2: Drop below SQL Profile: SYS_SQLPROF_7cd65e0000

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_7cd65e0000');


SQL> select NAME,CATEGORY,TYPE,STATUS,TASK_ID,TASK_EXEC_NAME from dba_sql_profiles where NAME like '%7cd65e0000%';


Drop SQL Plan directives:
==========================

Step-2: Drop SQL Plan directives:

SQL> EXEC DBMS_SPD.flush_sql_plan_directive;

PL/SQL procedure successfully completed.

SQL> BEGIN
FOR rec IN (SELECT directive_id did FROM DBA_SQL_PLAN_DIRECTIVES)
LOOP
DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE (directive_id => rec.did);
END LOOP;
END;   2    3    4    5    6
  7  /

PL/SQL procedure successfully completed.

SQL>

Step-3:
Set the optimizer_adaptive_features to FALSE. They can Ref. the DOC ID 396009.1 for the recommended parameters for EBS.
--

SQL> show parameter optimizer_adaptive_features

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features          boolean     TRUE
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH;

Optimizer Parameters

Optimizer Parameters
 

ð  Release 12 uses cost based optimization. The following optimizer  parameters must be set as shown, and should not be changed.
 

ð  It is recommended to disable the adaptive optimizer features: adaptive plans,  automatic re-optimization, and SQL plan directives. 

ð  Post 12.1.0.2.170831 (Aug 2017) Bundle Patch for Windows Platforms or higher

ð  Remove optimizer_adaptive_features from pfile/spfile and replace it with optimizer_adaptive_plans=FALSE  and optimizer_adaptive_statistics=FALSE

ð  For more details Refer MOS Doc ID 1594274.1(Table 1.5 - Footnote 1).

_optimizer_autostats_job=FALSE #MP Turn off automatic statistics.
optimizer_adaptive_features = FALSE #MP 

Parallel Execution and Oracle RAC parameters

Parallel Execution and Oracle RAC parameters

ð  It is recommended to set the parameters PARALLEL_FORCE_LOCAL on each instance, to ensure that parallel requests do not span instances.

ð  As of 11gR2, EBS customers must set the value of this parameter to TRUE and then are no longer required to set parallel_instance_groups and instance groups for the purpose of preventing inter-instance sql parallelism in RAC environments.

parallel_force_local=TRUE

TEMP_UNDO_ENABLED

TEMP_UNDO_ENABLED

ð  TEMP_UNDO_ENABLED helps to reduce the amount of redo caused by DML on global temporary tables.

ð   Setting to TRUE may cause serious issues, such as ORA-55526, for distributed transactions (Ref. Bug 20712819). 

ð  The recommended value for systems using distributed transactions is currently FALSE (Pending ER 24286334). 

ð  If not using distributed transactions, TRUE will improve performance by eliminating REDO on permanent UNDO.

temp_undo_enabled = FALSE

Database Password Case Sensitivity

Database Password Case Sensitivity

ð  The default value of this parameter is TRUE, i.e. passwords are case-sensitive at the database level.

ð  Although the parameter is deprecated in Database 12c, it still needs to be explicitly set for Oracle E-Business Suite.

ð  To enable case-sensitivity of database passwords in Oracle E-Business Suite (supported with Oracle E-Business Suite Release 12.1.1 or higher), set the parameter to TRUE and follow the steps in Doc ID 1581584.1. Also, if on a release prior to Release 12.2,  apply patch 12964564.

ð  To disable case-sensitivity, set the parameter to FALSE.

sec_case_sensitive_logon = FALSE

PGA_AGGREGATE_LIMIT feature in 12c limits PGA memory usage

PGA_AGGREGATE_LIMIT feature in 12c limits PGA memory usage

·         The default value of PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter.

·          It will not exceed 120% of the physical memory size minus the total SGA size.

·         PGA_AGGREGATE_LIMIT cannot be set below its default value. If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.

·         If total PGA memory usage is over PGA_AGGREGATE_LIMIT value. The sessions or processes that are consuming the most untunable PGA memory will be terminated.

·         Recommended value for PGA_AGGREGATE_LIMIT is 0.

·         pga_aggregate_limit = 0

FS_CLONE issues

FS_CLONE issues

Issue 1:

There is not enough space on the volume you have specified.

Oracle Universal Installer has detected that you currently have 0 MB available on the chosen volume. 750 MB of space is required for the software.

Solution:

Rapidwiz File System Upgrade Fails With "There is not enough space on the volume you have specified." Even Though There Is Sufficient Space (Doc ID 1942808.1)

ð   As per note id, we have requested SA team to reduce the mount point((/test/applmgr) size from 4TB to 2TB

Issue 2:

ERROR: RC-50410: Fatal: OUI Registration failed with an Abnormal Termination error during call from ouicli.pl.

Solution:

Fs_Clone fails with "ERROR: RC-50410: Fatal: OUI Registration failed with an Abnormal Termination error" (Doc ID 2095966.1)

ð  As per node id, we have unset the $ENV

Issue 3:

[UNEXPECTED]Error 1 occurred during Blocking ports on hostname01

[UNEXPECTED]Error 1 occurred during Blocking ports on hostname02

Patch filesystem services were running on hostname01 and hostname02

Solution:

ð   Issue got resolved after clearing the services related to patch filesystem(fs1).