Thinks you should know about sudo command

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

Envirnment: CentOS 6.4 64bit
Objective:  understanding sudo command and common usages.



<username1> <ALL hosts>=(username2) [NOPASSWD:] commands

Above syntax will grant username1 to run commands as username2 on all hosts

1. sudo -l after you ssh into server

sudo -l will list the possible commands you could run

2. sudo -i  to become root ( similiar to su - , using root user environment configuration including $HOME and $PATH)

Other similiar command such as sudo -s, sudo su and sudo bash.

3. sudo -k to clear password cache 

You can also configure password cache time (minutes) in /etc/sudoers, by default, it's cached for 5 minutes, change to 0 will always ask for

password.

[root@server1 ~]# grep -i timestamp_timeout /etc/sudoers
Defaults timestamp_timeout=1

To totally disable password prompt, use  NOPASSWD: between (ALL) and right-most ALL

<username> ALL=(ALL) NOPASSWD: ALL

4. always use visudo to edit /etc/sudoers instead of vi /etc/sudoers
visudo check syntax error


Note:
So you can remember above sudo command parameters by LIK(e), e for edit which uses visudo.

Refer to https://help.ubuntu.com/community/RootSudo

Use Linux Jumphost as Transparent Proxy

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

Environment: Office and data center, there's only one or two Linux jump hosts in data center, from office, you are only able to ssh into jump hosts, from jump hosts you can ssh into other servers.  your client Linux pc and jump hosts are running CentOS 6.4

Objective: make this ssh process one step only instead of two steps by configuring jump hosts as transparent ssh proxy.

Steps:
1.  add jump hosts into your /etc/hosts

[root@jephe .ssh]# grep jump /etc/hosts
172.16.50.1 jump01
172.16.50.2 jump02

2. putting the following into /etc/ssh/ssh_config in your client Linux pc

host jump01
ServerAliveInterval 60
ServerAliveCountMax 30
proxycommand none

host jump02
ServerAliveInterval 60
ServerAliveCountMax 30
proxycommand none

host *
ServerAliveInterval 60
ServerAliveCountMax 30
proxycommand ssh jephe@jump01 -W %h:%p
#proxycommand ssh jephe@jump01 nc %h %p

Note: 
a. put host jump01 and host jump02 before host *
b. if your ssh version is lower which doesn't support -W, you can use nc instead 
which commented above, take note that the syntax is different for -W and nc 
which is %h:%p vs %h %p

3. setting passwordless login from your linux pc to jump hosts

ssh-keygen -d 
ssh-copy-id -i /root/.ssh/id_dsa.pub jephe@jump01
ssh-copy-id -i /root/.ssh/id_dsa.pub jephe@jump02

Note: you can also use command below to make passwordless login:

cat /root/.ssh/id_dsa.pub | ssh jephe@jump01 'cat >> /home/jephe/.ssh/authorized_keys'

4. testing
Now, from your Linux client side pc, run 'ssh jephe@jump01' and 'ssh jephe@jump02', you should be able to ssh without password.

Also, from your client pc, run 'ssh user@allotherserver', it should prompt you password. 

After you ssh into other server directly, 'w' command will show it comes from jump host, not your Linux PC. It actually ssh into jumphost first in background, then from jumphost ssh into other server.

How to disable user ssh login under Linux

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

Objective:  explain all kinds of ways for disablinig user shell access under Linux and their pros and cons
Environment: CentOS 6.4 64bit


Methods:

1. using /sbin/nologin or /bin/false 
useradd jephe -s /sbin/nologin
or
chsh -s /sbin/nologin

pros: disable shell access
cons: doesn't disable SSH tcp port forwarding.

e.g. user can use ssh to server to enable port forwarding as follows without actual shell access:

ssh -N -L 2222:remote:22 server

2. password lock/unlock

passwd -l jephe and passwd -u jephe
usermod -L jephe and usermod -U jephe

Pros: disable shell access with using /etc/shadow password
cons: if user had configured public key authentication before you lock password, user can still ssh in.

related command: chage -d 0 # to make user password expire so that user have to change password immediately upon login, you can run 'chage -l user' to check.

chage -d 0 is different with chage -E0, chage -d 0, make password expire, to  force user to change password after login, chage -E0, make account expire, totally disable user for the system.

3. make user account expire totally
chage -E0 jephe and reverse it by chage -E-1 jephe

Pros and cons: totally disable user account, user is unable to ssh anyway
If you need to totally disable user ssh, you should use this way.

[root@server1 ~]# chage -E0 corkroo
[root@server1 ~]# getent shadow corkroo
corkroo:$6$ewLIUEu8$VNk7OC2ybTHDaeXX1xuCI9DHGLig3IhasJ3VbLUwRMt123/kT1NAtshYuq2yQKZab82D1FEPZXnM3zTt5krKl0:15992:0:99999:7::0:
[root@server1 ~]# chage -E-1 corkroo
[root@server1 ~]# getent shadow corkroo
corkroo:$6$ewLIUEu8$VNk7OC2ybTHDaeXX1xuCI9DHGLig3IhasJ3VbLUwRMt123/kT1NAtshYuq2yQKZab82D1FEPZXnM3zTt5krKl0:15992:0:99999:7:::

4. force sftp access only, not ssh

User still have normal shell /bin/bash, but configure /etc/ssh/sshd_config to force user to use sftp only, not shell access
[root@server1 ~]#   tail -7 /etc/ssh/sshd_config
Subsystem sftp internal-sftp

Match User corkroo
X11Forwarding no
AllowTcpForwarding no
ForceCommand internal-sftp

[root@server1 ~]# getent passwd corkroo
corkroo:x:504:505::/home/corkroo:/bin/bash

[root@server1 ~]# ssh corkroo@localhost
Password:  # then it's hanging here.

Retrieve directory data underneath NFS mounted directory

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

Problem:  we need to retrieve data from certain directory before we did NFS mount on the same directory,however, we cannot umount it since it's production environment
Concept:  use mount --bind / to another directory to retrieve data without umounting NFS 
Steps:


1. check the existing files under /u05, for testing purpose, I have copied some archive log files from /u03 to /u05.
root@linuxtechres:/u05# ll
total 420
-rw-r----- 1 root root 229888 Oct 15 10:48 1_21996_817700775.arc
-rw-r----- 1 root root 180736 Oct 15 10:48 1_22005_817700775.arc

2. NFS mount /u05 which is using Netapp NFS storage for controlfile 
root@linuxtechres:/# mount /u05
root@linuxtechres:/# cd /u05
root@linuxtechres:/u05# ll
total 4
drwxr-xr-x 3 oracle dba 4096 Jun 28 09:51 control

3. Now we need to retrieve the original files under /u05 
root@linuxtechres:/u05# mkdir /tmp/recover
root@linuxtechres:/u05# mount --bind / /tmp/recover   # use / since original /u05 content is under / partition

4. check mounted directory 
root@linuxtechres:/u05# cd /tmp/recover/
root@linuxtechres:/tmp/recover# ls
appdata boot home lost+found mnt proc selinux sys u01 u04 var
backup dev lib media net root srv tftpboot u02 u05
bin etc lib64 misc opt sbin stage tmp u03 usr

root@linuxtechres:/tmp/recover# cd u05
root@linuxtechres:/tmp/recover/u05# ll
total 420
-rw-r----- 1 root root 229888 Oct 15 10:48 1_21996_817700775.arc
-rw-r----- 1 root root 180736 Oct 15 10:48 1_22005_817700775.arc

5. compare file md5sum
root@linuxtechres:/tmp/recover/u05# md5sum 1_21996_817700775.arc
f0af8a88658071aab2babab74c614159 1_21996_817700775.arc

root@linuxtechres:/tmp/recover/u05# md5sum /u03/oraarch/JEPHE/1_21996_817700775.arc
f0af8a88658071aab2babab74c614159 /u03/oraarch/JEPHE/1_21996_817700775.arc

6. umount /tmp/recover after recovery
root@linuxtechres:/tmp/recover/u05# mount | grep recover
/ on /tmp/recover type none (rw,bind)
root@linuxtechres:/tmp/recover/u05# cd /
root@linuxtechres:/# umount /tmp/recover
root@linuxtechres:/# mount | grep recover


Note: you may also use debugfs to retrieve data 

TSIG,DNSSEC and forward/reverse DNS delegation

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

Objective: setting up forward and reverse domain classless delegation, TSIG for zone transfer and DNSSEC for security.
Environment: CentOS 6.4 64bit, company domain is jephe.com, class C ip address for jephe.com is a.b.c.0/24.
Forward dns delegation: sg.jephe.com will be delegated to singapore office name server ns1.sg.jephe.com and ns2.sg.jephe.com
Classless reverse dns delegation: APNIC Australia is in charge of reverse DNS in-addr.arpa.
ISP1 will use ip address a.b.c.0-63 (master dns:ns1.jephe.com:a.b.c.1 slave dns:ns2.jephe.com:a.b.c.2);
ISP2 will use ip address a.b.c.64-127 (master dns: ns1.wu.com:a.b.c.65 slave dns:ns2.wu.com:a.b.c.66)



1. start up bind on CentOS 6.4 64bit VM
if it looks like hanging when you try to startup named service, e.g. stop at 'Generating /etc/rndc.key'.
You can modify /etc/init.d/named the following, add -r /dev/urandom and -t /var/named/chroot

 if /usr/sbin/rndc-confgen -r /dev/urandom -t /var/named/chroot -a > /dev/null 2>&1; then

2. chroot by default on CentOS 6.4
you can modify any file under /var/named/, they will be hard-linked to exact same filename with same inode number under  /var/named/chroot/var/named/.

3. forward domain delegation
on Australia name server, run

dnssec-keygen -a HMAC-MD5 -b 128 -n HOST jephe.com

it will generate 2 files which are Kjephe.com.+157+<XXX>.key and Kjephe.com.+157+<XXX>.private

On Australia /etc/named.conf:
----------------
zone "jephe.com" IN {
type master;
file "named.jephe.com";
allow-transfer { key key.jephe.com; };
};

include "jephe.com.key";
;note: jephe.com.key is a file under /var/named/, it contains symmetric keys for TSIG use.
------------

In file named.jephe.com, it looks like this:
--------------
$TTL 600
@ IN SOA jephe.com. admin.jephe.com. (
2013073001 ; serial
1D ; refresh
1H ; retry
1W ; expire
3H ) ; minimum
NS ns1.jephe.com.
NS ns2.jephe.com.
ns1 A a.b.c.1
ns2 A a.b.c.2
www A a.b.c.3
mail A a.b.c.4


sg.jephe.com. 600 NS ns1.sg.jephe.com.
sg.jephe.com. 600 NS ns2.sg.jephe.com.

;now add glue record
ns1.sg.jephe.com. A a.b.c.65
ns2.sg.jephe.com. A a.b.c.66
------------

key file content on both AU and SG dns server is as follows:

[root@jephe named]# more jephe.com.key
key key.jephe.com {
algorithm HMAC-MD5;
secret "p0xJ5Bv5xzuY03QhUDWSjQ==";
};

server a.b.c.65 {
        keys { key.jephe.com; };
};


on Sinagpore dns server /etc/named.conf:
-------------
zone "jephe.com" IN {
type slave;
masters { a.b.c.1; };
file "slaves/named.jephe.com";
};

zone "sg.jephe.com" IN {
type master;
file "named.sg.jephe.com";
};

include "jephe.com.key";
-------------


4. reverse DNS delegation

On APNIC dns server:
---------------------
[root@apnic named]# more named.c.b.a
$TTL 3600
@ IN SOA c.b.a.in-addr.arpa. admin.c.b.a.in-addr.arpa. (
2013073001 ; serial
1D ; refresh
1H ; retry
1W ; expire
3H ) ; minimum
NS ns1.jephe.com.
NS ns2.jephe.com.

1-63 NS ns1.jephe.com.
1-63 NS ns2.jephe.com.
$GENERATE 1-63 $ CNAME $.0-63

64-127  NS ns1.wu.com.
64-127 NS ns2.wu.com.
$GENERATE  64-127 $ CNAME $.64-127
-----------------

note: 
You also can use the following instead of $GENERATE line
1  CNAME 1.1-63
2  CNAME 2.1-63
3  CNAME 3.1-63
...
65 CNAME 65.64-127
66 CNAME 66.64-127
67 CNAME 67.64-127
...

on ns1.jephe.com master dns server, configure reverse dns as follows:
--------------
[root@jephe named]# more named.c.b.a
$ORIGIN 1-63.c.b.a.in-addr.arpa.
$TTL 600
@ SOA  1-63.c.b.a.in-addr.arpa. root.1-63.c.b.a.in-addr.arpa. (
2013073001
1h
30m
1w
24h )

NS  ns1.jephe.com.
NS  ns2.wu.com.

$GENERATE  1-63 $ PTR host$.jephe.com.

Note: you also can use the following instead of above $GENERATE line
65 PTR host65.jephe.com.
66 PTR host66.jephe.com.
...
-------------

on ns1.wu.com master dns server, configure reverse dns as follows:
---------------
$ORIGIN 64-127.c.b.a.in-addr.arpa.
$TTL 600
@ SOA  64-127.c.b.a.in-addr.arpa. root.64.127.c.b.a.in-addr.arpa. (
2013073001
1h
30m
1w
24h )

NS  ns1.wu.com.
NS  ns2.wu.com.

$GENERATE  64-127 $ PTR host$.wu.com.

Note: you also can use the following instead of above $GENERATE line
65 PTR host65.wu.com.
66 PTR host66.wu.com.
...
---------------

4. DNSSEC

Steps for setting up a Secure Zone jephe.com

a. Enable DNSSEC in the configuration file (named.conf) 
dnssec-enable yes;
dnssec-validation yes;

b. Create key pairs (KSK and ZSK) 

dnssec-keygen -a rsasha1 -b 1024 -n -3 zone jephe.com
dnssec-keygen -a rsasha1 -b 1024 -f ksk -3 -n zone jephe.com

this will generate 4 files, 2 files for zsk, 2 files for ksk.

c. include your DNSKEYs in the zone files named.jephe.com

$INCLUDE “K<myzone>.+005+<id_of_zsk>.key”
$INCLUDE “K<myzone>.+005+<id_of_ksk>.key”

d. sign the zone using secret zsk keys.
dnssec-signzone -o jephe.com -N increment -f named.jephe.com.signed -k
Kjephe.com.+005+idofksk named.jephe.com Kjephe.com.+005+idofzsk

This will generate another file called dsset-jephe.com. which is the hash of ksk public key.

e. Publish your public key 
give above hash file dsset-jephe.com to your .com NS server, they should include to their zone file
$INCLUDE “dsset-jephe.com."

f. update config file to use named.jephe.com.signed file as zone file  

g. test with dig
dig @nsserverof.com www.jephe.com  +dnssec
check if the above command has ad flag which indicates the returned answer is dnssec authenticated data.

5.logging for troubleshooting

logging {
channel my_dns_log { file "dns_log.txt"; severity debug 99; };
category queries { my_dns_log; };
category security { my_dns_log; };
category xfer-in { my_dns_log; };
category xfer-out { my_dns_log; };
};


6. query results example

[root@jephe named]# dig @localhost -x a.b.c.65

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.el6_4.4 <<>> @localhost -x 192.168.3.65
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 25353
;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 1, ADDITIONAL: 1

;; QUESTION SECTION:
;65.c.b.a.in-addr.arpa. IN PTR

;; ANSWER SECTION:
65.c.b.a.in-addr.arpa. 86400 IN CNAME 65.64-127.c.b.a.in-addr.arpa.
65.64-127.c.b.a.in-addr.arpa. 60 IN PTR ns1.sg.jephe.com.

;; AUTHORITY SECTION:
64-127.c.b.a.in-addr.arpa. 60 IN NS ns1.sg.jephe.com.
64-127.c.b.a.in-addr.arpa. 60 IN NS ns2.sg.jephe.com.

;; ADDITIONAL SECTION:
ns1.sg.jephe.com. 86400 IN A a.b.c.65

;; Query time: 1031 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Sun Jul 28 19:04:24 2013
;; MSG SIZE  rcvd: 130


[root@jephe ~]# dig @192.168.3.1 jephe.com axfr

[root@jephe named]# dig @localhost jephe.com axfr
; Transfer failed.

[root@jephe named]# dig @localhost jephe.com axfr -k jephe.com.key

[root@jephe named]# dig @192.168.200.1 lab.net  +dnssec
note: assume 192.168.200.1 is the .net name server

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.el6_4.4 <<>> @192.168.200.1 lab.net +dnssec
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 2130
;; flags: qr rd ra ad; QUERY: 1, ANSWER: 2, AUTHORITY: 3, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags: do; udp: 4096
;; QUESTION SECTION:
;lab.net. IN A

;; ANSWER SECTION:
lab.net. 86400 IN A 192.168.3.1
lab.net. 86400 IN RRSIG A 5 2 86400 20130830032400 20130731032400 18513 lab.net. XLy8bq7fA0t4FEckC6wrjk

+kkPDU6CO05ftWlGGDK1engfcRKr4s35G4 a2Kd3vO7OCH92XQyPMGcW8QzBwfT/CPO7mCSn+gQ2c6ymUVnIJKeJSd5 QOVvdCph6mzweHCHB8DGN6vJ0ENSwOcfhz8vFaEYXgnttGFQwiCTLfzY
xHw=

;; AUTHORITY SECTION:
lab.net. 86383 IN NS ns1.lab.net.
lab.net. 86383 IN NS ns2.lab.net.
lab.net. 86383 IN RRSIG NS 5 2 86400 20130830032400 20130731032400 18513 lab.net. ngyOposOYTp2tPIFGhW7xwuMMbmvLqNDDt1n

+YvRbFVHjbUealyN6D1K Xw2j9X4Z7YjbtStgGDDyciovV2TmW89hSlQAISHMZAEzVLBxdCKTobfs 1Qn341eIhIpl7L8TuqiJ1ObHSS2TBW8208x2GaD5/pVjrTWEa21YbgUh +hY=

;; Query time: 63 msec
;; SERVER: 192.168.200.1#53(192.168.200.1)
;; WHEN: Wed Jul 31 16:15:40 2013
;; MSG SIZE  rcvd: 428


7. FAQ

a. all zones must be specified in view statement.
put all your zones inside view statement

b. client 127.0.0.1#37642: view internal: bad zone transfer request: 'domain.com/IN': non-authoritative zone (NOTAUTH)
client 127.0.0.1#37642: view internal: zone transfer setup failed

check master dns server zone file SOA part, must be 'domain.com'.



8. References

http://dnssec-debugger.verisignlabs.com
http://dnsviz.net/
http://dnssec.net/

How to use ipmitool to manage server

Jephe Wu - http://linuxtechres.blogspot.com 
Objective: to use ipmitool under Linux to remote manage servers


Steps:

1. References
http://docs.oracle.com/cd/E19469-01/820-6413-13/IPMI_Overview.html#50446406_95669  Sun server ipmi usage example
http://linux.die.net/man/1/ipmitool   ipmi man page

2. Check if the ipmi port is open
root@jephe:~/bin# nmap -sU -p 623 172.17.2.10

Starting Nmap 5.00 ( http://nmap.org ) at 2013-06-18 20:48 EST
Interesting ports on 172.17.2.10:
PORT    STATE         SERVICE
623/udp open|filtered asf-rmcp

Note: open|filtered doesn't mean it's definitely open. If it shows 'closed' , it means really closed.

how to check if tcp port is open
a. use nmap -sS or nmap -sT
root@jephe ~/bin# nmap -sS -p 22 server1

Starting Nmap 5.00 ( http://nmap.org ) at 2013-06-18 21:00 EST
Interesting ports on server1:
PORT   STATE SERVICE
22/tcp open  ssh

Nmap done: 1 IP address (1 host up) scanned in 0.33 seconds

b. use nc -vz
[root@jephe ~]# nc -vz ovms04 22
Connection to ovms04 22 port [tcp/ssh] succeeded!

3. enable ipmi through CLI or GUI
ssh into DRAC/LOM/ILO NIC, run
-> set /SP/services/ipmi servicestate=enabled
Set ‘servicestate’ to ‘enabled’

4. usage example for ipmitool
[root@ovms03 ~]# ipmitool -H 172.17.2.11 -U root -P calvin chassis status
[root@ovms03 ~]# ipmitool -H 172.17.2.12 -U root -P changeme chassis status

5. useful commands for ipmi

 ipmitool -I lanplus -C 1 -H 172.17.1.17 -U root -P changeme sel list


 ipmitool  -H 172.17.1.12 -U root -P calvin sdr 

[root@ovmm01 /]# ipmitool chassis bootparam
bootparam get <param #>
bootparam set bootflag <flag>
  force_pxe   : Force PXE boot
  force_disk  : Force boot from default Hard-drive
  force_safe  : Force boot from default Hard-drive, request Safe Mode
  force_diag  : Force boot from Diagnostic Partition
  force_cdrom : Force boot from CD/DVD
  force_bios  : Force boot into BIOS Setup

Power On the Host
$ipmitool -H 1.2.3.4 -v -I lanplus -U username -P userpassword chassis power on

Power Off the Host
$ ipmitool -H 1.2.3.4 -v -I lanplus -U username -P userpassword chassis power off

Power Cycle the Host
$ ipmitool -H 1.2.3.4 -v -I lanplus -U username -P userpassword chassis power cycle

Shut Down the Host Gracefully
$ ipmitool -H 1.2.3.4 -v -I lanplus -U username -P userpassword chassis power soft

Fixing Oracle Linux 6.4 udev issues without reboot server


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

Objective: fix NIC issue, get Linux to set all NICs MAC properly
Problem: udev mass up, some interfaces are not recognized, got error message such as
'during bootup, got error such as 'Device p1p1 does not seem to be present, delaying initialization.'

Environment: RHEL6/OL6/CentOS 6

References:
http://onlinedocs.info/centos-detect-new-network-card/
http://blog.hacka.net/#post64




Steps:
1. make sure there's no HWADDR in all /etc/sysconfig/network-scripts/ifcfg-eth*

2. rm -f /etc/udev/rules.d/70-persistent-net.rules

3. Use OOB/ILO/iLOM etc to connect to server console in case lost connection

4. remove all NIC modules, such as rmmod igb; rmmod bnx2

5. modprobe igb;modprobe bnx2  ; to generate a new /etc/udev/rules.d/70-persistent-net.rules

6. testing HA(LACP mode 4/bonding mode 1) by using iptraf, OOB console, and ifconfig down eth* without physicall pulling out cables

Alternatively, manually force udev to create the rules file by running the following command:
# udevadm trigger --subsystem-match=net

How to change Oracle 11gR2 RAC Database Files Layout and Public IP/VIP/SCAN IP etc

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

Objective:  Change all database files location for 11.2.0.3 database and RAC public ip/vip/scan ip etc
Environment: Oracle 11.2.0.3 RAC, 2 nodes, 2 VIPs, 3 SCAN IPs

References:
http://www.oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php


Part I - Change Oracle 11gR2 RAC database files layout

1. check current database files info

sqlplus / as sysdba

sql> select name from v$datafile
sql> select name from v$tempfile;
sql> select member from v$logfile;
sql> archive log list;
sql> select dest_name, status, destination from v$archive_dest
sql> show parameter spfile;
sql> show parameter control;
sql> show parameter LOG_ARCHIVE_DEST_1;


sql> select * from database_properties


2. change all database files location - offline mode


---------control file and archive log file

sqlplus / as sysdba
sql> show parameter spfile;
sql> create pfile='/tmp/pfile' from spfile;
sql> shutdown immediate;

vi pfile to make changes for controlfiles and archive log location line
sqlplus / as sysdba
sql> startup nomount;
sql> create spfile='/u03/spfile/spfileDBID.ora' from pfile='/tmp/pfile';
sql> startup mount;
sql> alter database open;
sql> show parameter control_files;

---------online redo logs, data files, temp files

sqlplus / as sysdba
sql> shutdown immediate;
sql> startup mount;
sql> select name from v$datafile;
sql> select name from v$tempfile;
sql> select name from v$controlfile;
sql> select log_mode from v$database;
sql> select member from v$logfile;
sql> host
cp -va /u02/oradata/DBID/*.dbf /u03/oradata/DBID/
cp -va /u02/oradata/DBID/redo* /u04/oralog/DBID/
exit
sql> alter database rename file '/u02/oradata/DBID/file1.dbf' to '/u03/oradata/DBID/file1.dbf';
Note: do above for all output from 'select name from v$datafile;' and 'select name from v$tempfile;'

sql> alter database open;

3. change all database files location - online mode

------ control file
sql> show parameter control_files;

while database is running/online, run
sql>alter system set control_files = '/u09/control/DBNAME/control01.ctl', '/u09/control/DBNAME/control02.ctl','/u09/control/DBNAME/control03.ctl'

scope=spfile;

sql>shutdown immediate;

--------redo log

sqlplus / as sysdba
sql> select * from v$log;  # show thread and group numbers
sql> select a.group#,a.member,b.status,b.archived,bytes from v$logfile a, v$log b where a.group# = b.group# order by 1,2;
or
sql> select a.group#,a.member,b.status,b.archived,bytes/1024/1024 mbytes from v$logfile a, v$log b where a.group# = b.group# order by 1,2;
sql>

# check which thread for which node:
sql> select * from v$instance;
or
grep -i instance /tmp/pfile
or
strings spfilename | grep -i instance

add 2 more groups for each thread

ALTER DATABASE ADD LOGFILE [THREAD 1]
 GROUP 5 ('/u04/oralog/NRMAPS0/redo05.log') size 100M,
 GROUP 6 ('/u04/oralog/NRMAPS0/redo06.log') size 100M;


ALTER DATABASE ADD LOGFILE [THREAD 2]
 GROUP 7 ('/u04/oralog/NRMAPS0/redo07.log') size 100M,
 GROUP 8 ('/u04/oralog/NRMAPS0/redo08.log') size 100M;

alter database drop logfile group 1;

---------- data file
normal method:

If database is in archive log mode and the datafile is not system tablespace.
sql> ALTER DATABASE DATAFILE '/old/location' OFFLINE;
SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> RECOVER DATAFILE '/new/location';
SQL> ALTER DATABASE DATAFILE '/new/location' ONLINE;

You can online rename datafiles provided that datafile is not in SYSTEM tablespace.
sql> select name from v$tablespace;
sql> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES  WHERE TABLESPACE_NAME = 'USERS';
sql> alter tablespace users read only;
sql> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES where tablespace_name = 'USERS';
sql> host
use os utiliy to copy files from old location to new location
sql> alter tablespace users offline;
sql> alter database rename file 'old path' to 'new path';
sql> alter tablespace users online;
sql> alter tablespace users read write;
host
delete/move old files

rman method:

rman> report schema;
rman> copy datafile 3 to '/new/path/filename';
RMAN> SQL 'ALTER TABLESPACE xyz OFFLINE';

RMAN> SWITCH DATAFILE 3 TO COPY;
RMAN> RECOVER TABLESPACE xyz;


RMAN> SQL 'ALTER TABLESPACE soe ONLINE';
RMAN> HOST 'rm /old/path/filename';

rman> report schema;

-----------temp file

sql> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
sql> ALTER DATABASE TEMPFILE '/path/to/file' offline;
sql> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
sql> !cp -va /path/to/oldfile /path/to/newfile
sql> alter database rename file 'oldfile' to 'newfile';
sql> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
sql> ALTER DATABASE TEMPFILE '/path/to/file' online;
sql> !rm -fr oldfile

------------archive log location

sql> set line 32000
sql> select dest_name, status, destination from v$archive_dest
sql> show parameter LOG_ARCHIVE_DEST_1;
sql> archive log list;
sql> alter system set log_archive_dest_1='LOCATION=/u05/oraarch/DBID';
sql> alter system archive log current;

4. change spfile, password file and ocr/votedisk etc

-------spfile

cd $ORACLE_HOME/dbs
normally, it's /u01/app/oracle/product/11.2.0/dbhome_1/dbs
vi initDBID.ora
put spfile location there like this:

SPFILE='/u12/spfile/spfileyourDBSID.ora'

then you need to change spfile location in clusterware as follows in oracle or root user:
/u01/app/11.2.0/grid/bin/srvctl modify database -d DBSID -p /u12/spfile/spfileyourDBSID.ora

------password file
create symbolic link under $ORACLE_HOME/dbs/orapwinstanceID pointing to centralized location for accessing from all nodes.

e.g.
database name is RACDB, two instance ID are RACDB1 and RACDB2

more $ORACLE_HOME/dbs/initRACDB1.ora
SPFILE='/u12/spfile/spfileRACDB.ora'

ls -l $ORACLE_HOME/dbs/orapwRACDB01.ora
orapwRACDB01.ora -> /u12/passwdfile/orapwRACDB

on another node, it's:

more $ORACLE_HOME/dbs/initRACDB2.ora
SPFILE='/u12/spfile/spfileRACDB.ora'

ls -l $ORACLE_HOME/dbs/orapwRACDB02.ora
orapwRACDB02.ora -> /u12/passwdfile/orapwRACDB

-------------ocr
online change ocr location:

touch ocrdisk;chown root:oinstall ocrdisk; chmod 640 ocrdisk  # make new ocr same permission as the existing ones

/u01/app/11.2.0/grid/bin/ocrcheck
/u01/app/11.2.0/grid/bin/ocrconfig -add /u12/crscfg/ocrdisk
/u01/app/11.2.0/grid/bin/ocrconfig -add /u12/crscfg/ocrdisk_mirror
/u01/app/11.2.0/grid/bin/ocrconfig -delete /u02/crscfg/ocr

------------votedisk
/u01/app/11.2.0/grid/bin/crsctl query css votedisk
crsctl query crs activeversion
/u01/app/11.2.0/grid/bin/crsctl add css votedisk /u12/crscfg/votedisk2
/u01/app/11.2.0/grid/bin/crsctl add css votedisk /u12/crscfg/votedisk3
/u01/app/11.2.0/grid/bin/crsctl delete css votedisk /u02/crscfg/vdsk

Part II - Change Public/Private/VIP/SCANIP etc

---------public ip
Refer to How to Modify Public Network Information Including VIP in Oracle Clusterware [ ID 276434.1 ]

You can change public NIC IP first then change ip in clusterware, or you can keep old ip, change clusterware info first, then change OS IP.

change OS IP first
then check and make sure clusterware is running by running commands below:
/u01/app/11.2.0/grid/bin/olsnodes -s
/u01/app/11.2.0/grid/bin/crsctl check clusterware -all

check public ip in OCR and clusterware:
in clusterware_interconnects parameter: oifcfg iflist -p -n
in OCR: oifcfg getif
from sqlplus : SELECT INST_ID, NAME_KSXPIA, IP_KSXPIA, PICKED_KSXPIA FROM X$KSXPIA;
debug interconnect traffic:
sqlplus / as sysdba
sql> oradebug setmypid
sql> oradebug ipc
then try to find the trace file under USER_DUMP_DEST parameter directory.
sql> show parameter user_dump_dest;
from sqlplus again:
sql> select * from v$cluster_initerconnects;
sql> select * from v$configured_interconnects;


oifcfg setif -global eth0/10.1.1.0:public
oifcfg delif -global eth0/10.2.1.0

--check network after changing public ip:
srvctl config network
srvctl modify network -k 1 -S 10.1.1.0/255.255.255.0/eth0

--stop/start clusterware:
srvctl stop cluster -all
srvctl start cluster -all
crs_stat -t
crsctl stat res -t

---------------VIP
Refer to: How to Modify Private Network Information in Oracle clusterware [ ID 283684.1 ]

check current VIPs
srvctl config nodeapps -a
crsctl stat res ora.db01.vip -p

use 'ifconfig eth0 192.168.2.1 netmask 255.255.255.0 up' to config OS ip first, then run
oifcfg setif -global eth3/192.168.2.0:cluster_interconnect

---------------SCANIP
refer to How to Modify SCAN settings or SCAN listener port after installation [ ID 972500.1 ]

crsctl stat res ora.scan1.vip -p
srvctl stop scan -f
crs_stat # to check scan name
srvctl modify scan -n db01-scan1
If SCANIP is using DNS name, you don't have to change it, just change DNS config.
if it's using /etc/hosts, and above command 'crsctl stat res ora.scan1.vip -p' shows you are using name for scan ip, you can add dummy line for

/etc/hosts for that scan ip

e.g.
root@db01:~# tail -3 /etc/hosts
#SCAN
10.12.1.200 db-scan.domain.com db-scan
10.12.1.200 db-scan1.domain.com db-scan1   # manually add this line

then modify it to db-scan1 first,then modify it back to db-scan
srvctl modify scan -n db-scan1
srvctl modify scan -n db-scan
then check it 'crsctl stat res ora.scan1.vip -p' , confirm it's using new IP address for SCAN IP

srvctl config scan
srvctl config scan_listener
srvctl modify scan_listener -u

Part III - Other RAC commands and troubleshooting

-------------misscount,reboottime and disktimeout 
refer to Steps To Change CSS Misscount, Reboottime and Disktimeout [ID 284752.1]
and
CSS Timeout Computation in Oracle Clusterware [ID 294430.1]

With 11gR2, these settings can be changed online without taking any node down:

1) Execute crsctl as root to modify the misscount:
     $CRS_HOME/bin/crsctl set css misscount <n>    #### where <n> is the maximum private network latency in seconds
     $CRS_HOME/bin/crsctl set css reboottime <r> [-force]  #### (<r> is seconds)
     $CRS_HOME/bin/crsctl set css disktimeout <d> [-force] #### (<d> is seconds)
2) Execute crsctl as root to confirm the change:
     $CRS_HOME/bin/crsctl get css misscount
     $CRS_HOME/bin/crsctl get css reboottime
     $CRS_HOME/bin/crsctl get css disktimeout

---------crs_stat -p
to check all detail settings for crs

crsctl enable crs  # enable startup for all crs daemons
crsctl disable crs
crsctl query crs softwareversion
crsctl query cdrs activeversion
crsctl check crs
crs_stat -t
crs_stat -p
crs_stat -ls
crsctl debug log res "ora.sdrac01.vip:5"
ocrconfig -showbackup

---------use srvctl to manage database resource
srvctl status database -d racdb
srvctl stop database -d racdb
srvctl start database -d racdb
srvctl stop instance -d racdb -i racdb1
srvctl start instance -d racdb -i racdb1

Note: it's recommended to use srvctl utility to manage database, otherwise, sometimes, if you use sqlplus to stop it, then its status is still online

in clusterware. you can use crs_stop to stop it then.

---------troubleshooting
select * from v$diag_info;  # show all alert and diagostic locations info


Part IV - find Oracle GI PSU and Oracle database latest patches


Bug 14727347 - 11.2.0.3.5 Grid Infrastructure Patch Set Update (GI PSU) [ID 14727347.8]
Patch 14727347: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES DB PSU 11.2.0.3.5)



How to use snapmanager to implement Disaster Recovery for Oracle database


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

Objective: use snapmanager for Oracle to backup production database to DR and restore from there
Environment: Oracle standalone database 11gR2 11.2.0.3 running in Oracle VM guest backed by Oracle VM server 3.1.1., use centralized netapp storage to store binary and database files.


Steps:

1. create the coresponding volumes in DR netapp for each volume in primary database

ssh -n netapp 'df -Ah'
ssh -n netapp 'df -h'
ssh -n netapp 'vol status -v volname'
ssh -n netapp 'qtree status'
ssh -n netapp 'rdfile /etc/rc'
ssh -n netapp 'rdfile /etc/exports'
ssh -n netapp 'rdfile /etc/quotas'

vol create volname aggr1  100g
snap reserve volname 0
snap sched volname 0 0 0

For binary, create default volume without disabling snap shot and schedule.

2. configuring snapmirror

if snapmirror is uninitialized, need to be in restricted mode first
vol restrict volname
snapmirror initialize -S src:vol dst:vol
snapmirror resync -S srv:vol dst:vol
snapmirror update dstvol

break it:

snapmirror quiesce
snapmirror break dstvolname

3. recover database
startup dr VM

restore control file volume snapshot  # use snap restore ....
restore coresponding snapshot for datafile volume
rman target /
rman> restore controlfile from 'put any one of backup controlfile name from snapshot here'
rman> startup mount;
rman> recover database
rman> alter database open resetlogs


4. for smo postscript to work
enable http and https admin on dr storage so that postscript for resync snapmirror will work

on dr netapp:
disable automatic snapmirror (in /etc/snapmirror.conf, use five dash )
ssh into netapp
dr> options httpd.admin.enable on
dr> options httpd.enable on



use tcpdump -nn host 10.1.30.2


snapdrive config list
snapdrive config set root password

How to separate datafiles from all other Oracle database files for SnapManager vol-based fast restore


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

Objective: to make Oracle RAC database 11gR2 11.2.0.3 64bit to be eligible for fast restore
Environment: Oracle 11.2.0.3 RAC databases 2 node VMs sitting on an Oracle VM Server 3.1.1 hypervisor, using centralized Netapp storage for the following volumes: /u01 for binary, /u02 for datafile, password file, pfile, ocr/votedisk, controlfile, online and archived redo logs

Concept: In oder to be eligible for fast restore, we need to separte datafile volume, let /u02 is only for datafiles.


Steps:

1. pfile/spfile and password file
In Oracle RAC, we use symbolic link for shared spfile for RAC, in defualt pfile:

Assuming database name is racdb, 2 instance names are racdb1 and racdb2

su - oracle
cd $ORACLE_HOME/dbs
more initINSTANCENAME.ora

oracle@racdb1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs$ more initracdb1.ora 
SPFILE='/u06/spfile/spfileracdb.ora'

the password file which is symbolic link:

lrwxrwxrwx 1 oracle oinstall   28 Mar 14 16:57 orapwracdb1 -> /u06/passwdfile/orapwracdb


2. control files

sqlplus / as sysdba
sql> create pfile='/tmp/pfile' from spfile;
sql> exit

vi /tmp/pfile for control_files line

sql> shutdown immediate;  # for both database

copy required controlfiles from existing one to new one in new locations

sqlplus / as sysdba
sql> startup mount;
sql> create spfile='/u06/spfile/spfileracdb.ora' from pfile='/tmp/pfile'
sql> alter database open;

3. online redo log file

offline method:
sqlplus / as sysdba
sql> shutdown immediate;
sql> exit

cp required online redo log to new locations
sqlplus / as sysdba
sql> startup mount;
alter datbase rename file 'old location in old controlfile' to 'new location'
...
e.g.
alter database rename file '/u03/oralog/racdb/redo02.log' to '/u04/oralog/racdb/redo02.log';
alter database rename file '/u03/oralog/racdb/redo03.log' to '/u04/oralog/racdb/redo03.log';
sql> alter database open;
sql> select member from v$logfile;

online method:

ALTER DATABASE ADD LOGFILE THREAD 1
 GROUP 5 ('/u04/oralog/NRMAPS0/redo05.log') size 100M,
  GROUP 6 ('/u04/oralog/NRMAPS0/redo06.log') size 100M;


ALTER DATABASE ADD LOGFILE THREAD 2
 GROUP 7 ('/u04/oralog/NRMAPS0/redo07.log') size 100M,
  GROUP 8 ('/u04/oralog/NRMAPS0/redo08.log') size 100M;

alter system archive log current;
alter system switch logfile;
alter database drop logfile group 1;


4. ocr/votedisk
/u01/app/11.2.0/grid/ocrcheck
 crsctl query css votedisk

oracle@db02:/u02$ more /etc/oracle/ocr.loc
ocrconfig_loc=/u02/crscfg/ocr
local_only=FALSE

===OCR===

As root user on racdb1: run
touch /u06/crscfg/ocrdisk
chown root:oinstall /u06/crscfg/ocrdisk
chmod 640 /u06/crscfg/ocrdisk

/u01/app/11.2.0/grid/bin/ocrconfig -showbackup
/u01/app/11.2.0/grid/bin/ocrconfig -manualbackup

root@db01:/tmp# /u01/app/11.2.0/grid/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
/u01/app/11.2.0/grid/bin/ocrconfig –add /u06/crscfg/ocrdisk
/u01/app/11.2.0/grid/bin/ocrcheck
/u01/app/11.2.0/grid/bin/ocrconfig –delete /u02/crscfg/ocr
/u01/app/11.2.0/grid/bin/ocrcheck

===Votedisk===


root@db01:/tmp# /u01/app/11.2.0/grid/bin/crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   10ab40543f574f1abf3a5f3135fb3532 (/u02/crscfg/vdsk) []
Located 1 voting disk(s).


/u01/app/11.2.0/grid/bin/crsctl add css votedisk /u06/crscfg/votedisk
/u01/app/11.2.0/grid/bin/crsctl delete css votedisk /u02/crscfg/vdsk


Useful commands:
show parameter control;
show parameter pfile;

orapwd file=orapwTEST01 password=Welc0me1 ignorecase=n
select * from v$pwfile_users;

5. check CRS status

su - oracle
. grid_env
crs_stat -t
or
crsctl stat res -t






How to mount Oracle VM guest image and fsck it


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

Objective: modify and fix Oracle VM guest OS image file
Environment: Oracle VM server 3.1.1, Oracle Linux 5.8 64bit OS image


Steps:

1. mount system image as follows

[root@ovms03 db02]# file /OVS/Repositories/0004fb00000300009471703af52bc769/VirtualDisks/0004fb00001200006ff9f87b9279c6dd.img
/OVS/Repositories/0004fb00000300009471703af52bc760/VirtualDisks/0004fb00001200006ff9f87b9279c6dr.img: x86 boot sector; partition 1: ID=0x83, active, starthead 0, startsector 2048, 208896 sectors; partition 2: ID=0x83, starthead 0, startsector 210944, 24954880 sectors, code offset 0x48
Note: there are two partitions inside this image file.

[root@ovms03 db02]#  mount -o offset=$((512*210944)) /OVS/Repositories/0004fb00000300009471703af52bc760/VirtualDisks/0004fb00001200006ff9f87b9279c6dr.img /mnt/a
[root@ovms03 db02]#  mount -o offset=$((512*2048))
/OVS/Repositories/0004fb00000300009471703af52bc760/VirtualDisks/0004fb00001200006ff9f87b9279c6dr.img /mnt/a/boot/

2. chroot to it and modify accordingly
cd /mnt/a
chroot .
chkconfig --list
vi /etc/hosts
umount /mnt/a/boot
umount /mnt/a

3. fsck image file if required.

[root@ovms03 db02]# losetup /dev/loop0 /OVS/Repositories/0004fb00000300009471703af52bc769/VirtualDisks/0004fb00001200006ff9f87b9279c6dr.img -o $((512*210944))
[root@ovms03 goepsdb02]# fsck /dev/loop0
fsck 1.39 (29-May-2006)
e2fsck 1.39 (29-May-2006)
/: clean, 147679/2985984 files, 955713/3119360 blocks

[root@ovms03 goepsdb02]# losetup -d /dev/loop0



4. Other useful commands:
xm create vm.cfg
xm list
xm shutdown id
xm destroy id

Understanding and configuring Percona server 5.5 with high server performance


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

Objective: understanding and configuring Percona server 5.5 with high server performance
Environment: CentOS 6.3 64bit, Percona server 5.5


Part I: the most important parameters for tuning mysql server performance

•   innodb_buffer_pool_size
•   innodb_log_file_sized

1. for innodb_buffer_pool_size

size it properly:
-----------------

You probably should set it at least 80% of your total memory the server can see.
Actually, if your server is dedicated mysql server, without nightly backup job scheduled, you might want to set it in such way it only left 4G to

Linux OS.

Monitoring for usage:
--------------------

Innodb_buffer_pool_pages_total - The total size of the buffer pool, in pages.

Innodb_buffer_pool_pages_dirty/ Innodb_buffer_pool_pages_total  - the percentage of dirty pages

Innodb_buffer_pool_pages_data - The number of pages containing data (dirty or clean).

buffer pool page size:  - always 16k per page
---------------------
[root@db03 ~]# echo "20000*1024/16" |bc
1280000
[root@db03 ~]# grep innodb_buffer_pool_size /etc/my.cnf
innodb_buffer_pool_size=20000M
---------------------


When innodb flush pages to disk:
-----------------------------

a. LRU list to flush
If there's no free pages to hold the data read from disk, innodb will LRU list to flush least recently used page.

b. Fust list
used if the percentage of dirty pages reach innodb_max_dirty_pages_pct, innodb will write pages from buffer pool memory to disk.

c. checkpoint acitivity
When innodb log file circles, it must make sure the coresponding dirty pages have been flushed to disk already before overwritting log file content.

Refer to http://www.mysqlperformanceblog.com/2011/01/13/different-flavors-of-innodb-flushing/

Monitoring flush pages:

[root@db04 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Innodb_buffer_pool_pages
Innodb_buffer_pool_pages_data 919597
Innodb_buffer_pool_pages_dirty 6686
Innodb_buffer_pool_pages_flushed 115285384
Innodb_buffer_pool_pages_LRU_flushed 0
Innodb_buffer_pool_pages_free 4321306
Innodb_buffer_pool_pages_made_not_young 0
Innodb_buffer_pool_pages_made_young 11000
Innodb_buffer_pool_pages_misc 1976
Innodb_buffer_pool_pages_old 339440
Innodb_buffer_pool_pages_total 5242879


How innodb flush dirty pages to disk:
------------------------------------
Innodb uses background thread to merge writes together to make it as sequential write, so it can improve performance.
It's called lazy flush. Another flush is called 'furious flushing' which means it has to make sure dirty pages have been written to disk before

overwriting transaction log files. So large innodb log file size will improve performance because innodb doesn't have to write dirty page more often.


2. For innodb_log_file_size

Before writing to innodb log file, it uses innodb_log_buffer_size, range is 1M-8M, don't have to be very big unless you write a lot of huge blob records.
the log entries are not page-based. Innodb will write buffer content to log file when transaction commits.

How to size it properly, a few ways below:
-----------------------
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
1) use method mentioned in above blog
mysql> pager grep sequence
show engine innodb status\G

--------------

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\G select sleep(60)\Gshow engine innodb status\G
Log sequence number 9591605145344
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 9591617510628
1 row in set (0.00 sec)

mysql> select (9591617510628-9591605145344)/1024/1024 as MB_per_min;
+-------------+
| MB_per_min  |
+-------------+
| 11.79245377 |
+-------------+
1 row in set (0.00 sec)

mysql> select 11.79245377*60/2 as MB_per_hour for each file in group.
+------------------+
| MB_per_hour      |
+------------------+
| 353.773613100000 |
+------------------+
1 row in set (0.00 sec)

------------------

2) use innodb_os_log_written - The number of bytes written to the log file
You can monitor this parameter for 10 seconds during peak hour time, then get value X kb/s.
use x * 1800s(half an hour for one of two log files) for log file size.

3) monitor actual file size modified time, make it at least half an hour for each log file modification time.


Part II - Other useful parameters in /etc/my.cnf for innodb

1. innodb_max_dirty_pages_pct

This is an integer in the range from 0 to 99. The default value is 75. The main thread in InnoDB tries to write pages from the buffer pool so that the

percentage of dirty (not yet written) pages will not exceed this value.

2. innodb_flush_method=O_DIRECT  - eliminate Double Buffering

3. default-storage-engine=innodb

4. innodb_file_per_table

5. innodb_flush_log_at_trx_commit=2

Value Meaning
0 Write to the log and flush to disk once per second
1 Write to the log and flush to disk at each commit
2 Write to the log at each commit, but flush to disk only once per second

Note that if you do not set the value to 1, InnoDB does not guarantee ACID prop-
erties; up to about a second’s worth of the most recent transactions may be lost if a
crash occurs.

6. max_connections=512

7. max_connect_errors
# default it's 10 only, we will get error like Host X is blocked because of many connection errors; unblock with mysqladmin flush-hosts
max_connect_errors=5000
# end

Part III - monitoring mysql server performance

1. aborted_client / abort_connects

2. bytes_sent/bytes_received , the number of bytes received/sent from/to all client

3. connections , the number of connection attempts (success or not ) to mysql server

4. created_tmp_disk_tables/created_tmp_tables , created on-disk temporary tables / the total number of created temporary tables

5. innodb_log_waits - The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.
mysql -uroot -ppassword -e 'show global status' | grep -i  Innodb_log_waits


6.  Innodb_os_log_written

The number of bytes written to the log file.

7.  Innodb_rows_deleted

The number of rows deleted from InnoDB tables.

 Innodb_rows_inserted

The number of rows inserted into InnoDB tables.

 Innodb_rows_read

The number of rows read from InnoDB tables.

 Innodb_rows_updated

The number of rows updated in InnoDB tables.

8. Max_used_connections - The maximum number of connections that have been in use simultaneously since the server started.

[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Max_used_connections
Max_used_connections 353

9.  Open_tables

The number of tables that are open.

10.  Select_full_join

The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your

tables.

11. Select_range_check - The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the

indexes of your tables.

[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Select_range_check
Select_range_check 1453


12. slow_queries
[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Slow_queries    
Slow_queries 38180440

13. [root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Table_locks_waited
Table_locks_waited 304

The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance

problems, you should first optimize your queries, and then either split your table or tables or use replication.


14. thread_connected/thread_running/max_used_connection
 Max_used_connections

The maximum number of connections that have been in use simultaneously since the server started.

 Threads_running

The number of threads that are not sleeping.

 Threads_connected

The number of currently open connections.





How to use hardware RAID10 to clone Linux between 2 Dell R720 servers


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

Environment: 2 Dell R720 (db01 and db02), need to clone db01 to db02 and wipe off everything on db02 hard disks which was used for other purpose.
db01 has 6 SAS 300G 15k rpm hard disk and configured as RAID10.
IP addresses for 2 servers: db1: 192.168.0.1 db2: 192.168.0.2

Objective: to clone server db01 to db02 and change ip address.



Steps:

1. Cloning hard disk

While db01 is running, remove hard disk ID 1,3,5 , insert any 3 hard disks from db02 while db02 is power off, then it will automatically start rebuilding raid10, you can observe it from Dell Openmanage page https://192.168.0.1:1311 for db01.

Put hard disk ID 1,3,5 from db01 into slot 0,2,4 on db05 which is the top row of disk slots, keep slot 1,3,4 empty first, then power on db02.

On boot up screen, it will say all original disk has been removed, press F to import foreign config.
You have to press it quickly otherwise, it will continue and fail to bootup Linux due to no boot up device found.

Press F so it can continue to boot Linux OS which is same as the one on db01

2. Configure network on db02 after OS boot up

As the hard disk comes from db01, after OS boots up, everything is same as db01, you need to change the following:

a. hostname. 
run commands below:
# hostname hmspzdb05
# vi /etc/sysconfig/network to change hostname to hmspzdb05
# service network restart


b. ip address
# cd /etc/sysconfig/network-scripts
# dmesg | grep eth  # to get all network NICs mac address and network card module name (igb)

[root@db01 ~]# dmesg | grep eth
igb 0000:01:00.0: eth0: (PCIe:5.0Gb/s:Width x4) bc:20:5b:ed:95:28
igb 0000:01:00.0: eth0: PBA No: G10565-011
igb 0000:01:00.1: eth1: (PCIe:5.0Gb/s:Width x4) bc:20:5b:ed:95:29
igb 0000:01:00.1: eth1: PBA No: G10565-011
igb 0000:01:00.2: eth2: (PCIe:5.0Gb/s:Width x4) bc:20:5b:ed:95:2a
igb 0000:01:00.2: eth2: PBA No: G10565-011
igb 0000:01:00.3: eth3: (PCIe:5.0Gb/s:Width x4) bc:20:5b:ed:95:2b
igb 0000:01:00.3: eth3: PBA No: G10565-011
igb 0000:44:00.0: eth4: (PCIe:5.0Gb/s:Width x4) a0:26:9f:01:c8:74
igb 0000:44:00.0: eth4: PBA No: G13158-000
igb 0000:44:00.1: eth5: (PCIe:5.0Gb/s:Width x4) a0:26:9f:01:c8:75
igb 0000:44:00.1: eth5: PBA No: G13158-000
igb 0000:44:00.2: eth6: (PCIe:5.0Gb/s:Width x4) a0:26:9f:01:c8:76
igb 0000:44:00.2: eth6: PBA No: G13158-000
igb 0000:44:00.3: eth7: (PCIe:5.0Gb/s:Width x4) a0:26:9f:01:c8:77
igb 0000:44:00.3: eth7: PBA No: G13158-000

# vi ifcfg-em1/2/3/4 and vi ifcfg-p2p1/2/3/4 (if any) to modify mac address and ip address line

c. service restart network to take affect without reboot

Note: you don't have to reboot to make networking work.
# rmmod igb
# modprobe igb
Note: it will update /etc/udev/rules.d/70-persistent-net.rules
# service network restart

3.  rebuild raid 10 on db02

You can now insert the remaining 3 hard disks into slot ID 1,3,5 on db02.

It won't rebuild RAID10 automatically by itself after insert.

Now check Open manage page at https://192.168.0.2:1311 (new ip address for db02 after change)

all the hard disks in slot ID 1,3,5 shows 'foreign' disks.

You need to clear 'foreign' status by doing the following:  refer to http://en.community.dell.com/support-forums/servers/f/906/t/19299553.aspx


You will need to clear the foreign configuration, reconfigure the drive as hotspare and the rebuild will start.

To clear foreign configuration:  select the controller on OpenManage, go to the "Information/Configuration" tab, on "Controller Tasks" select "Foreign Configiration Operations", click "Execute".  On the next page, click "Clear"


After clearing 'foreign', the status becomes 'ready', it's still not rebuilding RAID10.

To reconfigure as hot spare: go to the "Physical Disk" view, on the "Available Task" for that drive select "Assign Global Hot Spare". then it will start rebuilding RAID10.

4. reboot db01 and db02 to confirm it's okay.

How to startup OL6 as PVM under Oracle VM 2.X


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

Problem:  Oracle VM 2.2 cannot startup default OL 6.3 as PVM (Oracle Linux 6.3 VM is created from downloaded assembly .ova file), although it can startup as HVM.


Error message:
After xm create vm1.cfg, it immediately return error like this:

 Error: Boot loader didn't return any data

Reason:  Oracle VM 2.2 cannot read ext4 /boot partition to find kernel to boot up as PVM
Soluton:  bootup this OL 6.3 on Oracle VM 3 hypervisor,  then convert /boot to ext3, then copy system.img to Oracle VM 2 to use.

Steps:
1. check image file 
[root@dr01 xen]# file vm1.img
vm1.img: x86 boot sector, GRand Unified Bootloader (0.94); partition 1: ID=0x83, active, starthead 0, startsector 2048, 208896 sectors; partition 2: ID=0x83, starthead 0, startsector 210944, 20760576 sectors; partition 3: ID=0x82, starthead 63, startsector 20971520, 4194304 sectors, code offset 0x48

[root@dr01 xen]# mount dr01.img -o offset=$((512*2048)) /tmp/a
mount: unknown filesystem type 'ext4'

note: /boot partition is ext4 which is not recongized by Oracle VM server 2.2

2. boot up image on OVM3.1.1 then convert /boot from ext4 to ext3

#boot up OL 6.3 64bit VM as PVM on Oracle VM server 3.1.1
cp /boot /boot2 -va
umount /boot
mkfs -t ext3 /dev/xvda1
vi /etc/fstab to change /boot partition type from ext4 to ext3
mount /boot
cp /boot2/* /boot/ -va
sync
poweroff

3. boot up new image with 'xm create vm1.cfg' as follows

[root@dr01 xen]# more vm1.cfg
vif = ['type=netfront,bridge=fe', 'type=netfront,bridge=stor']
OVM_simple_name = 'vm1'
disk = ['file:/xen/vm1.img,xvda,w']
uuid = '0004fb00-0006-0010-07aa-15a5c3b8c419'
on_reboot = 'restart'
cpu_weight = 27500
memory = 4096
cpu_cap = 0
maxvcpus = 4
OVM_high_availability = False
maxmem = 4096
OVM_description = ''
on_poweroff = 'destroy'
on_crash = 'restart'
bootloader = '/usr/bin/pygrub'
name = 'vm1'
guest_os_type = 'default'
vfb = ['type=vnc,vncunused=1,vnclisten=0.0.0.0,keymap=en-us']
vcpus = 4
OVM_os_type = 'None'
OVM_cpu_compat_group = ''
OVM_domain_type = 'xen_pvm'

4. file system corrupted for / sometimes during reboot and give error message like
xvda: barrier or flush: disabled

Please refer to  http://lzone.de/blkfront+barrier+empty+write+xvda+op+failed 
Ubuntu 12.04 on Xen: blkfront: barrier: empty write xvda op failed
Solution: to add barrier=0 to mount options for all your local file system.


How to make netapp Oracle database snapshot copy crash-consistent


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

Objective: understanding Netapp point-in-time snapshot Oracle backup without putting in the hot backup mode


Crash-consistent snapshot copies should only be considered under special circumstances where requirements restrict the use of standard backup methods (such as rman or hot backup mode)

Oracle backup overview:
----------------------
physical backup and logical backup

physical backup can be classified as consistent backup or inconsistent backup

consistent backup means controlfile and data file are checkpointed with same SCN, only possible when database is cleanly shut down, no matter it's in nonarchivelog or archivelog mode

Besides the standard 3 methods for backup: cold/offline backup, rman backup and online/hot backup(user-managed backup), Oracle recently certify the third party snapshot copy technology as one of options of backup/recovery as long as it's crash consistent

In the past, Oracle did not support or recommend the use of a snapshot copy created of an online active
database without the database or tablespaces being put in backup mode. The risk was thought to be
the danger of mixing old archive logs with current archive logs, which can lead to data corruption or
potentially destroy the production database.

According to MOS note 604683.1, the snapshot of an online database not in backup mode can be
deemed valid and supported if and only if all of the following requirements are strictly satisfied:
•  Oracle’s recommended restore and recovery operations are followed. 
•  Database is crash consistent at the point of the snapshot. 
•  Write ordering is preserved for each file within a snapshot. 


Oracle recovery overview:
------------------------
instance recovery and media recovery
instance recovery is automatic done by Oracle itself, it requires redo log file only.
Media recovery requires archived redo log.
Media recovery has complete recovery and incomplete recovery


An incomplete recovery of the whole database is usually required in the following situations:
•  Data loss caused by user errors
•  Missing archived redo log, which prevents complete recovery
•  Physical loss or corruption of online redo logs
•  No access to current control file

When performing incomplete recovery, the types of media recovery are available.


  • Time-based recovery  Recovers the data up to a specified point in time. 
  • Cancel-based recovery  Recovers until you issue the CANCEL statement (not available when using Recovery Manager). 
  • Change-based recovery  Recovers until the specified SCN. 
  • Log sequence recovery  Recovers until the specified log sequence number (only available when using Recovery Manager). 

What's the crash consistent?
------------------------------
It's point-in-time(PIT) image of Oracle database, looks like it crashed due to power outage, instance crash or shutdown abort etc, it requires instance recovery after restart database, not media recovery. Netapp snapshot generate Point-In-time image for database.


How to make snapshot crash-consistent?
---------------------------------------
1. all databqase files(controlfile, datafile, online redo log) are in single volume, then snapshot will generate crash-consistent image.
Note: Not require archived logs to be in the same volume.

If a database has all of its files (control files, data files, online redo logs, and archived logs) contained
within a single NetApp volume, then the task is straightforward. A Snapshot copy of that single volume
will provide a crash-consistent copy.

2. use crash consistent group by snapmanager/snapdrive etc if database cross different volumes
e.g. data volume and log volume, data captured by snapshot for data volume must exist in log volume first because Oracle always makes sure it writes to redo log first before writing associated data buffer cache to data file.


Starting from SnapDrive for unix 2.2, SnapDrive supports the feature of consistency groups provided
by Data ONTAP (beginning with version 7.2 and higher). This feature is necessary for creating a
consistent Snapshot copy across multiple controller/volumes.

In an environment where all participating controllers support consistency groups, SnapDrive will use a
Data ONTAP consistency group as the preferred (default) method to capture multicontroller/volume
Snapshot copies.

SnapDrive can simplify the creation of a consistency group Snapshot copy when there are
multiple file systems.

snapdrive snap create -fs /u01/oradata/prod /u02/oradata/prod -snapname snap_prod_cg 


a. POINT-IN-TIME COPY OF THE DATABASE 

After the database is opened, no future redo logs beyond this snapshot
can be applied.

Open resetlogs operation is recommended to avoid potential mixing of existing
archive logs and new archive logs. and start a new incarnation and log ID:

1. SHUTDOWN IMMEDIATE 
2.  STARTUP MOUNT 
3.  RECOVER DATABASE UNTIL CANCEL 
4.  ALTER DATABASE OPEN RESETLOGS; 

b. FULL DATABASE RECOVERY WITH ZERO DATA LOSS 

Restore the snapshot of only the data files. Do not overwrite the current control files, current redo
logs, and current archived logs.

run commands below to fully recover database by applying archived and online redo logs
1. recover automatic database;
2. alter database open;

c. point-in-time(PIT) database recovery
PIT requires the presence of current controlfile, current online redo logs and archived logs.

only restore data files and run the following commands:
1. startup mount

Identify the minimum SCN we have to recover to by script @scandatafile.sql

SQL> @scandatafile  
File 1 absolute fuzzy scn = 861391  
File 2 absolute fuzzy scn = 0  
File 3 absolute fuzzy scn = 0  
File 4 absolute fuzzy scn = 0  
Minimum PITR SCN = 861391  

PL/SQL procedure successfully completed. 


scandatafiles.sql 
# scans all files and update file headers with meta information  
# depending on number and sizes of files, the scandatafile procedure can be 
a  
# time consuming operation.  
# create a script, “scandatafile”, with the following content 

spool scandatafile.sql  
set serveroutput on  
declare  
 scn number(12) := 0;  
 scnmax number(12) := 0;  
begin  
 for f in (select * from v$datafile) loop  
 scn := dbms_backup_restore.scandatafile(f.file#);  
 dbms_output.put_line('File ' || f.file# ||' absolute fuzzy scn = ' || 
scn);  
 if scn > scnmax then scnmax := scn; end if;  
 end loop;  

 dbms_output.put_line('Minimum PITR SCN = ' || scnmax);  
end; 


If the minimum PITR SCN is zero, then database is not required for further recovery, it can to opened now.
if it's no zero, database must be recovered to at least that SCN and onwards.

2. RECOVER AUTOMATIC DATABASE UNTIL CHANGE [Minimum PITR SCN or higher]  
or 
ALTER DATABASE RECOVER DATABASE UNTIL CHANGE [Minimum PITR SCN or higher] 

3. ALTER DATABASE OPEN RESETLOGS 

References:

--------------
1. Using Crash-Consistent Snapshot Copies as Valid Oracle Backups - http://media.netapp.com/documents/tr-3858.pdf
2. MOS Supported Backup, Restore and Recovery Operations using Third Party Snapshot Technologies [ID 604683.1]