Monday, 17 May 2010

Oracle DBA's useful linux commands

Most of us knows how to use chown and chgrp commands to change ownership and group of the files. Say you have several files like this:

# ls -l
total 8
-rw-r--r--    1 user1     users          70 Aug  4 04:02 file1
-rwxr-xr-x    1 oracle   dba           132 Aug  4 04:02 file2
-rwxr-xr-x    1 oracle   dba           132 Aug  4 04:02 file3
-rwxr-xr-x    1 oracle   dba           132 Aug  4 04:02 file4
-rwxr-xr-x    1 oracle   dba           132 Aug  4 04:02 file5
-rwxr-xr-x    1 oracle   dba           132 Aug  4 04:02 file6

and you need to change the permissions of all the files to match those of file1. Sure, you could issue chmod 644 * to make that change—but what if you are writing a script to do that, and you don’t know the permissions beforehand? Or, perhaps you are making several permission changes and based on many different files and you find it infeasible to go though the permissions of each of those and modify accordingly.

A better approach is to make the permissions similar to those of another file. This command makes the permissions of file2 the same as file1:

chmod --reference file1 file2
Now if you check:
# ls -l file[12]
total 8
-rw-r--r--    1 user1   users          70 Aug  4 04:02 file1
-rw-r--r--    1 oracle   dba           132 Aug  4 04:02 file2

The file2 permissions were changed exactly as in file1. You didn’t need to get the permissions of file1 first.

You can also use the same trick in group membership in files. To make the group of file2 the same as file1, you would issue:

# chgrp --reference file1 file2
# ls -l file[12]
-rw-r--r--    1 user1   users          70 Aug  4 04:02 file1
-rw-r--r--    1 oracle   users         132 Aug  4 04:02 file2

Of course, what works for changing groups will work for owner as well. Here is how you can use the same trick for an ownership change. If permissions

are like this:

# ls -l file[12]
-rw-r--r--    1 user1   users          70 Aug  4 04:02 file1
-rw-r--r--    1 oracle   dba           132 Aug  4 04:02 file2

You can change the ownership like this:

# chown --reference file1 file2
# ls -l file[12]
-rw-r--r--    1 user1   users          70 Aug  4 04:02 file1
-rw-r--r--    1 user1   users         132 Aug  4 04:02 file2

Note that the group as well as the owner have changed.

This is a trick you can use to change ownership and permissions of Oracle executables in a directory based on some reference executable. This proves

especially useful in migrations where you can (and probably should) install as a different user and later move them to your regular Oracle software owner.

The command cmp is similar to diff:
# cmp file1 file2
file1 file2 differ: byte 10, line 1

The output comes back as the first sign of difference. You can use this to identify where the files might be different. Like diff, cmp has a lot of options, the

most important being the -s option, that merely returns a code:

0, if the files are identical
1, if they differ
Some other non-zero number, if the comparison couldn’t be made

Here is an example:

# cmp -s file3 file4
# echo $?

The special variable $? indicates the return code from the last executed command. In this case it’s 0, meaning the files file1 and file2 are identical.

# cmp -s file1 file2
# echo $?

means file1 and file2 are not the same.

Recall from a previous tip that when you relink Oracle executables, the older version is kept prior to being overwritten. So, when you relink, the executable sqlplus is renamed to “sqlplusO” and the newly compiled sqlplus is placed in the $ORACLE_HOME/bin. So how do you ensure that the sqlplus

that was just created is any different? Just use:

# cmp sqlplus sqlplusO
sqlplus sqlplusO differ: byte 657, line 7

If you check the size:
# ls -l sqlplus*
-rwxr-x--x    1 oracle   dba          8851 Aug  4 05:15 sqlplus
-rwxr-x--x    1 oracle   dba          8851 Nov  2  2005 sqlplusO

Even though the size is the same in both cases, cmp proved that the two programs differ

This command generates a 32-bit MD5 hash value of the files:
# md5sum file1
ef929460b3731851259137194fe5ac47  file1

Two files with the same checksum can be considered identical. However, the usefulness of this command goes beyond just comparing files. It can also provide a mechanism to guarantee the integrity of the files.

Suppose you have two important files—file1 and file2—that you need to protect. You can use the --check option check to confirm the files haven't changed. First, create a checksum file for both these important files and keep it safe:

# md5sum file1 file2 > f1f2
Later, when you want to verify that the files are still untouched:

# md5sum --check f1f2    
file1: OK
file2: OK

This shows clearly that the files have not been modified. Now change one file and check the MD5:

# cp file2 file1
# md5sum --check f1f2
file1: FAILED
file2: OK
md5sum: WARNING: 1 of 2 computed checksums did NOT match

The output clearly shows that file1 has been modified.

md5sum is an extremely powerful command for security implementations. Some of the configuration files you manage, such as listener.ora, tnsnames.ora, and init.ora, are extremely critical in a successful Oracle infrastructure and any modification may result in downtime. These are typically a part of your change control process. Instead of just relying on someone’s word that these files have not changed, enforce it using MD5 checksum. Create a checksum file and whenever you make a planned change, recreate this file. As a part of your compliance, check this file using the md5sum command. If someone inadvertently updated one of these key files, you would immediately catch the change.

In the same line, you can also create MD5 checksums for all executables in $ORACLE_HOME/bin and compare them from time to time for unauthorized modifications.

alias and unalias

Suppose you want to check the ORACLE_SID environment variable set in your shell. You will have to type:

As a DBA or a developer, you frequently use this command and will quickly become tired of typing the entire 16 characters. Is there is a simpler way?

There is: the alias command. With this approach you can create a short alias, such as "os", to represent the entire command:

alias os='echo $ORACLE_HOME'
Now whenever you want to check the ORACLE_SID, you just type "os" (without the quotes) and Linux executes the aliased command.

However, if you log out and log back in, the alias is gone and you have to enter the alias command again. To eliminate this step, all you have to do is to put the command in your shell's profile file. For bash, the file is .bash_profile (note the period before the file name, that's part of the file's name) in your home

directory. For bourne and korn shells, it's .profile, and for c-shell, .chsrc.

You can create an alias in any name. For instance, I always create an alias for the command sqlplus "/as sysdba",
alias sql=’sqlplus "/as sysdba"

Here is a list of some very useful aliases I like to define:

alias bdump='cd $ORACLE_BASE/admin/$ORACLE_SID/bdump'
alias l='ls -d .* --color=tty'
alias ll='ls -l --color=tty'
alias mv='mv -i'
alias oh='cd $ORACLE_HOME'
alias os='echo $ORACLE_SID'
alias tns='cd $ORACLE_HOME/network/admin'

To see what aliases have been defined in your shell, use alias without any parameters

To remove an alias previously defined, just use the unalias command:

$ unalias rm


Most Linux commands are about getting an output: a list of files, a list of strings, and so on. But what if you want to use some other command with the output of the previous one as a parameter? For example, the file command shows the type of the file (executable, ascii text, and so on); you can manipulate the output to show only the filenames and now you want to pass these names to the ls -l command to see the timestamp. The command xargs

does exactly that. It allows you to execute some other commands on the output.

file -Lz * | grep ASCII | cut -d":" -f1 | xargs ls -ltr

Let's dissect this command string. The first, file -Lz *, finds files that are symbolic links or compressed. It passes the output to the next command, grep

ASCII, which searches for the string "ASCII" in them and produces the output similar to this:
alert_DBA102.log:         ASCII English text
alert_DBA102.log.Z:       ASCII text (compress'd data 16 bits)
dba102_asmb_12307.trc.Z: ASCII English text (compress'd data 16 bits)
dba102_asmb_20653.trc.Z: ASCII English text (compress'd data 16 bits)

Since we are interested in the file names only, we applied the next command, cut -d":" -f1, to show the first field only:

Now, we want to use the ls -l command and pass the above list as parameters, one at a time. The xargs command allowed you to to that. The last part,

xargs ls -ltr, takes the output and executes the command ls -ltr against them, as if executing:

ls -ltr alert_DBA102.log
ls -ltr alert_DBA102.log.Z
ls -ltr dba102_asmb_12307.trc.Z
ls -ltr dba102_asmb_20653.trc.Z

Thus xargs is not useful by itself, but is quite powerful when combined with other commands.

Here is another example, where we want to count the number of lines in those files:

$ file * | grep ASCII | cut -d":" -f1  | xargs wc -l
  47853 alert_DBA102.log
     19 dba102_cjq0_14493.trc
  29053 dba102_mmnl_14497.trc
    154 dba102_reco_14491.trc
     43 dba102_rvwr_14518.trc
  77122 total

(Note: the above task can also be accomplished with the following command:)

$ wc -l ‘file * | grep ASCII | cut -d":" -f1 | grep ASCII | cut -d":" -f1‘

The xargs version is given to illustrate the concept. Linux has several ways to achieve the same task; use the one that suits your situation best.

Using this approach you can quickly rename files in a directory.

$ ls | xargs -t -i mv {} {}.bak

The -i option tells xargs to replace {} with the name of each item. The -t option instructs xargs to print the command before executing it.

Another very useful operation is when you want to open the files for editing using vi:

$ file * | grep ASCII | cut -d":" -f1 | xargs vi

This command opens the files one by one using vi. When you want to search for many files and open them for editing, this comes in very handy.

It also has several options. Perhaps the most useful is the -p option, which makes the operation interactive:

$ file * | grep ASCII | cut -d":" -f1 | xargs -p vi
vi alert_DBA102.log dba102_cjq0_14493.trc dba102_mmnl_14497.trc   dba102_reco_14491.trc dba102_rvwr_14518.trc ?...

Here xarg asks you to confirm before running each command. If you press "y", it executes the command. You will find it immensely useful when you take some potentially damaging and irreversible operations on the file—such as removing or overwriting it.

The -t option uses a verbose mode; it displays the command it is about to run, which is a very helpful option during debugging.

What if the output passed to the xargs is blank? Consider:

$ file * | grep SSSSSS | cut -d":" -f1 | xargs -t wc -l
wc -l

Here searching for "SSSSSS" produces no match; so the input to xargs is all blanks, as shown in the second line (produced since we used the -t, or the

verbose option). Although this may be useful, In some cases you may want to stop xargs if there is nothing to process; if so, you can use the -r option:
$ file * | grep SSSSSS | cut -d":" -f1 | xargs -t -r wc -l

The command exits if there is nothing to run.

Suppose you want to remove the files using the rm command, which should be the argument to the xargs command. However, rm can accept a limited

number of arguments. What if your argument list exceeds that limit? The -n option to xargs limits the number of arguments in a single command line.

Here is how you can limit only two arguments per command line: Even if five files are passed to xargs ls -ltr, only two files are passed to ls -ltr at a time.

$ file * | grep ASCII | cut -d":" -f1 | xargs -t -n2 ls -ltr
ls -ltr alert_DBA102.log dba102_cjq0_14493.trc
-rw-r-----    1 oracle   dba           738 Aug 10 19:18 dba102_cjq0_14493.trc
-rw-r--r--    1 oracle   dba       2410225 Aug 13 05:31 alert_DBA102.log
ls -ltr dba102_mmnl_14497.trc dba102_reco_14491.trc
-rw-r-----    1 oracle   dba       5386163 Aug 10 17:55 dba102_mmnl_14497.trc
-rw-r-----    1 oracle   dba          6808 Aug 13 05:21 dba102_reco_14491.trc
ls -ltr dba102_rvwr_14518.trc
-rw-r-----    1 oracle   dba          2087 Aug 10 04:30 dba102_rvwr_14518.trc

Using this approach you can quickly rename files in a directory.

$ ls | xargs -t -i mv {} {}.bak

The -i option tells xargs to replace {} with the name of each item.


Among the most popular for Oracle users is the find command. By now you know about using find to find files on a given directory. Here is an example to

find files starting with the word "file" in the current directory:

$ find . -name "file*"

However, what if you want to search for names like FILE1, FILE2, and so on? The -name "file*" will not match them. For a case-insensitive search, use the

-iname option:

$ find . -iname "file*"

You can limit your search to a specific type of files only. For instance, the above command will get the files of all types: regular files, directories, symbolic links, and so on. To search for only regular files, you can use the -type f parameter.

$ find . -name "orapw*" -type f

The -type can take the modifiers f (for regular files), l (for symbolic links), d (directories), b (block devices), p (named pipes), c (character devices), s (sockets).

Oracle produces many extraneous files: trace files, log files, dump files, and so on. Unless they are cleaned periodically, they can fill up the filesystem and bring the database to a halt.

To ensure that doesn't happen, simply search for the files with extension "trc" and remove them if they are more than three days old. A simple command does the trick:

find . -name "*.trc" -ctime +3 -exec rm {} \;

To forcibly remove them prior to the three-day limit, use the -f option.

find . -name "*.trc" -ctime +3 -exec rm -f {} \;

If you just want to list the files:

find . -name "*.trc" -ctime +3 -exec ls -l {} \;

The top command is probably the most useful one for an Oracle DBA managing a database on Linux. Say the system is slow and you want to find out who is  gobbling up all the CPU and/or memory. To display the top processes, you use the command top.

Note that unlike other commands, top does not produce an output and sits still. It refreshes the screen to display new information. So, if you just issue top and leave the screen up, the most current information is always up. To stop and exit to shell, you can press Control-C.

It's probably needless to say that the top utility comes in very handy for analyzing the performance of database servers. Here is a partial top output

Let's analyze the output carefully. The first thing you should notice is the "idle" column under CPU states; it's 0.0%—meaning, the CPU is completely occupied doing something. The question is, doing what? Move your attention to the column "system", just slightly left; it shows 5.6%. So the system itself is not doing much. Go even more left to the column marked "user", which shows 1.0%. Since user processes include Oracle as well, Oracle is not consuming the CPU cycles. So, what's eating up all the CPU?

The answer lies in the same line, just to the right under the column "iowait", which indicates 91.2%. This explains it all: the CPU is waiting for IO 91.2% of the time.

So why so much IO wait? The answer lies in the display. Note the PID of the highest consuming process: 16143. You can use the following query to determine what the process is doing:

select s.sid, s.username, s.program
from v$session s, v$process p
where spid = 16143
and p.addr = s.paddr
------------------- -----------------------------
       159 SYS      rman@prolin2 (TNS V1-V3)

The rman process is taking up the IO waits related CPU cycles. This information helps you determine the next course of action.

Related Posts by Categories