Jephe Wu - http://linuxtechres.blogspot.com
Objective: create DDL trigger on database to audit all DDL statement.
Environment: CentOS 5.5 64bit, Oracle 11g 64bit
Steps:
1. create a separate tablespace and user called audittrail
2. create trigger on database, record all ddl log to audittrail user ddl_log table.
Trigger:
1. filter _TMP table in JEPHE schema
2. create the following trigger in sys schema
CREATE TABLE audittrail.DDL_LOG
(
USER_NAME VARCHAR2(30 BYTE),
DDL_DATE DATE,
DDL_TYPE VARCHAR2(30 BYTE),
OBJECT_TYPE VARCHAR2(25 BYTE),
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
CLIENT_USER VARCHAR2(40 BYTE),
MACHINE VARCHAR2(40 BYTE)
);
CREATE OR REPLACE TRIGGER DDL_LOG AFTER DDL ON DATABASE
DECLARE
MACHINE VARCHAR2(400);
OSUSER VARCHAR2(40);
IPINFO VARCHAR2(30);
IP1 VARCHAR2(30);
NO1 NUMBER(1);
erno NUMBER;
ermsg VARCHAR2(1500);
USER_INFO VARCHAR2(50);
SIDINFO VARCHAR2(50);
Host_name VARCHAR2(50);
USER_NAME VARCHAR2(25);
MACHINE_NAME VARCHAR2(40);
BEGIN
SELECT sys_context('USERENV','IP_ADDRESS') INTO IPINFO FROM DUAL;
SELECT SYS_CONTEXT('USERENV','TERMINAL') INTO MACHINE FROM DUAL;
SELECT sys_context('USERENV','OS_USER') INTO OSUSER FROM DUAL;
ermsg:=SUBSTR(ora_dict_obj_name,LENGTH(ora_dict_obj_name)-3,LENGTH(ora_dict_obj_name));
if ora_dict_obj_owner= 'JEPHE' AND upper(ermsg) = '_TMP'
then
null;
else
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);
END IF;
END;
/
Oracle DDL Trigger on Database
Linux Clustering Concept
Jephe Wu - http://linuxtechres.blogspot.com
Objective: understanding basic Linux clustering concept and tools
Environment: CentOS 5, FreeBSD 8
Concept:
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 http://www.linux-vs.org
b. Pound - http://www.apsis.ch/pound/
c. PF carp and pfsync - http://www.openbsd.org/faq/pf/carp.html
d. tomcat connector - http://tomcat.apache.org/connectors-doc/
How to use LVM snapshot to clone CentOS 5 server
Jephe Wu - http://linuxtechres.blogspot.com
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.
Concept:
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
Steps:
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 10.0.0.2 netmask 255.255.255.0 up
/usr/sbin/sshd
3. copy over the partition configurations from server A (10.0.0.1/24)
ssh 10.0.0.1 'sfdisk -d /dev/hda' | sfdisk [--force] /dev/hda
or
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
or
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 10.0.0.1:/etc/passwd /etc/
scp 10.0.0.1:/etc/group /etc/
ssh 10.0.0.1 'cd /backup; tar cvpf - .' | tar xvpf -
6. make grub
cd /mnt/hda2
chroot .
[MAKEDEV hda]
[vgchange -a y] to activate all LVM - optional
grub-install hd0
7. References:
http://www.howtoforge.com/linux_lvm_snapshots
How to use ntop and proxyarp to monitor network traffic
Jephe Wu - http://linuxtechres.blogspot.com
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:
before:
___2M lease line__Router(10.0.0.254)___10.0.0.0/24 LAN
after:
__2M lease line__Router(10.0.0.254)__eth1:10.0.0.253 Firewall(CentOS 5.5) eth0:10.0.0.253__10.0.0.0/24 LAN
Steps:
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=10.0.0.200).
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
or
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 10.0.0.0/24 dev eth0
ip route del 10.0.0.0/24 dev eth1
ip route add 10.0.0.254 dev eth1
ip route add 10.0.0.0/24 dev eth0
route add default gw 10.0.0.254 eth1
ntop -i eth1 >/dev/null 2>&1 &
4. OS update
vi /etc/yum.conf (put proxy=http://10.0.0.1:8080)
yum -y update
vi /etc/grub.conf to use the latest kernel without Xen
reboot
5. Install ntop
search google for 'DAG', go to http://dag.wieers.com/rpm/FAQ.php#B to install rpmforge rpm
wget http://apt.sw.be/redhat/el5/en/i386/rpmforge/RPMS/rpmforge-release-0.3.6-1.el5.rf.i386.rpm
rpm -Uvh rpmforge*.rpm
yum install ntop
6. test
use ping, arping, ssh etc to test server/pc in 10.0.0.0/24 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 10.0.0.254 from your own pc or gateways:
arp -d 10.0.0.254
arp -na
7. use browser to access http://10.0.0.253:3000
8. References:
1. http://alan.blog-city.com/linux_bridgefirewall_with_proxy_arp.htm
How to access office server and admin desktop from home
Jephe Wu - http://linuxtechres.blogspot.com
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
Other ways: VNC listening viewer mode, openvpn, hosted vpn such as LogMeIn Hamachi.
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 10.0.0.2:8080 -d jephe.domain.com:22
Run the following command to ssh into home Linux/Windows pc:
ssh -v -R 3389:adminpc.jephe.com:3389 -R 2222:localhost:22
[-L 8080:192.168.10.1:80] [-g] note:(-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
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.
note:
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:
Proxytunnel:
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 squidproxy.jephe.com:8080 [-r jephe.apacheserver.com:443] -d %host:%port
-r part is used for Apache mod_proxy, refer to Reference 1 link.
Note: another options for http proxy is http://www.agroman.net/corkscrew/
note:
zmodem file transfer within Leputty(http://leputty.sourceforge.net/) and xshell
References:
2. proxytunnel homepage: http://proxytunnel.sourceforge.net/intro.php
3. tunnel ssh over http(s): http://dag.wieers.com/howto/ssh-http-tunneling/
4. use corkscrew tool
http://www.agroman.net/corkscrew/
http://daniel.haxx.se/docs/sshproxy.html
5. HowTo SSH outside using Authenticated Proxy
http://www.sohailriaz.com/howto-ssh-outside-using-authenticated-proxy/
How to mount ntfs partition under CentOS
Jephe Wu - http://linuxtechres.blogspot.com
Steps:
1. CentOS 5
Refer to http://wiki.centos.org/TipsAndTricks/NTFS
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 http://dag.wieers.com/rpm/FAQ.php#B to install it
If you are using proxy server, use command rpm --httpproxy 10.0.0.1 --httpport 8080 -Uvh URL
If your version is CentOS 5.3 and below, you can install kmod-fuse from ELRepo (refer to
http://elrepo.org/tiki/tiki-index.php)
So, firstly, install ELRepo as follows:
rpm --import http://elrepo.org/RPM-GPG-KEY-elrepo.org
To install ELRepo for RHEL-5, SL-5 or CentOS-5:
rpm -Uvh http://elrepo.org/elrepo-release-5-1.el5.elrepo.noarch.rpm
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
References:
1. ntfs-3g http://www.tuxera.com/community/ntfs-3g-download/
How to map ftp/sftp drive for remote server from Windows/Linux
Jephe Wu - http://linuxtechres.blogspot.com
1. Perfect way - map sftp server as Windows drive letter
expandrive - www.expandrive.com (30days trail)
webdrive - http://www.webdrive.com/products/webdrive/sftpclient.html (20 days trial)
Directnet drive - http://www.directnet-drive.net/ (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
or
netdrive - http://www.netdrive.net/ ( with drive letter and it is free for non-commercial use. )
or
Directnet drive - http://www.directnet-drive.net/ (with drive letter and it's free for non-commercial use)
How to troubleshoot packet loss and latency for Internet VPN
Jephe Wu - http://linuxtechres.blogspot.com
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.
Steps:
The following is the vpn network diagram:
10.0.5.x__10.0.5.1||1.2.3.4++++++++++++++++5.6.7.8||10.0.6.1__10.0.6.X
1. Check latency and packet loss from host 1.2.3.4 to 5.6.7.8
a. ping check if you are able to ping from 1.2.3.4 to 5.6.7.8, 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 (http://tracetcp.sourceforge.net/), 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 www.redhat.com:443', 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 192.168.100.20 184.85.48.112 TCP 20527 > https [SYN] Seq=0 Win=16383 Len=0 (time to live is 1 in ip header)
14 3.734182000 192.168.100.1 192.168.100.20 ICMP Time-to-live exceeded (Time to live exceeded in transit)
15 4.232399000 192.168.100.20 184.85.48.112 TCP 24043 > https [SYN] Seq=0 Win=16383 Len=0 (time to live is 1 in ip header)
16 4.241227000 192.168.100.1 192.168.100.20 ICMP Time-to-live exceeded (Time to live exceeded in transit)
17 4.732323000 192.168.100.20 184.85.48.112 TCP 13233 > https [SYN] Seq=0 Win=16383 Len=0 (time to live is 1 in ip header)
18 4.735323000 192.168.100.1 192.168.100.20 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 (http://en.wikipedia.org/wiki/MTR_%28software%29) 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 192.168.100.20 184.85.48.112 ICMP Echo (ping) request
2083 191.590458 203.117.34.14 192.168.100.20 ICMP Time-to-live exceeded (Time to live exceeded in transit)
2084 191.673071 192.168.100.20 184.85.48.112 ICMP Echo (ping) request
2085 191.804462 192.168.100.20 184.85.48.112 ICMP Echo (ping) request
2086 191.874170 198.32.176.127 192.168.100.20 ICMP Time-to-live exceeded (Time to live exceeded in transit)
........
2090 191.804462 192.168.100.20 184.85.48.112 ICMP Echo (ping) request
2091 174.139518 184.85.48.112 192.168.100.20 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.
References:
a. http://help.rr.com/hmsfaqs/e_packetloss.aspx
2. http://library.linode.com/linux-tools/mtr/ --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 1.2.3.4 to $ext keep state
pass in log quick on $ext inet proto icmp all icmp-type { echo } from 1.2.3.4 to $ext keep state
References:
ICMP filtering on the firewall -
http://www.richweb.com/icmp_filter
Posts
