How to match an exact string using grep

grep -w string

See it in use here

How to remove unwanted parameters from init.ora using a shell script

1. Create a "removal list" containing your unwanted parameters. For example, put the following into a text file called removal_list.txt, and save it in $ORACLE_HOME/dbs:

_b_tree_bitmap_plans
_fast_full_scan_enabled
_grant_secure_role
_like_with_bind_as_equality
_projection_pushdown

2. create a Shell script to loop through the lines in remval_list.txt, called remove_params.sh:

for a in $(cat removal_list.txt ); do
 echo now testing $a
 sleep 1
 curr_param=`grep -w $a init${ORACLE_SID}.ora | awk -F '[=]' '{ print $1 }'`
 if [ $curr_param ]; then
  echo removing $curr_param
  sed -i "/${curr_param}/d" init${ORACLE_SID}.ora
  sleep 1
 else
  echo $a not found in parameter file. Skipping...
  sleep 1
 fi;
done
exit 0

It is important to use the -w flag with grep, as many Oracle initialization parameters can be named similarly and the script will then save multiple values in the curr_param variable

3. Make Your script executable:

chmod 755 remove_params.sh

4. execute the script:

./remove_params.sh

Expected output:

now testing _b_tree_bitmap_plans
removing _b_tree_bitmap_plans
now testing _fast_full_scan_enabled
removing _fast_full_scan_enabled
now testing _grant_secure_role
_grant_secure_role not found in parameter file. Skipping...
now testing _like_with_bind_as_equality
removing _like_with_bind_as_equality
now testing _projection_pushdown
_projection_pushdown not found in parameter file. Skipping...

How to delete a specific line from a file, or all characters following a specific character using sed

If you want to search a file for a particular string, and then delete that line, use sed, like this:

sed -i "/_use_column_stats_for_function/d" init.ora

In bash, using the double quotation marks was the only way to make this work when using a variable instead of a fixed string.

To delete a specific line number, in this case line number 7:

 sed -i '7d' myfile.txt
 

To delete lines 7 and 8:

 sed -i '7,8d' insert_data.sql

To delete everything after a specific character, for example #:

sed -i "s/#.*//g" /tmp/autoupgrade.ora

Simple sed-command for exchanging a string in multiple files

sed -i "s/oldstring/newstring/g" 

For example

sed -i "s/11204/12102/g" /etc/oratab

How to use multiple delimiters in awk

When creating a script for cloning, I wanted to perform some initial checks. One of them was to make sure the listener for the source database was up. For this, I wanted to use the tnsping utility and grep for the result:

tnsping mydb

where mydb corresponds to the tnsnames.ora alias of interest.

* If there was an error, exit script.
* If listener was up, continue.

I directed the output to a file, and after that grepped for the string that would indicate success or failure.
Problem was that the files would look very different and the interesting string would need to be grepped for using two different delimiters.

When successful, the file would look as follows:

OK (10 msec)

When not successful, the output would be:

TNS-03505: Failed to resolve name

So how to pull out either "TNS" or "OK" and use these values programmatically further on in the script?

I accomplished this task simply by using the notation '[-(]'in my awk statement:

REMOTE_LSNR_STATUS=`cat ${LOG_DIR}/test_tnsping.log | egrep -e 'TNS-|OK' | awk -F '[-(]' '{ print $1 }'`

How to use the case statement in a shell script, with case-insensitive input

Use the pipe (|) character for each possible case:

echo "TABLESPACE REPORT FOR $ORACLE_SID"

stty echo
   echo "All tablespaces or specific tablespace? [A|S]"
   read answer
stty echo

case $answer in
A|a) echo "All tablespaces selected...please wait...";
    getFiles A;;
S|s) echo "Which tablespace?";
    read wt;
    getFiles $wt;;
*) echo "Only A or S are supported parameters.";
    exit 1;;
esac

How to check that an Oracle database parameter is set in a unix shell script

Probably many ways to do the same thing, but I tend to spool database information to disk, then assign the values found to shell script variables, as in the following example, where I need to determined whether the parameters db_file_name_convert and log_file_name_convert is set in the database:

###############################################################
# Check that the db_convert_file_name and log_file_name_convert
# are both set. If not, abort script.
###############################################################
TRG_DB=${ORACLE_SID}
cat << EoF > check_${TRG_DB}_convert_params.sql
set termout off
set lines 200
set trimspool on
set heading off
set pages 0
set feedback off
set echo off
set verify off
col name format a30
col value format a40
spool check_${TRG_DB}_convert_params.lst
select name,value
from v\$parameter
where name in ('db_file_name_convert','log_file_name_convert');
exit
EoF
sqlplus -s / as sysdba @check_${TRG_DB}_convert_params.sql
CONV_DATA_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep db_file | awk '{print $2}'`
CONV_LOG_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep log_file | awk '{print $2}'`

case ${CONV_DATA_FILE_SET} in
'') echo db_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo db_file_name_convert is set. Continuing...;;
esac

case ${CONV_LOG_FILE_SET} in
'') echo log_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo log_file_name_convert is set. Continuing...;;
esac
exit