Jennifer Lin’s Weblog

July 27, 2008

Unix/Linux find command

Filed under: UNIX/LINUX — jennyca @ 9:14 pm

Locating Files:

The find command is used to locate files on a Unix or Linux system.  find will search any set of directories you specify for files that match the supplied search criteria.  You can search for files by name, owner, group, type, permissions, date, and other criteria.  The search is recursive in that it will search all subdirectories too.  The syntax looks like this:

find where-to-look criteria what-to-do

All arguments to find are optional, and there are defaults for all parts.  (This may depend on which version of find is used.  Here we discuss the freely available GNU version of find, which is the version available on YborStudent.)  For example where-to-look defaults to . (that is, the current working directory), criteria defaults to none (that is, show all files), and what-to-do (known as the find action) defaults to -print (that is, display found files to standard output).

For example:

find

will display all files in the current directory and all subdirectories.  The commands

find . -print
find .

do the exact same thing.  Here’s an example find command using a search criteria and the default action:

find / -name foo

will search the whole system for any files named foo and display them.  Here we are using the criteria -name with the argument foo to tell find to perform a name search for the filename foo. The output might look like this:

/home/wpollock/foo
/home/ua02/foo
/tmp/foo

If find doesn’t locate any matching files, it produces no output.

The above example said to search the whole system, by specifying the root directory (/) to search.  If you don’t run this command as root, find will display a error message for each directory on which you don’t have read permission.  This can be a lot of messages, and the matching files that are found may scroll right off your screen.  A good way to deal with this problem is to redirect the error messages so you don’t have to see them at all:

find / -name foo 2>/dev/null

Other Features And Applications:

The -print action lists the files separated by a space when the output is piped to another command.  This can lead to a problem if any found files contain spaces in their names, as the output doesn’t use any quoting.  In such cases, when the output of find contains a file name such as foo bar and is piped into another command, that command sees two file names, not one file name containing a space.

In such cases you can specify the action -print0 instead, which lists the found files separated not with a space, but with a null character (which is not a legal character in Unix or Linux file names).  Of course the command that reads the output of find must be able to handle such a list of file names.  Many commands commonly used with find (such as tar or cpio) have special options to read in file names separated with nulls instead of spaces.

You can use shell-style wildcards in the -name search argument:

find . -name foo\*bar

This will search from the current directory down for foo*bar (that is, any filename that begins with foo and ends with bar).  Note that wildcards in the name argument must be quoted so the shell doesn’t expand them before passing them to find.  Also, unlike regular shell wildcards, these will match leading periods in filenames.  (For example find -name \*.txt.)

You can search for other criteria beside the name.  Also you can list multiple search criteria.  When you have multiple criteria any found files must match all listed criteria.  That is, there is an implied Boolean AND operator between the listed search criteria.  find also allows OR and NOT Boolean operators, as well as grouping, to combine search criteria in powerful ways (not shown here.)

Here’s an example using two search criteria:

find / -type f -mtime -7 | xargs tar -rf weekly_incremental.tar
gzip weekly_incremental.tar

will find any regular files (i.e., not directories or other special files) with the criteria -type f, and only those modified seven or fewer days ago (-mtime -7).  Note the use of xargs, a handy utility that coverts a stream of input (in this case the output of find) into command line arguments for the supplied command (in this case tar, used to create a backup archive). 1

Another use of xargs is illustrated below.  This command will efficiently remove all files named core from your system (provided you run the command as root of course):

find / -name core | xargs /bin/rm -f
find / -name core -exec /bin/rm -f '{}' \; # same thing
find / -name core -delete                  # same if using Gnu find

(The last two forms run the rm command once per file, and are not as efficient as the first form.)

One of my favorite find criteria is to locate files modified less than 10 minutes ago.  I use this right after using some system administration tool, to learn which files got changed by that tool:

find / -mmin -10

(This search is also useful when I’ve downloaded some file but can’t locate it.)

Another common use is to locate all files owned by a given user (-user username).  This is useful when deleting user accounts.

You can also find files with various permissions set.  -perm /permissions means to find files with any of the specified permissions on, -perm -permissions means to find files with all of the specified permissions on, and -perm permissions means to find files with exactly permissionsPermisisons can be specified either symbolically (preferred) or with an octal number.  The following will locate files that are writeable by others:

find . -perm +o=w

(Using -perm is more complex than this example shows.  You should check both the POSIX documentation for find (which explains how the symbolic modes work) and the Gnu find man page (which describes the Gnu extensions).

When using find to locate files for backups, it often pays to use the -depth option, which forces the output to be depth-first—that is, files first and then the directories containing them.  This helps when the directories have restrictive permissions, and restoring the directory first could prevent the files from restoring at all (and would change the time stamp on the directory in any case).  Normally, find returns the directory first, before any of the files in that directory.  This is useful when using the -prune action to prevent find from examining any files you want to ignore:

find / -name /dev -prune | xargs tar ...

When specifying time with find options such as -mmin (minutes) or -mtime (24 hour periods, starting from now), you can specify a number n to mean exactly n, -n to mean less than n, and +n to mean more than n. 2 For example:

find . -mtime 0   # find files modified within the past 24 hours
find . -mtime -1  # find files modified within the past 24 hours
find . -mtime 1   # find files modified between 24 and 48 hours ago
find . -mtime +1  # find files modified more than 48 hours ago
find . -mmin +5 -mmin -10 # find files modifed between 6 and 9 minutes ago

The following displays non-hidden (no leading dot) files in the current directory only (no subdirectories), with an arbitrary output format (see the man page for the dozens of possibilities with the -printf action):

find . -maxdepth 1 -name '[!.]*' -printf 'Name: %16f Size: %6s\n'

As a system administrator you can use find to locate suspicious files (e.g., world writable files, files with no valid owner and/or group, SetUID files, files with unusual permissions, sizes, names, or dates).  Here’s a final more complex example (which I save as a shell script):

find / -noleaf -wholename '/proc' -prune \
     -o -wholename '/sys' -prune \
     -o -wholename '/dev' -prune \
     -o -wholename '/windows-C-Drive' -prune \
     -o -perm -2 ! -type l  ! -type s \
     ! \( -type d -perm -1000 \) -print

This says to seach the whole system, skipping the directories /proc, /sys, /dev, and /windows-C-Drive (presumably a Windows partition on a dual-booted computer).  The -noleaf option tells find to not assume all remaining mounted filesystems are Unix file systems (you might have a mounted CD for instance).  The -o is the Boolean OR operator, and ! is the Boolean NOT operator (applies to the following criteria).  So this criteria says to locate files that are world writable (-perm -2) and NOT symlinks (! -type l) and NOT sockets (! -type s) and NOT directories with the sticky (or text) bit set (! \( -type d -perm -1000 \)).  (Symlinks, sockets and directories with the sticky bit set are often world-writable and generally not suspicious.)

A common request is a way to find all the hard links to some file.  Using ls -li file will tell you how many hard links the file has, and the inode number.  You can locate all pathnames to this file with:

  find mount-point -xdev -inum inode-number

Since hard links are restricted to a single filesystem, you need to search that whole filesystem so you start the search at the filesystem’s mount point.  (This is likely to be either /home or / for files in your home directory.)  The -xdev options tells find to not search any other filesystems.

(While most Unix and all Linux systems have a find command that supports the -inum criteria, this isn’t POSIX standard.  Older Unix systems provided the ncheck command instead that could be used for this.)

Using -exec Efficiently

The -exec option to find is great, but since it runs the command listed for every found file, it isn’t very efficient.  On a large system this makes a difference!  One solution is to combine find with xargs as discussed above:

  find whatever... | xargs command

However this approach has two limitations.  Firstly not all commands accept the list of files at the end of the command.  A good example is cp:

find . -name \*.txt | xargs cp /tmp  # This won't work!

(Note the Gnu version of cp has a non-POSIX option -t for this.)

Secondly filenames may contain spaces or newlines, which would confuse the command used with xargs.  (Again Gnu tools have options for that, find ... -print0 |xargs -0 ....)

There are POSIX (but non-obvious) solutions to both problems.  An alternate form of -exec ends with a plus-sign, not a semi-colon.  This form collects the filenames into groups or sets, and runs the command once per set.  (This is exactly what xargs does, to prevent argument lists from becoming too long for the system to handle.)  In this form the {} argument expands to the set of filenames.  For example:

find / -name core -exec /bin/rm -f '{}' +

This form of -exec can be combined with a shell feature to solve the other problem.  The POSIX shell allows us to use:

sh -c 'command-line' [ command-name [ args... ] ]

(We don’t usually care about the command-name, so X, dummy, or inline cmd is used.)  Here’s an example of efficiently copying found files, in a POSIX-compliant way 3:

find . -name '*.txt' -exec sh -c 'cp "$@" /tmp' dummy {} +

Or even better:

find . -name '*.txt' -type f \
  -exec sh -c 'exec cp -f "$@" /tmp' find-copy {} +

The find command can be amazingly useful.  See the man page to learn all the criteria and options you can use.

http://content.hccfl.edu/pollock/Unix/FindCmd.htm

使ssh不用输入密码

Filed under: UNIX/LINUX — jennyca @ 8:54 pm

有些时候,我们在复制/移动文件到另一台机器时会用到scp,因为它比较安全。但如果每次

都要输入密码,就比较烦了,尤其是在script里。不过,ssh有另一种用密钥对来验证的方

式。下面写出我生成密匙对的过程,供大家参考。

第一步:生成密匙对,我用的是rsa的密钥。使用命令 “ssh-keygen -t rsa”

代码:

[user1@rh user1]$ ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/home/user1/.ssh/id_rsa):

Created directory ‘/home/user1/.ssh’.

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/user1/.ssh/id_rsa.

Your public key has been saved in /home/user1/.ssh/id_rsa.pub.

The key fingerprint is:

e0:f0:3b:d3:0a:3d:da:42:01:6a:61:2f:6c:a0:c6:e7 user1@rh.test.com

[user1@rh user1]$

生成的过程中提示输入密钥对保存位置,直接回车,接受默认值就行了。接着会提示输入一

个不同于你的password的密码,直接回车,让它空着。当然,也可以输入一个。(我比较懒

,不想每次都要输入密码。) 这样,密钥对就生成完了。

其中公共密钥保存在 ~/.ssh/id_rsa.pub

私有密钥保存在 ~/.ssh/id_rsa

然后改一下 .ssh 目录的权限,使用命令 “chmod 755 ~/.ssh”

代码:

[user1@rh user1]$ chmod 755 ~/.ssh

[user1@rh user1]$

之后把这个密钥对中的公共密钥复制到你要访问的机器上去,并保存为

~/.ssh/authorized_keys.

代码:

[user1@rh user1]$ scp ~/.ssh/id_rsa.pub rh1:/home/user1/.ssh/authorized_keys

user1@rh1’s password:

id_rsa.pub 100% 228 3.2MB/s 00:00

[user1@rh user1]$

之这样就大功告成了。之后你再用ssh scp sftp 之类的访问那台机器时,就不用输入密码

了,用在script上更是方便。

本文来自:http://doc.linuxpk.com/5102.html

非常全面的NFS文档(FOR LINUX)

Filed under: UNIX/LINUX — jennyca @ 4:35 pm

一、NFS简介

NFS-Network FileSystem的缩写,NFS是由Sun开发并发展起来的一项用于在不同机器,不同操作系统之间通过网络互相分享各自的文件。NFS server也可以看作是一个FILE SERVER,它可以让你的PC通过网络将远端得NFS SERVER共享出来的档案 MOUNT到自己的系统中,在CLIENT看来使用NFS的远端文件就象是在使用本地文件一样。

NFS协议从诞生到现在为止,已经有多个版本,如NFS V2(rfc1094),NFS V3(rfc1813)(最新的版本是V4(rfc3010)。

二、各NFS协议版本的主要区别

V3相对V2的主要区别:

1、文件尺寸

V2最大只支持32BIT的文件大小(4G),而NFS V3新增加了支持64BIT文件大小的技术。

2、文件传输尺寸

V3没有限定传输尺寸,V2最多只能设定为8k,可以使用-rsize and -wsize 来进行设定。

3、完整的信息返回

V3增加和完善了许多错误和成功信息的返回,对于服务器的设置和管理能带来很大好处。

4、增加了对TCP传输协议的支持

V2只提供了对UDP协议的支持,在一些高要求的网络环境中有很大限制,V3增加了对TCP协议的支持

*5、异步写入特性

6、改进了SERVER的mount性能

7、有更好的I/O WRITES 性能。

9、更强网络运行效能,使得网络运作更为有效。

10、更强的灾难恢复功能。

异步写入特性(v3新增加)介绍:

NFS V3 能否使用异步写入,这是可选择的一种特性。NFS V3客户端发发送一个异步写入请求到服务器,在给客户端答复之前服务器并不是必须要将数据写入到存储器中(稳定的)。服务器能确定何时去写入数据或者将多 个写入请求聚合到一起并加以处理,然后写入。客户端能保持一个数据的 copy以防万一服务器不能完整的将数据写入。当客户端希望释放这个copy的时候,它会向服务器通过这个操作过程,以确保每个操作步骤的完整。异步写入 能够使服务器去确定最好的同步数据的策略。使数据能尽可能的同步的提交何到达。与V2比较来看,这样的机制能更好的实现数据缓冲和更多的平行(平衡)。而 NFS V2的SERVER在将数据写入存储器之前不能再相应任何的写入请求。

V4相对V3的改进:

1:改进了INTERNET上的存取和执行效能

2:在协议中增强了安全方面的特性

3:增强的跨平台特性

三、CLIENT和SERVER的具体操作和设置

在讲NFS SERVER的运作之前先来看一些与NFS SERVER有关的东西:

RPC(Remote Procedure Call)

NFS本身是没有提供信息传输的协议和功能的,但NFS却能让我们通过网络进行资料的分享,这是因为NFS使用了一些其它的传输协议。而这些传输协议勇 士用到这个RPC功能的。可以说NFS本身就是使用RPC的一个程序。或者说NFS也是一个RPC SERVER.所以只要用到NFS的地方都要启动RPC服务,不论是NFS SERVER或者NFS CLIENT。这样SERVER和CLIENT才能通过RPC来实现PROGRAM PORT 的对应。可以这么理解RPC和NFS的关系:NFS是一个文件系统,而RPC是负责负责信息的传输。

NFS需要启动的DAEMONS

pc.nfsd:主要复杂登陆权限检测等。

rpc.mountd:负责NFS的档案系统,当CLIENT端通过rpc.nfsd登陆SERVER后,对clinet存取server的文件进行一系列的管理

NFS SERVER在REDHAT LINUX平台下一共需要两个套件:nfs-utils和PORTMAP

nfs-utils:提供rpc.nfsd 及 rpc.mountd这两个NFS DAEMONS的套件

portmap:NFS其实可以被看作是一个RPC SERVER PROGRAM,而要启动一个RPC SERVER PROGRAM,都要做好PORT的对应工作,而且这样的任务就是由PORTMAP来完成的。通俗的说PortMap就是用来做PORT的mapping 的。

一:服务器端的设定(以LINUX为例)

服务器端的设定都是在/etc/exports这个文件中进行设定的,设定格式如下:

欲分享出去的目录 主机名称1或者IP1(参数1,参数2) 主机名称2或者IP2(参数3,参数4)

上面这个格式表示,同一个目录分享给两个不同的主机,但提供给这两台主机的权限和参数是不同的,所以分别设定两个主机得到的权限。

可以设定的参数主要有以下这些:

rw:可读写的权限;

ro:只读的权限;

no_root_squash:登入到NFS主机的用户如果是ROOT用户,他就拥有ROOT的权限,此参数很不安全,建议不要使用。

root_squash:在登入 NFS 主機使用分享之目錄的使用者如果是 root 時,那麼這個使用者的權限將被壓縮成為匿名使用者,通常他的 UID 與 GID 都會變成 nobody 那個身份;

all_squash:不管登陆NFS主机的用户是什么都会被重新设定为nobody。

anonuid:将登入NFS主机的用户都设定成指定的user id,此ID必须存在于/etc/passwd中。

anongid:同 anonuid ,但是變成 group ID 就是了!

sync:资料同步写入存储器中。

async:资料会先暂时存放在内存中,不会直接写入硬盘。

insecure 允许从这台机器过来的非授权访问。

例如可以编辑/etc/exports为:

/tmp

*(rw,no_root_squash)

/home/public

192.168.0.*(rw)

*(ro)

/home/test

192.168.0.100(rw)

/home/linux

*.the9.com(rw,all_squash,anonuid=40,anongid=40)

设定好后可以使用以下命令启动NFS:

/etc/rc.d/init.d/portmap start (在REDHAT中PORTMAP是默认启动的)

/etc/rc.d/init.d/nfs start

exportfs命令:

如果我们在启动了NFS之后又修改了/etc/exports,是不是还要重新启动nfs呢?这个时候我们就可以用exportfs命令来使改动立刻生效,该命令格式如下:

exportfs [-aruv]

-a :全部mount或者unmount /etc/exports中的内容

-r :重新mount /etc/exports中分享出来的目录

-u :umount 目录

-v :在 export 的時候,将详细的信息输出到屏幕上。

具体例子:

[root @test root]# exportfs -rv <==全部重新 export 一次!

exporting 192.168.0.100:/home/test

exporting 192.168.0.*:/home/public

exporting *.the9.com:/home/linux

exporting *:/home/public

exporting *:/tmp

reexporting 192.168.0.100:/home/test to kernel

exportfs -au <==全部都卸载了。

客户段的操作:

1、showmout命令对于NFS的操作和查错有很大的帮助,所以我们先来看一下showmount的用法

showmout

-a :这个参数是一般在NFS SERVER上使用,是用来显示已经mount上本机nfs目录的cline机器。

-e :显示指定的NFS SERVER上export出来的目录。

例如:

showmount -e 192.168.0.30

Export list for localhost:

/tmp *

/home/linux *.linux.org

/home/public (everyone)

/home/test 192.168.0.100

2、mount nfs目录的方法:

mount -t nfs hostname(orIP):/directory /mount/point

具体例子:

Linux: mount -t nfs 192.168.0.1:/tmp /mnt/nfs

Solaris:mount -F nfs 192.168.0.1:/tmp /mnt/nfs

BSD: mount 192.168.0.1:/tmp /mnt/nfs

3、mount nfs的其它可选参数:

HARD mount和SOFT MOUNT:

HARD: NFS CLIENT会不断的尝试与SERVER的连接(在后台,不会给出任何提示信息,在LINUX下有的版本仍然会给出一些提示),直到MOUNT上。

SOFT:会在前台尝试与SERVER的连接,是默认的连接方式。当收到错误信息后终止mount尝试,并给出相关信息。

例如:mount -F nfs -o hard 192.168.0.10:/nfs /nfs

对于到底是使用hard还是soft的问题,这主要取决于你访问什么信息有关。例如你是想通过NFS来运行X PROGRAM的话,你绝对不会希望由于一些意外的情况(如网络速度一下子变的很慢,插拔了一下网卡插头等)而使系统输出大量的错误信息,如果此时你用的 是HARD方式的话,系统就会等待,直到能够重新与NFS SERVER建立连接传输信息。另外如果是非关键数据的话也可以使用SOFT方式,如FTP数据等,这样在远程机器暂时连接不上或关闭时就不会挂起你的会 话过程。

rsize和wsize:

文件传输尺寸设定:V3没有限定传输尺寸,V2最多只能设定为8k,可以使用-rsize and -wsize 来进行设定。这两个参数的设定对于NFS的执行效能有较大的影响

bg:在执行mount时如果无法顺利mount上时,系统会将mount的操作转移到后台并继续尝试mount,直到mount成功为止。(通常在设定/etc/fstab文件时都应该使用bg,以避免可能的mount不上而影响启动速度)

fg:和bg正好相反,是默认的参数

nfsvers=n:设定要使用的NFS版本,默认是使用2,这个选项的设定还要取决于server端是否支持NFS VER 3

mountport:设定mount的端口

port:根据server端export出的端口设定,例如如果server使用5555端口输出NFS,那客户端就需要使用这个参数进行同样的设定

timeo=n:设置超时时间,当数据传输遇到问题时,会根据这个参数尝试进行重新传输。默认值是7/10妙(0.7秒)。如果网络连接不是很稳定的话 就要加大这个数值,并且推荐使用HARD MOUNT方式,同时最好也加上INTR参数,这样你就可以终止任何挂起的文件访问。

intr 允许通知中断一个NFS调用。当服务器没有应答需要放弃的时候有用处。

udp:使用udp作为nfs的传输协议(NFS V2只支持UDP)

tcp:使用tcp作为nfs的传输协议

namlen=n:设定远程服务器所允许的最长文件名。这个值的默认是255

acregmin=n:设定最小的在文件更新之前cache时间,默认是3

acregmax=n:设定最大的在文件更新之前cache时间,默认是60

acdirmin=n:设定最小的在目录更新之前cache时间,默认是30

acdirmax=n:设定最大的在目录更新之前cache时间,默认是60

actimeo=n:将acregmin、acregmax、acdirmin、acdirmax设定为同一个数值,默认是没有启用。

retry=n:设定当网络传输出现故障的时候,尝试重新连接多少时间后不再尝试。默认的数值是10000 minutes

noac:关闭cache机制。

同时使用多个参数的方法:mount -t nfs -o timeo=3,udp,hard 192.168.0.30:/tmp /nfs

请注意,NFS客户机和服务器的选项并不一定完全相同,而且有的时候会有冲突。比如说服务器以只读的方式导出,客户端却以可写的方式mount,虽然可以成功mount上,但尝试写入的时候就会发生错误。一般服务器和客户端配置冲突的时候,会以服务器的配置为准。

4、/etc/fstab的设定方法

/etc/fstab的格式如下:

fs_spec

fs_file

fs_type

fs_options

fs_dump

fs_pass

fs_spec:该字段定义希望加载的文件系统所在的设备或远程文件系统,对于nfs这个参数一般设置为这样:192.168.0.1:/NFS

fs_file:本地的挂载点

fs_type:对于NFS来说这个字段只要设置成nfs就可以了

fs_options:挂载的参数,可以使用的参数可以参考上面的mount参数。

fs_dump

-

该选项被”dump”命令使用来检查一个文件系统应该以多快频率进行转储,若不需要转储就设置该字段为0

fs_pass

-

该字段被fsck命令用来决定在启动时需要被扫描的文件系统的顺序,根文件系统”/”对应该字段的值应该为1,其他文件系统应该为2。若该文件系统无需在启动时扫描则设置该字段为0 。

5、与NFS有关的一些命令介绍

nfsstat:

查看NFS的运行状态,对于调整NFS的运行有很大帮助

rpcinfo:

查看rpc执行信息,可以用于检测rpc运行情况的工具。

四、NFS调优

调优的步骤:

1、测量当前网络、服务器和每个客户端的执行效率。

2、分析收集来的数据并画出图表。查找出特殊情况,例如很高的磁盘和CPU占用、已经高的磁盘使用时间

3、调整服务器

4、重复第一到第三步直到达到你渴望的性能

与NFS性能有关的问题有很多,通常可以要考虑的有以下这些选择:

WSIZE,RSIZE参数来优化NFS的执行效能

WSIZE、RSIZE对于NFS的效能有很大的影响。

wsize和rsize设定了SERVER和CLIENT之间往来数据块的大小,这两个参数的合理设定与很多方面有关,不仅是软件方面也有硬件方面的因素会影响这两个参数的设定(例如LINUX KERNEL、网卡,交换机等等)。

下面这个命令可以测试NFS的执行效能,读和写的效能可以分别测试,分别找到合适的参数。对于要测试分散的大量的数据的读写可以通过编写脚本来进行测试。在每次测试的时候最好能重复的执行一次MOUNT和unmount。

time dd if=/dev/zero of=/mnt/home/testfile bs=16k count=16384

用于测试的WSIZE,RSIZE最好是1024的倍数,对于NFS V2来说8192是RSIZE和WSIZE的最大数值,如果使用的是NFS V3则可以尝试的最大数值是32768。

如果设置的值比较大的时候,应该最好在CLIENT上进入mount上的目录中,进行一些常规操作(LS,VI等等),看看有没有错误信息出现。有可能 出现的典型问题有LS的时候文件不能完整的列出或者是出现错误信息,不同的操作系统有不同的最佳数值,所以对于不同的操作系统都要进行测试。

设定最佳的NFSD的COPY数目。

linux中的NFSD的COPY数目是在/etc/rc.d/init.d/nfs这个启动文件中设置的,默认是8个NFSD,对于这个参数的设置一般是要根据可能的CLIENT数目来进行设定的,和WSIZE、RSIZE一样也是要通过测试来找到最近的数值。

UDP and TCP

可以手动进行设置,也可以自动进行选择。

mount -t nfs -o sync,tcp,noatime,rsize=1024,wsize=1024 EXPORT_MACHINE:/EXPORTED_DIR /DIR

UDP有着传输速度快,非连接传输的便捷特性,但是UDP在传输上没有TCP来的稳定,当网络不稳定或者黑客入侵的时候很容易使NFS的 Performance 大幅降低甚至使网络瘫痪。所以对于不同情况的网络要有针对的选择传输协议。nfs over tcp比较稳定, nfs over udp速度较快。在机器较少网络状况较好的情况下使用UDP协议能带来较好的性能,当机器较多,网络情况复杂时推荐使用TCP协议(V2只支持UDP协 议)。在局域网中使用UDP协议较好,因为局域网有比较稳定的网络保证,使用UDP可以带来更好的性能,在广域网中推荐使用TCP协议,TCP协议能让 NFS在复杂的网络环境中保持最好的传输稳定性。可以参考这篇文章:http: //www.hp.com.tw/ssn/unix/0212/unix021204.asp

版本的选择

V3作为默认的选择(RED HAT 8默认使用V2,SOLARIS 8以上默认使用V3),可以通过vers= mount option来进行选择。

LINUX通过mount option的nfsvers=n进行选择。

五、NFS故障解决

1、NFSD没有启动起来

首先要确认 NFS 输出列表存在,否则 nfsd 不会启动。可用 exportfs 命令来检查,如果 exportfs 命令没有结果返回或返回不正确,则需要检查 /etc/exports 文件。

2、mountd 进程没有启动

mountd 进程是一个远程过程调用 (RPC) ,其作用是对客户端要求安装(mount)文件系统的申请作出响应。mountd进程通过查找 /etc/xtab文件来获知哪些文件系统可以被远程客户端使用。另外,通过mountd进程,用户可以知道目前有哪些文件系统已被远程文件系统装配,并 得知远程客户端的列表。查看mountd是否正常启动起来可以使用命令rpcinfo进行查看,在正常情况下在输出的列表中应该象这样的行:

100005 1 udp 1039 mountd

100005 1 tcp 1113 mountd

100005 2 udp 1039 mountd

100005 2 tcp 1113 mountd

100005 3 udp 1039 mountd

100005 3 tcp 1113 mountd

如果没有起来的话可以检查是否安装了PORTMAP组件。

rpm -qa|grep portmap

3、fs type nfs no supported by kernel

kernel不支持nfs文件系统,重新编译一下KERNEL就可以解决。

4、can’t contact portmapper: RPC: Remote system error – Connection refused

出现这个错误信息是由于SEVER端的PORTMAP没有启动。

5、mount clntudp_create: RPC: Program not registered

NFS没有启动起来,可以用showmout -e host命令来检查NFS SERVER是否正常启动起来。

6、mount: localhost:/home/test failed, reason given by server: Permission denied

这个提示是当client要mount nfs server时可能出现的提示,意思是说本机没有权限去mount nfs server上的目录。解决方法当然是去修改NFS SERVER咯。

7、被防火墙阻挡

这个原因很多人都忽视了,在有严格要求的网络环境中,我们一般会关闭linux上的所有端口,当需要使用哪个端口的时候才会去打开。而NFS默认是使用111端口,所以我们先要检测是否打开了这个端口,另外也要检查TCP_Wrappers的设定。

六、NFS安全

NFS的不安全性主要体现于以下4个方面:

1、新手对NFS的访问控制机制难于做到得心应手,控制目标的精确性难以实现

2、NFS没有真正的用户验证机制,而只有对RPC/Mount请求的过程验证机制

3、较早的NFS可以使未授权用户获得有效的文件句柄

4、在RPC远程调用中,一个SUID的程序就具有超级用户权限.

加强NFS安全的方法:

1、合理的设定/etc/exports中共享出去的目录,最好能使用anonuid,anongid以使MOUNT到NFS SERVER的CLIENT仅仅有最小的权限,最好不要使用root_squash。

2、使用IPTABLE防火墙限制能够连接到NFS SERVER的机器范围

iptables -A INPUT -i eth0 -p TCP -s 192.168.0.0/24 –dport 111 -j ACCEPT

iptables -A INPUT -i eth0 -p UDP -s 192.168.0.0/24 –dport 111 -j ACCEPT

iptables -A INPUT -i eth0 -p TCP -s 140.0.0.0/8 –dport 111 -j ACCEPT

iptables -A INPUT -i eth0 -p UDP -s 140.0.0.0/8 –dport 111 -j ACCEPT

3、为了防止可能的Dos攻击,需要合理设定NFSD 的COPY数目。

4、修改/etc/hosts.allow和/etc/hosts.deny达到限制CLIENT的目的

/etc/hosts.allow

portmap: 192.168.0.0/255.255.255.0 : allow

portmap: 140.116.44.125 : allow

/etc/hosts.deny

portmap: ALL : deny

5、改变默认的NFS 端口

NFS默认使用的是111端口,但同时你也可以使用port参数来改变这个端口,这样就可以在一定程度上增强安全性。

6、使用Kerberos V5作为登陆验证系统

本文来自:http://doc.linuxpk.com/4037.html

how to check kernel is 32 bit or 64 bit

Filed under: Others — jennyca @ 3:46 pm

how to check linux kernel is 32 bit or 64 bit

$ uname -m

$ file /sbin/init

How to check HP-UX kernel 32bit or 64 bit

HP-UX 11.x version offers options to run 32 bit or 64 bit kernel, earlier release before HP-UX 11.x can only run 32 bit kernel. If you don’t have administrative rights or root account access you can still check whether you are running 32 bit or 64 bit kernel. Since there are many ways to check if HP-UX kernel is running 32 bit/64 bit mode, here are some commands you can run without requiring root access:

From UNIX shell prompt try this:

$getconf KERNEL_BITS

64

or running file command on /stand/vmunix to check if kernel type is 32 bit or 6bit

$file /stand/vmunix

vmunix: ELF-64 executable object file – PA-RISC 2.0(LP64)

Also if you have root privileges you check kernel type by running SAM

1) Go to Performance Monitors -> System Properties -> Operating System

2) Check OS Kernel Width: value

How to check if HP-UX hardware supports 32 bit or 64 bit kernel?

#getconf HW_CPU_SUPP_BITS

64

In this example HP-UX hardware is 64 bit enabled.

#getconf HW_CPU_SUPP_BITS

32/64

In this exampled HP-UX hardware supports both 32 bit and 64 bit kernel In HP-UX you can also check if OS is running in 32 bit or 64 bit mode by using print_manifest command

#print_manifest |grep -i ‘os mode’

OS mode: 64 bit

How to check AIX 5.x kernel 64bit or 32bit?

In AIX 5.x, you have options to choose a 32-bit or 64-bit kernel and it is important to know that both 32-bit and 64-bit kernel supports running 64-bit applications. Both 32 bit and 64 bit kernel also support advance JFS2 filesystem and large filesystem. But it’s is always good to have 64-bit kernel even though you have 32-bit applications to run on newer hardware. New p-series server has better performance running 64-bit kernel as we have benchmarked on our production servers.64 bit kernel in AIX provides better JFS2 performance and it support large memory up to 96 GB.

If you have plan to configure your p-series to run large memory (96 GB) and want to use JFS2 filesystem you should use 64-bit kernel on AIX.If you want to run only 32 bit applications then you can decide to run AIX kernel in 32-bit mode.

Now question is How to check on your AIX 5.x server if your system is 32bit or 64 bit enabled. All aix machines since RS/6000 H70 are 64 bit. To check if your AIX hardware is enabled to run 32 bit or 64 bit kernel you can run bootinfo –y :

#bootinfo -y

The bootinfo –K command returns the current kernel bit mode, it returns value in integer format as 32 or 64:

#bootinfo –K

64

Running svmon –p <pid> returns information if your application process is running in 32 bit or 64 bit mode. If your AIX kernel is running in 32bit mode you can switch to 64 bit mode, but you need to make sure you have bos.mp64 fileset is already installed. Here is how you can change your AIX kernel from 32 bit to 64 bit mode:

#ln –sf /usr/lib/boot/unix_64 /unix

#ln –sf /usr/lib/boot/unix_64 /usr/lib/boot/unix

#bosboot –ak /usr/lib/boot/unix_64

#shutdown -Fr

Once server is rebooted, AIX kernel will be changed to 64 bit mode.

How to check Solaris kernel is 32bit or 64 bit

To check if Solaris kernel is running in 32 bit mode or 64 bit from command line run the following:

#/usr/bin/isainfo –kv

64-bit sparcv9 kernel modules

The output “64 bit sparcv9″ indicates that Solaris kernel is running in 64 bit mode. Here is the output of Solaris kernel running in 32 bit:

#/usr/bin/isainfo –kv

32-bit sparcv9 kernel modules

How to check Solaris kernel on x86 (Intel) platform :

bash-3.00# uname –m

i86pc

bash-3.00# /usr/bin/isainfo -kv

32-bit i386 kernel modules

In the example above Solaris Kernel is running in 32 bit on intel platform, printed as “32-bit” in the output.

How to check if Solaris kernel is configured to boot in 32 bit or 64:

The system is configured to boot the 64-bit kernel if the output from the eeprom command is either:

# /usr/sbin/eeprom | grep boot-file

boot-file: data not available

or

boot-file=kernel/sparcv9/unix

else if Solaris system is configured to boot using 32-bit kernel, the output from the eeprom command comes back as:

kernel/unix

http://sysdigg.blogspot.com/2007/12/how-to-check-hp-ux-kernel-32bit-or-64.html

How can I determine if my computer/operating system is 64-bit?

Filed under: Others — jennyca @ 3:37 pm

Windows

Click Start -> Run, and type dxdiag. If a Windows dialog box is displayed asking if you want to verify your drivers, you can safely click no and continue. When the program has finished loading, you should see something similar to one of the following images:

The Operating System line, refers to Windows <some version> x64 or 64-bit edition. The last window specifies the processor Itanium, which is a 64-bit processor.

Macintosh

You must be running Mac OS X 10.5 (Leopard) or newer and have a Core 2 Duo or G5 processor for 64-bit support.* To determine if your computer meets both requirements, select About This Mac from the Apple menu. A window will appear from which you can determine the version of Mac OS X and the processor type installed on your computer.

* A 64-bit version of Stata 10 for the Macintosh is expected to be available during the second half of 2008. An announcement will be made once it is available.

Unix (IBM-AIX, Sun Solaris)

  • IBM-AIX

    The getconf command will return the configuration of your machine. Since you are looking only for the kernel parameters, you should type

    [cph@anna ~]$ getconf -a | grep KERN
    KERNEL_BITMODE: 64

    If that fails or does not return a result, try the file command.

    [cph@anna ~]$ file /usr/lib/boot/unix*
    /usr/lib/boot/unix: 64-bit XCOFF executable or object module not stripped
    /usr/lib/boot/unix_64: 64-bit XCOFF executable or object module not stripped
    /usr/lib/boot/unix_mp: executable (RISC System/6000) or object module not stripped
    /usr/lib/boot/unix_up: executable (RISC System/6000) or object module not stripped
    [cph@ozona ~]$ file /usr/lib/boot/unix*
    /usr/lib/boot/unix: symbolic link to /usr/lib/boot/unix_up.
    /usr/lib/boot/unix_kdb: executable (RISC System/6000) or object module not stripped
    /usr/lib/boot/unix_up: executable (RISC System/6000) or object module not stripped

    We can see that ‘anna’ is a 64-bit capable platform, and ‘ozona’ is only 32-bit.

  • Sun Solaris

    Sun’s platforms transitioned to 64-bit over a period of time, making identifying the platform for 64-bit compliance tricky. Sun has stated:

    Sun has implemented its 64-bit operating system in phases. The Solaris 2.5 Operating Environment provided support for increased precision with 64-bit math and also included support for 64-bit asynchronous I/O. The Solaris 2.6 Operating Environment added support for large datasets with large (1 Terabyte) filesystems while allowing co-existence of 32-bit and 64-bit files. Beginning with the Solaris 7 Operating Environment, Sun introduced support for large virtual address spaces with a full 64-bit operating system. Sun began shipping 64-bit hardware in 1995. By phasing in operating system support for 64-bits Sun has provided 64-bit features as the market has demanded them while guaranteeing compatibility for existing 32-bit applications.

    To determine what kind of binaries your Solaris machine can run, try the following:

    eden:/home/cph: isainfo -v
    64-bit sparcv9 applications
    32-bit sparc applications

    You can see that this Solaris machine is capable of running 32-bit as well as 64-bit applications. If isainfo fails, running a 64-bit application on your Sun will generally not be possible, unless of course isainfo is not in your path or has not been installed. You can also use the uname command to glean more information:

    eden:/home/cph: uname -a
    SunOS eden 5.8 Generic_108528-05 sun4u sparc SUNW,Ultra-5_10
    lagrange:/usr/users/cph: uname -a
    SunOS lagrange 5.5.1 Generic_103640-29 sun4u sparc SUNW,Ultra-1

    SunOS/Solaris versions map to the following:

    SunOS SOLARIS
    5.3 2.3
    5.4 2.4
    5.5 2.5
    5.5.1 2.5.1
    5.6 2.6
    5.7 or greater is 64-bit compliant 2.7 or greater is 64-bit compliant

    While all UltraSparc processors are capable of 64-bit computing, the OS release level may be the limiting factor. In the above excerpt, ‘eden’ (SunOS 5.8) is a fully compliant 64-bit platform, and ‘lagrange’ (SunOS 5.5.1) is a 64-bit UltraSparc limited to 32-bit computing.

Linux

Linux users should type the uname command. Depending on the platform, you may see

[cph@gaylord ~]$ uname -a
Linux gaylord.stata.com 2.6.11-1.27_FC3 #1 Tue May 17 20:24:57 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux
[cph@caddo ~]$ uname -a
Linux caddo.stata.com 2.6.9-5.0.5.EL #1 SMP Fri Apr 8 14:20:58 EDT 2005 ia64 ia64 ia64 GNU/Linux
[cph@tango ~]$ uname -a
Linux tango.stata.com 2.6.10-1.771_FC2smp #1 SMP Mon Mar 28 01:10:51 EST 2005 i686 i686 i386 GNU/Linux

In the above listing, ‘gaylord’ (x86_64 GNU/Linux) and ‘caddo’ (ia64 GNU/Linux) are 64-bit compliant. ‘tango’ (i386 GNU/Linux) is only a 32-bit platform.

http://www.stata.com/support/faqs/win/64bit.html

July 21, 2008

“Argument list too long”

Filed under: UNIX/LINUX — jennyca @ 2:30 am

Four approaches to getting around argument length limitations on the command line.

At some point during your career as a Linux user, you may have come across the following error:

[user@localhost directory]$ mv * ../directory2
bash: /bin/mv: Argument list too long

The “Argument list too long” error, which occurs anytime a user feeds too many arguments to a single command, leaves the user to fend for oneself, since all regular system commands (ls *, cp *, rm *, etc…) are subject to the same limitation. This article will focus on identifying four different workaround solutions to this problem, each method using varying degrees of complexity to solve different potential problems. The solutions are presented below in order of simplicity, following the logical principle of Occam’s Razor: If you have two equally likely solutions to a problem, pick the simplest.

Method #1: Manually split the command line arguments into smaller bunches.

Example 1

[user@localhost directory]$ mv [a-l]* ../directory2
[user@localhost directory]$ mv [m-z]* ../directory2

This method is the most basic of the four: it simply involves resubmitting the original command with fewer arguments, in the hope that this will solve the problem. Although this method may work as a quick fix, it is far from being the ideal solution. It works best if you have a list of files whose names are evenly distributed across the alphabet. This allows you to establish consistent divisions, making the chore slightly easier to complete. However, this method is a poor choice for handling very large quantities of files, since it involves resubmitting many commands and a good deal of guesswork.

Method #2: Use the find command.

Example 2

[user@localhost directory]$ find $directory -type f -name '*' -exec mv
{} $directory2/. \;

Method #2 involves filtering the list of files through the find command, instructing it to properly handle each file based on a specified set of command-line parameters. Due to the built-in flexibility of the find command, this workaround is easy to use, successful and quite popular. It allows you to selectively work with subsets of files based on their name patterns, date stamps, permissions and even inode numbers. In addition, and perhaps most importantly, you can complete the entire task with a single command.

The main drawback to this method is the length of time required to complete the process. Unlike Method #1, where groups of files get processed as a unit, this procedure actually inspects the individual properties of each file before performing the designated operation. The overhead involved can be quite significant, and moving lots of files individually may take a long time.

Method #3: Create a function. *

Example 3a

function large_mv ()
{       while read line1; do
                mv directory/$line1 ../directory2
        done
}
ls -1 directory/ | large_mv

Although writing a shell function does involve a certain level of complexity, I find that this method allows for a greater degree of flexibility and control than either Method #1 or #2. The short function given in Example 3a simply mimics the functionality of the find command given in Example 2: it deals with each file individually, processing them one by one. However, by writing a function you also gain the ability to perform an unlimited number of actions per file still using a single command:

Example 3b

function larger_mv ()
{       while read line1; do
                md5sum directory/$line1 >>  ~/md5sums
                ls -l directory/$line1 >> ~/backup_list
                mv directory/$line1 ../directory2
        done
}
ls -1 directory/ | larger_mv

Example 3b demonstrates how you easily can get an md5sum and a backup listing of each file before moving it.

Unfortunately, since this method also requires that each file be dealt with individually, it will involve a delay similar to that of Method #2. From experience I have found that Method #2 is a little faster than the function given in Example 3a, so Method #3 should be used only in cases where the extra functionality is required.

Method #4: Recompile the Linux kernel. **

This last method requires a word of caution, as it is by far the most aggressive solution to the problem. It is presented here for the sake of thoroughness, since it is a valid method of getting around the problem. However, please be advised that due to the advanced nature of the solution, only experienced Linux users should attempt this hack. In addition, make sure to thoroughly test the final result in your environment before implementing it permanently.

One of the advantages of using an open-source kernel is that you are able to examine exactly what it is configured to do and modify its parameters to suit the individual needs of your system. Method #4 involves manually increasing the number of pages that are allocated within the kernel for command-line arguments. If you look at the include/linux/binfmts.h file, you will find the following near the top:

/*
 * MAX_ARG_PAGES defines the number of pages allocated for   arguments
 * and envelope for the new program. 32 should suffice, this gives
 * a maximum env+arg of 128kB w/4KB pages!
 */
#define MAX_ARG_PAGES 32

In order to increase the amount of memory dedicated to the command-line arguments, you simply need to provide the MAX_ARG_PAGES value with a higher number. Once this edit is saved, simply recompile, install and reboot into the new kernel as you would do normally.

On my own test system I managed to solve all my problems by raising this value to 64. After extensive testing, I have not experienced a single problem since the switch. This is entirely expected since even with MAX_ARG_PAGES set to 64, the longest possible command line I could produce would only occupy 256KB of system memory–not very much by today’s system hardware standards.

The advantages of Method #4 are clear. You are now able to simply run the command as you would normally, and it completes successfully. The disadvantages are equally clear. If you raise the amount of memory available to the command line beyond the amount of available system memory, you can create a D.O.S. attack on your own system and cause it to crash. On multiuser systems in particular, even a small increase can have a significant impact because every user is then allocated the additional memory. Therefore always test extensively in your own environment, as this is the safest way to determine if Method #4 is a viable option for you.

Conclusion

While writing this article, I came across many explanations for the “Argument list too long” error. Since the error message starts with “bash:”, many people placed the blame on the bash shell. Similarly, seeing the application name included in the error caused a few people to blame the application itself. Instead, as I hope to have conclusively demonstrated in Method #4, the kernel itself is to “blame” for the limitation. In spite of the enthusiastic endorsement given by the original binfmts.h author, many of us have since found that 128KB of dedicated memory for the command line is simply not enough. Hopefully, by using one of the methods above, we can all forget about this one and get back to work.

Notes:

* All functions were written using the bash shell.

http://www.linuxjournal.com/article/6060

monitor RMAN progress scripts

Filed under: scripts — jennyca @ 12:27 am

RMAN> run {
set command id to ‘rman’;
allocate channel c1 type disk format ‘D:\oracle\backup\inconsistent\%d_%s_%p.dbf’;
backup
incremental level 0
tag “Full hot backup”
(database);

backup current controlfile tag=’current’;
release channel c1;

allocate channel c1 type disk format ‘D:\oracle\backup\inconsistent\arch_%d_%u_%s_%p’;
change archivelog all validate;

backup archivelog all;

sql “alter database backup controlfile to ”D:\ORACLE\BACKUP\INCONSISTENT720_bkp.ctl””;
release channel c1;
}

SQL*PLUS session
—————-

SQL> SELECT sid, spid, client_info
2 FROM v$process p, v$session s
3 WHERE p.addr = s.paddr
4 AND client_info LIKE ‘%id=rman%’;

SID SPID CLIENT_INFO
———- ———— ——————–
143 3388 id=rman

SQL> SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) “% Complete”
FROM v$session_longops
WHERE opname LIKE ‘RMAN%’
AND opname NOT LIKE ‘%aggregate%’
AND totalwork != 0
AND sofar <> totalwork
/

SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
———- ———- ———- ———- ———- ———-
139 67 1 56316 95360 59.06

SQL> /
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
———- ———- ———- ———- ———- ———-
139 67 1 81534 95360 85.5

SQL> /
no rows selected

SQL> SELECT sid, seconds_in_wait AS sec_wait, event FROM v$session_wait
WHERE wait_time = 0
ORDER BY sid;

Note: The V$SESSION_WAIT view shows only Oracle events, not media manager events.

RMAN> run {
allocate channel t1 type disk;
debug io;
backup database;
debug off;
}

You can change the option to “debug on” which is equivlent to “debug all” to
get even more detail about the sql and pl/sql being executed by RMAN.

July 20, 2008

tablespaces and datafiles used space and free space

Filed under: scripts — jennyca @ 6:32 pm

SELECT a.tablespace_name, a.file_name, a.bytes/1024/1024 ALLOCATED_MB,
b.free_bytes/1024/1024 FREE_MB
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
ORDER BY a.tablespace_name;

July 13, 2008

index and constraint

Filed under: scripts — jennyca @ 10:40 pm

How can I check if I have the right indexes for the foreign key constraints on a child table ?

Since any index always add some space and resource overhead, it is probably unwise to blindly index every foreign key. But, if there are legitimate locking or query performance benefits to be made, the following script authored by Tom Kyte gives a list of foreign keys, their columns and a flag indicating whether the appropriate index exists.

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,
	   a.table_name, a.columns, b.columns
from
( select substr(a.table_name,1,30) table_name,
		 substr(a.constraint_name,1,30) constraint_name,
	     max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
	     max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
	     max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||
	     max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
	     max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_ind_columns
   group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
/

http://www.jlcomp.demon.co.uk/faq/fk_ind.html

How can I identify which index represents which primary or unique key constraint ?

The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query:

        select  --+ rule
            o.owner as index_owner
            , o.object_name as index_name
            , n.name as constraint_name
        from    sys.cdef$ c
            , dba_objects o
            , sys.con$ n
        where c.enabled = o.object_id
        and c.con# = n.con#
        and n.owner# = uid
        /
       SQL> select uid from dba_users where username like '%RXADMIN%';

If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name'.

Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are unique as well.

Further, these indexes may also be non-unique. You need this if you have a deferred constraint that is checked only at commit time. If you would insist on a unique index the attempt to insert duplicate values would fail before the commit although another command may have undone the duplicate entry.

http://www.jlcomp.demon.co.uk/faq/constraints_and_indexes.html

unique index vs unique constraint

Filed under: Oracle Dev — jennyca @ 10:20 pm

A constraint is to define a business rule for a column of a table.

Constraints to be referential integrity rules that govern the allowable contents of a column and
in the case of a primary key (PK) and unique key (UK) constraint, in conjunction with foreign keys
(FK), define the formal relationship between columns and rows in one table to another.

The difference between a unique index and a unique key/primary key constraint starts with the fact
that the constraint is a rule while the index is a database object that is used to provide improved
performance in the retrieval of rows from a table. It is a physical object that takes space and is
created with the DDL command: create index or as part of a create table with primary key or unique
key constraints or an alter table command that adds these constraints.

Constraint types

Not Null – Column value must be present.
Unique Key – Column(s) value(s) must be unique in table or null.
Primary Key – Unique key + Not Null which equates to every column in the key must have a value and
this value is unique so the primary key uniquely identifies each and every row in the table.
Foreign Key – Restricts values in a table column to being a value found in the primary key or unique
key Constraint on the referenced table (parent/child relationship).
Check – Tests the column value against an expression (rule).

Technically it would be possible for Oracle, to support primary key and unique key constraints
without using an index at all. In the case of a unique key or primary key constraint Oracle could
perform a full table scan to check for the presence of a key value before performing the insert
but the performance cost of doing this for anything other than a very small table would be excessive
probably rendering Oracle useless.

Prior to Oracle 8 if you defined a primary key or a unique key constraint the Oracle RDBMS would
create a unique index to support enforcement of the constraint. If an index already existed on the
constrained columns Oracle would use it rather than define another index on the same columns.

Starting with Oracle version 8 Oracle has the ability to enforce primary key and unique key constraints
using non-unique indexes. The use of non-unique indexes supports deferring enforcement of the constraint
until transaction commit time if the constraint is defined at creation time as deferrable. Also starting
with version 8 Oracle has the ability to place constraints on tables where the existing data does not
meet the requirements imposed by the constraint through use of a novalidate option.

The practical difference between using a unique index to support data integrity and a unique key or
primary key on the same columns since Oracle will build an index to support the constraint if you do not
is that you can define foreign key constraints when the primary key or unique key constraint exist.
Also in the case of a primary key constraint Oracle will convert the columns in the constraint to be
not null constrained when it is added to meet the primary key requirement to uniquely identify each and
every row in the table.

There is no such restriction on a unique index. The primary key and unique key constraints along with
foreign key constraints that reference them also provide a form of documentation on the relationships
between objects.

The Oracle RDBMS Data Dictionary views All/DBA/USER_CONSTRAINTS and ALL/DBA/USER_CONS_COLUMNS may be used
to locate constraints on a table and the columns being constrained.

If you drop or disable a primary key or unique key constraint that is supported by a unique index the index
is dropped with the constraint. If a non-unique index is used to support the constraint the index is not
dropped with the constraint. This second condition is effective only with version 8 and higher.

Note: Unique key constraints allow the constrained column to be NULL. Nulls values are considered to be
valid and do not violate the constraint.

http://www.jlcomp.demon.co.uk/faq/uk_idx_con.html
http://askanantha.blogspot.com/2007/09/difference-between-unique-index-and.html

Next Page »

Blog at WordPress.com.