Recently I took over a HPUX running oracle 10g, when checking the alert log, I saw a few ORA-27300, ORA-27301, ORA-27301 errors, /var/log/messages also shows this:
Deferred swap reservation failure pid: 12345.
Apparently system doesn't have enough swap space for oracle.
$ swapinfo -am
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 6656 195 6461 3% 0 - 1 /dev/vg00/lvol2
reserve - 3134 -3134
memory 4536 1109 3427 24%
swapinfo shows that system has 4.5G physical memory, swap space is 6.6G, a bit smaller but quite close to oracle recommanded 4.5X1.5G
further check found that system hit ORA-27300 daily between 18:00 and 20:00, there must be some job running during this period causing the excessive swap usage.
AWR report shows this sql suspicous:
SELECT table_a.column_1, table_a.column_2, table_a.column_3
BULK COLLECT INTO
col_1, col_2, col_3
FROM table_a, table_b, table_c
WHERE table_a.created_date >= to_date(:1,'dd/mm/yyyy:hh24:mi')
AND table_a.created_date <= to_date(:2,'dd/mm/yyyy:hh24:mi');
two issues with this sql:
1. cartition join table_b table_c, yet no columns from table_b and table_c are selected columns
the cartition join of table_b and table_c produces about 2 billion rows,
so if there is 1 row in table_a match the WHERE condition, sql will try to load the same row 2 billion times in memory.
2. use BULK COLLECT INTO directly
according to oracle, BULK COLLECT should always be used with LIMIT
It turns out that the sql is part of a procedure, which runs daily at 18:00.
Since the job has been failing for months, I just disabled it, informed development team to rectify it.
So far two weeks passed, both server and database are working fine, no more ORA-27300 or "Deferred swap reservation failure" appears.
reference:
http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php
http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html
Thursday 6 December 2012
Wednesday 7 November 2012
How to get the process listening on certain port
At times we are asked: "what program is listening on port XX?"
In Redhat, we can easily get this using lsof. let's say if we want to know which program is listening on port 80.
[root@localhost ~]# lsof -i :80
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
httpd 2047 root 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2049 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2050 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2051 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2052 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2053 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2054 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2055 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2056 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
From the output, we know that httpd is listening on port 80.
Another way to get the process listening on certain port is using netstat.
[root@localhost ~]# netstat -anp | grep 80
tcp 0 0 :::80 :::* LISTEN 2009/httpd
[root@localhost ~]# ps -ef | awk '$2 == 2009'
root 2009 1 0 23:21 ? 00:00:00 /usr/sbin/httpd
[root@localhost ~]#
In AIX, most of the time it has no lsof installed, and the netstat is also different from Redhat, but we still can get our question answered.
$ netstat -Aan | grep '.22 ' | grep LISTEN
f1000e0001382bb8 tcp4 0 0 *.22 *.* LISTEN
$ rmsock f1000e0001382bb8 tcpcb
The socket 0xf1000e0001382808 is being held by proccess 3670142 (sshd).
$ ps -ef | grep 3670142
root 3670142 3014676 0 Sep 06 - 0:00 /usr/sbin/sshd
Please note rmsock will not remove the socket, you can confirm this by checking the content of /var
reference: http://unix.ittoolbox.com/groups/technical-functional/ibm-aix-l/determine-which-process-is-listening-on-a-port-without-using-lsof-on-aix-1468555
In Redhat, we can easily get this using lsof. let's say if we want to know which program is listening on port 80.
[root@localhost ~]# lsof -i :80
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
httpd 2047 root 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2049 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2050 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2051 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2052 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2053 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2054 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2055 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
httpd 2056 apache 4u IPv6 12636 0t0 TCP *:http (LISTEN)
From the output, we know that httpd is listening on port 80.
Another way to get the process listening on certain port is using netstat.
[root@localhost ~]# netstat -anp | grep 80
tcp 0 0 :::80 :::* LISTEN 2009/httpd
[root@localhost ~]# ps -ef | awk '$2 == 2009'
root 2009 1 0 23:21 ? 00:00:00 /usr/sbin/httpd
[root@localhost ~]#
In AIX, most of the time it has no lsof installed, and the netstat is also different from Redhat, but we still can get our question answered.
$ netstat -Aan | grep '.22 ' | grep LISTEN
f1000e0001382bb8 tcp4 0 0 *.22 *.* LISTEN
$ rmsock f1000e0001382bb8 tcpcb
The socket 0xf1000e0001382808 is being held by proccess 3670142 (sshd).
$ ps -ef | grep 3670142
root 3670142 3014676 0 Sep 06 - 0:00 /usr/sbin/sshd
Please note rmsock will not remove the socket, you can confirm this by checking the content of /var
reference: http://unix.ittoolbox.com/groups/technical-functional/ibm-aix-l/determine-which-process-is-listening-on-a-port-without-using-lsof-on-aix-1468555
Wednesday 17 October 2012
Oracle silent installation using response file
Earlier in Using Putty + Xming for X forwarding I mentioned how to setup X forwarding for oracle installation. Actually I have been using response file on most of the systems I configured.
The advantages of silent install using response file are:
1. All the installations are identical, so the setup is consistent across different environment.
2. No X required during the installation, just the normal ssh to server is sufficient.
3. Installation is simpler, just trigger the command, no need to click on different installing screen.
The most important part of silent install is the response file. Oracle installation binary contains a folder called response. From there we can modify the db installation rsp file. Many lines in the file are comments, so the file is self-explanatory. In 10g response file, the most important parameters to configure are:
runInstaller -responseFile /full/path/to/myInstall.rsp -silent
The last few lines of the installation output, we will get some instructions run root.sh as root user. After run root.sh following the instruction, we are done with the installation.
The advantages of silent install using response file are:
1. All the installations are identical, so the setup is consistent across different environment.
2. No X required during the installation, just the normal ssh to server is sufficient.
3. Installation is simpler, just trigger the command, no need to click on different installing screen.
The most important part of silent install is the response file. Oracle installation binary contains a folder called response. From there we can modify the db installation rsp file. Many lines in the file are comments, so the file is self-explanatory. In 10g response file, the most important parameters to configure are:
UNIX_GROUP_NAME, ORACLE_HOME, ORACLE_HOME_NAME, INSTALL_TYPE
Once the response file is ready, we can install oracle binary by calling OUI
runInstaller -responseFile /full/path/to/myInstall.rsp -silent
The last few lines of the installation output, we will get some instructions run root.sh as root user. After run root.sh following the instruction, we are done with the installation.
Friday 14 September 2012
Using Putty + Xming for X forwarding
A few years back, when I remotely installed Oracle in Solaris the first time, I thought exporting X display was the only option I have. I was told to use Xmanager for X forwarding, but Xmanager is not free. After searching around, I found Xming on sourceforge, so I wanted to give Xming a try.
Here is how I export X display to my local PC.
1. Software needed: Putty, Xming
Just search it, you can easily download them, you need to install Xming after downloading.
2. Start Xming
After starting Xming, you will see a small icon at the right bottom corner of your windows screen. Mouse over the icon, it will show "Xming Server:0.0"
3. Launch Putty to connect to remote server, after filling in the "Host Name" and "Port", click "Connection" -> "SSH" -> "X11", tick the checkbox before "Enable X11 forwarding", then click "Open" button.
4. You are connected to remote server with X11 forwarding, start any X application to test the settings are ok.
You can see that we run xterm from putty, will launch a xterm window on the local PC.
Reference: http://courses.cms.caltech.edu/cs11/misc/xwindows.html
Here is how I export X display to my local PC.
1. Software needed: Putty, Xming
Just search it, you can easily download them, you need to install Xming after downloading.
2. Start Xming
After starting Xming, you will see a small icon at the right bottom corner of your windows screen. Mouse over the icon, it will show "Xming Server:0.0"
3. Launch Putty to connect to remote server, after filling in the "Host Name" and "Port", click "Connection" -> "SSH" -> "X11", tick the checkbox before "Enable X11 forwarding", then click "Open" button.
4. You are connected to remote server with X11 forwarding, start any X application to test the settings are ok.
You can see that we run xterm from putty, will launch a xterm window on the local PC.
Reference: http://courses.cms.caltech.edu/cs11/misc/xwindows.html
Saturday 25 August 2012
How to setup NFS in RedHat and Solaris
NFS stands for Network File System, we can use it to share files between different computers.
In Redhat Linux, to share NFS, we need to configure /etc/exports, the format of exports is:
If we want to share directory /data/forhost1 to host1, we can configure my /etc/dfs/dfstab like this:
After we share /data/forhost1, we can mount it on host1:
if host1 is Redhat Linux
To make the nfs mount permanent, we can configure /etc/fstab or /etc/vfstab to mount nfs on system start.
In Redhat Linux, to share NFS, we need to configure /etc/exports, the format of exports is:
For example, if we want to share directory /data/forhost1 to host1, we can configure my /etc/exports like this:<directory to share> <host>(<option>)
if we only want to grant read permission to /data/forhost1, we can add the ro option, ro stands for read-only/data/forhost1 host1
Solaris uses a different configuration file /etc/dfs/dfstab for NFS./data/forhost1 host1(ro)
If we want to share directory /data/forhost1 to host1, we can configure my /etc/dfs/dfstab like this:
For both RedHat Linux and Solaris, we need to restart the nfs service to make our configuration take effect, this can be done by using service or svcadm.share -F nfs -o ro=host1 -d "Readonly" /data/forhost1
After we share /data/forhost1, we can mount it on host1:
if host1 is Redhat Linux
if host1 is Solaris:mount -t nfs server:/data/forhost1 /mnt/mynfs
mount -F nfs server:/data/forhost1 /mnt/mynfs
To make the nfs mount permanent, we can configure /etc/fstab or /etc/vfstab to mount nfs on system start.
Wednesday 13 June 2012
How to get hardware information in Linux
We often need to get the hardware information, In Redhat Linux, this can be easily done by using the dmidecode.
To get the bios information:
By using dmidecode, we can get many information about the system, for details, please refer to the man page
To get the bios information
Please note that we can only run dmidecode as super user, whereas we can run omreport as normal user.
To get the bios information:
In dmidecode, 0 is the type code for BIOS.# dmidecode -t 0
By using dmidecode, we can get many information about the system, for details, please refer to the man page
All the Linux systems I have been managed are Redhat running on Dell servers, so I am also using Dell OpenManage to get the system hardware information.# man dmidecode
To get the bios information
There are also many other options we can use in Dell OpenManage, for details, please refer to: http://support.dell.com/support/edocs/software/svradmin/5.1/en/omss_ug/html/cli.html#1220531$ omreport chassis bios
Please note that we can only run dmidecode as super user, whereas we can run omreport as normal user.
Thursday 7 June 2012
Linux: how to recover root password
In Linux, if you forget the root password, you can recover it in this way:
- Reboot the machine, at grub menu, edit the boot entry for Linux
- press 'e' to to edit the entry,
append " single" at the end of the kernel line
alternatively, you can also append " init=/bin/sh" at the end of the kernel line
- press 'b' to boot Linux into single user mode
- now you will be able to log in the system without entering password
- type 'passwd' to reset root password
Wednesday 23 May 2012
Set up password-less SSH login using ssh-keygen and ssh-copy-id
Logging in remote systems without password is very useful in system administration. It can be used for executing command on remote systems, copying files to other systems using rsync/rdist.
For the past few years, I have been manually setting up the key trust between systems. Here are the steps I followed.
Suppose I need to login to localserver from remoteserver using account scripter without password.
1. generate public-private key pair on localserver
2. copy scripter's public key to remoteserver
4. on localserver the directory and files are auto-generated, so the permissions are correct, on remoteserver, as we manually created the directories, we need to make the permissions are correct.
After all these steps, we can login from localserver to remoteserver without entering password:
The steps are quite easy, but can it be easier?
Yes! recently I learned the script ssh-copy-id from a forum post, to set up key trust from localserver to remoteserver using ssh-keygen and ssh-copy-id
1. generate public-private key pair on localserver
For the past few years, I have been manually setting up the key trust between systems. Here are the steps I followed.
Suppose I need to login to localserver from remoteserver using account scripter without password.
1. generate public-private key pair on localserver
you can use either dsa or rsa.$ ssh-keygen -t dsa
2. copy scripter's public key to remoteserver
3. on remoteserver, append the public key copied in step 2 to ~scripter/.ssh/authorized_keys$ scp id_dsa.pub remoteserver:
if ~scripter/.ssh doesn't exist, manually create it$ cat id_dsa.pub >> ~scripter/.ssh/authorized_keys
4. on localserver the directory and files are auto-generated, so the permissions are correct, on remoteserver, as we manually created the directories, we need to make the permissions are correct.
$ chmod 700 ~scripter/.ssh$ chmod 600 ~scripter/.ssh/authorized_keys
After all these steps, we can login from localserver to remoteserver without entering password:
$ ssh remoteserver hostnameremoteserver
The steps are quite easy, but can it be easier?
Yes! recently I learned the script ssh-copy-id from a forum post, to set up key trust from localserver to remoteserver using ssh-keygen and ssh-copy-id
1. generate public-private key pair on localserver
2. copy the public key from localserver to remoteserver, and set up the key trust$ ssh-keygen -t dsa
After you entering the password, everything is set up for you, you can log in remotely without password$ ssh-copy-id -i ~scripter/.ssh/id_dsa.pub \
scripter@remoteserver
ssh-copy-id saves us all the troubles of creating files and setting up the proper permission :)$ ssh remoteserver hostnameremoteserver
Friday 18 May 2012
Use telnet to test SMTP server
Besides testing HTTP server, telnet can also be used to test SMTP server
below is the testing I did on my linux box
in /var/spool/mail/tom will see the email we just sent
please note the lines starting with numbers are the output returned by SMTP server, the numbers are the status code, search "smtp status code" for more details.
"ehlo", "mail from:", "rcpt to:", "data", "quit" are the commands sent to SMTP server.
below is the testing I did on my linux box
$ telnet localhost 25
220 redhat.localdomain ESMTP Postfix
ehlo abc.com
250-redhat.localdomain
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN
mail from: scripter@linux.com
250 2.1.0 OK
rcpt to: nonexsit
550 5.1.1 <nonexist>: Recipient address rejected: User unknown in local recipient table
rcpt to:tom
250 2.1.5 OK
data
354 End data with <CR><LF>.<CR><LF>
Subject: hi this is subject
this is email body
more chars
.
250 2.0.0 OK: queued as 25AE12AD1
quit
221 2.0.0 Bye
in /var/spool/mail/tom will see the email we just sent
please note the lines starting with numbers are the output returned by SMTP server, the numbers are the status code, search "smtp status code" for more details.
"ehlo", "mail from:", "rcpt to:", "data", "quit" are the commands sent to SMTP server.
Tuesday 8 May 2012
Use telnet to test HTTP server
A few year ago, I managed a few hundred of servers, they provide HTTP service using Apache.
I often need to test the HTTP service on these servers.
I often need to test the HTTP service on these servers.
I used telnet for my testing
C:\>telnet www.redhat.com 80
GET /robots.txt HTTP/1.1
host: www.redhat.comHTTP/1.1 200 OK
Server: Apache
X-Powered-By: Servlet 2.5; JBoss-5.0/JBossWeb-2.1
Last-Modified: Fri, 27 Jan 2012 23:35:31 GMT
Accept-Ranges: bytes
Content-Length: 484
Content-Type: text/plain; charset=UTF-8
Cache-Control: no-cache
Date: Tue, 08 May 2012 12:20:11 GMT
Connection: keep-alive
User-agent: *Disallow: /apps/download/results.html Disallow: /apps/search/results.html Disallow: /apps/user/ Disallow: /apps/user/* Disallow: /WebX/* Disallow: /webx/* Disallow: /WebX/* Disallow: /test_community/ Disallow: /test_community/* Disallow: /network Disallow: /archives/redhat-install-list/2000-September/msg00588.html Disallow: /search Disallow: /search* Disallow: /mailman/listinfo/ceylon-core Disallow: /mailman/private/ceylon-core/ Disallow: /mailman/admin/ceylon-core
Subscribe to:
Posts (Atom)