Estimate database usage - Oracle

One question always haunts me a bit, "Could you provide the database machine requirement?".
Here's one method I often use to estimate the size for Oracle database:
1.  SELECT SUM(BYTES) FROM (
select SEGMENT_NAME, SUM(BYTES)/(1024*1024) BYTES from user_extents
where segment_type = 'TABLE'
and segment_name like 'ACT_%'
GROUP BY SEGMENT_NAME);

select SEGMENT_NAME, SUM(BYTES)/(1024*1024) BYTES from user_extents
where segment_type = 'TABLE'
and segment_name like 'ACT_%'
GROUP BY SEGMENT_NAME;

Tomcat JMX Monitoring

Monitoring Tomcat's resources could be done using the JConsole app which already included in the JDK. In order to monitor Tomcat, we also need to prepare the Tomcat first.

To configure Tomcat in *nix environment, you need to add the parameter below when starting tomcat:

-Dcom.sun.management.jmxremote
  -Dcom.sun.management.jmxremote.port=%my.jmx.port%
  -Dcom.sun.management.jmxremote.ssl=false
  -Dcom.sun.management.jmxremote.authenticate=false

To add the configuration above, one of the way is as below:
1. Open your catalina.sh
2. Add below line (example port is 12300)

3. Restart your tomcat
4. If you need to access it from other computers, make sure the firewall configuration  allows the configured port to be accessed from outside
5. In case the remote connection is not working, try to add below line in the configuration
    -Dcom.sun.management.jmxremote.host=localhost


Reference:
- https://tomcat.apache.org/tomcat-7.0-doc/monitoring.html
- http://docs.oracle.com/javase/6/docs/technotes/guides/management/jconsole.html

Resize LVM Partition in Virtual Box

Here's a note on how to resize LVM partition in VirtualBox
1. VBoxManage modifyhd vdi_path --resize new_size_in_mb
2. Resize the disk.
    a. Run the VM
    b. Open terminal/console and execute command below
        + df
           Write down the logical mapping, in my case it is /dev/mapper/vg_iplus-lv_root
     

     
        + fdisk -l
           Write down the Device name, in my case /dev/sda2

     
 + fdisk /dev/sda
     The next steps will delete the partition to resize, recreate the partition and make it primary. Then resize the partition to the new size and write the changes.
 + Still in fdisk, delete the partition
     input: d
 + Still in fdisk, select the device to partition number. In my case it is 2 (/dev/sda2)
     input: 2
   

  + Still in fdisk, create new partition and select the same partition as in previous steps (e.g. 2)
     Input: n
     Input: 2

  + Set the size for the new partition, set it to occupy all available space (or as preferred)
      Input: enter (twice to set default size)

  + Commit the changes
     Input: w

   + Reboot server
      Command: reboot

   + Resize the physical volume
      Command: pvresize /dev/sda2
     /dev/sda2 refers to the partition changed previously

   + Verify the new size, make sure the new size is applied
       Command: pvscan

   + Extend the logical volume to take all free space
      Command: lvextend -l +100%FREE /dev/mapper/fedora-root

   + Resize the file system
      Command: resize2fs /dev/mapper/vg_iplus-lv_root

   + Verify the new changes

      Command: df

And we're done! :)

Reference:
https://blog.jyore.com/2013/06/virtualbox-increase-size-of-rhelfedoracentosscientificos-guest-file-system/

Find blocking query - Oracle

If you have a query that stops and it seems that it will never end, you might have another query blocking it. Here's one help to find out which query blocking it.

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;

References:
http://www.oraclerecipes.com/monitoring/find-blocking-sessions/