Archive

Archive for July, 2012

Passwordless SSH login

Many times requirement comes from development team that they need to login to remote server for some activity – be it ‘ftp’ or ‘copy’ or ‘writing’ on the remote server. In such cases, it is required that there is seamless and password less login from source server to target server.

I am going to discuss the way to achieve the same…
First of all be clear on

  1. what is the user on source server. say e.g. on application tier it is ‘applmgr’ and source server is A
  2. what is the user on target server – user could be same of different on target server. For my case, i would consider it to be different i.e. ‘wlshop’ and server is B

Step 1: Login to source server A using ‘applmgr’ user

Step 2: Generate public/private key pair using ssh-keygen command. This will prompt you to save the key in the default directory (/home/applmgr/.ssh/id_rsa). It will ask yo put the passsphrase. You may or may not enter the same

Step 3: Now the identification key is saved in /home/applmgr/.ssh/id_rsa and public key is saved in /home/applmgr/.ssh/id_rsa.pub

Step 4: Login to target server B using ‘wlshop’ user and create .ssh directory if not already present

Step 5: applmgr.A> cat .ssh/id_rsa.pub | ssh wlshop@B ‘cat >> .ssh/authorized_keys’. It will ask for wlshop@B password. 

Step 6: Now you can login to B as ‘wlshop’ from server A without being prompted for password

applmgr.A> ssh wlshop@B

 -Anand

 

Categories: Unix/Linux

11i/R12 Concurrent requests going into ‘Inactive’ & ‘No Manager’ status

Couple of days back, got a mail from Development team that their concurrent requests are not being processed in DEV instance. From the front end, requests are showing ‘Inactive’ & ‘No Manager’

I started looking into it. From the historical run of the job, it was showing as processed by ‘Standard Manager’. Hence thought of bouncing Standard Manager. Later on development team reported that their another request (which is also processed by Standard Manager) is going into the same status.

This definitely looked like issue with Standard Manager. But bouncing Standard manager didn’t resolve the issue. Later on I decided to bounce CCM tier but that also didn’t help.

Later on I looked into the specialization rule defined for Standard Manager and found that one of the developers had defined a specialization rule ‘INCLUDE’ for this manager for one of his concurrent program. And this was the root cause of all issue.

A specialization rule such as ‘INCLUDE’ which is used in the standard manager, tells the manager to process ONLY those requests whose job is specified in the INCLUDE rule. It drastically limits which programs the Standard Manager is supposed to process. 

By default all jobs are defined to be handled by Standard Manager unless a specialization rule ‘EXCLUDE’ is explicitly defined. Hence in our case, both the reported requests/jobs are not defined with EXCLUDE in Standard Manager, they were going into ‘Inactive No Manager’ status. ‘INCLUDE’ is NEVER defined for Standard Manager.

Once the ‘INCLUDE’ specialization rule is removed, jobs started getting processed normally under Standard Manager.

Query to check the specialization rule defined for any concurrent program


SELECT decode(A.INCLUDE_FLAG,'I','Included','Excluded'),
       A.QUEUE_APPLICATION_ID,
       C.USER_CONCURRENT_QUEUE_NAME,
       B.CONCURRENT_PROGRAM_NAME
  FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A,
       APPLSYS.FND_CONCURRENT_PROGRAMS      B,
       APPS.FND_CONCURRENT_QUEUES_VL        C
 WHERE type_id = b.concurrent_program_id and b.concurrent_program_name = '&prog_short_name' and c.concurrent_queue_id = a.concurrent_queue_id

-Anand

Categories: Oracle Apps

How to trace in E-Business Suite

July 12, 2012 6 comments

As a DBA, the most challenging task comes when we need to work or diagnose the performance issue. Since I have worked extensively on performance tuning, I would like to share different ways to trace Oracle user session.

Tracing is a very handy tool for any DBA to be the first thing to look for when diagnosing any performance related issues.

  • Tracing one’s own session– many times while working on performance issue, I need to take the trace of my own database session. Steps which I follow
    • alter session set statistics_level=ALL (this is the default behaviour in Oracle 10g going forward)
    • alter session set tracefile_identifier = ‘<some_name_to_identify_session>’
    • alter session set sql_trace true –> this will enable level 4 trace OR
    • alter session set events ‘10046 trace name context forever, level x
      • x=4 –> level 4 trace
      • x=8 –> level 8 trace
      • x=12 –> level 12 trace
    • Run the SQL
    • Once the SQL has finished, disable the trace
      • alter session set sql_trace false OR
      • alter session set events ‘10046 trace name context off’
    • Go to the User dump location (user_dump_dest) on the database server and look for file named “tracefile_identifier”
  • Tracing other user’s database session(You MUST know the user session details – SID and Serial#). Different ways are
    • EXECUTE sys.dbms_system.set_ev(<SID>,<Serial#>,10046,<x>,”)
      • x=4 –> level 4 trace
      • x=8 –> level 8 trace
      • x=12 –> level 12 trace
    • EXECUTE dbms_support.start_trace_in_session(sid=><SID>,serial=><Serial#>,Waits=> true, binds=> true)
  • Tracing in oracle E-Business Suite– Different scenarios are
    • User is complaining that it is taking a lot of time while saving the transaction
      • In order to troubleshoot or isolate the issue, we MUST  know the query that runs in the database when  the user click on ‘SAVE’ button and hence should enable the trace before the ‘SAVE’ button is hit.
        • How to enable/disable trace
          • Login to Oracle application
          • Navigate to the required form (You need to enable the trace immediately before the ‘Save’ button is hit. This is done to ensure ONLY the offending SQLs are captured in the trace file)
          • Once on the Form –> Click on ‘Help’ –> Diagnostic –> Trace and Select any of the five options listed there. It will enable the trace and Oracle will prompt you the trace file identifier and its location as well.
          • Do the problematic transaction and once done, disable the trace (Follow the same navigation and select ‘No Trace’
    • Performance issue is reported by user– Not specific to any specific transaction
      • In such scenarios, we need to trace the complete navigation or User activity to identify the offending SQL .This is achieved by enabling the trace using “profile level tracing”
        • Login  to Oracle application
        • Profile –> System –> Check the ‘USER’ check box –> put the username whose transaction is being traced (NEVER DO THIS at SITE level) –> Query for the profile “Initialization SQL Statement – Custom”
        • Update this value for the USER with “BEGIN FND_CTL.FND_SESS_CTL (”,”,’TRUE’,’TRUE’,”, ‘ALTER SESSION SET TRACEFILE_IDENTIFIER =”<any_identifier>” MAX_DUMP_FILE_SIZE = ”UNLIMITED’ ‘ EVENTS=”10046 TRACE NAME CONTEXT FOREVER, LEVEL <level_no>”’); END;”
          • level_no –> could be 4,8 or 12
          • What this entry does
            • sets up a trace file identifier
            • sets the MAX_DUMP_FILE_SIZE as UNLIMITED
            • Enables the Level X trace
          • CAUTION: Be very careful in updating with the above value since any wrong entry can prevent specific user to login to Oracle application
        • Save this profile setting and ask the user to do the transaction. And once done, again query the profile for the specific user and remove the entry
    • Performance Issue for any concurrent program
      • This is normally done through enabling “Enable Trace” check box after querying the concurrent program
      • This way Level 8 trace is generated
      • For enabling Level 12 trace for any concurrent program, either it can be done from database side (as described above under section “Tracing User session” OR can be done from Oracle application front end. How to do it from Front end
        • Set the profile “Concurrent: Allow Debugging” to YES (ALWAYS DO this at USER level)
        • Login to application using the same responsibility as used to run the concurrent program
        • On the request submission screen, “Debug option” would be enabled (otherwise it is disabled)
        • Click on the “Debug Option” button and select the check box “SQL Trace”
    • Tracing SSWA/OAF application in Oracle E-Business Suite
      • You need to set the profile – “FND:Diagnostic” to YES (ALWAYS do at USER level)
      • Once done, navigate to the OAF page or SSWA page
      • You will see “Diagnostic” link on the top right cornet of the page
      • Click on the link
      • It will give you options to select – select “Set Trace Level”
      • Click on GO
      • Next page will give you the option to select the kind of trace you want and once selected, trace file identified will be displayed on the screen. You need to make a note of this.
      • Now do the required transactions and once done, Disable the trace (Diagnostic–>set trace level –> Disable Trace)
      • Diagnostic link will be visible as long as profile value is set to YES

-Anand

Categories: Oracle Apps

How to bounce Material Transaction Interface manager

July 6, 2012 1 comment

It is necessary to know the concurrent program corresponding to ‘Material Transaction’ Interface manager. This is so because before you launch this interface manager, corresponding concurrent program schedule needs to be cancelled.

Concurrent program corresponding to ‘Material Transaction’ interface manager – “Process transaction interface”

Step 1: Cancel the existing schedule

  • Login using ‘System Administrator’ responsibility
  • Concurrent –> Request –>Specific Request
  • key in the program name
  • Select phase =”Pending” and Status =”Scheduled”
  • Click on ‘Find’
  • ‘Cancel’ the request

Step 2: Relaunch material Transaction Interface Manager

  • Responsibility –> Any INV super user responsibility
  • navigation –> Setup –>Transactions –> Interface Managers
  • Highlight the row with ‘Material Transaction’
  • Navigation –> “Tools” –> Launch
  • Click on “Schedule’ to make it run in certain desired frequency
  • Click on ‘Submit’

-Anand

Categories: Oracle Apps