Oracle Business Intelligence 10.1.3.4.1

Oracle released OBIEE 10.1.3.4.1 & BI Apps 7.9.6 and can be downloaded from OTN

R12.1.1 is available to download

Oracle EBS Release 12.1.1 is available to download from edelivery.

The changes in the 12.1.1 technology stack

1. Database 11gR1 (11.1.0.7)
2. Oracle Application Server 10g Release 2 (OracleAS 10.1.2.3)
3. Oracle Application Server 10g Release 3 (OracleAS 10.1.3.4)

Note: The database tier technology stack for a new Release 12.1.1 installation is Oracle 11g Release 1 (11.1.0.7).

Upgrades from Release 11i with Oracle 10g Release 2 and Oracle 11g Release 1 are both supported

Custom reports exit with APP:FND-362 error

Customized Reports exit with error

====================================
APP-FND-00362: Routine &ROUTINE cannot execute request &REQUEST for program &PROGRAM, because theenvironment variable &BASEPATH is not set for the application to which the concurrent program executable &EXECUTABLE belongs. Shut down the concurrent managers. Set the basepath environment variable for theapplication. Restart the concurrent managers.
====================================

Customized reports registered under the customization Application directory structure were created under APPL_TOP directory and then were copied into it or that the concurrent program definition was incorrect. Rather, the custom top under which this program was registered was not defined correctly. the value of custom application registration within apps (applications -> register) should be the same value as in the shell level.

To implement the solution, execute the following steps:

1. Define customization _TOP directory in your Applications environment file (e.g: CUST_TOP=".../cust/xx.x.x")

2. If step 1 is executed already, login to forms, select system administrator responsibility and navigate to applications -> register. query up the Custom application and change the value of it with the same name as in the shell level.

3. Bounce the concurrent Managers

How to rename the access log file of the Oracle HTTP Server to include the date of the day it is created in?

We can rename the access log file of the Oracle HTTP Server to include the date of the day it is created in. Follow the below step to do it.

- For the 9iAS 1.0.2.x
1- Stop the HTTP Server2- Make a backup of $ORACLE_HOME/Apache/Apache/conf/httpd.conf:3- Open the file httpd.conf and search for something like the following line:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
where ORACLE_HOME=D:\oracle\FRM_REP_904
4 - Modify the httpd.conf file like this example; this will create a new access_log every 24 hours- 86400 seconds:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log.%d%m%Y 86400"common
5 - Save the httpd.conf file6 - Start the HTTPServer again

II - For the 9iAS 9.0.2.x
1- Stop the HTTP Server:$ORACLE_HOME/dcm/bin/dcmctl stop -ct ohs -v -d
2- Make a backup of $ORACLE_HOME/Apache/Apache/conf/httpd.conf:
3- Open the file httpd.conf and search for something like the following line:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
where ORACLE_HOME=D:\oracle\FRM_REP_904
4 - Modify the httpd.conf file like this example; this will create a new access_log every 24 hours- 86400 seconds:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log.%d%m%Y 86400"common
5 - Save the httpd.conf file
6- Apply the changes to the DCM Repository:$ORACLE_HOME/dcm/bin/dcmctl updateConfig -ct ohs -v -d
7- Start the HTTPServer again:$ORACLE_HOME/dcm/bin/dcmctl start -ct ohs -v -d

III For AS 10g , 9.0.4.x - 10.1.x
1- Stop the HTTP Server2- Make a backup of $ORACLE_HOME/Apache/Apache/conf/httpd.conf:
3- Open the file httpd.conf and search for something like the following line:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
where ORACLE_HOME=D:\oracle\FRM_REP_904
4 - Modify the httpd.conf file like this example; this will create a new access_log every 24 hours- 86400 seconds:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log.%d%m%Y 86400"common
5 - Save the httpd.conf file
6- Apply the changes to the DCM Repository:$ORACLE_HOME/dcm/bin/dcmctl updateConfig -ct ohs -v -d
7- Start the HTTPServer again

Note: For the 9.0.2.x - 10.1.x , you can use the EM WebPage to change the httpd.conf file then you will no longer need to run the "dcmctl updateconfig" command.

How to Configure the Account Analysis Report in Release 12 for Large Reports

A typical problem for application user's of these reports is the fact that it is likely to fail when generating the report for very large data sets so the environment needs to be configured properly to handle that.

1. Set the Scalable Option to on for these programs.
--> This prevents the following error in the Subledger Accounting program's log:

Calling XDO Data Engine...
****Warning!!! Due to high volume of data, got out of memory exception...***
****Please retry with scalable option or modify the Data template to run in scalable mode...***
The scalability option is set by performing these steps:
1. As System Administrator: Navigate to Concurrent->Program->Define
2. Query up the report: Account Analysis Report
3. Add a parameter named ScalableFlag:

* Value Set: yes_no
* Default Value: Y
* Select checkboxes Enable and Required
* Do not select the check box Displayed, or users could turn this off at runtime.
* Token needs to be ScalableFlag (this is a case sensitive value).

Complete these steps for both the General Ledger and the Subledger Accounting concurrent program definitions.

2. Configure the XML Publisher Administrator Configuration settings. This prevents "java.lang.OutOfMemoryError" errors in the Output Post Processor log associated to the Subledger Accounting program.

* As XML Publisher Administrator navigate to Administration->Configuration.
* Under Temporary Directory pick a temporary file location on your concurrent processing node. This should be at least 5GB or 20x larger than largest XML data file you generate
* Under FO Processing, set:

o Use XML Publisher's XSLT processor set to True
o Enable scalable feature of XSLT processor set to False
o Enable XSLT runtime optimization set to True

3. Configure the Output Post Processor's JVM. These steps set the JVM to 2GB, depending upon your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error "java.lang.OutOfMemoryError: Java heap space" in the Output Post Processor's log associated to the Subledger Accounting Program.

* Login to SQL*Plus as APPS.
* SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m' where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP'); * Bounce the concurrent managers.

4. Test the reports

Creating the Database link

Navigation for defining database link is Setup > System > Database Link.

To define a database link in General Ledger perform the following steps
on the Target instance (the one you are copying to):

1. Enter the Database Name and optional Description for the linked database.
(You must also supply the Database Name to the FSG Transfer Program).

NOTE: You can find the database name by executing the
following sql query in the source database:

sql> SELECT value FROM v$parameter
WHERE UPPER(name) = 'DB_NAME';

2. Enter the following additional information for the database:

Connect String = The network connect string to point to the
General Ledger database.

Domain Name = The domain of the remote General Leder database
to which this link connects.

NOTE: You can find the domain name by executing the
following query in the source database:

sql> SELECT value FROM v$parameter
WHERE UPPER(name) = 'DB_DOMAIN';

APPS Username = The name of the Oracle Applications account that has
read access to the General Ledger database tables.

NOTE: This is NOT the username you enter to login to Oracle
Applications.

APPS Password = The passwrod for the above username.

NOTE: For security reasons, when you enter the
password, General Ledger will display asterisks
Instead of actual characters in the password.

3. Save your changes before exiting the form.

Useful Queries Related to Concurrent Requests in 11i Applications

Scheduled concurrent requests:

The below query will return all the concurrent requests which are scheduled using any of the above methods:
==========================================================
SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, cr.resubmit_interval, NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ) schedule_type, DECODE (NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ), 'PERIODICALLY', 'EVERY ' cr.resubmit_interval ' ' cr.resubmit_interval_unit_code ' FROM ' cr.resubmit_interval_type_code ' OF PREV RUN', 'ONCE', 'AT :' TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'), 'EVERY: ' fcr.class_info ) schedule, fu.user_name, requested_start_date FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu, apps.fnd_conc_release_classes fcr WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fcr.release_class_id(+) = cr.release_class_id AND fcr.application_id(+) = cr.release_class_app_id;
==========================================================

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week. Positions 1 through 31: Specific day of the month. Position 32: Last day of the month Positions 33 through 39: Sunday through Saturday

Checking the duplicated schedules of the same program with the same arguments:

The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules. Note: This query will return even though the request was submitted using a different responsibility.
==========================================================
SELECT request_id, NAME, argument_text, user_name FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t1 WHERE EXISTS ( SELECT 1 FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t2 WHERE t1.NAME = t2.NAME AND t1.argument_text = t2.argument_text AND t1.user_name = t2.user_name GROUP BY NAME, argument_text, user_name HAVING COUNT (*) > 1) ORDER BY user_name, NAME
==========================================================

Average pending time per request:

This is a very useful query to check the performance of the concurrent managers. Average pending time for a request is calculated like below: ("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc) We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.

==========================================================
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY, concurrent_queue_name, (SUM ( ( actual_start_date - (CASE WHEN requested_start_date > request_date THEN requested_start_date ELSE request_date END ) ) * 24 * 60 * 60 ) ) / COUNT (*) "Wait_Time_per_Req_in_Secs" FROM apps.fnd_concurrent_requests cr, apps.fnd_concurrent_processes fcp, apps.fnd_concurrent_queues fcq WHERE cr.phase_code = 'C' AND cr.actual_start_date IS NOT NULL AND cr.requested_start_date IS NOT NULL AND cr.controlling_manager = fcp.concurrent_process_id AND fcp.queue_application_id = fcq.application_id AND fcp.concurrent_queue_id = fcq.concurrent_queue_id GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name ORDER BY 2
==========================================================

Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.

Checking which manager is going to execute a program:

The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
==========================================================
SELECT user_concurrent_program_name, user_concurrent_queue_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_queue_content cqc, apps.fnd_concurrent_queues_tl cq WHERE cqc.type_application_id(+) = cp.application_id AND cqc.type_id(+) = cp.concurrent_program_id AND cqc.type_code(+) = 'P' AND cqc.include_flag(+) = 'I' AND cp.LANGUAGE = 'US' AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND NVL (cqc.queue_application_id, 0) = cq.application_id AND cq.LANGUAGE = 'US'
==========================================================

To see all the pending / Running requests per each manager wise:

==========================================================
SELECT request_id, phase_code, status_code, user_name, user_concurrent_queue_name FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id AND cq.LANGUAGE = 'US' AND cwr.requested_by = fu.user_id ORDER BY 5
==========================================================
Note: The same information can be seen in Administer Concurrent Manager form for each manager.

Checking the incompatibilities between the programs:

The below query can be used to find all incompatibilities in an application instance.
==========================================================
SELECT a2.application_name, a1.user_concurrent_program_name, DECODE (running_type, 'P', 'Program', 'S', 'Request set', 'UNKNOWN' ) "Type", b2.application_name "Incompatible App", b1.user_concurrent_program_name "Incompatible_Prog", DECODE (to_run_type, 'P', 'Program', 'S', 'Request set', 'UNKNOWN' ) incompatible_type FROM apps.fnd_concurrent_program_serial cps, apps.fnd_concurrent_programs_tl a1, apps.fnd_concurrent_programs_tl b1, apps.fnd_application_tl a2, apps.fnd_application_tl b2 WHERE a1.application_id = cps.running_application_id AND a1.concurrent_program_id = cps.running_concurrent_program_id AND a2.application_id = cps.running_application_id AND b1.application_id = cps.to_run_application_id AND b1.concurrent_program_id = cps.to_run_concurrent_program_id AND b2.application_id = cps.to_run_application_id AND a1.language = 'US' AND a2.language = 'US' AND b1.language = 'US' AND b2.language = 'US'
==========================================================

The table apps.fnd_concurrent_program_serial has the information about incompatibilities.

Cannot Access Oracle Application Forms After Cloning

After cloaning the instance. sometimes users will not be able to launch any forms.

The error.log shows:
[error] mod_plsql: /pls/oracle/fnd_icx_launch.launch HTTP-503 ORA-12538
access_log shows:
"GET /pls/kmapat/fnd_icx_launch.launch?resp_app=SYSADMIN&resp_key=SYSTEM_ADMINISTRATOR&secgrp_key=STANDARD&start_func=FND_FNDCPQCR_SYS&other_params= HTTP/1.1" 503 316

We frequently see this sort of issue where users cannot access forms after a clone.
One of the things about the Cloning Process is that the Logs associated with the Clone Process will not show issues with environment, file or data corruption issues in the Clone Logs. The Logs do not capture any environment type issues that occur after a clone. You may be able to Clone an Instance 1000 times without any issue, but then you may encounter an issue on the 1001st clone,
which will not show up in the Logs because it is an environment, file or data corruption issue.
Solution

1. On the Target Instance which is having the problems after the clone, start comparing the size of the file systems between Target and Source.

du -sk prodappl (source)

du -sk testappl (target)

Do this for all base directories involved.
Then once a difference is found, then zero down to a particular directory and then the corrupted files.

2. If there is no difference between the Source and Target Instance, then
- Copy over the $JAVA_TOP, $ORACLE_HOME/forms and $OA_HTML/bin from the source to the target instance.

- Run AutoConfig or the Clone Steps once again to complete after copying the Files over.

- Retest the issue.

Adrelink: Error: Product Library For Product "IZU" Not Found

When attempting to relink Applications programs using adadmin utility,the following error occurs.

=================================
adrelink: error: Product library for product "izu" not found."izu" might not be listed correctly in your SID_hostname.env file,you may be missing some files for product "izu",or "izu" could be an invalid product.adrelink will not try to link product "izu".Continuing work on other products...
adrelink is exiting with status 1
=================================

Adadmin during the relinking process of product IZU uses the 2 files: izufile.drv and izuRDA.drv. Theissue is caused by those 2 driver files for IZU products which use improper directory names; forsome files there is used "bin" directory instead of "scripts".adadmin is unable to find the following files in their directories when relinking:

$IZU_TOP/bin/rda.cmd
$IZU_TOP/bin/rda.pl
$IZU_TOP/bin/rda.sh

To implement the solution, please execute the following steps:

Note: The issue is resolved by upgrading to file izufile.drv and version 120.4.12000000.7 or higher and file izuRDA.drv and version 120.6.12000000.7 or higher.
1. Ensure that you have taken a backup of your environment,

2. Download and review the readme and pre-requisites for one of the following Patches
whenavailable for download fomr the MetalinkPatch 7294700 R12.IZU.A.DELTA.6 or Patch 6728000 ORACLE E-BUSINESS SUITE 12.0.6 RELEASE UPDATE PACK (RUP6)

3. Apply the patch in a test environment.

4. Confirm the following file versions:file izufile.drv and version 120.4.12000000.7 file izuRDA.drv and version 120.6.12000000.7
You can use the commands like the following:
strings $IZU_TOP/admin/driver/izufile.drv grep '$Header'
strings $IZU_TOP/admin/driver/izuRDA.drv grep '$Header'

The following workaround should help until the Patches are available for download:

1. backup file $IZU_TOP/admin/driver/izufile.drv and apply the following changes:

From:izu bin FNDFlexfieldDiag115.sh
izu bin sdf_core2.txt
To
izu script FNDFlexfieldDiag115.sh
izu script sdf_core2.txt

2. Please backup file $IZU_TOP/admin/driver/izuRDA.drv and apply the following changes:
a)From:izu bin rda.plTo:izu script rda.pl
b)From:izu bin rda.cmd
izu bin rda.sh

To:
izu script rda.cmd
izu script rda.sh

Save changes.

3. Relink all programs using adadmin after stopping all Applications tier services.

Error - Function Not Available To Responsibility

When attempting to create a form in custom module $XX_TOP ,the following error occurs:

"Function is not available for this responsibility.Contact your systemAdministrator or change the responsibility"

The cause for this is that the path for custom module is not defined the file $ORA_CONFIG_HOME/10.1.2/forms/server/default.env . When forms is running in servlet mode in a standard configuration the file $ORA_CONFIG_HOME/10.1.2/forms/server/default.env is read to determine the environment in which forms connnection are ran.
The location of the FMX files is determined by the value set for XX_TOP. If this location is not found the error as seen is raised.

To sovle the issue, perform the following steps:

1.Submit the "Compile Security" Concurrent program with the parameter everything set to Yes to verify that all the menu functions are compiled properly.

2.Create your-own AutoConfig template file

2.1 Define a product_top
Use the Oracle Applications Manager Context Editor to add your custom product_top to the context file. Refer to the Help pages available in Oracle Applications Manager. The relevant information for adding custom context variables is located in the "System Configuration"> "AutoConfig"> "Manage Custom Parameters" section. Choose the variable type "PROD_TOP" when adding the product_top variable. The default value for the product_top variable must be set to 's_at/product name/12.0.0'.
This will add the XX_TOP to the .xml file, so it picked up when the default.env is created by Autoconfig

3.Run AutoConfig

4.Restart apps services

How to Get the Forms,PLL,LDT,Reports,Database Object Versions from the backend

How to get the version of below objects from backend

a) Forms
b) Reports
c) Library files
d) LDT
e) SQL files
f) Packages



a) Form

When considering forms one needs to get the version from $JA_TOP and $AU_TOP as well.

Navigate to cd $JA_TOP/forms/US
Execute command --- > strings -a | grep '$Header'

Example
strings -a JAINPORE.fmx | grep '$Header'

Navigate to cd $AU_TOP/forms/US
Execute command --- > strings -a | grep '$Header'

Example
strings -a JAINPORE.fmb | grep '$Header'


b) Reports

Navigate to cd $JA_TOP/reports/US
Execute command --- > strings -a | grep '$Header'

Example
strings -a JAINARDR.rdf | grep '$Header'

c) Library files

Navigate to cd $AU_TOP/resource
Execute command --- > strings -a | grep '$Header'

Example
strings -a CUSTOM.pll | grep '$Header'

d) LDT

Navigate to cd $JA_TOP/patch/115/import/US
Execute command --- > strings -a | grep '$Header'

Example
strings -a jai115cp_F.ldt | grep '$Header'

e) SQL files

Navigate to cd $JA_TOP/patch/115/sql
Execute command --- > strings -a | grep '$Header'

Example
strings -a ja_in_mass_tax_changes_p.sql | grep '$Header'

f) Packages

Navigate to cd $JA_TOP/patch/115/sql
Execute command --- > strings -a | grep '$Header' (Package Specification)
Execute command --- > strings -a | grep '$Header' (Package Body)

Example
strings -a jai_encum_prc.pls | grep '$Header'
strings -a jai_encum_prc.plb | grep '$Header'

How to change Application port using autoconfig

Open the context file ($INST_TOP/apps/$CONTEXT_NAME/appl/admin/$CONTEXT_NAME.xml) and change the old port to the new port in the following seven context variables. Search for the following variables and replace the old port with the new port between their tags.

- chronosURL oa_var

- EndUserMonitoringURL

- externURL oa_var

- login_page oa_var

- httplistenparameter oa_var

- web_port oa_var

- activewebport oa_var


Now run autoconfig. You can access the Application with the new port number, next time you log in.

How To Get Client IP Address For a Locking User

This code is created using Forms Builder 10.1.2.0.2 and WebUtil Version 1.0.6.

For step (4), user needs execute privilege on DBMS_APPLICATION_INFO

For step (7), you need to connect to the DB using any user has a "Select" privilege on v$session

Create IP-Address Sample form

1. Install and configure your Developer installation to use webutil

http://www.oracle.com/technology/software/products/forms/files/webutil/webutil_106.zip


2. Create new form using webutil


3. Create a WHEN-NEW-FORM-INSTANCE trigger add the following:

======================================================================
declare
v_timer timer;
begin
v_timer := create_timer('ip_timer',2,no_repeat);
end;
======================================================================

4. Create a database procedure

======================================================================
create or replace procedure SETCLIENTINFO (p_info varchar2) is
begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(p_info);
exception
when others then
raise_application_error(-20101,'SETCLIENTINFO: error: '||sqlerrm);
end;
======================================================================


5. Create a WHEN-TIMER-EXPIRED add the following code:

======================================================================
declare
v_trimer_name varchar2(30) := get_application_property(TIMER_NAME);
v_ip_address varchar2(40);
begin
if upper(v_trimer_name) = upper('ip_timer') THEN
v_ip_address := webutil_clientinfo.get_ip_address;
SETCLIENTINFO('Client IP='||v_ip_address);
end if;
end;
======================================================================

6. Call the form


7. Go to SQL*Plus or iSQL*Plus and run the following SQL statement:

======================================================================
select client_info from v$session where client_info is not null;
======================================================================

Sample Code Output


The SQL output in step (7) should be like :


Client IP = x.x.x.x

Most Common Reason for ORA-3136 WARNING : Inbound Connection Timed Out

The most common reason for warning message in alert log is when an user gets error at connect time, ORA-01017 invalid username /password , and on the same prompt, if no update is made for 1 minute(default) then the alert loggets WARNING: inbound connection timed out (ORA-3136)

To reproduce the issue try the following steps:

Place the below parameter in the sqlnet.ora of the server (this is the default value of the parameter)

=======================================================
SQLNET.INBOUND_CONNECT_TIMEOUT=60
=======================================================


Make the tnsnames entry on the server with dedicated as server like
=======================================================
orcl10203=
(DESCRIPTION=
(ADDRESS=(HOST=testnet.idc.oracle.com)(PORT=1521)(PROTOCOL=TCP))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=orcl10203)
)
)
=======================================================


Now open a sqlplus session and enter wrong username or password:
=======================================================
[oracle@testnet admin]$ sqlplus system/jkkdsf@orcl10203

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Mar 19 17:51:35 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied
=======================================================


Keep the sqlplus session open for 60 seconds and in the alert log after 60th second , you will notice ORA-3136
=======================================================
$tail -f alert_orcl10203.log
Thu Mar 19 17:52:36 2009
WARNING: inbound connection timed out (ORA-3136)
=======================================================


In the server sqlnet tracing you would see following :
=======================================================
[19-MAR-2009 17:52:36:865] ntt2err: entry
[19-MAR-2009 17:52:36:865] ntt2err: soc 1660 error - operation=5, ntresnt[0]=522, ntresnt[1]=4, ntresnt[2]=0
[19-MAR-2009 17:52:36:865] ntt2err: exit
[19-MAR-2009 17:52:36:865] ntt2err: entry
[19-MAR-2009 17:52:36:865] ntt2err: soc 1660 error - operation=5, ntresnt[0]=530, ntresnt[1]=38, ntresnt[2]=0
[19-MAR-2009 17:52:36:865] ntt2err: exit
=======================================================

SQL script to find requests running for more than 1 hour

Use the following script to find out requests that are running (currently) for more than one hour.

====================================================
-- --------------------------------------------------
-- Author : New Generation Consultants
-- Script to find long running requests
-- --------------------------------------------------
set lines 300
set trims on
set pages 60
set head on

ttit off
btit off
alter session set nls_Date_format='DD-MON-RRRR HH24:MI';
col acd format a23 Heading "End Time"
col cpn format a15 heading "Program"
col ucpn format a35 heading "Program Name"
col runtime format 9999999 heading "Runtime"
col asd format a23 Heading "Start Time"
SELECT f.request_id
, DECODE(p.concurrent_program_name,
'ALECDC',p.concurrent_program_name'['f.description']'
,p.concurrent_program_name) cpn
, pt.user_concurrent_program_name ucpn
, TRUNC(((sysdate-f.actual_start_date)/(1/24))*60) runtime
, f.actual_start_date asd
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.phase_code = 'R'
and (sysdate-f.actual_start_date)*24*60 > 60
and f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
ORDER by f.actual_start_date desc;
====================================================

SQL suery to find out all errored requests

Use this SQL script to find out all the requests that ended in ERROR, TERMINATED or WARNING state.

===================================================
-- --------------------------------------------------
-- Author : New Generation Consultants
-- Script to find erroring requests
-- --------------------------------------------------
clear columns
set pages 23
set lines 500
col os form A6
col program head "Program Name" form A45 trunc
col shrtnm head "Shortname" format a13
col time head Elapsed form 9999.99
col request_id head "Req Id" form 9999999
col cprogid head "Prg Id" form 999999
col "Started On" format a10
col "Finished On" format a11
col "Submitted By" format a35 trunc
col argument_text head "Arguments" format a50 trunc
col statustxt head Status format a12 trunc
col phasetxt head Phase format a10 trunc
set recsep wrap
set verify off
select a.request_id
,c.concurrent_program_name shrtnm
,c.concurrent_program_id cprogid
,ctl.user_concurrent_program_name "program"
,l2.meaning phasetxt
,l1.meaning statustxt
--,to_char(a.actual_start_date,'hh:mi:ssAM') "Started On"
--,to_char(a.actual_completion_date,'hh:mi:ssAM') "Finished On"
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
--,a.argument_text
,u.user_name ' - ' u.description "Submitted By"
from fnd_Concurrent_requests a,fnd_concurrent_programs c,fnd_concurrent_programs_tl ctl
,fnd_user u,
(select l1.lookup_code,l1.meaning from fnd_lookup_values l1
where l1.lookup_type = 'CP_STATUS_CODE'
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and (l1.start_date_active <= sysdate AND l1.start_date_active IS NOT NULL)
and (l1.end_date_active > sysdate or l1.end_date_active is null
and l1.meaning in ('Terminated','Warning','Cancelled','Error'))) l1,
(select l2.lookup_code,l2.meaning from fnd_lookup_values l2
where l2.lookup_type = 'CP_PHASE_CODE'
and l2.language = 'US'
and l2.enabled_flag = 'Y'
and (l2.start_date_active <= sysdate AND l2.start_date_active IS NOT NULL)
and (l2.end_date_active > sysdate or l2.end_date_active is null)
and l2.meaning = 'Completed')l2
where trunc(a.actual_start_date) = trunc(sysdate) and a.program_application_id = c.application_id
and a.concurrent_program_id = c.concurrent_program_id and ctl.application_id = c.application_id
and ctl.concurrent_program_id = c.concurrent_program_id
and l1.lookup_code = a.status_code
and l2.lookup_code = a.phase_code
and u.user_id = a.requested_by
order by actual_start_date
===================================================

RMAN: SET NEWNAME Command Using SQL

When you must restore the database the same directory structure is not always there. If you have a database containing 1000s of datafiles it can be very tedious to setup the set newname commands for all the datafiles. Using sqlplus we can extract the information we need into a file which can then be easily modifed and executed as an RMAN script to complete the task.

===================================================
sqlplus /nolog
connect system/manager
set echo off pages 0 feed off sqlp #
spool /path/setnewnamedf.lst
select 'set newname for datafile 'file#' to NEW;' from v$datafile;
-- select 'set newname for datafile 'file#' to /newpath/NEW;' from v$datafile;
spool off
===================================================
There are 2 select statements above with slightly different output.

Select #1 Output:

set newname for datafile 1 to NEW;
set newname for datafile 2 to NEW;
set newname for datafile 3 to NEW;
set newname for datafile 4 to NEW;
set newname for datafile 5 to NEW;

Select #2 Output:

set newname for datafile 1 to /newpath/NEW;
set newname for datafile 2 to /newpath/NEW;
set newname for datafile 3 to /newpath/NEW;
set newname for datafile 4 to /newpath/NEW;
set newname for datafile 5 to /newpath/NEW;

To generate set newname commands to point to an ASM volume execute the sql below
===================================================
sqlplus /nolog
connect system/manager
set echo off pages 0 feed off sqlp #
spool /path/setnewnamedf.lst
select 'set newname for datafile 'file#' to ''+DG'';' from v$datafile;
spool off
===================================================

Oracle EBS on Windows halts every now and then without an issue

The instance can be started and login successful. But it crashes with in few minutes.
The issue has been resolved after done the following steps,

1) Enabled the TCP port to be allowed by the firewall using the exception tab on the firewall or else Concurrent manager will not be started and also will not be able to ping to the server.

2) Added following entry in the http.conf after the install (430386.1)
MaxRequestsPerChild 0
MaxClients 150
ThreadsPerChild 20

3) Increaded the Paging file size to double the size of the RAM

4) For Oracle forms launch with FRM-92101/500 error, recompiled the forms using adadmin (Ref Note # 427714.1)

Notifications are not Received and Log file Shows Connection Refused Error

The cause for this error ios that the url for the web server was changed so that the events in the queue still have the old url:

Example:

{https://:8001/OA_HTML/OA.jsp?page=/oracle/apps/fnd/wf/worklist/webui/...

The web server name in the log file is not the same as the web server name in the WF_MAIL_WEB_AGENT profile option.

Since the mailer still had some events with the old url in the queue, the mailer fails with
connection refused error and goes down after 10 attempts.

To implement the solution, please execute the following steps:

1. Stop the Workflow mailer

2. Recreate the wf_notification_out queue and recreate it out of the wf_notifications table by
executing:

cd $FND_TOP/patch/115/sql
sqlplus APPS/ @wfntfqup.sql APPS APPLSYS

3. Start the mailer

4. Retest the issue by submitting the same workflow again.

5. Migrate the solution as appropriate to other environments.

How to start the Output Post Processor (OPP)?

To activate the OPP follow these setps

The Profile Option "Concurrent: GSM Enabled" must be set to Y

Then:

1. Login to Apps with sysadmin responsibility
2. Navigate to: Concurrent -> Managers -> Define
3. Query for
Manager = 'Output Post Processor'
or Short Name = FNDCPOPP
4. Check the checkbox "Enable" .
5. Click on 'Work Shifts button
6. see Work Shift of the OPP and
Set
Processes = 1
and
Parameters = oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
and
Sleep Second = 30
7. Save
8. Navigate to:_ Concurrent -> Managers -> Administer
9. Make sure the Output Post Processor Status is not 'Deactivate'
Set Status to 'Activate' if necessary.

10. Stop and Start the Concurrent Managers using
adcmctl.sh

custom schema name always starts with XX

Have you ever wondered why custom schema names always start with ‘XX’. Before getting into the detail lets see what happens if the custom schema name doesnt start with ‘XX’.Impact: If you dont have a custom schema name starting with ‘XX’, be assured that this schema will be no longer available in your instance when you perform an UPGRADE. During upgrade Oracle apps will wipe out all unregistered schemas which doesnt start with ‘XX’.Oracle had assured its customers that it will not touch any schemas that starts with ‘XX’. This is the reason why you find custom schema names with ‘XX’ prefixed.

Impact: If you dont have a custom schema name starting with ‘XX’, be assured that this schema will be no longer available in your instance when you perform an UPGRADE. During upgrade Oracle apps will wipe out all unregistered schemas which doesnt start with ‘XX’.

Oracle had assured its customers that it will not touch any schemas that starts with ‘XX’. This is the reason why you find custom schema names with ‘XX’ prefixed.