R12: How to Get the R12 Trace and FND Debug File / FND Log for Payables Processes

R12: How to Get the R12 Trace and FND Debug File / FND Log for Payables Processes [ID 458371.1]
  Modified 11-MAR-2011     Type HOWTO     Status PUBLISHED
Applies to:

Oracle Payables - Version: 12.0.0 to 12.1.2 - Release: 12.0 to 12.1
Information in this document applies to any platform.
Goal


Join our growing Oracle Payables Community and learn from your peers and Oracle on how to address your unique issues in AP.

How can we obtain the R12 Trace or FND Debug Log Filefor a request or online transaction?
Solution

To get the R12 Trace:

1. Navigate Responsibility: System Administrator > Profile > System > Query

User: User submitting the Report
Profile: Initialization SQL Statement - Custom

2. Click on User column - Edit Field and enter

begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET tracefile_identifier=''999999999'' EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'||''''); end;
Note: For tracefile_identifier you can change from 999999999 to whatever value you want to see in the trace file name to help identify the file created by this trace.

3. Save.
4. Upload the raw and tkprof'd trace file created. Use the following SQL to locate the file:

select value
from v$parameter
where name = 'user_dump_dest';
Please upload the output formatted in an Excel file.



To get the FND debug messages:

1. Navigate Responsibility: System Administrator > Profile > System > Query

User: User submitting the Report
Profile: FND:%Debug%

2. Set the following Profile values at the user level

FND: Debug Log Enabled Yes
FND: Debug Log Filename  
FND: Debug Log Level STATEMENT
FND: Debug Log Mode Asynchronous with Cross-Tier Sequencing
FND: Debug Log Module %

3. Save.

4. Navigate: Payables Responsibility > Other > Request > Run > Select and Submit the Report/Request

5. Once request has completed, disable Trace and FND Debug messages by setting the profiles back to their original values.

6.  Derive debug message using the following query:

SELECT substr(module,1,70), MESSAGE_TEXT, timestamp, log_sequence
FROM fnd_log_messages msg, fnd_log_transaction_context tcon
WHERE msg.TRANSACTION_CONTEXT_ID = tcon.TRANSACTION_CONTEXT_ID
AND tcon.TRANSACTION_ID =  
ORDER BY LOG_SEQUENCE

7. Please upload the output formatted in an Excel file.

8.  Lastly, remember to disable FND: Debug Log after you are done; otherwise, you could encounter tablespace issues.



Online FND log

To capture FND Debugging for an Online action such as validation or Create Accounting), complete the steps below:

1)  Set the Profiles

FND: Debug Log Enabled : Yes
FND: Debug Log Level : Statement
FND: Log Module : %
2)  Get the current log sequence in the FND table.

select max(log_sequence) from fnd_log_messages ; -- start
3)  Run Online process (Validation/Accounting) for one transaction (invoice/payment) from the
     respective workbench/screen.

4) Get the log sequence in the FND table after process completes.

select max(log_sequence) from fnd_log_messages ; -- end
5)  Get the FND log data for process

select * from fnd_log_messages where log_sequence between and ; 
6)  Spool this into an xls file.

7) Lastly, remember to disable FND: Debug Log after you are done; otherwise, you could encounter tablespace issues.



@ R12 TRACE FILE, R12 TRACE FILE, R12 TRACE FILE, R12 TRACE FILE, R12 TRACE FILE
References

NOTE:417238.1 - How to obtain and read a debug logfile for R12 E-Business Tax (EBTAX)

 Related
Products
Oracle E-Business Suite > Financial Management > Procure to Pay > Oracle Payables
Keywords
FND_LOG_TRANSACTION_CONTEXT; FND_LOG_MESSAGES; TRACE; EXCEL; TIMESTAMP; R12

Back to top

 Rate this document

Error Message Invalid LOB Locator Specified ORA-22275 ORA-06512 In Approval Notification

Error Message Invalid LOB Locator Specified ORA-22275 ORA-06512 In Approval Notification [ID 743148.1]
  Modified 29-NOV-2011     Type PROBLEM     Status PUBLISHED
In this Document
  Symptoms
  Cause
  Solution
     For Releases 11.5.9 and 11.5.10:
     For Release 12 or 12.1:
  References

Applies to:

Oracle Purchasing - Version: 11.5.10.2 to 12.1.3 - Release: 11.5 to 12.1
Information in this document applies to any platform.
Symptoms

The approval notification does not show the lines or action history of the document. It displays an error but it does not prevent the user from approving the document. The following error is displayed:

ERROR
ORA-06502: numeric value error: invalid LOB locator specified: ORA-22275 ORA-06512 at
"APPS_WF_NOTIFICATION". LINE 5585
ORA-06512 at line 5

Steps To Reproduce:
1. Log in as approver
2. Go to the notification summary
3. Open the approval notification

It displays the error in the notification body.

Cause

The workflow attribute PO_LINES_DETAILS uses the PLSQL:package.procedure causing the error invalid LOB locator specified in the notification when the approval process expects a PLSQLCLOB:package.procedure.

PO_LINES_DETAILS PLSQL:PO_WF_PO_NOTIFICATION.GET_PO_LINES_DETAILS/POAPPRV:274417-680132

This error will affect only open notifications and will happen after applying any of these patches:

11.5.9 : Patch: 5671680
11.5.10: Patch: 6932794
12 : Patch: 7291462



Note: An easy way to check if this issue is being encountered is by executing the following queries:

SELECT count(*)
FROM WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WFN
WHERE WFN.MESSAGE_TYPE IN ('POAPPRV',
'REQAPPRV')
AND WFN.STATUS <> 'CLOSED'
AND WFN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
AND WNA.NAME IN ('PO_REQ_APPROVE_MSG',
'PO_REQ_APPROVED_MSG',
'PO_REQ_NO_APPROVER_MSG',
'PO_REQ_REJECT_MSG',
'REQ_LINES_DETAILS',
'PO_LINES_DETAILS')
AND (WNA.TEXT_VALUE LIKE 'PLSQL:%POAPPRV%'
OR WNA.TEXT_VALUE LIKE 'PLSQL:%REQAPPRV%')

SELECT count(*)
FROM wf_item_attribute_values wiav,
WF_ITEM_ACTIVITY_STATUSES wias
WHERE wiav.NAME IN ('PO_REQ_APPROVE_MSG',
'PO_REQ_APPROVED_MSG',
'PO_REQ_NO_APPROVER_MSG',
'PO_REQ_REJECT_MSG',
'REQ_LINES_DETAILS',
'PO_LINES_DETAILS')
AND (wiav.text_value LIKE 'PLSQL:%POAPPRV%'
OR wiav.text_value LIKE 'PLSQL:%REQAPPRV%')
AND wiav.item_key = wias.item_key
AND wiav.item_type = wias.item_type
AND wias.notification_id IN (SELECT notification_id FROM wf_notifications
WHERE message_type IN ('POAPPRV','REQAPPRV')
AND status <> 'CLOSED')

If these queries return any rows, please go ahead and apply the datafix script.

Solution

For Releases 11.5.9 and 11.5.10:

1. Download and review the readme and pre-requisites for datafix Patch 6234198. The patch is intended for 11.5.9 but it can be applied in 11.5.10.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Retest the issue.

5. Migrate the solution as appropriate to other environments.

Note: Apply root cause patch 5671680 for 11.5.9. Release 11.5.10.x does not require a root cause fix.

For Release 12 or 12.1:

Download the Patch 6234198 from Metalink and extract the two SQL files: POXINVLOBC.sql. and POXINVLOBF.sql

1. In a SQL*Plus session, run the collection script and the fix script separately.

The collection script(POXINVLOBC.sql) will provide the data that has been corrupted or identified to be containing the problem.

The fix script(POXINVLOBF.sql) will provide the fix to the problem being identified.

2. In the same sql session rerun the collection script to see the result as none.

3. Retest the issue

Note: Release 12 does not require a root cause patch.

Note: The scripts will not update closed notifications





References

BUG:5671680 - REP-I:LARGE:ORA-06502 CHAR STRING BUFFER TOO SMALL WHEN VIEWING PO NOTIF
BUG:6932794 - MORE INFO REQUESTED NOTIFICATIONS ERROR WITH ORA-06502: PL/SQL: NUMERIC OR VALUE
BUG:7006113 - UNIT PRICE IN APPROVAL NOTIFICATION IS IN .XXX INSTEAD OF 0.XXX FORMAT
BUG:7204903 - DATAFIX AFTER ROOT CAUSE PATCH 6932794
NOTE:742454.1 - Scripts poxpoort.sql and poxposgt.sql Fail With ORA-00955 During Patching

 Related
Products
Oracle E-Business Suite > Procurement > Procurement > Oracle Purchasing
Keywords
APPROVAL NOTIFICATION; NOTIFICATION; PLSQLCLOB
Errors
ORA-6502; ORA-22275; ORA-6512

Back to top

 Rate this document

How To Maintain and/or Add Redo Logs [

How To Maintain and/or Add Redo Logs [ID 602066.1]
  Modified 14-OCT-2011     Type HOWTO     Status PUBLISHED
In this Document
  Goal
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.3 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Goal

The purpose of this document is to demonstrate:

A. How to maintain and/or add redo logs.

B. How to determine the optimal size for redo logs



Solution

 A.  How to maintain and/or add redo logs.

1. Review information on existing redo logs.

SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#
2. Add new groups

ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;

3. Check the status on all redo logs again.

SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#
4.  Drop the online redo log groups that are not needed.  You must have the ALTER DATABASE system privilege.

Note: Before dropping an online redo log group, consider the following restrictions and precautions:
a.  An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)

b. You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur.

By using this command :

ALTER SYSTEM SWITCH LOGFILE;

c. Make sure an online redo log group is archived (if archiving is enabled) before dropping it.  This can be determined by:

GROUP# ARC STATUS
---------   ---     ----------------
1             YES   ACTIVE
2             NO    CURRENT
3             YES   INACTIVE
4             YES   UNUSED
5             YES   UNUSED
6             YES   UNUSED

d.  Check that the group is inactive and archived before dropping it .

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;


ALTER DATABASE DROP LOGFILE GROUP 3;

e.  After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files.  For more information refer to Note 395062.1


B.  How to determine the optimal size for redo logs

You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to determine the size of your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online logs
to be at least this size.

Note, however, that the redo log file size affects the MTTR. In some cases, you may be able to
refine your choice of the optimal FAST_START_MTTR_TARGET value by re-running the MTTR Advisor with your suggested optimal log file size.

You can also refer to this Note 1038851.6 - How to Estimate Size of Redo Logs

Please note that there is no column OPTIMAL_LOGFILE_SIZE in
V$INSTANCE_RECOVERY view in 9i. It only applies to 10g.

References

Example of How To Resize the Online Redo Logfiles

          Example of How To Resize the Online Redo Logfiles [ID 1035935.6]
 
EXAMPLE OF HOW TO RESIZE THE ONLINE REDO LOGS:
==============================================

Often times the online redo logs are sized too small causing database
performance problems.

The following is an example of how to resize the online log groups:

NOTE:  Examples are given for 9i and higher.   In prior releases, you needed
to use Server Manager and connect as the internal user.

1. First see the size of the current logs:            
             
   > sqlplus /nolog
   SQL> connect / as sysdba

   SQL> select group#, bytes, status from v$log;
   GROUP#     BYTES      STATUS                  
   ---------- ---------- ----------------                    
            1    1048576 INACTIVE                      
            2    1048576 CURRENT                        
            3    1048576 INACTIVE                          
                             
    Logs are 1MB from above, let's size them to 10MB.                              
                                 

2. Retrieve all the log member names for the groups:                                          
                                                   
   SQL> select group#, member from v$logfile;                                  
                                                     
            GROUP# MEMBER                                                        
   --------------- ----------------------------------------                      
                 1 /usr/oracle/dbs/log1PROD.dbf                                  
                 2 /usr/oracle/dbs/log2PROD.dbf                                  
                 3 /usr/oracle/dbs/log3PROD.dbf                                  
                                                             
                                                               
3. In older versions of the database you needed to shutdown and issue the following
   commands in restricted mode.   You can still do this, but the database can be online
   to perform these changes.

   Let's create 3 new log groups and name them groups 4, 5, and 6, each 10MB in
   size:                          
                           
   SQL> alter database add logfile group 4                            
           '/usr/oracle/dbs/log4PROD.dbf' size 10M;                                
                               
   SQL> alter database add logfile group 5                                
           '/usr/oracle/dbs/log5PROD.dbf' size 10M;    
       
   SQL> alter database add logfile group 6        
           '/usr/oracle/dbs/log6PROD.dbf' size 10M;  
       

4. Now run a query to view the v$log status:                                  
                                                                       
   SQL> select group#, status from v$log;                                      

      GROUP# STATUS
   --------- ----------------
           1 INACTIVE
           2 CURRENT
           3 INACTIVE          
           4 UNUSED
           5 UNUSED
           6 UNUSED        
     
   From the above we can see log group 2 is current, and this is one of the
   smaller groups we must drop. Therefore let's switch out of this group into
   one of the newly created log groups.                
                 

5. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:                                
   
   SQL> alter system switch logfile;        
   ** repeat as necessary until group 4 is CURRENT **
                                                           
                                                           
6. Run the query again to verify the current log group is group 4:                                                          
                                                               
   SQL> select group#, status from v$log;                                      
                                                                 
      GROUP# STATUS
   --------- ----------------
           1 INACTIVE
           2 INACTIVE
           3 INACTIVE          
           4 CURRENT
           5 UNUSED
           6 UNUSED                        
                         
                                                 
7. Now drop redo log groups 1, 2, and 3:    

   SQL> alter database drop logfile group 1;                                  
   SQL> alter database drop logfile group 2;                                  
   SQL> alter database drop logfile group 3;                                  
                                 
   Verify the groups were dropped, and the new groups' sizes are correct.

   SVRMGR> select group#, bytes, status from v$log;

      GROUP#     BYTES STATUS
   --------- --------- ----------------
           4  10485760 CURRENT
           5  10485760 UNUSED
           6  10485760 UNUSED          


 
8.  At this point, you consider taking a backup of the database.

9.  You can now go out to the operating system and delete the files associated
    with redo log groups 1, 2, and 3 in step 2 above as they are no longer
    needed:
   
    % rm /usr/oracle/dbs/log1PROD.dbf
    % rm /usr/oracle/dbs/log2PROD.dbf
    % rm /usr/oracle/dbs/log3PROD.dbf                                                
                                                 
   Monitor the alert.log for the times of redo log switches. Due to increased
   redo log size, the groups should not switch as frequently under the same
   load conditions.


References:
===========

Chapter 6 of the Oracle8i Administrator's Guide, Release 8.1.5,
Part No. A67772-01 for further information on redo log maintenance.

Chapter 7 of the Oracle 9i Administrator's guide
Part No. A96521-01 for further information on redo log maintenance.


Chapter 6 of the Oracle 10g Administrator's guide
Part No. B10739-01 for further information on redo log maintenance.

Chapter 10 of the Oracle 11g Administrator's guide
Part No. B28310-04 for further information on redo log maintenance.


 Related
Products
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Keywords
LOG SIZE; LOGFILE; MAINTENANCE; ONLINE REDO LOG; RESIZE; V$LOG; V$LOGFILE

Back to top

 Rate this document

Resize the size of redo log files

METALINK ID --- ID 1035935.6

SQL> select group#,status,member from v$logfile;

    GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
         2
/erpdata21/CLN03/data/log02a.log

         2
/erpdata21/CLN03/data/log02b.log

         1
/erpdata21/CLN03/data/log01a.log


    GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
         1
/erpdata21/CLN03/data/log01b.log

/erpdata21/CLN03/data/log03a.log

         3
/erpdata21/CLN03/data/log03b.log

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








alter database add logfile group 1 ('/erpdata21/CLN03/data/newredo01a.log', '/erpdata21/CLN03/data/newredo01b.log') size 1024M reuse;

alter database add logfile group 2 ('/erpdata21/CLN03/data/newredo02a.log', '/erpdata21/CLN03/data/newredo02b.log') size 1024M reuse;

alter database add logfile group 3 ('/erpdata21/CLN03/data/newredo03a.log', '/erpdata21/CLN03/data/newredo03b.log') size 1024M reuse;


SQL> alter database open;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 ACTIVE
         3 ACTIVE

SQL> alter  system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 ACTIVE
         3 CURRENT

SQL> alter system checkpoint;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 ('/erpdata21/CLN03/data/newredo01a.log', '/erpdata21/CLN03/data/newredo01b.log') size 1024M reuse;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 INACTIVE
         3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 ACTIVE

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 ('/erpdata21/CLN03/data/newredo02a.log', '/erpdata21/CLN03/data/newredo02b.log') size 1024M reuse;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 ACTIVE

SQL> alter system checkpoint;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 ('/erpdata21/CLN03/data/newredo03a.log', '/erpdata21/CLN03/data/newredo03b.log') size 1024M reuse;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT

SQL> select group#, members, sum(bytes)/1024/1024 from v$log group by group#,members;

    GROUP#    MEMBERS SUM(BYTES)/1024/1024
---------- ---------- --------------------
         1          2                 1024
         2          2                 1024
         3          2                 1024

SQL>

How to Shrink the datafile of Undo Tablespace

How to Shrink the datafile of Undo Tablespace [ID 268870.1]
  Modified 17-AUG-2011     Type HOWTO     Status PUBLISHED
In this Document
  Goal
  Solution
  References

Applies to:

Oracle Server - Standard Edition - Version: 9.2.0.7 to 11.2.0.2 - Release: 9.2 to 11.2
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2   [Release: 9.2 to 11.2]
Information in this document applies to any platform.
Goal

Your production database has semiannual or annual purging programs which generate huge redo. Due to this requirement, your undo tablespace grows rapidly and occupies most of the space on file system.
The purging process is run only few times a year. So would not like to keep the huge undo datafile in your database throughout the year. You don't want to buy additional disks unnecessarily.

You have created an undo tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA 1651 : unable to extend save undo segment by in tablespace .

You have tried "alter database datafile .. resize" which always fails with Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value.

You want to shrink the datafile to utilize the disk space for other tablespaces or other purposes.
Solution

-- Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 100m;

-- Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;

-- Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.

NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.    Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted.  The disk space will remain "allocated" from the OS perspective until the database restart.

Points to Consider:

-  The value for undo_retention also has a role in growth of undo tablespace. If there is no way to get the undo space for a new transaction, then the undo space (retention) will be reused. But, if the datafiles for undo tablespace are set to auto extensible, it will not reuse the space. In such scenarios new transaction will allocate a space and your undo tablespace will start growing.

-  Is big really bad?  Overhead on larger file/tablespaces can theoretically impact the database and the OS.  With a small file, the OS would have to do minimal I/O.  Oracle would be able to cache the whole file and there would be less segments to manage.  With AUM you get bitmapped files and all its (space management) performance benefits-- (number of) undo segments are automatically managed and are not related to the size of the tablespace.  With the bigger file/tablespace you will have other overhead--e.g. backup will take longer--but as far as the undo management there should be no performance impact just because the file/tbs is bigger.  That said, it is important to monitor systems (e.g. with statspack) and watch for environment-specific issues.


References

NOTE:135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)

 Related
Products
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition
Keywords
AUTOMATIC UNDO MANAGEMENT; PURGE PROCESS; SPACE MANAGEMENT; TABLESPACE GROWS; UNDO; UNDO_RETENTION; UNDO_TABLESPACE; DYNAMIC ADV USE
Errors
ORA-30013

Back to top

Concurrent Processing - How to Cancel a Concurrent Request Stuck in the Queue

Concurrent Processing - How to Cancel a Concurrent Request Stuck in the Queue? [ID 749748.1]
  Modified 13-APR-2011     Type PROBLEM     Status PUBLISHED
In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Application Object Library - Version: 11.0.0 to 12.0 - Release: 11 to
Oracle Concurrent Processing - Version: 11.5.10.0 to 12.0.6   [Release: 11.5.10 to 12]
Information in this document applies to any platform.
11.0.x - 12.0.x
Symptoms

***Checked for relevance on 30-Aug-2010***



Unable to cancel a concurrent request out of the queue that is stuck.

Cause

Tried to Cancel a concurrent request. Used the "Cancel Request" button from the Administer > Concurrent > Manager form.

Got the following message:

Request xxxxxx can no longer be cancelled. The Concurrent Manager Process that was running this request has exited abnormally. The ICM will mark this request as completed with error.


Solution

Manually cancel the request out of the queue with the following SQL against the offending
request id(s). This can be safely done while managers are up and running:




SQL> UPDATE fnd_concurrent_requests
     SET phase_code = 'C', status_code = 'X'
     WHERE request_id = ''; 
     commit;



Note:  To obtain request details prior to cancelling the request, use Note 134035.1 ANALYZEREQ.SQL - Detailed Analysis of One Concurrent Request. When prompted, provide the request id to be analyzed.  This can be useful for determining the reason why the request may be stuck in the queue.

References

Release 12 File System Changes : R12 Vs 11i

Release 12 File System Changes : R12 Vs 11i [ID 433111.1]
  Modified 07-MAY-2010     Type WHITE PAPER     Status PUBLISHED
In this Document
  Abstract
  Document History
  Release 12 File System Changes : R12 Vs 11i

Applies to:

Oracle Applications Technology Stack - Version: 11.5.2 to 12.0.6 - Release: to 12
Information in this document applies to any platform.
Abstract

Oracle Applications Release 12 Technology Stack and File System changes vis-a-vis Oracle Applications 11i
Document History

07-MAY-2010 - Corrected location of forms default.env
Release 12 File System Changes : R12 Vs 11i

Here, you will find a ready reckoner for the important File system and Environment changes that have taken place between 11i and R12:


File System

 File

Oracle Applications 11i

Release 12

Environment Source file APPSORA.env APPS_.env
Context File (middle tier) $APPL_TOP/admin/$TWO_TASK.xml $INST_TOP/appl/admin/$TWO_TASK_.xml
tnsnames.ora (OH)

$ORACLE_HOME/network/admin/ $INST_TOP/ora/10.1.2/network/admin
listener.ora $ORACLE_HOME/network/admin/ $INST_TOP/ora/10.1.2/network/admin
appsweb.cfg
$OA_HTML/bin

$INST_TOP/ora/10.1.2/forms/server
tnsnames.ora (Apache)
$IAS_ORACLE_HOME/network/admin/

$INST_TOP/ora/10.1.3/network/admin
zone.properties $IAS_ORACLE_HOME/Apache/Jserv/etc
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/oa_servlets/orion-web.xml  and  .../oacore/html/orion-web.xml
(Same file located in both directories)

jsev.properties $IAS_ORACLE_HOME/Apache/Jserv/etc
$INST_TOP/ora/10.1.3/opmn/conf/opmn.xml

httpd.conf $IAS_ORACLE_HOME/Apache/Apache/conf $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf
apps.conf $IAS_ORACLE_HOME/Apache/Apache/conf $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf
formservlet.ini
$IAS_ORACLE_HOME/Apache/Jserv/etc

$ORA_CONFIG_HOME/10.1.2/forms/server/default.env

topfile.txt
$APPL_TOP/admin

$APPL_CONFIG_HOME/admin

adovars.env
$APPL_TOP/admin

$APPL_CONFIG_HOME/admin

adjborg2.txt
$APPL_TOP/admin

$APPL_CONFIG_HOME/admin

SSL Certificates
$COMMON_TOP/admin/certs

$INST_TOP/certs

AD scripts logs
$COMMON_TOP/admin/log/

$LOG_HOME/appl/admin/log

Concurrent Request logs
$APPLCSF

 $APPLCSF

Apache logs
 $IAS_ORACLE_HOME/Apache/Apache/logs

 $LOG_HOME/ora/10.1.3/Apache

Jserv logs $IAS_ORACLE_HOME/Apache/Jserv/logs $LOG_HOME/ora/10.1.3/j2ee
javacache.log $COMMON_TOP/rgf/ $LOG_HOME/appl/rgf











Environment variables

Variable

Oracle Applications 11i

Release 12

APPL_TOP $HOME/appl $HOME/apps/apps_st/appl
COMMON_TOP

$HOME/comn $HOME/apps/apps_st/comn
ORACLE_HOME (applmgr) $HOME/ora/8.0.6 $HOME/apps/tech_st/10.1.2
IAS_ORACLE_HOME $HOME/ora/iAS $HOME/apps/tech_st/10.1.3
ORACLE_HOME (oracle) $HOME/db/10.2.0 $HOME/db/tech_st/10.2.0
ORADATA $HOME/data $HOME/db/apps_st/data
JAVA_TOP, OA_JAVA $COMMON_TOP/java $COMMON_TOP/java/classes
OA_HTML $COMMON_TOP/html $COMMON_TOP/webapps/oacore/html
FND_SECURE $FND_TOP/secure/ $INST_TOP/appl/fnd/12.0.0/secure
ADMIN_SCRIPTS_HOME $COMMON_TOP/admin/scripts/ $INST_TOP/admin/scripts
LOG_HOME - $INST_TOP/logs
FORMS_WEB_CONFIG_FILE - $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg
AF_JLIB - $COMMON_TOP/java/lib
JAVA_BASE - $COMMON_TOP/java
INST_TOP - $HOME/inst/apps/
ORA_CONFIG_HOME

-
$INST_TOP/ora

APPLCSF
$COMMON_TOP/admin

$LOG_HOME/appl/conc

OPP Service log file and location on server In previous post on OPP, we learnt about OPP services using application. Below mentioned SQL is to identify the log file name and location in unix server.

OPP Service log file and location on server In previous post on OPP, we learnt about OPP services using application. Below mentioned SQL is to identify the log file name and location in unix server.

If concurrent program ends in warning due to any OPP service failure, run below sql by passing request id.

SELECT fcpp.concurrent_request_id req_id,
  fcp.node_name,
  fcp.logfile_name
FROM fnd_conc_pp_actions fcpp,
  fnd_concurrent_processes fcp
WHERE fcpp.processor_id        = fcp.concurrent_process_id
AND fcpp.action_type           = 6
AND fcpp.concurrent_request_id = &REQ -- 12345

Req_id      Node_name          LogFileName
12345  ebs002learn   /oracle/ebs/inst/apps/ebs_002learn/logs/appl/conc/log/FNDOPP383669.txt

Open putty
Login to server ebs002learn
open specified Logfile and search for your request id (12345) for detail log message

Concurrent Processing - ICM log file shows 'CONC-SM TNS FAIL', 'Call to PingProcess failed', and/or 'Call to StopProcess failed' for FNDCPGSC/FNDOPP [ID 1312632.1] Modified 02-APR-2012 Type PROBLEM Status PUBLISHED In this Document Symptoms Changes Cause Solution Information Center, Diagnostics, & Community References Applies to: Oracle Application Object Library - Version: 11.5.9 to 12.0.0 - Release: 11.5 to 12 Oracle Concurrent Processing - Version: 11.5.9 to 12.0.0 [Release: 11.5 to 12] Information in this document applies to any platform. FNDCPOPP FNDCPGSC FNDRSRUN INCTCM CONC-SM TNS Symptoms Have an issue or question related to Core Concurrent processing? Please visit the Core Concurrent Processing Community to and post your question or issue for a quick response from Oracle and Industry experts. End users begin to see issues running requests, checking the ICM logfile shows the following errors: CONC-SM TNS FAIL Call to PingProcess failed for FNDCPGSC CONC-SM TNS FAIL Call to StopProcess failed for FNDCPGSC CONC-SM TNS FAIL Call to PingProcess failed for FNDOPP CONC-SM TNS FAIL Call to StopProcess failed for FNDOPP Changes Issue can occur after fresh installs, after patching/maintenance, or after other configuration changes Cause To manage non concurrent processing middle tier services, Service Managers calls into control cartridges (shared object libraries) to Start, Stop, Ping, Suspend, Resume, Verify GSM service processes. Following are the two control cartridges supplied with GSM. 1. AQCART (for Java services) Examples: Service Fulfillment Manager Adapters Workflow Notification Mailer 2. CMDCART (for C services) Examples: Forms Listener Metrics Server Metrics Client Reports Server Apache Listener Each service may have one or more active OS processes. These processes are monitored by the ICM and controlled via the Service Manager acting as the ICM's agent. All communication between Concurrent Processing and the integrated service will take place through the StartProcess,PingProcess and StopProcess functions. For example to check the status of generic services, the ICM will call the PingProcess function from the appropriate control cartridge. Following messages are expected when the control cartridge is unable to communicate with generic services. Call to PingProcess failed for FNDCPGSC Call to PingProcess failed for FNDOPP Hence issue can have multiple causes, usually due to instance specific issues during installs,patching/maintenance, or other configuration changes. Solution Potential Solutions Please perform the following steps: 1. Stop all middle tier services including the concurrent managers. Make sure no FNDLIBR, FNDSM, or other dead processes are running. 2. Stop and then restart the database. 3. Connect to SQLPLUS as APPS user and run the following : EXEC FND_CONC_CLONE.SETUP_CLEAN; COMMIT; EXIT; 4. Reference Note.260887.1 regarding the Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES. 5.. Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and Web tiers to repopulate the required system tables. 6. Connect to SQLPLUS as APPS user and run the following statement : select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%'; If the above SQL does not return any value please do the following: cd $FND_TOP/patch/115/sql Connect to SQLPLUS as APPS user and run the following script : SQL> @afdcm037.sql; Reference Note 218893.1 How to Create The Service Manager 'FNDSM' on Oracle Applications to create libraries for FNDSM and create managers for preexisting nodes. Check again that FNDSM entries now exist: select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%'; 7. . Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit). Reference Note.134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables 8. Start the middle tier services including your concurrent manager. 9. Retest the issue. If the above potential solutions do not resolve the issue, please seek support assistance to reference limited distribution Note.1312980.1 to generate diagnostics and debugging to further investigate the issue

Concurrent Processing - ICM log file shows 'CONC-SM TNS FAIL', 'Call to PingProcess failed', and/or 'Call to StopProcess failed' for FNDCPGSC/FNDOPP [ID 1312632.1]
  Modified 02-APR-2012     Type PROBLEM     Status PUBLISHED
In this Document
  Symptoms
  Changes
  Cause
  Solution
     Information Center, Diagnostics, & Community
  References

Applies to:

Oracle Application Object Library - Version: 11.5.9 to 12.0.0 - Release: 11.5 to 12
Oracle Concurrent Processing - Version: 11.5.9 to 12.0.0   [Release: 11.5 to 12]
Information in this document applies to any platform.
FNDCPOPP FNDCPGSC FNDRSRUN INCTCM CONC-SM TNS
Symptoms


Have an issue or question related to Core Concurrent processing? Please visit the Core Concurrent Processing Community to and post your question or issue for a quick response from Oracle and Industry experts.

End users begin to see issues running requests, checking the ICM logfile shows the following errors:

CONC-SM TNS FAIL
Call to PingProcess failed for FNDCPGSC
CONC-SM TNS FAIL
Call to StopProcess failed for FNDCPGSC
CONC-SM TNS FAIL
Call to PingProcess failed for FNDOPP
CONC-SM TNS FAIL
Call to StopProcess failed for FNDOPP


Changes

Issue can occur after fresh installs, after patching/maintenance, or after other configuration changes
Cause

To manage non concurrent processing middle tier services, Service Managers calls into control cartridges (shared object libraries) to Start, Stop, Ping, Suspend, Resume, Verify GSM service processes. Following are the two control cartridges supplied with GSM.

1. AQCART (for Java services)
 Examples:
 Service Fulfillment Manager Adapters
 Workflow Notification Mailer
2. CMDCART (for C services)
Examples:
Forms Listener
Metrics Server
Metrics Client
Reports Server
Apache Listener
Each service may have one or more active OS processes. These processes are monitored by the ICM and controlled via the Service Manager acting as the ICM's agent. All communication between Concurrent Processing and the integrated service will take place through the  StartProcess,PingProcess and StopProcess functions.

For example to check the status of generic services, the ICM will call the PingProcess function from the appropriate control cartridge. Following messages are expected when the control cartridge is unable to communicate with generic services.

Call to PingProcess failed for FNDCPGSC
Call to PingProcess failed for FNDOPP

Hence issue can have multiple causes, usually due to instance specific issues during installs,patching/maintenance, or other configuration changes.
Solution

Potential Solutions

Please perform the following steps:

1. Stop all middle tier services including the concurrent managers. Make sure no FNDLIBR, FNDSM, or other dead processes are running.

2. Stop and then restart the database.

3. Connect to SQLPLUS as APPS user and run the following :

EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

4.  Reference Note.260887.1 regarding the Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES.

5.. Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and Web tiers to repopulate the required system tables.

6. Connect to SQLPLUS as APPS user and run the following statement :

select CONCURRENT_QUEUE_NAME
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';

If the above SQL does not return any value please do the following:

cd $FND_TOP/patch/115/sql

Connect to SQLPLUS as APPS user and run the following script :

SQL> @afdcm037.sql;

Reference Note 218893.1 How to Create The Service Manager 'FNDSM' on Oracle Applications
to create libraries for FNDSM and create managers for preexisting nodes.

Check again that FNDSM entries now exist:

select CONCURRENT_QUEUE_NAME
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';

7. . Run the CMCLEAN.SQL script from the referenced note below (don't forget to
commit). Reference Note.134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

8. Start the middle tier services including your concurrent manager.

9. Retest the issue.

If the above potential solutions do not resolve the issue, please seek support assistance to reference limited distribution Note.1312980.1 to generate diagnostics and debugging to further investigate the issue

GET_SM_INFO Failed Due to ORA-01422: Exact Fetch Returns More Than Requested Number of Rs

GET_SM_INFO Failed Due to ORA-01422: Exact Fetch Returns More Than Requested Number of Rs [ID ow289817.1]
  Modified 28-DEC-2009     Type PROBLEM     Status ARCHIVED

Checked for relevance on 28-DEC-2009
Applications Install - 11.5.8 to 11.5.10

SYMPTOMS:
=========

Starting the Concurrent Managers with profile option 'Concurrent:GSM Enabled'set to'Y'
generates the following error:

ORACLE error 1422 in get_sm_info

Cause:  get_sm_info failed due to ORA-1422: exact fetch returns more than
requested number of rows.

The SQL statement being executed at the time of the error was: select
FND_CONCURRENT_PROCESSES_S.nextval
Could not initialize the Service Manager FNDSM__prod. Verify
that  has been registered for concurrent processing.
ORACLE error 1427 in cleanup_node

Cause:  cleanup_node failed due to ORA-1427: single-row subquery returns more
than one row
ORA-6512: at "APPS.FND_CP_FNDSM", line 29
ORA-6512: at line 1.

Routine AFPEIM encountered an error while starting concurrent manager
FNDLIBR101 with library /proda/mtaprod/mtaprodappl/fnd/11.5.0/bin/FNDLIBR.

Check that your system has enough resources to start a concurrent manager
process.

CAUSE:
======

SQL> select concurrent_queue_name, node_name from fnd_concurrent_queues where
     manager_type=6;

CONCURRENT_QUEUE_NAME          NODE_NAME
------------------------------ ------------------------------
FNDSM_SYSADMIN-SUN             SYJONES-SUN
FNDSM_AOLESC2-SUN              SYJONES-SUN
FNDSM_SYJONES-SUN              SYJONES-SUN

There is more than one FNDSM Concurrent Queue defined to run
on Node SYJONES-SUN.

This is a result of performing a clone from SYSADMIN-SUN & AOLESC2-SUN
to SYJONES-SUN and carrying over the Source machine names as
FNDSM concurrent queues.


SOLUTION:
=========

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
     COMMIT;
     EXIT;


Re-run AutoConfig.

Concurrent Managers Are Down Error: "Cause: cleanup_node failed due to ORA-01427: single-row subquery returns more than one row" "Ora-06512: At "Apps.Fnd_cp_fndsm", Line 29"

Concurrent Managers Are Down Error: "Cause: cleanup_node failed due to ORA-01427: single-row subquery returns more than one row" "Ora-06512: At "Apps.Fnd_cp_fndsm", Line 29" [ID 1308821.1]
  Modified 19-JUN-2011     Type PROBLEM     Status ARCHIVED
In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Application Object Library - Version: 11.5.10.2 and later   [Release: 11.5.10 and later ]
Information in this document applies to any platform.
Symptoms

On : 11.5.10.2 version, Application report sets, repport server, seeded reports

While trying to start concurrent managers getting the below error (PCP implemented):

ERROR
-----------------
Cause: cleanup_node failed due to ORA-01427: single-row subquery returns more than one row
ORA-06512: at "APPS.FND_CP_FNDSM", line 29
ORA-06512: at line 1.

The SQL statement being executed at the time o
Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library /mnt/oraarch1/arch1appl/fnd/11.5.0/bin/FNDLIBR.

Check that your system has enough resources to start a concurrent manager process. Contact your system administrat : 28-MAR-2011 05:36:25
Starting STANDARD Concurrent Manager : 28-MAR-2011 05:36:25
ORACLE error 1422 in get_sm_info
Cause

Need to clean tables and repopulate them with AutoConfig.
Solution

1 - Stop the concurrent managers , Forms server and Apache

2 - Run the following SQL statement :

EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

3 - Run AutoConfig.

Workflow Directory Services User/Role Validation' with parameters 10000, yes, yes

It is often experienced in Oracle Applications R12 that user has been assigned a responsibility but not able to see it while accessing it. However , everything seems fine with responsibility ,it is not end dated and looks good. To overcome this issue , there is a concurrent request that does sync up of such users and responsibiltity in R12 version.

Login as System Administrator --> Submit Concurrent Program ""Workflow Directory Services User/Role Validation' with parameters 10000, yes, yes

This request would check all users and assigned responsibilities and should sync up users with attached responsibilities , users should be able to view assigned responsibilty now.

Hope this would be helpful for you to address similar issue

Integrating Oracle Application Server with Oracle eBusiness Suite

Integrating Oracle Application Server with Oracle eBusiness Suite

We will provide some tips on how to integrate key features of Oracle 10gAS with the R12 EBS Applications suite.

First, we will provide a quick summary of the architecture that exists within Oracle E-Business Suite Release 12 in terms of Oracle 10gAS.

Oracle Metalink Note # 415007.1 is a FAQ that provides a useful summary of ways to integrate the Oracle R12 EBS Suite with Oracle 10g Application Server. Even though Oracle Weblogic is the heir apparent for future Application Server/Fusion Middleware directions as evidenced by the SOA suite products, fact is that Oracle 10g Application Server is the core middleware technology at work for Oracle R12 Applications E-Business Suite.

One key difference between a stand alone Oracle 10g Application Server environment versus R12 EBS is that the Oracle 10gAS Infrastructure is NOT SUPPORTED in the Release 12 of Oracle E-Business Suite for Oracle 12i Applications. Oracle Metalink Note 251627.1tells further details on how to workaround this issue.


Oracle Application Server 10g integration with Oracle EBS R12


Oracle EBS R12 uses many of the components that exist in Oracle 10g Application Server. For Oracle R12, these include:

- OHS- the Oracle HTTP Server (Apache 1.3)
- PL/SQL
- Forms Server
- Reports Server
- OC4J- Oracle Containers for J2EE


What versions of Oracle Application Server 10g can be used with Oracle E-Business Suite Release 12?


Oracle EB R12 has an Oracle 10g application server instance (10.1.2) that runs the Forms and Reports Services. In addition to this application server instance, we also have a secondary application server instance (10.1.3) which is used to run the OC4J instance. By default, when you install Oracle EBS R12, these 10gAS instances are installed and created by the R12 Rapid Install process.

Oracle R12 EBS supports the following Oracle Application Server 10g services:

Single Sign-On 10g (10.1.2.0.2)
Oracle Internet Directory 10g (10.1.2.0.2)
Portal 10g (10.1.4)
Discoverer 10g (10.1.2.0.2)
Web Cache 10g (10.1.2.0.2)
Oracle Business Integration 10g (10.1.3)
Oracle BPEL Process Manager 10g (10.1.3)

A complete discussion on how to setup and configure all of these products with EBS R12 would require a book in itself so I will kindly refer you to the Oracle EBS R12 Tech Stack Roadmap guide available in Oracle Metalink Note # 380482.1

Our main discussion will now shift to SSL and R12 EBS for Oracle 10g Application Server.

I have two homework assignments for you, besides the previous Metalink notes, I have another humble request. Either download or order the 29 DVDs for R12 EBS. Install a new test instance single tier for R12. You can do this at low cost with an external 250Gb portable drive, low cost laptop with 2-4Gb RAM, and VMWare. I built my R12 EBS test environment on Oracle Enterprise Linux for under $1000 and this included all softwares which are available from the Oracle Edelivery store (about $125 for non-commercial usage trial learning versions). Second, the following Metalink Notes are germane to our discussion for SSL, OID, and SSO with R12 EBS and Oracle 10g Application Server.

Note 376811.1, Integrating Oracle E-Business Suite Release 12 with Oracle Internet Directory and Oracle Single Sign-On

Note 403537.1, Best Practices for Securing Oracle E-Business Suite Release 12
Note 376694.1, Using the Oracle Wallet Manager Command Line Interface with Oracle E-Business Suite Release 12
Note 376700.1, Enabling SSL in Oracle Applications Release 12
Note 380490.1, Oracle E-Business Suite Release 12 Configuration in a DMZ

Since we already covered OID and SSO in great detail, for now, I will focus on SSL and R12 EBS for Oracle 10g Application Server.

SSL and Oracle 12i (Release 12) Applications

For us, the biggest change for using the Secure Sockets Layer (SSL) in terms of R12 and Oracle 10gAS is the use of mod_ossl module (plugin) for the Oracle HTTP Server (OHS). Both mod_ssl and mod_ossl enable strong cryptography for OHS (Apache 1.3). However, the key difference to note here is that mod_ossl is based on Oracle's implementation of SSL which supports SSL 3.

Oracle R12 EBS manages SSL certificates from the Oracle Wallet Manager (OWM) which we saw earlier in addition, we can also manage the wallets for SSL certificates with the new ORAPKI command line interface (CLI). The good news here for Oracle EBS, is that R12 now uses the Forms Listener Servlet, so we do not have a Forms server that existed in earlier releases of EBS such as 11i. This means that a separate certificate is no longer needed for Forms. Forms share the same wallet as the Oracle HTTP Server (OHS). However, I would like to point out an important caveat or gotcha. We no longer can use the Forms Server listener with the ConnectMode=https. This is no longer supported in Release 12i for Oracle E-Business Applications with R12. For R12 EBS, ConnectMode=https will only work with the JInitiator that contains the Oracle SSL libraries. Release 12 uses Sun's Java Plugin so in case you need to use https for the forms communication layer, it is imperative that you use the servlet architecture.

Middle Tier Configuration Steps to Enable SSL with Oracle R12 Applications

By default the location for the Oracle wallet in R12 is under $INST_TOP/certs/Apache. This directory contains a wallet with demo certificates. If you wish to use these certificates for testing start with Step 8 below to configure SSL, and then do Steps 1 through 7 when you are ready to switch to real certificates.

The demo certificates are not secure and should never be used in a production environment.

The main steps for setting up SSL on the Middle Tier are:

1. Set Your Environment.
2. Create a wallet.
3. Create a Certificate Request.
4. Submit the Certificate Request to a Certifying Authority.
5. Import your Server Certificate to the Wallet.
6. Copy the Apache Wallet to the OPMN Wallet.
7. Update the JDK Cacerts File.
8. Update the Context File.
9. Run Autoconfig.
10. Restart the middle tier services.

We will use the Oracle Wallet Manager (OWM) to configure SSL and certificates.
An optional method is to use the CLI (Command Line Interface) with Oracle Wallet Manager for R12. To do so, I refer you to Oracle Metalink Note# 376694.1: Using the Oracle Wallet Manager Command Line Interface in Release 12. Ready for some real fun? OK! Great, lets configure SSL for Oracle E-Business Release 12i.

Set the R12 Environment

Login as applmgr or the OS user who owns the Apps Tier for your Oracle 12i EBS environment.
Source the environment file

[applmgr@ebs scripts]$ whoami
applmgr
[applmgr@ebs scripts]$ cd $APPL_TOP
[applmgr@ebs appl]$ . APPSPROD_ebs.env
[applmgr@ebs appl]$


Create the Wallet

Navigate to $INST_TOP/certs/Apache directory to create the new wallet.

[applmgr@ebs appl]$ cd $INST_TOP/certs/Apache
[applmgr@ebs Apache]$ pwd
/d01/app/oracle/inst/apps/PROD_ebs/certs/Apache
[applmgr@ebs Apache]$ ls
cwallet.sso ewallet.p12

You should see the demo wallets that Rapid Install created after R12 was installed as shown above. Start the OWM (Oracle Wallet Manager) as shown in the figure example listed below.




Select the option Wallet-> New
It will prompt you with “Your default wallet directory doesn't exist. Do you wish to create it now?” Choose No.




The new wallet screen will now prompt you to enter a password for your wallet.
Enter the password




The new empty wallet is created. We do need to create the new certificate request so choose yes.



Common Name: is the name of your server including the domain.
Organizational Unit: (optional) The unit within your organization.
Organization: is the name of your organization.
Locality/City: is your locality or city.
State/Province: is the full name of your State or Province - do not abbreviate.

Select your Country from the drop down list.
Click OK.





Submit the Certificate Request to a Certifying Authority (CA)


We see our new wallet details below.



Now that we have our new wallet, we will need to export the Certificate Request and then submit it to a Certifying Authority (CA). Here are the steps.


Click on Certificate [Requested] to Highlight it.
From the menu click Operations -> Export Certificate Request



Save the file as server.csr
From the menu click Wallet and then click Save.



Change the Directory to the fully qualified wallet directory which in our case would be
/d01/app/oracle/inst/apps/PROD_ebs/certs/Apache


Click the OK button.


From the menu click Wallet and check the Auto Login box.



We will need to use the password whenever we open the wallet with Oracle Wallet Manager or if we ever need to perform operations on the wallet. Auto login allows for simplified maintenance tasks in the future.

Exit out of OWM

Our wallet directory now contains the following files:

[applmgr@ebs Apache]$ whoami;pwd;ls
applmgr
/d01/app/oracle/inst/apps/PROD_ebs/certs/Apache
cwallet.sso ewallet.p12 server.csr



Import Server Certificate to the Wallet.


Once we have received our Server Certificate the Certifying Authority (CA) we need to import it into our wallet. We copy the certificate to server.crt in the wallet directory by either using ftp or copy and paste into a server.crt file.
We use OWM (Oracle Wallet Manager) to perform this task.


Modify Wallet for Oracle Process and Notification Manager (OPMN)


Oracle Applications R12 Rapid Install creates a default "demo" type opmn wallet in the $INST_TOP/certs/opmn directory that is available for basic SSL testing. Now that the Apache wallet has been created for us, we need to to use these same certificates for opmn. We need to perform the the following steps to backup and copy the wallets:

Navigate to the $INST_TOP/certs/opmn directory.
Create a new directory named BAK
Move the ewallet.p12 and cwallet.sso files to the BAK directory just created.
Copy the ewallet.p12 and cwallet.sso files from the $INST_TOP/certs/Apache directory to the $INST_TOP/certs/opmn directory.


[applmgr@ebs Apache]$ cd $INST_TOP/certs/opmn
[applmgr@ebs opmn]$ mkdir BAK
[applmgr@ebs opmn]$ cp /d01/app/oracle/inst/apps/PROD_ebs/certs/Apache/e* cw* $INST_TOP/certs/opmn/BAK

[applmgr@ebs BAK]$ pwd
/d01/app/oracle/inst/apps/PROD_ebs/certs/opmn/BAK
[applmgr@ebs BAK]$ ls
cwallet.sso ewallet.p12


Make SSL Updates for R12 EBS Context File.



Now we need to update our R12 EBS Context file variables to account for SSL configuration.
To do so, we need to use the Oracle Applications Manager (OAM) Context Editor to change the SSL related variables as shown in the following screenshots.



We choose AutoConfig under System Configuration on the Oracle 12i Applications Manager (OAM) interface to access our context files.



Now we need to edit the Context file for Apps Tier to enable SSL for R12 E-Business environment.
Change variables for s_url_protocol from http to https for SSL and make sure to set the s_webssl_port to 4433 which is the default SSL port. Below is an example.





Finalize changes for R12 EBS and SSL Configuration - Autoconfig


Now we need to run the Autoconfig utility to have our changes made to our R12 context file for SSL. We do this by executing the adautocfg.sh script located in the Middle Tier (Apps Tier) under the $ADMIN_SCRIPTS_HOME directory.

$ cd $ADMIN_SCRIPTS_HOME
[applmgr@ebs scripts]$ ls
adalnctl.sh adcmctl.sh adformsrvctl.sh adopmnctl.sh adstrtal.sh java.sh L308828.log sqlnet.log
adapcctl.sh adexecsql.pl adoacorectl.sh adpreclone.pl gsmstart.sh jtffmctl.sh mwactl.sh
adautocfg.sh adformsctl.sh adoafmctl.sh adstpall.sh ieo L308827.log mwactlwrpr.sh

$ adautocfg.sh
Enter the APPS user password:

The log file for this session is located at: /d01/app/oracle/inst/apps/PROD_ebs/admin/log/05222339/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /d01/app/oracle/inst/apps/PROD_ebs
Classpath : /d01/app/oracle/apps/apps_st/comn/java/lib/appsborg2.zip:/d01/app/oracle/apps/apps_st/comn/java/classes

Using Context file : /d01/app/oracle/inst/apps/PROD_ebs/appl/admin/PROD_ebs.xml

Context Value Management will now update the Context file
The log file for this session is located at: /d01/app/oracle/inst/apps/PROD_ebs/admin/log/05222339/adconfig.log

AutoConfig is configuring the Applications environment...

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
Configuring BIS_TOP.......COMPLETED
Configuring AMS_TOP.......COMPLETED
Configuring CCT_TOP.......COMPLETED
Configuring WSH_TOP.......COMPLETED
Configuring CLN_TOP.......COMPLETED
Configuring OKE_TOP.......COMPLETED
Configuring OKL_TOP.......COMPLETED
Configuring OKS_TOP.......COMPLETED
Configuring CSF_TOP.......COMPLETED
Configuring IGS_TOP.......COMPLETED
Configuring IBY_TOP.......COMPLETED
Configuring JTF_TOP.......COMPLETED
Configuring MWA_TOP.......COMPLETED
Configuring CN_TOP........COMPLETED
Configuring CSI_TOP.......COMPLETED
Configuring WIP_TOP.......COMPLETED
Configuring CSE_TOP.......COMPLETED
Configuring EAM_TOP.......COMPLETED
Configuring FTE_TOP.......COMPLETED
Configuring ONT_TOP.......COMPLETED
Configuring AR_TOP........COMPLETED
Configuring AHL_TOP.......COMPLETED
Configuring OZF_TOP.......COMPLETED
Configuring IES_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.
[applmgr@ebs scripts]$


Restart Apps R12 EBS middle tier services.


Our final step on the road to SSL for Oracle Release 12 for the E-Business Suite is to stop and restart the middle tier services. We use the adapcctl.sh script located in the $ADMIN_SCRIPTS_HOME directory to stop and restart the middle tier Apache services.

[applmgr@ebs scripts]$ adapcctl.sh stop

You are running adapcctl.sh version 120.6.12000000.2

Stopping OPMN managed Oracle HTTP Server (OHS) instance ...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /d01/app/oracle/inst/apps/PROD_ebs/logs/appl/admin/log/adapcctl.txt for more information ...

[applmgr@ebs scripts]$ adapcctl.sh start

You are running adapcctl.sh version 120.6.12000000.2

Starting OPMN managed Oracle HTTP Server (OHS) instance ...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /d01/app/oracle/inst/apps/PROD_ebs/logs/appl/admin/log/adapcctl.txt for more information ...

[applmgr@ebs scripts]$ adapcctl.sh status

You are running adapcctl.sh version 120.6.12000000.2

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: PROD_ebs.ebs.sandiego.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 8269 | Alive
OC4J | forms | 8267 | Alive
OC4J | oacore | 8268 | Alive
HTTP_Server | HTTP_Server | 21490 | Alive


adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /d01/app/oracle/inst/apps/PROD_ebs/logs/appl/admin/log/adapcctl.txt for more information ...


There are of course many advanced options for SSL configuration beyond the scope of what we have covered today. I encourage you to dig deeper into the above reference Metalink notes and test to find out what SSL options will benefit your Oracle E-Business Release 12 environments. As you can now see, SSL configuration requires different steps for the E-Business Suite than for a stand alone Oracle 10g Application Server environment. Until next time where we provide some troubleshooting tips on Oracle 10g Application Server.

Prequisite Checks for Display Fail Installing Oracle Application Server 10g [ID 304594.1]

Prequisite Checks for Display Fail Installing Oracle Application Server 10g [ID 304594.1]
		Modified 11-MAY-2009     Type PROBLEM     Status PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References

---

Applies to:

Oracle Application Server 10g Enterprise Edition - Version: 9.0.4.0.0 to 10.1.3.3.0
This problem can occur on any platform.
Checked for relevance on 11-May-2009

Symptoms

• Installing Oracle Application Server 10g
• During the install, prerequisite checks fail with:

Checking monitor: must be configured to display at least 256 colors
Failed
Could not execute auto check for display colors using command /usr/openwin/bin/xdpyinfo

Cause

• Unpublished Bug 3177021  ERROR MESSAGE IS NOT PROPER IF XHOST + NOT RUN
  
• xhost + has not been run as root
  
• If this command is not run then any other user cannot connect to the display, hence an oracle user trying to do an install will fail to connect to the display doing the prerequisite checks

Solution

To implement the solution, execute the following steps:

1. Log on to the server as the root user
2. Execute: xhost +
3. As the oracle user, run the installer again and the prerequisite display check will run.

References

---

Related Products Middleware > Application Servers > Oracle Application Server > Oracle Fusion Middleware Keywords INSTALLATION FAILS

Back to top

Concurrent Request ORA-20100 errors in the request logs

Concurrent Processing - Troubleshooting Concurrent Request ORA-20100 errors in the request logs [ID 261693.1]
 
Applies to:

Oracle Application Object Library - Version: 11.5.10.2 to 12.0.6 - Release: 11.5 to 12
Oracle Concurrent Processing - Version: 11.5.10.0 to 12.1.3   [Release: 11.5 to 12.1]
Information in this document applies to any platform.
Checked for relevance on 05-DEC-2010
Purpose

Provide hints for application administrators and DBAs on how to troubleshoot ORA-20100 when running PL/SQL programs on Apps Concurrent Processing.
Last Review Date

MSG-00102: Error Message :ORA-20100: File o0000071.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 378
ORA-06512: at "APPS.FND_FILE", line 473
ORA-06512: at "APPS.AP_TRIAL_BALANCE_PKG", line 192
REP-1419: 'beforereport': PL/SQL program aborted.


ORACLE error 20100 in FDPSTP
Cause: FDPSTP failed due to ORA-20100: File o0036176.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 410
ORA-06512

NOTE: Applications also produces temporary PL/SQL output files used in concurrent processing. These files are written to a location on the database server node specified by the APPLPTMP environment setting. The APPLPTMP directory must be the same directory as specified by the utl_file_dir parameter in your database initialization file.

Rapid Install sets both APPLPTMP and the utl_file_dir parameter to the same default directory. As the temporary files placed in this directory may contain context sensitive information, it should be a secure directory on the database server node with read and write access for the database server owner. In a multi-node system, the directory defined by APPLPTMP does not need to exist on the application tier servers. During an upgrade with AutoUpgrade, you must provide the utl_file_dir parameter value for the APPLPTMP environment setting.


To isolate where the problem is, verify the following:

1) Make sure your environment is properly sourced.

For example, if you don't have your database environment sourced properly, you will be missing ORA_NLSxx, that is used to indicate where Oracle RDBMS/client software can locate the
defintions of Charactersets (used in NLS_LANG or as NLS_CHARACTERSET/
NLS_NCHAR_CHARACTERSET),NLS_SORT, NLS_LANGUAGE (or derived/related parameters)
or NLS_TERRITORY (or derived/related parameters).

ORA_NLS10 missing causes a non recognition of the usual chr(10) as a new line.

2) Make sure that the name of the file is valid (the file name should not include characters like "^").

3) Make sure that APPLPTMP is set to a valid directory and that BOTH the applmgr user and the database user have read and write permissions on that directory (normally, it can be set to the same directory as APPLPTMP).

Make sure to run autoconfig if you happen to modify APPLPTMP in the  autoconfig context file.

If you have a RAC-enabled environment, make sure you are not missing any mount points you may have pointing to APPLPTMP/APPLTMP

4) Make sure the directory pointed by APPLPTMP is the first entry on the utl_file_dir. Also, verify that all the entries on the utl_file_dir are valid and that the applmgr has read/write permissions.

If using an spfile, verify the proper syntax to set utl_file_dir:

Ex.
    ALTER SYSTEM SET UTL_FILE_DIR='directory1','directory2' scope=spfile;

5) Check if you can write a file directly using FND_FILE, which is the package used by the Application. From sqlplus, connected as the apps user, run:
    SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
This should dump a file on APPLPTMP.

If this test works, it would indicate that FND_FILE is working fine and the problem is possibly with the Application.

You may want to leave only one entry on utl_file_dir for this test.

FND_FILE uses the UTL_FILE package, which can only write to directories specified in init.ora. APPLPTMP is a special directory used only for PLSQL temporary files. When a concurrent program uses the FND_FILE package, the concurrent manager uses APPLPTMP as the directory to which it writes temporary files. Thus, APPLPTMP must be one of the directories listed in init.ora in order for FND_FILE to work.

6) Make sure that the file does not exit on the directory pointed by APPLPTMP.

This issue may occur if the temporary file being created by the concurrent program already exists in that directory and that already existing file is owned by another user, therefore it cannot override it. For example, you have a cloned instance, and the APPLPTMP directory still keeps the temp files that were created in the source instance.

Verify the file stated in the error message you are getting (for the example above File o0000071.tmp) does not exist in the directory pointed by $APPLPTMP. If it does, do the following:

1. Stop all services. This is to make sure no user is logged on and making use of $APPLPTMP
2. Remove all files from directory pointed by $APPLPTMP
3. Restart all services
4. Re-test the failed process

7) If still having problems, check if you can write a file using UTL_FILE, which is used by FND_FILE.

Run the PL/SQL below,  after changing to the first entry on utl_file_dir (you may want to leave just one entry on utl_file_dir for this test).
set serveroutput on
DECLARE
  file_location VARCHAR2(256) := ''; 
  file_name VARCHAR2(256) := 'utlfile1.lst';
  file_text VARCHAR2(256) := 'THIS IS A TEST';
  file_id UTL_FILE.file_type;
BEGIN
  file_id := UTL_FILE.fopen(file_Location, file_name, 'W');
  UTL_FILE.put_line(file_id, file_text);
  UTL_FILE.fclose(file_id);
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH
  THEN dbms_output.put_line('Invalid path ' || SQLERRM);
    WHEN OTHERS
  THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM);
END;
/
This program should dump a file on the requested directory. If the test fails, the problem is probably on the Database side.

If it works, the problem is probably on FND_FILE. In this scenario, check the versions of AFCPPIOS.pls and AFCPPIOB.pls.

8) If still having problems, go to your temporary files directory (i.e. $APPLPTMP) and check is not full. If it is, do the following:

    1. Shutdown all the concurrent processes and managers.
    2. Verify that no FNDLIBR process is running on (ps -ef | grep FNDLIBR).
    3. Delete all the files in the temporary directory.
    4. Run the CMCLEAN.SQL script from Note 134007.1 (don't forget to commit)
    5. Run the concurrent program "Purge Concurrent Request and/or Manager Data" for the Entry ="All" and mode ="Age"
    6. Test the issue.
    The mode value for the concurrent program "Purge Concurrent Request and/or Manager Data" can be the number of days before which the logs will be purged.
    7. Make sure to verify UTL_FILE_DIR and APPLPTMP are set to same directory.

9) If still having problems, your SPFILE may be corrupt. Do the following to recreate your SPFILE:
    1. Login to the database server using oraprod user

    2. Create a new PFILE (aka init.ora file) from the current SPFILE
    2.1. cd $ORACLE_HOME/dbs
    2.2. sqlplus " / as sysdba"
    2.3. SQL> create pfile from spfile
    2.4. Make sure that there is a file with name "init.ora" has been created today

    3. Edit "init.ora" file and change the value of the utl_file_dir parameter to a readable/writable directory (i.e. $APPLPTMP, "/usr/tmp")

    4. Either rename the original spfile in order to startup the database with the new pfile or create a new spfile for pfile.

    8. Restart the database

Concurrent Manager Not Running, Manager Status Column Shows 'System Hold Fix Manager before resetting counters

Concurrent Manager Not Running, Manager Status Column Shows 'System Hold Fix Manager before resetting counters

CM is not running and you will get the below messages:

Concurrent Manager Not Running, Manager Status Column Shows 'System Hold Fix Manager before resetting counters






Resolution:

Shutdown apps on all nodes and make sure no FNDLIBR process are running.


Shutdown and start the databases so that you have a clean env.

run the cmclean.sql as per the doc:

Article- ID : 134007.1

start all your apps now:



All my CM are UP now. Happy day..







Note:- I didn't use adrelink.sh since I have not made any changes to the Application tier or oracle tech stack.