Script otamlsw.odf Fails during Application of Patch 6678700

Action plan to avoid otamlsw.odf  job issue

Issue:

DROP INDEX OTA.OTA_CATEGORY_USAGES_TL_PK
AD Worker error:
The following ORACLE error:

ORA-02429: cannot drop index used for enforcement of unique/primary key

occurred while executing the SQL statement:

DROP INDEX OTA.OTA_CATEGORY_USAGES_TL_PK

AD Worker error:
Unable to compare or correct tables or indexes or keys
        because of the error above

Solution:

Disable the constraint OTA_EVENT_ASSOCIATIONS_UK2 as follows:

SQL> select * from dba_constraints
     where constraint_name = 'OTA_EVENT_ASSOCIATIONS_UK2'

SQL> alter table OTA_EVENT_ASSOCIATIONS disable constraint OTA_EVENT_ASSOCIATIONS_UK2;

Reference:

-Oracle SR 3-6974638882
-Doc ID 1312500.1: Under What Conditions Can Adding A Primary Key Constraint Not Create An Index?
-Doc ID 370633.1: ALTER TABLE DROP PRIMARY KEY CONSTRAINT DOES NOT DROP

Script Czhist.Sql Fails during Application of Patch 6678700

Execute below procedure to avoid issue (Script Czhist.Sql Fails during Application of Patch 6678700)

SQL> execute DBMS_STATS.UPGRADE_STAT_TABLE('APPLSYS','FND_STATTAB');

How to Unlock the stats for given tables

Identify the tables whose stats are locked by using below query:

Unlock the stats for given tables:

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null
and owner = 'APPLSYS' -- Schema Name

Unlock stats using below script:

SQL> exec dbms_stats.unlock_table_stats('owner', 'table_name');

For Example:

SQL> sho user
USER is "SYS"
SQL>
SQL> exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');

PL/SQL procedure successfully completed.

Unable to find an Output Post Processor service to post-process request in PCP environment

Problem Description:

Concurrent requests getting completed in warning status which are running on CM Admin node 2 in PCP environment.

Error in Log File:

POST-PROCESSING ---------+ Unable to find an Output Post Processor service to post-process request 13343094. Check that the Output Post Processor service is running.

Cause:

OPP manager does not exist on secondary admin node.

Solution:

In a Parallel Concurrent Processing (PCP) environment an need to create additional Output Post Processing (OPP) concurrent managers in nodes other than the primary node.

Reference:

How to Create a Second OPP Concurrent Manager in a Node Different Than The Primary Node (Doc ID 729883.1)

Intermittent Login issue R12.1 - 500 Internal Server Error

Problem Description:

Users are unable to get application login page.

Users are frequently getting below error while accessing the EBS application URL.

Cause:

This issue occurred due to low oacore heap size.

Solution:

Increase the oacore heap size and restart application services.

Change the parameters in $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml

of both form nodes as shown below:

From:

-Xmx512M -Xms128M -XX:MaxPermSize=160M

To:

-Xmx1536M -Xms128M -XX:MaxPermSize=160M

Reference:

JVM: Guidelines to setup the Java Virtual Machine in Apps E-Business Suite 11i and R12 (Doc ID 362851.1)

Intermittent Login issue 11i - 500 Internal Server Error

Check Logs for Error Details:

[appltest@appnode jvm]$ pwd
/oraclesw/testora/iAS/Apache/Jserv/logs/jvm
[appltest@appnode jvm]$

[appltest@appnode jvm]$ tail -15f OACoreGroup.0.stderr
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
ApacheJServ/1.1.2: An error occurred listening to the port: java.lang.OutOfMemoryError
-------------------------------
Thu Nov 12 13:50:09 PST 2015

Increase the JVM Memory:

To increase the memory for oacore JVM,
edit the file   $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties

From:

wrapper.bin.parameters=-verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB

To:
wrapper.bin.parameters=-verbose:gc -Xmx1024M -Xms512M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB

Bounce Apache Services:

./adapcctl.sh stop
./adapcctl.sh start

Verify in below log file to confirm JVM started with updated memory parameter:

[appltest@appnode jvm]$ pwd
/oraclesw/testora/iAS/Apache/Jserv/logs/jvm

[appltest@appnode jvm]$ tail -f OACoreGroup.0.stdout

-------------------------------
Mon Nov 23 00:44:25 PST 2015
-------------------------------
/usr/java/j2sdk1.4.2_15/jre/bin/java -DCLIENT_PROCESSID=11905 -Xmx1024M -Xms512M -XX:NewSize=60M -XX:MaxPermSize=128M -XX:MaxNewSize=120M -Xrs

Also Modify the JVM parameter in CONTEXT_FILE, so next time when we run the autoconfig this will make changes permanently:

Parameter : 
<jvm_options oa_var="s_jvm_options" osd="Linux">-Xmx1024M -Xms512M -XX:NewSize=60M -XX:MaxPermSize=128M -XX:MaxNewSize=120M -Xrs</jvm_options>

Read Only APPS User Creation (APPSRO)

Step 1: Create the read-only schema, in this case lets call it APPSRO

bash $ sqlplus "/ as sysdba"

SQL> create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;

SQL> grant connect to appsro;

SQL> grant ALTER SESSION to appsro;

SQL> exit;

Step 2: Connect with APPS User and Create XX_GRANTS_FAIL_APPSRO To Save the Details of Failed Grants

conn apps/<passwd> ;


create table XX_GRANTS_FAIL_APPSRO ( 
        object_name VARCHAR2(100)
       ,sqlerrm varchar2(2000)
       ,creation_date DATE 
        );

grant all on XX_GRANTS_FAIL_APPSRO to apps with grant option;

grant select on XX_GRANTS_FAIL_APPSRO to appsro ;

Step 3: Granting SELECT on All synonyms and views to APPSRO

conn apps/<passwd> ;

PROMPT This can take upto 15-30 minutes
PROMPT Granting SELECT on All synonyms and views to appsro
DECLARE
--One off script to execute grants to appsro
  v_error VARCHAR2(2000);
BEGIN

  FOR p_rec IN (SELECT *
                FROM   all_objects
                WHERE  owner = 'APPS'
                AND    object_type IN ('SYNONYM', 'VIEW')
                AND    object_name NOT LIKE '%\_S' escape '\')
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'grant select on ' || p_rec.object_name ||
                        ' to appsro';
    EXCEPTION
      WHEN OTHERS THEN
        v_error := substr(SQLERRM, 1, 2000);
        INSERT INTO apps.XX_GRANTS_FAIL_APPSRO
          (object_name
          ,SQLERRM
          ,creation_date 
          )
        VALUES
          (p_rec.object_name
          ,v_error
          ,sysdate
          );
    END;
  END LOOP;
  COMMIT;
END;
/

Step 4: Write a after logon trigger on appsro schema. The main purpose of this trigger is to alter the session to apps schema, such that the Current Schema will be set to apps for the session (whilst retaining appsro restrictions).In doing so your logon will retain the permissions of appsro schema(read_only). However it will be able to reference the apps objects with exactly the same name as does a direct connection to apps schema.

This will eliminate creation of synonyms in APPSRO Schema.

conn apps/<passwd> ;

PROMPT CREATE OR REPLACE TRIGGER xx_appsro_logon_trg
CREATE OR REPLACE TRIGGER xx_appsro_logon_trg
--Trigger to toggle schema to apps, but yet retaining appsro resitrictions
  AFTER logon ON appsro.SCHEMA
DECLARE
BEGIN
  EXECUTE IMMEDIATE 
          'declare begin ' ||
          'dbms_application_info.set_client_info ( 101 ); end;';
  EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/

Step 5: Create a Trigger on the apps schema to issue select only grants for all new views and synonyms. Please note that - excluding grants for sequences. SELECT grants for views and synonyms will be provided to appsro as and when such objects are created in APPS schema.

conn apps/<passwd> ;

PROMPT CREATE OR REPLACE TRIGGER xx_grant_appsro
CREATE OR REPLACE TRIGGER xx_grant_appsro
  AFTER CREATE ON APPS.SCHEMA
DECLARE
  l_str VARCHAR2(255);
  l_job NUMBER;
BEGIN
  IF (ora_dict_obj_type IN ('SYNONYM', 'VIEW'))
     AND (ora_dict_obj_name NOT LIKE '%\_S' escape '\')
  THEN
    l_str := 'execute immediate "grant select on ' || ora_dict_obj_name ||
             ' to appsro";';
    dbms_job.submit(l_job, REPLACE(l_str, '"', ''''));
  END IF;
END;
/

APPSRO Read Only User is Ready to Use, You can validate by Selecting couple of tables by connecting to APPSRO schema.