NOHUP for DBA & Developer

NOHUP is a Linux Command to run a job in the background. Why is it important to DBA and developer? NOHUP stands for "No Hang Up" which will make sense when you are done reading this article.  Whenever, I push a change to production database, we do it during off hours when there are no or minimum traffics that is mostly at midnight, early morning or weekends depending on the nature of business. I hate to go to office during those off hours to implement db changes therefore, I work from home using my VPN connection.

What are the risks of running your script from home? Well, I can list over dozens but I am going to point out just the main one.  You may loose your VPN connection anytime! What do you think? What will happen to the script you are running on your SQL Developer or SQL*Plus when you loose connection? Limbo right? Well, one of two things will happen, either the script will continue to run or the change gets rollback. Do you really want this to happen when you are working on a strict maintenance window? Well, I am going to show you how you can run your script in the background and not worry about your loosing VPN connection on both Linux & Window's Server.

Linux Server: Linux command <nohup> runs your script in the background. You don't have to wait for your script to be done running it. Just run the script in the background and move on to your next project.  Next, we will do a demo on how <nohup> command is used while running Linux shell script and in SQL*Plus to run SQL query.

vi demoscript.ksh
#!/bin/ksh
echo " no hup demo exercise"
echo "back from 1 min sleep"
sleep 60
echo "back from 5 min sleep"
sleep 300
exit
## making script executable
chmod 775 demoscript.ksh
## Running script in background
nohup ./demoscript.ksh &
## viewing background jobs
jobs

Foreground to Background: There may be a time where your script runs longer than expected time and you may need to leave for the day. In such case you must switch your foreground jobs to background and then log out.

## executing demoscript.ksh
./demoscript.ksh
## suspending execution of demo script.ksh
"CTRL +Z"
## switching foreground jobs to background
bg
## viewing background jobs
jobs
## bring background job back to foreground
fg %1

Running SQL Query in the Background:
nohup sqlplus username/password@oracleserver @query_file.sql &

Windows: Linux Server has a cool <nohup> utilities that takes care of everything we are trying to accomplish? What does Microsoft offer for their windows user? Well, there is no equivalent that I am aware of but I would always RDP into the Window's host where my database sits and run the deployment script using SQL*Plus from the command. You may even use SQL Developer if that is installed. If anyone knows the better alternatives, please post a comment below. Since, the query is running on the host, you can logout of your pc and then monitor from home later.

Others: What options do you have beside <nohup> and RDP? There are two other options that I am aware of but they require additional work. You may schedule your script to run via Cron Schedular. Cron runs all the process in the background. The second options is to run Oracle Query using DBMS Scheduler which runs the job in the background like Cron. When Linux is not your OS these are the second best options to run your scripts.

Now, you know the risk of running SQL queries from SQL Developer and SQL*Plus especially when working from home.  If I am working on queries that is going to take a long time at 4:00 pm, I will run my sql using <nohup>, exit my laptop, go home and then start monitor from home. I hope you find this article helpful and  I encourage you make to use it if you are working from home/remotely or deploying the critical changes.