Enabling OTA for R12 :

Enabling OTA for R12 :

Rather than manually modifying the orion-web.xml file, do the following to set the Autoconfig oavar variable:

1) Logon as the System Administrator Responsibility.

2) Under Oracle Applications Manager, select Autoconfig.

3) Click the icon for Edit Parameters on the Applications tier.

4) Click on the System tab.

5) Look under oa_web_server for Load OXTA Servlet (s_load_oxta_servlet).

6) If this is currently set to -1 (default disabled configuration), you need to set the value to 1 (enable).

7) Look in this same section for the following and set as needed (if you have a proxy server that http/https communications need to go through):

OXTAOutUseProxy (s_oxta_proxy)

OXTAOutProxyHost (s_oxta_proxyhost)

OXTAOutProxyPort (s_oxta_proxyport)

Once you make changes, you will need to run Autoconfig to apply them and then bounce the applications tier to start up the OXTA.

Additional Parameters that are Set Through AutoConfig for XML Gateway OTA:

applSysSchema : APPLSYS (this indicates queues are in applsys schema)

OXTAInPoolSize=1

OXTAOutThreads=1 (These 2 set the number of DB connections in your system for inbound and outbound requests)

OXTAOutUseProxy

OXTAOutProxyHost

OXTAOutProxyPort (These control whether a proxy is used for outgoing socket connections).

Parameters not Set Through AutoConfig:

OXTAOutBaseTimeout =10

OXTAOutLinearTimeout = 500

OXTAOutMaxAttempts= 5 (these control the time allowed for transactions to complete).

OXTAInMaxContent=1000000 (maximum payload size in bytes).

Preserving Autoconfig parameters

To preserve parameters for XML Gateway in Autoconfig that do not appear via the web interface, please refer to:

Note 829449.1 - Preserve System Properties Customized For Your ECX (XML Gateway)

Inbound OTA URL

The new parameter for posting inbound XML transactions to OTA is now the following URL:

http://<WebEntryURL/webservices/TransportAgentServer

12.1.1+ OTA Failure

The OAFM Container will not start OTA if the ASADMIN EBS user is not enabled.

Error:

Exception creating connection pool. Exception: oracle.oc4j.sql.DataSourceException: Exception setting the property 'dbcFile' on the DataSource. Exception: java.lang.reflect.InvocationTargetException

Note 1087499.1 - Cannot Start OXTA After Upgrade To R12.1.x

Solution:  Setup/Configuration Changes

In E-Business Suite Applications (EBS) Release 12 (R12) and Release 12.1 (R12.1), Jserv is no longer used in the applications tier for java components. It has been replaced by the Oracle Containers for J2EE (OC4J) component. The configuration and setup for Oracle Transport Agent (OTA) has changed accordingly.

In EBS Release 11i instances where OTA was utilized, OTA ran under Jserv having its properties set in the files found in the directory:

$IAS_ORACLE_HOME/Apache/Jserv/etc

11.5.10/11.5.10.2 - xmlsvcs.properties

For R12 (versions 12.0.X or 12.1.X) , OTA is run in the oafm container under OC4J. The system properties are read from the oc4j.properties file of the oafm container located in the file

$INST_TOP/ora/10.1.3/j2ee/oafm/config/oc4j.properties    

There is also an autoconfig property, <load-on-startup>, as reflected in the orion-web.xml file:

$INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/oafm/webservices/orion-web.xml

In the orion-web.xml file the following section will be found:

<!-- OXTA Servlet -->

<load-on-startup>1</load-on-startup>

To verify that OTA is started, the value of the attribute <load-on-startup> in orion-web.xml should be set to 1 for enabled. By default, this is set to -1, or disabled.

The AutoConfig oavar name for <load-on-startup> is s_load_oxta_servlet.

URL getting error like HTTP 500 Internal server error after Cloning :

Error:-

URL getting error like HTTP 500 Internal server error after Cloning :

ORA-01578: ORACLE data block corrupted (file # 377, block # 4205)
ORA-01110: data file 392: '/u01/app/oracle/db/apps_st/data/a_txn_data01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Solution:-

Partitions in Workflow Local Tables are Automatically Switched to NOLOGGING [ID 433280.1]

How to identify all the Corrupted Objects in the Database with RMAN [ID 472231.1]

SQL> select b.tablespace_name, count(BLOCK#) from v$database_block_corruption a, dba_data_files b where a.FILE#=b.FILE_ID group by b.tablespace_name

WF_LOCAL_ROLES

WF_LOCAL_USER_ROLES

WF_USER_ROLE_ASSIGNMENTS

Set all partitions and staging tables to use LOGGING.

1) For any partition that is set to NOLOGGING, manually alter it to LOGGING:

1.1) SQL to run to retrieve partitions that are set to NOLOGGING:

select table_name, partition_name

from dba_tab_partitions

where table_name in

('WF_LOCAL_ROLES' ,

'WF_LOCAL_USER_ROLES' ,

'WF_USER_ROLE_ASSIGNMENTS')

and logging = 'NO' ;

1.2) switch all partitions retrieved in step 1.1 to LOGGING = 'YES' using

the following syntax:

alter table applsys.<table_name> modify partition <partition_name> logging;

Example:

alter table applsys.WF_LOCAL_USER_ROLES modify partition ENG_LIST logging;

2) For any stage table that is set to NOLOGGING, manually alter them to LOGGING.

2.1) SQL to run to retrieve stage tables set to NOLOGGING:

select table_name

from dba_tables

where table_name in

('WF_LOCAL_ROLES_STAGE' ,

'WF_LOCAL_USER_ROLES_STAGE' ,

'WF_UR_ASSIGNMENTS_STAGE')

and logging = 'NO' ;

2.2) Switch all the tables retrieved by statement above to LOGGING = 'YES' using the

following syntax:

alter table applsys.<table_name> logging;

example:

alter table applsys.WF_LOCAL_ROLES_STAGE logging;

3) Then run the synchronize WF local tables concurrent program with logging mode = 'LOGGING'.

4) After completion, monitor tables and partitions: they should remain to LOGGING = 'YES'.

How to Recover “APPS” PASSWORD – R12

Note : it is very easy to break / decrypt the apps password ,who are well deserved on APPS:

Step by step to retrieve apps password:

Step 1: To create function for encrypt password

Log in   DB-node on oracle user / as sysdba

SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)

RETURN VARCHAR2

AS

LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

  /

Function created.

Step 2: To find the encrypt password

SQL> set linesize 10000;

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD

----------------------------------------------------------------------------------------------------

ZG6E0BA81F7BB8B47EF79CD47E3A9F1FB25FB8D95055EEC767BB88E54B90F3B30A85C577614C0D62C0442A4CC7F3979B1F65

Step 3:To find  converted  Decrypt  password

SQL>  SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG6E0BA81F7BB8B47EF79CD47E3A9F1FB25FB8D95055EEC767BB88E54B90F3B30A85C577614C0D62C0442A4CC7F3979B1F65') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG6E0BA81F7BB8B47EF79CD47E3A9F1FB25FB8D950

--------------------------------------------------------------------------------

SUPTAPPS01

Step 4: Please test the apps password

SQL> conn apps/SUPTAPPS01

Connected.

SQL> show user

USER is "APPS"

++++++++++++++++Manjunatha++++++++++++++++

How to add SYSADMIN responsibility to a user like MANJURED

Login to SYSADMIN user

++++++++++++++++++

Creation of User

Navigation – 

System Administrator ->                                        Security ->                                                            User ->                                                                           Define ->                                             enter new user name & password 

+++++++++++++++++++++++++++++++++++++++++++

Login to apps user

SQL>

BEGIN

 

   FND_USER_PKG.ADDRESP(

      USERNAME        =>  'MANJURED',             

      RESP_APP        =>  'SYSADMIN',            

      RESP_KEY        =>  'SYSTEM_ADMINISTRATOR',

      SECURITY_GROUP  =>  'STANDARD',

      DESCRIPTION     =>  NULL,

      START_DATE      =>  SYSDATE,

      END_DATE        =>  NULL);

   COMMIT;

 

   DBMS_OUTPUT.PUT_LINE('SYSADMIN Responsibility successfully added');

   

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('SYSADMIN responsibility not added due to ' || SQLERRM);

      ROLLBACK;

END;

+++++++++++++++++++++++++++++++++++++++

Ho to re-collect SYSADMIN password in EBS R12 :

Step 1: create package through apps user :-

SQL> CREATE OR REPLACE PACKAGE XXARTO_GET_PWD AS

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2;

END XXARTO_GET_PWD;  2    3    4

  5  /

Package created.

Step 2: create package body

SQL> CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD AS

  2  FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

  3  RETURN VARCHAR2 AS

  4  LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

  5  (java.lang.String,java.lang.String) return java.lang.String';

  6  END XXARTO_GET_PWD;

  7  /

Package body created.

Step 3:-

SQL> SELECT Usr.User_Name,

  2  Usr.Description,

  3  XXARTO_GET_PWD.Decrypt (

  4  (SELECT (SELECT XXARTO_GET_PWD.Decrypt (

  5  Fnd_Web_Sec.Get_Guest_Username_Pwd,

  6  Usertable.Encrypted_Foundation_Password)

  7  FROM DUAL)

  8  AS Apps_Password

  9  FROM applsys.Fnd_User Usertable

10  WHERE Usertable.User_Name =

11  (SELECT SUBSTR (

12  Fnd_Web_Sec.Get_Guest_Username_Pwd,

13  1,

14  INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd,

15  '/')

16  - 1)

17  FROM DUAL)),

18  Usr.Encrypted_User_Password)

19  Password

20  FROM applsys.Fnd_User Usr

21  WHERE Usr.User_Name = '&User_Name';

Enter value for user_name: SYSADMIN

old  21: WHERE Usr.User_Name = '&User_Name'

new  21: WHERE Usr.User_Name = 'SYSADMIN'

USER_NAME         DESCRIPTION             PASSWORD

-------------------    ------------------------      ---------------------------

SYSADMIN        System Administrator      svrprd6uk

How do I recover the Applications context file if it is lost or deleted accidentally?

The Applications context file can be retrieved by running the adclonectx.pl script.

To retrieve the applications tier context file,

• Execute the following command on the applications tier. 

perl <COMMON_TOP>/clone/bin/adclonectx.pl retrieve

• On being prompted for the context file to be retrieved, select the option of retrieving the applications tier context file that has been lost and retrieve it to the default location specified by the script.

The above command can be used only when the <INST_TOP> is still intact. In case the <INST_TOP> has also been lost accidentally, the applications tier context file may be retrieved as follows:

• Execute the following command on the database tier:

perl <ORACLE_HOME>/appsutil/clone/bin/adclonectx.pl retrieve

• On being prompted for the context file to be retrieved, select the option of retrieving the applications tier context file that has been lost.
• While confirming the location for the context file, set it to any existing directory with write permission.
• Once the context file has been generated in the specified location, move it to the location specified for the context file in the context variable 's_contextfile'.

To retrieve the database tier context file,

• Execute the following command on the database tier:

perl <ORACLE_HOME>/appsutil/clone/bin/adclonectx.pl retrieve

• On being prompted for the context file to be retrieved, select the database tier context file and retrieve it to the default location specified by the script.

++++++++++++++++++++++++++++++++

svrhrsuas2:[SVRSUPT]:/u401/app/oracle/SVRSUPT/apps/apps_st/comn/clone/bin:>

# perl adclonectx.pl retrieve

                     Copyright (c) 2002 Oracle Corporation

                        Redwood Shores, California, USA

                        Oracle Applications Rapid Clone

                                 Version 12.0.0

                      adclonectx Version 120.23.12010000.1

Running:

/u401/app/oracle/SVRSUPT/apps/apps_st/comn/clone/bin/../jre/bin/java -Xmx600M -classpath /u401/app/oracle/SVRSUPT/apps/apps_st/comn/clone/bin/../jlib/ojdbc14.jar:/u401/app/oracle/SVRSUPT/apps/apps_st/comn/clone/bin/../jlib/xmlparserv2.jar:/u401/app/oracle/SVRSUPT/apps/apps_st/comn/clone/bin/../jlib/java oracle.apps.ad.context.RetrieveContext

Log file located at /u401/app/oracle/SVRSUPT/apps/apps_st/comn/clone/bin/RetrieveContext_03301309.log

Database Host [svrhrsuas2]:cgvmhrsudb2

Database Host Domain [singiri.com]:

Database Port [1521]:1525

Database SID:SVRSUPT

APPS Schema Name [APPS]: apps

APPS Schema Password: apps

The following context files are available:

[1] SVRSUPT_svrhrsuas2 (APPL_TOP Context)

[2] SVRSUPT_ svrhrsuas 1 (APPL_TOP Context)

[3] SVRSUPT_ svrhrsudb (Database Context)

Select the context to retrieve [1]:1

Destination file path [/home/SVRSUPT/apps/SVRSUPT_svrhrsuas2/appl/admin/SVRSUPT_svrhrsuas2.xml]:

Retrieved context file to /home/SVRSUPT/apps/SVRSUPT_svrhrsuas2/appl/admin/SVRSUPT_svrhrsuas2.xml

Log file location: /u401/app/oracle/SVRSUPT/apps/apps_st/comn/clone/bin/RetrieveContext_03301309.log

++++++++

Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 (Doc ID 387859.1)

++++++++

SQL*Loader-643: error executing INSERT statement for table ...

ERROR :-

“SQL*Loader-643: error executing INSERT statement for table “SVR_STG"."STG_XML_FILES"

ORA-03113: end-of-file on communication channel

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.”

Reason : XML - EVENT :

Solution :-

 $ connect / as sysdba
 SQL>  alter system set events '31156 trace name context forever, level 0x400';

           System altered.

SQL> exit
    

Following syntax works -

SQL> alter system set events '31156 trace name context forever, level 0x400';

System altered.

Correct method to set the event in the SPFILE is to use the below SQL syntax:

SQL> alter system set event='31156 trace name context forever, level 0x400' scope=spfile;

or

SQL> alter system set event='31150 trace name context forever, level 0x400' scope=spfile;

The instance must be restarted for the events to take effect.

Other way to set the event at database level is to set it in parameter file using the syntax below -

event="31150 trace name context forever, level 0x400"

or

event="31156 trace name context forever, level 0x400"

If you are using SPFILE then you may have to generate the pfile from SPFILE using the steps from below note-

How to Modify the Content of an SPFILE Parameter File (Doc ID 137483.1)

Modify the PFILE parameter value with a text editor and save the file, e.g. -

*.event='31150 trace name context forever, level 0x4000'

Recreate the SPFILE from the modified PFILE.

++++++++++++++++++++++++++++