Modern Oracle DBA

  Step 1 : Login the application Step 2: Use below script $FND_TOP/sql/afsvcpup.sql Cd $FND_TOP/sql/ Sqlplus apps/apps SQL> @ afsvcpup.sql Select component id for Workflow Notification Mailer - default is 10006 Enter Component Id: 10006 Show you the parameters and their values for the mailer Select the comp param id for the Override address (it will say Test Address ) - default is 10093 This script will display the following: You have selected parameter: Test Address Current value of parameter: NONE Enter a value for the parameter: <Here you will put the email address > Retest the Workflow mailer and confirm the override now works as expected.   Using SQL Query’s to set email address First check the current address use below query SELECT fscpv.parameter_value FROM fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv WHERE fscpt.display_name = 'Test Address' AND fscpt.parameter_id = fscpv.parameter_id;   Update email...

  If we forget the apps password for r12.2 instance follow the below steps to recover the password Step 1 : Login to Database server sqlplus / as sysdba STEP 2 : Create Function for to decrypt the encrypted password SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; / Function created. STEP 3 : Query for encrypted password SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST'; Output ENCRYPTED_FOUNDATION_PASSWORD -------------------------------------------------------------------------------- ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A STEP 4: Query for decrypt the password SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG37E123746712BDB2D99...

This ORA Error post will update frequently for same page  ORA-00031: session marked for kill. The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done. No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner. Solution Find the thread in OS level select vs.sid,vs.username,vs.osuser, vs.process,vp.spid from v$session vs, v$process vp where vs.paddr = vp.addr and vs.username='<User_name>' and vs.osuser='<applmgr>'; Will get output like this SID    USERNAME     OSUSER   PROCESS   SPID 123        AP              applm...

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 ...

Top Command Analysis Top command is used to show the Linux process This command shows the summary information of the system and the list of process which are currently managed by the kernel Top command we need to check two session summery session and task session Summery session System Time, Up time and user session Load average Tasks CPU usage Memory Usage System Time, Up time and user session top - 11:59:39 up 225 days, 22:31, 136 users, System time: 11:59:39 Up time: up 225 days, 22hrs:31min User session: 136 users connected. Load average Load average is the system load calculated over given period 1,5 and 15 minutes Load average have 3 sessions Load average: 4.43, 4.14, 4.03 Load average over last 1 minutes is 4.43 Load average over last 5 minutes is 4.14 Load average over last 15 minutes is 4.03 The high load average that system is overloaded many processes are waiting for CPU time Tasks Tasks session showing how many processes runni...