 |
|
Oracle UNIX Administration Changing Default
File Permissions with umask
Oracle UNIX/Linux Tips by Burleson Consulting |
Change default file permissions with
umask
As we noted in Chapter 1, the umask and
chmod commands are often used by the Oracle DBA to allow other UNIX
users the ability to read and execute Oracle files.
For example, if we want to create a file
with read-write permission for the Oracle user and ready-only
permissions for everybody else, we can set the umask to 022:
root>
umask 022
root> umask
022
root>
touch dumpfile.trc
root> ls
-al dumpfile.trc
-rw-r--r--
1 oracle dba
0 Aug 13 09:36 dumpfile.trc
Change file permissions in UNIX
For another example, say we wanted to allow
read-only access to all of the trace file in our user_dump_dest
directory in UNIX. This will allow Oracle developers to review
their trace files without bothering the Oracle DBA.
janet*test9i-/export/home/oracle
>udump
janet*test9i-/u01/app/oracle/admin/test9i/udump
>ls -al
total 76
drwxr-xr-x 2 oracle dba
2048 Oct 21 01:00 .
drwxr-xr-x 6 oracle dba
96 Aug 12 19:50 ..
-rw-r----- 1 oracle dba
2230 Oct 7 01:00 test9i_ora_11777.trc
-rw-r----- 1 oracle dba
2230 Oct 21 01:00 test9i_ora_14541.trc
-rw-r----- 1 oracle dba 2230
Sep 9 01:00 test9i_ora_14814.trc
-rw-r----- 1 oracle dba
2230 Aug 19 01:00 test9i_ora_21031.trc
-rw-r----- 1 oracle dba
2230 Oct 14 01:00 test9i_ora_23910.trc
-rw-r----- 1 oracle dba
2230 Sep 23 01:00 test9i_ora_27041.trc
-rw-r----- 1 oracle dba
2226 Aug 26 01:00 test9i_ora_517.trc
-rw-r----- 1 oracle dba
2228 Sep 2 01:00 test9i_ora_6784.trc
-rw-r----- 1 oracle dba
2228 Sep 30 01:00 test9i_ora_7800.trc
janet*test9i-/u01/app/oracle/admin/test9i/udump
>chmod 644 *.trc
janet*test9i-/u01/app/oracle/admin/test9i/udump
>ls -al
total 76
drwxr-xr-x 2 oracle dba
2048 Oct 21 01:00 .
drwxr-xr-x 6 oracle dba
96 Aug 12 19:50 ..
-rw-r--r-- 1 oracle dba
2230 Oct 7 01:00 test9i_ora_11777.trc
-rw-r--r-- 1 oracle dba
2230 Oct 21 01:00 test9i_ora_14541.trc
-rw-r--r-- 1 oracle dba
2230 Sep 9 01:00 test9i_ora_14814.trc
-rw-r--r-- 1 oracle dba
2230 Aug 19 01:00 test9i_ora_21031.trc
-rw-r--r-- 1 oracle dba
2230 Oct 14 01:00 test9i_ora_23910.trc
-rw-r--r-- 1 oracle dba
2230 Sep 23 01:00 test9i_ora_27041.trc
-rw-r--r-- 1 oracle dba
2226 Aug 26 01:00 test9i_ora_517.trc
-rw-r--r-- 1 oracle dba
2228 Sep 2 01:00 test9i_ora_6784.trc
-rw-r--r-- 1 oracle dba
2228 Sep 30 01:00 test9i_ora_7800.trc
The chmod command also has a set pf plus
operators (+) that can be used to add read (+r), write (+w) or
execute (+x) to a file. For example, to make all korn shell
programs in a directory un-executable for everyone, the following
command could be used.
root>
chmod -x *.ksh
root> ls
-al *.ksh
-rw-r--r--
1 oracle dba
205 Jun 10 09:11 zulu.ksh
-rw-r--r-- 1 oracle dba
303 Jun 10 09:11 alert_ram.ksh
-rw-r--r-- 1 oracle dba
312 Jul 19 11:32 backup_online.ksh
-rw-r--r-- 1 oracle dba
567 Jun 10 09:12 count_rows.ksh
Once the maintenance is complete, the script
can again be made executable with the chmod command.
root>
chmod +x *.ksh
root> ls
-al *.ksh
-rwxr-xr-x
1 oracle dba
205 Jun 10 09:11 a.ksh*
-rwxr-xr-x 1 oracle dba
303 Jun 10 09:11 lert.ksh*
-rwxr-xr-x 1 oracle dba
312 Jul 19 11:32 back.ksh*
-rwxr-xr-x 1 oracle dba
567 Jun 10 09:12 coun.ksh*
Change file owners in UNIX
The UNIX chown command is used by the Oracle
DBA to change the group and owner for UNIX files. There are time
when a UNIX developer creates or changes a UNIX file for Oracle and
the Oracle DBA must change both the UNIX owner and the UNIX group
for a file.
In the example below, we change the owner
from mario to oracle and the UNIX group from devl to dba for all
files in the directory.
root> ls
-al
total 56
drwxr-sr-x 2 mario devl
512 Aug 31 1999 ./
drwxr-sr-x 8 mario devl
512 Apr 13 08:28 ../
-rwxrwxr-- 1 mario devl
819 Jun 23 16:11 initmysid1.ora
-rwxrwxr-- 1 mario devl
4435 Jun 26 15:00 initprodserv1.ora
root>
chown oracle:dba *
root> ls
-al
total 56
drwxr-sr-x
2 oracle dba
512 Aug 31 1999 ./
drwxr-sr-x 8 oracle dba
512 Apr 13 08:28 ../
-rwxrwxr-- 1 oracle dba
819 Jun 23 16:11 initmysid1.ora
-rwxrwxr-- 1 oracle dba
4435 Jun 26 15:00 initprodserv1.ora
Create a UNIX soft link for Oracle files
In Oracle environments with multiple $ORACLE_HOME
directories, it is often challenging to find the certain Oracle
parameter files. There are some files such as listener.ora and
tnsnames.ora that should only have a single copy on a UNIX server,
regardless of how many databases reside on the server.
For example, if we have a UNIX server with
Oracle8, Oracle8i and Oracle9i installed, we may want to create a
common location for generic file that are used by every database on
the server. These generic files include the following:
* tnsnames.ora – Each UNIX server should
only have one tnsnames.ora file.
* listener.ora – Each server should only
have one listener.ora file
* sqlnet.ora – There should only be a single
sqlnet.ora file on a server.
To make these singular file, we must trick
the Oracle software into thinking that the files are in their
default directory ($ORACLE_HOME/network/admin), while in reality
they exist in a common directory.
The common directories depend on the dialect
of UNIX.
* /etc
- This is used by HP/UX, Linux and AIX
* /var/opt/oracle
- This directory is used by Solaris
To create this illusion, the Oracle DBA must
use soft links to make each $ORACLE_HOME have access to the proper
file (Figure 10-1).
Figure 1: Using the UNIX soft link with
Oracle
As we noted, it is important to have a
single tnsnames.ora file of each server. In UNIX, the search
order for finding the tnsnames.ora file is as follows:
1. Search $TNS_ADMIN
2. Search /etc (or /var/opt/oracle in
Solaris)
3. Search $ORACLE_HOME/network/admin
The soft links are create by going to each $ORACLE_HOME/network/admin
directory on the UNIX server and using the ln –s command to create a
soft link for the tnsnames.ora and listener.ora files:
janet*test9i-/export/home/oracle
>cd $ORACLE_HOME/network/admin
janet*test9i-/u01/app/oracle/product/8.1.7_64/network/admin
>ls –al
total 56
drwxr-xr-x
3 oracle dba
2048 Jul 13 15:43 .
drwxr-xr-x 15 oracle dba
2048 Jul 12 10:56 ..
-rw-rw-r-- 1 oracle dba
558 Jul 12 10:57 listener.ora
-rw-r--r-- 1 oracle dba
148 Mar 9 2000 shrept.lst
-rw-rw-r-- 1 oracle dba
177 Jul 12 10:57 sqlnet.ora
-rw-rw-r-- 1 oracle dba
348 Jul 12 10:57 tnsname.ora
janet*test9i-/u01/app/oracle/product/8.1.7_64/network/admin
>mv tnsnames.ora tnsnames.old
janet*test9i-/u01/app/oracle/product/8.1.7_64/network/admin
>mv sqlnet.ora sqlnet.old
janet*test9i-/u01/app/oracle/product/8.1.7_64/network/admin
>mv listener.ora listener.old
janet*test9i-/u01/app/oracle/product/8.1.7_64/network/admin
>ln -s /etc/tnsnames.ora
janet*test9i-/u01/app/oracle/product/8.1.7_64/network/admin
>ln -s /etc/sqlnet.ora
janet*test9i-/u01/app/oracle/product/8.1.7_64/network/admin
>ln -s /etc/listener.ora
janet*test9i-/u01/app/oracle/product/8.1.7_64/network/admin
>ls –al
total 56
drwxr-xr-x
3 oracle dba
2048 Jul 13 15:43 .
drwxr-xr-x 15 oracle dba
2048 Jul 12 10:56 ..
-rw-rw-r-- 1 oracle dba
558 Jul 12 10:57 listener.old
lrwxrwxrwx 1 oracle dba
28 Jul 13 15:43 listener.ora ->
var/opt/oracle/listener.ora
-rw-r--r-- 1 oracle dba
148 Mar 9 2000 shrept.lst
-rw-rw-r-- 1 oracle dba
177 Jul 12 10:57 sqlnet.old
lrwxrwxrwx 1 oracle dba
26 Jul 13 15:43 sqlnet.ora ->
var/opt/oracle/sqlnet.ora
-rw-rw-r-- 1 oracle dba
348 Jul 12 10:57 tnsname.old
lrwxrwxrwx 1 oracle dba
28 Jul 13 15:43 tnsnames.ora ->
var/opt/oracle/tnsnames.ora
As we can see, these tedious commands can
easily be encapsulated into a UNIX script that loops through the
/etc/oratab file for each database.
# Loop
through each database name on the host /etc/oratab . . .
for db in `cat /etc/oratab|egrep ':N|:Y'|grep -v \*|cut -f1 -d':'`
do
# Get the ORACLE_HOME for each database
home=`cat /etc/oratab|egrep ':N|:Y'|grep -v \*|grep ${db}|cut -f2 -d':'`
echo " "
echo "database is $db"
cd $home/dbs
ln –s /etc/tnsnames.ora
done
While this soft link approach is great for
multiple Oracle databases in a single UNIX server, most shops have
multiple Oracle servers. To keep the standard Oracle files the
same on each server, many Oracle DBAs write UNIX scripts to use the
rsh command to distribute a singular tnsnames.ora file to all Oracle
servers.
Copy tnsnames.ora to all UNIX Servers
This is a super useful UNIX command snippet
to distribute common files to all servers. As we have noted,
this script requires the .rhosts setup to allow the UNIX rcp and rsh
commands.
#!/bin/ksh
echo
'starting distribution of tnanames.ora'
# Note:
dbnames file is in the form HOST DATABASE
for host in `cat dbnames|awk '{ print $2 }'`
do
db=`cat dbnames|awk '{ print $1 }'`
echo
starting distr to $host
rcp -p tnsnames.ora $host:/etc/tnsnames.ora
rsh $host ls –al /etc/tnsnames.ora
done
Note that this script uses a driving file
called dbnames that contains a list of hostname and database name
pairs. Here is a sample of this file.
dioneges
mysid1
dioneges prod9i
dioneges testm1
praetor test9i
praetor testapps
praetor prodapps
Make a UNIX tape backup using TAR
While most DBA use sophisticated tools such
as ebu and rman to manage Oracle backups, it is important to note
that there are easy to use UNIX commands for copying Oracle files
onto tape devices.
UNIX has several native utilities called tar
and cpio for file copying to tape archives. Here is an example
of a simple Oracle backup to a tape.
#!/bin/ksh
echo Start
`date`
#**********************************************
# Mount the tape and rewind
#**********************************************
mt -f /dev/rmt/2m rew
#**********************************************
# Copy directories onto /dev/rmt/2m
#**********************************************
tar cvf /dev/rmt/2m /u01/oradata/prodsidDB /u02/oradata/prodsidDB
echo End
`date`
A UNIX script to detect listener failure
Starting in SQL*Net and on into Net8, there
are special UNIX conditions that may cause the listener to start
refusing Oracle connections. To alleviate theses issues with a
hanging listener, the Oracle DBA can write a UNIX script to
constantly monitor the listener’s ability to accept connections.
Note that this script is only for Oracle databases that use
dedicated connections, and it should not be used if you have
installed the multi-threaded server.
This script uses the ps and wc commands to
detect if the listener process is running and if the database is
running, it re-starts the listener process with the Oracle UNIX
lsnrctl command.
# See if
listener is running.
lsnr_up=`ps
-eaf |grep lsnr |grep -v grep |wc -l`
# If not, see if database is running . . .
if test $lsnr_up
-eq 0 then
pmon_up=`ps -eaf |grep -i pmon
|grep -v grep |wc -l`
smon_up=`ps -eaf |grep -i smon
|grep -v grep |wc -l`
dbwr_up=`ps -eaf |grep -i dbwr
|grep -v grep |wc -l`
lgwr_up=`ps -eaf |grep -i lgwr
|grep -v grep |wc -l`
# If database is up, restart listener
if test $pmon_up -gt 0 &&
test $smon_up
-gt 0 &&
test $dbwr_up
-gt 0 &&
test $lgwr_up
-gt 0
then # Oracle
is up
lsnrctl start
$1 # Start tnslsnr
echo 'Started
tnslsnr ' `date`
fi
fi
A simple script to exit when the database
is not running
When writing scripts that visit many Oracle
servers it is always a good idea to check if the Oracle database is
running before attempting a connection to the database.
#*************************************************************
# Let's exit immediately if the database is not running . . .
#*************************************************************
check_stat=`ps -ef|grep ${ORACLE_SID}|grep pmon|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -lt 1 ]
then
exit 0
fi
Detect when Oracle is not accepting
connections:
When automating Oracle maintenance with
scripts, you should always test to ensure that Oracle is accepting
database connections before attempting to perform maintenance on the
database. We begin by creating a stand-alone script that
connects to the database.
test_connection.ksh
#!/bin/ksh
#*************************************************************
# Test to see if Oracle is accepting connections
#*************************************************************
$ORACLE_HOME/bin/sqlplus -s /<<!
select * from v\$database;
exit
!
Once we have this script working, we write
another shell script that submits the script in the background using
the UNIX nohup command. We then wait to 30 seconds with the
UNIX sleep command, and then inspect the messages in the listing
file for the connection.
#**************************************************************
# Submit the Oracle task in the background
#**************************************************************
nohup test_connection.ksh > /tmp/check_$ORACLE_SID.ora 2>&1 &
#**************************************************************
# Wait for 30 seconds with the UNIX sleep command
#**************************************************************
sleep 30
#*************************************************************
# If the script is done, check for the string “error”
# in the listing file from the prior nohup command:
#*************************************************************
check_stat=`cat /tmp/check_$ORACLE_SID.ora|grep -i error|wc -l`;
oracle_num=`expr $check_stat`
#*************************************************************
# If there is an error, make the script exit with a return code of
99
#*************************************************************
if [ $oracle_num -gt 0 ]
then
exit 99
fi
 |
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. |
