UNIX tricks and treats

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

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.


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:


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

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.


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


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 ;-)


page 2 de 2 -