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
Thinks you should know about sudo command
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
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
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
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
$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;
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
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]
Posts
