Sunday 16 March 2014

how to handle inbound connection timed out (Fatal NI connect error 12170, ORA-3136)

how to handle inbound connection timed out (ORA-3136)?


Today when going through regular activities like alert log checking, i found the following warning in one of database
Tue Mar 16 10:44:55 2014
WARNING: inbound connection timed out (ORA-3136)
Also, following entry was there in sqlnet.log file
Fatal NI connect error 12170.
  VERSION INFORMATION:
 TNS for 64-bit Windows: Version 11.1.0.7.0 – Production
 Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 – Production
 Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 – Production
  Time: 15-JUN-2010 12:58:20
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.3.1.1)(PORT=1521))
To my surprise, in next 2 min i got a ticket stating user is facing problem in connecting to database. After waiting for long time, user is getting error in connection establishment.
After analysis, i found following
user connectivity time bound is 60 sec from 10.2 which means oracle will try to establish connection for 60 seconds, once it crosses that limit, it will send error to user process.
This error you will not see in 10.1 version, because in that version time bound limit in infinite.
I did following which resolved my problem
1) Add following entires in listener.ora file on database server
INBOUND_CONNECT_TIMEOUT_<LISTENER_NAME>=0
DIRECT_HANDOFF_TTC_<LISTENER_NAME>=OFF
alternatively you can also do this
lsnrctl> set inbound_connect_timeout=0
2) add following entries in sqlnet.ora of database server
SQLNET.INBOUND_CONNECT_TIMEOUT=0
we are setting time bound limit to infinite using above parameters. you need to reload the listener after performing above changes using
$ lsnrctl reload
Note : Always take backup of listener.ora or sqlnet.ora files before modifying anything inside
More about this error, you can check in metalink docs 465043.1 and 345197.1
Sometimes, this problem will also occur if you have firewall restrictions. So please check from that end too.
Posted by at No comments:

Tuesday 4 March 2014

CUSTOM TOP CREATION IN Oracle EBS R12


Make the Directory Structure for your Custom Application Files

cd $APPL_TOP
mkdir XXERP
mkdir XXERP/12.0.0
mkdir XXERP/12.0.0/admin
mkdir XXERP/12.0.0/admin/sql
mkdir XXERP/12.0.0/admin/odf
mkdir XXERP/12.0.0/sql
mkdir XXERP/12.0.0/bin
mkdir XXERP/12.0.0/reports
mkdir XXERP/12.0.0/reports/US
mkdir XXERP/12.0.0/forms
mkdir XXERP/12.0.0/forms/US
mkdir XXERP/12.0.0/lib
mkdir XXERP/12.0.0/out
mkdir XXERP/12.0.0/log
mkdir XXERP/12.0.0/help
mkdir XXERP/12.0.0/html
mkdir XXERP/12.0.0/java
mkdir XXERP/12.0.0/mds
mkdir XXERP/12.0.0/media
mkdir XXERP/12.0.0/mesg
mkdir XXERP/12.0.0/patch 
2) Add the custom module into the environment
cd $APPL_TOP
echo “XXERP_TOP=/d01/oracle/PROD/apps/apps_st/appl/XXERP/12.0.0″ &gt;
export XXERP_TOP
source the environment file ( /d01/oracle/PROD/apps/apps_st/appl/customPROD_linux7.env )
echo “export XXERP_TOP ” &gt;&gt; customPROD_linux7.env
Make entry to context file
/d01/oracle/PROD/inst/apps/customPROD_linux7/appl/admin/customPROD_linux7.xml
/d01/oracle/PROD/apps/apps_st/appl/XXERP/12.0.0
cd $INST_TOP/admin/install
sh adgentopfile.sh
Open new session, source environment file, and stop middle tier services, run autoconfig
Open new session, source environment file, check for custom top in topfile.txt in $APPL_TOP/admin, 
start the middle tier services.
3) Create Tablespace
create tablespace XXERP datafile ‘/sandb1/oracle/TEST/db/apps_st/data/XXERP01.dbf’ size 500M
4) Create USER
create user XXERP identified by XXERP
default tablespace XXERP
temporary tablespace temp1
quota unlimited on XXERP
grant connect, resource to XXERP;
5) Register your Oracle Schema
 Login to Applications with System Administrator responsibility
Navigate to Application–>Register
Application = XXERP Custom Application
Short Name = XXERP
Basepath = XXERP_TOP
Description = XXERP Custom Application
6) Register Oracle User
 Naviate to Security–>Oracle–>Register
Database User Name = XXERP
Password = XXERP
Privilege = Enabled
Install Group = 0
Description = XXERP Custom Application User
7) Add Application to a Data Group
 Navigate to Security–>Oracle–>DataGroup
Data Group = XXERPGroup
Description = XXERP Custom Data Group
Click on “Copy Applications from” and pick Standard data Group, then add the following entry.
Application = XXERPCustom
Oracle ID = APPS
Description = XXERPCustom Application
8) Create custom request group
 This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage)
Navigate to Security–>responsbility–>Request
Group = XXERP Request Group
Application = XXERP Custom
Code = XXERP
Description = XXERP Custom Requests
We will not define any requests to add to the group at this stage, but you can add some now if required.
9) Create custom menu
 This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage). We will create
two menus, one for Core Applications and one for Self Service.
Navigate to Application–>Menu
Menu = XXERP_CUSTOM_MENU
User Menu Name = XXERP Custom Application
Menu Type =
Description = XXERP Custom Application Menu
Seq = 100
Prompt = View Requests
Submenu =
Function = View All Concurrent Requests
Description = View Requests
Seq = 110
Prompt = Run Requests
Submenu =
Function = Requests: Submit
Description = Submit Requests
Menu = XXERP_CUSTOM_MENU_SSWA
User Menu Name = XXERP Custom Application SSWA
Menu Type =
Description = XXERP Custom Application Menu for SSWA
10) Create new responsibility. One for Core Applications and One for Self Service (SSWA)
Navigate to Security–>Responsibility–>Define
Responsibility Name = XXERP Custom
Application = XXERP Custom
Responsibility Key = XXERPCUSTOM
Description = XXERP Custom Responsibility
Available From = Oracle Applications
Data Group Name = XXERPGroup
Data Group Application = XXERP Custom
Menu = XXERP Custom Application
Request Group Name = XXERP Request Group
Responsibility Name = XXERP Custom SSWA
Application = XXERP Custom
Responsibility Key = XXERPCUSTOMSSWA
Description = XXERP Custom Responsibility SSWA
Available From = Oracle Self Service Web Applications
Data Group Name = XXERPGroup
Data Group Application = XXERP Custom
Menu = XXERP Custom Application SSWA
Request Group Name = XXERP Request Group
11) Add responsibility to user
 Navigate to Security–>User–>Define
Add XXERP Custom responsibility to users as required.
12) Other considerations
 You are now ready to create your database Objects, custom Reports, Forms, Packages, etc
Create the source code files in the XXERP_TOP directory appropriate for the type of object. For example forms would be located in $XXERP_TOP/forms/US or
package source code in $XXERP_TOP/admin/sql for example.
Database Objects, such as tables, indexes and sequences should be created in the XXERP schema, and then you need to
a) Grant all privilege from each custom data object to the APPS schema.
For example : logged in as XXERP user
grant all privileges on myTable to apps;
b) Create a synonym in APPS for each custom data object
For example : logged in as APPS user
create synonym myTable for XXERP.myTable;
13) Login to sysadmin, Application Developer Responsibility
Application > Form ( Register the form )
Application > Funtion ( Add the form to a function )
Application > Menu ( Attach the function to a menu )
Menu that is added to a particular responsbility is given to specific user
Security > User > Define
************************************
 RUN AUTOCONFIG ON APPS TIER
 BOUNCE THE APPLICATION TIER
You need a custom schema and datafile to have all your custom objects in this schema
If i create new custom_top i use below grants: - 
CREATE USER XX
IDENTIFIED BY XX
DEFAULT TABLESPACE XX
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CONNECT TO XX;
ALTER USER XX DEFAULT ROLE ALL;
GRANT CREATE VIEW TO XX;
GRANT CREATE TABLE TO XX;
GRANT CREATE SESSION TO XX;
GRANT CREATE SYNONYM TO XX;
GRANT CREATE TRIGGER TO XX;
GRANT CREATE SEQUENCE TO XX;
GRANT CREATE PROCEDURE TO XX;
GRANT CREATE PUBLIC SYNONYM TO XX;
ALTER USER XX QUOTA UNLIMITED ON XX;
ALTER USER XX QUOTA UNLIMITED ON XX_INDEX;
Posted by at No comments:

SOA integration with R12.1.3



Steps for Integrated SOA Gateway Setup

Step 1: Upgrade Oracle AS to Release 3 (10.1.3) Patch Set 5 (10.1.3.5.0)

Upgrade the Oracle Application Server on the CRP1 (VIS) instance to the Oracle Application Server 10g Release 3 (10.1.3) Patch Set 5 (10.1.3.5.0) version.
Here is the note for the steps: Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12 (Doc ID 454811.1)

Step 2: Apply Patches

After the upgrade please apply the following patches:
 Source the file $INST_TOP/ora/10.1.3/.env and then apply the following patches to the Oracle Application Server 10.1.3.5 Oracle Home:
1.       Patch 13800972: Merge Request on Top of 10.1.3.1.0 for Bugs 8857799 9223438 12352047 (Patch 13800972 is also compatible with Oracle Application Server 10.1.3.5.0.)
2.       Patch 9371120: Web Service Framework Takes More Time for Giving the Response
3.       Patch 7366746: WLP: Enabling "Use SAML Authority: Verify Signature" Doesn't Require SAML Token
4.       Patch 15914125: Merge Request on Top of 10.1.3.5.1 for Bugs 9187189 9668283 13248533

 Apply the following Oracle E-Business Suite patches:
1.       Patch 13347633:R12.OWF.B: Performance Issues in PIM Load
2.       Patch 13957925:R12.OWF.B: One-off:13029726:12.1.3: SQL Exception in SOA Monitor Page
3.       Patch 14063221:R12.OWF.B: Consolidated Fixes on Top of 11688301:R12.OWF.B
4.       Patch 9139673:R12.OWF.B: IREP Issues When APPLSYS Schema Name is Changed
5.       Patch 13516999:R12.OWF.B: Performance Degradation of Web Service Calls Hosted in R12 SOA Gateway
6.       Patch 14741766:R12.TXK.B: Need to Add a New Property for Session in OC4J.Properties

 Step 3: Setup steps

1.     Set applications environment and stop all application tier processes.
o    From the applications instance $APPL_TOP, set the environment by running the APPS<CONTEXT_NAME>.env script
o    Stop all application tier processes for the instance by running the script $ADMIN_SCRIPTS_HOME/adstpall
2.     Run TXK development script to install Oracle Application Server Adapter for Oracle Applications as shown below:
3.     $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp -applicationname=pcapps -oracleinternal=Yes -oc4jpass=welcome -runautoconfig=No

Note: If the 'oc4jadmin' password for the OAFM oc4j instance is not known, then reset the password in the file$INST_TOP/ora/10.1.3/j2ee/oafm/config/system-jazn-data.xml before running the script. Take the backupofsystem-jazn-data.xml before resetting the password. Replace the modified system-jazn-data.xml with backupofsystem-jazn-data.xml after running the script.
For example, if you want to reset the password to '!welcome', below should be the entry:
<user>
<name>oc4jadmin</name>
<display-name>OC4J Administrator</display-name>
<guid>23C8E4F0BDDE11DCBFB8AF3B7E0DDB2D</guid>
<description>OC4J Administrator</description>
<credentials>!welcome</credentials>
</user>
4.     Run TXK deployment script to install forms-c4ws.ear and configure container Forms-c4ws J2EE group correctly, as shown below:

$FND_TOP/bin/txkrun.pl -script=DeployForms-c4ws
5.     Enable the new container forms-c4ws. (By default, the new OC4J container forms-c4ws will be disabled.)
6.     To enable the new container forms-c4ws, modify the following values of context variables in the $CONTEXT_FILE:
7.     Set 's_forms-c4wsstatus' to "enabled".
8.     Set 's_forms-c4ws_nprocs' to "1".
Note: The 's_forms-c4ws_display' context variable is used by the forms-c4ws OC4J instance. It must be set properly in order to use the Java APIs for Forms interfaces. This display must always be accessible during runtime. It should be set to an active and authorized X Windows display, and should point to a machine that is always available to the application instance.
If you are planning to use the Supply Chain Management (SCM) Web services (Java APIs for Forms interfaces) published in OracleIntegration Repository that encapsulate Oracle Forms logic, see "Oracle E-Business Suite Java APIs for Forms Troubleshooting Guide, Release 12", My Oracle Support Knowledge Document 966982.1 for latest troubleshooting information related to SCM Web services.
9.     Run AutoConfig script present in $ADMIN_SCRIPTS_HOME on the application tier. For example, $ADMIN_SCRIPTS_HOME/adautocfg.sh
Ensure to start up the middle tier when AutoConfig script completes. For information on how to run AutoConfig, see "Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12", My Oracle Support Knowledge Document 387859.1 for details.
10.   Bounce OPMN & Start all application tier processes for the instance by running the script $ADMIN_SCRIPTS_HOME/adstrtal

Step 5: Setup ASADMIN user

1.    Enable ASADMIN user using the following steps:
o    Log on to Oracle E-Business Suite using sysadmin/sysadmin.
o    Select the User Management responsibility in the Navigator.
o    Click the Users link from the navigation menu to open the User Maintenance window.
o    Locate 'ASADMIN' user by entering information in the search area to retrieve the 'ASADMIN' user.
o    Click the Update icon next to the ASADMIN user to open the Update User window.
o    Remove the Active To date field and click Apply.
o    Click the Reset Password icon next to the ASADMIN user to open the Reset Password window.
o    Enter new password twice and click Submit.
2.    After activating the ASADMIN user, verify if the ASADMIN user has the 'Apps Schema Connect Role' (UMX|APPS_SCHEMA_CONNECT) role in wf_user_roles. 

If the 'Apps Schema Connect Role' role is not present in the wf_user_roles for the ASADMIN user, then run the 'Workflow Directory Services User/Role Validation' concurrent program to grant the role.
3.    Reset the 'ASADMIN' password in the file system.
After 'ASADMIN' user is enabled from Oracle E-Business Suite, update the file 
$INST_TOP/ora/10.1.3/j2ee/oafm/config/system-jazn-data.xml as shown below to reset the password: 

<user> 
<name>ASADMIN</name> 
<display-name>Default Apps SOA User</display-name> 
<description>Used by SOAProvider for DB connection</description> 
<credentials>!<NEW PASSWORD></credentials> 
</user>
4.      Note: The password should be preceded by a '!' (Exclamation) so that when OAFM is started, 
it gets encrypted. For example, if your password is 'welcome', then you should write it in the above file as "!welcome".
5.    Bounce the middle tier.

Step 6: Install the Perl Modules as per Note: 1079218.1

Note: It is required to obtain a native C compiler for the platform and operating system version that you are running on to build the perl modules. The following are the minimum versions of compilers certified for Oracle E-Business Suite platforms: 

Linux x86/x86-64: Intel C/C++ Compiler (icc) version 7.1.032 
Oracle Solaris on SPARC (64-bit): Oracle Studio 11 (5.8) 
Microsoft Windows Server (32-bit): Microsoft Visual Studio 2005 (VC 8.0) 
HP-UX Itanium: HP ANSI C B3910B A.0.06.05 
HP-UX PA-RISC (64-bit): HP92453-01 B.11.11.10 HP C Compiler 
IBM AIX on Power Systems (64-bit): XL C Enterprise 8.0
Prerequisites for Installing Perl Modules on Windows

Perform the following steps before installing Perl modules on Windows:

1. Apply Windows specific Patch 9153106:R12.OWF.B. 

2. It is necessary to create a manifest file for perl.exe in the 10.1.3Home\perl\5.8.3\bin\MSWin32-x86-multi-thread directory if your installation is on Windows. 

To create a manifest file for perl.exe:
a. Log on to the Oracle E-Business Suite middle-tier server.
b. Change directories to c:\WINDOWS\WinSxS.
c. Verify if there is a file that starts with x86_Microsoft.VC80.CRT.
For example, x86_Microsoft.VC8.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd.
d. Record this filename.
e. Change directories to where the perl.exe resides in the 10.1.3 Home. For example,
cd e:\PROD\apps\tech_st\10.1.3\perl\5.8.3\bin\MSWin32-x86-multi-thread
f. Open a file with text editor (such as Notepad) to create a manifest file.
g. Enter the following statements, for example, with the 'version' and 'publicKeyToken' taken from the x86_Microsoft.VC80.CRT file name:
manifestVersion='1.0'>

version='8.0.50727.42' processorArchitecture='x86'
publicKeyToken='1fc8b3b9a1e18e3b'/>
h. Save the file with the name perl.exe.manifest.

To install Perl modules:

1. Set the Oracle E-Business Suite application environment

From the Oracle E-Business Suite application instance APPL_TOP, set the environment by running the APPSAPPS.env(.cmd) script.

2. Set 10.1.3 ORACLE_HOME

Navigate to the /ora/10.1.3 and source the .env/.cmd file to set your 10.1.3 ORACLE_HOME.

3. Add directory $FND_TOP/perl to environment variable PERL5LIB:
a. Find physical path of $FND_TOP/perl.
b. Add this physical path in PERL5LIB variable.

Example:
export PERL5LIB=/slot/ems3404/appmgr/apps/apps_st/appl/fnd/12.0.0/perl:/slot/ems3404/appmgr/apps/tech_st/10.1.3/perl/lib/5.8.3:/slot/ems3404/appmgr/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3:/slot/ems3404/appmgr/apps/apps_st/appl/au/12.0.0/perl:/slot/ems3404/appmgr/apps/tech_st/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/i686-linux-thread-multi.

4. Use the following steps for installation on different platforms: 

On Unix:
a. Find the value of $IAS_ORACLE_HOME/perl in your environment,
For example /slot/ems1340/appmgr/apps/tech_st/10.1.3/perl.
b. Locate the $IAS_ORACLE_HOME/perl/lib/5.8.3/i686-linux-thread-multi/Config.pm.
c. Take backup of this file.
d. Replace all occurrences of /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt with value of $IAS_ORACLE_HOME/perl. 
For example, /slot/ems1340/appmgr/apps/tech_st/10.1.3/perl.

On Windows:
a. Search for all Config.pm files underneath %IAS_ORACLE_HOME%\perl, and record their location, such as:
%IAS_ORACLE_HOME%\perl\5.8.3\bin\Config.pm
%IAS_ORACLE_HOME%\perl\5.8.3\lib\MSWin32-x86-multi-thread\Config.pm
b. For each Cofing.pm file, modify all parameters that point to perl with the correct location of %IAS_ORACLE_HOME%\perl. 
For example, In the %IAS_ORACLE_HOME%\perl\5.8.3\bin\Config.pm file, modify archlibexp from '%ORACLE_HOME%\perl\5.8.3\lib\MSWin32-x86-multi-thread to e:\PROD\apps\tech_st\10.1.3\perl\5.8.3\lib\MSWin32-x86-multi-thread.
c. For each Cofing.pm file, modify all parameters that point to Visual C++ with the correct location of Visual C++.  The location of Visual C++ is identified through the msdevdir parameter in the context file at %INST_TOP%\apps\admin\.xml.
For example, in the %IAS_ORACLE_HOME%\perl\5.8.3\lib\MSWin32-x86-multi-thread\Config.pm file, modify libpth to the correct location of Visual C++:libpth=d:\VC8\VC\lib (d:\VC8\VC is an example).

5. Search and download the following Perl modules that are required to be installed manually from CPAN:
For example, use the following steps to install Compress-Raw-Zlib-2.009.tar.gz:
#gzip -d Compress-Raw-Zlib-2.009.tar.gz
tar -xvf Compress-Raw-Zlib-2.009.tar
cd Compress-Raw-Zlib-2.009

On Unix: 
perl Makefile.PL
make
make install

Note: Ignore any warning in make command.

On Windows: 
perl Makefile.PL
nmake
nmake install






Posted by at No comments:
Subscribe to: Posts (Atom)