Quick and Easy Way to Loop Forever in Linux

 As an DBA, you will find yourself in a situation where you need to monitor something on your Server or Database at every few seconds/ minutes. Instead of running the command manually again and again, you can write a one line Linux shell scripting code directly into the linux prompt and execute your command automatically with a desired interval.

Here is an example:

while true
 do
   echo `date`
   echo Will chek after 5 seconds
   sleep 5
 done

The above WHILE loop will always return true and will execute the commands in the loop forever. You need to press Ctrl + c to exit from the loop. You do not have to write the above script in a file and execute the file. You can simply run these set of lines directly in the linux command prompt.

The above script can be wriiten in just one line (insted of spanning into multiple lines), using semi-colon (;) to separate each line. Below is the modified command:

while true; do echo `date`; echo "Will chek after 5 seconds"; sleep 5; done

If you need, you can add a message to instruct the user to press ctrl + c to exit from the execution.

while true; do echo `date`; echo "Will chek after 5 seconds"; echo "Press CTRL + c to Exit"; sleep 5; done

In place of "while true", you can write "while [ 1 ]" or use the shorthand notation "while :" as in the below examples:

while [ 1 ]; do echo `date`; echo "Will chek after 5 seconds"; echo "Press CTRL + c to Exit"; sleep 5; done

while :; do echo `date`; echo "Will chek after 5 seconds"; echo "Press CTRL + c to Exit"; sleep 5; done

However, it is better to use the "while true" format as this syntax provides better clarity as what is happenning.

The same can also be achieved using a for loop with the below syntax, which always evaluates to TRUE.

$ for (( ; ; ))
> do
>    echo `date`
>    echo “Press CTRL+C to exit”
>    echo "Will chek after 5 seconds"
>    echo "Press CTRL + c to Exit"
>    sleep 5
> done

Similar to WHILE syntax, the FOR loop syntax can also be written in a single line:

for (( ; ; )); do echo `date`; echo "Will chek after 5 seconds"; echo "Press CTRL + c to Exit"; sleep 5; done

How can it help DBAs?

Let's see a simple example as how can it be used to make DBA's task easier.

If you have an alert for Server space usage for a mount point and you are cleaning up the space to bring the usage below the threshold or you are tracking the space usage for a mount point after every few seconds, you can use the below command to run the "df -h" command after 10 seconds.

while true; do date; df -h "/u02" | grep -v grep; echo ""; sleep 10; done

** echo "" will add a blank line after every loop for clarity in the output.

The above method can be used to check anything at the OS level or at the database level. 

How to check at the Database level

Let's say, you need to monitor the FRA usage by running the SELECT from the view v$recovery_file_dest. Then, enter the SELECT statement along with the SQL* Plus formatting in a sql script (Example below).

[oracle@olinux8 sql]$ cat check_fra.sql
set echo off
set lines 300
col name format A20
set feedback off
connect system/oracle
SELECT
            name, ROUND(space_used/1024/1024/1024,2) used_space_gb,
            ROUND(space_limit/1024/1024/1024,2) total_space_gb,
            ROUND((space_used/space_limit)*100,2) AS used_percent,
            ROUND(space_reclaimable/1024/1024/1024,2) reclaimable_space_gb,
            ROUND((space_reclaimable/space_limit)*100,2) AS reclaimable_percent
        FROM v$recovery_file_dest;
exit
[oracle@olinux8 sql]$

** Please note that:

• 'set echo off' command is important as it will supress the SQL & SQL*Plus commands in the output. 
• The database login credential is provided in the script so that these details are not exposed at the OS prompt.
• 'exit' command is added at the end to logout from the database after every execution.

Now, execute the below while loop command from the OS prompt to execute the SQL script after every 5 seconds.

while true; do date; sqlplus -S /nolog @check_fra.sql; echo ""; sleep 5; done

** Please note that:

• 'sqlplus -S' will supress the additional message that you see during a sqlplus login (Oracle edition, version, and other detail)
• Ctrl + c will not make you come out of the loop if you are running a SQL script using sqlplus. Use Ctrl + z to exit from the loop in this scenario.

Hope this will help you in your DBA work and save you a lot of time (And make you look cool in front of other DBAs). 

Please comment your scenarios where you are using this trick to automate your DBA work.