From time to time, some concurrent manger hangs and it can not be fixed by using adcmctl.sh to re-start ICM. The reason is that the OS process for the concurrent manager may be "dead" and you have to kill it at OS level before the concurrent manager can be re-started.
Steps for finding concurrent manager, such as Output Post Processor, stuck in 'Initializing' or other phase:
. Log on to EBS Forms or to Oracle Application Manager.
. Find the concurrent manager in question.
. Click on "Process" button or Drill down to find the processes shown as active or initializing.
. If the operating system process ID shown is running on the concurrent processing node, kill that process.
. Activate the concurrent manager.
. Verify that the concurrent manager comes up and the number of active managers is equal to the target number.
Saturday, April 26, 2008
Friday, April 11, 2008
View other's Output files and Log files within a Responsibility
By default, user can not view the Output of concurrent program/request submitted by others. This setup will allow all users of a responsibility to view each others log and report outputs.
. Login to Apps as SYSADMIN
. Navigate to SysAdmin > Profile > System.
. From the "Find System Profile Values" window query up the Profile named "Concurrent: Report Access Level"
. In the "System Profile Values" window find "Concurrent: Report Access Level" profile.
. Set the Site Column LOV to "Responsibility" (be careful not to set the Site Column to “User” because that will completely open the system up where all requests will be available to all users regardless of level)
. Set the "Concurrent: Report Access Level" profile to "Responsibility" (Setting this value to “User” here exhibits the behavior where a user is restricted from viewing other users’ concurrent requests).
The important profile value to set is "Concurrent: Report Access Level", and also the setting is at the responsibility level, instead of system level.
UPDATE: For R12, Profile option "Concurrent: Report Access Level" does not exist and it is more difficult to see other's Output file. Check Oracle document IDs 804296.1 and 2112509.1
. Login to Apps as SYSADMIN
. Navigate to SysAdmin > Profile > System.
. From the "Find System Profile Values" window query up the Profile named "Concurrent: Report Access Level"
. In the "System Profile Values" window find "Concurrent: Report Access Level" profile.
. Set the Site Column LOV to "Responsibility" (be careful not to set the Site Column to “User” because that will completely open the system up where all requests will be available to all users regardless of level)
. Set the "Concurrent: Report Access Level" profile to "Responsibility" (Setting this value to “User” here exhibits the behavior where a user is restricted from viewing other users’ concurrent requests).
The important profile value to set is "Concurrent: Report Access Level", and also the setting is at the responsibility level, instead of system level.
UPDATE: For R12, Profile option "Concurrent: Report Access Level" does not exist and it is more difficult to see other's Output file. Check Oracle document IDs 804296.1 and 2112509.1
Sunday, April 6, 2008
Directory defined by $APPLPTMP & $APPLTMP
Oracle Doc ID 1355735.1 (Difference between APPLPTMP and APPLTMP Directories in EBS) tells the difference between APPLPTMP and APPLTMP Directories in EBS. Here is more info on troubleshooting.
Instead, the directory has to exist on the database server. Otherwise, below error will show up in CM program log. I also saw similar error when the folder has too many files.
ORACLE error 20100 in FDPSTP
Cause: FDPSTP failed due to ORA-20100: File o0275459.tmp creation for FND_FILE filed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 410
ORA-06512
And, make sure the directory is part of the database parameter utl_file_dir:
SQL> select name, value from v$parameter where name = 'utl_file_dir';
To test its setup, run
SQL> exec FND_FILE.PUT_LINE(fnd_file.log, 'Hello World!');
A new file with a name like l000xxxx.tmp is created in the first directory in the db parameter utl_file_dir, containing 'Hello World!'.
UPDATE: *warning* In 12c database, after above statement, it needs to run CLOSE line. Otherwise the log file size may be 0!
SQL> exec fnd_file.close;
Below line will work but will not create a file o000xxxx.tmp and write one line to it until CLOSE line was run.
SQL> exec FND_FILE.PUT_LINE(fnd_file.output, 'Hello output!');
- Variable $APPLTMP is the location to save temporary files from forms run-time on the forms/web server. A cron job can be scheduled to delete old files in that directory.
One day, server experienced files filling up $APPLTMP quickly with files in 2GB size:
-rw-r----- 1 user1 users 2147483647 Feb 4 07:12 OFN6pYCK.t
-rw-r----- 1 user1 users 2147483647 Feb 4 07:16 OFNF0HJf.t
-rw-r----- 1 user1 users 2007334912 Feb 4 07:39 OFVuhSzG.t
The file holds entries of hours earlier than the file's timestamp:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Subledger Accounting: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XLAACCUP module: Accounting Program
+---------------------------------------------------------------------------+
Current system time is 04-FEB-20XX 01:02:47
... ... ...
I found debug log was turned on by "FND: Debug Log Enabled =Yes" on site level. So, make sure below settings are off in Profile options:
FND: Debug Log Enabled = No
SLA: Enable Diagnostics = No
AR: Enable Debug Message Output = No
- Variable s_forms_tmpdir in CONTEXT_FILE also defines a location holding forms temp files.
On a busy day, Sys Admin saw /tmp was almost full by 7 GB file /tmp/file75jBt9.TMP, while EBS R12 variable s_forms_tmpdir sets to /tmp directory. Even this file was marked "deleted", the space was not released as long as a frmweb / forms session had it open by a user who was extracting a large portion of year-end data.
SQL> exec fnd_file.close;
One day, server experienced files filling up $APPLTMP quickly with files in 2GB size:
-rw-r----- 1 user1 users 2147483647 Feb 4 07:12 OFN6pYCK.t
-rw-r----- 1 user1 users 2147483647 Feb 4 07:16 OFNF0HJf.t
-rw-r----- 1 user1 users 2007334912 Feb 4 07:39 OFVuhSzG.t
The file holds entries of hours earlier than the file's timestamp:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Subledger Accounting: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XLAACCUP module: Accounting Program
+---------------------------------------------------------------------------+
Current system time is 04-FEB-20XX 01:02:47
... ... ...
I found debug log was turned on by "FND: Debug Log Enabled =Yes" on site level. So, make sure below settings are off in Profile options:
FND: Debug Log Enabled = No
SLA: Enable Diagnostics = No
AR: Enable Debug Message Output = No
- Variable s_forms_tmpdir in CONTEXT_FILE also defines a location holding forms temp files.
On a busy day, Sys Admin saw /tmp was almost full by 7 GB file /tmp/file75jBt9.TMP, while EBS R12 variable s_forms_tmpdir sets to /tmp directory. Even this file was marked "deleted", the space was not released as long as a frmweb / forms session had it open by a user who was extracting a large portion of year-end data.
UPDATE in Sept 2022:
Starting in Oracle Database 18c, parameter UTL_FILE_DIR is no longer supported. Instead, specify the name of a directory object.
Since the UTL_FILE_DIR initialization parameter is deprecated, it is no longer listed in V$SYSTEM_PARAMETER and related views:
SQL> SELECT value FROM v$system_parameter WHERE name='utl_file_dir' ;
no rows selected
SQL> SELECT value FROM v$parameter WHERE name='utl_file_dir' ;
VALUE
--------------------------------------------------------------------------------
/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV
And, it can not be altered at DB level:
SQL> alter system set utl_file_dir='/path/to/utl_dir';
Wednesday, April 2, 2008
Log files from various programs in 11i
Log files are most important in helping troubleshooting. Various parograms in 11i put log files in different locations.
CONTENTS (See Metalink Note 130183.1)
--------
1. Internal manager
2. Concurrent managers
3. Concurrent programs
4. AD, FND and other utilities
5. User exits
6. Forms server
7. SQL*NET, NET8 listener
8. Apache, Jserv
9. SSP5 (iProcurement)
10. PO Document Approval Manager
11. XML Publisher (Output Post Processor)
1. INTERNAL MANAGER (also called ICM)
-------------------
a. when the ICM is started by adcmctl.sh, a log file is created in
$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG
File format is: $<SID>_mmdd.mgr
(or <mgrname>.mgr. mgrname is figured in startmgr, and is 'std' by default. startmgr is a shell script under $FND_TOP/bin/) (version depend?)
- To debug the ICM startup, set the value of DIAG_PARAM to Y in adcmctl.sh.
- The ICM log file on startup can be also located by SQL:
SELECT 'LOG=' fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'AND fcp.process_status_code = 'A';
b. directly from Oracle Applications :
System Administrator responsability
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Internal Manager Log
2. CONCURRENT MANAGERS (See note 105133.1)
----------------------
a. retrieve Manager log files under :
$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG
File format is:
w<Concurrent Process Id>.mgr (log from one of Concurrent managers)
c<Concurrent Process Id>.mgr (Conflict Resolution manager log)
t<Concurrent Process Id>.mgr (Transaction manager log)
s<Concurrent Process Id>.mgr
FNDxxxxx.mgr
If you see errors on cartridge in the logfile, below query will help to identify the queue:
SELECT q.concurrent_queue_id, q.concurrent_queue_name
FROM apps.fnd_concurrent_queues q, apps.fnd_cp_services s
WHERE enabled_flag = 'Y'
AND q.manager_type = s.service_id
AND s.cartridge_handle = 'CMDCART'; -- for example
b. directly from Oracle Applications :
System Administrator responsability
Navigator: Concurrent => Requests, choose a request ...
menu: Special => Manager Log
or
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Manager Log
3. CONCURRENT PROGRAMS
----------------------
a. retrieve Concurrent Program log files under :
$APPLCSF/$APPLLOG (if $APPLCSF and $APPLLOG exist) or
$<module_top>/$APPLLOG
File format is: l<Concurrent Request Id>.req
- If the concurrent program generates a output file, it goes to $APPLCSF/$APPLOUT.
- SQL can be used to get the exact file names for Log file and Output file:
SELECT logfile_name, outfile_name, outfile_node_name, last_update_date
FROM apps.FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = &requestID;
- There are also log files for concurrent requests under $COMMON_TOP/admin/log/$SID_$hostname. (Will they be useful only when you have Forms auditing on?)
b. directly from Oracle Applications :
System Administrator responsability (for own and other users requests) :
Navigator: Concurrent => Requests, button View Log...
Note: User may not be able to view other's Output (and Log) files.
4. AD, FND AND OTHER UTILITIES
-------------------------------
Some AD utilities like adpatch, adadmin, etc. generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file names are :
adunload.log
adrelink.log
adlibout.log
adlibin.log
adfrmgen.log
admrgpch.log
adrepgen.log
adctrl.log
admvcode.log
adaimgr.log
adwork01.log, adwork02.log, adwork03.log, ...
Utility adodfcmp generates log file under $<module_top>/admin/odf/ or $<module_top>/patch/<version>/odf/, file name is:
adodfcmp.log
FND utilities like fdfcmp, FNDFFVGN, FNDMDGEN generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file name is :
L<request_id>.req
5. USER EXITS (See note 292058.1)
--------------
User exits from forms generate log file under directory from where the forms server is started if variable FDUDEBUG is set to ON. File name is:
userexit.log
6. FORMS SERVER (See note 471921.1)
----------------
When starting forms server you can specify a log file name.
Exemples:
Windows: f60srv32 log=c:\temp\f60srv.log
Unix: f60ctl start port=9000 log=/temp/f60srv.log
(f60ctl is a script located in $ORACLE_HOME/bin)
Note: $COMMON_TOP/admin/log/$SID_$hostname/f60svrm.txt has very basic info from executing adfrmctl.sh
7. SQL*NET, NET8 LISTENER
--------------------------
You can specify LOG_DIRECTORY_<LISTENER_NAME> and LOG_FILE_<LISTENER_NAME> specific values for theses parameters in listener.ora.
You can set LOG_FILE and TRACE_FILE variables from a lsnrctl session.
There are also LOG_DIRECTORY_SERVER and LOG_FILE_SERVER parameters in sqlnet.ora.
8. APACHE, JSERV
----------------
Logs from Apache server are under $APACHE_TOP/logs, file names are:
error_log
access_log
httpds_access_log
httpds_error_log
sqlnet.log
Logs from Jserv are under $ORACLE_HOME/Apache/Jserv/logs (?), file names are:
jserv.log
debug.log
Note: it is recommended to clean up these log files, shutdown and restart Apache server before trying to reproduce the issue and analyze.
$APACHE_TOP is not defined in any $APPL_TOP/*.env file ("grep APACHE_TOP $APPL_TOP/*.env" gets nothing).
9. SSP5 (iPROCUREMENT)
---------------------
Apache and Jserv logs are useful to check for iProcurement (SSP5), you can also add these lines in ssp_init.txt (should be under $ORACLE_HOME/Apache/Jserv/etc) to generate specific SSP5 log file:
DebugOutput=<Directory/File name> (preferably under $APACHE_TOP/logs)
DebugLevel=5
DebugSwitch=ON
Note: don't forget to restart Apache server after editing ssp_init.txt.
10. PO Document Approval Manager
------------------------------------
Responsibility - System Administrator responsibility (Nav - Concurrent Manager - Administer).
Locate and find the PO Document Approval Manager and then click on Processes button and then select an Active concurrent manager process and click on the Manager Log button to display the log file.
CONTENTS (See Metalink Note 130183.1)
--------
1. Internal manager
2. Concurrent managers
3. Concurrent programs
4. AD, FND and other utilities
5. User exits
6. Forms server
7. SQL*NET, NET8 listener
8. Apache, Jserv
9. SSP5 (iProcurement)
10. PO Document Approval Manager
11. XML Publisher (Output Post Processor)
1. INTERNAL MANAGER (also called ICM)
-------------------
a. when the ICM is started by adcmctl.sh, a log file is created in
$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG
File format is: $<SID>_mmdd.mgr
(or <mgrname>.mgr. mgrname is figured in startmgr, and is 'std' by default. startmgr is a shell script under $FND_TOP/bin/) (version depend?)
- To debug the ICM startup, set the value of DIAG_PARAM to Y in adcmctl.sh.
- The ICM log file on startup can be also located by SQL:
SELECT 'LOG=' fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'AND fcp.process_status_code = 'A';
b. directly from Oracle Applications :
System Administrator responsability
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Internal Manager Log
2. CONCURRENT MANAGERS (See note 105133.1)
----------------------
a. retrieve Manager log files under :
$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG
File format is:
w<Concurrent Process Id>.mgr (log from one of Concurrent managers)
c<Concurrent Process Id>.mgr (Conflict Resolution manager log)
t<Concurrent Process Id>.mgr (Transaction manager log)
s<Concurrent Process Id>.mgr
FNDxxxxx.mgr
If you see errors on cartridge in the logfile, below query will help to identify the queue:
SELECT q.concurrent_queue_id, q.concurrent_queue_name
FROM apps.fnd_concurrent_queues q, apps.fnd_cp_services s
WHERE enabled_flag = 'Y'
AND q.manager_type = s.service_id
AND s.cartridge_handle = 'CMDCART'; -- for example
b. directly from Oracle Applications :
System Administrator responsability
Navigator: Concurrent => Requests, choose a request ...
menu: Special => Manager Log
or
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Manager Log
3. CONCURRENT PROGRAMS
----------------------
a. retrieve Concurrent Program log files under :
$APPLCSF/$APPLLOG (if $APPLCSF and $APPLLOG exist) or
$<module_top>/$APPLLOG
File format is: l<Concurrent Request Id>.req
- If the concurrent program generates a output file, it goes to $APPLCSF/$APPLOUT.
- SQL can be used to get the exact file names for Log file and Output file:
SELECT logfile_name, outfile_name, outfile_node_name, last_update_date
FROM apps.FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = &requestID;
- There are also log files for concurrent requests under $COMMON_TOP/admin/log/$SID_$hostname. (Will they be useful only when you have Forms auditing on?)
b. directly from Oracle Applications :
System Administrator responsability (for own and other users requests) :
Navigator: Concurrent => Requests, button View Log...
Note: User may not be able to view other's Output (and Log) files.
4. AD, FND AND OTHER UTILITIES
-------------------------------
Some AD utilities like adpatch, adadmin, etc. generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file names are :
adunload.log
adrelink.log
adlibout.log
adlibin.log
adfrmgen.log
admrgpch.log
adrepgen.log
adctrl.log
admvcode.log
adaimgr.log
adwork01.log, adwork02.log, adwork03.log, ...
Utility adodfcmp generates log file under $<module_top>/admin/odf/ or $<module_top>/patch/<version>/odf/, file name is:
adodfcmp.log
FND utilities like fdfcmp, FNDFFVGN, FNDMDGEN generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file name is :
L<request_id>
5. USER EXITS (See note 292058.1)
--------------
User exits from forms generate log file under directory from where the forms server is started if variable FDUDEBUG is set to ON. File name is:
userexit.log
6. FORMS SERVER (See note 471921.1)
----------------
When starting forms server you can specify a log file name.
Exemples:
Windows: f60srv32 log=c:\temp\f60srv.log
Unix: f60ctl start port=9000 log=/temp/f60srv.log
(f60ctl is a script located in $ORACLE_HOME/bin)
Note: $COMMON_TOP/admin/log/$SID_$hostname/f60svrm.txt has very basic info from executing adfrmctl.sh
7. SQL*NET, NET8 LISTENER
--------------------------
You can specify LOG_DIRECTORY_<LISTENER_NAME>
You can set LOG_FILE and TRACE_FILE variables from a lsnrctl session.
There are also LOG_DIRECTORY_SERVER and LOG_FILE_SERVER parameters in sqlnet.ora.
8. APACHE, JSERV
----------------
Logs from Apache server are under $APACHE_TOP/logs, file names are:
error_log
access_log
httpds_access_log
httpds_error_log
sqlnet.log
Logs from Jserv are under $ORACLE_HOME/Apache/Jserv/logs (?), file names are:
jserv.log
debug.log
Note: it is recommended to clean up these log files, shutdown and restart Apache server before trying to reproduce the issue and analyze.
$APACHE_TOP is not defined in any $APPL_TOP/*.env file ("grep APACHE_TOP $APPL_TOP/*.env" gets nothing).
9. SSP5 (iPROCUREMENT)
---------------------
Apache and Jserv logs are useful to check for iProcurement (SSP5), you can also add these lines in ssp_init.txt (should be under $ORACLE_HOME/Apache/Jserv/etc) to generate specific SSP5 log file:
DebugOutput=<Directory/File name> (preferably under $APACHE_TOP/logs)
DebugLevel=5
DebugSwitch=ON
Note: don't forget to restart Apache server after editing ssp_init.txt.
10. PO Document Approval Manager
------------------------------------
Responsibility - System Administrator responsibility (Nav - Concurrent Manager - Administer).
Locate and find the PO Document Approval Manager and then click on Processes button and then select an Active concurrent manager process and click on the Manager Log button to display the log file.
11. Output Post Processor
---------------------------
See Metalink note 364547.1
Sunday, March 30, 2008
Table SYS.HISTGRM$ owned by SYS
SYS.HISTGRM$ holds statistics for tables. When we used csscan to converte a database to Unicode UTF-8 character set, it reported that SYS.HISTGRM$ had one row of Convertible data. We got "ORA-701 object necessary for warm starting database cannot be altered" from issuing command "truncate table sys.histgrm$".
We did followings to work it out:
1. Find the rowid from the csscan report on the record in SYS.HISTGRM$ that is Convertible.
2. Identify the object in SYS.HISTGRM$ and in the csscan report: SQL> select a.owner, a.object_name, a.object_type
from all_objects a, sys.histgrm$ b
where a.object_id = b.obj# and b.rowid='AAAAD7AABAAANS+ABL'
group by a.owner, a.object_name, a.object_type;
OWNER OBJECT_NAME OBJECT_TYPE
----- ----------- -----------
DSS BIN$P3VMZ66Y4JTgQwoBAoDglA==$0 TABLE
-- It shows that object is in recycle bin.
3. Purge the recyclebin as SYSDBA:
SQL> PURGE TABLE "BIN$P3VMZ66Y4JTgQwoBAoDglA==$0" ;
SQL> purge DBA_RECYCLEBIN;
-- Now, "select * from DBA_RECYCLEBIN;" should got zero rows.
-- Note: If it is a regular table, run a package to delete the satats:
-- exec DBMS_STATS.DELETE_TABLE_STATS ('OWNER','TABLE_NAME');
After those steps, SYS.HISTGRM$ did not show up in the csscan report.
Notes:
Step 1 and Step 2 can be replaced by running script @?/nls/csscan/sql/analyze_histgrm.sql (which uses csmv$errors).
Step 3 is to delete the stats on involved tables in order to have HISTGRM$ contain just the clob records. These clob records can be altered with the csalter.plb script.
We did followings to work it out:
1. Find the rowid from the csscan report on the record in SYS.HISTGRM$ that is Convertible.
2. Identify the object in SYS.HISTGRM$ and in the csscan report: SQL> select a.owner, a.object_name, a.object_type
from all_objects a, sys.histgrm$ b
where a.object_id = b.obj# and b.rowid='AAAAD7AABAAANS+ABL'
group by a.owner, a.object_name, a.object_type;
OWNER OBJECT_NAME OBJECT_TYPE
----- ----------- -----------
DSS BIN$P3VMZ66Y4JTgQwoBAoDglA==$0 TABLE
-- It shows that object is in recycle bin.
3. Purge the recyclebin as SYSDBA:
SQL> PURGE TABLE "BIN$P3VMZ66Y4JTgQwoBAoDglA==$0" ;
SQL> purge DBA_RECYCLEBIN;
-- Now, "select * from DBA_RECYCLEBIN;" should got zero rows.
-- Note: If it is a regular table, run a package to delete the satats:
-- exec DBMS_STATS.DELETE_TABLE_STATS ('OWNER','TABLE_NAME');
After those steps, SYS.HISTGRM$ did not show up in the csscan report.
Notes:
Step 1 and Step 2 can be replaced by running script @?/nls/csscan/sql/analyze_histgrm.sql (which uses csmv$errors).
Step 3 is to delete the stats on involved tables in order to have HISTGRM$ contain just the clob records. These clob records can be altered with the csalter.plb script.
Subscribe to:
Comments (Atom)