Unix for Oracle DBAs



Unix for Oracle Database Administrators

Robert Wylie,

DBA, SSG VLC,

O/o the Accountant General(A&E),

Rajasthan, Jaipur

The Article is a quick reference describing, how to create complex one-line Unix commands in addition to some most often used Unix commands used by Oracle database administrators.

Building Unix Commands

One of the most confounding things for the Unix neophyte is being confronted with a complex Unix command. The cryptic nature of Unix is such that even the most seasoned Unix professional may have trouble deciphering such a command.

Regarding the terms commands and scripts, you should note that any command may become a script if it is encapsulated into a file for execution. Hence, find . –print can be a command if executed from the prompt, or a script if placed into a file.

Building a Complex Unix Command from Scratch

A complex, one-line script is really composed of several simpler commands connected by the pipe operator (|). We will start by how to build a one-line script to kill all the Oracle processes on your server. Then we will see how to build one-line script to find files that contain a specific text string.

A script to kill all Oracle processes

This is a common Unix script used by Oracle DBAs when a database is locked up, and Server Manager cannot be used to stop the database in more “gentle“ fashion.

To begin, the Unix kill command is used to kill a process. The basic format of the kill command is as follows:

Kill –9 PID1 PID2 PID3 …PIDn

The trick is to be able to identify and kill only the Oracle processes. That’s done by stringing several commands together. The resulting one-line script looks like this:

ps –ef|grep “ora_”|grep –v grep|grep $ORACLE_SID|

awk ‘{print $2}’|xargs kill –9

We ‘ll walk through the process of building the command. To begin, we want to get a list of active processes on the server. We can do that using the following command:

ps –ef

If we execute ps –ef on our server, we’ ll see a long list of processes - both for Oracle and for many other things. However, we want to limit your output to only those processes that are related to the Oracle Database. The grep command can be used to do this. Oracle background process names always begin with “ora_”, so piping the output of ps –ef through grep “ora_” will remove all but the Oracle background processes. For example:

>ps –ef |grep “ora_”

oracle 12011 1 0 Dec 06 … ora_dbwr_JAP

oracle 12789 20202 0 12:10:55 0:00 grep ora_

oracle 13202 1 0 Dec 06 … ora_smon_JAP

oracle 14983 1 0 Dec 06 … ora_arch_JAP

oracle 10209 1 0 Dec 06 … ora_pmon_JAP

oracle 2090 1 0 Dec 06 … ora_reco_JAP

oracle 10404 1 0 Dec 06 … ora_lgwr_JAP

oracle 10403 1 0 Dec 06 … ora_dbwr_TEST

oracle 10401 1 0 Dec 06 … ora_lgwr_TEST

In the above output as we can see it includes the process that’s running grep command. Pipe this output through grep –v grep to remove the grep command, so you don’t kill your own process. The –v option makes grep work in a way that’s opposite its usual manner. Whereas grep finds and includes strings, grep –v excludes strings. In this next example, we’ ll see that the grep line is now missing from the output:

>ps –ef |grep “ora_”|grep –v grep

oracle 12011 1 0 Dec 06 … ora_dbwr_JAP

oracle 13202 1 0 Dec 06 … ora_smon_JAP

oracle 14983 1 0 Dec 06 … ora_arch_JAP

oracle 10209 1 0 Dec 06 … ora_pmon_JAP

oracle 2090 1 0 Dec 06 … ora_reco_JAP

oracle 10404 1 0 Dec 06 … ora_lgwr_JAP

oracle 10403 1 0 Dec 06 … ora_dbwr_TEST

oracle 10401 1 0 Dec 06 … ora_lgwr_TEST

Next, we should filter out all processes except those for the current ORACLE_SID. That way we delete the background processes only for that one instance instead of for all instances (if there are multiple database instance running). Do that by grepping for the SID name:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID

oracle 12011 1 0 Dec 06 … ora_dbwr_JAP

oracle 13202 1 0 Dec 06 … ora_smon_JAP

oracle 14983 1 0 Dec 06 … ora_arch_JAP

oracle 10209 1 0 Dec 06 … ora_pmon_JAP

oracle 2090 1 0 Dec 06 … ora_reco_JAP

oracle 10404 1 0 Dec 06 … ora_lgwr_JAP

Now that we have an accurate list of processes that you want to kill, you can use the awk command to get the process ID (PID) for each of these processes. The PID is in the second column, so we will use the awk ‘{print $2}’ command to display only that column:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|

awk ‘{print $2}’

12011

13202

14983

10209

2090

10404

Now we have a list of process Id numbers for the Oracle background processes. For the last step, we use the xargs command to pipe the list of PIDs to the kill command. For example:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|

awk ‘{print $2}’|xargs kill –9

Now that we’ve created this compound command, we can assign it to a Unix alias or we can put it in a file and make it a shell script so that we can execute it with a single short command.

Note: Not all shells support aliases. For example, if we are using the Bourne shell we will not be able to use aliases.

The following command assigns the new compound command to an alias named kill_oracle:

alias kill_oracle = “ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|awk ‘{print $2}’|xargs kill –9”

By placing the command to create the alias in your .profile file, we’ll have it available every time you sign on to Unix. By using an alias, you encapsulate the command without the burden of placing the command into a script file. Now, entering the alias kill_oracle at the command prompt will cause your command to run, which will kill all Oracle background processes for the instance to which $ORACLE_SID points.

A script to find all files containing a specific string

In Unix, it is not easy to find files that contain specific strings. Now we will explore a way to quickly build a command that will allow us to find a file that contains a particular character string.

Using commands such as xargs, we can quickly generate Unix scripts to perform many useful tasks. Suppose that we have created a script to create database TEST. Unfortunately, we have completely forgotten the name and location of the script file, and we need a Unix command to locate it. The example here demonstrates how you can leverage the xargs command to quickly create a complex command that searches for our lost file.

We will begin by writing a command that will display all filenames on the server. This syntax is quite simple in Unix, as the find command can be used to return a list of every file on the server starting from your current directory:

>find . –print

/home/oracle/wylie/sqlnet.log

/home/oracle/wylie/abc.sql

/home/oracle/wylie/tablespace.sql

/home/oracle/wylie/create1.sql

/home/oracle/wylie/export.dmp

/home/oracle/wylie/create2.sql

/home/oracle/wylie/create3.sql

/home/oracle/wylie/a.txt

We now have a complete list of all the Unix files under our current directory. The next step is to pipe this list of filenames to the grep command to search for files containing the string CREATE_DB_TEST. Because the grep command accepts a filename as an argument, you can use xargs to execute a grep command to search each file for the string we need:

find . –print|xargs grep –i create_db_test

The –i option tells grep to ignore case. We can execute this new command at the Unix prompt, and we’ll see that it quickly finds the file we are seeking:

>find . –print|xargs grep –i create_db_test

This ability to take a basic Unix command and pipe the output into another command is a fundamental principle of Unix shell programming for Oracle.

Unix Server Environment

Here are some handy Unix commands that will make it easier for use to navigate in our Unix environment. Firstly we will see at commands that can be automatically executed when we sign on to Unix as the Oracle user. There is a special file in our home directory in which we can place Unix commands that we want automatically executed when we sign on to the system. If we use Korn shell, this file is named .profile. if we use C shell, it will be called .cshrc.

We will also see how to create a standard Unix prompt, wrap SQL in Unix script, and write a utility to quickly change all files in a directory.

Set a Standard Unix Prompt

Placing the following code snippet in our .profile file will give you a Unix prompt that identifies our current server name, database name, and working directory. This can help prevent you from accidentally running a command against the wrong database. Note that I have my prompt go to a new line after displaying the information, so that I have a full 79 characters in which to type my Unix commands.

#*********************************************************************

# Standard Unix Prompt

#*********************************************************************

PS1=”

`hostname`*\${ORACLE_SID}-\${PWD}

>”

Here is what the prompt looks like after we have executed the PS1 command shown in the previous example. Note now the prompt changes when you change directories.

agj1*JAP-/home/oracle

>pwd

/home/oracle

agj1*JAP-/home/oracle

>cd /home2/dmp/treasury

agj1*JAP-/home2/dmp/treasury

>

Create Useful Unix Aliases for Oracle

Here we will see how we can place a list of helpful Unix aliases in the .profile file of a Unix Oracle User.

An alias is a Unix shortcut whereby we can define a short name to use in place of long Unix command. For example, we can create a shortcut called “log” that would execute the Unix cd (change directory) command to take us to the Unix directory where our alert log is located:

alias log = ‘cd $DBA/$ORACLE_SID/bdump’

The following example shows how aliases such as these can be used in place of typing a long command:

$log

$pwd

/home/oracle/app/oracle/admin/JAP/bdump

$

Any alias can be removed easily with the Unix unalias command. For example, to remove the log alias, we would enter the command unalias log.

Place a SQL*Plus Script in a Unix Shell Wrapper

Novice to Unix often find it convenient to execute SQL commands directly from the Unix prompt, without having to enter SQL*Plus each time. The following script shows how to create a Unix shell wrapper for any set of SQL*Plus commands. The Unix script in this example is named run_sql.ksh, and it invokes SQL*Plus to execute a SELECT statement followed by the SQL*Plus script contained in the file /home/oracle/abc.sql:

$cat run_sql.ksh

#!/bin/ksh

#First, we must set the environment . . . . .

ORACLE_SID=mysid

export ORACLE_SID

ORACLE_HOME=\`cat /etc/oratab|grep ^$OARCLE_SID(cut –f2 –d’:’`

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

$ORACLE_HOME/bin/sqlplus system/passwd ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download