Showing posts with label Concurrent Managers. Show all posts
Showing posts with label Concurrent Managers. Show all posts

Sunday, May 15, 2011

Configure Oracle Parallel Concurrent Processing ( Oracle PCP )

Configure Parallel Concurrent Processing

3.1.1 Check prerequisites for setting up Parallel Concurrent Processing


To set up Parallel Concurrent Processing (PCP), you must have more than one Concurrent Processing node in your environment

3.1.2 Set Up PCP


  1. Edit the applications context file via Oracle Applications Manager, and set the value of the variable APPLDCP to ON.
  2. Execute AutoConfig by running the following command on all concurrent processing nodes:

$ $INST_TOP/admin/scripts/adautocfg.sh

  1. Source the Applications environment.
  2. Check the tnsnames.ora and listener.ora configuration files, located in $INST_TOP/ora/10.1.2/network/admin. Ensure that the required FNDSM and FNDFS entries are present for all other concurrent nodes.
  3. Restart the Applications listener processes on each application tier node.
  4. Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator Responsibility. Navigate to Install > Nodes screen, and ensure that each node in the cluster is registered.
  5. Verify that the Internal Monitor for each node is defined properly, with correct primary and secondary node specification, and work shift details. For example, Internal Monitor: Host2 must have primary node as host2 and secondary node as host3. Also ensure that the Internal Monitor manager is activated: this can be done from Concurrent > Manager > Administrator.
  6. Set the $APPLCSF environment variable on all the Concurrent Processing nodes to point to a log directory on a shared file system.
  7. Set the $APPLPTMP environment variable on all the CP nodes to the value of the UTL_FILE_DIR entry in init.ora on the database nodes. (This value should be pointing to a directory on a shared file system.)
  8. Set profile option 'Concurrent: PCP Instance Check' to OFF if database instance-sensitive failover is not required. By setting it to 'ON', a concurrent manager will fail over to a secondary Application tier node if the database instance to which it is connected becomes unavailable for some reason.


3.1.3 Set Up Transaction Managers ,Internal Concurrent Manager ,Standard Manager


  1. Shut down the application services (servers) on all nodes
  2. Shut down all the database instances cleanly in the Oracle RAC environment, using the command:

SQL>shutdown immediate;

  1. Edit $ORACLE_HOME/dbs/_ifile.ora. Add the following parameters:

      • _lm_global_posts=TRUE
      • _immediate_commit_propagation=TRUE

  1. Start the instances on all database nodes, one by one.
  2. Start up the application services (servers) on all nodes.
  3. Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator responsibility. Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE', and verify that the transaction manager works across the Oracle RAC instance.
  4. Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.
  5. Restart the concurrent managers.
  6. If any of the transaction managers are in deactivated status, activate them from Concurrent > Manager > Administrator.
Transaction Manager (Internal use only)

Standard Manager

Internal Concurrent Manager

Internal Monitor for Apps-1


Internal Monitor for Apps-2

CRP Manager


3.1.4 Set Up Load Balancing on Concurrent Processing Nodes


  1. Edit the applications context file through the Oracle Applications Manager interface, and set the value of Concurrent Manager TWO_TASK (s_cp_twotask) to the load balancing alias (OURPROD_balance>).

Note: Windows users must set the value of "Concurrent Manager TWO_TASK" (s_cp_twotask context variable) to the instance alias.

  1. Execute AutoConfig by running $INST_TOP/admin/scripts/adautocfg.sh on all concurrent nodes.

Example:-

Then restart your application tier it will work .


Sunday, March 27, 2011

Analysis of a concurrent request

---------------------------------------------------------------------------------------- -- -- analyzereq.sql -- Analyze a concurrent request -- -- USAGE: sqlplus apps_user/apps_passwd @analyzereq request_id -- EX: sqlplus apps/apps @analyzereq 304504 -- -- -- $Id: analyzereq.sql,v 1.2 2000/04/03 04:18:04 pferguso Exp $ -- -- $Log: analyzereq.sql,v $ -- Revision 1.2 2000/04/03 04:18:04 pferguso -- added print_mgrs, more request info -- -- Revision 1.1.1.1 2000/02/23 22:00:36 pferguso -- initial import into CVS -- -- Revision 1.3 1999-12-29 13:46:44-05 pferguso -- added responsibility info, logfile names -- -- Revision 1.2 1999-12-29 13:13:58-05 pferguso -- first usable version -- -- Revision 1.1 1999-10-19 18:04:23-04 pferguso -- Initial revision -- -- -- ---------------------------------------------------------------------------------------- set serveroutput on set feedback off set verify off set heading off set timing off variable help_text varchar2(2000); prompt DECLARE req_id number(15) := &1; FUNCTION get_status(p_status_code varchar2) return varchar2 AS c_status fnd_lookups.meaning%TYPE; BEGIN SELECT nvl(meaning, 'UNKNOWN') INTO c_status FROM fnd_lookups WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND LOOKUP_CODE = p_status_code; return rtrim(c_status); END get_status; FUNCTION get_phase(p_phase_code varchar2) return varchar2 AS c_phase fnd_lookups.meaning%TYPE; BEGIN SELECT nvl(meaning, 'UNKNOWN') INTO c_phase FROM fnd_lookups WHERE LOOKUP_TYPE = 'CP_PHASE_CODE' AND LOOKUP_CODE = p_phase_code; return rtrim(c_phase); END get_phase; PROCEDURE manager_check (req_id in number, cd_id in number, mgr_defined out boolean, mgr_active out boolean, mgr_workshift out boolean, mgr_running out boolean, run_alone out boolean) is cursor mgr_cursor (rid number) is select running_processes, max_processes, decode(control_code, 'T','N', -- Abort 'X','N', -- Aborted 'D','N', -- Deactivate 'E','N', -- Deactivated 'Y') active from fnd_concurrent_worker_requests where request_id = rid and not((queue_application_id = 0) and (concurrent_queue_id in (1,4))); run_alone_flag varchar2(1); begin mgr_defined := FALSE; mgr_active := FALSE; mgr_workshift := FALSE; mgr_running := FALSE; for mgr_rec in mgr_cursor(req_id) loop mgr_defined := TRUE; if (mgr_rec.active = 'Y') then mgr_active := TRUE; if (mgr_rec.max_processes > 0) then mgr_workshift := TRUE; end if; if (mgr_rec.running_processes > 0) then mgr_running := TRUE; end if; end if; end loop; if (cd_id is null) then run_alone_flag := 'N'; else select runalone_flag into run_alone_flag from fnd_conflicts_domain d where d.cd_id = manager_check.cd_id; end if; if (run_alone_flag = 'Y') then run_alone := TRUE; else run_alone := FALSE; end if; end manager_check; PROCEDURE print_mgrs(p_req_id number) AS CURSOR c_mgrs(rid number) IS SELECT user_concurrent_queue_name name, fcwr.running_processes active, decode(fcwr.control_code, 'A', fl.meaning, 'D', fl.meaning, 'E', fl.meaning, 'N', fl.meaning, 'R', fl.meaning, 'T', fl.meaning, 'U', fl.meaning, 'V', fl.meaning, 'X', fl.meaning, NULL, 'Running', '** Unknown Status **') status FROM fnd_concurrent_queues_vl fcqv, fnd_concurrent_worker_requests fcwr, fnd_lookups fl WHERE fcwr.request_id = rid AND fcwr.concurrent_queue_id = fcqv.concurrent_queue_id AND fcwr.concurrent_queue_id not in (1, 4) AND fl.lookup_code (+) = fcwr.control_code AND fl.lookup_type (+) = 'CP_CONTROL_CODE'; BEGIN for mgr_rec in c_mgrs(p_req_id) loop DBMS_OUTPUT.PUT_LINE('- ' mgr_rec.name ' Status: ' mgr_rec.status ' (' mgr_rec.active ' active processes)'); end loop; END print_mgrs; PROCEDURE analyze_request(p_req_id number) AS reqinfo fnd_concurrent_requests%ROWTYPE; proginfo fnd_concurrent_programs_vl%ROWTYPE; c_status fnd_lookups.meaning%TYPE; m_buf fnd_lookups.meaning%TYPE; conc_prog_name fnd_concurrent_programs.concurrent_program_name%TYPE; exe_method_code fnd_concurrent_programs_vl.execution_method_code%TYPE; conc_app_name fnd_application_vl.application_name%TYPE; tmp_id number(15); tmp_status fnd_concurrent_requests.status_code%TYPE; tmp_date date; conc_app_id fnd_concurrent_requests.program_application_id%TYPE; conc_id fnd_concurrent_requests.concurrent_program_id%TYPE; conc_cd_id fnd_concurrent_requests.cd_id%TYPE; v_enabled_flag fnd_concurrent_programs.enabled_flag%TYPE; conflict_domain fnd_conflicts_domain.user_cd_name%TYPE; parent_id number(15); resp_name varchar2(100); rclass_name fnd_concurrent_request_class.request_class_name%TYPE; exe_file_name fnd_executables.execution_file_name%TYPE; c_user fnd_user.user_name%TYPE; last_user fnd_user.user_name%TYPE; fcd_phase varchar2(48); fcd_status varchar2(48); traid fnd_concurrent_requests.program_application_id%TYPE; trcpid fnd_concurrent_requests.concurrent_program_id%TYPE; icount number; ireqid fnd_concurrent_requests.request_id%TYPE; pcode fnd_concurrent_requests.phase_code%TYPE; scode fnd_concurrent_requests.status_code%TYPE; live_child boolean; mgr_defined boolean; mgr_active boolean; mgr_workshift boolean; mgr_running boolean; run_alone boolean; reqlimit boolean := false; mgrname fnd_concurrent_queues_vl.user_concurrent_queue_name%TYPE; filename varchar2(255); qcf fnd_concurrent_programs.queue_control_flag%TYPE; apps_version varchar2(3); sep varchar2(200) := '------------------------------------------------------'; REQ_NOTFOUND exception; CURSOR c_wait IS SELECT request_id, phase_code, status_code FROM fnd_concurrent_requests WHERE parent_request_id = p_req_id; CURSOR c_inc IS SELECT to_run_application_id, to_run_concurrent_program_id FROM fnd_concurrent_program_serial WHERE running_application_id = conc_app_id AND running_concurrent_program_id = conc_id; CURSOR c_ireqs IS SELECT request_id, phase_code, status_code FROM fnd_concurrent_requests WHERE phase_code = 'R' AND program_application_id = traid AND concurrent_program_id = trcpid AND cd_id = conc_cd_id; CURSOR c_userreqs(uid number, s date) IS SELECT request_id, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') start_date, phase_code, status_code FROM fnd_concurrent_requests WHERE phase_code IN ('R', 'P') AND requested_by = uid AND requested_start_date < s AND hold_flag = 'N'; BEGIN BEGIN SELECT * INTO reqinfo FROM fnd_concurrent_requests WHERE request_id = p_req_id; EXCEPTION WHEN NO_DATA_FOUND THEN raise REQ_NOTFOUND; END; DBMS_OUTPUT.PUT_LINE('Analyzing request 'req_id':'); DBMS_OUTPUT.PUT_LINE(sep); -- Program information DBMS_OUTPUT.PUT_LINE('Program information:'); SELECT fvl.* INTO proginfo FROM fnd_concurrent_programs_vl fvl, fnd_concurrent_requests fcr WHERE fcr.request_id = p_req_id AND fcr.concurrent_program_id = fvl.concurrent_program_id AND fcr.program_application_id = fvl.application_id; DBMS_OUTPUT.PUT_LINE('Program: ' proginfo.user_concurrent_program_name ' (' proginfo.concurrent_program_name ')'); SELECT nvl(application_name, '-- UNKNOWN APPLICATION --') INTO conc_app_name FROM fnd_application_vl fvl, fnd_concurrent_requests fcr WHERE fcr.request_id = p_req_id AND fcr.program_application_id = fvl.application_id; DBMS_OUTPUT.PUT_LINE('Application: 'conc_app_name); SELECT nvl(meaning, 'UNKNOWN') INTO m_buf FROM fnd_lookups WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE' AND lookup_code = proginfo.execution_method_code; SELECT nvl(execution_file_name, 'NONE') INTO exe_file_name FROM fnd_executables WHERE application_id = proginfo.executable_application_id AND executable_id = proginfo.executable_id; DBMS_OUTPUT.PUT_LINE('Executable type: ' m_buf ' (' proginfo.execution_method_code ')'); DBMS_OUTPUT.PUT_LINE('Executable file name or procedure: ' exe_file_name); DBMS_OUTPUT.PUT_LINE('Run alone flag: ' proginfo.run_alone_flag); DBMS_OUTPUT.PUT_LINE('SRS flag: ' proginfo.srs_flag); DBMS_OUTPUT.PUT_LINE('NLS compliant: ' proginfo.nls_compliant); DBMS_OUTPUT.PUT_LINE('Output file type: ' proginfo.output_file_type); if proginfo.concurrent_class_id is not null then select request_class_name into rclass_name from fnd_concurrent_request_class where application_id = proginfo.class_application_id and request_class_id = proginfo.concurrent_class_id; DBMS_OUTPUT.PUT_LINE('Request type: ' rclass_name); end if; if proginfo.execution_options is not null then DBMS_OUTPUT.PUT_LINE('Execution options: ' proginfo.execution_options); end if; if proginfo.enable_trace = 'Y' then DBMS_OUTPUT.PUT_LINE('SQL Trace has been enabled for this program.'); end if; DBMS_OUTPUT.PUT_LINE(sep); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(sep); -- Submission information DBMS_OUTPUT.PUT_LINE('Submission information:'); begin SELECT user_name into c_user from fnd_user where user_id = reqinfo.requested_by; exception when no_data_found then c_user := '-- UNKNOWN USER --'; end; begin SELECT user_name into last_user from fnd_user WHERE user_id = reqinfo.last_updated_by; exception when no_data_found then last_user := '-- UNKNOWN USER --'; end; DBMS_OUTPUT.PUT_LINE('It was submitted by user: 'c_user); SELECT responsibility_name INTO resp_name FROM fnd_responsibility_vl WHERE responsibility_id = reqinfo.responsibility_id AND application_id = reqinfo.responsibility_application_id; DBMS_OUTPUT.PUT_LINE('Using responsibility: ' resp_name); DBMS_OUTPUT.PUT_LINE('It was submitted on: ' to_char(reqinfo.request_date, 'DD-MON-RR HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('It was requested to start on: ' to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('Parent request id: ' reqinfo.parent_request_id); DBMS_OUTPUT.PUT_LINE('Language: ' reqinfo.nls_language); DBMS_OUTPUT.PUT_LINE('Territory: ' reqinfo.nls_territory); DBMS_OUTPUT.PUT_LINE('Priority: ' to_char(reqinfo.priority)); DBMS_OUTPUT.PUT_LINE('Arguments (' reqinfo.number_of_arguments '): ' reqinfo.argument_text); c_status := get_status(reqinfo.status_code); DBMS_OUTPUT.PUT_LINE(sep); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(sep); -- Analysis DBMS_OUTPUT.PUT_LINE('Analysis:'); -- Completed Requests ------------------------------------------------------------------------------------------------------------- IF reqinfo.phase_code = 'C' THEN DBMS_OUTPUT.PUT_LINE('Request 'p_req_id' has completed with status "'c_status'".'); DBMS_OUTPUT.PUT_LINE('It began running on: ' nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --')); DBMS_OUTPUT.PUT_LINE('It completed on: ' nvl(to_char(reqinfo.actual_completion_date, 'DD-MON-RR HH24:MI:SS'), '-- NO COMPLETION DATE --')); BEGIN SELECT user_concurrent_queue_name INTO mgrname FROM fnd_concurrent_queues_vl WHERE concurrent_queue_id = reqinfo.controlling_manager; DBMS_OUTPUT.PUT_LINE('It was run by manager: ' mgrname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('It was run by an unknown manager.'); END; SELECT nvl(reqinfo.logfile_name, '-- No logfile --') INTO filename FROM dual; DBMS_OUTPUT.PUT_LINE('Logfile: ' filename); SELECT nvl(reqinfo.outfile_name, '-- No output file --') INTO filename FROM dual; DBMS_OUTPUT.PUT_LINE('Output file: ' filename); DBMS_OUTPUT.PUT_LINE('It produced completion message: '); DBMS_OUTPUT.PUT_LINE(nvl(reqinfo.completion_text, '-- NO COMPLETION MESSAGE --')); -- Running Requests ------------------------------------------------------------------------------------------------------------- ELSIF reqinfo.phase_code = 'R' THEN DBMS_OUTPUT.PUT_LINE('Request 'p_req_id' is currently running with status "'c_status'".'); DBMS_OUTPUT.PUT_LINE('It began running on: ' nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --')); BEGIN SELECT user_concurrent_queue_name INTO mgrname FROM fnd_concurrent_queues_vl WHERE concurrent_queue_id = reqinfo.controlling_manager; DBMS_OUTPUT.PUT_LINE('It is being run by manager: ' mgrname); EXCEPTION WHEN NO_DATA_FOUND THEN null; END; SELECT nvl(reqinfo.logfile_name, '-- No logfile --') INTO filename FROM dual; DBMS_OUTPUT.PUT_LINE('Logfile: ' filename); SELECT nvl(reqinfo.outfile_name, '-- No output file --') INTO filename FROM dual; DBMS_OUTPUT.PUT_LINE('Output file: ' filename); IF reqinfo.status_code = 'Z' THEN -- Waiting request, See what it is waiting on FOR child in c_wait LOOP DBMS_OUTPUT.PUT_LINE('It is waiting on request ' child.request_id' phase = 'get_phase(child.phase_code) ' status = 'get_status(child.status_code)); END LOOP; ELSIF reqinfo.status_code = 'W' THEN -- Paused, check and see if it is a request set, and if its children are running SELECT nvl(concurrent_program_name, 'UNKNOWN') INTO conc_prog_name FROM fnd_concurrent_programs WHERE concurrent_program_id = reqinfo.concurrent_program_id; DBMS_OUTPUT.PUT_LINE('A Running/Paused request is waiting on one or more child requests to complete.'); IF conc_prog_name = 'FNDRSSTG' THEN DBMS_OUTPUT.PUT_LINE('This program is a Request Set Stage.'); END IF; IF instr(conc_prog_name, 'RSSUB') > 0 THEN DBMS_OUTPUT.PUT_LINE('This program is a Request Set parent program.'); END IF; live_child := FALSE; FOR child in c_wait LOOP DBMS_OUTPUT.PUT_LINE('It has a child request: ' child.request_id' (phase = 'get_phase(child.phase_code) ' - status = 'get_status(child.status_code)')'); IF child.phase_code != 'C' THEN live_child := TRUE; END IF; END LOOP; IF live_child = FALSE THEN DBMS_OUTPUT.PUT_LINE('This request has no child requests that are still running. You may need to wake this request up manually.'); END IF; END IF; -- Pending Requests ------------------------------------------------------------------------------------------------------------- ELSIF reqinfo.phase_code = 'P' THEN DBMS_OUTPUT.PUT_LINE('Request 'p_req_id' is in phase "Pending" with status "'c_status'".'); DBMS_OUTPUT.PUT_LINE(' (phase_code = P) (status_code = 'reqinfo.status_code')'); -- could be a queue control request SELECT queue_control_flag INTO qcf FROM fnd_concurrent_programs WHERE concurrent_program_id = reqinfo.concurrent_program_id AND application_id = reqinfo.program_application_id; IF qcf = 'Y' THEN DBMS_OUTPUT.PUT_LINE('This request is a queue control request'); DBMS_OUTPUT.PUT_LINE('It will be run by the ICM on its next sleep cycle'); GOTO diagnose; END IF; -- why is it pending? -- could be scheduled IF reqinfo.requested_start_date > sysdate or reqinfo.status_code = 'P' THEN DBMS_OUTPUT.PUT_LINE('This is a scheduled request.'); DBMS_OUTPUT.PUT_LINE('It is currently scheduled to start running on ' to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('This should show on the form as Pending/Scheduled'); GOTO diagnose; END IF; -- could be on hold IF reqinfo.hold_flag = 'Y' THEN DBMS_OUTPUT.PUT_LINE('This request is currently on hold. It will not run until the hold is released.'); DBMS_OUTPUT.PUT_LINE('It was placed on hold by: 'last_user' on 'to_char(reqinfo.last_update_date, 'DD-MON-RR HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/On Hold'); GOTO diagnose; END IF; -- could be disabled IF proginfo.enabled_flag = 'N' THEN DBMS_OUTPUT.PUT_LINE('This request is currently disabled.'); DBMS_OUTPUT.PUT_LINE('The concurrent_program ' proginfo.user_concurrent_program_name ' needs to be enabled for this request to run.'); DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Disabled'); GOTO diagnose; END IF; -- check queue_method_code -- unconstrained requests IF reqinfo.queue_method_code = 'I' THEN DBMS_OUTPUT.PUT_LINE('This request is an unconstrained request. (queue_method_code = I)'); IF reqinfo.status_code = 'I' THEN DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Normal" status, ready to be run by the next available manager.'); ELSIF reqinfo.status_code = 'Q' THEN DBMS_OUTPUT.PUT_LINE('It has a status of "Standby" even though it is unconstrained. It will not be run by any manager.'); ELSIF reqinfo.status_code IN ('A', 'Z') THEN DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.'); SELECT nvl(parent_request_id, -1) INTO parent_id FROM fnd_conc_req_summary_v WHERE request_id = p_req_id; IF parent_id = -1 THEN DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request'); ELSE DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' to_char(parent_id)); END IF; ELSE DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' reqinfo.status_code '. I was not really expecting to see this status.'); END IF; -- constrained requests ELSIF reqinfo.queue_method_code = 'B' THEN DBMS_OUTPUT.PUT_LINE('This request is a constrained request. (queue_method_code = B)'); IF reqinfo.status_code = 'I' THEN DBMS_OUTPUT.PUT_LINE('The Conflict Resolution manager has released this request, and it is in a "Pending/Normal" status.'); DBMS_OUTPUT.PUT_LINE('It is ready to be run by the next available manager.'); ELSIF reqinfo.status_code = 'Q' THEN DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Standby" status. The Conflict Resolution manager will need to release it before it can be run.'); ELSIF reqinfo.status_code IN ('A', 'Z') THEN DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.'); SELECT nvl(parent_request_id, -1) INTO parent_id FROM fnd_conc_req_summary_v WHERE request_id = p_req_id; IF parent_id = -1 THEN DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request'); ELSE DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' to_char(parent_id)); END IF; ELSE DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' reqinfo.status_code '. I was not really expecting to see this status.'); END IF; -- incompatible programs SELECT program_application_id, concurrent_program_id, cd_id INTO conc_app_id, conc_id, conc_cd_id FROM fnd_concurrent_requests WHERE request_id = p_req_id; icount := 0; FOR progs in c_inc LOOP traid := progs.to_run_application_id; trcpid := progs.to_run_concurrent_program_id; OPEN c_ireqs; LOOP FETCH c_ireqs INTO ireqid, pcode, scode; EXIT WHEN c_ireqs%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Request ' p_req_id ' is waiting, or will have to wait, on an incompatible request: ' ireqid ); DBMS_OUTPUT.PUT_LINE('which has phase = ' pcode ' and status = ' scode); icount := icount + 1; END LOOP; CLOSE c_ireqs; END LOOP; IF icount = 0 THEN DBMS_OUTPUT.PUT_LINE('No running incompatible requests were found for request 'p_req_id); END IF; -- could be a runalone itself IF proginfo.run_alone_flag = 'Y' THEN DBMS_OUTPUT.PUT_LINE('This request is constrained because it is a runalone request.'); END IF; -- single threaded IF reqinfo.single_thread_flag = 'Y' THEN DBMS_OUTPUT.PUT_LINE('This request is constrained because the profile option Concurrent: Sequential Requests is set.'); reqlimit := true; END IF; -- request limit IF reqinfo.request_limit = 'Y' THEN DBMS_OUTPUT.PUT_LINE('This request is constrained because the profile option Concurrent: Active Request Limit is set.'); reqlimit := true; END IF; IF reqlimit = true THEN DBMS_OUTPUT.PUT_LINE('This request may have to wait on these requests:'); FOR progs in c_userreqs(reqinfo.requested_by, reqinfo.requested_start_date) LOOP DBMS_OUTPUT.PUT_LINE('Request id: ' progs.request_id ' Requested start date: ' progs.start_date); DBMS_OUTPUT.PUT_LINE(' Phase: ' get_phase(progs.phase_code) ' Status: ' get_status(progs.status_code)); END LOOP; END IF; -- error, invalid queue_method_code ELSE DBMS_OUTPUT.PUT_LINE('** This request has an invalid queue_method_code of 'reqinfo.queue_method_code); DBMS_OUTPUT.PUT_LINE('** This request will not be run. You may need to apply patch 739644.'); GOTO diagnose; END IF; DBMS_OUTPUT.PUT_LINE(sep); DBMS_OUTPUT.PUT_LINE('Checking managers available to run this request...'); -- check the managers manager_check(p_req_id, reqinfo.cd_id, mgr_defined, mgr_active, mgr_workshift, mgr_running, run_alone); -- could be a runalone ahead of it IF run_alone = TRUE THEN DBMS_OUTPUT.PUT_LINE('There is a runalone request running ahead of this request'); DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager'); select user_cd_name into conflict_domain from fnd_conflicts_domain where cd_id = reqinfo.cd_id; DBMS_OUTPUT.PUT_LINE('Conflict domain = 'conflict_domain); -- see what is running begin select request_id, status_code, actual_start_date into tmp_id, tmp_status, tmp_date from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp where fcp.run_alone_flag = 'Y' and fcp.concurrent_program_id = fcr.concurrent_program_id and fcr.phase_code = 'R' and fcr.cd_id = reqinfo.cd_id; DBMS_OUTPUT.PUT_LINE('This request is waiting for request 'tmp_id ', which is running with status 'get_status(tmp_status)); DBMS_OUTPUT.PUT_LINE('It has been running since: ' nvl(to_char(tmp_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --')); exception when NO_DATA_FOUND then DBMS_OUTPUT.PUT_LINE('** The runalone flag is set for conflict domain 'conflict_domain ', but there is no runalone request running'); end; ELSIF mgr_defined = FALSE THEN DBMS_OUTPUT.PUT_LINE('There is no manager defined that can run this request'); DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager'); DBMS_OUTPUT.PUT_LINE('Check the specialization rules for each manager to make sure they are defined correctly.'); ELSIF mgr_active = FALSE THEN DBMS_OUTPUT.PUT_LINE('There are one or more managers defined that can run this request, but none of them are currently active'); DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager'); -- print out which managers can run it and their status DBMS_OUTPUT.PUT_LINE('These managers are defined to run this request:'); print_mgrs(p_req_id); ELSIF mgr_workshift = FALSE THEN DBMS_OUTPUT.PUT_LINE('Right now, there is no manager running in an active workshift that can run this request'); DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager'); -- display details about the workshifts ELSIF mgr_running = FALSE THEN DBMS_OUTPUT.PUT_LINE('There is one or more managers available to run this request, but none of them are running'); DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager'); -- print out which managers can run it and their status print_mgrs(p_req_id); ELSE -- print out the managers available to run it DBMS_OUTPUT.PUT_LINE('These managers are available to run this request:'); print_mgrs(p_req_id); END IF; -- invalid phase code ELSE DBMS_OUTPUT.PUT_LINE('Request 'p_req_id' has an invalid phase_code of "'reqinfo.phase_code'"'); END IF; <> BEGIN FND_CONC.DIAGNOSE(p_req_id, fcd_phase, fcd_status, :help_text); EXCEPTION WHEN OTHERS THEN :help_text := 'The FND_CONC package has not been installed on this system.'; END; DBMS_OUTPUT.PUT_LINE(sep); EXCEPTION WHEN REQ_NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Request 'p_req_id' not found.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error number ' sqlcode ' has occurred.'); DBMS_OUTPUT.PUT_LINE('Cause: ' sqlerrm); END analyze_request; BEGIN analyze_request(req_id); END; / prompt prompt Additional information (from FND_CONC.DIAGNOSE): print help_text;

Monday, December 27, 2010

Concurrent Manager Setup (PCP) in 11i RAC Environment

Concurrent Manager Setup (PCP) in 11i(RAC) Environment

PURPOSE
-----------------------------
Configuring parallel concurrent processing allows you to distribute concurrent
managers, and workload across multiple nodes in a cluster, or networked environment.
PCP can also be implemented in a RAC environment in order to provide automated
failover of workload should the primary (source), or secondary (target) concurrent
processing nodes, or RAC instances fail. There are several different failure
scenarios that can occur depending on the type of Applications Technology Stack
implementation that is performed.

The basic failure scenarios are:

1. The database instance that supports the CP, Applications, and Middle-Tier
processes such as Forms, or iAS can fail.
2. The Database node server that supports the CP, Applications, and Middle-Tier
processes such as Forms, or iAS can fail.
3. The Applications/Middle-Tier server that supports the CP (and Applications)
base can fail.

The concurrent processing tier can reside on either the Applications, Middle-Tier,
or Database Tier nodes. In a single tier configuration, non PCP environment, a
node failure will impact Concurrent Processing operations due to any of these
failure conditions. In a multi-node configuration the impact of any these types
of failures will be dependent upon what type of failure is experienced, and how
concurrent processing is distributed among the nodes in the configuration. Parallel
Concurrent Processing provides seamless failover for a Concurrent Processing
environment in the event that any of these types of failures takes place.

In an Applications environment where the database tier utilizes Listener
(server) load balancing, and in a non-load balanced environment, there
are changes that must be made to the default configuration generated by
Autoconfig so that CP initialization, processing, and PCP functionality are
initiated properly on their respective/assigned nodes. These changes are
described in the next section - Concurrent Manager Setup and Configuration
Requirements in an 11i RAC Environment.

The current Concurrent Processing architecture with Global Service Management
consists of the following processes and communication model, where each process
is responsible for performing a specific set of routines and communicating with
parent and dependent processes.

Internal Concurrent Manager (FNDLIBR process) - Communicates with the Service
Manager.

The Internal Concurrent Manager (ICM) starts, sets the number of active processes,
monitors, and terminates all other concurrent processes through requests made to
the Service Manager, including restarting any failed processes. The ICM also
starts and stops, and restarts the Service Manager for each node. The ICM will
perform process migration during an instance or node failure. The ICM will be
active on a single node. This is also true in a PCP environment, where the ICM
will be active on at least one node at all times.

Service Manager (FNDSM process) - Communicates with the Internal Concurrent Manager,
Concurrent Manager, and non-Manager Service processes.

The Service Manager (SM) spawns, and terminates manager and service processes (these
could be Forms, or Apache Listeners, Metrics or Reports Server, and any other process
controlled through Generic Service Management). When the ICM terminates the SM that
resides on the same node with the ICM will also terminate. The SM is ‘chained’ to
the ICM. The SM will only reinitialize after termination when there is a function it
needs to perform (start, or stop a process), so there may be periods of time when the
SM is not active, and this would be normal. All processes initialized by the SM
inherit the same environment as the SM. The SM’s environment is set by APPSORA.env
file, and the gsmstart.sh script. The TWO_TASK used by the SM to connect to a RAC
instance must match the instance_name from GV$INSTANCE. The apps_ listener must
be active on each CP node to support the SM connection to the local instance. There
should be a Service Manager active on each node where a Concurrent or non-Manager
service process will reside.

Internal Monitor (FNDIMON process) - Communicates with the Internal Concurrent
Manager.

The Internal Monitor (IM) monitors the Internal Concurrent Manager, and restarts any
failed ICM on the local node. During a node failure in a PCP environment the IM will
restart the ICM on a surviving node (multiple ICM's may be started on multiple nodes,
but only the first ICM started will eventually remain active, all others will
gracefully terminate). There should be an Internal Monitor defined on each node
where the ICM may migrate.

Standard Manager (FNDLIBR process) - Communicates with the Service Manager and any
client application process.

The Standard Manager is a worker process, that initiates, and executes client requests
on behalf of Applications batch, and OLTP clients.

Transaction Manager - Communicates with the Service Manager, and any user process
initiated on behalf of a Forms, or Standard Manager request. See Note 240818.1
regarding Transaction Manager communication and setup requirements for RAC.

SCOPE & APPLICATION
-----------------------------
This article is provided for Applications development, product management, system
architects, and system administrators involved in deploying and configuring Oracle
Applications in a RAC environment. This document will also be useful to field
engineers and consulting organizations to facilitate installations and configuration
requirements of Applications 11i in a RAC environment.

Concurrent Manager Setup and Configuration Requirements in an 11i RAC Environment
-----------------------------
In order to set up Setup Parallel Concurrent Processing Using AutoConfig with GSM,
follow the instructions in the 11.5.8 Oracle Applications System Administrators Guide
under Implementing Parallel Concurrent Processing using the following steps:

1. Applications 11.5.8 and higher is configured to use GSM. Verify the configuration
on each node (see WebIV Note 165041.1).
2. On each cluster node edit the Applications Context file (.xml), that resides
in APPL_TOP/admin, to set the variable ON .
It is normally set to OFF. This change should be performed using the Context
Editor.
3. Prior to regenerating the configuration, copy the existing tnsnames.ora,
listener.ora and sqlnet.ora files, where they exist, under the 8.0.6 and iAS
ORACLE_HOME locations on the each node to preserve the files (i.e./
directory>/ora/$ORACLE_HOME/network/admin//tnsnames.ora). If any of
the Applications startup scripts that reside in COMMON_TOP/admin/scripts/
have been modified also copy these to preserve the files.
4. Regenerate the configuration by running adautocfg.sh on each cluster node as
outlined in Note 165195.1.
5. After regenerating the configuration merge any changes back into the tnsnames.ora,
listener.ora and sqlnet.ora files in the network directories, and the startup
scripts in the COMMON_TOP/admin/scripts/ directory. Each nodes tnsnames.ora
file must contain the aliases that exist on all other nodes in the cluster. When
merging tnsnames.ora files ensure that each node contains all other nodes
tnsnames.ora entries. This includes tns entries for any Applications tier nodes
where a concurrent request could be initiated, or request output to be viewed.
6. In the tnsnames.ora file of each Concurrent Processing node ensure that there is
an alias that matches the instance name from GV$INSTANCE of each Oracle instance
on each RAC node in the cluster. This is required in order for the SM to establish
connectivity to the local node during startup. The entry for the local node will
be the entry that is used for the TWO_TASK in APPSORA.env (also in the
APPS_.env file referenced in the Applications Listener [APPS_]
listener.ora file entry "envs='MYAPPSORA=/APPS_.env)
on each node in the cluster (this is modified in step 12).
7. Verify that the FNDSM_ entry has been added to the listener.ora file under
the 8.0.6 ORACLE_HOME/network/admin/ directory. See WebiV Note 165041.1 for
instructions regarding configuring this entry. NOTE: With the implementation of
GSM the 8.0.6 Applications, and 9.2.0 Database listeners must be active on all PCP
nodes in the cluster during normal operations.
8. AutoConfig will update the database profiles and reset them for the node from
which it was last run. If necessary reset the database profiles back to their
original settings.
9. Ensure that the Applications Listener is active on each node in the cluster where
Concurrent, or Service processes will execute. On each node start the database
and Forms Server processes as required by the configuration that has been
implemented.
10. Navigate to Install > Nodes and ensure that each node is registered. Use the node
name as it appears when executing a ‘nodename’ from the Unix prompt on the server.
GSM will add the appropriate services for each node at startup.
11. Navigate to Concurrent > Manager > Define, and set up the primary and secondary
node names for all the concurrent managers according to the desired configuration
for each node’s workload. The Internal Concurrent Manager should be defined on the
primary PCP node only. When defining the Internal Monitor for the secondary
(target) node(s), make the primary node (local node) assignment, and assign a
secondary node designation to the Internal Monitor, also assign a standard work
shift with one process.
12. Prior to starting the Manager processes it is necessary to edit the APPSORA.env
file on each node in order to specify a TWO_TASK entry that contains the
INSTANCE_NAME parameter for the local nodes Oracle instance, in order to bind
each Manager to the local instance. This should be done regardless of whether
Listener load balancing is configured, as it will ensure the configuration
conforms to the required standards of having the TWO_TASK set to the instance
name of each node as specified in GV$INSTANCE. Start the Concurrent Processes
on their primary node(s). This is the environment that the Service Manager
passes on to each process that it initializes on behalf of the Internal
Concurrent Manager. Also make the same update to the file referenced by the
Applications Listener APPS_ in the listener.ora entry "envs='MYAPPSORA=
/APPS_.env" on each node.
13. Navigate to Concurrent > Manager > Administer and verify that the Service Manager
and Internal Monitor are activated on the secondary node, and any other
addititional nodes in the cluster. The Internal Monitor should not be active on
the primary cluster node.
14. Stop and restart the Concurrent Manager processes on their primary node(s), and
verify that the managers are starting on their appropriate nodes. On the target
(secondary) node in addition to any defined managers you will see an FNDSM
process (the Service Manager), along with the FNDIMON process (Internal Monitor).

Failover Considerations
-----------------------------
In order to have log and output files available to each node during an extended node
failure, each log and out directory needs to be made accessible to all other CP nodes
in the cluster (placed on shared disk).

In order to view log and output files from a failed node during an outage/node
failure, the FNDFS_ entry in tnsnames.ora under the 8.0.6 ORACLE_HOME location
on each node should be configured using an ADDRESS_LIST following the example below,
to provide connect time failover. This entry should be placed in the tnsnames.ora
file on each node that supports Concurrent Processing, with the local node first in
the ADDRESS_LIST entry.

FNDFS_coe-srv5-pc=(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=coe_svr5_pc)(PORT=1231))
(ADDRESS=(PROTOCOL=tcp)(HOST=coe_svr7_pc)(PORT=1232)))
(CONNECT_DATA=(SID=FNDFS))
)

See Bug 3259441 for issues related to the TNS alias length exceeding 255 characters.

Without configuring connect time failover using the 8.0.6 ORACLE_HOME tnsnames.ora
by specifying the ADDRESS_LIST entry the only other alternative is to perform a manual
update of the fnd_concurrent_requests table for each request, in order to reflect the
change in outfile_node_name, and logfile_node_name from the failed to the surviving
node.

Determine the failed node name, and update the out and log entry to the
surviving node:

SQL> select outfile_node_name from fnd_concurrent_requests
2 where request_id=166273;

COE-SVR7-PC

SQL> select logfile_node_name from fnd_concurrent_requests
2* where request_id=166273

COE-SVR7-PC

Update both outfile and logfile_node_name from the failed to the surviving instance:

SQL> update fnd_concurrent_requests set outfile_node_name = 'COE-SVR5-PC'
2* where request_id =166273

SQL> update fnd_concurrent_requests set logfile_node_name = 'COE-SVR5-PC'
2* where request_id =166273

Using the ADDRESS_LIST rather than updating fnd_concurrent_reqests is the recommended
method to estabilsh failover access for Concurrent Manager log and output files.


Configuration Examples:
-----------------------------
Finding the instance name -

column host_name format a20;
select host_name, instance_name from gv$instance;

HOST_NAME INSTANCE_NAME
----------- ----------------
coe-svr7-pc APRA7
coe-svr5-pc APRA5

Modifying the APPSORA.env & APPS_.env file for node coe-svr5-pc -

> cd $APPL_TOP
> cat APPSORA.env
:
# $Header: APPSORA_ux.env 115.4 2003/03/01 01:02:35 wdgreene ship $
# =============================================================================
# NAME
# APPSORA.env
#
# DESCRIPTION
# Execute environment for Oracle and APPL_TOP
#
# NOTES
#
# HISTORY
#
# =============================================================================
#
# ###############################################################
#
# This file is automatically generated by AutoConfig. It will be read and over.
# If you were instructed to edit this file, or if you are not able to use the ss
# created by AutoConfig, refer to Metalink document 165195.1 for assistance.
#
# ###############################################################
#
. /oralocal/apraora/8.0.6/APRA.env
. /oralocal/apraappl/APRA.env
TWO_TASK=APRA5
export TWO_TASK

TNS alias definition in tnsnames.ora on each node -

cd $TNS_ADMIN
pwd
/oralocal/apraora/8.0.6/network/admin/APRA

APRA5 = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= coe-svr5-pc)(PORT=1523))
(CONNECT_DATA=(INSTANCE_NAME=APRA5)(SERVICE_NAME=apradb))
)
APRA7 = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= coe-svr7-pc)(PORT=1523))
(CONNECT_DATA=(INSTANCE_NAME=APRA7)(SERVICE_NAME=apradb))
)

FNDFS entry in listener.ora on each node -

cd $TNS_ADMIN
pwd
/oralocal/apraora/8.0.6/network/admin/APRA

( SID_DESC = ( SID_NAME = FNDFS )( ORACLE_HOME = /oralocal/apraora/8.0.6 )
( PROGRAM = /oralocal/apraappl/fnd/11.5.0/bin/FNDFS )
( envs='EPC_DISABLED=TRUE,NLS_LANG=AMERICAN_AMERICA.WE8ISO8859
1,LD_LIBRARY_PATH=/usr/dt/lib:/usr/openwin/lib:/oralocal/apraora/8.0.6/lib,SHLI
_PATH=/usr/lib:/usr/dt/lib:/usr/openwin/lib:/oralocal/apraora/8.0.6/lib,LIBPATH
/usr/dt/lib:/usr/openwin/lib:/oralocal/apraora/8.0.6/lib' )
)
)

Wednesday, December 9, 2009

Killiing the Standard Manger when one or instances running on the same server

Standard Manger Killing:-

set linesize 120
set head off
set pages 0
select 'kill -9 '||' '|| substr(b.os_process_id,0,10) "OS Proc"
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='STANDARD'
and b.process_status_code='A'
order by b.process_status_code;

Wednesday, August 6, 2008

Concurrent Manager : troubleshooting

Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase


Below are several different possible solutions to the problem where concurrent
requests are stuck in pending status:

1. When shutting down the concurrent manager are there any FNDLIBR processes still running at the OS level? If so, do a kill -9 on them. Then restart the concurrent manager.

2. Try Relinking $FND_TOP.

3. Rebuild the concurrent manager views. As applmgr run the following from
the OS:

This is non-destructive. Concurrent Manager views can be rebuild by running the following command at the command line:

Ensure that concurrent manager is shutdown.

FNDLIBR FND FNDCPBWV apps/apps SYSADMIN 'System Administrator' SYSADMIN

restart the concurrent mgr.

4. Another possibility is that the Profile Option "Concurrent: OPS Request Partitioning" is set to OFF (by default). Unless Concurrent OPS is being used, the setting for this profile option should be NULL.

5. Yet another possibility is that System Profile Option: Concurrent Active
Requests is set to 0.


1. Log into Oracle Applications as SYSADMIN.
2. Select System Administrator responsibility.
3. Navigate to PROFILE --> SYSTEM.
4. Query for %CONC%ACTIVE%.
5. Change the profile option for Concurrent: Active Request Limit to Null (blank).
6. Exit Oracle Applications and log in again for the change to take affect.
7. Run a new concurrent request.


6. Another possibility is that Concurrent managers were brought down, while an
outstanding request was still running in the background. In which case, Update
the FND_CONCURRENT_REQUESTS table as follows:
.
sql> update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';
sql> commit;

7. The control_code for concurrent_queue_name = 'FNDCRM' is 'N' in the FND_CONCURRENT_QUEUES table, which means 'Target node/queue unavailable'. This value should be NULL (CRM is running; target and actual process amount are the same), or 'A' ('Activate concurrent manager' control status).

Set the control_code to 'A' in fnd_concurrent_queues for the Conflict Resolution Manager:

1. Logon to Oracle Applications database server as 'applmgr'.
2. Verify the Applications environment is setup correctly ($ORACLE_HOME and $ORACLE_SID).

3. Logon to SQL*Plus as 'APPS' and run the following SQL statement:

update fnd_concurrent_queues
set control_code = 'A'
where concurrent_queue_name = 'FNDCRM';

commit;

4. Verify the status of the concurrent managers through the
Concurrent -> Manager -> Administer form.

If the CRM is still not active, bounce (deactivate, activate) the Internal Concurrent Manager. This is done through the Concurrent -> Manager ->

Administer form from the 'System Administrator' responsibility. It can also be done through the CONCSUB command at the command level.

Setting the control_code to 'A' in the fnd_concurrent_queues table for the Conflict Resolution Manager indicates that this concurrent manager is to be activated with the parameter values specified through this table for this manager (MAX_PROCESSES, CACHE_SIZE, etc).

8. What is the cache size? Try increasing it then bounce the concurrent manager.

If concurrent requests are rarely prioritized and there are managers that service short-running requests, consider setting the cache size to equal at least twice the number of target processes. This increases the throughput of the concurrent manaagers by attempting to avoid any sleep time. For example, if more than one manager or worker processes the same type of requests with only a small cache size, it may be unable to process any jobs in a single processing cycle, because other processes have already run the cached requests. When this happens, it is important to note that the manager will sleep before refreshign its cache. To increase manager throughput where there are sufficient requests of the required type in the queue, increase the cache size to improve the chance of the manager finding work to process and thus avoid having to enter a sleep phase.

TIP: Ensure that the system is not resource-constrained before attempting to increase the rate of concurrent processing in this way; otherwise, these changes may actually reduce concurrent processing throughput because jobs take longer to run..

Enter the number of requests your manager remembers each time it reads which requests to run. For example, if a manager's workshift has 1 target process and a cache value of 3, it will read three requests,, and will wait until these three requests have been run before reading new requests.

In reading requests, the manager will only put requests it is allowed to run into its cache. For example, if you have defined your manager to run only Order Entry reports then the manager will put only Order Entry requests into its cache.

If you enter 1, the concurrent manager must look at its requests list each time it is ready to process another request. By setting the cache size at a higher number, the concurrent manager does not have to read its requests list each time it runs a request. However, the manager does not recognizea nay priority changes you make for a particular request if it has already read that request into its cache. Further, even if you give a higher priority to a new request, that new request must wait until the buffer isempty and the manager returns to look at the requests list. That request may have to wait a long time if you set the buffer size to a high number.

You should use cache size to tune your concurrent managers to work most efficiently for you site's needs. If your organization tends to reprioritize jobs going to a certain manager, that manager should have its buffer size set fairly low.

*Suggestion: *Enter a value of 1 when defining a manager that runs long, time-consuming jobs, and a value of 3 or 4 for managers that run small, quick jobs.



9. Please check the sleep seconds set for the concurrent manager which runs the report that is taking long time for completion. Reducing the sleep time will improve performance.

1. Check for the value set for the sleep seconds for the concurrent manager using the following path: System Administrator Responsibilty -> Concurrent -> Manager -> Define.

2. Check the processes running for the concurent manager using the following command:

ps -ef | grep 'INVLIBR'

As all concurrent programs are dealt by INVLIBR, the number of processes returned here must tally with the value specified for the processes defined for the concurrent manager.

3. Also, check for the duration for which the processes are sleeping. Reducing the value assigned for it would improve performance.

As a workaround to improve performance, you could change the priority of concurent request.

Explanation
-----------
The sleep state would be assigned to some tasks when the program running it has to handle more tasks than it has been configured to process. For example, if the number of processes has been defined as say 10, then at any point of time there is a provision to run 10 tasks. Now say, 12 tasks arrive for the program, then time slots are alloted for 10 and the rest 2 are put to sleep. So, if the concurrent program running the problem report is handling larger number of processes the sleep and run behaviour can be observed.

Thursday, June 26, 2008

Script to Monitor Concurrent Jobs and Hanging Sessions

The examples below are based on the following demo schema's:
• user SCOTT created with script: $ORACLE_HOME/rdbms/admin/scott.sql
• user HR created with script: $ORACLE_HOME/demo/schema/human_resources/hr_main.sql
The Export Data Pump and Import Data Pump examples that are mentioned below are based on the directory my_dir. This directory object needs to refer to an existing directory on the server where the Oracle RDBMS is installed. Example:
-- for Windows platforms:

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;

-- for Unix platforms:

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;
1. QUERY in Parameter file.
Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause.
Example to export the following data with the Export Data Pump client:
• from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and
• from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more.
File: expdp_q.par
-----------------
DIRECTORY =&nb p;my_dir
DUMPFILE = exp_query.dmp
LOGFILE = exp_query.log
SCHEMAS &nbs ;= hr, scott
INCLUDE = TABLE:"IN ('EMP', 'DEPARTMENTS')"
QUERY &n sp; = scott.emp:"WHERE job = 'ANALYST' OR sal >= 3000"
place following 3 lines on one single line:
QUERY = hr.departments:"WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = 'ANALYST' OR e.salary >= 3000)"

-- Run Export DataPump job:

%expdp system/manager parfile=expdp_q.par
Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema.
2. QUERY on Command line.
The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause.
Example to export the following data with the Export Data Pump client:
• table scott.dept; and
• from table scott.emp all employees whose name starts with an 'A'
-- Example Windows platforms:
-- Note that the double quote character needs to be 'escaped'
-- Place following statement on one single line:

D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp
LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\"WHERE ename LIKE 'A%'\"

-- Example Unix platforms:
-- Note that all special characters need to be 'escaped'

% expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \
QUERY=emp:\"WHERE ename LIKE \'A\%\'\"
Note that with the original export client two jobs were required:
-- Example Windows platforms:
-- Place following statement on one single line:

D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp
QUERY=\"WHERE ename LIKE 'A%'\"

D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

-- Example Unix platforms:

> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \
QUERY=\"WHERE ename LIKE \'A\%\'\"

> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept
3. QUERY in Oracle Enterprise Manager Database Console.
The QUERY can also be specified in the Oracle Enterprise Manager Database Console. E.g.:
• Login to the Oracle Enterprise Manager 10g Database Console, e.g.: http://my_node_name:5500/em
• Click on link 'Maintenance'
• Under 'Utilities', click on link 'Export to Files'
• Answer questions on the following pages.
• At 'step 2 of 5' (the page with the Options), click on link 'Show Advanced Options'
• At the end of the page, under the QUERY option, click on button 'Add'
• At the next page, choose the table name (SCOTT.EMP)
• And specify the SELECT statement predicate clause to be applied to tables being exported, e.g.: WHERE ename LIKE 'A%'
• Continue with the remaining options, and submit the job.
4. Import Data Pump parameter QUERY.
Similar to previous examples with Export Data Pump, the QUERY parameter can also be used during the import. An example of how to use the QUERY parameter with Import Data Pump:
-- In source database:
-- Export the schema SCOTT:

%expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott

-- In target database:
-- Import all employees of department 10:

%impdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=impdp_s.log TABLES=emp TABLE_EXISTS_ACTION=append \
QUERY=\"WHERE deptno = 10\" CONTENT=data_only
Note that this feature was not available with the original import client (imp). Also note that the parameter TABLE_EXISTS_ACTION=append is used to allow the import into an existing table and that CONTENT=data_only is used to skip importing statistics, indexes, etc.

Read more!


How to copy database users from one database to another new database and keep the
login password and granted roles, privileges ?

1. Oracle10g and above: Use Data Pump.
In Oracle10g you can use the Export DataPump and Import DataPump utilities. Example:
-- Step 1: In source database, run a schema level Export Data Pump
-- job and connect with a user who has the EXP-FULL_DATABASE role:

% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \
LOGFILE=exp_scott.log SCHEMAS=scott

-- Step 2: In target database, run a schema level Import Data Pump
-- job which will also create the user in the target database:

% impdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \
LOGFILE=imp_scott.log SCHEMAS=scott
Or you can create a logfile (SQLFILE) with all relevant statements. E.g.:
impdp system/manager directory=my_dir dumpfile=exp_scott.dmp logfile=imp_scott.log schemas=scott sqlfile=imp_user.sql

or:
2. In Oracle9i and above: query the Data Dictionary in the source database to obtain the required information to pre-create the user in the target database. Example:
2.1. Obtain the CREATE USER statement in the source database. E.g.:
SET long 200000000
SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;
2.2. Run other queries in the source database to determine which privileges, grants, roles, and tablespace quotas are granted to the users. E.g.:
SET lines 120 pages 100
SELECT * FROM dba_sys_privs WHERE grantee='SCOTT';
SELECT * FROM dba_role_privs WHERE grantee='SCOTT';
SELECT * FROM dba_tab_privs WHERE grantee='SCOTT';
SELECT * FROM dba_ts_quotas WHERE username='SCOTT';
2.3. Create a script file that contains the CREATE USER, CREATE ROLE statements, the GRANT statements, and the ALTER USER statements for tablespace quotas.
2.4. Pre-create the tablespaces for this user with SQL*Plus in the target database. Note that the original CREATE TABLESPACE statement can be obtained in the source database with DBMS_METADATA.GET_DDL. E.g.:
SET long 200000000
SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') FROM dual;
2.5. Run the script of step 2.3. to create the user in the target database.
2.6. Run a user level export. E.g.:
exp system/manager file=exp_scott.dmp log=exp_scott.log owner=scott
2.7. Import this export dumpfile into the target database. E.g.:
imp system/manager file=exp_scott.dmp log=imp_scott.log fromuser=scott touser=scott

or:
3. If all users need to be copied into the new target database: use a full database export from the source database and a full database into the target database. Example:
3.1. Run a full database export. E.g.:
exp system/manager file=exp_f.dmp log=exp_f.log full=y
or in Oracle10g:
expdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=expdp_f.log full=y
3.2. Pre-create the tablespaces with SQL*Plus in that target database if they have a different directory structure on the target server. If the directory structure on the target is the same as on the source, ensure that this directory structure is in place (import does not create directories when creating tablespaces). Note that the original CREATE TABLESPACE statement can be obtained with dbms_metadata.get_ddl (see the example in step 2.4. above).
3.3. Import the data into the target database with:
imp system/manager file=exp_f.dmp log=imp_f.log full=y
or in Oracle10g:
impdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=impdp_f.log full=y


The rlwrap (readline wrapper) utility provides a command history and editing of keyboard input
for any other command. This is a really handy addition to SQL*Plus and RMAN .

Download the latest rlwrap software from the following URL.
http://utopia.knoware.nl/~hlub/uck/rlwrap/
Unzip and install the software using the following commands.
gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install

Run the following commands, or better still append then to the ".bash_profile" of the

oracle software owner.

alias sqlplus='rlwrap ${ORACLE_HOME}/bin/sqlplus'
alias rman='rlwrap ${ORACLE_HOME}/bin/rman'
alias expdp='rlwrap ${ORACLE_HOME}/bin/expdp'
You can now start SQL*Plus or RMAN using "sqlplus" and "rman" respectively, and you will have

a basic command history and the current line will be editable using the arrow and delete keys.

Read more!






StatspackAnalyzer is a true expert system that seeks to codify expert DBA knowledge and advice against any
STATSPACK or AWR report. The well-structured decision rules of expert Oracle tuning specialists were collected,
quantified and then generalized and validated against real-world STATSPACK and AWR reports.
While no automated tool can fully replicate the decision processes of a human DBA tuning expert, this tool
makes observations about exceptional conditions within the STATSPACK or AWR report. This tool was never
intended to replace the human intuition of an Oracle performance expert, and all observations from
statspackanalyzer should be validated with a human expert.





Script to Monitor Concurrent Jobs and Hanging Sessions

Here is a monitoring system to monitor all concurrent jobs, concurrent managers and hung sessions every
hour proactively and take appropriate action immediately. It gives the following reports
1. List of Concurrent Jobs that completed with error in last one hour.
2. List of Concurrent Jobs running for more then 1 hour.
3. List of concurrent Jobs completed with Warning in last one hour
4. List of Jobs that are Pending Normal for more than 10 Minutes.
5. List of Hung sessions or Orphan sessions.
6. List of Concurrent managers with Pending Normal jobs.
7. Critical Jobs completed in last one hour with completion time.

SELECT A FROM
(
select 'CONCURRENT PROGRAMS COMPLETED WITH ERROR STATUS BETWEEN '||to_char(sysdate - (1/24),
'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'A' B,1 SRT from dual
UNION
select RPAD('-',125,'-') A, 'A' B,1.1 SRT from dual
UNION
SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||
rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'A' B,1.2 FROM DUAL
UNION
select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,
'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),
10)||' '||to_char(Parent_Request_ID) ) A, 'A' B, 1.4 SRT from fnd_conc_req_summary_v conc
where actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'E'
UNION
select RPAD('-',125,'-') A, 'A' B,1.6 SRT from dual
UNION
SELECT ' ', 'A', 1.8 FROM DUAL
UNION
SELECT ' ', 'A', 1.86 FROM DUAL
-----------------------------------------------------------
UNION
select 'CONCURRENT PROGRAMS COMPLETED WITH WARNING STATUS BETWEEN '||to_char(sysdate - (1/24),
'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'D' B,1 SRT from dual
UNION
select RPAD('-',125,'-') A, 'D' B, 1.1 SRT from dual
UNION
SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||
rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'D' B, 1.2 FROM DUAL
UNION
select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,
'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)
||' '||to_char(Parent_Request_ID) ) A, 'D' B, 1.4 SRT from fnd_conc_req_summary_v conc where
actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'G'
and concurrent_program_id not in (47654,31881,47737)
UNION
select RPAD('-',125,'-') A, 'D' B, 1.8 SRT from dual
-----------------------------------------------------------
UNION
SELECT ' ', 'D', 1.86 FROM DUAL
UNION
SELECT ' ', 'D', 1.88 FROM DUAL
UNION
select 'CONCURRENT PROGRAMS THAT ARE PENDING NORMAL FOR THE PAST 10 MINUTES ' A, 'E' B,1 SRT
from dual
UNION
select RPAD('-',125,'-') A, 'E' B, 1.1 SRT from dual
UNION
SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||
rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'E' B, 1.2 FROM DUAL
UNION
select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,
'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)
||' '||to_char(Parent_Request_ID) ) A, 'E' B, 2 SRT FROM FND_CONC_REQ_SUMMARY_V CONC
WHERE SYSDATE - REQUEST_DATE > 0.00694444444444444 AND REQUESTED_START_DATE < SYSDATE
AND PHASE_CODE = 'P' AND STATUS_CODE = 'Q'
UNION
select RPAD('-',125,'-') A, 'E' B, 3 SRT from dual
UNION
SELECT chr(10)||chr(10) A, 'E' B, 4.4 SRT FROM DUAL
UNION
select 'CONCURRENT PROGRAMS THAT STARTED BEFORE '||to_char(sysdate - (1/24),'dd-mon-yyyy hh24:mi:ss')
||' AND ARE STILL RUNNING ' A, 'B' B,4.6 SRT FROM DUAL
UNION
SELECT RPAD('-',125,'-') A, 'B' B, 4.8 SRT FROM DUAL
UNION
SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||
rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'B' B, 4.84 SRT FROM DUAL
UNION
SELECT to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,
'dd-mon-yyyy hh24:mi:ss'),'-'),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),
10)||' '||to_char(Parent_Request_ID) ) A, 'B' B, 4.86 SRT FROM FND_CONC_REQ_SUMMARY_V CONC
WHERE SYSDATE - ACTUAL_START_DATE > 0.0416666666666667 AND PHASE_CODE = 'R' AND STATUS_CODE = 'R'
-----------------------------------------------------------------------
UNION
SELECT RPAD('-',125,'-') A, 'C' B, 1.1 SRT FROM DUAL
UNION
SELECT ' ', 'C', 1.2 FROM DUAL
UNION
SELECT ' ', 'C', 5.8 FROM DUAL
UNION
select ' FOLLOWING ARE THE DETAILS OF HUNG OR ORPHAN SESSIONS AS OF '||to_char(sysdate ,
'dd-mon-yyyy hh24:mi:ss') A, 'C' B,1.5 SRT from dual
UNION
select RPAD('-',125,'-') A, 'C' B, 1.6 SRT from dual
UNION
SELECT to_char(rpad(to_char('SID'),5) ||' '||rpad('PROCESS',12)|| ' ' ||rpad('MODULE',10)||' '||rpad('ACTION',
25)||' '||rpad('USERNAME',15)||' '||rpad('PROGRAM',20)||' '||rpad('EVENT',25)) A, 'C' B, 5.2 FROM DUAL
UNION
select to_char(rpad(nvl(to_char(a.sid), ' '),7,' ')||' '||rpad(nvl(a.process, ' '),19,' ')||' '||rpad(nvl(a.module,
' '),10)||' '||rpad(nvl(a.action, ' '),20)||' '||rpad(nvl(a.username, ' '),15)||' '||rpad(nvl(a.program, ' '),20)||' '||
rpad(c.event,25)) A,'C' B, 5.4 SRT from gv$session a, gv$process b, gv$session_Wait c where c.event not
like 'SQL%' and c.event not in ('pmon timer','rdbms ipc message','pipe get','queue messages','smon timer',
'wakeup time manager','PL/SQL lock timer','jobq slave wait','ges remote message','async disk IO','gcs remote
message','PX Deq: reap credit','PX Deq: Execute Reply') and a.paddr=b.addr and a.sid=c.sid and a.inst_id=
c.inst_id and a.inst_id=b.inst_id and a.last_call_et >1800
UNION
select RPAD('-',125,'-') A, 'C' B, 5.6 SRT from dual
UNION
-----------------------------------------------------------------------
SELECT ' ', 'F', 1.01 FROM DUAL
UNION
select 'PENDING NORMAL MANAGERS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),
'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A, 'F' B,1 SRT from dual
UNION
select RPAD('-',125,'-') A, 'F' B, 1.1 SRT from dual
UNION
SELECT to_char( rpad('CONCURRENT MANAGER NAME',35) || rpad('ACTUAL',25)|| ' ' ||rpad('TARGET',
20)||' '||rpad('RUNNING',25)||' '||'PENDING'), 'F' B, 1.2 FROM DUAL
UNION
select to_char (
decode (
fcq.USER_CONCURRENT_QUEUE_NAME,
'XXXXXX: High Workload',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,53),
'XXXXXX: Standard Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),
'XXXXXX: MRP Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),
'XXXXXX: Payroll Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),
'XXXXXX: Fast Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),
'XXXXXX: Workflow',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),
'XXXXXX: Critical Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),
'Inventory Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),
'Conflict Resolution Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),
null)
||' '||rpad(TO_CHAR(NVL(FCQ.RUNNING_PROCESSES,0)),30)||' '||
rpad(to_char(nvl(FCQ.MAX_PROCESSES,0)),30) ||' '||rpad(to_char(NVL(running,0)),30) || ' '||
to_char(NVL(PENDING,0))) A, 'F' B, 1.3 SRT
from
apps.fnd_concurrent_queues_vl FCQ,
(SELECT nvl(count(*),0) Running, fcwr.concurrent_queue_id
FROM fnd_concurrent_worker_requests fcwr
WHERE fcwr.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)
AND (fcwr.phase_code = 'R')
AND fcwr.hold_flag != 'Y'
AND SYSDATE - fcwr.requested_start_date >= 0.00694444444444444
group by fcwr.concurrent_queue_id ) RUNNING ,
( SELECT nvl(count(*),0) Pending, fcwp.concurrent_queue_id
FROM fnd_concurrent_worker_requests fcwp
WHERE fcwp.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)
AND (fcwp.phase_code = 'P')
AND fcwp.hold_flag != 'Y'
AND sysdate-fcwp.requested_start_date >= 0.00694444444444444
group by fcwp.concurrent_queue_id ) PENDING
WHERE FCQ.concurrent_queue_id=RUNNING.concurrent_queue_id(+)
AND FCQ.concurrent_queue_id=PENDING.concurrent_queue_id(+)
AND fcQ.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)
UNION
select RPAD('-',125,'-') A, 'F' B, 1.4 SRT from dual
UNION
SELECT chr(10)||chr(10) A, 'F' B, 1.5 SRT FROM DUAL
UNION
-----------------------------------------------------------------------
SELECT ' ', 'G', 1.01 FROM DUAL
UNION
select 'CRITICAL PROGRAMS STATUS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),
'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A , 'G' B,1 SRT from dual
UNION
select RPAD('-',125,'-') A, 'G' B, 1.1 SRT from dual
UNION
SELECT to_char( rpad('CONCURRENT PROGRAM NAME',55) || rpad('AVG TIME',20)|| ' ' ||rpad
('CURR MAX TIME',16)||' '|| 'REQUEST_ID'), 'G' B, 1.1 FROM DUAL
UNION
SELECT to_char (
decode (PROGRAM_NAME,
'AutoCreate Configuration Items',RPAD(PROGRAM_NAME,70),
'Memory-based Snapshot',RPAD(PROGRAM_NAME,71),
'Order Import',RPAD(PROGRAM_NAME,80),
'Workflow Background Process',RPAD(PROGRAM_NAME,69),
PROGRAM_NAME) ||' '||
rpad(TO_CHAR(STATIC.AVG_TIME),25) || ' '||
rpad(TO_CHAR(DYNAMIC.CURR_MAX_TIME),25) || ' '||
to_char(NVL(REQUEST_ID,NULL))) A, 'G' B, 1.2 SRT
FROM
(SELECT
CONCURRENT_PROGRAM_ID,
USER_CONCURRENT_PROGRAM_NAME,
REQUEST_ID,
ROUND((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,0) CURR_MAX_TIME
FROM APPS.FND_CONC_REQ_SUMMARY_V fcr
WHERE CONCURRENT_PROGRAM_ID IN (36888,
48681,39442,33137,47730,47731,47712,47729,31881)
and phase_code='C'
AND STATUS_CODE='C'
AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))
AND REQUEST_ID IN (
SELECT MAX(REQUEST_ID) FROM APPS.FND_CONC_REQ_SUMMARY_V fcr WHERE CONCURRENT_PROGRAM_ID
IN (36888,48681,39442,33137,47730,47731,47712,47729,31881)
and phase_code='C'
AND STATUS_CODE='C'
AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))
GROUP BY CONCURRENT_PROGRAM_ID) ) DYNAMIC ,
(select distinct CONCURRENT_PROGRAM_ID "CONCURRENT_PROGRAM_ID",
USER_CONCURRENT_PROGRAM_NAME "PROGRAM_NAME",
DECODE ( CONCURRENT_PROGRAM_ID,36888,39442,33137,31881,10,NULL) AVG_TIME
FROM APPS.FND_CONCURRENT_PROGRAMS_TL fcr
WHERE CONCURRENT_PROGRAM_ID IN
(36888,39442,33137,31881)
AND LANGUAGE='US' ) STATIC
WHERE DYNAMIC.CONCURRENT_PROGRAM_ID(+)=STATIC.CONCURRENT_PROGRAM_ID
UNION
select RPAD('-',125,'-') A, 'G' B, 1.4 SRT from dual
UNION
SELECT chr(10)||chr(10) A, 'G' B, 1.5 SRT FROM DUAL
-----------------------------------------------------------------------
) TEMP
ORDER BY B, SRT, A
Labels: 11i, Common APPS

Read more!






rated 5.0 by 2 people [?]




Posted by Famy Rasheed 0 comments



Prevent Concurrent Requests Executing on New Cloned Environment

Email this | Share on Facebook | Subscribe to this feed


To prevent concurrent requests executing on a newly cloned environment 2 steps will be required.

1) Prevent Concurrent Managers from starting as part of the Clone

2) Change data concerning concurrent requests so they do not execute when the concurrent managers start.

To prevent Applications Services (including Concurrent Managers) from starting automatically during Rapid Clone an enhancement request has been raised to see if this can be considered an option during the clone.
Until this feature is available there is the following workaround.

1. Edit the $COMMON_TOP/clone/bin/adcfgclone.pl

2. Go to the end of the file

3. Change the following lines:-

print "\n Starting application Services for $s_dbSid:\n";
print "Running:\n";
print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/\n");
system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");

TO:

print "\nNOT Starting application Services for $s_dbSid:\n";
#print "Running:\n";
#print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/\n");
#system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");

4. Run "perl adcfgclone.pl appsTier" as normal.

The services will not start Automatically when the clone completes allowing data to be changed safely.
There is a need to change 2 sets of concurrent requests to prevent execution
a) Terminate 'Running' Requests
b) Set Pending jobs to 'On Hold'

a) Set Terminating or Running to Completed/Terminated
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code ='T'
OR phase_code = 'R'
/
b) Place Pending/(Normal/Standby) to On Hold
UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
/
Once these changes have been committed then the Application services including concurrent manager can be restarted and the concurrent jobs will not be executed.
Labels: 11i

Read more!






rated 5.0 by 2 people [?]




Posted by Famy Rasheed 1 comments



Queries Related to Concurrent Requests in 11i Applications

Email this | Share on Facebook | Subscribe to this feed


As part of day to day work, we need to use lot of queries to check the information about concurrent requests. Here are few queries which can be frequently used for day to day works and troubleshooting concurrent request / manager issues.
Note: These queries needs to be run from APPS schema.
Scheduled concurrent requests

Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week).

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

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.

Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday
Checking the duplicated schedules of the same program with the same arguments

The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules.

Note: This query will return even though the request was submitted using a different responsibility.
SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:'
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME
Average pending time per request

This is a very useful query to check the performance of the concurrent managers.

Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests

A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)

We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
ORDER BY 2
Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.
Checking which manager is going to execute a program
The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'
To see all the pending / Running requests per each manager wise
SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
Note: The same information can be seen in Administer Concurrent Manager form for each manager.
Checking the incompatibilities between the programs

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

Read more!






rated 5.0 by 1 person [?]




Posted by Famy Rasheed 0 comments

Wednesday, March 5, 2008

Concurrent manager -- Doc

http://www.dbatoolz.com/scripts_desc.htm --- Concurrent manager/program scripts

Concurrent Processing (CP) / APPS Reporting Scripts
Doc ID: Note:213021.1


Note:200358.1 Oracle Application Object Library Report Review Agent Setup Test
Note 74717.1: Troubleshooting Concurrent Manager Startup problems
Note 97798.1 ORA-904 when starting concurrent managers
Note 230121.1 Routine AFPEIM encountered an error while starting Concurrent Manager on Cloned instance
Note 1059201.6 Status pending with 'Inactive No Manager' cor Concurrent Requests
Note 113135.1 Conflict Resolution Manager FNDCRM Does Not Start; NoError Message.
Note 123607.1 CONCSUB SYSADMIN 'System Administrator' GIVES INVALID RESPONSIBILITY ERROR
Note 2055375.6 RESET PMON METHOD (CHOICES: LOCK, OS, RDBMS)
Note 2069781.6 Basic Troubleshooting of the Concurrent Managers
Note 2120154.6 Unable to start the ccm after install - core dump
Note 171855.1 CCM.sql Diagnostic Script for Concurrent Manager
Note 185036.1 Unable to Startup or Shutdown the Concurrent Managers in 11.5.5 by Using the Syntax adcmctl.sh apps/ Start or Stop