ORA-27300 and swap error

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

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/adm/ras/rmsock.log.

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

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:

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.

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

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:

<directory to share>   <host>(<option>)

For example, if we want to share directory /data/forhost1 to host1, we can configure my /etc/exports like this:

/data/forhost1      host1

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(ro)

Solaris uses a different configuration file /etc/dfs/dfstab for NFS.
If we want to share directory /data/forhost1 to host1, we can configure my /etc/dfs/dfstab like this:

share -F nfs -o ro=host1 -d "Readonly" /data/forhost1

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.

After we share /data/forhost1, we can mount it on host1:
if host1 is Redhat Linux

mount -t nfs server:/data/forhost1 /mnt/mynfs

if host1 is Solaris:

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.

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:

# dmidecode -t 0

In dmidecode, 0 is the type code for BIOS.

By using dmidecode, we can get many information about the system, for details, please refer to the man page

# man dmidecode

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.
To get the bios information

$ omreport chassis bios

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

Please note that we can only run dmidecode as super user, whereas we can run omreport as normal user.

Linux: how to recover root password

In Linux, if you forget the root password, you can recover it in this way:

1. Reboot the machine, at grub menu, edit the boot entry for Linux
2. 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
3. press 'b' to boot Linux into single user mode
4. now you will be able to log in the system without entering password 
5. type 'passwd' to reset root password

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

$ ssh-keygen -t dsa

    you can use either dsa or rsa.

2. copy scripter's public key to remoteserver

$ scp id_dsa.pub remoteserver:

3. on remoteserver, append the public key copied in step 2 to ~scripter/.ssh/authorized_keys

$ cat id_dsa.pub >> ~scripter/.ssh/authorized_keys

    if ~scripter/.ssh doesn't exist, manually create it

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 hostname

remoteserver

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

$ ssh-keygen -t dsa

2. copy the public key from localserver to remoteserver, and set up the key trust

$ ssh-copy-id -i ~scripter/.ssh/id_dsa.pub \
scripter@remoteserver

After you entering the password, everything is set up for you, you can log in remotely without password

$ ssh remoteserver hostname

remoteserver

ssh-copy-id saves us all the troubles of creating files and setting up the proper permission :)

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

$ 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.

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 used telnet for my testing

C:\>telnet www.redhat.com 80
GET /robots.txt HTTP/1.1
host: www.redhat.com

HTTP/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