Shell scripting cookbook

▸ Proficiency in scripting with Python (Pandas, PySpark, Airflow) and Windows/UNIX shell scripting.

 https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/03/oracle-rac-database-cloning-shell-script.html

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/03/oracle-password-encrypt-for-shell.html

Setting the Oracle Environment

Before running Oracle utilities like sqlplus or rman, you must set specific environment variables within your script. A common approach involves sourcing the system's oraenv utility or explicitly defining the ORACLE_HOME and ORACLE_SID

#!/bin/bash

# Set Oracle environment variables

export ORACLE_SID=ORCL

export ORACLE_SID=OP

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export ORACLE_HOME=/home/oracle/app/oracle/product/19.3.0/dbhome_1

export TNS_ADMIN=$ORACLE_HOME/network/admin

# Optional: Use the oraenv script (requires /etc/oratab to be correctly configured)

#. oraenv <<< $ORACLE_SID

Basic Scripting Concepts

• Variables: Define variables using VARIABLE_NAME=value. Access them using $VARIABLE_NAME.
• Command Line Arguments: Access arguments passed to the script using $1, $2, etc. $0 is the script's name.
• Conditional Statements (if): Use if [ condition ]; then ... fi for decision making.
• Loops: Use for or while loops for repetitive tasks, such as processing a list of databases from the oratab file.

• Functions: Organize repeated code into functions for better structure and readability. 

List  of sample 

1)  Bring down multiple database 

2) Troubleshooting and Debugging Shell Scripts

3) Backup Script Example

4)  Check the read  input

1)  Bring down multiple database 

export ORACLE_HOME=your oracle home

export PATH=$ORACLE_HOME/bin:$PATH

datestr=`date +20%y-%m-%d-%H.%M.%S`

logfile=db_shut_log_$datestr.log

ORAENV_ASK=NO

for ORASID in prod dev test

do

export ORACLE_SID=$ORASID

sqlplus "/as sysdba" <<!

spool DB_$ORASID.log

set echo off

set lines 300

set pages 300

col object_name for a30

select to_char(sysdate, 'dd-mon-yy hh24:mi:ss') from dual;

select instance_name,to_char(STARTUP_TIME, 'dd-mon-yy hh24:mi:ss') as "STARTUP_TIME",host_name from v\$instance;

select name,open_mode from v\$database;

shutdown immediate;

spool off

!

cat DB_*.log >> $logfile

done

rm DB_*.log

df -k >> $logfile

2) Troubleshooting and Debugging Shell Scripts

Debugging is a key skill for DBAs. Use set -x at the beginning of your script to trace execution:

sh

#!/bin/bash

set -x

# Script with Debugging

3) Backup Script Example

sh

#!/bin/bash

# Backup Script

ORACLE_SID=ORCL

BACKUP_DIR=/backup/ORCL

rman target / <<EOF

RUN {

  BACKUP DATABASE FORMAT '$BACKUP_DIR/ORCL_%U.bkp';

}

EOF

or 

#!/bin/bash

#setting up oracle environment

export ORACLE_HOME=/data/app/oracle/product/19c/dbhome

export ORACLE_SID=prim

export PATH=$PATH:$ORACLE_HOME/bin

date=$(date +"%y-%m-%d")

echo "RMAN BACKUP STARTED...$date"

INCREMENTAL_LEVEL_0(){

rman target / &amp;amp;lt; /home/oracle/rman_level0_log_$date

RUN

{

ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;

ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;

ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;

BACKUP

FORMAT '/data/rman/%d_D_%T_%u_s%s_p%p'

INCREMENTAL LEVEL 0 DATABASE

CURRENT CONTROLFILE

FORMAT '/data/rman/%d_C_%T_%u'

SPFILE

FORMAT '/data/rman/%d_S_%T_%u'

PLUS ARCHIVELOG

FORMAT '/data/rman/%d_A_%T_%u_s%s_p%p';

RELEASE CHANNEL ch11;

RELEASE CHANNEL ch12;

RELEASE CHANNEL ch13;

}

exit

EOF

}

INCREMENTAL_LEVEL_1(){

rman target / &amp;amp;lt; /home/oracle/rman_level1_log_$date

RUN

{

ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;

ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;

ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;

BACKUP

FORMAT '/data/rman/%d_D_%T_%u_s%s_p%p'

INCREMENTAL LEVEL 1 DATABASE

CURRENT CONTROLFILE

FORMAT '/data/rman/%d_C_%T_%u'

SPFILE

FORMAT '/data/rman/%d_S_%T_%u'

PLUS ARCHIVELOG

FORMAT '/data/rman/%d_A_%T_%u_s%s_p%p';

RELEASE CHANNEL ch11;

RELEASE CHANNEL ch12;

RELEASE CHANNEL ch13;

}

exit

EOF

}

filename=/home/oracle/rman_$1_log_$date

echo "filename is $filename"

if [ "$#" == 1 -a "$1" == "level0" ]

then

        echo "correct argument passed...$1"

        INCREMENTAL_LEVEL_0

elif [ "$#" == 1 -a "$1" == "level1" ]

then

        echo "correct argument passed...$1"

        INCREMENTAL_LEVEL_1

        echo "Backup is successfull" | mailx -s "Backup Script" -a $filename infoorcldata@gmail.com

elif [ $# -eq 0 ]

then

        echo "Please pass one argument - level0 or level1"

else

        echo "Something went wrong"

fi

4)  Check the read  input

#!/bin/sh

echo "Tell me your Training Details ?"

read input1

if [ $input1 == "Shell" -o $input1 == "Python" ]

then

echo "your input is $input1"

else

echo "Incorrect Input"

fi