Jeff Stonacek, Principal Architect
For years, I worked in a Unix/Linux command line on a daily basis. Lately however, my work is more strategic than operational, so I work at a command prompt on a less frequent basis. While I am still hands on keyboard, at my age, the memory is starting to slip a bit.
Occasionally, I have to run an ad-hoc, long SQL job or an RMAN backup from the command line. If it has been a while, I have to probe my memory for ways to do it without running the risk of losing my connection. In other words, how do I run how to run things in the background so that only a host failure would interrupt the job. There are many different ways to accomplish this, but all are fraught with arrows and pipes and ampersands. As with all things Unix, it can get very cryptic and terse. So, the goal of this blog I explain how I like to handle these situations and why.
No Script File
There are a variety of ways to submit things to background on Unix/Linux. Typically, the simplest way would be to put your script in a file, make the script executable and then run that script in background. But, I don’t like creating files as they tend to hang around and become a nuisance. Instead, I prefer to formulate my commands in an editor and then run them from the command line.
Here is an example of running an RMAN command from the command line with no script or command files involved. Just submit this and monitor the job by tailing the log file.
nohup rman <<EOF > /home/oracle/rman/prod_stby.log 2>&1 &
connect target sys/pw@prod;
connect auxiliary sys/pw@prod_stby;
run {
allocate channel c1 device type sbt_tape;
allocate channel c2 device type sbt_tape;
allocate auxiliary channel a1 device type sbt_tape;
allocate auxiliary channel a2 device type sbt_tape;
DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER NOFILENAMECHECK;
}
EOF
Let’s dissect the syntax:
- <<EOF – Means continue to read from STDIN until the characters “EOF” are encountered. EOF is an arbitrary character or string of characters – it could read <<SMELLMYFEET, but that might be offensive to some people. Notice the very last line of the script is EOF.
- > /home/oracle/rman/prod_stby.log – This should be intuitive to all Unix folk. Even Windows people probably recognize this part. Redirect STDOUT to the path and file specified. This is your log file of the job.
- 2>&1 – Again, this should be a fairly well-known Unix idiom. It simply means redirect STDERR to STDOUT. The job can be run without this, but anything written to STDERR will fall into the abyss.
- & – Run this thing in the background.
The following example encrypts a tablespace, which can take a very long time, depending on the size of the data. The same concept applies to SQLPlus jobs.
nohup sqlplus / as sysdba <<EOF > data_enc.log 2>&1 & set lines 132 pages 500 timing on time on alter tablespace DATA encryption online using 'AES192' encrypt file_name_convert = ( '/u01/oracle/oradata/dev/data01.dbf', '/u01/oracle/oradata/dev/data01_enc.dbf', '/u01/oracle/oradata/dev/data02.dbf', '/u01/oracle/oradata/dev/data02_enc.dbf' ); exit EOF
Notice that all of the syntax for the SQLPlus command is the same as the RMAN job. The authentication for the job is included on the command line for the SQLPlus job because “/ as sysdba” is no secret.
Conclusion
In this blog, I provided some examples of running long Oracle jobs in the background. I know that none of the concepts presented are especially groundbreaking, and people working on Unix systems will recognize all of the syntax. But, if you haven’t done this in a while, it can take a while to remember how to put all of the components in the correct spot, so this article offers details on exactly where everything goes.
