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_SIDcut –f2 –d’:’`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus system/passwd<
SELECT * FROM v\$database;
@/home/oracle/abc.sql
exit
!
We can also execute a script directly from the command line, provided we have set ORACLE_HOME and ORACLE_SID in our Unix environment. For example:
$sqlplus system/manager @abc
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_SIDcut –f2 –d’:’`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus system/passwd<
SELECT * FROM v\$database;
@/home/oracle/abc.sql
exit
!
We can also execute a script directly from the command line, provided we have set ORACLE_HOME and ORACLE_SID in our Unix environment. For example:
$sqlplus system/manager @abc
Comments
Post a Comment