UNIX tricks and treats

Aller au contenu | Aller au menu | Aller à la recherche

mardi 12 août 2008

Guide rapide d'installation Apache 2 + PHP5 + OCI8 SOUS AIX 5.2


Fonctionne sous: IBM AIX 5.2

Les librairies OCI8 de PHP permettent de se connecter à une base de données Oracle depuis un serveur web comme Apache.

1)    Prérequis : installer bos.compat.termcap :

Installer le package bos.compat depuis le CD 1 de AIX 5.2. On peut simplement copier bos.compat depuis le CD dans un répertoire du serveur et l’installer avec smit.

2)    Installer le package amp (Apache Mysql PHP) :

Informations générales utiles sur :
http://www-941.ibm.com/collaboration/wiki/display/WikiPtype/aixopen

Télécharger le package complet pour AIX 5.2 depuis le site de pware :
ftp://ftp.hvcc.edu/pub/pware/aix52/bundles/amp/amp.pware-bundle.tar.gz

Gunzipper, détarer dans un répertoire et installer tous les paquets avec smit.

3)    Mettre à jour httpd.conf

Sous /opt/pware/conf, modifier httpd.conf comme il suit :

a) Ajouter les lignes :

LoadModule php5_module modules/libphp5.so

et :

<IfModule mod_php5.c>
  AddType application/x-httpd-php .php .phtml .php3
  AddType application/x-httpd-php-source .phps
</IfModule>

b) Modifier :

<IfModule dir_module>
    DirectoryIndex index.html
</IfModule>

en :

<IfModule dir_module>
    DirectoryIndex index.html index.php
</IfModule>

c) Modifier :

User daemon
Group daemon

en :

User daemon
Group staff

On peut alors faire un /opt/pware/bin/apachectl start pour verifier que Apache démarre bien.
On peut éventuellement créer un petit fichier index.php avec un phpinfo() à l’intérieur pour vérifier que PHP fonctionne bien.

/opt/pware/bin/apachectl stop

4)    Créer un fichier tnsnames.ora

Le placer dans le répertoire spécifié par $TNS_ADMIN du .profile de root.

5)    Mettre à jour le .profile de root :

Ajouter /opt/pware/bin dans $PATH
Ajouter /opt/pware/instantclient_10_2 dans $LIBPATH
(Sous AIX, LIBPATH est utilisé en lieu et place de LD_LIBRARY_PATH)

Ajouter:
export TNS_ADMIN=/opt/pware/instantclient_10_2
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Se déconnecter et se reconnecter de root

Se créer une petite page PHP testoci8.php et la mettre dans le répertoire htdocs.

apachectl start

Test de connexion. http://MON_SERVEUR/testoci8.php
Si vous obtenez une erreur ORA-12737, il faudra effectuer un mise à jour de la version basic light vers basic.

apachectl stop

6)    Mise à jour vers l’instant client basic :

Amp est installé avec les librairies instant client basic light, qui ne gère qu’une quantité limitée de NLS_LANG (client) et CHARACTER SET (serveur).

SQL> select * from nls_database_parameters where parameter IN ('NLS_LANGUAGE','N LS_TERRITORY','NLS_CHARACTERSET');

Si votre serveur Oracle a un character set un peu exotique (genre FRENCH_FRANCE. WE8ISO8859P15 ;-)), vous obtiendrez  une erreur ORA-12737 lors du test de connexion testoci8.php.

Télécharger les librairies instant client basic chez Oracle:
http://www.oracle.com/technology/tech/oci/instantclient/index.html

Prenez la verion pour AIX5L qui vous convient (bootinfo –k).
Dézippez-la dans un répertoire.
Copiez les fichiers de ce répertoire dans le répertoire /opt/pware/instantclient_10_2 en écrasant ceux qui s’y trouvent déjà.

apachectl start

Test de connexion. http://MON_SERVEUR/testoci8.php


7)    Démarrage automatique au boot:

Créer un script /opt/pware/bin/demarre_apache.sh

######################
#!/bin/ksh

PATH=$PATH:/opt/freeware/bin
export PATH

### Pour OCI8 PHP ###
LIBPATH=/usr/lib:/opt/pware/instantclient_10_2
export LIBPATH

TNS_ADMIN=/opt/pware/instantclient_10_2
export TNS_ADMIN

/opt/pware/bin/apachectl start

exit
######################

Le rendre exécutable.

Ajouter son lancement dans /etc/inittab :

apache:2:once:/opt/pware/bin/demarre_apache.sh


Laissez-moi un commentaire si cet article vous a été utile.

Nixman

mercredi 28 mai 2008

Using Pen load balancer as a port-forwarding proxy


Suppose you're a Paris-based firm that has several databases spread across different locations.

For example:
a) several Oracle databases listening on port 1521 on your own network, on addresses 10.75.75.1 - 10,
b) one Oracle database listening on port 1521 in Turku, Finland linked by a VPN  tunnel, on address 172.16.2.2,
c) one oracle database listening on port 1521 in Toulouse linked by a leased line, on address 172.31.31.31,
d) one Oracle database listening on port 21521 on a public internet WAN port in Tanger, with just source-address filtering as security, on address 212.66.66.66,
e) plus about a dozen other databases in different parts of the world at your clients' sites.

You have a partner providing an extra service to your clients, and he has to connect in real time on all of your databases. He doesn't want to spend money on a network connection to each and everyone of your clients. He proposes to pay a leased line to your Paris site, and you will do the dispatching.
Of course, you don't want him to know too much about your network, so you will restrict his access to only one address, which will be a firewall at your end of the line between your two sites .

Let's suppose the outside interface address of your firewall is 192.168.15.100, and the inside address (the one on your network) 10.75.75.254.

Providing  connectivity to the (a) databases on your local  network is pretty easy: you just have to give a port-forwarding rule and an access list to your router.

For example:
port 2001 on the outside interface ---> port 1521 on server1 at  10.75.75.1,
port 2002 on the outside interface ---> port 1521 on server2 at 10.75.75.2,
port 2003 on the outside interface ---> port 1521 on server3 at 10.75.75.3,
and so on...
Then, you just tell your partner to configure his tnsnames.ora to point at address 192.168.15.100 and port 2001 for server1, address 192.168.15.100 and port 2002 for server2, and so on...

However, forwarding the ports to the external (b), (c) and (d) databases is another affair.

Luckily, Pen is there for you. It was designed as load-balancing piece of software for server farms, but its features allow it to be used as a port-forwarding proxy, which is what we need in this case. It is available prepackaged for rpm- as well as deb-based Linux  distros, or as GPL'ed source code. You may learn more on its numerous features on its website: http://siag.nu/pen/

All you need is is a standard PC on your network, with a Linux Distro, let's say Debian, installed on it , as well as one (yes, only one) NIC.

Do an apt-get install pen (or an rpm-Uvh pen on an rpm-based distro).

Let's suppose you've given address 10.75.75.75 to this computer.
It has to know the routes to reach the Turku, Toulouse, and Tanger based servers, and of course the route to reach your partner who wants to connect to them. It has also to be allowed to reach them on the ports on which they are listening (i.e 1521, 1521 and 21521 respectively).

All you need now is to write a little snippet of shell code in a file that you would call for example port-fwrd.sh:

###############
#!/bin/bash

# This is for the Turku-based database
pen 10.75.75.75:2011 172.16.2.2:1521

# This is for the Toulouse-based database
pen 10.75.75.75:2012 172.31.31.31:1521

# This is for the Tanger-based database
pen 10.75.75.75:2013 212.66.66.66:21521

exit
###############

Make port-fwrd.sh executable by a chmod, and launch it: ./port-fwrd.sh
Have it start in your init scripts at rc3 level, so that it will get executed upon reboot of your machine.

All you have to do now on your firewall is to forward:
port 2011 on the outside interface ---> port 2011 on 10.75.75.75
port 2012 on the outside interface ---> port 2012 on 10.75.75.75
port 2013 on the outside interface ---> port 2013 on 10.75.75.75

and tell your partner to configure his tnsnames.ora to reach:
Turku on address 192.168.15.100 and port 2011,
Toulouse on address 192.168.15.100 and port 2012,
Tanger on address 192.168.15.100 and port 2013.

Beautiful and simple, ain't it?

Note: of course, if the server on one of the locations doesn't support shared sockets (as it is the case with for example a Windows 2000 Server failsafe cluster), you won't be able to use portforwarding, since the answering port on the target server will a dynamic one, and thus unpredictable.

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman




dimanche 18 mai 2008

Installing and configuring Oracle Heterogeneous Services for SQLServer


All databases share a common set of normalized SQL, which, in theory, allows them to interoperate directly using database links.
However, reality is not so simple, as those who've tried to connect DB2 with SQLServer might have realized.

Luckily, with Oracle, there are at least two ways to achieve direct SQL*NET connectivity to foreign databases: Oracle Heterogeneous Services ODBC (HSODBC) and Oracle Transparent Gateways.

Here, we will achieve a simple database link between an Oracle database on a UNIX server and an SQLServer database residing on a Windows Server 2003 machine through the simplest of the two methods: Oracle Heterogeneous services. Bluntly, it consists in installing an Oracle pseudo-listener on the target non-Oracle database server.

As Microsoft doesn't provide any sort of UNIX client for SQL Server, all this interoperability is achieved thanks to work done by Oracle coders. Kudos to them, and  the opposite to the other guys.
 
X = Windows Server 2003 with SQLServer 2005 + Oracle 8iR3 with Oracle HS
Y = Solaris 8 server with Oracle 8iR3 + Heterogeneous Services installed.
 
 
On X:

Step 0) On X: Install Oracle Server 8iR3 software or later with Heterogenous Database connectivity (Check that ODBC DRIVERS have really been installed). I won't detail the installation of Oracle on Windows here.
 
Step 1) On X: Configure DSN:
Go to: Settings -> Control Panel. Double-click on ODBC icon.
Then click on the System DSN tab and Add button. Add SQL Server, as local server. Name it, for example, MSQL (we will be using "MSQL" in our example configuration files from now on).
Test it. The default database is the on we're targetting.
 
Step 2) On X :  Copy the file inithsodbc.ora into initMSQL.ora in the  $ORACLE_HOME\hs\admin directory (If you'd named the DSN "ZOZO" in the previous step, you would have named the file initZOZO.ora, of course).
 
Step 3) On X: Modify  the initMSQL.ora file in the following manner (HS_FDS_CONNECT_INFO must have the same name as the DSN):

###########
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSQL
HS_FDS_TRACE_LEVEL = NO
###########
 
Step 4) On X:  Modify the listener.ora file in $ORACLE_HOME\network\admin directory in the following manner (you're modifying the  SID_LIST_LISTENER paragraph. SID_NAME must be the same as the DSN) :
 
###########
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSQL)
         (ORACLE_HOME = c:\Orant)
         (PROGRAM=hsodbc)
       )
      )
 ###########

Another solution would be to add an altogether new listener, that you've called MSQL, like this (here, we've set it to listen on port 1522):

###########
MSQL =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
 
SID_LIST_MSQL=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSQL)
         (ORACLE_HOME = c:\Orant)
         (PROGRAM=hsodbc)
       )
      )
###########

In that case, you would have to start this listener specifically by issuing a lsnrctl start MSQL
 
Step 5) On X: restart the listener by issuing a  lsnrctl reload.
 
 
On Y:
 
Step 1) On Y: set GLOBAL_NAMES=FALSE  in your database's init.ora  file or in the initialization parameters.
 
Step 2) On Y: Add an entry pointing at server X listener by adding the following lines to the tnsnames.ora file:
 
###########
testMSQL  =
  (DESCRIPTION=
    (ADDRESS=
        (PROTOCOL=tcp)
        (HOST=SERVER_X_IP_ADDRESS)
        (PORT=SERVER_X_LISTENER_PORT)
     )
     (CONNECT_DATA=
        (SID=MSQL)
     )
     (HS=OK)
  )
###########
 
Step 3) On Y: Test connectivity by issuing a tnsping testMSQL
 
Step 4) On Y: Create the database link between your Oracle database and testMSQL by issuing the following SQL command:
SQL> create public database link testingMSQL connect to USER identified by PASSWORD using 'testMSQL';
 
Step 5) On Y: Do some selects on Server X's tables:
SQL> select * from TABLE_NAME@testingMSQL;

You're done!

Beware that you're restricted to normalized SQL. between the two databases. Old-timers will find themselves back in Oracle 6 days: You won't be able to use INSERT SELECT statements or other Oracle enhancements, but will have to go through a cursor, etc... However, you will be able to issue simple SELECT, INSERT and UPDATE commands. Which is what you wanted in the first place.

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman

jeudi 15 mai 2008

Installing a syslog server on AIX


Works on: AIX

AIX relies mostly on its own error reporting tools like errpt in order to keep track of incidents.

Thus, by default, AIX doesn't have a working configuration of syslog server, even though syslogd is installed. It simply lacks the proper configuration files.

Here are the steps to create a working configuration file and activate the service.

First, you have to create and edit the /etc/syslog.conf file. For example like this:

########
kern.debug;mail.none      /var/adm/messages       rotate size 2m files 3 compress
*.emerg;mail.none /var/adm/messages       rotate size 2m files 3 compress
*.alert;mail.none /var/adm/messages       rotate size 2m files 3 compress
*.crit;mail.none  /var/adm/messages       rotate size 2m files 3 compress
*.warning;mail.none       /var/adm/messages       rotate size 2m files 3 compress
*.err;mail.none   /var/adm/messages       rotate size 2m files 3 compress
*.notice;mail.none        /var/adm/messages       rotate size 2m files 3 compress
*.info;mail.none  /var/adm/messages       rotate size 2m files 3 compress
auth.notice     /var/adm/authlog        rotate size 2m files 3 compress
mail.info       /var/adm/mailerrors     rotate size 2m files 3 compress
########

This configuration allows you to rotate the logs on three files of 2MB each, and compress them.

Then, all you have to do is to run the following commands in order to create the log files, and restart the syslog service.
 
# touch /var/adm/messages
# touch /var/adm/authlog
# touch /var/adm/mailerrors
# refresh -s syslogd

 
If the configuration is successful, you will see a line resembling the following:
Nov 26 15:53:06 SERVER_NAME syslogd: restart
in the /var/adm/messages file right after running refresh -s syslogd

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman

Installer un serveur de syslog sous AIX


(The english version of this post is here)

Fonctionne sous: AIX IBM


L'UNIX AIX d'IBM utilisant ses propres systèmes de collecte d'informations et erreurs, comme errpt, il ne possède pas de serveur de syslog actif et configuré par défaut.

Voici les étapes pour le mettre en place:

Editer le fichier /etc/syslog.conf
 
Y ajouter les lignes suivantes:
 
########
kern.debug;mail.none      /var/adm/messages       rotate size 2m files 3 compress
*.emerg;mail.none /var/adm/messages       rotate size 2m files 3 compress
*.alert;mail.none /var/adm/messages       rotate size 2m files 3 compress
*.crit;mail.none  /var/adm/messages       rotate size 2m files 3 compress
*.warning;mail.none       /var/adm/messages       rotate size 2m files 3 compress
*.err;mail.none   /var/adm/messages       rotate size 2m files 3 compress
*.notice;mail.none        /var/adm/messages       rotate size 2m files 3 compress
*.info;mail.none  /var/adm/messages       rotate size 2m files 3 compress
auth.notice     /var/adm/authlog        rotate size 2m files 3 compress
mail.info       /var/adm/mailerrors     rotate size 2m files 3 compress
########
 
Cette configuration vous permet d'effectuer une rotation des logs sur trois fichiers de 2 Mo chacun et de les compresser.

Ensuite, il ne vous reste plus qu'à lancer les commandes suivantes:
 
# touch /var/adm/messages
# touch /var/adm/authlog
# touch /var/adm/mailerrors
# refresh -s syslogd

 
Si tout va bien, vous verrez une ligne du type:
Nov 26 15:53:06 NOM_SERVEUR syslogd: restart
dans le fichier /var/adm/messages après le refresh -s syslogd


Laissez-moi un commentaire si cet article vous a été utile.

Bonne journée.

Nixman

lundi 12 mai 2008

Activating disk cache on a Sun Solaris Server


Works on: Sun Solaris

Solaris disables disk cache by default, which has debatable advantages of data integrity, and definitive disadvantages in terms of I/O performance.

Here are the steps needed to enable the functionality:

# init 1
# format -e
format> cache
format> write_cache
format> display
format> enable
  (if disabled)

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman


vendredi 9 mai 2008

Selecting tables for an import parfile


This little SQL script is quite handy when constructing a parfile for an import from a dump file of a previous Oracle export

Suppose you want to import all tables beginning with "XY", and there are several hundreds of them.

You would first construct a parfile named my_parfile.par, with the following content:

USERID=USER/PASSWORD
BUFFER=40960
FILE=DUMP_FILE_NAME.dmp
LOG=IMPORT_LOG_FILE_NAME.log
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y

Then, you would create the following sql script (that you've called select_xy_tables_parfile.sql), in order  to construct  the list of tables you want to import (supposing the tables still have the same names as at the moment of the initial export):

set head off 
set pages 0
set trims on
set echo off
set feedback off  
spool my_XY_tables.txt
select decode( rownum, 1, 'TABLES=(', ',' ), table_name
from user_tables
where table_name like 'XY%'
union all
select ')', null
from dual ;
spool off
quit


Then, just run the script against your database:

SQL> @select_xy_tables_parfile.sql


After that, just do a cat my_XY_tables.txt >> my_parfile.par

Finally, after setting the correct ORACLE_SID, do an imp parfile=./my_parfile.par

You're done.

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman

mardi 6 mai 2008

Activer le cache disque sur un serveur Sun Solaris


(The english version of this post is here)

Fonctionne sous: Sun Solaris

Dans un souci intégriste ... d'intégrité des données, Solaris désactive par défaut le cache d'écriture des disques durs, au détriment de la performance entrée/sortie des disques.

Voici les étapes permettant de restaurer cette fonctionnalité:

# init 1
# format -e
format> cache
format> write_cache
format> display
format> enable
  (si disablé)

Attention! Effectuer cette manipulation de préférence avant que le serveur ne soit en production, dans la mesure où il faut passer par le niveau d'exécution single user.


Laissez-moi un commentaire si cet article vous a été utile.

Bonne journée.

Nixman

Quelques commandes utiles pour serveurs Sun Solaris


Fonctionne sous: Sun Solaris


- Les serveurs SunFire V2xx (V210, V240, etc...), T1000 et T2000 (processeurs Niagara) possèdent une mémorisation du dernier état de l'alimentation électrique. Il faut cependant activer cette fonctionnalité, qui est bizarrement désactivée par défaut, avec la commande suivante:

/usr/platform/`uname -i`/sbin/scadm set sc_powerstatememory true 

... Sinon, votre serveur ne redémarrera pas automatiquement après une coupure de courant, ce qui est pour le moins fâcheux.

Note: Sur les serveurs SunFire T1000 et T2000 plus anciens (d'avant avril 2007), il n'est pas possible d'utiliser la commande scadm, et il faut passer par la console série, ou l'ALOM en Net Management, et taper la commande suivante: setsc sc_powerstatememory true (Merci à patrickm1 pour cette précision). La procédure d'activation de l'ALOM en NetMGMT est expliquée ici.


- Visualiser les paramètres hardware (température, ventilateurs, etc...)  et pannes matérielles éventuelles :

/usr/platform/sun4u/sbin/prtdiag -v

... Selon le modèle du serveur et le nombre de senseurs, vous pouvez monitorer un nombre de paramètres plus ou moins élevés.


Laissez-moi un commentaire si cet article vous a été utile.

Bonne journée.

Nixman

dimanche 4 mai 2008

Replacing a failing rootvg disk on AIX


Works on : AIX

Let's suppose you're getting permanent hardware errors on hdisk0  when running the errpt -a command on an IBM AIX server.

In order to check that both disks are really assigned to the volume group, you should start with:
lsvg -p rootvg
You should see both hdisk0 and hdisk1 under the PV name.

A second thing to check would be that the re really are copies:
lsvg -l rootvg
Just check that there is a 1:2 relationship between LPs and PPs, and that PVs is equal to 2. Otherwise, you should check that the volume that's not copied doesn't reside on the failing disk with:
lslv -l LV_NAME

Once you've done these preliminary checks, you can start detaching hdisk0 from the volume:
unmirrorvg rootvg hdisk0

After running the command, I've sometimes had these messages, which are mostly informational:
0516-1246 rmlvcopy: If hd5 is the boot logical volume, please run 'chpv -c <diskname>'
        as root user to clear the boot record and avoid a potential boot
        off an old boot image that may reside on the disk from which this
        logical volume is moved/removed.
0301-108 mkboot: Unable to read file blocks. Return code: -1
0516-1132 unmirrorvg: Quorum requirement turned on, reboot system for this
        to take effect for rootvg.
0516-1144 unmirrorvg: rootvg successfully unmirrored, user should perform
        bosboot of system to reinitialize boot records.  Then, user must modify
        bootlist to just include:  hdisk0.

Then we reduce the volume:
reducevg rootvg hdisk0

And remove the device from configuration:
rmdev -dl hdisk0

Then, we will have to power down the machine, as we're dealing with a rootvg disk. However, before doing so, it's preferable to check whether we will boot of from the right drive:
bootinfo -b will tell you which drive was last booted up.
If it's the failed drive (hdisk0 in our case), we should change it to the drive still usable (hdisk1 in our case) by creating the boot image on hdisk1 and recrcreating the fixed ipldevice link, which was deleted by the previous rmdev command  :
bosboot -ad /dev/hdisk1

ln /dev/rhdisk1 /dev/ipldevice

Then, we can check bootlist:
bootlist -m normal -o

... And now, we can finally power down our server, replace the failed drive, and power it back on...

Once the server has booted up, we should run:
cfgmgr
so that the OS will recognize the new disk.

To check that AIX really has done its job, run:
lsdev -Cc disk
which should list both disks hdisk0 and hdisk1

Now, we can assign the new disk to the rootvg volume group:
extendvg rootvg hdisk0

Then we mirror the group:
mirrorvg rootvg

Wait for hdisk1 to complete copying on hdisk0 (it can take some time, as you can imagine). You can check activity with iostat.

You should check that both disks are really assigned to rootvg by typing:
lsvg -p rootvg

An lsvg -l rootvg will show you whether mirroring has worked OK. You should once again have a 1:2 relationship between LPs and PPs.

Then, create the boot image on the new disk:
bosboot -a -d hdisk0

Finally, modify the bootlist to take into account both disks:
bootlist -m normal hdisk0 hdisk1
Check with:
bootlist -m -normal -o
 
And you're finally done!

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman

mercredi 30 avril 2008

Trouver le process ID UNIX d'une session Oracle


(If you're looking for the english version, it's here)
(Ceci est la traduction française de l'article précédent.)

Fonctionne sur: Solaris, Linux, AIX

Supposons que vous ayiez un processus Oracle qui ait généré un verrou sur votre base de données et n'arrivez pas à le tuer avec une simple commande ALTER SYSTEM KILL SESSION 'sid, serial#'

Il vous suffit alors d'effecuer la jointure suivante entre les tables V$SESSION et V$PROCESS d'Oracle, afin de retrouver le process ID système (spid) qu'il faudra tuer:

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;


La colonne spid vous donne la valeur du process ID qu'il faudra tuer avec une commande shell kill -9 spid.

Et voilà!

Rem: Sur un serveur Windows, on utiliserait la commande suivante:
orakill $ORACLE_SID spid

Laissez-moi un commentaire si cet article vous a été utile.

Bien à vous

Nixman

vendredi 14 mars 2008

Finding the UNIX process ID of an Oracle session to kill


Works with: Solaris, Linux, AIX


Let us start with something easy and frequently used on sites with badly written PL/SQL code.

Let's suppose that you have an Oracle process that has gone astray, or even worse, is generating a lock on your database, preventing other users from making updates on a table.

The first step would be to find the Oracle session that's causing the lock (not the ones that are suffering from the lock, which you would find easily by quering the V$SESSION table).

It's as easy as:

SELECT * FROM dba_blockers;

You might have to wait for a while before getting the magic serial# and sid (not to be mistaken with ORACLE_SID of course) needed to run your ALTER SYSTEM KILL SESSION 'sid , serial#' command.

Note: on Oracle 8i and earlier, you would first have to run the catblock.sql script in $ORACLE_HOME/rdbms/admin/ directory in order to create the DBA_BLOCKERS table.

However, sometimes killing the session simply won't work. In that case, you could have to kill the session, or an Oracle proces gone astray through UNIX system utilities.

For that purpose, on UNIX systems, Oracle has the V$PROCESS table, which can be joined with the V$SESSION table to find the UNIX process ID matching your Oracle session ID (the sid field of DBA_BLOCKERS).

The following query will yield you the needed system process ID (spid), among other useful information about your rogue process or session:

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;


All you need to do now is to run a kill -9 spid  on your UNIX machine upon the process number given by the spid column of the above query.

Note: On a windows box, you would use the orakilll.exe utility located in $ORACLE_HOME/bin. Like this:
orakill $ORACLE_SID spid

Happy computing.

Drop me a comment if this post has been useful to you.
Alternatively, you could also visit a few links to keep me in business ;-)


Nixman

page 3 de 3 -