Oracle E-Business Suite Technology Webcasts

https://blogs.oracle.com/stevenChan/entry/e_business_suite_technology_learning

Migration Of Non-ASM 11g Database To ASM

http://orasteps.wordpress.com/2013/04/23/migration-of-non-asm-11g-database-to-asm/

Adding & Configuring a new disk to a VMWare Virtual Machine in Linux

Source: http://www.matttopper.com/2006/05/adding-a-new-disk-to-a-vmware-virtual-machine-in-linux/comment-page-5/
 
http://download.parallels.com/desktop/v9/ga/docs/en_US/Parallels%20Desktop%20User%27s%20Guide/27773.htm



Step 1: Open virtual machine settings
Select your virtual machine, as you can see from the photo I selected the Infrastructure virtual machine. Next press the “Edit virtual machine settings’ to open the Virtual Machine Settings dialog. 

Step 2: Add new hardware
From the “Virtual Machine Settings” dialog select the “Add…” button at the bottom of the screen. From this dialog you can also modify how much memory you dedicate to the machine when it boots.
Step 3: Select new hard disk
From this screen we can see the many types of hardware we can add to a virtual machine. You can emulate just about any piece of hardware that one can expect in a modern operating system. It definitely makes testing with different configurations and devices much easier. For our example we want to select “Hard Disk” and then select the “Next >” button.
Step 4: Create the virtual disk
In the next screen we see the three options for adding a new disk. We can “Create a new virtual disk”, this will create a brand new disk on the guest operating system. The second option, “Use an existing virtual disk”, allows you to mount a disk from another virtual machine. I like to do this with my “source” drive. I have one virtual disk that I’ve made that has all the Oracle and Linux CDs on it, that way I can just mount it to the machine I need when I have to do a new install instead of copying the binaries I need across disks, its definitely a big time saver. The last option is to “Use a physical disk”, this allows you to mount a local physical disk to the operating system. This option is akin to NFS mounting a drive to a virtual machine. To add a new disk we select the “Create a new virtual disk” option and select the “Next >” button.
Step 5: Select type of disk
Next we want to select the type of disk. I’ve been using VMWare for a long time and agree that the recommended Virtual Disk Type should be SCSI. I don’t know why, but I’ve had much better success with the SCSI virtual disks than the IDE ones. So in this step we want to select “SCSI (Recommended)” and the “Next >” button.
Step 6: Set disk size and options
Now we want to set the size of the disk we are creating. One of the nice features of VMWare is that you don’t have to allocate all of the disk when you create it. So if you create a 40 GB disk it doesn’t have to take it all right away, the disk will grow as your virtual machine needs it. I will say this is a big performance hit you take when the disk has to extend, but for most applications its OK. Also, I will warn that if the virtual disk grows and there is no physical disk left on the host operating system you will see a catastrophic failure and in most cases both the host and guest operating systems lock up and become unusable. (Don’t say I didn’t warn you) Lastly, you can split the files into 2GB sizes, while this isn’t necessary, it just makes all the disks much easier to manage and move around. For this step we want to set our disk size (12 GB in this case), I chose not to allocate the disk space right now (the machine has a 300 GB drive and has only 20 GB on it) and Split disk into 2 GB files.
Step 7: Name the disk file
This is actually pretty simple in that you decide what you want to physically call the disk and where to put it. .vmdk is the extension for VMWare virtual disks. After we name the disk we can select the “Finish” button which adds the disk to the virtual machine.
Step 8: Ensure new disk exists
So now we can see that the new disk has been added to the “Virtual Machine Settings” within the selected virtual machine. From here the disk acts just like it would if you added a new disk to a standalone server. So we select the “OK” button to continue.
Step 9: Boot the virtual machine
From here we just start the virtual machine like we would normally, either by selecting the button on the toolbar or selecting the “Start this virtual machine” link.
Step 10: Virtual machine start up
The machine boots normally as it would any other time.
Step 11: Create the Partition
After we’ve logged in and accessed a terminal window as root (or another user with root/sudo privs) we first want to run fdisk on the newly created drive. In Linux the first SCSI drive is sda, the second sdb, the third sdc, etc. since this was the second SCSI drive we added to the system, the device is known as /dev/sdb
The first command we want to run is fdisk /dev/sdb (NOTE: Thanks to everyone that caught my typo here) this utility works very much like the DOS utility of the old days and allows you to create and manage partitions. To create a new partition we enter the command n to create a new partition. This is going to be a primary partition p, and the first partition number 1. Because I want this disk to consume the full 12 GB I specified earlier we start at the first cylinder and end it at the last cylinder. We then want to write the partition table with the new partition we have just created so we enter the command w which writes the new table and exits fdisk.
Step 12: Format the partition
Now that we’ve create the partition, we now want to format the first with the new file system. I’ve decided to use ext3 filesystem for this disk, ext3 provides all the features of the classic ext2 file system plus journaling which helps to prevent disk corruption in the event of an improper shutdown and speeds up the recovery process. For a good overview of Linux standard file systems check out this article: http://linux.org.mt/article/filesystems So, to format the new partition we enter the command mkfs -t ext3 /dev/sdb1. This command makes a new files system with the type t ext3 on the /dev/sdb1 partition, this is the first partition on the sdb disk.
Step 13: Create the mount point
Determine where you want to add the new virtual disk you’ve created. I like to create a partition specifically for all the software I install after the basic Linux install called /software to do that we run mkdir /software, just a simple make directory command. Once that is complete we then want to mount the newly created partition. Because we haven’t added the partition to the /etc/fstab yet we have to mount it manually. To do that we run mount -t ext3 /dev/sdb1 /software. To break down this command we run mount with the ext3 filesystem type, the partition /dev/sdb1 to the directory /software. Pretty simple and straight forward. To check that the partition is properly mounted we run df -k which shows us the mounted partitions and the amount of available space.
Step 14: Open the fstab file
The fstab file holds all of the used disks and partitions, and determines how they are supposed to be used by the operating system. So we edit the file to add the newly created partition
http://www.matttopper.com/images/blog/adding_disk_to_vmware/15.jpg
Step 15: Modify the fstab for the new partition
After we open the fstab file in the previous step we add the following line: /dev/sdb1 /software ext3 defaults 1 1
The first column is the partition name, the second is the default mount point, the third is the filesystem type. The fourth is the mount options, in this case I used default which mounts the drive rw, suid, dev, exec, auto, nouser and asynchronous. The 5th and 6th options are for the dump and fsck options. If dump is set to 1 the filesystem is marked to be backed up, if you are going to have sensitive material on the drive its a good idea to set it to 1. If fsck is set to greater than 1, then the operating system uses the number to determine in what order fsck should be run during start up. If it is set to 0 it will be ignored such as in the case of a cdrom drive since its a solid state disk. For more information on the fstab file check out this article: http://www.tuxfiles.org/linuxhelp/fstab.html
Lastly, we write and quit the file with the :wq command.
So now that the fstab has been written the drive will be mounted and unmounted when the machine is either started or shutdown. So there you have it, the quick and dirty process for adding a brand new disk to a virtual machine. Until next time…

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/