Sending Oracle UNIX files to Internet Mail Administration Oracle UNIX/Linux Tips by Burleson Consulting

Sending UNIX files to internet mail

As we noted in earlier chapters, it is easy to take a UNIX file such as a trace file and send it to a user via e-mail. This is especially useful for routing Oracle alerts and reports to your e-mail inbox.

Note: This script requires your network administrator to set up the e-mail address to be routed to the appropriate e-mail server in your UNIX environment.

In the example below, we e-mail all recorded UNIX commands for the oracle UNIX user. In UNIX, a complete record of every UNIX command is kept in a file called .sh_history in the oracle users home directory.

This is a common script used by an Oracle DBA manager to unobtrusively keep track of what their DBAs are doing, without their knowledge!

cat ~oracle/.sh_history|\
   mailx -s "DBA Command Report" don@buleson.cc

Changing a string for all files in a directory

We took a quick look at this script in Chapter 1, but it is so important that we should review it again here. This is one of my favorite scripts in UNIX. This script accepts an old string, locates the old string, and changes it to the values of the new string in all files in the directory.  One problem with working in UNIX is that is that it is very hard to perform a global change operation on all files within a directory. The script shown in this section does a search and replace in all files in a directory, replacing one string with another and making a backup of the unaltered files.

The for loop that you see in the script causes the sed command to be executed for each command in the current directory. Note that the sed command does the actual search and replace work, but it does not overlay the files.  Instead, sed writes the new versions of any affected files to a temporary directory.

This script always makes a backup of the files in a tmp sub-directory before issuing the change, so you can always get back your original files.  Also note that this script is defined as a Bourne shell script (.sh file suffix).  This is so the script will not change itself when it is executed against Korn Shell scripts.

chg_all.sh
#!/bin/ksh

tmpdir=tmp.$$

mkdir $tmpdir.new

for f in $*
do
  sed -e 's/oldstring/newstring/g' < $f > $tmpdir.new/$f
done

# Make a backup first!
mkdir $tmpdir.old
mv $* $tmpdir.old/

cd $tmpdir.new
mv $* ../

cd ..
rmdir $tmpdir.new

Below we execute this script, passing the file mask as an argument.  In the example below, we change the string ?oldstring? to ?newstring? for all *.sql files in our current directory:

> chg_all.sh *.sql

Remember that the strings to be changed are specified inside the body of the UNIX script, while the file mask is passed as a parameter. I don?t pass the old and new strings as parameters because the sed command can be quite tricky, especially if your strings contain special characters.

Let?s take a closer look at how this works. The sed command that you see in the script invokes the ?string editor? for Unix.  The sed command always makes a copy of the changed files, and never changes a file in-place.  Hence, you see in this example that the sed command writes new versions of all changed files to the $tmpdir.new directory. The changes are actually made using sed before the backup copies are made. However, the new versions of the files are not copied back from $tmpdir.new until after the old versions have been copied to the backup directory.

How to use UNIX to display row information

One trick commonly used by UNIX hackers is to leverage the UNIX operating system to probe into the Oracle data blocks.  With some knowledge of UNIX and Oracle, the nasty hacker can use UNIX to verify the contents of Oracle data rows.  This can be especially useful if a data corruption is causing a data file to go offline, or if Oracle data is suspect.  Let?s see how this is done.

* STEP 1 ? We start by running a SQL query to locate the ROWID of the data block that contains the row we want to investigate. Here we rely on the dbms_rowid package, and use the row_block_number procedure to return the data block corresponding to our desired row.

select
   dbms_rowid.rowid_block_number(rowid) block
from
   customer
where
   customer_name = ?Burleson?;

BLOCK
-----
141

Here we see that the customer information for Burleson resides on the 141st block in the data file.  We can now go to UNIX and display the contents of this row. 

This is a great tool because we can display Oracle data even if the database is shut down.  Of course, hackers can also use these tools to bypass the security of the Oracle database, hacking directly into the Oracle data files.

To display block 141, we can use the UNIX dd command.  The dd command accepts a skip parameter that tells it how far into a file to travel. To get to block 141 we must allow for nine blocks in the datafile header.  We must also remember that the skip statement should take us to the block immediately before our data block.

Hence, our data block is on block 150 (141+9) and the skip parameter for block 141 will be:  141+9-1 = 149.  We also need to specify the blocksize for the dd command in the ibs parameter.

Once we run the UNIX dd command to read the Oracle data block, we can filter the output by piping it to the UNIX strings command to only show printable information. Here is the UNIX command and the output showing the displayable data inside the data block:

root> dd if=/u01/oradata/prod/customer.dbf \
      ibs=8192 skip=149 count=1|strings

1+0 records in
149+0 records out
Donald Burleson 3/35/56 1401 West Avenue

While this technique is most useful in emergency situation when you cannot start the Oracle database, it is important to understand how a UNIX hacker can bypass Oracle and read information directly from your Oracle database files.

Killing UNIX Oracle Processes

We briefly addressed the UNIX kill command in Chapter 1, but we should re-visit the kill command as an emergency tool for the UNIX Oracle DBA. There are times when it is necessary to kill all Oracle processes or a selected set of Oracle processes on your UNIX server.  This killing of Oracle processes is required when one of the following conditions occurs:

* You cannot connect to the database with SQL*Plus

* Parts of the Oracle instance (SGA, background processes) have aborted and the database will not shut down normally.

Of course, Oracle recommends that the background processes only be aborted in emergency situations, but remember that Oracle will always attach warnstart after an abort and roll back any in-flight transactions. Also note that when you kill the Oracle processes you must also issues the ipcs command to ensure that all memory segments are removed. To kill all Oracle background processes and connections, we issue the following command. 

root> ps -ef|grep "ora_"|grep -v grep|awk '{print $2}'|xargs -i kill -9 {}  

The power of UNIX to quickly cause trouble for your database should be evident by the simplicity of this command.  Clearly access to the UNIX oracle account should be tightly controlled.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.