Modern Oracle DBA

Long Running Query Copy SELECT fcr.request_id, fcr.parent_request_id, ftp.user_concurrent_program_name, fcu.user_name, fcu.description User_Detail, fcr.phase_code phase, floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) || ' Hrs ' || floor((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) - floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600) / 60) || ' Mins ' || round((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) - floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600 - (floor((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) - floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60))) || ' Secs ' "RUNNING_SINCE", to_char(FCR.requested_start_date, ' DAY DD-MON-YY HH:MI AM') REQUESTED_START_DATE, to_char(FCR.actual_start_date, ' DAY DD-MON-YY HH:MI AM') ...

  Check the status and process id for the concurrent request select oracle_process_id ,decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G','Warning','T','Terminating')"Status_code",phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time" from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'   Collect SID and serial# from process id select s.sid,s.serial#,module,s.status from v$session s,v$process p where s.paddr=p.addr and p.spid=&oracle_process_id   Backup from fnd_consurrent_requests table and run the update command and please make sure database session for the request id is killed SQL> update apps.fnd_concurrent_requests set status_code='X',Phase_code='C' where request_id=’1234567’; 1 row ...

Concurrent programs log and output files are maintenance polices in OS level When we plan to start maintenance activity for those log files the purge concurrent request program come into picture based on the information Purge concurrent request using below tables. FND_CONCURRENT_REQUESTS This table contains a complete history of all concurrent requests. This table should ideally be kept at around4 - 5k records and should be regularly purged as part of normal 'housekeeping' by running the standard FNDCPPUR program. FND_RUN_REQUESTS When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report. FND_CONC_REQUEST_ARGUMENTS This table records arguments passed by the concurrent. FND_DUAL This table records when requests do not update FND_CONCURRENT_PROCESSES This table records information about Oracle Applications and operating system processes. FND_CONC_STAT_LIST This table col...

Concurrent manager process flow FNDSM is initiate the concurrent manager in OS processer. Then start the database session and read the settings. Update the fnd_concurrent_processes and fnd_concurrent_queues tables Build SQL for quiring the request queue. Execute the sql to request concurrent manager. Checking for Sleep time seconds, Pending requests Checking the “Cache size” number of requests into the execution cache. Execute the request from the execution Queue. Update the status code of the concurrent request. Execute the request and update the status code. If Concurrent Managers are not starting Check Concurrent Manager log files Click here Check the database alert log for any space issue. Check the application tier filesystem and Middle tier file system space and /tmp directory space. Check application listner Running/Not adalnctl.sh status Run the adcmctl script with debug option will get some idea where its failed sh -x adcmctl status apps/appspw Check if FND...

We frequently encounter issues with concurrent programs running long and concurrent requests not picking up jobs. These problems are often expected from the development and functional team we need to address this issue To solve this, we should take the following steps: Collect below basis level of information to development team 1 Oracle seeded program/Custom program? 2 How much time it used earlier? 3 Is there any recent code change done in concurrent program? 4 Is this program fetching higher data compare to last run? 5 Does this job running any specific time/ It can be run any time? 6 Does this job fetching data using DB link? 7 Does the problem happen on both the test and production instance?   Troubleshooting Steps for Concurrent Requests Check for Locks and Blocking sessions Identify if the request is running but blocked by database locks/Blocking sessions. Use DBA tools or queries to identify and release locks if necessary. Review Parameters and Data ...

Concurrent Processing using the Output Post Processor (OPP) to enforce post-processing actions for concurrent requests. Post-processing actions are actions taken on concurrent request output. Example of a post-processing action is that used in Concurrent Processing support of XML Publisher. If a request is submitted with an XML Publisher template specified as a layout for the concurrent request output, then after the concurrent manager finishes running the concurrent program, it will contact the OPP to apply the XML Publisher template and create the final output. Tuning for Output post processor 1.Increase the number of Output Post Processor as follows:  Logon to Applications with "System Administrator" responsibility  Navigate to Concurrent -> Manager -> Define  Query for "Output Post Processor"  Click on "Work Shifts" and Increase the number of processes. (If you have 2 processes then make them 4 if require...

Concurrent manager log file location: Concurrent manager log files are located under $APPLCSF and $APPSLOG home [oracle@VIS ~]$cd $APPLCSF [oracle@VIS conc]$pwd /apps/applmgr/fs_ne/inst/VIS/logs/appl/conc [oracle@VIS conc]$cd log/ [oracle@VIS log]$pwd /apps/applmgr/fs_ne/inst/VIS/logs/appl/conc/log [oracle@VIS log]$ Internal concurrent manager log file: [oracle@VIS log]$ls -lrt *$TWO_TASK* Standard manager log file: [oracle@VIS log]$ls -lrt w*.mgr Conflict resolution manager log file: [oracle@VIS log]$ ls -lrt c*.mgr Output post processer log file: [oracle@VIS log]$ps -ef |grep OPP Concurrent program related some basic SQL queries How to find concurrent program status? SQL> select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from apps.fnd_concurrent_requests where request_id=’1234567’; How to find concurrent request output and log file location? SQL> select LOGFILE_NAME, OUTFILE_NAME from fnd_concurrent_requests where request_id...

fnd_conc_clone.setup_clean : fnd_conc_clone is the package name setup_clean is procedure name After clone we must need to run the fnd_conc_clone.setup_clean script It will be clear the below tables in target node information in to source node table fnd_concurrent_queue_size fnd_concurrent_queues_tl fnd_concurrent_queue_size fnd_concurrent_queues fnd_nodes Syntax: $sqlplus apps/<apps_password> SQL> exec fnd_conc_clone.setup_clean; PL/SQL procedure successfully completed. SQL> commit; Commit complete. Once the script was executed must run the autoconfig in DB and Middle-Tier   Cmclean.sql We use the cmclean.sql scrip clean the running and pending requests Down the concurrent manager and check if any concurrent program running/not(ps- ef |grep FNDLIBR, ps- ef |grep FNDOPP) Cmclean.sql script is not recommended by oracle. R12 will user cpadmin.sh and  concurrent manager recovery wizard. It wil...