Oracle DDL Trigger on Database

Jephe Wu -

Objective: create DDL trigger on database to audit all DDL statement.
Environment: CentOS 5.5 64bit, Oracle 11g 64bit

1. create a separate tablespace and user called audittrail
2. create trigger on database, record all ddl log to audittrail user ddl_log table.

1. filter _TMP table in JEPHE schema
2. create the following trigger in sys schema
  OWNER        VARCHAR2(30 BYTE),

erno NUMBER;
ermsg VARCHAR2(1500);
Host_name VARCHAR2(50);

if  ora_dict_obj_owner= 'JEPHE'  AND upper(ermsg) = '_TMP' 
INSERT INTO audittrail.ddl_log VALUES(ora_login_user,SYSDATE,ora_sysevent,ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name,OSUSER,MACHINE);

Linux Clustering Concept

Jephe Wu -

Objective: understanding basic Linux clustering concept and tools
Environment: CentOS 5, FreeBSD 8

1. What's the clustering
Connecting two or more computers together in such a way that they behave like a single computer.

2. 2 kinds of clustering 
parallel processing, and  load balancing

Parallel processing means the simultaneous use of more than one CPU to execute a program. Ideally, parallel processing makes a program run faster because there are more engines (CPUs) running it. In practice, it is often difficult to divide a program in such a way that separate CPUs can execute different portions without interfering with each other.

Load balancing means distributing processing and communications activity evenly across a computer network so that no single device is overwhelmed. Busy web sites typically employ two or more web servers in a load balancing scheme.

3. some example of web and application load balancing solutions
a. LVS - Linux Virtual Server at
b. Pound -
c. PF carp and pfsync -

d. tomcat connector -

How to use LVM snapshot to clone CentOS 5 server

Jephe Wu -

Environment: 36G IDE hard disk with CentOS 5.5 default installation which has /boot as /dev/hda1 and the rest are on LVM, this is server A.
Objective: to clone to another same kind of machine by using LVM snapshot. This is server B.
Tools used: RIP(Recovery Is Possible) CD V8.9, create LVM snapshot for / partition before using tar to copy to another machine.

1. use RIP to boot up the destination machine
2. create partitions same as the original one and make file system for all partitions
3. use external hard disk or thumb drive on original machine to extend the volume group
4. create LVM snapshot logical volume on original machine before using tar to copy the whole file system
5. use tar on destinaion machine to copy over the whole file system from original machine
6. install grub on destiniation machine. reboot

1.  use RIP to boot up the server B, choose skip the keyboard map.
2.  setting up the environment of RIP for network use
login as root without password
passwd root
ifconfig eth0 netmask up

3.  copy over the partition configurations from server A (

ssh 'sfdisk -d /dev/hda' | sfdisk [--force] /dev/hda
use fdisk -ul /dev/hda on server A to get the sector layout, then configure it on server B

mkfs -t ext3 /dev/hda1
mkfs -t ext3 /dev/VolGroup00/LogVol00
mkswap /dev/VolGroup00/LogVol01
e2label /boot /dev/hda1

4. create snapshot on server A for intact tar backup over ssh.
4.1 extend the current volume group first
use external hard disk or thumb drive as the extra space for snapshot, as long as the thumb drive can hold the extra changes between the time you created snapshot and the time you finish then delete snapshot, thumb drive doesn't have to be same size as hard disk.

How LVM snapshot works?
As soon as you create a snapshot, LVM creates a pool of blocks. I believe that this pool also contains a full copy of the metadata of the volume. When writes happen to the main volume, the block being overwritten is copied to this new pool and the new block is written to the main volume. This is the 'copy-on-write'. Because of this, the more data that gets changed between when a snapshot was taken and the current state of the main volume, the more space will get consumed by that snapshot pool.

4.2 create snapshot (I used a 4G thumb drive as /dev/sda1)

vgextend VolGroup00 /dev/sda1
vgdisplay -v
lvcreate -n backup -l 126 VolGroup00
mkfs -t ext3 /dev/VolGroup00/backup
mkdir /backup
mount /dev/VolGroup00/backup /backup/
vcreate -l 126 -s -n rootsnapshot /dev/VolGroup00/LogVol00

4.3 to remove snapshot later, do:
umount /backup/
lvremove /dev/VolGroup00/backup
vgreduce VolGroup00 /dev/hda1

pvremove /dev/sda1
vgreduce --removemissing
vgreduce --removemissing VolGroup00

Note: If the physical volume is still used you will have to migrate the data to another physical volume using pvmove.

5.  copying the whole file system to server B
on server B:
mount /dev/VolGroup00/LogVol00 /mnt/hda2
mount /dev/hda1 /mnt/hda2/boot
scp /etc/
scp /etc/
ssh 'cd /backup; tar cvpf - .' | tar xvpf -

6. make grub
cd /mnt/hda2
chroot .
[vgchange -a y] to activate all LVM - optional
grub-install hd0

7. References:

How to use ntop and proxyarp to monitor network traffic

Jephe Wu -

Environment: 2M lease line connecting office and datacenter. CentOS 5.5 bridge firewall with proxyarp enabled, ntop for monitor traffic
Objective: use the existing network segment without any changes, use ntop builtin web server at port 3000 to monitor traffic

Network diagram:
___2M lease line__Router( LAN
__2M lease line__Router( Firewall(CentOS 5.5) eth0: LAN

1. Install CentOS 5.5 32bit on firewall
Use NFS installation(put DVD iso file under /root on NFS server, service nfs restart) and VNC remote install (type in: linux vnc vncconnect=
use same ip address on both eth1 and eth0.

2. enable proxyarp and ip_forward in /etc/sysctl.conf

# Controls IP packet forwarding
net.ipv4.ip_forward = 1
net.ipv4.conf.eth0.proxy_arp = 1
net.ipv4.conf.eth1.proxy_arp = 1

in /etc/rc.local as follows:
echo 1 > /proc/sys/net/ipv4/conf/eth0/proxy_arp
echo 1 > /proc/sys/net/ipv4/conf/eth1/proxy_arp
echo 1 > /proc/sys/net/ipv4/ip_forward

3. modify ip route

adding the following into /etc/rc.local

ip route del dev eth0
ip route del dev eth1
ip route add dev eth1
ip route add dev eth0
route add default gw eth1
ntop -i eth1 >/dev/null 2>&1 &

4. OS update

vi /etc/yum.conf (put proxy=
yum -y update
vi /etc/grub.conf to use the latest kernel without Xen

5. Install ntop
search google for 'DAG', go to to install rpmforge rpm
rpm -Uvh rpmforge*.rpm
yum install ntop

6. test

use ping, arping, ssh etc to test server/pc in can reach the other end, as well as datacenter servers.

If  things are not working, you might need to delete arp entries for your gateway from your own pc or gateways:
arp -d
arp -na

7. use browser  to access

8. References:


How to access office server and admin desktop from home

Jephe Wu - 

Objective: assume you have a  admin Windows xp pro desktop in office which is running 24 hours.  You need to access company Linux servers and the admin Windows pc from home.
Environment:  assume your office has lease line connecting to Internet, also 2 level of squid proxy server (parent proxy concept) running on the LAN for users to access Internet, Windows XP pro admin desktop pc and CentOS servers.

Connection diagram:
office lan - squid proxy - squid parent proxy
--internet firewall -[cloud] - Singapore ISP(starthub)--
home cable modem - wireless router - home Linux server + home Windows pc

Method 1: Directly SSH, VNC or RDP port forwarding on Internet firewall
You can do port forwarding for ssh,VNC or RDP on Internet firewall directly to your Linux admin server and Windows xp pro admin pc, use ssh public key authentication.

Other ways: VNC listening viewer mode, openvpn, hosted vpn such as LogMeIn Hamachi.

Method 2:  Teamviewer or logmein free edition
You can just let the admin Windows pc to have Internet connection, then install teamviewer or logmein on that PC.

Method 3: openssh/putty + proxytunnel +[Apache mod_proxy]
a. openssh + proxytunnel
Assume your 2 squid proxy has enabled port 22 for SSL
acl SSL_ports port 443 22
acl Safe_ports port 443 22

squid -k reconfigure

Configure home wireless router to port forwarding port 22 to your home Linux/Windows cygwin ssh server at port 22.

At home, configure ssh server /etc/ssh/sshd_config or /etc/sshd_config in cygwin (GatewayPorts yes)

In office, use the following ssh_config configuration (/etc/ssh/ssh_config): download proxytunnel v1.9 rpm and installed on admin linux server first.

Host jephe
    ServerAliveInterval 60
    ServerAliveCountMax 600

    Proxycommand /usr/bin/proxytunnel -p -d

Run the following command to ssh into home Linux/Windows pc:
ssh -v -R -R 2222:localhost:22  [-L 8080:] [-g] 

(-g means allows remote hosts to connect to local forwarded ports)
(-L part means you can connect to linux admin server at port 8080 which will be tunnelled to home wireless router admin access page)

How to connect to office from home:
use rdp to connect to localhost for office admin pc remote desktop. 
use putty to connect to localhost:2222 for ssh into office linux admin pc

note: Windows 7 Professional 64bit users:
a. You need to manually allow tcp port 22 incoming in Windows firewall incoming rules configuration
b. You need to allow remote desktop connection (right click computer, properties, advanced system settings,remote, choose users if necessary)
c. You cannot use port 3389 and 3390 for port forwarding at localhost, use 3391 instead.
so, after ssh, you should use localhost:3391 for connecting to office desktop pc.

I have tried cygwin 1.7.7 on Windows xp for remote port portforwaring which is also working, without proxytunnel and http proxy. 

b. openssh+nc
you can use the following in the /etc/ssh/ssh_config

Host test
    hostname dest_server_ip
    ProxyCommand ssh jephe@firewall_ip nc -w 1 %h %p

c. putty + proxytunnel
According to my test, putty can use http proxy option or external program proxytunnel. I tried both options, the remote port forwarding part doesn't work(putty 0.60 and latest snapshot,also Xshell, CentOS 5.5 openssh server), the normal port forwarding through putty is working, which means you can access home pc and wireless router when you are in office, but not office pc and linux admin server when you are at home.

Putty's http proxy:
Just specify the squid proxy ip and port number, it will be able to tunnel your ssh connection through squid proxy:

go into the Connection > Proxy menu. Select the Local proxy type. And then provide as Telnet command, or local proxy command the following line:
proxytunnel -q -p [-r] -d %host:%port

-r part is used for Apache mod_proxy, refer to Reference 1 link.

Note: another options for http proxy is

zmodem file transfer within Leputty( and xshell

2. proxytunnel homepage:

3. tunnel ssh over http(s):
4. use corkscrew tool
5. HowTo SSH outside using Authenticated Proxy

How to mount ntfs partition under CentOS

Jephe Wu -

1.  CentOS 5
Refer to
If your version is CentOS 5.4 and above, you can install rpmforge repo first, then run yum install fuse fuse-ntfs-3g

For installing rpmforge repo, go to to install it

If you are using proxy server, use command rpm --httpproxy --httpport 8080 -Uvh URL

If your version is CentOS 5.3 and below, you can install kmod-fuse from ELRepo (refer to
So, firstly, install ELRepo as follows:

rpm --import
To install ELRepo for RHEL-5, SL-5 or CentOS-5:
rpm -Uvh

after that, go to /etc/yum.repo.d/, vi file elrepo.repo to enable them, then run 'yum install kmod-fuse' to install fuse module.
or yum --enablerepo=elrepo kmod-fuse, so you don't have to edit file to enable them.

mounting ntfs:

mount -t ntfs-3g /dev/sda1 /mnt/windows


1. ntfs-3g

How to map ftp/sftp drive for remote server from Windows/Linux

Jephe Wu -

1. Perfect way - map sftp server as Windows drive letter

expandrive - (30days trail)
webdrive -  (20 days trial)

Directnet drive - (free for home user only)  --it hung my Windows Vista Home Basic laptop

2. second option - ftp and webdav
Windows builtin functionality - map network drive for ftp server (lack of drive letter functionality)
note: choose tools/map network drive/signup online storage or connect to a network server/choose another network location, specify the address of a web site, network location or ftp site
netdrive -  ( with drive letter and it is free for non-commercial use. )
Directnet drive - (with drive letter and it's free for non-commercial use)

How to troubleshoot packet loss and latency for Internet VPN

Jephe Wu -

Objective:  use all kinds of open source free softwares to troubleshoot the Internet vpn slowness issue and pinpoint where is the packet loss router.
Environment: OpenBSD, Freebsd as vpn firewall. When accessing servers such as web servers, Oracle database servers through Internet vpn, we experiencd very slow connection.


The following is the vpn network diagram:

1. Check latency and packet loss from host to

a. ping check if you are able to ping from to, if yes, check the latency and packet loss rate.
The latency is not accurate because it might be due to icmp rate limiting confiugred by router, only tcp traffic is in priority.
b. if the ping is blocked, use tcptraceroute or traceroute -T (on CentOS 5) and tracetcp on Windows (, the latency here is more accurate as it's real tcp traffic from sender to receiver, although the return traffic is icmp TTL exceeded message.

How does tcptraceroute and tracetcp work?

When you issue command like 'tracetcp', Wireshark captures the traffic below, for each hop, it will send 3 tcp packets and set TTL starting from 1. When the final destination reached and it gets ACK reply from destination host, it will immediately tear down the connection.

13    3.732592000    TCP    20527 > https [SYN] Seq=0 Win=16383 Len=0  (time to live is 1 in ip header)   
14    3.734182000    ICMP    Time-to-live exceeded (Time to live exceeded in transit)
15    4.232399000    TCP    24043 > https [SYN] Seq=0 Win=16383 Len=0  (time to live is 1 in ip header)   
16    4.241227000    ICMP    Time-to-live exceeded (Time to live exceeded in transit) 
17    4.732323000    TCP    13233 > https [SYN] Seq=0 Win=16383 Len=0  (time to live is 1 in ip header)   
18    4.735323000    ICMP    Time-to-live exceeded (Time to live exceeded in transit)     

2. use mtr or pathping to check packet loss rate

You can install mtr ( on Linux/FreeBSD/OpenBSD to check the packet loss rate for the trace path. There are also winmtr and pathping on Windows for similiar functionality.

MTR relies on ICMP Time Exceeded (type 11) packets coming back from routers, or ICMP Echo Reply packets when the packets have hit their destination host.

2082    191.558234    ICMP    Echo (ping) request
2083    191.590458    ICMP    Time-to-live exceeded (Time to live exceeded in transit)
2084    191.673071    ICMP    Echo (ping) request
2085    191.804462    ICMP    Echo (ping) request
2086    191.874170    ICMP    Time-to-live exceeded (Time to live exceeded in transit)
2090    191.804462    ICMP    Echo (ping) request
2091    174.139518    ICMP    Echo (ping) reply

Note: mtr will send icmp ping request with incremental TTL value starting from 1 to the destination host, by getting reply from each hop to get round trip time and packet loss rate.

3. the importance of having no/low packet loss and how to read the mtr report for packet loss

a. Packet loss kills throughput.
b. a slower connection with zero packet loss can easily outperform a faster connection with some packet loss
c. packet loss on the last hop, the desination, is what is most important; packet loss will happen on the return path which is totally different with the outgoing path.
d. sometimes routers in-between will not send ICMP "TTL expired in transit" messages, it will see 3 asterisk which is normal.
e. some routers may specifically block (or down-prioritize) ICMP echo requests, or might do the same where TTL=0. These routers (or the final destination) might show 100% packet loss
f. the router may also be programmed to limit the number of responses it sends to ICMP packets in an effort to mitigate DoS attacks
g. just because you see a hop with high loss doesn't mean it's slowing down "real" traffic; it may only be throwing away ICMP.

2.   --for how to read mtr report

4. how to configure vpn firewall for icmp traffic in OpenBSD or FreeBSD packet filter firewall

pass out log quick
pass in log quick on $ext inet proto icmp all icmp-type { echorep, timex, unreach }
pass in log quick on $ext inet proto udp from to $ext keep state
pass in log quick on $ext inet proto icmp all icmp-type { echo } from to $ext keep state

ICMP filtering on the firewall -