31 March 2011

How to change the oracle All APPL_TOP password using FNDCPASS

Hi,
Please execute the below query copy the output. make the shell script then run your server .

select concat('FNDCPASS apps/apps 0 Y system/manager ORACLE ' || replace(ORACLE_USERNAME,'$','\$' ) || ' ',  concat(substr('FNDCPASS apps/apps 0 Y system/manager ORACLE ' || replace(ORACLE_USERNAME,  '$','\$'),46),'_OUR12')) muthu
from APPLSYS.FND_ORACLE_USERID
where READ_ONLY_FLAG = 'A'
and ORACLE_USERNAME in (select USERNAME from DBA_USERS);

28 March 2011

Forbidden You don't have permission to access in apex

Hi,
recently I have installed the apex after installation am facing below error .

Forbidden You don't have permission to access

Log file detail :-
[client 10.1.0.207] [ecid: 1301317763:10.2.0.189:7364:0:4,0] mod_plsql: /pls/ourprod/apex HTTP-403 ORA-1017 ORA-01017: invalid username/password; logon denied\n

my dads.conf detail :-
<Location /pls/ourprod>
    Order deny,allow
    PlsqlDocumentPath docs
    AllowOverride None
    PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
    PlsqlDatabaseConnectString 10.2.0.183:1523:OURPROD SIDFormat
    PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
    PlsqlAuthenticationMode Basic
    SetHandler pls_handler
    PlsqlDocumentTablename wwv_flow_file_objects$
    PlsqlDatabaseUsername APEX_PUBLIC_USER
    PlsqlDefaultPage apex
    PlsqlDatabasePassword welcome
    PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
    Allow from all
</Location>
Solution:-
SQL >ALTER USER APEX_PUBLIC_USER  IDENTIFIED BY welcome;

Then my problem is resolved ....

25 March 2011

mod_oc4j: Failed to find a failover oc4j process for session request for destination oc4j_socket_recvfull timed out

mod_oc4j: Failed to find a failover oc4j process for session request for destination

oc4j_socket_recvfull timed out

Have encountered an issues with oacore crashing in R12 Oracle Applications frequently:

Error:-

[Mon Mar 28 16:45:06 2011] [warn] [client 10.1.2.165] oc4j_socket_recvfull timed out
[Mon Mar 28 16:45:06 2011] [error] [client 10.1.2.165] [ecid: 1301310604:10.2.0.181:18046:0:3,0] mod_oc4j: request to OC4J our12-apps2.bilt.com:21510 failed: Connect failed
[Mon Mar 28 16:45:28 2011] [warn] [client 10.1.2.51] oc4j_socket_recvfull timed out
[Mon Mar 28 16:45:28 2011] [error] [client 10.1.2.51] [ecid: 1301310626:10.2.0.181:18104:0:1,0] mod_oc4j: request to OC4J our12-apps1.bilt.com:21510 failed: Connect failed
[Mon Mar 28 16:45:56 2011] [warn] [client 10.1.2.165] oc4j_socket_recvfull timed out
[Mon Mar 28 16:45:56 2011] [error] [client 10.1.2.165] [ecid: 1301310654:10.2.0.181:17174:0:8,0] mod_oc4j: request to OC4J our12-apps2.bilt.com:21510 failed: Connect failed
[Mon Mar 28 16:45:58 2011] [warn] [client 10.1.2.165] oc4j_socket_recvfull timed out
[Mon Mar 28 16:45:58 2011] [error] [client 10.1.2.165] [ecid: 1301310656:10.2.0.181:27684:0:1200,0] mod_oc4j: request to OC4J our12-apps2.bilt.com:21510 failed: Connect failed
Desc:
-------

The error 'mod_oc4j Failed to find a failover oc4j process for session request for destination' indicates that a session request was received by the Process Manger (PM). The PM was unable to find a java process to route the session to which would be defined as a "island" or "jgroup" and it failed.

The error indicates for some reason the OC4J process has become non responsive to the point where the PM has determined it failed or is no longer available. This error usually occurs when OHS (mod_oc4j) is not able to communicate ( over AJP ) with OC4J.

There are multiple reasons why this error may have happened: Some common reasons are

1. Load: e.g. Too many clients connecting to the application :
 - The number of requests are high enough that the process cannot respond to new requests prior to reaching the timeout period.

2. Performance: Heavily loaded JVM or Lack of enough memory for JVM
  - The OC4J oacore JVM processes in the middle tier are heavily loaded
  - High number of jdbc connections with database there by increasing the number of threads on the java process.


Solution :-

a) The number of jvms (oc4j in R12) is configured by the autoconfig variables s_oacore_nprocs, s_disco_nprocs, s_forms_servlet_nprocs and s_xmlsvcs_nprocs.

b) In 11i, those changes are made in the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf file, while in R12 in the $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml

Heap Configuration:

1) For OACore, start with the following configuration:

For R12.0 and 11i:

       -Xmx512M -Xms256M -XX:MaxPermSize=128M
       -XX:NewRatio=2 -XX:+PrintGCTimeStamps

For R12.1 and higher:

      -Xmx1024M -Xms512M -XX:MaxPermSize=256M
      -XX:NewRatio=2 -XX:+PrintGCTimeStamps

     Also, ensure you add the following parameter to the DBC file:

     JDBC\:oracle.jdbc.maxCachedBufferSize=262144

   

Adjust the heap sizes accordingly to minimize garbage collection frequency and user pause times due to full garbage collections.
In JDK 1.6, the JVM detects that you have a server class machine (2 or more CPUs with 2GB or more memory), and will automatically enable Parallel Throughput Garbage Collector. The number of GC threads defaults to the number of CPUs on the machine. If you are running multiple JVMs on the same machine, or if your machine has more than 2 CPUs, to avoid the GC threads to be overly active you should reduce the number of GC threads by using:

      -XX:+UseParallelGC -XX:ParallelGCThreads=2

In most cases, using 2 GC threads should be sufficient. If you are using heap size of over 1GB, you can experiment with 4 GC threads and see if it gives you better performance.


2) If using Forms in Servlet mode, the following configuration for FormsGroup should be enough for most of the cases:

      -Xmx256M -Xms128M -XX:MaxPermSize=128M -XX:NewRatio=2

For More Information Please Ref:Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12 [ID 362851.1]

24 March 2011

ORA-02082: a loopback database link must have a connection qualifier

SQL> CREATE DATABASE LINK "muthu"
 CONNECT TO APPS
 IDENTIFIED BY <PWD>
 USING '10.2.0.189:1521/orcl';
CREATE DATABASE LINK "muthu"
 CONNECT TO APPS
 IDENTIFIED BY <PWD>
 USING '10.2.0.189:1521/orcl'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

Solutin :-

a loopback database link needs a trailing qualifier, e.g.
  muthu.kirthi.com - the 'kirthi.com' is the qualifier

 CREATE DATABASE LINK "muthu.kirthi.com"
 CONNECT TO APPS
 IDENTIFIED BY <PWD>
 USING '10.2.0.189:1521/orcl';

23 March 2011

WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136)

When you see the error in the alert.log file


NOTE: It is normal warnings message , can be ignored.
Solution: 
set the following parameter in the sqlnet.ora
Both parameter will be set in the 'DB SERVER' side.
In the sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
In the listener.ora
INBOUND_CONNECT_TIMEOUT_LISTENER = 120

Also refer below Metalink note : 465043.1

 

15 March 2011

ORA-16038: log 2 sequence# 2051 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 2 thread 1

[oracle@our12-appload ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 14 12:46:06 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to an idle instance.

SQL>startup
ORACLE instance started.

Total System Global Area 1573527552 bytes
Fixed Size                  1300156 bytes
Variable Size             838863172 bytes
Database Buffers          721420288 bytes
Redo Buffers               11943936 bytes
Database mounted.
ORA-16038: log 2 sequence# 2051 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/oracle/db/10.2.0/oradata/
orcl/redo02.log'
--------------------------------------------------
 [oracle@our12-appload dbs]$ vi initorcl.ora
orcl.__db_cache_size=788529152
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=1308622848
orcl.__streams_pool_size=0
*.audit_file_dest='/oracle/db/10.2.0/dbhome_1/admin/orcl/adump'
*.background_dump_dest='/oracle/db/10.2.0/dbhome_1/admin/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oradata/orcl/control01.ctl','/oradata/orcl/control02.ctl','/oradata/orcl/control03.ctl'
*.core_dump_dest='/oracle/db/10.2.0/dbhome_1/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/oracle/db/10.2.0/dbhome_1/flash_recovery_area'
*.db_recovery_file_dest_size=1024967296
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/appload/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=60000
*.pga_aggregate_target=1073741824
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=2147483648

Solution :-
 I will changed the value

*.db_recovery_file_dest_size=4294967296


another method..
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4294967296 SCOPE=BOTH;

SQL> shutdown abort
SQL> startup

07 March 2011

How to create the FND_USER using pl/sql script

Create the FND_USER using pl/sql script 


/* Formatted on 25-Mar-11 01:20:26 PM (QP5 v5.163.1008.3004) */
DECLARE
   P_HIRE_DATE         DATE;
   P_USER_NAME         VARCHAR2 (32767);
   P_PASSWORD          VARCHAR2 (32767);
   P_USER_START_DATE   DATE;
   P_USER_END_DATE     DATE;
   P_EMAIL_ADDRESS     VARCHAR2 (32767);
   P_FAX               VARCHAR2 (32767);
   P_DESCRIPTION       VARCHAR2 (32767);
   P_PASSWORD_DATE     DATE;
   P_LANGUAGE          VARCHAR2 (32767);
   P_HOST_PORT         VARCHAR2 (32767);
   P_EMPLOYEE_ID       VARCHAR2 (32767);
   P_CUSTOMER_ID       VARCHAR2 (32767);
   P_SUPPLIER_ID       VARCHAR2 (32767);
   P_USER_ID           NUMBER;
BEGIN
   P_HIRE_DATE := NULL;
   P_USER_NAME := 'muthu';
   P_PASSWORD := 'welcome1';
   P_USER_START_DATE := SYSDATE;
   P_USER_END_DATE := NULL;
   P_EMAIL_ADDRESS := NULL;
   P_FAX := NULL;
   P_DESCRIPTION := NULL;
   P_PASSWORD_DATE := NULL;
   P_LANGUAGE := 'AMERICAN';
   P_HOST_PORT := NULL;
   P_EMPLOYEE_ID := NULL;
   P_CUSTOMER_ID := NULL;
   P_SUPPLIER_ID := NULL;
   P_USER_ID := NULL;
   DBMS_OUTPUT.put_line ('entry');
   APPS.HR_USER_ACCT_INTERNAL.CREATE_FND_USER (P_HIRE_DATE,
                                               P_USER_NAME,
                                               P_PASSWORD,
                                               P_USER_START_DATE,
                                               P_USER_END_DATE,
                                               P_EMAIL_ADDRESS,
                                               P_FAX,
                                               P_DESCRIPTION,
                                               P_PASSWORD_DATE,
                                               P_LANGUAGE,
                                               P_HOST_PORT,
                                               P_EMPLOYEE_ID,
                                               P_CUSTOMER_ID,
                                               P_SUPPLIER_ID,
                                               P_USER_ID);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('error occured in user creation' || SQLERRM);
      ROLLBACK;
END;

THEN
UPDATE fnd_user
SET password_lifespan_days = 45
WHERE user_id = 'MUTHU';

01 March 2011

How to Find the File version in Oracle Apps

1, How to find Version of Apache used with oracle apps R12 ? Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command
./httpd -version
You will see output like
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19
Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server
Above is output If you have installed 10g Application Server with 11i 

 2, How to find Jinitiator Version ?
Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin defined by environment variable FORMS60_WEB_CONFIG_FILE & search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ; if your version is 1.3.1.18 you will see entry like 1,3,1,18
3, How to find Forms Version in 11i ?
Login to forms from frontend , on top menu bar of forms click on "Help" & Select "About Oracle Applications" go to "Forms Server " section. You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 . If you want to know whats your forms patchset level then subtract 9 from fourth digit which means for above case form patchset 17 is applied.

4, How to find Forms Version in Apps from command Line ?
Enter "f60gen" on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit - 9)
5, How to find any File Version R12 ?
In Oracle Applications under ad utilities there is utility called as adident Used for Identification purpose or to find out file version use
adident Header <filename>
 example  find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp. Similarly you can use adident to find version of any report in 11i.

6, How to find the Oracle Database version in 11i and R12?

Source the env file type sqlplus <username>/<Password>    press enter
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 1 17:42:32 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>