Friday, 4 July 2014

Packt celebrates 10 years with a special $10 offer

 A note from publisher of my book...

This month marks 10 years since Packt Publishing embarked on its mission to deliver effective learning and information services to IT professionals.

To celebrate this huge milestone, Packt is offering all of its eBooks and Videos at just $10 each for 10 days – this promotion covers every title and customers can stock up on as many copies as they like until July 5th.


http://bit.ly/1q9nmAG

Tuesday, 17 June 2014

Enabling SQL Trace in Oracle E-Business Suite


Enabling SQL Trace in Oracle E-Business Suite

Steps for Forms Based Applications

  1. Log in to the environment from which you want to trace
  2. Set the value for profile option "Utilities:Diagnostics" to "Yes" at user-level for your application account.
  3. Choose your responsibility, and navigate to the starting point of the form when you want to begin tracing.
  4. Turn on Tracing by using the menu option: Home > Diagnostics > Trace > Trace with waits
  5. A popup note with the trace file name and location is displayed. Note the trace filename. The location will be on the database server.
  6. Proceed with the application steps that need to be traced. Once finished tracing, exit the application.
  7. Retrieve the raw trace file using the filename from Step 5
  8. Format the raw trace file with TKPROF.

Steps for HTML/Self-Service Based Applications

  1. Log in to the environment from which you want to trace
  2. Set the value for the profile option €œFND:DIAGNOSTICS to "Yes" at userlevel.
  3. Log into Self-Service Applications of that environment
  4. Click the 'Diagnostics' icon at the top-right of the page.
  5. Select 'Set Trace Level' radio button and click 'Go'.
  6. Select 'Trace with waits' (recommended) and click 'Save'
  7. Select 'Home' and proceed to perform your screen processing.
  8. Disable trace once you are finished tracing your screens, with these steps:
    Click on 'Diagnostics' icon > Set Trace Level > Disable Trace
  9. Write down the 'Trace Ids' shown on the left side of the screen (note that tracefile_identifier is by default set to the userid.
  10. Log out of the application.
  11. Retrieve the raw trace file using the process ids from Step 9 or the tracefile_identifier (set to userid by default)
  12. Format the raw trace file with TKPROF.

Tracing Concurrent Programs

Steps to enable trace for Concurrent Requests: Request Level
  1. Log in to the environment from which you want to trace.
  2. Set the value for the profile option Concurrent: Allow Debugging œto "Yes" at user level.
  3. Choose the appropriate responsibility and concurrent program to be executed.
  4. Click on the "Debug Options" button.
  5. Enable tracing by selecting the "SQL Trace" Check box, and choose the trace level from the DropDown list. Confirm your selection by clicking the "OK" button.
  6. Submit the concurrent program.

    [Perform Steps 1-6 for each individual concurrent request that needs to be traced.]
  7. Write down the request_id of your concurrent program job.
  8. Once the concurrent request completes, retrieve the raw trace file using the request_id (from Step 7) or the tracefile_identifier (set by default to the userid).
  9. Format the raw trace with TKPROF
The Debug Options button used for tracing individual requests is not available for request sets. You must set the Initialization SQL Statement Custom profile option method (described below) in order to trace request sets. With this method, a trace file will be produced for each program within the request set.
Steps to enable trace for Concurrent Requests - Program Level
  1. Log in to the environment from which you want to trace.
  2. Choose an appropriate responsibility and select the Concurrent > Program > Define screen.
  3. Press [F11] to enter query mode and search for the concurrent program. Press [CTRL][F11] to execute the search.
  4. Check the 'Enable Trace' box to turn on tracing for the concurrent program. Note that once checked, anyone who runs this concurrent program will produce a level 8 (wait events) trace.
  5. Submit and run the concurrent program.
  6. Write down the request_id of your concurrent program job.
  7. Go back to the Concurrent > Program > Define screen and uncheck the enable trace box for this concurrent program.
  8. Retrieve the raw trace file using the request_id (from Step 6) or the tracefile_identifier (set by default to the userid).
  9. Format the raw trace file with TKPROF.

Profile-Based Tracing

An alternative method to enable trace from any location at the application user level is to set the profile option "Initialization SQL Statement - Custom" at user level, as described next.
  1. Log in to Forms for the desired application.
  2. If you are activating trace for your own account, navigate to the Profile > Personal menu option, press F11 to query, enter "Initialization%" in the Profile Name column, then press Ctrl-F11 to search for this profile option. If you are enabling trace for another user, select +Profile >> System ('Find System Profile Values' screen will pop up).
  3. Check 'User' and Type in the Username (in which the account for that user will be traced).
  4. Enter 'Initialization%' in the Profile box and press 'Find'.
  5. In the User box for "Initialization SQL Statement - Custom", enter the following statement and then press 'Save':
    BEGIN
    FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER=''TESTING'' MAX_DUMP_FILE_SIZE=unlimited EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 8''');
    END;
    It is advisable to specify an identifier to help identify your trace. In this case, TESTING will appear at the end of the trace file name. Quotes in the statement are all single quotes.
  6. Log in to the application and page you wish to trace with a suitable username/password, and start your flow or submit your request set. Everything will be traced.
  7. Log out of the application once you completed your flow.
  8. Go back to the Profile option in the Forms application, delete the Initialization SQL statement, and press 'Save'.
  9. Identify and retrieve the trace file using the tracefile_identifier specified in Step 5.
  10. Format the raw trace file with TKPROF.

SQL*Plus-Based Tracing

  1. Log in to SQL *Plus
  2. Run the following query to find out the server process id for your session:
    SQL>select p.spid
    from v$session s, v$process p where s.paddr = p.addr and s.audsid = to_number(sys_context('USERENV','SESSIONID'));
  3. Write down the server process ID (Server PID).
  4. Turn on and configure tracing by executing the following commands in your SQL *Plus session:
    SQL>alter session set tracefile_identifier='';
    SQL>alter session set events '10046 trace name context forever, level 8';
    SQL>alter session set statistics_level='ALL';
    SQL>alter session set max_dump_file_size= unlimited;
  5. Declare and populate the necessary bind variables used by the statement, for example:
    SQL>variable B1 number;
    SQL>variable B2 varchar2(50);
    SQL>exec :B1 := 123456;
    SQL>exec :B2 := 'ABC';
  6. Run the SQL or PL/SQL code to be traced using the same bind variables as would be used by the application.
  7. Once completed, run the following command, then exit the SQL *Plus session:
    SQL>exec dbms_session.reset_package;
  8. Identify and retrieve the trace file using the process_id from Step2 or tracefile_identifier set in Step 4.
  9. Format the raw trace file using TKPROF.

Collecting Diagnostic Data for Performance Issues in Oracle E-Business Suite (Doc ID 1121043.1)





 

Oracle Innovation Management

Monday, 9 June 2014

Delete files in Linux (when there are too many files in a directory and ls command is not responding)

Useful commands in Linux


An easy way to delete files in Linux especially when there are too many files in a directory and ls command is not responding or is very slow.


find $ORACLE_HOME/rdbms/audit  -name "*.*" -mtime +15 -type f -delete &


How to display latest files, sorted by size
ls -ltS