Analyzing CPU patches for Oracle Products

Method 1:

For a EBS instance, search for the string below in the Oracle Support and select the suitable Doc you are looking for.

“Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Knowledge Document”

Example:

Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Knowledge Document (July 2014) (Doc ID 1668237.1)

Method 2:

If you want to know the CPUs released for the various Oracle Products, please check the link below.

http://www.oracle.com/technetwork/topics/security/alerts-086861.html#CriticalPatchUpdates

CPU intensive long running Concurrent Program

Step1: Find the process that are consuming high CPU on DB Node.
oracledb=>prstat
   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
 26477 oracledb  2407M 2369M cpu2     0    0   2:59:40  13% oracle/39
  1958 oracledb  2393M 2355M cpu6     0    0   1:54:27  12% oracle/1
  2124 oracledb  2393M 2355M cpu2     0    0   1:53:35  12% oracle/1
  2202 oracledb  2393M 2355M cpu5     0    0   1:52:07  12% oracle/1



Step2: Identify the PROCESS(Apps Node) from DB.

SQL>select s.sid, s.serial#, s.process, s.program, s.module 
    from v$session s, v$process p
    where s.paddr =p.addr and p.spid in ('26477','2124','1958','2202');

       SID    SERIAL# PROCESS      PROGRAM                                          MODULE
---------- ---------- ------------ ------------------------------------------------ --------------------
        93      35457 17991
       

Step3:On the APPLICATION Server, grep for the PROCESS:
apps=>ps -ef|grep 17991
apps 27982 25127  0 14:14:24 pts/2    0:00 grep 17991
apps 17991  9806  0 10:40:02 ?        0:02 ar60run P_CONC_REQUEST_ID=13318110 P_CORP='055' P_COMPANY='004' P_AS_OF_DATE='1


Step4:Get the Concurrent Request details
select /*+ CHOOSE*/
      'Node Name..............................: ' || q.node_name                          || chr(10) ||
      'Req id.................................: ' || Request_Id                           || chr(10) ||
      'Requestor..............................: ' || User_Name                            || chr(10) ||
      'Manager................................: ' || Q.User_Concurrent_Queue_Name         || chr(10) ||
      'Status code............................: ' || decode(status_code,'A', 'Waiting','B',
      'Resuming','D', 'Cancelled','E', 'Error', 'G', 'Warning', 'H',
      'On Hold', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating',
                      'W', 'Paused', 'X','Terminated', status_code)                                       || chr(10) ||
      'Phase code.............................: '  || decode(phase_code, 'C', 'Completed',
      'I', 'Inactive', 'P', 'Pending', 'R', 'Running', phase_code)                        || chr(10) ||
      'Priority...............................: ' || Fcr.priority                         || chr(10) ||
      'Program................................: ' || Fcp.User_Concurrent_Program_Name     || chr(10) ||
      'Time so far ...........................: ' || trunc((sysdate-Fcr.actual_start_date)*24*60,2)||' min'|| chr(10) ||
      'Avg execution time in  30 days.........: ' ||  trunc(AVG_TIME,2)  ||' min'          || chr(10) ||
      'Max execution time in 30 days..........: ' ||  trunc(MAX_TIME,2)  ||' min'         || chr(10) ||
      'Fastest execution Time in 30 days......: ' ||trunc( MIN_TIME,2) || ' min'          || chr(10) ||
      'Number of executions in last 30 days...: ' ||occurance                             || chr(10) ||
      'ClientPID..............................: ' || Fcr.OS_PROCESS_ID                    || chr(10) ||
      'ServerPID..............................: ' || Fcr.ORACLE_PROCESS_ID     || chr(10) ||
      'Arguments passed to the program .......: ' ||Fcr.argument_text
       from apps.Fnd_Concurrent_Requests Fcr,
    apps.Fnd_Concurrent_Programs_vl Fcp,
    apps.Fnd_Oracle_Userid O,
    apps.Fnd_Concurrent_Processes P,
    apps.Fnd_Concurrent_Queues_vl Q,
    apps.Fnd_User,(select
    concurrent_program_id
    ,count(concurrent_program_id) occurance
    ,min(actual_completion_date-actual_start_date)*24*60 MIN_TIME
    ,max(actual_completion_date-actual_start_date)*24*60 MAX_TIME
    , avg(actual_completion_date-actual_start_date)*24*60 AVG_TIME
    from apps.fnd_concurrent_requests
    where status_code='C' and phase_code='C'
    and trunc(actual_start_date)>trunc(sysdate-30)
    group by concurrent_program_id
    having max(actual_completion_date-actual_start_date)*24*60 >5) CREQ
    where Controlling_Manager = Concurrent_Process_ID
    and ( P.Concurrent_Queue_ID=Q.Concurrent_Queue_ID AND P.Queue_Application_ID = Q.Application_ID )
    and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
    and( Fcr.Program_Application_Id=Fcp.Application_Id
    and  Fcr.Concurrent_Program_Id=Fcp.Concurrent_Program_Id)
    and Requested_By = User_Id
    and Phase_Code = 'R' and status_code in ('R','T')
    --adding joins with new
    and Fcr.Concurrent_Program_Id=CREQ.Concurrent_Program_Id(+)
    Order By   Q.User_Concurrent_Queue_Name,q.node_name, Actual_Start_Date,Request_Id;
Enter value for reqid: 13318110
old  40:     and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
new  40:     and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('13318110')

Node Name..............................: server02
Req id.................................: 13318110
Requestor..............................: USER10
Manager................................: Standard Manager
Status code............................: Normal
Phase code.............................: Running
Priority...............................: 50
Program................................: Some Long Running Program
Time so far ...........................: 218.63 min
Avg execution time in  30 days.........:  min
Max execution time in 30 days..........:  min
Fastest execution Time in 30 days......:  min
Number of executions in last 30 days...:
ClientPID..............................:
ServerPID..............................:
Arguments passed to the program .......: , , , , 055, 004, , , , , 10-JUL-2014, , Merchant Number, , 71, Y, N

Concurrent Program issue --- fndcpesr

http://oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/concurrent-requst-invoking-shellscript-via-fndcpesr-stops-working-why-4133965

Optimizer Statistics

Understanding Optimizer Statistics - White Paper:

http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1354477.pdf






Best Practices for Gathering Optimizer Statistics - White Paper:
www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf

To Remove/Delete Nth line from a Big File

To remove 100th line from a file.
sed -e '100d' oracle.log > oracle_new.log

To remove lines between 100-110 from a file.
sed -e '101,111d' oracle.log > oracle_new.log

To view the content of Nth(200) line in a Big File

Method 1:
oracle=>head -200 filename | tail -1



Method 2:
oracle=>sed '199p;200q;d' filename

Registering PLSQL Stored Procedure in Oracle Apps via Concurrent Program

The purpose of the concurrent program is to drop the table/tables from a specific schema.
The program always holds a default value <Drop all tables older than 1 Month>, which will drop all the tables that are older than one month. Either you can go with the default value or you can provide the program with a valid value (tablename).

STEP 1: Create the Package and Package Body.
create or replace PACKAGE      XXFND_UTILITIES
AS
   PROCEDURE DROP_BKP_TABLES (errbuf           OUT VARCHAR2,
                              retcode          OUT NUMBER,
                              p_bkp_table   IN     VARCHAR2);
END XXFND_UTILITIES;

create or replace PACKAGE BODY      XXFND_UTILITIES
AS
   PROCEDURE DROP_BKP_TABLES (errbuf           OUT VARCHAR2,
                              retcode          OUT NUMBER,
                              p_bkp_table   IN     VARCHAR2)
   IS
      l_found   BOOLEAN := FALSE;

      CURSOR c_bkp_tables
      IS
           SELECT object_name, created, status
             FROM dba_objects
            WHERE object_type = 'TABLE' AND owner = 'XXBKP'
                  AND object_name = UPPER (
                         DECODE (
                            p_bkp_table,
                            '<Drop all tables older than 1 Month>', object_name,
                            p_bkp_table))
                  AND created <
                         DECODE (
                            p_bkp_table,
                            '<Drop all tables older than 1 Month>', SYSDATE- 30,
                            created + 1)
         ORDER BY 1;
   BEGIN
      FND_FILE.
      PUT_LINE (FND_FILE.LOG, ('Parameter: p_bkp_table = ' || p_bkp_table));

      FND_FILE.
      PUT_LINE (FND_FILE.LOG, ('======================================='));

      IF p_bkp_table = '<Drop all tables older than 1 Month>'
      THEN
         FND_FILE.
         PUT_LINE (FND_FILE.LOG,
                   ('Dropping backup tables older than 1 month'));
      END IF;

      FOR c_bkp_table_rec IN c_bkp_tables
      LOOP
         FND_FILE.
         PUT_LINE (FND_FILE.LOG,
                   ('Dropping table XXBKP.' || c_bkp_table_rec.object_name));

         EXECUTE IMMEDIATE 'DROP TABLE XXBKP.' || c_bkp_table_rec.object_name;

         l_found := TRUE;
      END LOOP;

      IF l_found = FALSE
      THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, ('No table(s) found to Drop.'));
      END IF;

      FND_FILE.
      PUT_LINE (FND_FILE.LOG, ('======================================='));
   EXCEPTION
      WHEN OTHERS
      THEN
         errbuf := SQLERRM;
         retcode := '2';
         FND_FILE.
         PUT_LINE (
            FND_FILE.LOG,
            ('Unknown Exception occurred  - ' || SUBSTR (SQLERRM, 1, 100)));
         RAISE;
   END;
END XXFND_UTILITIES;

STEP 2: Register Executable.
System Administrator > Concurrent > Executable



STEP 3: Register Program.
System Administrator > Concurrent > Program


STEP 4: Provide appropriate parameters.
System Administrator > Concurrent > Program:Concurrent Program Parameters


STEP 5: Attach this concurrent program to a Request group.
System Administrator > Security > Responsibility > Request


STEP 6: Submit the Concurrent Program. 
System Administrator >  Concurrent > Requests
 
When Backup Table value is default "<Drop all tables older than 1 Month>"

+---------------------------------------------------------------------------+
Application Object Library: Version : 11.5.0 - Development
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XXFND_DROP_BKP_TABLES module: GP Drop Backup Tables
+---------------------------------------------------------------------------+
Current system time is 03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+
**Starts**03-JUL-2014 09:26:27
**Ends**03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Parameter: p_bkp_table = <Drop all tables older than 1 Month>
=======================================
Dropping backup tables older than 1 month
No table(s) found to Drop.
=======================================
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Executing request completion options...
Printing output file.
               Request ID : 13313415  
         Number of copies : 0  
                  Printer : noprint 
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+

When Backup Table value is "T1", below is log file.

+---------------------------------------------------------------------------+
Application Object Library: Version : 11.5.0 - Development
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XXFND_DROP_BKP_TABLES module: GP Drop Backup Tables
+---------------------------------------------------------------------------+
Current system time is 03-JUL-2014 09:42:14
+---------------------------------------------------------------------------+
**Starts**03-JUL-2014 09:42:14
**Ends**03-JUL-2014 09:42:14
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Parameter: p_bkp_table = T1
=======================================
Dropping table XXBKP.T1
=======================================
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Executing request completion options...
Printing output file.
               Request ID : 13313419  
         Number of copies : 0  
                  Printer : noprint 
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 03-JUL-2014 09:42:15
+---------------------------------------------------------------------------+ 

References:

http://jayantaapps.blogspot.in/2012/10/how-to-registration-pl-sql-stored.html 

http://www.techmandate.com/steps-to-create-a-concurrent-program-of-type-plsql-stored-procedure/