Linux system administrator and Oracle/DB2 DBA guide

How to setup compression for high volume zabbix server

2012-01-15T14:37:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: CentOS 5 64bit, zabbix server with very large items.
Objective: make 'latest data' with group and host set to 'all' page loading faster.

Steps:
1. make sure deflate_module is enabled in apache configuration

LoadModule deflate_module modules/mod_deflate.so

2. filtered by type
for example:

<Location />
    AddOutputFilterByType DEFLATE text/html text/plain text/xml text/x-js text/css
</Location>

Note: you can use Lynx or similar to do a headers dump on the file and it will tell you want the mime type is,

e.g.:

$ lynx -head -dump http://www.example.com/js/jquery-1.2.6.min.js
HTTP/1.1 200 OK
Date: Wed, 25 Jun 2008 03:25:53 GMT
Server: Apache/2.2.3 (CentOS)
Last-Modified: Wed, 25 Jun 2008 03:24:47 GMT
ETag: "38016-d9de-37f45dc0"
Accept-Ranges: bytes
Content-Length: 55774
Connection: close
Content-Type: application/x-javascript

<Location />
    AddOutputFilterByType DEFLATE application/x-javascript
</Location>
--------------------
3. example settings
root@zabbixserver01:/var/log/httpd/ # more /etc/httpd/conf.d/deflate.conf
<IfModule mod_deflate.c>
        AddOutputFilterByType DEFLATE text/plain
        AddOutputFilterByType DEFLATE text/html
        AddOutputFilterByType DEFLATE text/xml
        AddOutputFilterByType DEFLATE text/css
        AddOutputFilterByType DEFLATE application/xml
        AddOutputFilterByType DEFLATE application/xhtml+xml
        AddOutputFilterByType DEFLATE application/rss+xml
        AddOutputFilterByType DEFLATE application/javascript
        AddOutputFilterByType DEFLATE application/x-javascript
        DeflateCompressionLevel 9
        BrowserMatch ^Mozilla/4 gzip-only-text/html
        BrowserMatch ^Mozilla/4\.0[678] no-gzip
        BrowserMatch \bMSIE !no-gzip !gzip-only-text/html

    # Don't compress images
    SetEnvIfNoCase Request_URI \
    \.(?:gif|jpe?g|png)$ no-gzip dont-vary

    # Make sure proxies don't deliver the wrong content
    Header append Vary User-Agent env=!dont-vary

    DeflateFilterNote Input instream
        DeflateFilterNote Output outstream
        DeflateFilterNote Ratio ratio
        LogFormat '"%r" %{outstream}n/%{instream}n (%{ratio}n%%)' deflate
    CustomLog logs/deflate_log deflate
</IfModule>

4. References
a. Apache mod_deflate example:
http://httpd.apache.org/docs/2.0/mod/mod_deflate.html

b. http://www.linuxjournal.com/article/6802?page=0,1

c. use php zlib compress by modifying php.ini

PHP with the --with-zlib configure option and then reconfiguring the php.ini file.

Below is what the output buffer method looks like:

output_buffering = On
output_handler = ob_gzhandler
zlib.output_compression = Off

The ZLIB method uses:

output_buffering = Off
output_handler =
zlib.output_compression = On

How dbbix works with Zabbix

2012-01-13T11:54:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: dbforbix running on separated CentOS 5 box, Zabbix server or zabbix proxy is on another server
Objective: use separated server running dbbix to serve database performance check and show graph on zabbix web gui
Concept:
dbforbix collects data from separated server, then send it to zabbix server(destination server was configured without proxy) or zabbix proxy(server was configured with proxy)

Steps:
1. on dbforbix, configure it as follows:

ZabbixServerList=zabbixserver01,zabbixproxy01
zabbixserver01.Address=172.16.4.1
zabbixserver01.Port=10051

zabbixproxy01.Address=172.16.4.2
zabbixproxy01.Port=10051

DatabaseList=db01,db02

db01.Url=jdbc:mysql://db01:3306/mysqldb01.User=zabbix
db01.Password=zabbix
db01.DatabaseType=mysql
db01.QueryListFile=./conf/mysql/mysqlquery.props

oradb-01.Url=jdbc:oracle:thin:@oradb-01-vip:1521:LIVEDB1
oradb-01.User=zabbix
oradb-01.Password=zabbix
oradb-01.QueryListFile=./conf/oracle/query.props

dbent.Url=jdbc:jtds:sqlserver://172.16.7.2:1987/master
dbent.User=zabbix
dbent.Password=zabbix
dbent.DatabaseType=mssql
dbent.QueryListFile=./conf/mssql/mssqlquery.props

host1.Url=jdbc:postgresql://host1:5432/databasename
host1.User=zabbix
host1.Password=zabbix
host1.DatabaseType=pgsql
host1.QueryListFile=./conf/pgsql/pgsqlquery.props

.....

2. debug if the gui is not updating data

make sure dbforbix server can telnet zabbix server or proxy at port 10051, if not, make sure proxy or server is not running iptables.
otherwise, you will get !X (man traceroute, it's administratively prohibitted indication))
use traceroute or tcptraceroute to troubleshoot.

How to use kickstart to install CentOS 6.1 on non-DHCP network

2012-01-10T10:24:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: HP BladeSystem Proliant BL460c G6(ip: 192.168.0.0/24, DHCP/tftp/kickstart/PXE/HTTP server is on different network: 192.168.1.0/24)
Objective: install CentOS 6.1 into bare-metal blades from

Difficulties: We are not able to boot from PXE as it's in different network.
Concepts:
We can use CentOS netinstall ISO CD to boot up blades and specify dhcp/tftp/http server as installation source and use kickstart to install it automatically.

We can further modify CentOS netinstall iso CD to add kickstart location to make it automatically installed through kickstart

Steps:
1. make customized netinstall CentOS 6.1 CD
download iso from http://mirror.optus.net/centos/6.1/isos/x86_64/CentOS-6.1-x86_64-netinstall.iso and put it /root

cd /root; mkdir c
mount CentOS-6.1-x86_64-netinstall.iso c -o loop
cp -va c c.1
cd c.1
rm -fr images
cd isolinux
vi isolinux.cfg
-------------
default linux
prompt 0
timeout 1

display boot.msg

menu background splash.jpg
menu title Welcome to CentOS 6.2!
menu color border 0 #ffffffff #00000000
menu color sel 7 #ffffffff #ff000000
menu color title 0 #ffffffff #00000000
menu color tabmsg 0 #ffffffff #00000000
menu color unsel 0 #ffffffff #00000000
menu color hotsel 0 #ff000000 #ffffffff
menu color hotkey 7 #ffffffff #ff000000
menu color scrollbar 0 #ffffffff #00000000

label linux
menu label ^Install or upgrade an existing system
menu default
kernel vmlinuz
append initrd=initrd.img ks=http://jephe.server/ks_centos_6_x86_64 text ksdevice=eth0 gateway=192.168.0.254 ip=192.168.0.1 dns=192.168.1.1 netmask=255.255.255.0 --onboot=on
label vesa
menu label Install system with ^basic video driver
kernel vmlinuz
append initrd=initrd.img xdriver=vesa nomodeset
label rescue
menu label ^Rescue installed system
kernel vmlinuz
append initrd=initrd.img rescue
label local
menu label Boot from ^local drive
localboot 0xffff
label memtest86
menu label ^Memory test
kernel memtest
append -
-------------------
cat > /root/c.1/mkiso.sh << END
mkisofs -o ../CentOS-6.1_x86_64-Kickstart-192.168.0.1.iso -b isolinux/isolinux.bin -c isolinux/boot.cat -no-emul-boot -boot-load-size 4 -boot-info-table -R -J -v -T .
END
-------------------
then, cd /root/c.1, run
./mkiso.sh
it will create the iso file under /root

2. prepare kickstart file http://jephe.server/ks_centos_6_x86_64
Here is the KS file I used:
-------------
install
url --url=http://jephe.server/packages/centos_6_x86_64
text
lang en_US
keyboard us
rootpw password
firewall --disabled
services --disabled=NetworkManager,abrtd,acpid,certmonger,cgconfig,cgred,corosync,cpuspeed,cups,dovecot,ip6tables,iptables,kdump,modclusterd,multipathd,mysql-mmm-agent,mysql-proxy,netconsole,nfs,nscd,nslcd,ntpdate,oddjobd,psacct,quota_nld,rdisc,restorecond,rpcbind,rpcgssd,rpcidmapd,rpcsvcgssd,saslauthd,smartd,snmptrapd,spamassassin,sssd,vncserver,ypbind
firstboot --disable
selinux --disabled
authconfig --enableshadow --passalgo=sha512 --enablefingerprint
timezone Australia/Sydney
bootloader --location=mbr --driveorder=sda --append="rhgb crashkernel=auto rhgb quiet"
zerombr yes
clearpart --all --initlabel
part /boot --fstype=ext3 --size=256
part swap --asprimary --size=8192
part / --fstype=ext4 --grow --asprimary --size=200
%packages --ignoremissing
@base
@client-mgmt-tools
@compat-libraries
@console-internet
@core
@debugging
@directory-client
@mail-server
@hardware-monitoring
@java-platform
@large-systems
@mysql-client
@mysql
@network-file-system-client
@performance
@perl-runtime
@server-platform
@server-policy
@storage-client-multipath
@system-admin-tools
pax
oddjob
sgpio
certmonger
pam_krb5
krb5-workstation
nscd
pam_ldap
nss-pam-ldapd
perl-DBD-MySQL
perl-DBD-SQLite
screen
vlock
lsscsi
sendmail
sendmail-cf
lrzsz
dos2unix
unix2dos
tigervnc

%pre --log=/tmp/pre.log
ETH=eth0
IP=`ifconfig $ETH | grep "inet " | cut -d : -f 2 | cut -d " " -f 1`
MAC=`ifconfig $ETH | grep HWaddr | awk -F "HWaddr " '{print $2}'`
if [ $MAC == "00:00:B3:B0:44:00" ];then
    NAME=db01.domain
    SHORTNAME=db01
fi
if [ $MAC == "00:00:B3:B0:43:18" ];then
    NAME=db02.domain
    SHORTNAME=db02
fi
echo "NETWORKING=yes" > /tmp/network.txt
echo "HOSTNAME=${NAME}" >> /tmp/network.txt

cat > /tmp/hosts.txt <<'EOF'
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
EOF
echo "$IP $NAME $SHORTNAME" >> /tmp/hosts.txt

%post --nochroot --log=/tmp/post.log
cp /tmp/network.txt /mnt/sysimage/etc/sysconfig/network
. /mnt/sysimage/etc/sysconfig/network
/mnt/sysimage/bin/hostname $HOSTNAME
cp /tmp/hosts.txt /mnt/sysimage/etc/hosts
/usr/bin/cp /tmp/pre.log /mnt/sysimage/root/pre.log
/usr/bin/cp /tmp/post.log /mnt/sysimage/root/post.log

# if you use rollout for server configuration deployment. ( search 'rollout google' in google)
echo "Kickstarted `/usr/bin/date`" > /mnt/sysimage/root/kickstart_report
wget -O /mnt/sysimage/usr/local/sbin/rollout http://server.domain/rollout
sed -i -e 's#http://server.domain#http://rollout.domain#' /mnt/sysimage/usr/local/sbin/rollout
chmod +x /mnt/sysimage/usr/local/sbin/rollout

echo "Kickstart complete" >> /mnt/sysimage/root/kickstart_report
-------------

3. use HP ILO to boot from above-made CentOS netinstall ISO CD.
it will use kernel and initrd files from customized netinstall iso CD to boot up machine, and install packages from approved specified kickstart file
http://jephe.server/packages/centos_6_x86_64

For configuration files under pxelinux.cfg directory, you can use something like this:
append initrd=centos_6_x86_64/initrd.img ramdisk_size=8192 ks=http://kickstart.jephe/ks_centos_6_x86_64 ip=dhcp ksdevice=eth0

4. configuring bonding device
[root@db01 network-scripts]# more /etc/modprobe.d/bonding.conf
alias eth0 e1000e
alias eth1 e1000e
alias eth2 e1000e
alias eth3 e1000e
#alias eth4 bnx2x
#alias eth5 bnx2x
alias scsi_hostadapter lpfc
alias bond0 bonding

ifcfg-bond0
::::::::::::::
DEVICE=bond0
GATEWAY=192.168.0.254
IPADDR=192.168.0.1
IPV6INIT=no
NETMASK=255.255.255.0
ONBOOT=yes
TYPE=Ethernet
USERCTL=no
BONDING_OPTS="mode=1 miimon=100"
::::::::::::::
ifcfg-eth0
::::::::::::::
DEVICE=eth0
IPV6INIT=no
MASTER=bond0
MTU=1500
ONBOOT=no
PEERDNS=yes
SLAVE=yes
TYPE=Ethernet
USERCTL=no
::::::::::::::
ifcfg-eth1
::::::::::::::
# Intel Corporation 82571EB Quad Port Gigabit Mezzanine Adapter
DEVICE=eth1
HWADDR=00:00:00:B0:44:01
ONBOOT=no
HOTPLUG=no
::::::::::::::
ifcfg-eth2
::::::::::::::
DEVICE=eth2
IPV6INIT=no
MASTER=bond0
MTU=1500
ONBOOT=no
PEERDNS=yes
SLAVE=yes
TYPE=Ethernet
USERCTL=no
::::::::::::::
ifcfg-eth3
::::::::::::::
# Intel Corporation 82571EB Quad Port Gigabit Mezzanine Adapter
DEVICE=eth3
HWADDR=00:00:00:B0:44:03
ONBOOT=no
HOTPLUG=no
::::::::::::::
::::::::::::::
ifcfg-eth4
::::::::::::::
# Broadcom Corporation NetXtreme II BCM57711E 10-Gigabit PCIe
DEVICE=eth4
HWADDR=F4:CE:46:7E:00:00
ONBOOT=no
HOTPLUG=no
::::::::::::::
ifcfg-eth5
::::::::::::::
# Broadcom Corporation NetXtreme II BCM57711E 10-Gigabit PCIe
DEVICE=eth5
HWADDR=F4:CE:46:7E:00:01
ONBOOT=no
HOTPLUG=no
::::::::::::::
ifcfg-lo
::::::::::::::
DEVICE=lo
IPADDR=127.0.0.1
NETMASK=255.0.0.0
NETWORK=127.0.0.0
# If you're having problems with gated making 127.0.0.0/8 a martian,
# you can change this to something else (255.255.255.255, for example)
BROADCAST=127.255.255.255
ONBOOT=yes
NAME=loopback

Note: prior to CentOS 6, you may add bonding options as follows in /etc/modprobe.d/bonding.conf:
options bonding mode=1 miimon=100

How to setup MMM control under CentOS 6.1

2012-01-10T10:20:00.003+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: CentOs 6.1, MMM 2.2.1, MySQL 5.1.52
Objective: setup MMM among the databases to gain HA and load balance for read access

Steps:

1. install required packages for mmm agent on database servers
yum -y install perl-Config-IniFiles
yum -y install mysql-mmm mysql-mmm-agent mysql-mmm-tools mysql-proxy

2. install required packages for mmm monitor on monitoring host
yum -y install mysql-mmm-monitor

chkconfig mysql-mmm-agent on
chkconfig mysql-mmm-monitor on

3. preparing ips for databases
db1: 172.17.7.1 master
db2: 172.17.7.2 master
db3: 172.17.7.3 slave

master exclusive ip: 172.17.7.4
slave balanced ip addresses: 172.17.7.5, 172.17.7.6, 172.17.7.7

4. setup /etc/mysql-mmm/mmm_agent.conf to change 'this' line to db1/db2 or db3
5. setup /etc/mysql-mmm/mmm_common.conf for all monitor and agents

# more /etc/mysql-mmm/mmm_common.conf
active_master_role          writer

<host default>
    cluster_interface       bond0

    pid_path                /var/run/mysql-mmm/mmm_agentd.pid
    bin_path                /usr/libexec/mysql-mmm/

    replication_user        replication
    replication_password    replication_password

    agent_user              mmm_agent
    agent_password          agent_password
</host>

<host db1>
    ip                      172.17.7.1
    mode                    master
    peer                    db2
</host>

<host db2>
    ip                      172.17.7.2
    mode                    master
    peer                    db1
</host>

<host db3>
    ip                      172.17.7.3
    mode                    slave
</host>

<role writer>
    hosts                   db1, db2
    ips                     172.17.7.4
    mode                    exclusive
</role>

<role reader>
    hosts                   db1, db2, db3
    ips                     172.17.7.5, 172.17.7.6, 172.17.7.7
    mode                    balanced
</role>

6. start up agents on database servers first
on db1,db2 then db3, to start up mmm agent: /etc/init.d/mysql-mmm-agent start

7. start up monitor process on monitoring host
on mon01, to start up: /etc/init.d/mysql-mmm-monitor start

8. bring up them online from mon01
mon01# mmm_control set_online db1
mon01# mmm_control set_online db2
mon01# mmm_control set_online db3

root@mon01:/etc/mysql-proxy/ # mmm_control show
db1(172.17.7.1) slave/REPLICATION_DELAY. Roles:
db2(172.17.7.2) master/ONLINE. Roles: reader(172.17.7.5), reader(172.17.7.6)
db3(172.17.7.3) master/ONLINE. Roles: reader(172.17.7.7), writer(172.17.7.4)

9. FAQ
a. read_only (on/off) from show variables comand in mysql
if db2 and db3 are writer, when writer role goes to db3, mmm will set read_only flag for db2 to prevent any writing process, but only for normal user, not root.

Preparing Oracle RAC system

2011-12-30T11:30:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: openfiler as iscsi server, node1 and node2 are OL5.7, all of them are running under VirtualBox VM, Oracle 10gR2 10.2.0.1 clusterware
IP address assignment:
node1: eth0(public): 192.168.1.100, eth1(priv):172.16.1.100, vip:(going to be eth0:0) 192.168.1.102
node2: eth0(public): 192.168.1.101, eth1(priv):172.16.1.101, vip:(going to be eth0:0) 192.168.1.103
openfiler: 172.16.1.200 web login: https://172.16.1.200 (login as : openfiler/password)

Part I: OS installation
a. oracle user and groups
# groupadd oinstall
# groupadd dba
# groupadd oper
# useradd -u 200 -g oinstall -G dba[,oper] oracle
note: make sure all cluseter nodes has the same user and group id, otherwise ,it will fail to install clusterware.

b. verify nobody user exists
# id nobody

c. configure ssh on all nodes
make sure you can ssh into all nodes, public names and priv interconnect names

d. check hardware requirements
memory size
swap size

e. NFS (http://download.oracle.com/docs/cd/B19306_01/install.102/b14203/prelinux.htm)
If you are using NFS for your shared storage, then you must set the values for the NFS buffer size parameters

rsize and wsize to at least 16384. Oracle recommends that you use the value 32768.

For example, if you decide to use rsize and wsize buffer settings with the value 16384, then update the /etc/fstab

file on each node with an entry similar to the following:

clusternode:/vol/DATA/oradata /home/oracle/netapp     nfs

rw,bg,vers=3,tcp,hard,nointr,timeo=600,rsize=32768,wsize=32768,actimeo=0 1 2

f. NTP for both nodes
g. /etc/hosts and dns
For each node, register one virtual host name and IP address in DNS.
For each private interface on every node, add a line similar to the following to the /etc/hosts file on all nodes,

specifying the private IP address and associated private host name:

h. /etc/sysctl.conf
kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 1048576

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

run sysctl -p

i. Add the following lines to the /etc/security/limits.conf file:

oracle              soft    nproc   2047

oracle               hard    nproc   16384

oracle               soft    nofile 1024

oracle               hard    nofile 65536

j. Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:

session    required     /lib/security/pam_limits.so
note: should not add above, according to redhat KB, otherwise you cannot login from console
Why the system text console cannot be login? - Article ID: 52661

the following should be added to /etc/profile:

if [ $USER = "oracle" ]; then

        if [ $SHELL = "/bin/ksh" ]; then

              ulimit -p 16384

              ulimit -n 65536

        else

              ulimit -u 16384 -n 65536

        fi

fi

k. identify oracle base and home directories
more /etc/oraInst.loc - Inventory directory
more /etc/oratab - Oracle home directory

l. create oracle base and clusterware home directory
mkdir -p /u01/app/oracle/
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle (/u01 is the mount point)

mkdir -p /u01/crs/oracle/product/10/crs
chown root:oinstall /u01/crs/
chmod -R 775 /u01/crs/oracle

if mount point is /u01/, then the recommended Oracle clusterware home directory is
/u01/crs/oracle/product/10.2.1/crs

m. Verifying Hangcheck-timer Module on Kernel 2.6

For Red Hat Linux 4.0 and SUSE 9 systems, to verify that the hangcheck-timer module is running on every node:

    Enter the following command on each node to determine which kernel modules are loaded:

    # /sbin/lsmod

    If the hangcheck-timer module is not listed for any node, then enter a command similar to the following to

start the module located in the directories of the current kernel version:

    # insmod /lib/modules/kernel_version/kernel/drivers/char/hangcheck-timer.ko hangcheck_tick=1

hangcheck_margin=10

    In the preceding command example, the variable kernel_version is the kernel version running on your system.

    To confirm that the hangcheck module is loaded, enter the following command:

    # lsmod | grep hang

    The output should be similar to the following:

    hangcheck_timer         3289 0

    To ensure that the module is loaded every time the system restarts, verify that the local system startup file

contains the command shown in the previous step, or add it if necessary:

        Red Hat:

        On Red Hat Enterprise Linux systems, add the command to the /etc/rc.d/rc.local file.

        SUSE:

        On SUSE systems, add the command to the /etc/init.d/boot.local file.

configure .bash_profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_HOME/product/10.2.0/db_1
export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crs
export PATH=$PATH:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin

2. clusterware installation

Please refer to http://oracleinstance.blogspot.com/2010/03/oracle-10g-installation-in-linux-5.html for complete

screenshot example
and also
http://space.itpub.net/21162451/viewspace-696413 - RHEL5.4+Oracle10gR2 RAC+OCFS2

2.1 os requirements
Prerequisite Checks Fail When Installing 10.2 On Red Hat 5 (RHEL5)
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
                                      Failed <<<<

-> Prerequisite Checks Fail When Installing 10.2 On Red Hat 5 (RHEL5) [ID 456634.1]
If you are installing 10.2 from DVD, copy the <path>/database/install/oraparam.ini to a temporary directory (for

example, /tmp).

If you are installing 10.2 from an OTN download or have copied the 10.2 media to disk, take a backup of

<path>/database/install/oraparam.ini

Now edit oraparam.ini and change the appropriate line:

Original

[Certified Versions]
Linux=redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2

New

[Certified Versions]
Linux=redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2,redhat-5

note:
a. do not use ifconfig to configure vip before installing clusterware, just configure them in /etc/hosts
for example:

#cat /etc/hosts

127.0.0.1       localhost.localdomain localhost
192.168.1.100   jephe1.jephe.com jephe1
192.168.1.101   jephe2.jephe.com jephe2

172.16.1.100    jephe1-priv.jephe.com jephe1-priv
192.168.1.102   jephe1-vip.jephe.com jephe1-vip

172.16.1.101    jephe2-priv.jephe.com jephe2-priv
192.168.1.103   jephe2-vip.jephe.com jephe2-vip

OCR configration:
normal redundancy

specify OCR Location: /dev/raw/raw1
specify OCR Mirror Location: /dev/raw/raw2

2.2 How To Setup UDEV Rules For RAC OCR And Voting Devices On SLES10, RHEL5, OEL5, OL5
- refer to http://www.held.org.il/blog/2007/11/setting-a-raw-device-in-redhatcentos-5/

Add the required raw device ownership and permissions, for example:
a. Add to /etc/udev/rules.d/60-raw.rules:
ACTION==”add”, KERNEL==”sdb1″, RUN+=”/bin/raw /dev/raw/raw1 %N”
ACTION=="add", KERNEL=="sdc1", RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add", KERNEL=="sdd1", RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", KERNEL=="sde1", RUN+="/bin/raw /dev/raw/raw4 %N"
ACTION=="add", KERNEL=="sdf1", RUN+="/bin/raw /dev/raw/raw5 %N"

b. To set permission (optional, but required for Oracle RAC!), create a new /etc/udev/rules.d/99-raw-perms.rules

containing lines such as:

    KERNEL==”raw[1-5]“, MODE=”0640″, GROUP=”oinstall”, OWNER=”oracle”

Notice this:

    The raw-perms.rules file name has to begin with the number 99, which defines its order during rules apply, so that it will be used after all other rules take place. Using lower numbers might cause permissions to be

incorrect.
    The following permissions have to apply:

OCR Device(s): root:oinstall , mode 0640
Voting device(s): oracle:oinstall, mode 0666
need to test the following commands:
# /sbin/udevcontrol reload_rules
# /sbin/start_udev

2.3. at the end of clusterware installation, you will be prompt to run 2 programs which need root

permission,follow by the sequences below:
first program on first node
first program on second node

/home/oracle/oraInventory/orainstRoot.sh
ssh jephe2 -l root
/home/oracle/oraInventory/orainstRoot.sh
exit
/home/oracle/oracle/product/10.2.0/crs/root.sh
ssh jephe2 -l root
/home/oracle/oracle/product/10.2.0/crs/root.sh

second program on first node
second program on second node
basically, you need to finish the number 1 script on all nodes first before going to the next script.
also, run it under GUI interface, aka, X windows in vnc on node2 as root user, not oracle user, otherwise vipca will fail, vipca will use GUI

c. FAQ
c.1 some error at the end of running second root program on the second node:
Running vipca(silent) for configuring nodeapps
/home/oracle/oracle/product/10.2.0/crs/jdk/jre//bin/java: error while loading shared libraries: libpthread.so.0:

cannot open shared object file: No such file or directory

-> this is a bug, due to newer version of glibc(RHEL5) is incompatible with Java, need to modify vipca script as

follows:
vi /home/oracle/oracle/product/10.2.0/crs/bin/vipca
change
LD_ASSUME_KERNEL=2.4.19
export LD_ASSUME_KERNEL
to:

LD_ASSUME_KERNEL=2.4.19
export LD_ASSUME_KERNEL
unset LD_ASSUME_KERNEL

#add this line to uncomment variable LD_ASSUME_KERNEL

Now, login xwindows on second node, run /home/oracle/oracle/product/10.2.0/crs/bin/vipca again to get errors below:

Error 0(Native: listNetInterfaces:[3])

[Error 0(Native: listNetInterfaces:[3])]

solution:

$ oifcfg iflist

eth0 192.168.1.0
eth1 172.16.1.0

$ oifcfg setif -global eth0/192.168.1.0:public
$ oifcfg setif -global eth1/172.16.1.0:cluster_interconnect
$ oifcfg getif

eth0 192.168.1.0 global public
eth1 172.16.1.0 global cluster_interconnect

# vipca (run on the second node with root user login at X windows, don't run it with oracle user then su - as root)
just choose eth0 as vip interface, do not choose eth0:0, otherwise, after finishing installation, the vip will become eth0:0:1 for 192.168.1.102 for node1

You will be asked for entering ip alias name and ip address for both codegen1 and codegen2. Firstly, enter ip address for codegen1: 192.168.1.102, then the rest will come out automatically.node1-vip.jephe.com and node2-vip.jephe.com are ip alias name.

after finishing configure vipca, back to CRS installation GUI, click ok to finish installation of clusterware

If environment variable
$ORA_CRS_HOME/cfgtoollogs/configToolFailedCommands.sh

If you got error: PRKR-1062 : Failed to find configuration for node codegen1,
then your /etc/hosts might be missing domain name such as
192.168.1.100 jephe1
not
192.168.1.100 jephe1.jephe.com jephe1

Part II: openfiler iscsi:

How to Dynamically Add and Remove SCSI Devices on Linux [ID 603868.1]

yum install lsscsi

lsscsi
cat /proc/scsi/scsi
grep host /etc/modprobe.conf
ls -ld /sys/class/scsi_host/host*
dmsetup ls | sort
multipath -d -ll
raw -qa
ls -l /dev/mapper/
ls -l /dev/raw/
ocrcheck
crsctl query css votedisk
crsctl check crs
ocrconfig -showbackup
cat /etc/oracle/ocr.loc

where is the setting for RAC service preference nodes?

nodes applications contain listener, gsnd etc?

Firstly, Oracle checks /etc/oracle/ocr.loc to find out where is the ocr (raw1 and raw2), then from ocr, to find out where are the voting disk devices.

[root@codegen1 bin]# lsscsi
[0:0:0:0]    disk    ATA      VBOX HARDDISK    1.0   /dev/sda
[2:0:0:0]    cd/dvd VBOX     CD-ROM           1.0   /dev/sr0
[3:0:0:0]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdb
[3:0:0:1]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdc
[3:0:0:2]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdd
[3:0:0:3]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sde
[3:0:0:4]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdf
[3:0:0:5]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdg
[3:0:0:6]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdh
[3:0:0:7]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdi
[3:0:0:8]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdj
[3:0:0:9]    disk    OPNFILER VIRTUAL-DISK     0     /dev/sdk

5.1. Resizing an Online Multipath Device
If you need to resize an online multipath device, use the following procedure.

    Resize your physical device.
    Use the following command to find the paths to the LUN:

    # multipath -l [ -ll] [-d -ll]

    Resize your paths. For SCSI devices, writing a 1 to the rescan file for the device causes the SCSI driver to rescan, as in the following command:

    # echo 1 > /sys/block/device_name/device/rescan

    Resize your multipath device by running the multipathd resize command:

    # multipathd -k'resize map mpath0'

    Resize the filesystem (assuming no LVM or DOS partitions are used):

    # resize2fs /dev/mapper/mpath0

For further information on resizing an online LUN, see the Online Storage Reconfiguration Guide.

echo "scsi add-single-device 3 0 0 0" > /proc/scsi/scsi

Reference:
1. http://space.itpub.net/21162451/viewspace-696413 - RHEL5.4+Oracle10gR2 RAC+OCFS2
2. http://www.oracle.com/webfolder/technetwork/tutorials/demos/db/10g/r2/rac_r2_work/02_01_crs_install/02_01_crs_inst

all_viewlet_swf.html - Install 10g clusterware livedemo from Oracle
3. http://www.oracle.com/webfolder/technetwork/tutorials/demos/db/11g/r1/clusterware/installation_of_oracle_clusterware/installation_of_oracle_clusterware_viewlet_swf.html - Install clusterware on Oracle 11gR1
4. install oracle 11gR2 database - http://st-curriculum.oracle.com/obe/db/11g/r2/2day_dba/install/install.htm
5. http://oracleinstance.blogspot.com/2010/03/oracle-10g-installation-in-linux-5.html

How to check or disable SELinux

2011-12-23T07:08:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: CentOS 5 or 6
Objective: check selinux status
Methods:
1. check selinux status:
getenforce
or
selinuxenabled;echo $? (return 1 means disabled, 0 means enabled, man selinuxenabled)
note: use setenforce to switch between permissive or enforce mode runtime.

Accoding to Redhat knowledge base: Only if current selinux status is not disabled. you can use setenforce to set Current Enforcing Mode which can be switched between "Enforcing" and "Permissive". This setting is effective immediately but is not persistent - the system will revert to the "System Default Enforcing Mode" setting when it is restarted.

2. how to set selinux permanently
a. GUI tool: system-config-securitylevel
b. vi /etc/selinux/config or /etc/sysconfig/selinux , change SELINUX=Disabled
c. vi grub.conf,

  kernel /vmlinuz-xxxx ro root=/dev/VolGroup00/LogVol00 rhgb quiet selinux=0

d. at install time, type 'linux selinux=0' to boot prompt.

Set up mysql proxy with mmm2 and mysql master-slave replication environment for zabbix read/write splitting

2011-12-12T11:03:00.008+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: 2 mysql database configured with master-slave replication. mysql-proxy 0.8.1 from EPEL running on CentOS 6 x86_64, as well as mmm_monitor. Zabbix server connecting to mysql-proxy ip address as database. separated web server for Zabbix running on another machine connecting to mysql-proxy ip address

Diagram:

Zabbix Web -> mysql-proxy 0.8.1/mmm2 (CentOS 6 64bit)
Zabbix Server -> mysql-proxy 0.8.1/mmm2 -> writer mysql db
-> reader mysql db

Objective: Make Zabbix to send non-transactional queries to reader mysql db only, and DML to writer mysql db, in short, read/write splitting.

Steps: We are only talking about mysql-proxy configuration part in this article, you can refer other blogs on this website for Zabbix and master-slave database replication.

1. use mysql-proxy from EPEL on CentOS 6 64bit
configuring EPEL first, then install mysql-proxy

2. use read-write splitting script from 0.8.2 alpha source code instead of tutorial-keep-alive.lua from 0.8.1 yum repository which is not working.

3. configuration file:
[root@mysql-proxy ~]# more /etc/sysconfig/mysql-proxy
# Options for mysql-proxy
#LUA_PATH=/usr/share/doc/mysql-proxy-0.8.1/examples/?.lua
ADMIN_USER="admin"
ADMIN_PASSWORD="password"
ADMIN_LUA_SCRIPT="/usr/lib64/mysql-proxy/lua/admin.lua"
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="--daemon --keepalive --proxy-backend-addresses=192.168.12.233:3306 --proxy-read-only-backend-addresses=192.168.12.234:3306 --proxy-address=:3306 --proxy-lua-script=/usr/share/doc/mysql-proxy-0.8.1/examples/rw-splitting-got-from-0.8.2alpha-source-tar-file.lua --log-level=message --log-file=/var/log/mysql-proxy.log"

netstat -ntpl , you will find mysql proxy admin port listening at 4041, you can connect to it from another host to admin port:
mysql -uadmin -ppassword -P4041 -h mysql-proxy-host
===============
or with the following configuration:
root@mon01:/etc/mysql-proxy/ # more /etc/sysconfig/mysql-proxy
# Options to mysql-proxy
# do not remove --daemon
LUA_PATH="/srv/mysql-proxy/lib/?.lua"
PROXY_OPTIONS="--daemon --defaults-file=/etc/mysql-proxy/my.cnf --keepalive"
---------
root@mon01:/etc/mysql-proxy/ # more my.cnf
[mysql-proxy]
admin-username=root
admin-password=password
proxy-address=0.0.0.0:3306

proxy-read-only-backend-addresses=172.17.1.6:3306
proxy-read-only-backend-addresses=172.17.1.7:3306
proxy-read-only-backend-addresses=172.17.1.8:3306
proxy-backend-addresses=172.17.1.5:3306

proxy-pool-no-change-user=true
proxy-skip-profiling=true

log-file=/srv/mysql-proxy/log/mysql-proxy.log
#log-level=warning
#log-level=message
#log-level=debug
user=mysql
admin-lua-script=/srv/mysql-proxy/lib/mysql-proxy/lua/admin.lua
pid-file=/srv/mysql-proxy/run/mysql-proxy.pid
proxy-lua-script=/srv/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua (this rw-splitting.lua is from mysql-proxy 0.8.2 tar file)

4. /etc/init.d/mysql-proxy restart

5. monitor slave mysql db logfile -> tail -f /var/lib/mysql/mysql_query.log (see /etc/my.cnf)

6. you can also see some DML statements on slave database, that's because master-slave replication, slave gets syncing with master operations.

7. once you finished configuring above, when clicking zabbix web monitoring - dashboard, most of select statements will go to slave database.

FAQ:
1. Zabbix web interface gives error "Lock wait timeout exceeded; try restarting transaction"
=> Uncommen innodb_lock_wait_timeout = 50, change it to 500 seconds. restart mysql

2. Error in query: Deadlock found when trying to get lock; try restarting transaction
=> Just try again and refer to http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html - how to cope with deadlock

3. For read/write splitting, tutorial-keep-alive.lua which comes with EPEL mysql-proxy rpm doesn't work.

4. how to check if rw-spliting is working?
while true ;do mysql -uroot -ppassword -e 'show processlist' ;sleep 1;done
to check if you can find any select statement.

References:
1. Mysql master-slave and mysql-proxy read/write splitting - http://www.linuxso.com/linuxrumen/10236.html
2. http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html - how to cope with Deadlocks

Commands:
1. show slave status\G
2. show create table history; to display DDL statement for table creation
3. show table status
4. show processlist;
5. show full processlist; to find out slow queries.

Install PSP on CentOS 6 x86_64

2011-12-09T10:35:00.003+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: HP ProLiant BL460c G6, installed CentOS 6 x86_64
Objective: install proliant support package (PSP)

Steps:

Note: do not use psp for CentOS 6 one, it's older version and it might not work. Use Redhat Enterprise Linux 6 instead.

1. change /etc/centos-release
cp /etc/centos-release /etc/centos-release.orig -va
vi /etc/centos-release as follows:
Change CentOS to
Red Hat Enterprise Linux Server release 6.0 (Final)

otherwise, some psp packages will fail to install and hpsmh will fail to install as well , it will report zlib version 1.1.4 or above is missing.

2. install hp-snmp-agents first from command line, otherwise, smhd installation will fail from ./hpsum (software update manager)

[root@db03 psp]# yum localinstall hp-snmp-agents-8.7.0.23-17.rhel6.x86_64.rpm
Loaded plugins: fastestmirror
Setting up Local Package Process
Examining hp-snmp-agents-8.7.0.23-17.rhel6.x86_64.rpm: hp-snmp-agents-8.7.0.23-17.x86_64
Marking hp-snmp-agents-8.7.0.23-17.rhel6.x86_64.rpm to be installed
Loading mirror speeds from cached hostfile
* base: mirror.optus.net
* epel: mirror.optus.net
* extras: mirror.optus.net
* updates: mirror.optus.net
Resolving Dependencies
--> Running transaction check
---> Package hp-snmp-agents.x86_64 0:8.7.0.23-17 set to be updated
--> Processing Dependency: hp-health for package: hp-snmp-agents-8.7.0.23-17.x86_64
--> Processing Dependency: libcpqci.so.2 for package: hp-snmp-agents-8.7.0.23-17.x86_64
--> Processing Dependency: libhpasmintrfc.so.3 for package: hp-snmp-agents-8.7.0.23-17.x86_64
--> Processing Dependency: libhpev.so.1 for package: hp-snmp-agents-8.7.0.23-17.x86_64
--> Processing Dependency: libcpqci64.so.2()(64bit) for package: hp-snmp-agents-8.7.0.23-17.x86_64
--> Processing Dependency: libhpasmintrfc64.so.3()(64bit) for package: hp-snmp-agents-8.7.0.23-17.x86_64
--> Finished Dependency Resolution
Error: Package: hp-snmp-agents-8.7.0.23-17.x86_64 (/hp-snmp-agents-8.7.0.23-17.rhel6.x86_64)
           Requires: libhpasmintrfc64.so.3()(64bit)
Error: Package: hp-snmp-agents-8.7.0.23-17.x86_64 (/hp-snmp-agents-8.7.0.23-17.rhel6.x86_64)
           Requires: libcpqci64.so.2()(64bit)
Error: Package: hp-snmp-agents-8.7.0.23-17.x86_64 (/hp-snmp-agents-8.7.0.23-17.rhel6.x86_64)
           Requires: libhpev.so.1
Error: Package: hp-snmp-agents-8.7.0.23-17.x86_64 (/hp-snmp-agents-8.7.0.23-17.rhel6.x86_64)
           Requires: hp-health
Error: Package: hp-snmp-agents-8.7.0.23-17.x86_64 (/hp-snmp-agents-8.7.0.23-17.rhel6.x86_64)
           Requires: libhpasmintrfc.so.3
Error: Package: hp-snmp-agents-8.7.0.23-17.x86_64 (/hp-snmp-agents-8.7.0.23-17.rhel6.x86_64)
           Requires: libcpqci.so.2
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

[root@db03 psp]# yum localinstall hp-health-8.7.0.22-17.rhel6.x86_64.rpm --nogpgcheck
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.optus.net
* epel: mirror.optus.net
* extras: mirror.optus.net
* updates: mirror.optus.net
Setting up Install Process
Examining hp-health-8.7.0.22-17.rhel6.x86_64.rpm: hp-health-8.7.0.22-17.x86_64
Marking hp-health-8.7.0.22-17.rhel6.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package hp-health.x86_64 0:8.7.0.22-17 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================

==========================================================================================================================
Package                                                    Arch                                                    Version

                              Repository                                                                            Size
====================================================================================================================================================

==========================================================================================================================
Installing:
hp-health                                                  x86_64                                                  8.7.0.22-17

                              /hp-health-8.7.0.22-17.rhel6.x86_64                                                  1.6 M

Transaction Summary
====================================================================================================================================================

==========================================================================================================================
Install       1 Package(s)
Upgrade       0 Package(s)

Total size: 1.6 M
Installed size: 1.6 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing     : hp-health-8.7.0.22-17.x86_64

                                                                                                                     1/1
Please read the Licence Agreement for this software at

         /opt/hp/hp-health/hp-health.license

By not removing this package, you are accepting the terms
of the "HP Proliant Essentials Software End User License Agreement".
==============================================================================
NOTE: To activate the software contained in this package, you must type:
      /etc/init.d/hp-health start
      /etc/init.d/hp-asrd start
as 'root' user.
==============================================================================
The hp-health RPM has installed successfully.

Installed:
hp-health.x86_64 0:8.7.0.22-17
Complete!

[root@db03 psp]# yum localinstall hp-snmp-agents-8.7.0.23-17.rhel6.x86_64.rpm --nogpgcheck
Loaded plugins: fastestmirror
Setting up Local Package Process
Examining hp-snmp-agents-8.7.0.23-17.rhel6.x86_64.rpm: hp-snmp-agents-8.7.0.23-17.x86_64
Marking hp-snmp-agents-8.7.0.23-17.rhel6.x86_64.rpm to be installed
Loading mirror speeds from cached hostfile
* base: mirror.optus.net
* epel: mirror.optus.net
* extras: mirror.optus.net
* updates: mirror.optus.net
Resolving Dependencies
--> Running transaction check
---> Package hp-snmp-agents.x86_64 0:8.7.0.23-17 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================

==========================================================================================================================
Package                                                       Arch                                                  Version

                           Repository                                                                               Size
====================================================================================================================================================

==========================================================================================================================
Installing:
hp-snmp-agents                                                x86_64                                                8.7.0.23-17

                           /hp-snmp-agents-8.7.0.23-17.rhel6.x86_64                                                5.9 M

Transaction Summary
====================================================================================================================================================

==========================================================================================================================
Install       1 Package(s)
Upgrade       0 Package(s)

Total size: 5.9 M
Installed size: 5.9 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing     : hp-snmp-agents-8.7.0.23-17.x86_64

                                                                                                                     1/1
Please read the Licence Agreement for this software at

         /opt/hp/hp-snmp-agents/hp-snmp-agents.license

By not removing this package, you are accepting the terms
of the "HP Proliant Essentials Software End User License Agreement".
Installing /opt/hp/hp-snmp-agents/nic/etc/HPcmanic.pp SELinux policy module
==============================================================================
NOTE: In order to activate the software contained in this package, you must
      type '/sbin/hpsnmpconfig' as 'root' user.
      Once configuration is completed start the agents by typing
      /etc/init.d/hp-snmp-agents start
==============================================================================

Installed:
hp-snmp-agents.x86_64 0:8.7.0.23-17


3. run /sbin/hpsnmpconfig to configure /etc/snmp/snmpd.conf
configure it to add the following part on the top of the /etc/snmp/snmpd.conf

dlmod cmaX /usr/lib64/libcmaX64.so
rwcommunity public 127.0.0.1
trapcommunity public
rocommunity public 127.0.0.1
trapsink 192.168.0.1 public
syscontact Jephe Wu <jwu@domain.com>
syslocation someplace

4. install tigervnc* and xterm for GUI installation environment for hpsum
yum install tigervnc* xterm

5. install psp by hpsum
cd /root/
download psp for RHEL6 x86_64
mkdir psp
cd psp
tar xvpzf ../psp-xxxxxx.tar.gz

vncserver
./hpsum

6. hpsum installation
a. check only 'software update'
b. do not rebuild kernel module for certain NIC
c. install them

7. check and reboot
psp create a new set of initrd file as initramfs blabla

8. commands and references:
yum localinstall *.rpm
yum whatprovides filename

9. modify /opt/hp/hp-snmp-agents/cma.conf
change
trapemail /bin/mail -s 'hmspzdb01 HP Insight Management Agents Trap Alarm' root
to
trapemail /bin/mail -s 'db1 hmspzdb01 HP Insight Management Agents Trap Alarm' root

Set up the third mysql server to be slave of the running master-master mysql database

2011-12-08T10:10:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: master(db1)-master(db2) mysql 5.5 database (replicate each other) running Oracle Linux 5.6 64bit
Objective: setup a third slave only database(db3, mysql 5.1 on CentOS 6) to sync with one of the master(db2) which the zabbix application is connecting to

Concept:
a. stop slave on db1 and db2 (mysql -uroot -ppass; sql> stop salve)
b. use mysqldump to make snapshot on db1
c. import data into db3
d. make db3 to sync with db2

Steps:
1. stop slave on both db1 and db2
mysql -uroot -ppass
sql> stop slave;
sql> show slave status\G

3. setup db3 on CentOS 6 64bit, use /srv/mysql/data as data folder
sh /usr/bin/mysql_install_db --user=mysql --datadir=/srv/mysql/data

copy back /etc/my.cnf mysqld part from db2 or db1

/etc/init.d/mysqld restart
chkconfig mysqld on
chkconfig --list mysqld

Note:
[root@db03 mysql]# tail -f /srv/mysql/data/db03.domainnameofserver.err
/usr/libexec/mysqld: Can't create/write to file '/tmpfs/ibJw77ig' (Errcode: 2)
111207 14:19:01 InnoDB: Error: unable to create temporary file; errno: 2
111207 14:19:01 [ERROR] Plugin 'InnoDB' init function returned error.
111207 14:19:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
111207 14:19:01 [ERROR] Unknown/unsupported table type: innodb
111207 14:19:01 [ERROR] Aborting

111207 14:19:01 [Note] /usr/libexec/mysqld: Shutdown complete

111207 14:19:01 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

solution=> change /etc/my.cnf /tmpfs to /dev/shm (according to df -h)

4. use mysqldump to make snapshot
on db01,do:
stop slave;
show slave status\G

note: record down about output for master_log_file and position so that db3 will use it to sync with db2 again after importing data

mysqldump -u root -ppass zabbix [--lock-all-tables] | gzip -c | ssh db03 'cat > /srv/mysql/mysqldump_on_db01.gz'

please refer to online doc at http://dev.mysql.com/doc/refman/5.0/en/replication-howto-mysqldump.html

5. import data into db3
zcat mysqldump_on_db01.gz | mysql -uroot -ppass zabbix

6. configuring db3 as slave for db2
/etc/init.d/mysqld start
sql> show slave status\G
empty record
sql> change master to master_host='192.168.0.1', master_user='replication', master_password='password',master_log_file='mysql-bin.001080',master_log_pos=50862743;
sql> show slave status\G

7. FAQ
After downgrading to 5.1 and make mysql 5.1 as master database, if you try to create partition, you might encounter the following error:
ERROR 1548 (HY000) at line 1: Cannot load from mysql.proc. The table is probably corrupted

Solution: on mysql 5.1, run "mysql_upgrade -uroot -ppassword", please refer to http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

8. References
a. database partitioning:http://www.slideshare.net/datacharmer/partitions-performance-with-mysql-51-and-55
b. mysql 5.5 online manual for replication at http://dev.mysql.com/doc/refman/5.5/en/replication.html

Set up bonding in RHEL6

2011-12-08T08:50:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: CentOS 6 x86_64 on HP Blade Proliant BL460c G6, 2 builtin broadcom NIC and a NC364m Quad Port 1Gb NIC for c-class BladeSystem
Objective: after using builtin NIC to install OS , then configure bonding for 2 of quad port NIC.
Steps:

1. OS installation
You can download http://mirror.optus.net/centos/6/isos/x86_64/CentOS-6.0-x86_64-netinstall.iso and install OS through http URL from Internet.

2. bonding configuration

Firstly, disable network manager if NM is running.
service NetworkManager stop
chkconfig NetworkManager off

a. /etc/udev/rules.d/70-persistent-net.ruels
make sure it contains all interfaces you have in the system and don't configure bond0 inside.

b. /etc/sysconfig/network-scripts/

configuring ifcfg-bond0 as follows: (assuming ip address is 192.168.0.1)

[root@db03 network-scripts]# more ifcfg-bond0
DEVICE="bond0"
BROADCAST="192.168.0.255"
GATEWAY="192.168.0.254"
IPADDR="192.168.0.1"
NETMASK="255.255.255.0"
ONBOOT="yes"
USERCTL=no
BONDING_OPTS="mode=0 miimon=100 downdelay=300 updelay=300 max_bonds=4"

note: you must use capital letter for left side variables, especially BONDING_OPTS, otherwise, you might not be able to see slave devices from command output of 'ifconfig'.
Do not configure BONDING_OPTS in /etc/modprobe.d/bonding.conf as suggested by Redhat.

c. /etc/sysconfig/network-scripts/ifcfg-ethX

[root@db03 network-scripts]# more ifcfg-eth*
::::::::::::::
ifcfg-eth0
::::::::::::::
DEVICE=eth0
BOOTPROTO=none
MASTER=bond0
HWADDR=00:01:02:03:04:00
ONBOOT=yes
SLAVE=yes
USERCTL=no
::::::::::::::
ifcfg-eth1
::::::::::::::
DEVICE=eth1
BOOTPROTO=none
HWADDR=00:01:02:03:04:01
ONBOOT=no
USERCTL=no
::::::::::::::
ifcfg-eth2
::::::::::::::
DEVICE=eth2
BOOTPROTO=none
MASTER=bond0
HWADDR=00:01:02:03:04:02
ONBOOT=yes
SLAVE=yes
USERCTL=no
::::::::::::::
ifcfg-eth3
::::::::::::::
DEVICE=eth3
BOOTPROTO=none
HWADDR=00:01:02:03:04:03
ONBOOT=no
USERCTL=no
::::::::::::::
ifcfg-eth4
::::::::::::::
DEVICE="eth4"
HWADDR="00:00:00:00:00:04"
NM_CONTROLLED="no"
ONBOOT="no"
::::::::::::::
ifcfg-eth5
::::::::::::::
DEVICE="eth5"
HWADDR="00:00:00:00:00:05"
NM_CONTROLLED="no"
ONBOOT="no"
note: we connected network cables for eth0 and eth2 only, so only use these 2 ports for bonding.

d. /etc/modprobe/bonding.conf
[root@db03 network-scripts]# more /etc/modprobe.d/bonding.conf
alias eth0 e1000e
alias eth1 e1000e
alias eth2 e1000e
alias eth3 e1000e
alias eth4 bnx2x
alias eth5 bnx2x
alias scsi_hostadapter cciss
alias scsi_hostadapter1 lpfc
alias bond0 bonding

3. Testing and debug
a. more /proc/net/bonding/bond0
b. ethtool eth0

4. References
a. RHEL6 deployment guide
http://docs.redhat.com/docs/en-US/Red_Hat_Enterprise_Linux/6/html/Deployment_Guide/s2-networkscripts-interfaces-chan.html

b. Redhat knowledge base DOC-48159 for 'How do I configure the bonding device on Red Hat Enteprise Linux 6" or search bonding in access.redhat.com knowledge base.

Clone Oracle virtual machine to physical server

2011-11-30T18:18:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Oracle virtual machine image file .img (shutdown VM to make image), use hda as hard disk only, no any scsi driver in initrd image file. Oracle Enterprise Linux 4.5, grub-0.95-3.8.0.1 rpm which is the latest in OL4.9
Objective: Clone it to a dell PowerEdge 1950 (megaraid scsi driver)

Steps:

1. boot from RIP CD(Recovery Is Possible) non-X version, 64bit kernel

login as root without password

vi /etc/hosts.deny to comment out the last line to allow remote ssh

vi /etc/ssh/sshd_config to allow direct root login

dhcpcd eth0 or use ifconfig to manually set up ip address

/usr/sbin/sshd

passwd root

2. transfer System.img which is Oracle VM image file over somewhere on the network which you can mount it

# file System.img

# mkdir iso

# mount System.img iso -o offset=$((512*63))

3. transfer file system over physical machine

make partition for the physical server hard disk - sda(2x72G raid1)
fdisk /dev/sda
cd /mnt
mkdir sda
mount /dev/sda1 sda
cd sda
ssh remoteserver 'cd /path/to/iso; tar cvpf - . | tar xvpf -'

4. generate mkinitrd and modify some configuration

initrd:

vi /etc/modprobe.conf

alias scsi_hostadapter megaraid_sas
alias scsi_hostadapter1 ata_piix

cd /boot

mkinitrd -f /boot/initrd.img.new 2.6.9xxxxsmp (check folder name under /lib/modules)

vi /boot/grub/grub.conf to modify accordingly to use above generated initrd.img.new

configurations:

cd /mnt/sda
chroot .
modify /etc/sysconfig/network-scritps/ifcfg-eth0 and ifcfg-eth1 if necessary
modify /etc/sysconfig/network
modify /etc/fstab
modify /etc/grub.conf
cp /etc/blkid /etc/blkid.bak
rm -f /etc/blkid/*
cp /etc/mtab /etc/mtab.bak
vi /etc/mtab (to correct the / and /boot line to have the correct partition name)
vi /boot/grub/device.map to something like '(hd0) /dev/sda'
cd /dev
./MAKEDEV sda

After that, create root, sys and proc directory for cloned virtual server.

cd /mnt/sda
mkdir proc sys root

5. generate MBR

(install it again everytime you modify file /etc/grub.conf)

# grub-install hd0

Or after reboot, at grub prompt:\

root (hd0,0) -> hd0 means the first hard disk, 0 means /boot directory is sitting on the first partition, if /dev/sda3 is /boot partition, this should be 'root (hd0,2)'

find /grub/stage1
find /grub/stage2
setup (hd0)

reboot

Note: if you cannot use grub-install to install MBR for grub boot loader, you can use RIP CD to boot GRUB from cdrom or usb option to boot the OS on the first hard disk.

then you can fix it from actual os itself.

------
/etc/grub.conf
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Oracle Linux Server (2.6.32-100.26.2.el5uek)
    root (hd0,0)
    kernel /vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda3
    initrd /initrd-2.6.32-100.26.2.el5.img

note:
a. splashimage=(hd0,0)/grub/splash.xpm.gz
means /boot partition is the first partition on first hard disk hd0, it's /grub, not /boot/grub
b. kernel /vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda3
means /vmlinuz-2.6.32-100.26.2.el5, not /boot/vmlinuz-2.6.32-100.26.2.el5 since it's already specified root (hd0,0)
c. initrd /initrd-2.6.32-100.26.2.el5.img
means /initrd-2.6.32-100.26.2.el5.img, not /boot/initrd-2.6.32-100.26.2.el5.img if /boot is a separated partition

6. case study
/boot is not separated partition, after clone, it becomes seperated one, you need to change grub configuration as follows:

previous: (no /boot partition, only / one partition)
#boot=/dev/hda
default=0
timeout=5
splashimage=(hd0,0)/boot/grub/splash.xpm.gz
hiddenmenu
title Oracle Linux Server (2.6.32-100.26.2.el5)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.32-100.26.2.el5 ro root=LABEL=/
        initrd /boot/initrd-2.6.32-100.26.2.el5.img

after: (/boot is at /dev/sda3)
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,2)/grub/splash.xpm.gz
hiddenmenu
title Oracle Linux Server (2.6.32-100.26.2.el5)
        root (hd0,2)
        kernel /vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda1
        initrd /initrd-2.6.32-100.26.2.el5.img.new

7. possible solution
a. error: /boot/grub/stage1 not read correctly when I tried to install GRUB by grub-install /dev/sdc to the new drive.

Grub consults your /etc/fstab and /etc/mtab files to determine which partition/drive the '/boot' directory is actually on, so check those files are correct. (See the next section for more details about this.)

solution 1: to create a separated /boot partition after boot from RIP CD boot loader, then modify /etc/grub.conf and install grub again:

grub-install /dev/sda

or

find /boot/grub/stage1
root (hd0,0)
find /boot/grub/stage1
setup hd0

solution 2:
Problem might be that the old legacy GRUB can not handle GPT partition tables. You can simply fix this with the tool gptsync:

gptsync /dev/sdc ( not tested)

How to make mysql slave db to sync with master again

2011-11-29T20:51:00.020+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: mysql 5.5, CentOS 5.6 64bit for both master(192.168.0.1) and slave(192.168.0.2)
Objective: to make slave db to sync with master again

You can use manual method as stated on http://mysql-mmm.org/mmm2:guide for configuring mysql replication.

Steps:

1. [slave] stop slave mysql database
/etc/init.d/mysql stop
rm -fr /srv/mysql/data /srv/mysql/log

2. [master]
use mylvmbackup(http://www.lenzg.net/mylvmbackup/) to create snapshot for LVM volume where mysql db data and log file resides.
modify /etc/mylvmbackup.conf to keep_snapshot=1 and keep_mount=1

# copy mounted snapshot data and log files to slave database
scp -pr /snap/backup/data /snap/log slavedb:/srv/mysql/

# record down the master log file and position
root@db02:/srv/mysql-db2-snap/backup-pos/ # more backup-20111129_110147_mysql.pos
Master:File=mysql-bin.000046
Master:Position=25449360

Note: log folder on master database is not useful if there's no slave database which will use it, you can rm -fr log/* after shutting down master database, then make code backup for data folder only.

3. [slave]
cd /srv/mysql
chown mysql:mysql -R data log
/etc/init.d/mysql start
mysql -uroot -ppassword
mysql> show slave status\G

mysql> stop slave;
mysql> change master to MASTER_HOST='192.168.0.1', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000046', MASTER_LOG_POS=25449360;

or after checking 'show slave status\G', the master host is correct, you can just use:
mysql> change master to master_log_file='mysql-bin.000046', MASTER_LOG_POS=25449360;

mysql> start slave;
mysql> show slave status; make sure it shows:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

monitoring Seconds_Behind_Master: 7770 column to make sure it's reducing until 0 which means it finishs syncing with master again.

4. [slave] make master to sync with slave too
on slavedb, run
mysql> show master status\G;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000055 | 17584079 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5. [master] make it sync with slave
mysql> stop slave;
if not, maybe
mysql> reset slave;
mysql> change master to master_log_file='mysql-bin.000055', MASTER_LOG_POS=17584079;
mysql> start slave;
mysql> show slave status\G

note: you have to run 'show master status' first on slavedb, then record down master_log_file and master_log_pos, then use it on master db to make it sync with slave also.

6. [master] use pt-table-checksum to compare 2 databases
show tables status;
create database pt;
pt-table-checksum -uroot -ppassword --create-replicate --replicate pt.checksums --databases zabbix --empty-replicate-table --chunk-size=5000 localhost

pt-table-checksum -uroot -ppassword --create-replicate --replicate pt.checksums --databases zabbix --empty-replicate-table --chunk-size=5000 localhost --replicate-check=1

echo $?
0
----------------------------
or skip some huge tables
----------------------
pt-table-checksum -uroot -ppassword --create-replicate --replicate pt.checksums --databases zabbix --empty-replicate-table --chunk-size=30000 --chunk-size-limit 50 localhost --ignore-tables events,history,history_uint,trends,trends_uint --replicate-check 1

note:use phpmyadmin or show table status to get rough size of tables which might be skipped for pt-table-checksum.
--------------------------
If it's different between master and slaves, it will show by paramter --replicate-check 1

pt-table-checksum -uroot -ppassword --create-replicate --replicate pt.checksums --databases mysql --empty-replicate-table --chunk-size=30000 --chunk-size-limit 50 localhost --replicate-check 1
Differences on P=3306,h=db03
DB    TBL           CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
mysql db                0        1        1 1=1
mysql help_keyword      0       -1        1 1=1
mysql help_relation     0        1        1 1=1
mysql help_topic        0        1        1 1=1
mysql proc              0        0        1 1=1

echo $?
1
------------------
You can make mysql database itself sync between master and slaves by the following method:

on master:
mysqldump --add-drop-table mysql -uroot -ppassword | ssh db03 'mysql -uroot -ppassword mysql'

mysql -uroot -ppassword -h db03
sql> flush privileges;

FAQ:
root@db02:/etc/cron.d/ # pt-table-checksum -uroot -ppassword --create-replicate --replicate pt.checksums --databases zabbix --empty-replicate-table --chunk-size=5000 localhost
You do not have the PROCESS privilege at /usr/bin/pt-table-checksum line 3761.
root@db02:/etc/cron.d/ # mysql -uroot -ppassword -h slavedb zabbix
ERROR 1044 (42000): Access denied for user 'root'@'192.168.7.2' to database 'zabbix'

[root@db03 sysconfig]# mysql -uroot -ppassword
mysql> grant all on zabbix.* to root@'192.168.7.22' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

try again.
or

The following method will definitely work when using pt-table-checksum:

mysql> grant all privileges on *.* to root@'172.17.7.22' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

otherwise, you might encounter error like 'You do not have the PROCESS privilege at /usr/bin/pt-table-checksum line 3761.'

References:
1. how to completely delete snapshot logical volume
df -h
umount /mountpoint_for_snapshot_volume
lvdisplay -v
lvremove /dev/VolGroup02/snap02
cd /dev/mapper
dmsetup remove snap02
or
kpartx -d xxxxx

2. purge master log - http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
This statement is safe to run while slaves are replicating. You need not stop them, it will Purge all log files up to but not including the target file.
example:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days (see Section 5.1.3, 揝erver System Variables?. If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.

mysql> show master logs;

#!/bin/bash
CURRENT_LOGFILE=$(/usr/bin/mysql -e "SHOW SLAVE STATUS\G" | awk '$1 == "Master_Log_File:" {print $2}')
/usr/bin/mysql -h MASTER -e "PURGE MASTER LOGS TO '${CURRENT_LOGFILE}'"
exit $?

note: cronjob script to purge master log on slave db:

3. master/slave /etc/my.cnf configuration:
note: server_id must be different. If not, change it, restart mysqld service.
sql> reset slave;
sql> change master to ....
sql> start slave;

slave:

root@db01:/srv/mysql/log/ # cat /etc/my.cnf | grep -v ^# | grep -v ^$
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
default-storage-engine=innodb
innodb_file_per_table
innodb_lock_wait_timeout = 150
innodb_rollback_on_timeout
innodb_buffer_pool_size=20000M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
transaction-isolation=READ-COMMITTED
log-queries-not-using-indexes
key_buffer_size = 64M
user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /srv/mysql/data
tmpdir          = /tmpfs
skip-external-locking
bind-address            = 0.0.0.0
key_buffer              = 128M
max_allowed_packet      = 128M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections=512
tmp_table_size=256M
max_heap_table_size=256M
table_cache=1024
thread_cache=16
read_buffer_size        = 2M
read_rnd_buffer_size    = 2M
sort_buffer_size        = 2M
thread_stack            = 256K
join_buffer_size        = 8M
log-queries-not-using-indexes
thread_concurrency     = 32
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 16M
log_error                = /srv/mysql/log/error.log
log_slow_queries        = /srv/mysql/log/mysql-slow.log
long_query_time = 2
server_id=1
log_bin             = /srv/mysql/log/mysql-bin.log
log_bin_index       = /srv/mysql/log/mysql-bin.log.index
relay_log           = /srv/mysql/log/mysql-relay-bin
relay_log_index     = /srv/mysql/log/mysql-relay-bin.index
expire_logs_days    = 2
max_binlog_size     = 100M
log_slave_updates   = 1
binlog-format=MIXED
sync_binlog=1

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
[isamchk]
key_buffer              = 16M
------------------------------------
root@db02:/srv # cat /etc/my.cnf | grep -v ^# | grep -v ^$
[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
default-storage-engine=innodb
innodb_file_per_table
innodb_lock_wait_timeout = 150
innodb_rollback_on_timeout
innodb_buffer_pool_size=20000M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
transaction-isolation=READ-COMMITTED
log-queries-not-using-indexes
key_buffer_size = 64M
user        = mysql
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /srv/mysql/data
tmpdir        = /tmpfs
skip-external-locking
bind-address        = 0.0.0.0
key_buffer        = 128M
max_allowed_packet    = 128M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections=512
tmp_table_size=256M
max_heap_table_size=256M
table_cache=1024
thread_cache=16
read_buffer_size        = 2M
read_rnd_buffer_size    = 2M
sort_buffer_size        = 2M
thread_stack            = 256K
join_buffer_size        = 8M
log-queries-not-using-indexes
thread_concurrency     = 32
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 16M
log_error                = /srv/mysql/log/error.log
log_slow_queries    = /srv/mysql/log/mysql-slow.log
long_query_time = 2
server_id=2
log_bin             = /srv/mysql/log/mysql-bin.log
log_bin_index       = /srv/mysql/log/mysql-bin.log.index
relay_log           = /srv/mysql/log/mysql-relay-bin
relay_log_index     = /srv/mysql/log/mysql-relay-bin.index
expire_logs_days    = 2
max_binlog_size     = 100M
log_slave_updates   = 1
binlog-format=MIXED

#added by Jephe to ignore replication error, use all or comma separated error codes,such as 1507 for dropping non-existed partitions.
#slave-skip-errors=all

[mysqldump]
quick
quote-names
max_allowed_packet    = 16M

[mysql]
[isamchk]
key_buffer        = 16M

4. commands
a. grant pt.checksums table to my pc ip 192.168.0.100 with login id root and password as 'password'

mysql> grant all privileges on pt.* to root@'192.168.0.100' identified by 'password';
mysql> flush privileges;
mysql> show grants;

5. documents
http://www.howtoforge.com/mysql5_master_master_replication_debian_etch

6. References
http://dev.mysql.com/doc/refman/5.5/en/replication.html
http://mysql-mmm.org/mmm2:guide - 2 masters/2 slaves mysql db and mmm2 setup guide

7. how to ignore db for replication setup ( http://www.packtpub.com/article/install-manage-use-mmm-for-mysql-high-availability )
Now configure the mysqld section /etc/my.cnf on both nodes with the following steps:

Prevent the server from modifying its data until told to do so by MMM. Note that this does not apply to users with SUPER privilege (that is, probably you at the command line!):
```
read-only
```
Prevent the server from modifying its mysql database as a result of a replicated query it receives as a slave:
```
replicate-ignore-db = mysql
```
Prevent this server from logging changes to its mysql database:
```
binlog-ignore-db = mysql
```

how to access IBM p5 ASMI through firefox

2011-11-07T19:11:00.002+08:00

Jephe Wu - http://linuxrechres.blogspot.com

Environment: IBM P5 ASMI IP address 192.168.0.254.
Problem: firefox and IE are not able to access https://192.168.0.254

Steps:

1. use default ip address (192.168.2.147/24 and 192.168.3.147), laptop and crossover cable to configure ASMI ip address to 192.168.0.254.

2. use elinks to access it , then you can only see login page.

3. enable security.ssl3.rsa_rc4_40_md5 in firefox about:config to access https://192.168.0.254

References:
Front cover Integrated Virtualization Manager on IBM System p5 - http://www.redbooks.ibm.com/redpapers/pdfs/redp4061.pdf

How to be familiar with a new Oracle database

2011-10-30T07:15:00.006+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to take over a new database and issue some commands to know the basic things about this database

Commands:

a. know what platform it is (workable on 10g)
select name, platform_id,platform_name from v$database;

note: use the following view result to get the endian table:
select PLATFORM_ID,platform_name,endian_format from v$transportable_platform;

b. check if it's cluster database (RAC)
show parameter cluster_database;

c. check instance status
select * from v$instance;
note: you can find out instance_name, version, startup_time, status etc

d. check database status
select * from v$database;

note: you can find out dbid, dbname, db_uniq_name, created_date, log_mode, db_role, platform_name etc

e. check datafile, logfile and controlfile name and location

select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;
select destination from v$archive_dest;

f. check archive log status
archive log list;
or
select log_mode from v$database;

g. check datafile, tempfile and online logfile size
select sum(bytes)/1024/1024 "MB" from dba_data_files;
select sum(bytes)/1024/1024 "MB" from dba_temp_files;
select sum(bytes)/1024/1024 from v$log;

h. check using spfile or pfile
show parameter spfile;

i. check dataguard status
select database_role from v$database;
(primary or standby)

dgmgrl / as sysdba
show configuration

# check archive log destination settings.
select destination, error from v$archive_dest;

j. check characterset
select * from nls_database_parameters;
select * from v$nls_parameters;

commonly used commands:
1. alter system register
2. alter system checkpoint
3. alter system switch logfile;

References:
1. alter system syntax
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#SQLRF00902

Upgrading Oracle Database Server from 10.2.0.4 to 11.2.0.2 by dbua

2011-10-27T18:08:00.012+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2003 server SP2 32bit, Oracle 10.2.0.4(was upgraded from Oracle 9.2)
Objective: upgrade it to Oracle standard edition 11.2.0.2.

Steps:

Part I - Upgrading Oracle database

1. backup before doing anything.
a. rman backup
Sign on to RMAN:
rman "target / nocatalog"
Issue the following RMAN commands:
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';
or
BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
}

or

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

b. database full export backup (expdp)
set ORACLE_SID=ORCL
exp backup_admin/password file=d:\oracle\oradata\orcl\backup\full_orcl.dmp log=d:\oracle\oradata\orcl\backup\exp_full_orcl.log consistent=y full=y

c. binary backup (cold copy)
Backup c:\program files\oracle directory and d:\oracle data directory.
If you have enough space to backup, no matter how big the temporary tablespace is, just copy them.

After doing copy and before running dbua, check temporary tablespace datafile size, shrink it if it's huge as follows:

shutdown listener and make sure nobody is using database (bounce db if required)

SQL> SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'TEMP';

----------
TEMP
D:\ORACLE\ORADATA\orcl\TEMP01.DBF
1.7826E+10

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\orcl\TEMP01.DBF' DROP INCLUDING DATAFILES;
Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE 'D:\ORACLE\ORADATA\orcl\TEMP01.DBF' SIZE 50m
AUTOEXTEND ON NEXT 20m MAXSIZE 17000M [EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576];
Tablespace altered.

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';

d. Windows OS backup
shutdown Windows, use RIP CD or system rescue cd to backup the whole C drive(c:\program files\oracle) and d:\oracle (where data exists) by ntfsclone program.

boot from RIP CD
a. backup partition table
sfdisk -d /dev/cciss/c0d0 | ssh remoteserver 'cat > /path/to/c0d0_sfdisk-d'
for restore, use
ssh remoteserver 'cat /path/to/c0d0_sfdisk-d' | sfdisk /dev/cciss/c0d0

b. backup MBR
dd if=/dev/cciss/c0d0 count=1 bs=512 | ssh remoteserver 'dd of=/path/to/mbr_dd'
for restore, use
ssh remoteserver 'dd if=/path/to/mbr_dd' | dd of=/dev/cciss/c0d0

c. backup ntfs partition /dev/cciss/c0d0p1
ntfsclone -s -o - /dev/cciss/c0d0p1 |gzip -c | ssh remoteserver 'cat > /path/to/c0d0p1_ntfsclone.gz'
for restore, use
ssh remoteserver 'gzip -dc /path/to/c0d0p1_ntfsclone.gz' | ntfsclone -r - -O /dev/cciss/c0d0p1

e. backup OS related files c:\program files\oracle
backup c:\program files\oracle

2. Software Installation (11.2.0.2)
a. check before software installation
check the existing database charactset

sql> select * from nls_database_parameters;

backup the existing spfile to pfile [optional]
sql> create pfile from spfile

b. Unzip both 1/6 and 2/6 zip files into same directory, then perform installation (must unzip and put them into the same directory)
c. Execute runInstaller to install ‘software only’ without creating database

3. Pre-upgrading preparation

a.[optional] Shrink temporary tablespace if required as above, otherwise, during installation, 11g will try to use cold backup method to copy all control,redo and data files to d:\oracle\admin\backup directory, including huge temporary tablespace files.

note: for all methods to shrink temporary tablespace under 10g, refer to support article - Resizing (or Recreating) the Temporary Tablespace [ID 409183.1]

Make sure nobody is using the database (stop listener service and bounce database if needed), if someone is accessing database, refer to above support ID for another way to shrink temporary tablespace.

b. Confirm you have done backup for your database as step 1
During upgrade process, 11g will shutdown current database to do cold backup automatically before it performs the upgrade procedure if we enable backup option. 11g dbua will also create a batch file D:\oracle\product\10.2.0\admin\orcl\backup\SID_restore.bat for restoring our backup in case of upgrade failure.

c. After installing 11g db, Run pre-upgrade information tool
Once 11g software is installed, such as d:\oracle\11.2.0.2\db_1, copy %ORACLE_HOME%\rdbms\admin\utlu112i.sql to somewhere, now let's login to 10g db as sysdba and startup database.

Sql> spool \path\to\upgrade.sql
Sql> @utlu112i.sql or
@$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql
Sql> spool off

d. Adjust parameters if necessary before running ‘dbua’
Sql> purge dba_recyclebin;
Sql> alter system set sga_target=596M scope=spfile [might be adviced by pre-upgrade script,optional]
Sql> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Sql> SELECT * FROM v$recover_file; should be no media files to recover

Resolving Outstanding Distributed Transactions
Sql> SELECT * FROM dba_2pc_pending;
If the query in the previous step returns any rows, then issue the following statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

Ensure no files are no backup mode:
Sql> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

Check the current flush recovery area settings:
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\flash_recovery_area
db_recovery_file_dest_size           big integer 5000M

Record down the number of invalid objects before running dbua.
Sql> select owner,object_name,object_type from dba_objects where status='INVALID';
For example:
"OWNER"    "OBJECT_NAME"    "OBJECT_TYPE"
"MDSYS"    "SDO_INDEX_METHOD_9I"    "TYPE BODY"
"WKSYS"    "WK_UTIL"    "PACKAGE BODY"
3 rows selected.

Check compatibility parameter before running ‘dbua’, the minimum value required by 11.2 is 10.0.0.0.0.

Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional).
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
SQL> ALTER SYSTEM SET COMPATIBLE = '10.0.0' SCOPE=SPFILE;

For invalid objects or invalid components, Oracle recommends running the the utlrp.sql before starting the upgrade as a means to minimize the number of invalid objects and components marked with WARNING.

Get characterset from the current database
sql> select * from nls_database_parameters;
record down nls_characterset and nls_nchar_characterset.

4. Run dbua to upgrade database

Inform the support team we are going to upgrade, ask them not to touch server.

a. actual upgrade by dbua
Cd $ORACL_HOME ( for Windows, start- program-choose 11g, database upgrade assistant )
Cd bin
./dbua &

Do Not Move Database Files as Part of Upgrade
Choose ‘backup database’ and upgrade timezone version to version 14

Upgrade log is at C:\oracle\cfgtoollogs\dbua\orcl\upgrade2

b. Identifying Invalid Objects
After running pre-upgrade information tool, any invalid SYS/SYSTEM objects found before upgrading the database are stored in the table named registry$sys_inv_objs.
Any invalid non-SYS/SYSTEM objects found before upgrading the database are stored in registry$nonsys_inv_objs.

(sqlplus / as sysdba;
select * from sys.registry$sys_inv_objs;
select * from sys.registry$nonsys_inv_objs;)

To identify any new invalid objects due to the upgrade
run @%ORACLE_HOME%\rdbms\admin\utluiobj.sql after finishing upgrade.

SQL> select owner,object_name,object_type from dba_objects where status='INVALID';
should see the same number of invalid objects as sys.registry$sys_inv_objs and sys.registry$nonsys_inv_objs

c. Check the flush recovery area settings and confirm archive logging mode is enabled.
sql> show parameter db_recovery_file_dest;
Sql> archive log list;

d. Configure Enterprise Manager if required
If you are not yet using Oracle Enterprise Manager to manage your database, then install and configure Enterprise Manager Database Control.
If your database is being managed by Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control, then use the following command to update the configuration:
emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]
You must run this from the Oracle home of the new Oracle Database 11g release. When prompted, provide the Oracle home from which the configuration is being upgraded.
You can also configure Enterprise Manager using DBCA. Select the Configure Database Options option, and then select the Enterprise Manager Repository option.

5. Rollback plan
a. Use the script that generated by dbua to restore to old 10g database
C:\oracle\admin\sid\backup\sid_restore.bat

b. use our own rman backup to restore back

New features and changes in 11.2
Automatic Diagnostic Repository
The locations of alert logs and trace files are no longer set by the initialization parameters BACKGROUND_DUMP_DEST and USER_DUMP_DEST. They are now kept in the Automatic Diagnostic Repository (ADR), whose location is set the by the initialization parameter DIAGNOSTIC_DEST.

JOB_QUEUE_PROCESSES Parameter
Beginning with Oracle Database 11g Release 1 (11.1), the JOB_QUEUE_PROCESSES parameter is changed from a basic to a non-basic initialization parameter. Most databases only need to have basic parameters set in order to run properly and efficiently. The default value is also changed from 0 to 1000.
Starting with Oracle Database 11g Release 2 (11.2), setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run.

6. FAQ

1.    When installing 10.2.0.4 over 10.2.0.1 oracle, it prompts the following error message:
Cannot write to c:\oracle\product\10.2.0\db_1\bin\msvcr71.dll,
When using ‘process explorer’ and found process msdtc is holding it, which is ‘microsoft distributed coordinator’, stop that services from services.msc, if necessary, install ‘process explorer’ to find handle or dll for msvcr71.dll

2.    Services, ‘oracleserviceORCL’ previously point to 10.2, after upgrade, will point to 11.2,
OracleOraDb11g_home1TNSListener (C:\oracle\product\11.2.0\dbhome_2\BIN\TNSLSNR ) service will be created during upgrading (dbua), at the time start doing enterprise manager repository data migrate, it will prompt you to backup EM data for downgrade, after you say ‘yes’ to continue, it will start a 11g listener.

3.    EM create repository, got error SEVERE: 'job_queue_processes' must be greater than or equal to 1.
Sql> Alter system set job_queue_processes=2; can be changed dynamically.

Part II - actual upgrade notes

1. upgrading Oracle
reboot server into Windows, follow the Oracle upgrade guide, install Oracle database 11.2.0.2 32bit for Windows in a separated directory

2. cleanup the database before upgrading
a. sql>exec dbms_stats.gather_dictionary_stats;
b. sql>purge dba_recyclebin;
c. sql> select * from v$recover_file; should be no media file to recover

d. Sql> SELECT * FROM dba_2pc_pending;
If the query in the previous step returns any rows, then issue the following statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

e. Ensure no files are no backup mode:
Sql> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

f. Check the current flush recovery area settings:
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\flash_recovery_area
db_recovery_file_dest_size           big integer 5000M

g. Record down the number of invalid objects before running dbua.
Sql> select owner,object_name,object_type from dba_objects where status='INVALID'
For devora01, the following is the output:
"OWNER"    "OBJECT_NAME"    "OBJECT_TYPE"
"MDSYS"    "SDO_INDEX_METHOD_9I"    "TYPE BODY"
"WKSYS"    "WK_UTIL"    "PACKAGE BODY"
3 rows selected.

h. Check compatibility parameter before running ‘dbua’, the minimum value required by 11.2 is 10.0.0.0.0.
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';

i. run downloaded script dbupgdiag.sql from Oracle support to check the number of invalid objects detail
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

after that,Validating invalid objects first
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

j. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.
SQL> @$ORACLE_HOME\rdbms\admin\utlrp.sql
Verify that all expected packages and classes are valid:
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';

k.Note down the location of datafiles, redo logs, control files. Also take a backup of all configuration files like listener.ora,tnsnames.ora (C:\oracle\product\11.2.0\dbhome_2\NETWORK\ADMIN) ,etc., from $ORACLE_HOME

SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;

Check services.msc to confirm
OracleServiceORCL is started
OracleOraDb10g_home1TNSListener is started
c:\windows\system32\drivers\etc\hosts has a line for EM to work
ipaddress name_with_domain name

run select * from nls_database_parameters
to get nls_characterset WE8MSWIN1252
and nls_NCHAR_CHARACTERSET as AL16UTF16

backup
backup c:\program files\oracle to f:\jephe\
bakcup d:\oracle to f:\jephe
record down the number of files and folders for d:\oracle
104927 files and 12986 folders around 30G
after done backup, compare the number of files and folders to make sure they are same.

stop 10g listener
sqlplus / as sysdba
shutdown immediate;
go to services.msc to stop OracleServiceORCL service.

sqlplus / as sysdba
sql> spool d:\dba\upgrade.sql
sql> @......\utlu112i.sql
sql> spool off

change compatible to 10.1.0
execute dbms_stats.gather_dictionary_stats;

3. startup 'database upgrade assistance -dbua' from start- program files - oracle 11g to upgrade the 10g database,
follow the GUI to finish upgrading.

dbua from 11gR2
significant changes before dbua
a. compatible=10.1.0
b. tempfile resize

do not move database file
backup database
upgrade timezone to 14

8. checking after upgrade

a. it should be in archive log mode
sql> archive log list;
sq> SELECT name, value FROM v$parameter WHERE name = 'compatible';
compatible parameter should be in 10.1.0.
b. check if Enterprise Manager is able to start up.
c. query invalid objects generated by upgrading process

start upgrade at 3pm.
14% to 34% to finish 'upgrading Oracle server' part.
34%-38% for upgrading Jserver Java virtual machine
upgrading EM repository takes time - 48%-71%
upgrading Oracle XML database takes long time
post upgrading database also takes time

9. Reconfigure EM

Although inside windows services.msc dbconsole is not starting, but port 5500 might be already running and you can access https://hostname:5500/em/

If you need to recreate EM, do this:
a.    Drivers\etc\hosts to add ip and hostname
b.    Make sure sysman account Is there and you can login as sysman
c.    Check if can login as sysman and dbsnmp

disable EM SSL , just use http
emctl unsecure dbconsole

10. Abandon the Upgrade
a. To cancel the upgrade by restoring the previous backup

Log in to the system as the owner of the Oracle home directory of the previous release.
Sign on to RMAN:

sql> rman "target / nocatalog"

    STARTUP NOMOUNT
    RUN
    {
        RESTORE CONTROLFILE FROM 'save_controlfile_location';
        ALTER DATABASE MOUNT;
        RESTORE DATABASE FROM TAG before_upgrade
        ALTER DATABASE OPEN RESETLOGS;
    }

b. or use dbua generated sid_restore.bat script to restore the cold backup back.

11. References:
a. http://www.diybl.com/course/7_databases/oracle/Oracleshl/20110207/553475.html
b. How to create oracle Windows service - http://psoug.org/reference/oradim.html
c. Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
d. Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]

12. configuration files location

a. Dbconsole log file is at c:\oracle\product\11.2.0\dbhome_2\xxx_orcl\sysman\log
b. db console config file at C:\oracle\product\11.2.0\dbhome_2\xxx_orcl/sysman/config/emd.properties
c. listener.ora and tnsname.ora is at C:\oracle\product\11.2.0\dbhome_2\NETWORK\ADMIN by default
d. 1/6 and 2/6 zip file for 11.2 contains 2824files, 906 folders and totally around 2.18G after unzip.

13. check scheduled task scripts, rewrite Oracle backup script if necessary

How to create Oracle database service and listener on Windows 2003 server

2011-10-26T17:23:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2003 server SE, upgrading Oracle 10.2.0.4 standard edition to 11.2.0.2
Objective: to be familiar with Oracle service and listener creation and deletion in case needed.
Summary
Normally, if you install Oracle 11g on Windows, it'll create listener and service automatically. In case you have deleted them and need to create back, here are the steps:

Steps

1. delete Oracle Windows service (database and listener)

a. manually delete from registry
run regedit or regedt32
Open regedit and browse to the following key
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

Find the service name 'OracleOraDb11g_home1TNSListener' or 'OracleOraDb10g_home1TNSListener'
right click it and export to a .reg file, then delete the key. (You might need to reboot server as refresh might not be able to remove service name completely, if that's the case, try also the following sc command to remove it)

b. use sc to delete service

backup the registry according to point a, then run:

sc delete OracleOraDb11g_home1TNSListener

2. create Oracle Windows service - Listener
a. sc

Create listener service:
sc create OracleOraDb11g_home1TNSListener [binPath= "C:\oracle\product\11.2.0\dbhome_2\BIN\TNSLSNR.exe"]
note: after binPath=, there must be a space there

b. run netca to create listener service by Oracle

Start -> All programs -> Oracle - OraDb11g_home1 -> Configuration and Migration Tools -> Net Configuration Assistant

When asking for Listener Name, don't change anything, just leave the default name 'LISTENER' there, it will create service name as OracleOraDb11g_home1TNSListener

3. create Oracle Windows service - database

a. stop OracleServiceorcl service (orcl is the sid here)

net stop OracleServiceORCL

b. use oradim to delete service

c:\> oradim -delete -sid orcl

c. use oradim to create again

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
Or
c:\> oradim -new -id orcl -startmode auto
to use OS authentication

SID    The same SID name as the SID of the database you are upgrading.
PASSWORD    The password for the new release 10.2 database instance. This is the password for the user connected with SYSDBA privileges. The -INTPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required.
USERS    The maximum number of users who can be granted SYSDBA and SYSOPER privileges.
ORACLE_HOME    The release 10.2 Oracle home directory. Ensure that you specify the full path name with the -PFILE option, including drive letter of the Oracle home directory.

4. References
a. http://publib.boulder.ibm.com/infocenter/ltscnnct/v1r0/index.jsp?topic=/com.ibm.help.lotus.connections.doc/t_db_oracle_listener.html
b. http://psoug.org/reference/oradim.html
c. http://www.theeldergeek.com/add_a_service_in_windows_xp.htm

How to cleanup Windows disk space?

2011-10-25T19:00:00.001+08:00

Jephe Wu - http://linuxrechres.blogspot.com

Objective: explore ways to clean up Windows disk drive space.
Methods:

1. use 'portable folder' software to get bigger picture
use portable folder to clean up C/D drive recycler folder
2. delete browser cache files and disk cleanup to compress unused files, also empty recycle bin
3. clear system center configuration manager cache files ( control panel - configuration manager - delete cache)

5. create a scheduled task to use tool 'blat' to send disk space notification by email

dir c:\ d:\ | find "free" > c:\blat\drivespace.txt
dir c:\ d:\"sql backups\daily backups" /OD >> c:\blat\drivespace.txt
c:\blat\blat c:\blat\drivespace.txt -subject "C and D Drive free Space and database daily backup logs" -to jwu@domain.com -server smtp -f server@domain.com

How to get HP ILO ip address?

2011-10-25T18:34:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: get the HP Proliant server IP address online or offline
Environment: Windows and/or Linux

Steps:

1. hponcfg
search google 'HP Lights-Out Online Configuration Utility for Windows' or 'HP Lights-Out Online Configuration Utility for Linux' to access the following site:

http://h20000.www2.hp.com/bizsupport/TechSupport/SoftwareIndex.jsp?lang=en&cc=us&prodNameId=1135772&prodTypeId=18964&prodSeriesId=1146658&swLang=8&taskId=135&swEnvOID=1005

C:\Program Files\HP\hponcfg>hponcfg /w iLo.xml

This will export the current settings from ILO, you can modify it then import it:

C:\Program Files\HP\hponcfg>hponcfg /f ilo.xml

2. web interface
Install HP PSP on Windows/Linux, access it through http://localhost:2301 or https://localhost:2381, management processor part to find out the IP address and link status.

3. Reboot the server and press the F8 key when the server is booting and displays the HP integrated Lights Out text.

4. References:
a. http://www.itaspirin.com/node/10 - Reconfigure iLo network settings using HPONCFG

b. HP Lights-Out Online Configuration Utility for Linux
http://h20000.www2.hp.com/bizsupport/TechSupport/SoftwareDescription.jsp?lang=en&cc=us&prodTypeId=15351&prodSeriesId=1121486&swItem=MTX-da4fdbec8db549b8a98de5d9c3&mode=4&idx=1

c. HP Lights-Out Online Configuration Utility for Windows Server 2003/2008
http://h20000.www2.hp.com/bizsupport/TechSupport/SoftwareDescription.jsp?lang=en&cc=us&prodTypeId=18964&prodSeriesId=1146658&prodNameId=1135772&swEnvOID=1114&swLang=8&mode=2&taskId=135&swItem=MTX-ea306043175c40bcb35952a862

How to prepare Oracle Installation on OEL5/RHEL5/CentOS5

2011-10-25T18:31:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Linux 5.6 x86_64(OEL/CentOS/RHEL), without ULN support

Objective: preparing minimal system to install Oracle database

Steps:

1. OEL5
as oracle-validated rpm requires UEK kernel(Unbreakable Enterprise kernel). The oracle-validated package is intended for OEL and RHEL systems registered and configured to use the Unbreakable Linux Network (ULN)

a. install Oracle-validated rpm from http://oss.oracle.com/el5/oracle-validated/
b. prepare public Oracle yum repository according to http://public-yum.oracle.com/
b. yum install oracle-validated-1.1.0-7.el5.x86_64.rpm
it will install all required rpm, but it won't prepare system such as creating oracle user, /etc/sysctl.conf etc

2. RHEL5/CentOS5
a. get required package from OEL5 system if you have, refer to point 1, install those rpms manually
or
b. just run command rpm -Uhv oracle-validated-<version>-<release>.rpm to get missing dependencies, just install them manually.
c. check required packages list from Oracle support article:
Master Note of Linux OS Requirements for Database Server [ID 851598.1]
then install those rpms manually.

References:
a. http://public-yum.oracle.com/
b. http://gerardnico.com/wiki/database/oracle/oracle-validated
c. http://oss.oracle.com/el5/oracle-validated/
d. Oracle support ID: Linux OS Installation with Reduced Set of Packages for Running Oracle Database Server [ID 728346.1]
e. Oracle Linux source rpms at http://oss.oracle.com/ol5/ and http://oss.oracle.com/ol6/

How to enable core dump for application users under RHEL5

2011-09-27T17:38:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: enable core dump for application and users
Environment: RHEL5

Steps:
1. enable it for interactive login users globally
By default, it's disabled in Linux, you can change file /etc/profile.
from
    # No core files by default
    ulimit -S -c 0 > /dev/null 2>&1
to
    ulimit -c unlimited >/dev/null 2>&1

or for individual user, edit your $HOME/.bash_profile.

2. for those program started by daemon or services, please add the following into /etc/sysconfig/init
#added by Jephe for enabling core dump for application users
DAEMON_COREFILE_LIMIT='unlimited'

This environment variable will be picked up by /etc/init.d/* daemons/services.

To enable core dump for individual daemon /etc/init.d/abc
add the following into that file /etc/init.d/abc after ". /etc/rc.d/init.d/functions" line - RHEL
DAEMON_COREFILE_LIMIT='unlimited'

or for other distribution
ulimit -c unlimited >/dev/null 2>&1
echo /tmp/core > /proc/sys/kernel/core_pattern

3. specify core dump file location
By default, the core dump file will be generated at program directory, you can change it to /tmp as follows:

echo "/tmp/core" > /proc/sys/kernel/core_pattern

or add it to /etc/sysctl.conf
kernel.core_pattern = /tmp/core
then run 'sysctl -p'

note:
a.it was 'core' in /proc/sys/kernel/core_pattern

b.core dump file will be generated as /tmp/core.$PID, provided kernel.core_uses_pid=1 which is default in RHEL

c. you can also set kernel.core_pattern as
kernel.core_pattern = /tmp/core-%e-%s-%u-%g-%p-%t

    %% - A single % character
    %p - PID of dumped process
    %u - real UID of dumped process
    %g - real GID of dumped process
    %s - number of signal causing dump
    %t - time of dump (seconds since 0:00h, 1 Jan 1970)
    %h - hostname (same as ’nodename’ returned by uname(2))
    %e - executable filename

3.1 for suid program
echo 2 > /proc/sys/fs/suid_dumpable (for RHEL5)

4. testing
a. ssh as normal user, to run command 'sleep 1000 &'
c. run 'kill -s SIGSEGV $$'.
d. assuming 15043 is the PID, check if file /tmp/core.15043 exists.
e. if successful, logout then ssh again to the server, restart your program

4.1 to revoke core dump settings above
edit /etc/profile
edit /etc/sysconfig/init
echo 0 > /proc/sys/kernel/suid_dumpable
echo core > /proc/sys/kernel/core_pattern

5. References:
a. for setuid program, core dumps are not generated to prevent sensitive information to be leaked.
According to Redhat, to enable it
For Red Hat Enterprise Linux 5: "suidsafe" (recommended) - protect privileged information by
having the core dump be owned by and only readable for root:
echo 2 > /proc/sys/fs/suid_dumpable

For Red Hat Enterprise Linux 5: "debug" (may cause privileged information to be leaked):
echo 1 > /proc/sys/fs/suid_dumpable

For Red Hat Enterprise Linux 4:
echo 2 > /proc/sys/kernel/suid_dumpable
For Red Hat Enterprise Linux 3:
echo 1 > /proc/sys/kernel/core_setuid_ok

to enable them persistent over reboot:
fs.suid_dumpable = 2 # RHEL 5 only
kernel.suid_dumpable = 2 # RHEL 4 only
kernel.core_setuid_ok = 1 # RHEL 3 only
kernel.core_pattern = /tmp/core

b. commands:
ulimit -c
ulimit -a
ulimit -c 2000 (limit core dump to 2000 bytes)
you might want to limit the individual user core dump size through /etc/security/limits.conf
add something like this:

jephe soft core unlimited

c. ssh login and /etc/security/limits.conf consideration
When you set some restriction for users in limits.conf and you login through ssh, then you need to make sure /etc/ssh/sshd_config to have 'usePAM yes'
For more information on ssh and ulimit, please refer to setting bash shell limits for oracle user - http://linuxtechres.blogspot.com/2010/09/setting-bash-shell-limits-for-oracle.html

d. how to read core dump file
gdb program_path corefile_path

How to recreate EM repository for Oracle 10g R2 on Windows

2011-08-26T18:52:00.003+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2003 server SE SP2, Oracle 10g standard edition 10.2.0.4.0, Windows is using domain account to authenticate.
Problem: Due to some reason, we cannot login as sys/system/sysman/dbsnmp, we can only use oracle login Windows server and use os authentication(sqlpuls / as sysdba) to check database. Also, Enterprise Manager is not working. We need to drop/recreate EM repository to get EM up so that we can monitor database through EM

Objective: change password for sys/system/sysman/dbsnmp, recreate EM repository for getting EM working, use the latest version of sqldeveloper(3.0) so that we can use menu 'view-DBA' function to manage database also.

Question: For sql developer 3.0, why cannot we use os authentication?
I have successfully tried to use sql developer 2.0 for os authentication, please refer to
Refer to http://linuxtechres.blogspot.com/2011/07/how-to-use-sqldeveloper-with-os.html, but 3.0 doesn't work this way. So we cannot use DBA view function which is very good feature in version 3.0.

Steps:

1. change sys/system/sysman/dbsnmp password for database first.
login Windows, use os authentication
sqlplus / as sysdba
sql> alter user sys identified by "yourpassword";
sql> alter user system identified by "yourpassword";
sql> alter user sysman identified by "yourpassword";
sql> alter user dbsnmp identified by "yourpassword";

make user all above users are open, not expiried and locked.
Your might want to refer to another article at http://linuxtechres.blogspot.com/2010/09/jephe-wu-httplinuxtechres.html
and How to Change DBSNMP Password in Database 10g and 11g Monitored by DB Control [ID 259387.1] in Oracle support.

2. drop/recreate the existing EM repository.

Make sure c:\windows\system32\drivers\etc\hosts contains a line like this even though you can ping FQDN with correct ip without this following line.
1.2.3.4 FQDN hostname

otherwise, you will encouter error message below when cretae EM repository:
May 17, 2006 7:34:39 PM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfig
Exception: Error instantiating EM configuration files at oracle.sysman.emcp.EMAgentConfig.updateAgentConfigFiles(EMAgentConfig.java:2560)
at oracle.sysman.emcp.EMAgentConfig.performConfiguration(EMAgentConfig.java:1166)
at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:207)
at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:185)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:146)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:479)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1123)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:463)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:412)

Refer to EMCA Fails With Error "SEVERE: Error instantiating EM configuration files" [ID 370207.1] in Oracle support.

If you encounter error message like this:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one upper, one lower and
one special character

that's due to internal BUG 4195090. check REPMANAGER AND EMCA FAILS WITH PASSWORD VERIFICATIONFUNCTION [ID 779098.1] from Oracle support
=> change

ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION null;

then put back later after done emca.

c:> set ORACLE_SID=xxxx (important, otherwise, you cannot secure dbconsole after creating repository)
c:> emca -deconfig dbcontrol db -repos drop
type in sid, listener port, passwords etc

c:> emca -config dbcontrol db -repos create
type in sid, listener port, passwords etc.
You will get successfully created EM repository message, and secured dbcontrol successfully, but failed to start up dbcontrol, wait for 15 mins.
it will fail and give the following errors:

Refer to the log file at D:\oracle\10.2.0\db_1\cfgtoollogs\emca\xxxx\emca_2011-08-26_10-32-25-AM.log for more details.
26/08/2011 10:49:38 oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Error starting Database Control
    at oracle.sysman.emcp.EMDBPostConfig.performConfiguration(EMDBPostConfig.java:646)
    at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:224)
    at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:193)
    at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:184)
    at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:486)
    at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1142)
    at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:470)
    at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:419)

According to Enterprise Manager Database Control Configuration - Recovering From Errors Due to CA Expiry on Oracle Database 10.2.0.4 or 10.2.0.5 [Video] [ID 1222603.1]
and ATTENTION - Enterprise Manager Database Control 10.2.0.4 Or 10.2.0.5 - Patch Required from 31-Dec-2010 onwards [ID 1217493.1]

According to above docs, you can either apply Patch 8350262, or disable https for EM as follows:

3. disable https for EM (Starting from 10.2.0.4 onwards DBConsole uses HTTPS in its URL by Default [ID 747770.1] in Oracle support)

c:> emctl unsecure dbconsole
c:> emctl start dbconsole
access EM at http://FDQN:5501/em/console/

4. use Sqldeveloper 3.0 and EM to monitor/manage database.

How to setup MS SQL server logshipping and failover

2011-08-21T15:51:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: setup a logshipping between primary and secondary. and failover it.
Environment: sql server 2005/2008.

Steps:

Setup requirments:
1. Installing both primary and standby SQL servers, configure primary database first
2. setup shared folder on primary or another network share(third server)

You must have a shared folder to copy the transaction log backups to.
The SQL Server Agent service account of the primary server must have read/write access either to the shared folder or to the local NTFS folder. The SQL Server Agent account of the standby server must have read access to the shared folder for applying transaction log
3. primary server must be in full or bulk-loged mode for generating transaction logs

You can configure log shipping with SQL Server Agent services stopped, but the process does not run until the agent is started.

Note: refer to step-by-step configuring logshipping at
http://www.mssqltips.com/tipprint.asp?tip=2301

How to failover - http://msdn.microsoft.com/en-us/library/ms191233.aspx
1. copy any uncopied backup files from backup share to file copy folder on each secondary server
if possible, generate tail log backup from primary provided primary server is still alive.

a. if db is online and you plan to perform restore operation, before starting restore, backup tail of the log with command below:

BACKUP LOG <dbname> TO <backup_device> WITH NORECOVERY;
go

note: This leaves the database in the restoring state, and eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database(i.e original secondary server)

b. if db is offline and does not start.
As no transaction can occur this time, using 'with norecovery' is optional, if db is damaged, use 'with continue_after_error'

as follows:

backup log <dbname> to <backup_device> with continue_after_error

Use CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database.

c. if db is damaged and inaccessible, however, transaction log is undamaged and accessbible:

backup log <dbname> to <backup_device> with no_truncate;
go

note:
a.no_truncate=copy_only+continue_after_error
b. How to: Back Up the Tail of the Transaction Log (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/dd297499
How to: Back Up the Transaction Log When the Database Is Damaged (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms189606
c. For details about tail-log backup, see http://msdn.microsoft.com/en-us/library/ms179314

2. apply any unapplied transaction log backups in sequence to each secondary database
How to: Recover a Database from a Backup Without Restoring Data (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms176039.aspx
How to: Restore a Transaction Log Backup (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/ms177446.aspx

For restoring the last log backup, you can do the following:
restore log <dbname> from <backup_device> with recovery; bring database online from Read-only mode.
go

or
restore log <dbname> from <backup_device> with norecovery;
restore database <dbname> with recovery; bring database online from Read-only mode.
go

note:
a.always explicitly specify either with norecovery or with recovery.
b. <backup_device> is the name of the device that contains the log backup being restored.

How to recover a database from a backup without restoring data - http://msdn.microsoft.com/en-us/library/ms176039.aspx
restore database <dbname> with recovery

3. after you have recovered secondary db, you can reconfigure it to act as a primary database for other secondary db, and you can redirect client to this server instance.

Before this, you might need to transfer login ids from primary to seconday server:
How to set up and perform a log shipping role change (Transact-SQL) - http://msdn.microsoft.com/en-us/library/aa215392(v=sql.80).aspx

References and Commands:
a. alter database <dbname> set recovery full
b. how to restore database with norecovery
restore database <dbname> from <device_name> with norecovery;
go
c. how to apply transaction log backup (T-SQL) - http://msdn.microsoft.com/en-us/library/ms187607.aspx
d. High Availability Solutions Overview - http://msdn.microsoft.com/en-us/library/ms190202%28v=SQL.105%29.aspx

How to use opatch to apply Oracle Critical Patch Update(CPU)

2011-07-31T19:58:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to apply the latest CPU for Oracle Windows
Environment: Windows 2003 server SP2, Oracle 10.2.0.4 32bit

Steps:

1. backup database (rman, expdp/impdp and code backup if possible)
2. backup oracle home and c:\program files\oracle
3. stop listener, oracle service, zabbix agent and netbackup client service
4. sqlora32.dll cannot be used by other process, use process explorer from systeminternal to check.
5. list number of invalid objects and views
6. select * from registry$history for patching history
7. opatch version requirement, if higher version is required, download the latest opatch
8. opatch lsinventory to list inventory
9. opatch apply to apply patch
10. compile all views again , compare the number of invalid views and objects.

How to use sqldeveloper with OS authentication

2011-07-31T19:55:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: use OS authentication with sql developer on Windows server itself
Environment: Windows 2003 server SP2 32bit, Oracle 10g R2 standard edition 32bit

Steps:

1. download sqldeveloper 2.1.1.64(build MAIN-64.45) for 32bit Windows with JDK included
2. configure sqldeveloper to use OCI/Thick driver
tools-preferences-database-advanced- tick 'use OCI/Thick driver'
3. download Oracle instance client 11.1.0.7.1 for 32bit Windows
extract ocijdbc11.dll from the zip file, copy into Oracle home directory (e.g. d:\oracle\10.2.0\db-1\bin\)
There should be a ocijdbc10.dll there too.
4. configure connection as follows:

connection name: server_name_sys
role: sysdba
connection type: basic
hostname: localhost
port: 1521
sid: get it from sqlplus 'select instance_name from v$instance'
tick 'OS authentication'

How to solve common DG broker issues?

2011-07-22T20:23:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to make dgmgrl to work, show configuration should display 'SUCCESS'.
Environment: RHEL4.8 64bit, Oracle 10.2.0.3 64bit (RAC+Dataguard, RAC as primary, standalone DB as standby).

Error messages:
When running dgmgrl, connect /, show configuration, Get the following errors:
1.   In the alert log: /u01/app/oracle/admin/LIVEDB/bdump/drcLIVEDB1.log

DG 2011-07-22-16:55:32        0 2 756506724 DMON: Automatic health check timed out
DG 2011-07-22-16:55:32        0 2 756506724 Operation CTL_GET_STATUS continuing with warning, status = ORA-16619
DG 2011-07-22-16:55:32        0 2 0 INSV: cancelling message publish for opcode CTL_GET_STATUS
DG 2011-07-22-16:55:32        0 2 0       request ID = 1.2.756506724

2 When run 'show configuration', got 'Warning: DMON: health check timeout'

3. in the dataguard log file on one of RAC DB server, got error:
DG 2011-07-22-15:35:07        0 2 0 DMON: found read, but unconsumed message
DG 2011-07-22-15:35:07        0 2 0 DMON: req_id = 1.2.756506586

Solution:
1. On one of the RAC DB, run command 'ALTER SYSTEM SET DG_BROKER_START=FALSE' to shutdown dg broker for RAC, then run 'ALTER SYSTEM SET DG_BROKER_START=TRUE' to start it again.

Other useful commands:
select * from v$archive_gap;
show parameter dg_broker_start; # should be true on dataguard
http://www.ritzyblogs.com/OraTalk/PostID/105/How-to-setup-DGMGRL-broker-with-example
SQL> set line 32767;
SQL> select * from v$logfile;

Startup standby db and redo apply:
sql>STARTUP MOUNT;
sql> alter database recover managed standby database cancel;
sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
or
sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT FROM SESSION;
note: cancel first like above, otherwise, you will get error such as "an incompatible media recovery is active".

How to install Mercurial on CentOS 4.9

2011-07-19T19:55:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: CentOS 4.9 64bit, Mercurial 1.9, Python 2.6.7

Steps:
1. download bzip2 1.0.6 from www.bzip.org
make;make install

2. compile Pythoon normally (./configure; make; make install)
Since Python 2.7.1, it doesn't support bz. If you use the latest version, you might encounter error 'warning: IronPython detected (no bz2 support)' when compiling mercurial

3. compile mercurial 1.9
make all
make install

How to install zabbix agent on Windows and backup zabbix database configuration

2011-07-01T08:09:00.004+08:00

Jephe Wu - http://linuxtechres.blogspot.com

steps:

1. refer to doc at http://www.zabbix.com/documentation/1.8/manual/processes/zabbix_agentd_win

2. copy 32bit or 64bit unzipped files to c:\Program Files\Zabbix Agent\ (create this directory first)

3. copy configuration file from zabbix source to above directory as zabbix_agentd.conf

An example configuration file is available in Zabbix source archive as misc/conf/zabbix_agentd.win.conf.

EnableRemoteCommands=0
Server=172.16.1.1,172.16.1.2
Hostname=JEPHE
ListenPort=10050
ServerPort=10051
StartAgents=5

4. install it
go to cmd,
cd "Program Files\Zabbix Agent"
zabbix_agentd.exe -c "c:\Program Files\Zabbix Agent\zabbix_agentd.conf" -i

5. go to services.msc, start zabbix agent.

6. test
from agent host, telnet zabbixserver 10051
from server host, telnet zabbixagent 10050

7. add agent host into zabbix monitored server list

========
backup zabbix configuration shell script:

root@db01:~/bin/ # more zabbix_config_backup.sh
#!/bin/sh
DAY=`date +%w`
HOSTNAME=`hostname`
TABLES="acknowledges actions applications autoreg_host conditions config dchecks dhosts drules dservices escalations expressions functions globalmacro graph_theme graphs graphs_items groups help_items hostmacro hosts hosts_groups hosts_profiles hosts_prof
les_ext hosts_templates housekeeper httpstep httpstepitem httptest httptestitem ids images items items_applications maintenances maintenances_groups maintenances_hosts maintenances_windows mappings media media_type nodes opconditions operations opmediatypes profiles proxy_autoreg_host regexps rights screens screens_items scripts services services_links sessions slides slideshows sysmaps sysmaps_elements sysmaps_link_triggers sysmaps_links timeperiods trigger_depends triggers users users_backup users_groups usrgrp valuemaps"
COMMAND="mysqldump -uzabbix -pzabbix zabbix --add-drop-table --add-locks --extended-insert --single-transaction --quick"
DIR="/mysql/config_backup"
DATATABLES="alerts auditlog events history history_log history_str history_str_sync history_sync history_text history_uint history_uint_sync node_cksum proxy_dhistory proxy_history service_alarms services_times trends trends_uint"

# program starts here
$COMMAND --tables $TABLES | gzip > $DIR/zabbix_config_db.$DAY.gz

(echo "From: `hostname`<root@`hostname`>";echo "Subject: Finished daily Zabbix config backup"; echo "To: jwu@domain.com"; echo "";echo "According to Zabbix
.8 network monitoring book page 377, the following tables are data tables, all others are configuration tables"; echo "";echo "$DATATABLES";echo "";echo "running command $COMMAND --tables $TABLES | gzip > $DIR/zabbix_config_db.$DAY.gz"; echo ""; echo "`ls
-lat /srv/mysql/config_backup`") | /usr/sbin/sendmail jwu@domain.com

How to login MS SQL server 2008 database engine

2011-06-28T09:51:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2008 server R2 64bit with MS SQL Server 2008. Initially, user1 who belongs to Windows local Administrators group installed this SQL server,
after he resigned, another user who also belongs to local administrators users group cannot login database engine.

Objective: to make user2 to login to database engine.

Errors: When user2 tries to login sql server 2008 with Windows authentication mode, it gives the following error messages in event log:
Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Steps:
1. login sql server management studio as the initial user1 who set up the sql server.
use [local] to login, you may want to try computer name in server name part

2. under 'security - login' tab, create a new user login name as \\computername\jwu for Windows authentication, grant public and sysadmin server role , and enable 'grant permission to login database engine' under status tab.

3. logout user1, login as local administrator user 'jwu', use computername as servername to login sql server management studio.

4. you should be able to login now.

References:
1. Choose an authenticatin mode - http://msdn.microsoft.com/en-us/library/ms144284.aspx
2. How to change server authentication mode - http://msdn.microsoft.com/en-us/library/ms188670.aspx

How to make SQL server 2005 logshipping secondary database to re-sync with primary database again

2011-06-25T18:06:00.004+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to make secondary db server to sync with primary again, logshipping is not working due to some required log files were deleted by some job.
Environment: Windows 2003 32bit, MS SQL server 2005 (management studio)

Problem: logshipping is not working according to Windows event log and sql job history, due to some required files were deleted, so the restoring is not working.

Steps:
1. disable restoration job in job agent from SQL management studio
SQL Server Agent -> Jobs - LSRestore_DBxxxx\DBRSQLP4_xxxxx

also, disable any other sql server agent job which might affect restoration process.
2.   restore the full database backup from primary by right clicking database DR_DB1 on secondary server.

right click, task, restore database, General, source for restore, from device, add a file which is copied from production daily full backup, tick it to choose it.
go to options, choose 'overwrite the existing database', do not care about 'Restore the database file as' part even the destination name is not same as what the DR server is having now for that database.

Recovery state, choose the second options which is 'Leave the database non-operational, and do not roll back uncommitted transactions, Additional transaction logs can be restored (RESTORE WITH NORECOVERY), then click 'OK'.

After finishing it, the restore job will start it automatically and database will automatically try to find the transaction log file to recovery .

5. right click on the restoratin job to check history, It will check all transaction log files in the specified directory, skip those which already contained in the restored database. click refresh and get more information.
6. check reports for transaction log.

References:
1. Index rebuild generate huge log file makes logshipping and mirroring out of sync - http://connect.microsoft.com/SQLServer/feedback/details/352338/index-rebuild-generate-huge-log-file-makes-logshipping-and-mirroring-out-of-sync

2. How to: Remove Log Shipping (SQL Server Management Studio)
This topic explains how to remove log shipping using Microsoft SQL Server Management Studio.
To remove log shipping

    Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.

    Under Select a page, click Transaction Log Shipping.

    Clear the Enable this as a primary database in a log shipping configuration check box.

    Click OK to remove log shipping from this primary database.

Vmware FAQ

2011-06-19T13:53:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Performance issue when copying files between Windows 7 64bit host and Linux guest

According to Vmware KB - Performance issues with bridged networking on Windows Server 2003 and Windows 2008 host operating systems - http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1006619

You must disable the TCP Offload Engine features in the driver on the host.

To disable the TCP Offload Engine features:

Click Start > Run.
Type regedit and press Enter.
Browse to the following location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Create the key DisableTaskOffload (type: DWORD).
Set the value of the key to 1 .
Close the Registry Editor and restart the computer.

Oracle RAC Documentation

2011-06-18T18:10:00.008+08:00

Jephe Wu - http://linuxtechres.blogspot.com

How to check if it's running RAC

BEGIN
  IF dbms_utility.is_cluster_database THEN
      dbms_output.put_line('Running in SHARED/RAC mode.');
  ELSE
      dbms_output.put_line('Running in EXCLUSIVE mode.');
  END IF;
END;
/

or

SQL> show parameter CLUSTER_DATABASE

 2. How to stop/start dbconsole?

Linux:

$ export ORACLE_HOME=xxx
$ export ORACLE_SID=xxx
$ ORACLE_HOME/bin/emctl stop dbconsole

Windows:

set ORACLE_HOME=xxx

set ORACLE_SID=xxx
ORACLE_HOME\bin\emctl stop dbconsole

Linux:

$ export ORACLE_HOME=xxx
$ export ORACLE_SID=xxx
$ ORACLE_HOME/bin/emctl start dbconsole

Windows:

set ORACLE_HOME=xxx

set ORACLE_SID=xxx
ORACLE_HOME\bin\emctl start dbconsole

Reference: Enterprise Manager Database Console FAQ [ID 863631.1]

3.  How to stop/start RAC

 Bring up the inst1 of database db1

$ srvctl start instance -d db1 -i inst1

Stop the db1 database: all its instances and all its services, on all nodes.
$ srvctl stop database -d db1

4. database uptime

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;

5. alert log location

show parameter BACKGROUND_DUMP_DEST;

6. stop/start RAC

Starting the Oracle 10g RAC Cluster 10g Environment:

- Run as oracle:  su - oracle

$ export ORACLE_SID=orcl1

$ srvctl start nodeapps -n linux1

$ srvctl start asm -n linux1

$ srvctl start instance -d orcl -i orcl1

$ emctl start dbconsole

Start/Stop All Instances with SRVCTL



Stopping the Oracle 10g RAC Cluster 10g Environment: 

- Run as oracle:  su - oracle

$ export ORACLE_SID=orcl1

$ emctl stop dbconsole

$ srvctl stop instance -d orcl -i orcl1

srvctl status instance -d dbname -i instancename

$ srvctl stop asm -n linux1 [-o immediate]

$ srvctl stop nodeapps -n linux1

Starting the Oracle RAC 10g Environment

To stop or start both database instances at once: 

$ srvctl start database -d orcl [-o open | -o mount | -o nomount]

$ srvctl stop database -d orcl [-o normal | -o transactional | -o immediate | -o abort]

srvctl status database -d dbname

srvctl config database -d dbname (shows instances name, node and oracle home) ==========

Services:

srvctl status service -d dbname

 srvctl config service -d dbname

 srvctl start service -d dbname -s servicename

srvctl stop service -d dbname -s servicename

７.　lsnrctl PLSExtProc, XDB and XPT

PLSExtProc: This is used to Call OS Commands from PL/SQL using External Procedures. Default in the Listener file.

the XDB service is used for the XML DB database option

the XPT service is used for Dataguard. 

All three of these can be disabled if you are not using their associated features.

Oracle 10g and 11g Installation Documentation for Linux and Windows

2011-06-18T09:02:00.008+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Part I: Documentation from Oracle website itself:

You can access https://apex.oracle.com/pls/apex/f?p=44785:1:0 to search all the tags

Installing Oracle Database 10g on Windows - QBE - including creating oracle user - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/install/wininst/wininst_otn.htm
Installing Oracle Database 11g on Windows - QBE -without creating oracle user - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/dbinst/windbinst2.htm
Installing and Configuring Oracle Enterprise Linux 5 General Availability - including creating oracle user before installing database 11g - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/oel5gainst/oel5gainst.htm
Installating Oracle Database 11g on Linux - QBE -without creating oracle user - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/dbinst/dbinst.htm
Installing Oracle Software and Building the Database - QBE - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/install/dbinstr2/dbinst.htm

Part II: Documentation from third-party website:

Installing Oracle on Enterprise Linux & RHEL - http://wiki.oracle.com/page/Installing+Oracle+on+Enterprise+Linux+%26+RHEL
http://www.puschitz.com/InstallingOracle10g.shtm

10g 64bit installation requirement:

Install 10g on RHEL4 64bit - installation requirement , similar with 11g installation steps:

- http://download.oracle.com/docs/cd/B19306_01/install.102/b15667/pre_install.htm

yum install xorg-x11-deprecated-libs

otherwise ,you will get error message jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or dir afer launching runInstlaler.

2. yum install libaio*

3. run this:
yum install xorg-x11-deprecated-libs
yum install sysstat*
yum install libaio*
yum install unixODBC*
yum install binutils*
yum install compat-db*
yum install gcc-c++*
yum install libstdc++*
yum install make*
yum install sysstat*
yum install glibc*
yum install control-center*

Install Zabbix on CentOS 5.6

2011-06-15T19:13:00.004+08:00

Jephe Wu - http://linuxtechres.blogspot.com

PHP and Zabbix

CentOS 5.6, after installing zabbix server, try to access http://localhost/zabbix/queue.php, it displays empty page, httpd error log shows
[Wed Jun 15 11:08:14 2011] [error] [client 192.168.1.3] PHP Fatal error: Call to undefined function bcscale() in /var/www/html/zabbix/include/defines.inc.php on line 794

Solution: yum install bcmath

Check of pre-requisites

When accessing http://localhost/zabbix/queue.php, we have to pass all the checking as follows:
vi /etc/php.ini to change:
timezone = Australia/Sydney
post_max_size = 32M
upload_max_filesize = 2M
max_execution_time = 600
max_input_time = 600

yum install php-mbstring
yum install php-gd

PHP and PHP53

When you use yum to install php* , you can exclude php53* as follows:

vi /etc/yum.conf to add
exclude=php53*

or yum install php* --exclude=php53*

References

http://www.muck.net/?p=16 - Installing Zabbix 1.4 on CentOS 5

How to make cygwin publickey authentication to work again after upgrading from Windows 2000 to Windows 2003

2011-05-26T14:50:00.005+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2000 server sp4, cygwin sshd, password authentication and public key login were working fine. After upgrading to Windows 2003 sp2 server, cygwin sshd password login still works, but public key login doesn't work anymore

Objective: to make ssh public key login work for cygwin after upgrading OS.

Steps:
1. create both sshd and sshd_server user accounts, sshd is just normal user who belongs to users group, sshd_server needs to be in Administrators groups, otherwise when you ssh into this cygwin server from Linux using public key authentication, you will encounter the errors below:

ssh from CentOS 4 to cygwin server under win2k03 server sp2:

debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
Last login: Thu May 26 14:06:59 2011 from 165.114.120.117
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
Connection to web1 closed.
debug1: Transferred: stdin 0, stdout 0, stderr 34 bytes in 5.1 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 6.6
debug1: Exit status 255

however, the password login without using public key authentication will work.

run services.msc to edit cygwin service to logon as user sshd_server and type in password.

Right click on my computer, properties, configure system variables CYGWIN as "ntsec tty", use 'set' command under command prompt to confirm it's been set.

2. double click cygwin, run 'ssh-host-config' to generate configuration files, answer yes to overwrite and yes for privileges seperation.
3. generate /etc/passwd and /etc/group files after user creations for sshd and sshd_server
under cygwin prompt window, run:
mkpasswd -l > /etc/passwd
mkgroup -l > /etc/group

4. make sure the /var/empty directory has permission like this and it should be empty
chown sshd_server /var/empty
chmod 755 /var/empty

note: you might be able to use cygwin to give permission like this:

$ chown sshd_server /var/log/sshd.log
  $ chown -R sshd_server /var/empty
  $ chown sshd_server /etc/ssh*

5. make sure /etc directory and /var/log directory and all files under it should be readable by running user sshd_server

6. modify group policy for sshd_server to be able to switch user to sshd for privileges seperation
run 'gpedit.msc', go to local computer policy - computer configuration - windows settings - security settings - local policies - user rights assignment
look for 'create a token object', to add sshd_server user inside.

run 'gpupdate' to refresh group policy immediately, then restart cygwin sshd service.

Note: you don't have to add sshd_server into 'replace a process token level'.

Error messages and issues:

Isses we encountered:
a. couldnot start up cygwin sshd service
solution: check /var/log/sshd.log for error message and event log for ssh error, setting correct permission for /etc for sshd_server user to read, and /var/log/sshd.log for sshd_server to read/write

b. error messages appeared under evern log
1. The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 3996 : fatal: setreuid 1013: Permission denied.
2.
The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 4848 : starting service `' failed: redirect_fd: open (1, /var/log/sshd.log): 13, Permission denied.
3. The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 5376 : starting service `sshd' failed: execv: 1, Operation not permitted.
4. The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 4588 : starting service `sshd' failed: execv: 128, Transport endpoint is not connected.
5. The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 4668 : error: Could not load host key: /etc/ssh_host_key.

c. error message when you use public key authentication.
ssh from CentOS 4 to cygwin server under win2k03 server sp2:
# ssh -v SYSMGR@web1
debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
Last login: Thu May 26 14:06:59 2011 from 1.2.3.4
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
Connection to web1 closed.
debug1: Transferred: stdin 0, stdout 0, stderr 34 bytes in 5.1 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 6.6
debug1: Exit status 255

References:

How to setup the secure shell daemon on a Windows 2003 server - http://ist.uwaterloo.ca/~kscully/CygwinSSHD_W2K3.html

How to add additional columns for a table in db2 where default 4k tablespace is not enough

2011-05-23T14:33:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: RHEL4 32bit with "DB2 v8.1.2.88", "s050422", "MI00117", and FixPak "9". We need to add an additional column for a table, but we encountered error message below:

The row length of the table exceeded a limit of "4005" bytes. (Table space "xxxxx".)

Objective: to create a 8k tablespace and migrate this table over to new tablespace

Steps:

1. backup the table jephe by using the following commands:(assuming database and schema name are wu, talbe name is jephe)

# more backup.sql
export to "jephe.txt" of del messages jephe.msg select * from wu.jephe;
CONNECT RESET;

db2 connect to wu user wu
db2 -tvf backup.sql -l backup.sql.log -s

2. use db2look to extract the whole database ddl statements and grep the necessary table creation statements
db2look -d wu -e -z wu -o db2look.sql
vi db2look.sql to search string JEPHE to copy out the table create and alter statements for 'JEPHE' as well as alter statements for other tables which has foreign keys on table 'JEPHE'

e.g.
# more createtable.sql
CREATE TABLE "WU   "."JEPHE" (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                balabala
                balabala
                ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE" ;

ALTER TABLE "WU    "."JEPHE"
        ADD PRIMARY KEY
                ("LIFE_INSURANCE_OID");

ALTER TABLE "WU    "."JEPHE"
        ADD CONSTRAINT "SQL050615160106790" FOREIGN KEY
                ("BENEFIT_FILE_OID")
        REFERENCES "WU    "."BINARY_FILE_REPOSITORY"
                ("BINARY_FILE_OID")
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
        ENFORCED
        ENABLE QUERY OPTIMIZATION;

# following is for another table 'TABLE1' which has foreign key for WU.JEPHE
ALTER TABLE "WU    "."TABLE1"
        ADD CONSTRAINT "SQL050615160109840" FOREIGN KEY
                ("JEPHE_OID")
        REFERENCES "WU   "."JEPHE"
                ("JEPHE_OID")
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
        ENFORCED
        ENABLE QUERY OPTIMIZATION;

Note: once you drop table 'WU.JEPHE', the foreign key for WU.TABLE1 will also be gone. After recreating table WU.JEPHE in 8k tablespace, you have to create this foreign key again.

You also need to extract view creation statements which depends on the table 'WU.JEPHE'. Because you need to create view again after dropping and creating again WU.JEPHE in 8k tablespace.

# more view.sql
create view emp_benefits_life_ins_v as select balabala.

3. create a 8k buiffer pool tablespace and a 8k system temporary tablespace for 'order by' operation on new table wu.jephe, specify container path.
If you don't create a 8k temporary tablespace, you will encouter the following error message when doing 'order by' operation

db2 "select * from wu.jephe order by status"
SQL1585N A system temporary table space with sufficient page size does not
exist. SQLSTATE=54048

creation statement is as follows for system temporary tablespace:

CONNECT TO WU;
CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 8 K MANAGED BY SYSTEM USING ('/db2/db2inst1/db/WU/tb_temp2' ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL IBM8KBP ;
COMMENT ON TABLESPACE TEMPSPACE2 IS '8k temporary tablespace';
CONNECT RESET;

4 drop table
login as db2inst1
db2 connect to wu
db2 "drop table wu.jephe"

5 recreate table in 8k tablespace
db2 connect to wu
db2 set schema = jephe
modify createtable.sql to change
CREATE TABLE "WU   "."JEPHE" (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                balabala
                balabala
                ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE" ;

to

CREATE TABLE "WU   "."JEPHE" (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                balabala
                balabala
                ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE2";
db2 -tvf createtable.sql -l createtable.sql.log -s

6. restore data into table wu.jephe again from backup

#more restore.sql
import from "jephe.txt" of del messages "jephe.impmsg" insert into wu.jephe;
connect reset;

db2 connect to wu
db2 set schema = jephe
db2 -tvf restore.sql -l restore.sql.log -s

import from "jephe.txt" of del messages "jephe.impmsg" insert into wu.jephe

Number of rows read         = 77
Number of rows skipped      = 0
Number of rows inserted     = 77
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 77

7. run command to add additional comumns for new table wu.jephe
# more command.sql
alter table wu.jephe add column contact_detail varchar(250);

db2 connect to wu
db2 set schema = jephe
db2 -tvf command.sql -l command.sql.log -s

8. grant permission for table WU.JEPHE and related views from db2 control center GUI

How to compile mod_proxy module additionally while the Apache httpd is running

2011-05-12T21:19:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: CentOS 4.4 32bit web server, Apache web server is running within chroot environment, it's also Cognos web gateway server.
Objective: we need to enable mod_proxy module to use reverse proxy method instead of using web gateway method to make httpd to take to Cognos server on application network segmen. (As somehow the cognos web gateway is not able to take to Cognos server anymore unless using reverse proxy method)

Tried to use apxs to compile mod_proxy.c and mod_proxy_http.c, it's not working.

Steps:
1. find out the apache httpd version first
cd /usr/local/chroot/usr/local/apache2
bin/httpd -V

2. find out the original compiling options
cd /usr/local/chroot/usr/local/apache2/build
cat config.nice

[root@web1 build]# more config.nice
#! /bin/sh
#
# Created by configure

"./configure" \
"--prefix=/usr/local/apache2" \
"--enable-mods-shared=all" \
"--enable-ssl=shared" \
"--enable-rewrite=shared" \
"--enable-headers=shared" \
"--enable-expires=shared" \
"--enable-cern-meta=shared" \
"--enable-unique-id=shared" \
"--enable-mime-magic=shared" \
"--with-ssl=/usr/local/ssl" \
"$@"

3. find where the original Apache httpd installation files are, then compile mod_proxy.

"./configure" "--prefix=/usr/local/apache2_test" "--enable-mods-shared=all" "--enable-ssl=shared" "--enable-rewrite=shared" "--enable-proxy=shared" "--enable-headers=shared" "--enable-expires=shared" "--enable-cern-meta=shared" "--enable-unique-id=shared" "--enable-mime-magic=shared" "--with-ssl=/usr/local/ssl" "$@"

use different installation prefix directory, then run 'make;make install' to install everything into /usr/local/apache2_test.

4. copy necessary mod_proxy modules into /usr/local/chroot/usr/local/apache2/modules/

cd /usr/local/chroot/usr/local/apache2/modules/
cp /usr/local/apache2_test/modules/mod_proxy.so .
cp /usr/local/apache2_test/modules/mod_proxy_http.so .

5. modify /usr/local/chroot/usr/local/apache2/conf/httpd.conf to enable mod_proxy

LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_http_module modules/mod_proxy_http.so
#LoadModule proxy_connect_module modules/mod_proxy_connect.so
#LoadModule proxy_ftp_module modules/mod_proxy_ftp.so

6. configure virutalhost to enable mod_proxy directives

    RewriteEngine On
    RewriteCond %{REQUEST_METHOD} ^(TRACE|TRACK)
    RewriteRule .* - [F]

rewriterule ^/reporting/(.*) http://10.0.0.1/cognos8/$1 [P,L]
rewriterule ^/cognos8/(.*) http://10.0.0.1/cognos8/$1 [P,L]

7. use USR1 to kill Apache parent process
ps -efH to find out the parent process id
then kill -USR1 thatid
ps -efH to check again.

How to backup Windows NTFS partitions before upgrading

2011-05-10T10:11:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: Upgrade Windows 2000 sp4 standalone server to Windows 2003 server standard edition, in case of failure, we are still able to restore back to Windows 2000 server
Environment: Windows 2000 sp4 server, Dell PowerEdge 2450 server, 10G basic NTFS partition for C drive with 5G free space
Tools needed for backup NTFS partition: RIP(Recovery Is Possible) CD 12.3 non-X edition - http://www.tux.org/pub/people/kent-robotti/looplinux/rip/

Steps:
1. boot up with RIP non-X 12.3 CD, choose the second option which is without keyborad mapping,login as root without password.
If you need to do it remotely,start up ssh by running /usr/sbin/sshd, then 'passwd root' to give root password

2. connect a external 300G hard disk with USB cable, we are going to backup C drive to external hard disk

3. mount external hard disk ntfs partition as read/write under RIP Linux
mount.ntfs-3g /dev/sdc1 /mnt/usb
ntfsclone -s - /dev/sda2 | gzip -c | cat > /mnt/usb/jephe.sda2.gz

4. backup mbr of /dev/sda
dd if=/dev/sda count=1 bs=512 of=/mnt/usb/jephe.dd.mbr

5. in case of failure for upgrade, you can restore mbr and C drive back
dd if=/mnt/usb/jephe.dd.mbr of=/dev/sda
gzip -dc /mnt/usb/jephe.sda2.gz | ntfsclone -r - -O /dev/sda2

6. If you need to backup C drive and MBR to network server through ssh connection, please refer to article
Cloning Windows server - using RIP and ntfsclone
at http://linuxtechres.blogspot.com/2007/07/cloning-windows-server-using-rip-and.html

7. While Windows 2000 server is running, as an administrator in the I386 directory on the Windows 2003 CD run Winnt32.exe and follow the prompts.

How to restore db2 database to specified date and restore the deleted records from certain table

2011-04-20T15:53:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Scenario: single DB2 instance and database db1, database transaction log is enabled, partial data of some tables in schema 'jephe' were deleted accidently. As this is a production database used by many different clients/schemas, we cannot restore back as the client only realized this accident one week later after deletion.

Solution: restore monthly database full backup plus the transaction logs just before the time which deletion happened on another server(DR?). Then use db2 export and db2 import to import back those deleted data.

Environment: RHEL5, IBM DB2 UDB 9.1 fixpack 3.

Steps:

1. restore the monthly full online database backup to /db2/db2inst1 directory
db2 "restore database db1 from /data to /db2/db2inst1 into db1dr with 2 buffers buffer 1024 parallelism 1 without prompting"

note: /data is the directory where the database full backup image exists.

2. get all the transaction log files after that full backup and before the deletion time

3. copy all the necessary log files to the /data/db2log/DB1/logs, then run the following command:
db2 "rollforward database db1dr to 2011-04-19-11.30.00.000000 using local time and complete overflow log path (\"/data/db2log/DB1/logs\")"

PLease refer to my last time blog at http://linuxtechres.blogspot.com/2010/07/how-to-onlineoffline-backup-and-restore.html

4. backup those tables on production database first in case they are destroyed during import process

# more backup.sh
cd /db2/db2inst1/scripts/20110420_restore/backup
db2 connect to db1
db2 set schema = jephe
db2 "export to \"./table1\" of del messages \"./table1.msg\" select * from table1"
db2 "export to \"./table2\" of del messages \"./table2.msg\" select * from table2"
db2 "export to \"./table3\" of del messages \"./table3.msg\" select * from table3"
db2 terminate

5. extract those deleted data first from restored DR database server

# more extract.sh
cd /home/db2inst1
db2 connect to db1dr
db2 set schema = jephe
db2 "export to \"./table1\" of del messages \"./table1.msg\" select * FROM table1 WHERE balabala-same statement used during deletion"
db2 "export to \"./table2\" of del messages \"./table2.msg\" select * FROM table2 WHERE balabala-same statement used during deletion"
db2 "export to \"./table3\" of del messages \"./table3.msg\" select * FROM table3 where balabala-same statement used during deletion"

6. import back to the production database (the sequence for importing might be different from the original deletion sequence as it might depends on foreign key or something)

# more import.sh
cd /db2/db2inst1/scripts/20110420_restore/
db2 connect to db1
db2 set schema = jephe
db2 "import from \"./table3\" of del messages \"./table1.imp\" insert into table1"
db2 "import from \"./table2\" of del messages \"./table2.imp\" insert into table2"
db2 "import from \"./table1\" of del messages \"./table3.imp\" insert into table3"
db2 terminate

How to make sqlplus more friendly

2011-04-08T18:50:00.004+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to solve some issues with sqlplus such as backspace and command history
Environment: CentOS 5.5 64bit, Oracle 11g 64bit, putty or Xshell ssh client

Steps:
1. History command
Original sqlpuls doesn't support history commands, you can use rlwrap.

a. download rlwrap from http://utopia.knoware.nl/~hlub/rlwrap/
b. tar xvpfz rlwrap-0.37.tar.gz
c. ./configure; make; make install

2. put the following into ~oracle/.bash_profile
stty erase ^H (ctrl + V, then H)
# for some ssh client, you might need to use stty erase '^?' (that's shift+6+?)
alias sqlplus='rlwrap sqlplus'

3. run sqlplus normally like sqlpluls / as sysdba, now you can easily use history commands and move cursors

How to use bash script to send out Oracle AWR report automatically

2011-04-06T11:58:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com
Objective: write bash shell script as cronjob to send out Oracle AWR report every 2 hours automatically
Environment: Oracle 11g 11.1.0.6 64bit default installation, CentOS 5.5 64bit
Concept: use base number and current unix seconds to get the latest snap id, use the latest snap id to get the begin snap id(current snap id -2). Use template awrrpt.sql file to generate the predefined filename, num_days, begin snap id and end snap id.

Steps:
1. preparing the template files
cd $ORACLE_HOME/rdbms/admin
# do not touch the original file awrrpt.sql and awrrpti.sql, use awr.sql and awri.sql instead.
cp awrrpt.sql awrtemp.sql
cp awrrpti.sql awri.sql

vi awri.sql --> to append 'exit;' at the end of the file so that sqlplus will exit automatically during script run
vi awrtemp.sql to modify it like this:
---------------
select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
from v$database d,
       v$instance i;

define num_days = '1';
define report_type = 'html';
define report_name = 'TEMPLATE0';
define begin_snap = 'TEMPLATE1';
define end_snap = 'TEMPLATE2';

@@awri
--
-- End of file
-------------------------

2. get the latest snap id unix timestamp(seconds)
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrrpt.sql
----------
Enter value for num_days: 1

Listing the last day's Completed Snapshots

                            Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL          12953 06 Apr 2011 00:00       1
                  12954 06 Apr 2011 01:00       1
                  12955 06 Apr 2011 02:00       1
                  12956 06 Apr 2011 03:00       1
                  12957 06 Apr 2011 04:00       1
                  12958 06 Apr 2011 05:00       1
                  12959 06 Apr 2011 06:00       1
                  12960 06 Apr 2011 07:00       1
                  12961 06 Apr 2011 08:00       1
                  12962 06 Apr 2011 09:00       1
                  12963 06 Apr 2011 10:00       1
                  12964 06 Apr 2011 11:00       1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
----------------

we use the latest snap id 12964 as base number, let's convert it to unix timestamp.
date --date="Wed Apr 6 11:00:00 SGT 2011" +%s
1302058800

3. preparing shell script
[oracle@hpaydb admin]$ more ~/bin/awr.sh
#!/bin/sh

export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin

# variables
SEC=`date +%s`
INIT=12964
INCR=`echo "($SEC-1302058800)/3600" | bc`
#1302058800 is Wed Apr 6 11:00:00 SGT 2011
SNAPEND=$[$INIT+$INCR]
SNAPBEGIN=$[${SNAPEND}-2]

# program starts here , do not change anything below
sed -e "s#TEMPLATE1#$SNAPBEGIN#g" -e "s#TEMPLATE2#$SNAPEND#g" -e "s#TEMPLATE0#/tmp/prod_awr_${SNAPBEGIN}_${SNAPEND}.html#g" $ORACLE_HOME/rdbms/admin/awrtemp.sql > $ORACLE_HOME/rdbms/admin/awr.sql

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awr.sql

# send out email
rm -f /tmp/prod_alert_awr_$SNAPEND.zip
zip -r /tmp/prod_alert_awr_$SNAPEND.zip /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log /tmp/prod_awr_${SNAPBEGIN}_${SNAPEND}.html
mutt -a /tmp/prod_alert_awr_$SNAPEND.zip -s "Prod alert log and AWR report between snap id $SNAPBEGIN and $SNAPEND" jephewu_email_address < /dev/null

4. put in cronjob
login as oracle, crontab -e
30 */2 * * * /home/oracle/bin/awr.sh
# every 2 hours to send out report

4. References
a. convert unix second to normal date
date -d @1187769064
b. put the following into /home/oracle/bin/awr to simplify the normal awr report generation
[oracle@hpaydb admin]$ more ~/bin/awr
#!/bin/sh

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrrpt.sql

chmod +x ~/bin/awr
Just run awr to generate normal awr report and awr.sh to generate awr every 2 hours automatically.

How to upgrade Oracle 11g 64bit from 11.1.0.6 to 11.1.0.7

2011-04-04T23:25:00.001+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: easily upgrade Oracle 11g to 11.1.0.7
Environment: CentOS 5.5 64bit, Oracle 11g 11.1.0.6
Steps:

1. ssh into server as user 'oracle', then type in 'vncserver'
2. From windows pc, use vncviewer to connect to server.
3. at xterm windows, run Oracle installer, use the same path as what 11.1.0.6 is using. You cannot choose the different path.
4. follow the screen instruction to finish upgrading
5. use dbua command to finished database upgrading
6. lsnrctl start, sqlplus / as sysdba ; alter system register
7. database should has already been started by dbua itself.

How to enable ftp client to access ftp server behind FreeBSD firewall

2011-04-04T18:40:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: allow the users on the LAN who are using Squid proxy (10.0.0.1) to be able to access ftp sites.
Environment: OpenBSD 4.5(1.2.3.4/10.0.0.2), Squid client(10.0.0.1) behind this OpenBSD firewall

Internet[1.2.3.4]OpenBSD4.5[10.0.0.2]<->[10.0.0.1]Squid/Web/DNS server
<->[10.0.0.10]sysadmin pc
Steps:
1. modify /etc/rc.conf to enable ftpproxy
vi /etc/rc.conf to change ftpproxy_flag from NO to YES

2. enable pf.conf for ftp outgoing and incoming web/dns requests

ext_if="fxp1"
int_if="fxp0"

set block-policy return
set loginterface $ext_if

set skip on lo

# scrub incoming pcakets like you cannot set both SYN and FIN
scrub in all

# ftpproxy
nat-anchor "ftp-proxy/*"
rdr-anchor "ftp-proxy/*"

# Redirect ftp traffic to proxy
rdr pass proto tcp from any to any port ftp -> 127.0.0.1 port 8021

# let squid proxy act as web server and dns server
rdr pass on $ext_if proto tcp from any to 1.2.3.4/32 port {80,443} -> 10.0.0.1
rdr pass on $ext_if proto udp from any to 1.2.3.4/32 port 53 -> 10.0.0.1

# squid proxy server can go to anywhere
nat pass on $ext_if from 10.0.0.1 to any -> 1.2.3.4

# setup a default deny policy
block in all

# activate spoofing protection for all interfaces
block in quick from urpf-failed

# anchor for ftpproxy
anchor "ftp-proxy/*"

# pass tcp, udp, and icmp out on the external (Internet) interface.
# tcp connections will be modulated, udp/icmp will be tracked statefully
pass out modulate state

antispoof quick for { lo $int_if }
pass in quick on $ext_if inet proto icmp all icmp-type { echorep, timex, unreach }

pass in quick on $ext_if proto udp to 1.2.3.4 port 53 keep state
pass in quick on $ext_if proto tcp to 1.2.3.4 port {80,443} synproxy state

# use synproxy for internal host 10.0.0.0/24
pass in quick on $int_if proto tcp from 10.0.0.0/24 to $int_if port ssh synproxy state

# allow admin pc for anything
pass in quick on $int_if from 10.0.0.10/24

3. startup ftp proxy
ftp-proxy
pfctl -f /etc/pf.conf

References:
a. http://www.cyberciti.biz/faq/freebsd-opebsd-pf-firewall-ftp-configuration/

How to register with RHN

2011-03-14T15:32:00.003+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: summarize a few ways to register RHEL5 system with RHN
Environment: RHEL5, Squid proxy, RHN

Steps:
1. Configure /etc/sysconfig/rhn/up2date first

make sure the following lines are there.
serverURL=https://xmlrpc.rhn.redhat.com/XMLRPC
sslCACert=/usr/share/rhn/RHNS-CA-CERT

# ls -l /usr/share/rhn/RHNS-CA-CERT [file should be present]

If you are using Squid http proxy, configure this:
enableProxy=1
httpProxy=ip/name:port
proxyUser=
proxyPasword=

2. how to identify it's Oracle Linux or RHEL
a. cat /usr/share/rhn/RHNS-CA-CERT to see it's Oracle or Redhat
b. more /etc/issue.net or more /etc/issue
c. rpm -qi kernel to check Vendor part and Build Host

3. registration
3.1 - interactive way - rhn_register (configure proxy in step 1 first before use, if you are using proxy. or use

rhn_register --help to specify proxy settings)

If you have already registered before and /etc/sysconfig/rhn/systemid

exists on the system, rhn_register first asks if you are sure that

you would like to register in this way.

3.2 - non-interactive way - rhnreg_ks (kickstart style)

activationkey method (https://access.redhat.com/kb/docs/DOC-2395, need management entitlement)
e.g. rhnreg_ks --profilename=jephe --activationkey=1-2b48feedf5b5a0e0609ae028d9275c93

username/password method (for security reason, you need to clear history commands, use history -d or history -c and clear commands)
e.g. rhnreg_ks --profilename "jephe" --username "username" --password "password" [--force]

4. references
a. RHN FAQ - https://access.redhat.com/kb/docs/DOC-16303
b. How to register with RHN - https://access.redhat.com/kb/docs/DOC-11217
c. rhnreg_ks command: - https://access.redhat.com/kb/docs/DOC-2395
d. using rhn_register, up2date or yum to access RHN, RHN Satellite or RHN proxy via an HTTP proxy
- https://access.redhat.com/kb/docs/DOC-9826
e. activationkey (https://access.redhat.com/kb/docs/DOC-2395, need management entitlement)
f. difference between all kinds of entitlements: https://access.redhat.com/kb/docs/DOC-11277

GPG Concept and Usages

2011-03-10T16:04:00.000+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: understanding how gpg works and command usages
Environment: RHEL or CentOS, GnuPG

Concepts:
1. keypair generation
When you generate a pair of gpg keys by using command 'gpg --gen-key', basically you generated two pair of keys, one is used for DSA signature, another is for encryption/decryption(Elgamal). Private key contains the private part for both DSA and Elgamal keys, Public key contains the public part for both DSA and Elgamal keys.

You can use private key or public key to sign or encrypt file, then send to the peer. Or you can encrypt and sign at the same time.

when to use sign only?
If you need to publish a software to the public, in this case, the software itself is not confidential, you don't have to encrypt it. You can sign the software itself, in this case, you attach you signature at the end of the software. Or just put software itself on the website, then upload your signed result(.sig for binary file and .asc for ascii file) on the website for users to verify the signature of your software. Of course, user needs to get your master sign key(a part of public key) first. In order to let user to make sure that public key/sign key beglongs to you, you can put on your website for user to download. Or in personal email communcation case, just email public key to the peer.

If you need to communicate with your friends with some secret messages, like password, bank statements etc, you might need to use both encrypt/sign functions.

2. when you received a public key, what to do?
Firstly, you need to import to your public key ring, then you need to make sure it's from the real person you'd like to communicate with. If you received it through email or downloaded from that person's website, you will be pretty sure it's from that person, if not, you can check the fingerprint of that public key then call the person to confirm:

gpg --import jephe.gpg

gpg --fingerprint # get the fingerprint of the master public sign key
gpg --sign-key "emailaddress or name"

or run commands below to sign the key to validate it.
gpg --edit-key "emailadddress or name"
fpr
sign

3. how gpg sign files (what does it mean for 'good signature' after decrypting encrypted/signed file)
The following paragraph is from http://www.glump.net/howto/gpg_intro.
When GPG creates a digital signature, it doesn't encrypt the entire file with the signer's private key. Instead, it computes a hash value,6) encrypts that, and appends it to the original data as the signature. This makes it possible to create signed files that are readable without any encryption software, and aren't significantly larger; GPG is needed only to verify the authenticity of the file.

To verify a signature, GPG reads the data that was signed and computes its hash value. Then it decrypts the signature, using the signer's public key, to obtain the true hash value. If the two hash values match, the signature is valid and the data you have is exactly the data the signer had when he created the signature.

3.1 how to sign a file?
a. gpg -s # sign a file and append signature information to the file. No matter the file is ascii or binary file

b. For ascii files, you can also use --clearsign feature, for example.
# gpg -r jephe@domain.com --clearsign hosts # in this case, the original text and signature are in the same file.
# cat hosts.asc
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
10.0.0.1 jephe.domain.com jephe

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQFNeH45bkqqCc9zAksRAtwvAKCzxPtS8VajyPVL69+5L1KhOcHsPACgoAUf
bD+LeQhMSMkLqt41+mwHTOM0
=Wnmj
-----END PGP SIGNATURE-----

You can verify the signature by using 'gpg --verify hosts.asc'.

c. sign a binary file with detached signature (doc and zip files are not allowed for appending to it)
gpg -r jephe@domain.com -o file.zip.sig --detach-sign file.zip

Note: you need to use only .sig or .asc as detached file name. And, when you use gpg --verify to verify signature, you need to have original file and signature file at the same current directory, otherwise, gpg will report 'bad signature' because gpg needs the original data file to calculate hash value. Pls refer to point 3.

4. other usages
a. use symmetric key to encrypt/decrypt file
gpg -c files

b. decrypting files automatically
echo PASSPHRASE| gpg --passphrase-fd 0 OPTIONS COMMAND
or
cat filecontainspassphrase | gpg --passphrase-fd 0 OPTIONS COMMAND

c. Encrypting for Multiple Recipients
You can specify more than one people who need to decrypt your file, gpg will use public keys from all these people to encrypt file in such a way that any one of their private key can decrypt the file.
gpg -e -r user1 -r user2

Use RIP CD to fix the grub issue after RHEL 5 hard power shutdown

2011-03-05T22:05:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Scenario: HP Proliant DL360G5 server running RHEL 5 and 32bit Oracle 11g database (11.1), after yum update and some Oracle impdp/expdp operations, the server hangs, we have to press power button to shut it down, after power it on again, the GRUB stops at 'loading grub stage 2'.

Steps:
1. use RIP(Rescue Is Possible - http://www.tux.org/pub/people/kent-robotti/looplinux/rip/) to reboot server.
2. re-generate grub
# vgdisplay -v
# lvdisplay -v (find out the root LVM partition name)
# vgchange -a y
# mount /dev/VolGroup00/LogVol00 /mnt/hd
# cd /mnt/hd; chroot .
# mount /dev/cciss/c0d0p0 boot
# cd /dev
# MAKEDEV cciss
# grub-install hd0

3. finishing job
# cd /mnt/hd
# umount boot
# exit
# umount /mnt/hd
# reboot

Use ACL and SGID to make a group of users to work on the same directory and Linux file permission

2011-02-20T20:46:00.017+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: a parent directory called /shared is owned by db2inst1 (umask 022 only), and 2 other users called jephe and zhitan are going to work on the this directory /shared. They should be able to read/write any files and directories under /shared each other, also be able to read/write files created by db2inst1 which means any files created by db2inst1 will have group-writable permission although its umask is 022.

Environment: RHEL 5 or CentOS 5

Preparation:
You can mount the partition with acl option by using 'mount / -o remount,acl'.
or

cd /path/to/directory; df . -> find out which mount directory, let's say it's /data, then mount /data -o remount,acl

Steps:
1. configure the permission
When a new file is created on a Unix-like system, its permissions are determined from the umask of the process that created it.

[root@linuxtest /]# ls -ld shared (let its group to have write permission so that jephe and zhitan can create files and directories under /shared, but any files created by db2inst1 itself will not be group writable because its umask is 022)
drwxrwsr-x 2 db2inst1 its 4096 Feb 20 10:22 shared

[root@linuxtest /]# usermod -G its jephe
[root@linuxtest /]# id jephe
uid=500(jephe) gid=500(jephe) groups=500(jephe),502(its)
[root@linuxtest /]# usermod -G its zhitan
[root@linuxtest /]# id zhitan
uid=501(zhitan) gid=501(zhitan) groups=501(zhitan),502(its)

2. configure default acl permissions
[root@linuxtest /]# setfacl -d -m u:jephe:rwx,u:zhitan:rwx /shared
[root@linuxtest /]# setfacl -R -m u:jephe:rwx,u:zhitan:rwx /shared

Note:
The default ACL will be applied only for newly created files or directories under /shared directory. If you use only setfacl -d for the parent directory and user jephe and zhitan are still not able to write for this directory,

[root@linuxtest /]# getfacl shared
# file: shared
# owner: db2inst1
# group: its
user::rwx
group::rwx
other::r-x
default:user::rwx
default:user:jephe:rwx
default:user:zhitan:rwx
default:group::rwx
default:mask::rwx (the mask defines the maximum permissions that can be given to users or group, if it's --x, means the user and group can get maximum permission is --x, if the user or group itself doesn't have x permission, then mean user and group will get effective permission as ---, aka nothing)
default:other::r-x

Now, any files or directories created by db2inst1 will have group writable permission although the umask for db2inst1 is 022, this is different from the case before setting setfacl. Also, user jephe and zhitan will be able to create files or directories under /shared.

or you can just set up mask permission as rwx, then any files created by db2inst1 will have group write permissions.
[root@linuxtest /]# setfacl -R -m d:m:rwx /shared (modify mask as rwx)

A directory may contain default ACL entries. If a file or directory is created in a directory that contains default ACL entries, the newly created file will have permissions generated according to the intersection of the default ACL entries and the permissions requested at creation time. The umask will not be applied if the directory contains default ACL entries.

--------------umask explanation by example--------------
[root@linuxtest /]# setfacl -m m:r-- shared (set shared directory itself mask as r--)
[root@linuxtest /]# getfacl shared
# file: shared
# owner: db2inst1
# group: its
user::rwx
group::rwx                      #effective:r--
mask::r--
other::r-x
default:user::rwx
default:user:jephe:--x
default:group::rwx
default:mask::rwx
default:other::r-x

[root@linuxtest /]# setfacl -d -m m:r-- shared (set default mask as r--, means for any newly files/directories created under it )
[root@linuxtest /]# getfacl shared
# file: shared
# owner: db2inst1
# group: its
user::rwx
group::rwx                      #effective:r--
mask::r--
other::r-x
default:user::rwx
default:user:jephe:--x          #effective:---
default:group::rwx              #effective:r--
default:mask::r--
default:other::r-x
[root@linuxtest /]# ls -ld shared
drwxr-Sr-x+ 7 db2inst1 its 4096 Feb 20 13:18 shared

------------------end------------------------------------

3. remove all acl permissions
setfacl -R -b /shared

To remove all the permissions for a user, group, or others, use the -x option and do not specify any permissions:

setfacl -x <rules> <files>

To remove all permissions from the user jephe

setfacl -x u:jephe /shared

Linux file permissions:

Any program uses system calls to access files and directories.

1. directory:
You can imagine directory is like a datafile which conains a table, each row has filename and its inode number:

Read:  
This permissioin can only find out the name of the files under it , not inode and not other things stated in inode data structure

.

Write:  
This permission allows you to add, rename, or delete files within, not modifying file directly like

# echo testing > file1

# > file1

But vim can modify files. The following is from http://vimdoc.sourceforge.net/htmldoc/editing.html

*write-readonly*
When the 'cpoptions' option contains 'W', Vim will refuse to overwrite a
readonly file.  When 'W' is not present, ":w!" will overwrite a readonly file,
if the system allows it (the directory must be writable).

Also, all above actions also require changing or at least reading the inodes of the affected files,

so search permission is also needed to add/rename/delete files within

Execute: 
This permission means search, grants the ability to traverse its tree in orde to access files/subdirectories.

It's required to access the "inode" information of the files within.


2. File:
Read: system call read()
You need to get file's inode number to read it.
Only file owner or root user can modify information in the inode such as the owner and group name and permissions.

Write:system call write()  to modify file content

Execute: system call  exec()
If file is a program, you can only need x without r to execute that program, but if the file is a shell script,

you also must have r permission to execute it. The proper permissions on a script are both read and execute

Example: cat /home/jephe/file1, firstly you need to have read permission for file1 to read the content, then 
you need x permission on / /home /home/jephe to locate inode of file1and thus to read it.

References:

a. Unix File and Directory Permissions and Modes - http://content.hccfl.edu/pollock/AUnix/FilePermissions.htm

b. http://en.wikipedia.org/wiki/Filesystem_permissions

Some advanced usages on Putty and OpenSSH

2011-02-17T12:06:00.018+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: explore advanced usage on Putty on Windows and OpenSSH on Linux
Environment: Putty, OpenSSH

Usage:
1. use putty or openssh client to create a secure socks web proxy tunnel
You can create socks proxy server by using putty or openssh.

Putty:
create a normal ssh session, enable compression and ssh version 2, under tunnel menu, create a auto and dynamic source port 8080.
then configure your favorite browser to use socks5 proxy at 127.0.0.1:8080,try socks4 proxy if socks5 doesn't work. Configure 'no proxy for' part with 'localhost,127.0.0.1'.

Note: For data you retrieved through browser, most of them is text or HTML data, the compression rate is very high, so, enable compression is better.

OpenSSH:
The following command uses compression, SSH2, Quite, Force pseudo-tty allocation, Redirect stdin from /dev/null, and use 'master' mode for ssh client for connection sharing.

ssh -C2qTnN -D 8080 jephe@server.domain.com
or

ssh -C2qTnN -L 8080:localhost:3333 jephe@server.domain.com

(ssh tunnel and local forwarding to ssh server at port 3333(squid proxy)

You can try to access http://www.whatismyip.com/ to get the external source IP.

login as root to your ssh server, then use above command to ssh into the destination server to create socks proxy tunnel, then use 127.0.0.1:8080 as socks5 proxy to access Internet.

DNS query issue:
when using above socks5 proxy, by default, firefox/thunderbird requires your local Windows pc must be able to resolve DNS request. If not, you can make changes for firefox/thunderbird to enable remote DNS (open the about:config page, and change network.proxy.socks_remote_dns to true), which also secure DNS queries)

Multiple tabs:

network.http.max-persistent-connections-per-proxy 25

Note: some socksifier program
a. http://widecap.com/
b. http://www.proxycap.com/
c. http://tsocks.sourceforge.net/(Linux)

2. use http proxy software in ssh proxycommand option to ssh into Internet server directly
In office environment, you might not be able to direct ssh into some servers on the Internet. If the ssh server is listening at port 80 or 443, usually squid proxy server is allowing that, if not, you might need to do something to establish ssh connection, either by changing the destination server port to port 80/443 or enabling the squid to allow port 22.

For openssh/cygwin, you can use http://proxytunnel.sourceforge.net/ to use squid proxy server to tunnel your ssh connection.
In /etc/ssh/ssh_config, put
Host jephe
    Proxycommand /usr/bin/proxytunnel -p 10.0.0.1:8080 -d jephe.domain.com:22

then you can use 'ssh -v username@jephe -R 2222:localhost:22 -R 3389:10.0.0.2:3389' to do remote port forwarding to RDP/ssh to your office pc from home.

Another options is to use corkscrew - http://www.agroman.net/corkscrew/

Please refer to another article for details at How to access office server and admin desktop from home - http://linuxtechres.blogspot.com/2010/12/how-to-access-office-server-and-admin.html

3. use nc in proxycommand option to directly ssh into server on Internet through firewall
Case: You are not able to ssh directly to Internet , you have to ssh into firewall/proxy server, then you can ssh to Internet from firewall itself.

Solution: use nc to make it one step only.
on /etc/ssh/ssh_config, put the following line
Host external_ssh_server.domain.com
        ServerAliveInterval 60
        ServerAliveCountMax 600
    ProxyCommand ssh jephe@firewall_ip nc %h %p

Then run 'ssh username@jephe' to ssh directly to host on the Internet.
Note:
You might need to configure public key authentication without password for firewall and external_ssh_server.domain.com, otherwise, you might get something like 'write pipe error'.

Note: OpenSSH 5.4 onwards supports netcat mode with option -W host:port. See
http://www.openssh.org/txt/release-5.4

* Added a 'netcat mode' to ssh(1): "ssh -W host:port ..." This connects
   stdio on the client to a single port forward on the server. This
   allows, for example, using ssh as a ProxyCommand to route connections
   via intermediate servers.

The following example for using nc is by Fabian Arrotin from http://planet.centos.org/
You need to ssh/scp from your pc to HostC directly. (normal path: your pc->hostA->hostB->hostC)
==================================

Host HostB
Hostname the.known.fqdn.as.resolvable.by.HostA
User arrfab
ForwardAgent yes
Port 22
ProxyCommand ssh remoteuser@HostA.with.ssh.access nc %h %p

And what if you need to reach HostC, which itself is only reachable by HostB ? Let’s just define a new Host section in the ~/.ssh/config and another ProxyCommand !

Host HostC
Hostname the.known.fqdn.as.resolvable.by.HostB
User arrfab
ForwardAgent yes
Port 22
ProxyCommand ssh remoteuser@HostB nc %h %p

====================================

4. use zmodem transfer with leputty (http://leputty.sourceforge.net/)

It's much faster for you to upload/download files directly with putty, without opening winscp to do it.

You can use leputty, configuring sz/rz path for default putty settings so that all session created later on will have this settings automatically.

When you need to upload file from Windows pc to server, just use 'Zmodem upload' in Leputty.
When you need to download a file, ssh into server with Leputty, then type in 'sz filename', then click on menu 'Zmodem receive' to transfer to the predefined directory on Windows PC.

It's faster then using winscp.

References:
a. https://calomel.org/firefox_ssh_proxy.html

Oracle pfile, spfile and parameters, as well as AMM

2011-02-13T11:11:00.005+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: Checking the current running parameters for Oracle database and configuring AMM (automatic memory managgement)
Environment: Oracle 11g 64bit, RHEL 5

Commands:
1. Checking if the database is using pfile or spfile
sqlplus / as sysdba
select name,value from v$parameter where name='pfile';
select name,value from v$parameter where name='spfile';

2. generate pfile from spfile or memory
sqlplus / as sysdba
create pfile from spfile;
create pfile from memory;
create spfile from pfile; (if the database starts up with pfile initially, then need to change to spfile)

3. checking pfile,spfile and memory
sqlplus / as sysdba
show parameter memory_target;
show parameter pga;
show parameter sga;
show sga;

AMM configuration:
1. Configure tmpfs size
error message:
a. ORA-845: MEMORY_TARGET not supported on this system
b. Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature.
This feature requires the /dev/shm file system to be mounted for at
Least <size> bytes.The /dev/shm is either not mounted or is mounted
With available space less than this size.
Please fix this so that MEMORY_TARGET can work as expected.
Current available is <size> and used is <size> bytes.memory_target needs larger /dev/shm

If ORA-04031 is seen in the alert log, sometimes you can not establish new connections due to this problem.

Solutions:
(ORA-00845 When Starting Up An 11g Instance With AMM Configured. [ID 460506.1])
1. If you are installing Oracle 11g on a Linux system, note that Memory Size (SGA and PGA), which sets
the initialization parameter MEMORY_TARGET or MEMORY_MAX_TARGET, cannot be greater than the shared memory filesystem (/dev/shm) on your operating system. To resolve the current error, increase the /dev/shm file size. For example:
# mount -t tmpfs tmpfs -o size=12g /dev/shm
Also, to make this change persistent across system restarts, add an entry in /etc/fstab similar to the following:
tmpfs /dev/shm tmpfs size=12g 0

note: tmpfs is previously called shmfs - http://en.wikipedia.org/wiki/Tmpfs

2. Configure Automatic Memory Management(AMM) on 11g [ID 443746.1]
Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.

SQL>SHOW PARAMETER TARGET

Add the values of pga_aggregate_target and sga_target. In our case it is 12g

3.Decide on a maximum amount of memory that you would want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes. In our case we decide to set to 12g

4. apply changes to spfile
SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 12g SCOPE = SPFILE; SQL>ALTER SYSTEM SET MEMORY_TARGET = 12g SCOPE = SPFILE; SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE; SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

5. restart database
SQL> shutdown immediate;
sql> startup (or startup mount, then alter database open)

sql> show parameter target;

Home network troubleshooting for using cable modem, laptop/pc and wireless router

2011-02-03T09:01:00.005+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Environment: home cable modem network in Singapore (ISP: Starhub), Mortolona cable modem, Linksys WRT54 ver2.2 wireless router and laptop/pc
Objective: home network issue troubleshooting for accessing Internet.
Network diagram:
cable wall socket->cable modem->network cable RJ45 -> Linksys wireless router -> laptop/pc connected through physical cable or wireless access point
Issue: able to connect to wireless router through physical cable or access point and get IP address from wireless router itself, but cannot access Internet.

Troubleshooting steps:
1. basic troubleshooting to find out the problem
Everything seems okay between laptop and wireless router, laptop can get IP address and can access wireless router admin page at http://192.168.1.1(default linksys wireless router IP address).

2. check admin page router status page
Find out router itself cannot get WAN IP address actually.

3. Check cable modem itself (sequence is important, otherwise, you might not be able to get WAN IP address)
Disconnect network cable between cable modem and wireless router.
Connect a dell laptop to cable modem by RJ45 cable.
Shutdown both cable modem and laptop first (totally power down)
Power on the cable modem to wait for the first 4 lights become solid green (if standby light is on after that, please press the button on the top of the modem)
Now power on the laptop and wait for the PC/Activitity light is on
Check if the laptop is getting WAN ip address from ISP through DHCP (ipconfig /all)

In my case, the cable modem is okay and laptop is getting WAN IP and is able to access Internet.

4. Solve the problem of wireless router
Try above steps in point 3, shutdown power for both cable modem and wireless router, connect cable modem and wireless router through RJ45 network cable, power on cable modem first then wireless router later, check admin page, this time, the wireless router can get WAN IP address but client laptop/pc are still not able to access Internet(try to ping 8.8.8.8, cannot).

Try to check Linksys website to upgrade firmware if it's not the latest one. It's already latest in my case.
Use wireless admin page at http://192.168.1.1 to reset to factory default settings.
Then modify the settings such as wireless router admin password, SSID and WPA2 personal/AES password.

Optional - to disable Block Anonymous Internet Requests — This setting prevents the router from being able to be pinged or otherwise connected to on the external interface, unless you have defined a port-forwarding filter. This should be enabled, but keep in mind that not being able to ping the router can make it more difficult to troubleshoot.

Disable UPnP under Administration page.

With above setting is disabled, then configure port forwarding settings.

5. References
http://www.ciscopress.com/articles/article.asp?p=598649&seqNum=5

Site-to-Site IPSec VPN in FreeBSD 7 and OpenBSD 4

2011-01-28T11:15:00.021+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: understanding ipsec vpn and how to configure it in FreeBSD and OpenBSD
Environment: FreeBSD 7.2 and OpenBSD 4.6, the sender ip is 1.2.3.4/10.0.10.10, the peer ip is 5.6.7.8/10.0.5.10,
Concepts:

The IPsec suite is an open standard. IPsec uses the following protocols to perform various functions:^[2]^[3]

Authentication Headers (AH) provide connectionless integrity and data origin authentication for IP datagrams and provides protection against replay attacks.AH is used to authenticate — but not encrypt — IP traffic.

Authentication is performed by computing a cryptographic hash-based message authentication code over nearly all the fields of the IP packet (excluding those which might be modified in transit, such as TTL or the header checksum), and stores this in a newly-added AH header and sent to the other end.

AH use a special hashing algorithm and a specific key known only to the source and the destination. A security association between two devices is set up that specifies these particulars so that the source and destination know how to perform the computation but nobody else can. On the source device, AH performs the computation and puts the result (called the Integrity Check Value or ICV) into a special header with other fields for transmission. The destination device does the same calculation using the key the two devices share, which enables it to see immediately if any of the fields in the original datagram were modified (either due to error or malice).

data origin authentication, which enables the recipient to verify that messages have not been tampered with in transit (data integrity) and that they originate from the expected sender (authenticity).

Encapsulating Security Payloads (ESP) provide confidentiality, data origin authentication, connectionless integrity, an anti-replay service (a form of partial sequence integrity), and limited traffic flow confidentiality.

We want to not only protect against intermediate devices changing our datagrams, we want to protect against them examining their contents as well. For this level of private communication, AH is not enough; we need to use the Encapsulating Security Payload (ESP) protocol.

Security associations (SA) provide the bundle of algorithms and data that provide the parameters necessary to operate the AH and/or ESP operations.

A Security Association (SA) is the establishment of shared security attributes between two network entities to support secure communication. In a nutshell, an SA is a logical group of security parameters, that enable the sharing of information to another entity.

IPSec provides the following security services:
Confidentiality
traffic is encrypted to ensure that only the legitimate receiver is able to access the data transmitted.
Connectionless integrity
ensures that no modifications were made to the data while in transit across the network.
Data origin authentication
the receiver is able to verify that data actually originates from the claimed source.
Detection and rejection of replays (anti-replay)
duplicate IP datagrams are detected and processed only once.

These security services are provided at the IP layer (layer 3 of the OSI model), thus protecting all
protocols that may be carried over IP, including IP itself.

IPSec can be run in either tunnel mode or transport mode.

Tunnel mode is most commonly used between gateways, or at an end-station to a gateway
Transport mode is used between end-stations or between an end-station and a gateway, if the gateway is being treated as a host, in which the gateway is the actual destination.

Transport Mode provides a secure connection between two endpoints as it encapsulates IP's payload, while Tunnel Mode encapsulates the entire IP packet to provide a virtual "secure hop" between two gateways. The latter is used to form a traditional VPN, where the tunnel generally creates a secure tunnel across an untrusted Internet.

A secure VPN requires both authentication and encryption. We know that ESP is the only way to provide encryption, but ESP and AH both can provide authentication:

ESP+Auth is used in Tunnel mode to fully encapsulate the traffic on its way across an untrusted network, protected by both encryption and authentication in the same thing.

To accomplish security service, IPSec must perform (at least) the following tasks:

They must agree on a set of security protocols to use, so that each one sends data in a format the other can understand.
They must decide on a specific encryption algorithm to use in encoding data.
They must exchange keys that are used to “unlock” data that has been cryptographically encoded.
Once this background work is completed, each device must use the protocols, methods and keys previously agreed upon to encode data and send it across the network.

1. why phrase 1 and phrase2?
Phase 1 is used to set up a protected channel just
between the two gateway machines. This channel is then used for
the phase 2 negotiation traffic (i.e. encrypted & authenticated).

'Phase 2' defines which connections the daemon should establish.
These connections contain the actual "IPsec VPN" information.

2. ipsecctl since OpenBSD 3.8
A new command starts with version 3.8: ipsecctl.
a simple VPN can be setup by editing /etc/ipsec.conf, then you can start up VPN as follows:
isakmpd -K ipsecctl -f /etc/ipsec.conf
note: -K      When this option is given, isakmpd does not read the policy con-
             figuration file and no keynote(4) policy check is accomplished.
             This option can be used when policies for flows and SA establish-
             ment are arranged by other programs like ipsecctl(8) or bgpd(8).

use 'ipsecctl -sa' to get the status of the ipsec flows and associations:

or you can
Set this up to start automatically at reboot in /etc/rc.conf
isakmpd="-K"
PF=YES   # use pfctl -sr to get the running pf rules

in /etc/rc.local,put
ipsecctl -f /etc/ipsec.conf

3. FreeBSD-specific requirements
a. Recompiled FreeBSD kernel with IPSec support according to FreeBSD Handbook.
http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/kernelconfig-building.html
b. IPsec-Tools port (/usr/ports/security/ipsec-tools) installed on FreeBSD host.
c. manually create directory /usr/local/etc/

OpenBSD has required applications and IPSec support compiled into the GENERIC kernel, so there's no need to do anything.

4. encryption and authentication choices
Default racoon configuration available from FreeBSD's port uses 3des cipher for encryption and hmac_sha1 algorithm for authentication in both phases.

Default settings for isakmpd on OpenBSD are: aes and hmac-sha1 for phase 1 and aes and hmac-sha2-256 for phase 2.

5. protocols used in ipsec vpn
50 - Encapsulation Header (ESP)
51 - Authentication Header (AH)
4 - IP-ENCAP(ipencap protocol)

500/udp - Internet Key Exchange (IKE)
4500/udp - NAT-Traversal

esp     50      IPSEC-ESP       # Encap Security Payload [RFC2406]
ah      51      IPSEC-AH        # Authentication Header [RFC2402]

ipencap 4       IP-ENCAP        # IP encapsulated in IP (IP in IP)

OpenBSD: use udp 500 and esp protocol. Here is example:

set skip on { lo enc0 } #skip on enc0, you can enable enc0 and filter traffic
block on em0
pass  in on em0 proto udp from 5.6.7.8 to 1.2.3.4 port 500
pass  in on sk0 proto esp from 5.6.7.8 to 1.2.3.4
pass out on em0 proto udp from 1.2.3.4 to 5.6.7.8 port 500
pass out on sk0 proto esp from 1.2.3.4. to 5.6.7.8

Freebsd: uses 500/UDP, ESP and -- additionally -- IPENCAP traffic.
pass in quick on dc0 proto udp from 5.6.7.8 port = isakmp to 1.2.3.4 port = isakmp
pass in quick on dc0 proto esp from 5.6.7.8 to 1.2.3.4
pass out quick on dc0 proto udp from 1.2.3.4 port = isakmp to 5.6.7.8 port = isakmp
pass out quick on dc0 proto esp from 1.2.3.4 to 5.6.7.8

pass in quick on dc0 proto ipencap from 5.6.7.8 to 1.2.3.4
pass out quick on dc0 proto ipencap from 1.2.3.4 to 5.6.7.8
block quick on dc0
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Methods:
1. openbsd 4.6 - isakmpd method

a. root@SHGFWHA9 # grep remote_vpngw /etc/pf.conf
vpn_wan_ip   = "{1.2.3.4}"
remote_vpngw = "{5.6.7.8}"
pass in log quick on $vpn_wan_if proto {tcp,udp,icmp} from $remote_vpngw to $vpn_wan_ip keep state
pass in log quick on $vpn_wan_if proto esp from $remote_vpngw to $vpn_wan_ip   keep state
pass in log quick on $vpn_wan_if proto udp from $remote_vpngw port { 500, 4500 } to   $remote_vpngw port { 500, 4500 } keep state

b. isakmpd and ipsec
root@SHGFWHA9 # grep -v ^# /etc/isakmpd/isakmpd.conf | grep -v ^$
[General]
Retransmits=            5
Exchange-max-time=      120
Default-phase-1-lifetime= 86400,28800:86400
Listen-on=              1.2.3.4
DPD-check-interval=     30 #DPD means Dead Peer Detection

[Phase 1]
5.6.7.8 =         ISAKMP-peer1

[Phase 2]
Connections=            IPsec-peer1,IPsec-peer2

[ISAKMP-peer1]
Phase=                  1
Transport=              udp
Local-Address=          1.2.3.4
Address=                5.6.7.8
Configuration=          Default-main-mode
Authentication=         abcdef

[IPsec-peer1]
Phase=                  2
ISAKMP-peer=            ISAKMP-peer1
Configuration=          Default-quick-mode
Local-ID=               Net-local
Remote-ID=              Net-peer1

[Net-local]
ID-type=                IPV4_ADDR_SUBNET
Network=                10.0.10.0
Netmask=                255.255.255.0

[Net-peer1]
ID-type=                IPV4_ADDR_SUBNET
Network=                10.0.5.0
Netmask=                255.255.255.0

[Default-main-mode]
DOI=                    IPSEC
EXCHANGE_TYPE=          ID_PROT
Transforms=             DES-SHA

[Default-quick-mode]
DOI=                    IPSEC
EXCHANGE_TYPE=          QUICK_MODE
Suites=                 QM-ESP-DES-SHA-SUITE

c. when you run ps with isakmpd method, you should see something below:
root@SHGFWHA9 # ps ax | grep isakmpd | grep -v grep
26976 ?? Is      0:42.69 isakmpd: monitor [priv] (isakmpd)
8410 ?? S       0:00.40 isakmpd

d. get vpn status in OpenBSD
# ipsecctl -sa

2. OpenBSD 4.6 - ipsec method
a. /etc/ipsec.conf
ike esp from 10.0.10.0/24 to 10.0.5.0/24 peer 5.6.7.8
main auth hmac-sha1 enc blowfish group modp1024
quick auth hmac-sha2-256 enc blowfish group modp1024

b. refer to concepts second point to start up VPN in ipsec method

3. FreeBSD 7.2 - ipsec/racoon method
Refer to handbook link below to setup IPSec VPN on FreeBSD
http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/ipsec.html
a. more /etc/ipsec.conf
spdadd 1.2.3.4/32 5.6.7.8/32 ipencap -P out ipsec esp/tunnel/1.2.3.4-5.6.7.8/require;
spdadd 5.6.7.8/32 1.2.3.4/32 ipencap -P in ipsec esp/tunnel/5.6.7.8-1.2.3.4/require;
or
more /etc/ipsec.conf
spdadd 10.0.10.0/24 10.0.5.0/24 any -P out ipsec esp/tunnel/1.2.3.4-5.6.7.8/require;
spdadd 10.0.5.0/24 10.0.10.0/24 any -P in ipsec esp/tunnel/5.6.7.8-1.2.3.4/require;

1.2.3.4/32 and 5.6.7.8/32 are the IP addresses and netmasks that identify the network or hosts that this policy will apply to. In this case, we want it to apply to traffic between these two hosts. ipencap tells the kernel that this policy should only apply to packets that encapsulate other packets. -P out says that this policy applies to outgoing packets, and ipsec says that the packet will be secured.

b. /usr/local/etc/racoon.conf
vpn# grep -v ^# /usr/local/etc/racoon.conf | grep -v ^$
path pre_shared_key "/usr/local/etc/psk.txt" ;
log debug2;
remote anonymous
{
    exchange_mode main,base;
    lifetime time 24 hour ;
    proposal {
        encryption_algorithm des;
        hash_algorithm sha1;
        authentication_method pre_shared_key ;
        dh_group 2 ;
    }
    proposal_check strict;
}
sainfo anonymous
{
    lifetime time 12 hour ;
    encryption_algorithm 3des, cast128, blowfish 448, des, rijndael ;
    authentication_algorithm hmac_sha1,hmac_md5 ;
    compression_algorithm deflate ;
}
note: proposal part is phrase 1, sainfo part is phrase 2.

c. vpn# more psk.txt
5.6.7.8    abcdefg

d. vpn# more /etc/rc.local
/usr/local/sbin/racoon -l /var/log/racoon.log
note:
# racoon -F (put racoon in foreground to debug)

e. vpn# /etc/rc.conf

vpnsg# more /etc/rc.conf

linux_enable="YES"
sshd_enable="YES"
usbd_enable="YES"
gateway_enable="YES"

ipsec_enable="YES"
ipsec_file="/etc/ipsec.conf"

pf_enable="YES"
pf_flags=""
pf_rules="/etc/pf.conf"
pflogd_enable="YES"
pflog_logfile="/var/log/pflog"
pflog_flags=""

ifconfig_xl0="inet 10.0.10.10 netmask 255.255.255.0"
ifconfig_fxp0="inet 1.2.3.4 netmask 255.255.255.240"
defaultrouter="1.2.3.1"
hostname="vpn.jephe.com"

###
gif_interfaces="gif0"
gifconfig_gif0="1.2.3.4 5.6.7.8"

static_routes="vpn"
route_vpn="-net 10.0.5.0/24 10.0.5.10"
# 10.0.5.10 is the remote peer vpn gateway internal NIC ip address

f. /etc/pf.conf
vpnsg# cat /etc/pf.conf | grep -v ^# | grep -v ^$
ext_if="fxp0"    # fxp0 ip is 1.2.3.4/255.255.255.240, gateway is 1.2.3.1
int_if="xl0"    # xl0 ip is 10.0.10.10/24
scrub in all
set skip on lo0
block in log all
pass out log quick keep state
pass in log quick on $ext_if inet proto icmp all icmp-type { echorep, timex, unreach }
pass in log quick on $int_if inet proto icmp all icmp-type { echorep, timex, unreach }
pass in log quick on $int_if proto tcp from 10.0.10.0/24 to $int_if port 22 keep state
pass in log quick on $int_if from 10.0.10.0/24 keep state
pass in log quick on $ext_if from 5.6.7.8 to $ext_if keep state

g. get status of vpn in FreeBSD
# setkey -D  # dump SAD entries
# setkey -DP # dump PAD entries

note: the following is from http://www.tcpipguide.com/free/t_IPSecSecurityAssociationsandtheSecurityAssociation.htm
Security Policies, Security Associations and Associated Databases

To manage all of this complexity, IPSec is equipped with a flexible, powerful way of specifying how different types of datagrams should be handled. To understand how this works, we must first define two important logical concepts:

Security Policies: A security policy is a rule that is programmed into the IPSec implementation that tells it how to process different datagrams received by the device. For example, security policies are used to decide if a particular packet needs to be processed by IPSec or not; those that do not bypass AH and ESP entirely. If security is required, the security policy provides general guidelines for how it should be provided, and if necessary, links to more specific detail.

Security policies for a device are stored in the device's Security Policy Database (SPD).
Security Associations: A Security Association (SA) is a set of security information that describes a particular kind of secure connection between one device and another. You can consider it a "contract", if you will, that specifies the particular security mechanisms that are used for secure communications between the two.

A device's security associations are contained in its Security Association Database (SAD).

It's often hard to distinguish the SPD and the SAD, since they are similar in concept. The main difference between them is that security policies are general while security associations are more specific. To determine what to do with a particular datagram, a device first checks the SPD. The security policies in the SPD may reference a particular security association in the SAD. If so, the device will look up that security association and use it for processing the datagram.

References:
1. http://unixwiz.net/techtips/iguide-ipsec.html (An Illustrated Guide to IPsec)
2. http://en.wikipedia.org/wiki/IPsec (wiki ipsec)
3. http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/ipsec.html ( FreeBSD handbook VPN over IPSec)

How to troubleshoot Internet connectivity issues

2011-01-13T10:04:00.002+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Situation: packet loss on Internet between countries, one end ip is 1.2.3.4, other end is 5.6.7.8
Objective: use free tools to troubleshoot the issue

Tools:
1. ping
ping -c 100 1.2.3.4 to check the RTT and packet loss rate

2. traceroute or tracert
check the RTT time for each hop.

You can paste the result to http://www.geobytes.com/TraceRouteLocator.htm to get the location information.

3. tcptraceroute or traceroute -T(CentOS 5)
If the udp normal traceroute is not able to go through, you can try tcptraceroute or traceroute -T

4. telnet or nc
telnet serverip portnumber to check the connectivity

nc -vz servername portnumber to check if tcp portnumber is open, not applicable for udp port which it will always say it's open.

5. mtr (My TraceRoute) - http://www.bitwizard.nl/mtr/
use mtr to check packet loss and RTT time.

6. wireshark(GUI) or tshark(CLI)
use tshark to monitor packet loss (tcp retransmission)

7. tcpdump to check if the traffic is coming into the server

References:
http://www.geobytes.com/IpLocator.htm - IP Locator

use pscp with DSA key without password under Windows

2011-01-04T14:21:00.004+08:00

Jephe Wu - http://linuxtechres.blogspot.com

Objective: use pscp and pageant for ssh DSA authentication without password
Environment: Windows

Script:
1. use pageant to generate jephe and jephe.ppk on Windows , then put the content of jephe to the openssh server jephe homedirectory .ssh/authorized_keys2.

2. script
@For /F "tokens=1,2,3 delims=/ " %%A in ('Date /t') do @(
    Set Month=%%A
    Set Day=%%B
    Set Year=%%C
)
note: above script "tokens=1,2,3 delims=/ ", means delimiter is / or white space. %%A means %A, which is for the whole string of command 'date /t', then use / or white space as delimiter

@For /F "tokens=1,2 delims= " %%A in ('Time /t') do @(
    Set Hour=%%A
    Set Second=%%B
)

note: use only whitespace as delimiter.

set yearmonthday=%Year%%Month%%Day%
set hoursecond=%Hour% %Second%

echo --- >> log.txt
echo pscp -i jephe.ppk jephe@1.2.3.4:jephe.txt jephe.txt.%yearmonthday% %hoursecond% >> log.txt
pscp -i jephe.ppk jephe@1.2.3.4:jephe.txt . >> log.txt 2>&1

Oracle DDL Trigger on Database

2010-12-30T10:14:00.001+08:00

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;
/

Linux Clustering Concept

2010-12-25T11:58:00.000+08:00

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

2010-12-16T18:23:00.000+08:00

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

2010-12-15T15:12:00.003+08:00

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

2010-12-11T19:33:00.013+08:00

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

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 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

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.

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:
1. bypass any firewall: http://www.saulchristie.com/how-to/bypass-firewalls (with apache mod_proxy, saved pdf on dropbox proxytunnel folder)
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

2010-12-09T19:36:00.001+08:00

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

2010-12-08T17:21:00.004+08:00

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

2010-12-05T17:40:00.001+08:00

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