Running in the Background

We all have that list of features we just wish Oracle would adopt in their latest version: alterable redo log files, command history in SQL*Plus, DBA_OOPS package with various rollback options when you screw something up…

One of my biggest wishlist items is detachable sessions. In Oracle 10g we got the Datapump tool for exports and imports. One of its best features is the ability to detach from a running export or import and re-attach as needed. When you kick off an import (impdp) you can press CTRL+C and drop out to a client. From there, you can type ‘exit_client’ and detach completely, allowing you to go home, get a coffee, turn off your computer, or otherwise amuse yourself in whatever way seems best. For example:

bash-3.00$ expdp system/******** directory=dpexp full=y dumpfile=test.dmp

Export: Release 11.2.0.3.0 - Production on Mon Jun 17 07:27:50 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_02":  system/******** directory=dpexp full=y dumpfile=test.dmp 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 52.70 GB
^C
Export> exit_client

bash-3.00$ 

As you can see, pressing CTRL+C allowed me to exit the client back to a bash prompt. Since the task is owned by the job SYS_EXPORT_FULL_02, it stays running in the background. However, I can re-attach to the job and use continue_client to keep watching, or:

bash-3.00$ expdp system/******** attach
...
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

bash-3.00$ 

And there it is. But the important thing to note is that when I kick off an export or import with expdp and impdp, it always runs as a job. That means that your foreground process is only attaching to the background process running the job; this is what allows you to attach and detach at will.

At the same time, that is technically always the case. When you connect to the database in SQL*Plus and run a command, your SQL*Plus process is not the one performing the work; instead, the commands are run against the DB with your server process–LOCAL=NO owned by init for remote connections, or LOCAL=YES owned by your SQL*Plus process for non-remote connections. It would be very nice to be able to detach and re-attach to tasks run from any Oracle client.

That being said, it’s not possible. So here are some ways to run your work in the background that are useful for cases where you have a long running process and aren’t sure if you can wait around for it to complete.

NOHUP + Background

Most everyone knows NOHUP (No Hangup). It is a command run from the UNIX/Linux prompt in which you call your script, usually with a ‘&’ sign at the end signifying you want the work to occur in the background, like so:

steve@220883 backgrounder]$ nohup ./longrun.sh &
[1] 6446
[steve@220883 backgrounder]$ nohup: appending output to `nohup.out'

[steve@220883 backgrounder]$ 

You can still keep an eye on your job with the ‘jobs’ command, and even kill the job if need be:

[steve@220883 backgrounder]$ jobs
[1]+  Running                 nohup ./longrun.sh &
[steve@220883 backgrounder]$ kill %1
[1]+  Terminated              nohup ./longrun.sh
[steve@220883 backgrounder]$ 

Running a command with ‘nohup’ is a great way to kick something off in the background, allowing you to disconnect and do other things.

Disown

But what if you forgot to run the nohup command? You can still kick your program to the background with CTRL+Z and run it in the background; however, you still own it:

[steve@220883 backgrounder]$ ./longrun.sh
[1]+  Stopped                 ./longrun.sh
steve@220883 backgrounder]$ bg
[1]+ ./longrun.sh &

Notice that pressing CTRL+Z stopped the job, and typing ‘bg’ ran it in the background (signified by the ‘&’ sign). However, without NOHUP the command is still owned by my session. If I exit right now it will hang and the fate of my background job will be unclear.

To get around this problem, you can use the ‘disown’ command:

[steve@220883 backgrounder]$ disown
[steve@220883 backgrounder]$ 

Nothing special happens in the foreground. But in the background, your job is now owned by init (PID 1). You can feel free to exit and go about your business.

Special Note: There is a special place in Hell for those who CTRL+Z out of ‘vi’.

Screen

Shamelessly borrowed from 5 Quick and Dirty Linux Tips You May Not Know

This one was a godsend. Have you ever been working on a long running operation and you lost your connection to Oracle, or had to go home, or god knows what else? It is a horrible pain and one that really messes up a lot if it gets you at the wrong time. That should be a thing of the past if you use screen.

Screen is a multiplexed terminal, allowing you to spawn multiple terminals in a single terminal session. Opening a new ‘screen’ is simple; simply type the word screen at a Linux command line:

[skaram@server2 ~]$ echo HELLO!
HELLO!
[skaram@server2 ~]$ screen

Notice your terminal clears and you start at a new prompt. Now I will go ahead and make it wait for input.

[skaram@server2 ~]$ read -p "Press Enter"
Press Enter

If I press CTRL+A, then CTRL+D, my screen will detach, putting me back on the ‘parent’ terminal session:

[skaram@server2 ~]$ 
[skaram@server2 ~]$ 
[skaram@server2 ~]$ echo HELLO!
HELLO!
[skaram@server2 ~]$ screen
[detached]
[skaram@server2 ~]$ 

If I want to get back to the child screen, I can type screen -rx to re-attach. Or start a new terminal with a fresh ‘screen’ command. You can even detach from multiple terminals and connect to the one of your choosing:

[skaram@server2 ~]$ screen -list
There are screens on:
	10913.pts-2.server2	(Detached)
	10883.pts-2.server2	(Detached)
	9700.pts-2.server2	(Detached)
3 Sockets in /var/run/screen/S-skaram.

[skaram@server2 ~]$ screen -rx 10883.pts-2.server2

But wait, there’s more! If you and a buddy, coworker, consultant, engineer, parole officer, etc. want to share the same screen, you can login as the same user via SSH and then both type screen -rx. You will both join the multiplexed session (screen) and be able to type and see what each other is typing. It is awesome for following along, mentoring, etc.

DBMS_SCHEDULER

While Oracle Scheduler is an outstanding tool for running tasks in the future or on a schedule, it can also be used to run tasks right away. The best part is that since it is native to Oracle, it can run a PL/SQL block in addition to a shell script. That means if all you need to do is gather some stats or run a big update, you don’t need to make a shell script wrapper and nohup it. For example:

begin
  dbms_scheduler.create_job('stats_gather_test_uno',
    job_type=>'plsql_block',
    job_action=>'begin
dbms_stats.gather_schema_stats(ownname => ''SCOTT'');
end;',
    enabled=>true);
end;
/

That command will create a job called STATS_GATHER_TEST_UNO that gathers stats on the SCOTT schema right away in the background. You can use DBA_SCHEDULER_RUNNING_JOBS and other views to get more info on its progress. If you want to run a shell script, then job_type should be set to ‘EXECUTABLE’ and job_action will be the script itself.

Note: There are two single quotes around SCOTT in that example, as we are using single quotes inside of single quotes. If you need to run something like ‘execute immediate’ you will have to use even more quotes, for example:

'begin
execute immediate ''something with ''''quotes'''''';
end;'

Ouch. You can get around this problem with q-quotes, introduced in 10gR1.

Conclusion

These options are a far cry from a real backgrounding capability for Oracle sessions. It would be great if we could do something like DBMS_SESS.DETACH(sid=>123, jobname=>’way_too_long’) then re-attach as needed. But until that day, there are some viable options as long as you show a little forethought.

You may have noticed that ‘cron’ didn’t make the cut. We all know and love the cron tool for scheduling something that we don’t want running in our terminals; however, it’s already a very commonly blogged-about tool and somewhat inadequate for our “just run it once in the background please, thanks” requirements.