Login from Unix to any database and running a query

Script below is used to login by any user and to keep the password hidden (not displayed on the screen)

#!/bin/bash
#Usage
# Pass Username & Connectsting
#password will be asked at login time
#read -s is used to hide password and it will not been displayed to the user

if [ "$1" = "" ] || [ "$2" = "" ]; then
   echo "Please pass Username and Database name to connect"
else
echo "Enter Pass"
read -s password
sqlplus -s "$1/$password@$2" << EOF
select sysdate from dual
/
EOF
fi

Hide Password on Unix prompt

Hide Password on Unix Prompt

While we are dealing with the password. Security is very essential. There are no of ways to do the same. Storing the password's in a file and securing it by using tight access rights on the file to user's and groups.

If there are no of users using the shell script and using their own login credentials, Most effective way is to hide the password on the screen.

Below script depicts the similar use of the command :-

#!/bin/bash
#Usage
# Pass Username & Connectsting
#password will be asked at login time

if [ "$1" = "" ] || [ "$2" = "" ]; then
   echo "Please pass Username and Database name to connect"
else
echo "Enter Pass"
read -s password
sqlplus -s "$1/$password@$2" << EOF
select sysdate from dual
/
EOF
fi

Script checks for the user name and database name in case user did not provide the same it will return the message and exit.

read -s password
will read the password from the user prompt however will not show the alphabets typed by the user and keep it secure since it is not available anywhere except with the user.

Creating a Menu in Unix Shell Script

Today I learned how to create a menu to display and choose from the menu. It has became a Menu driven program. which internally function based on your choice:-

Below is the sample script.


#!/bin/sh
# Wedding guest meals
# These variables hold the counters.
NUM_CHICKEN=0
NUM_STEAK=0
ERR_MSG=""
# This will clear the screen before displaying the menu.
clear
while :
do
        # If error exists, display it
        if [ "$ERR_MSG" != "" ]; then
                echo "Error: $ERR_MSG"
                echo ""
        fi
        # Write out the menu options...
        echo "Chicken: $NUM_CHICKEN"
        echo "Steak: $NUM_STEAK"
        echo ""
        echo "Select an option:"
        echo " * 1: Chicken"
        echo " * 2: Steak"
        echo " * 3: Exit"
        # Clear the error message
        ERR_MSG=""
        # Read the user input
        read SEL
        case $SEL in
                1) NUM_CHICKEN=`expr $NUM_CHICKEN + 1` ;;
                2) NUM_STEAK=`expr $NUM_STEAK + 1` ;;
                3) echo "Bye!"; exit ;;
                *) ERR_MSG="Please enter a valid option!"
        esac
        # This will clear the screen so we can redisplay the menu.
        clear
done


Execution:-

Call the script - ./menu.sh

Below will be shown on the screen, Now choose the option.. say I choose 1 then then counter for chicken will counter to 1 and so on..

Chicken: 0
Steak: 0
Select an option:
 * 1: Chicken
 * 2: Steak
 * 3: Exit

Testing a Condition

You want to write a script that checks for certain conditions such as the number of parameters passed to a script. Based on the condition, you want to perform an action such as display an informational message or exit the script.

The if/then/else structure is an important programming technique. However, it is the combination of if/then/else with a condition that can be tested that gives you a much more powerful tool to automate DBA tasks. The test command gives you the ability to check a condition within an if command.

Here is the basic syntax for the test command:

test operand1 operator operand2

The test command can also be written with the [ ] syntax. This syntax uses a left brace to start the command and then finishes the command with a right brace. Its syntax is as follows:[ operand1 operator operand2 ]

Note :- The shell script examples in this note will use the [ ] form of the test command.
For some test conditions, an operand1 isn't required. The syntax for this condition is as follows:

[ operator operand2 ]

The previous test conditional checks will exit with a status of 0 (true) or 1 (false), depending on the evaluation of the condition. Ensure that you have a space between the operands, operators, and brackets. The space is how the shell knows where the operator and operand are separated. If there is no space between the operator, operand, and brackets, then the shell will interpret the value as one string, which will result in erroneous outcomes.

Verifying the Number of Parameters

To bring if/then/else and test together, you'll write a small—but useful—piece of code that checks to see whether the correct number of parameters are passed to a script. The script will use the $# parameter. The $# parameter automatically gets assigned to the number of positional parameters typed at the command line. The $# variable is handy when you want to check for the correct number of parameters passed to a script.
The following bit of code uses the -ne conditional check to determine whether the number of variables passed to the script is not equal to 1:

#!/bin/bash

if [ $# -ne 1 ]

then

echo "Wrong number of parameters passed to script."

exit 1

fi

The $0 parameter is often used in conjunction with the $# parameter to display the correct syntax required when invoking a script. Within a shell script, the $0 parameter contains the name of the shell script being executed. Here's a slight variation of the previous code that uses the $0 variable to display the name of the script:

if [ $# -ne 1 ]

then

echo "Wrong number of parameters passed to script."

echo "Usage: $0 ORACLE_SID"

exit 1

fi

The -ne operator is an arithmetic operator and is used to test whether the operands are not equal. If the script is called without passing exactly one parameter to it, then the following output is displayed:

Wrong number of parameters passed to script.

Usage: ./ss.bsh ORACLE_SID

Notice that there is a ./ in front of the script name in the previous output. To scrape the ./ out of the output, use the basename command as follows:

Pgm=$(basename $0)

if [ $# -ne 1 ]

then

echo "Wrong number of parameters passed to script."

echo "Usage: $Pgm ORACLE_SID"

exit 1

fi

In the previous piece of code, notice the first line of code uses a shell technique known as command substitution. Command substitution allows you to take the output of a command and load it into a variable. The basic syntax for doing this is as follows:

variable=$(shell commands)

Verifying the Amount of Physical Memory

Another arithmetic check that you may want to do is to verify the amount of physical memory on your database server. Here's a simple script that verifies that the database server memory is greater than 1 gigabyte:

#!/bin/bashthresh=1048576

totMem=$(grep MemTotal /proc/meminfo awk '{print $2}')

if [ $totMem -lt $thresh ];

then

echo "Total Memory $totMem is less than: $thresh"

exit 1

else

echo "Total Memory is: $totMem"

fi

Several arithmetic operators are available with the Bash shell.

Checking the Name of User Running the Script

You can also use strings with test conditions. There are a wide variety of ways to use string comparisons. Below is the lists test operations for strings and their descriptions. For example, you may want to check to ensure that you're logged on as a certain operating system user before you run commands in a script. This example checks to see whether the user running the script is oracle:

#!/bin/bash

checkUser=oracle

curWho=$(whoami)

if [ "$curWho" != "$checkUser" ];

then

echo "You are currently logged on as: $curWho"

echo "Must be logged in as $checkUser to run this script.."

exit 1

fi

In the previous bit of code, the curWho variable is assigned to the name of the user running the script. That string variable is then checked to see whether it matches the string of oracle. If user doesn't match, then the script displays informational messages and exits the script.

Accepting Input from the Command Line

Another useful example of a string comparison is to read input from a user and verify an operation. Suppose you want to check the current Oracle SID variable before continuing to run more commands within the script. This is useful if you work with multiple databases contained on one physical server. This script displays the value of ORACLE_SID and asks whether you want to continue running the script:

#!/bin/bash

keepGoing=n

echo "Current value of ORACLE_SID: $ORACLE_SID"

echo -n "Do you want to continue? y/n "

read keepGoing

if [ "$keepGoing" = "y" ];

then

echo "Continue to run script."

else

echo "Exiting script"

exit 1

fi

How It Works

In addition to arithmetic and string comparisons, you can also perform various tests on operating system files. The test command allows you to perform checks such as the availability of a file, the file type, and so on. Below table contains descriptions of the Bash shell tests for file operations.

For example, you may want to determine whether an error log file exists and, if it does, then send an e-mail to the appropriate support person. This script uses the -e parameter of the test command to determine this:

#!/bin/bash

checkFile=/home/trace/error.log

if [ -e $checkFile ];

then

mail -s "errors" bbill@gmail.com <$checkFile

else

echo "$checkFile does not exist"

fi

If you want your shell script to do nothing after checking for a condition, then use the colon (:) command (sometimes called no-operation or null). For example, the following bit of code does nothing if it detects that the given file exists:

#!/bin/bash

checkFile=/home/oracle/.bashrc

if [ -e $checkFile ];

then

:

else

echo "$checkFile does not exist"

fi

Tip

The test options will vary by vendor and version of Linux. For a complete listing of available test operations in your environment, use the help test command or the man test command.

Checking Simple Conditions

You want to check for a condition such as whether a critical database background process is running and send an e-mail if there is a problem.

Solution

Use the if/then/else Bash control structure to check for a condition and perform an appropriate action. The following example uses an if/then/else structure to determine whether the Oracle system monitor process is running and sends an e-mail if the process is not detected:

#!/bin/bash
SID=SAND
critProc=ora_smon
ps -ef grep -v 'grep' grep ${critProc}_$SID
if [ $? -eq 0 ]; then
echo "$SID is available."
else
echo "$SID has issues." mail -s "problem with $SID" bbill@gmail.com
fi
exit 0

The previous example uses the $? variable. This variable is often used after conditional statements to evaluate the success or failure of the previous command. The $? contains the status of the last executed command. If the previously executed command was successful, then the $? variable will contain a zero; otherwise, it will contain a nonzero value.

How It Works

The if/then/else control structure comes in three basic forms. The first one states that if a condition is true, then execute the following commands. Its syntax is as follows:

if condition ; then
commands
fi

On the first line of code in the previous example, the keyword then is a separate command, so you must insert a semicolon to indicate the end line termination point of the if keyword. Another way of executing the previous bit of code would be as follows:

if condition
then
commands
fi

The next form of the if/then/else structure states if a condition is true, execute the following commands. If the first condition is false, then execute a separate set of commands. Its syntax is as follows:

if condition ; then
commands
else
commands
fi

The third form of the if/then/else structure states that if a condition is true, then execute the first set of commands; otherwise, check for the next condition, and if it is true, execute the commands. This functionality is enabled with the elif keyword. You can have many elif conditions in this form. Its syntax is as follows:

if condition ; then
commands
elif condition
commands
elif condition
commands
fi

Shell Script - Tips

Writing a Simple Shell Script
Problem
You're new to shell programming, and you want to write a simple script to determine whether your database is accessible.
Solution
Use an editor to create a new file (see Chapter 4 for details on using the vi file editor). Place within the file the following text:

#!/bin/bash
ORACLE_SID=SCDEV
ORACLE_HOME=/orahome/oracle/product/10.2.0.1/db_1
PATH=$ORACLE_HOME/bin:$PATH
echo "select 'DB up' from dual;" | sqlplus -s system/foo
exit 0

Modify the previous code to match your ORACLE_SID and ORACLE_HOME variables and your system schema password. After you've created the file, you'll also need to modify the permissions on the file to be executable. In this example, the file name is dbcheck.bsh:

$ chmod u+x dbcheck.bsh

This changes the permission for the owner (u) of the file to executable (x). Now you should be able to successfully run the program:
$ dbcheck.bsh
If your database is up, you should receive a message like this:

'DBUP
-----
DB up

All of the checking within the script is performed with one line of code:
echo "select 'DB up' from dual;" | sqlplus -s system/foo
The echo command pipes a valid SQL statement to the sqlplus executable. The sqlplus executable will then attempt to log on with the system schema and run the statement.


Bash Shell Exit Command

You can place an exit command at any location within a shell script to instruct the Bash shell to immediately terminate the program. A successful exit is normally specified with an exit or an exit 0. Exiting a shell script when a failure condition has been detected is indicated by a nonzero value, such as an exit 1. We recommend you explicitly place an exit 0 command within your shell script to indicate a successful completion. You should also use a nonzero value such as an exit 1 to indicate that some sort of an error condition has been detected.
Each Bash shell command that executes will also return an exit code. If a command executes successfully, it will terminate with a status of 0. If there has been some sort of a failure, the exit code will be nonzero. You can check the status of an exit code by inspecting the $? variable. The $? variable holds the exit value of the previously executed command. The nonsuccess value of an exit code will vary by each command. For example, the grep utility will return a 0 on successfully finding a match, a 1 if no matches are found, and a 2 if there has been some sort of a syntax error or missing input file.


How It Works
The first line of the shell script in the "Solution" section of this recipe needs a little more explanation. We've reproduced the line here for the discussion:

#!/bin/bash

The # character is normally used to comment out a line in a shell script. One exception to that rule is when #! appears as the first text in the shell script. When #! is placed on the first line, it can be then combined with a path and program name. The path and program name specify the location and name of the program that will interpret the commands within the script. This is important because it means you can have the script run with a designated shell regardless of the interactive shell you're using.
If you don't specify a #!//, then the shell you're currently logged on to will be used to interpret the commands within the script. We recommend you specify the path and shell program on the first line of your script so that there is no ambiguity about which shell will be used to interpret the commands within the script.
On most Linux distributions, the bash interpreter is in the /bin directory. If you don't know the location of your bash executable, you can use the which or whereis command to locate it:
$ whereis bash
bash: /bin/bash
When you first attempt to run a shell script, you may receive an error similar to the following:
-bash: dbcheck.bsh: command not found

This means your PATH variable doesn't include the current working directory. To work around this, you can reset your PATH variable to include the current working directory. This example exports the PATH variable to include the current working directory:
$ export PATH=$PATH:.
Another method for ensuring that the Bash shell can locate a script is to include the complete directory path to where the script resides. In this example, the script is located in the directory /home/oracle and is run as shown here:
$ /home/oracle/dbcheck.bsh
You can also instruct the shell to look in the current working directory to determine the location of the script. You do this by placing a ./ before the script name:
$ ./dbcheck.bsh

Note Adding the file extension .sh, .bsh, or .bash to the end of a Bash shell script is a common industry practice. Keep in mind that file extensions are meaningless in Linux/Unix environments (other than helping you document the type of script). This is different from DOS where .exe, .com, and .bat indicate executable operating system files.