A Case of Changing or Creating OSDBA and OSOPER Groups

Goal

Created an Unix user (e.g. tier1 ) in the Oracle database server that can perform some database administration tasks but cannot be used to view or modify any application data.


Current typical Unix/Liux Environment:

- OS user: "oracle" is the Oracle software owner and its primary group is "dba" (i.e. Oracle database files have ownership oracle:dba )

- Assuming OSDBA group is "dba" (i.e. every os user that belongs to "dba" group can login as sysdba and view any data)

Suggested changes


- add a Unix group "oper" if not exist
# /usr/sbin/groupadd -g 503 oper

- add a Unix group "dbax" if not exist
# /usr/sbin/groupadd -g 504 dbax

- Change OSDBA group to "dbax" and define OSOPER group in $ORACLE_HOME/rdbms/lib/config.c, edit and ensure the following:
#define SS_DBA_GRP "dbax"
#define SS_OPER_GRP "oper"
shutdown instance and relink:
  cd $ORACLE_HOME/rdbms/lib/
   mv config.o config.o.orig
    make -f ins_rdbms.mk ioracle

- assign "dbax" as secondary unix group to "oracle"
# /usr/sbin/usermod -g dba -G dbax oracle

- create tier1 user with priamry group as "dba" and secondry group as "oper" # /usr/sbin/useradd -g dba -G oper tier1

Test cases


Test case 1 - "oracle" user can login as sysdba as it belongs to OSDBA group:dbax

[oracle@localhost ~]$ id
uid=500(oracle) gid=54322(dba) groups=504(dbax),54322(dba)
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 13:37:51 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> shutdown 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             390073016 bytes
Database Buffers           58720256 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.
SQL>

Test case 2 - "tier1" user can login as sysoper but not as sysdba

[tier1@localhost ~]$ id
uid=502(tier1) gid=54322(dba) groups=54322(dba),54324(oper)
[tier1@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 13:50:41 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> conn / as sysoper
Connected to an idle instance.
SQL> startup 
ORACLE instance started.
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Test case 3 - "tier1" and "oracle" belong to same primary OS group "dba", so "tier1" can operate on various Oracle files based on group privillege:

[tier1@localhost 2013_05_09]$ id
uid=502(tier1) gid=54322(dba) groups=54322(dba),54324(oper)
[tier1@localhost 2013_05_09]$ ls -lh o1_mf_1_401_8rr62tq0_.arc
-rw-rw---- 1 oracle dba 7.2M May  9 14:42 o1_mf_1_401_8rr62tq0_.arc
[tier1@localhost 2013_05_09]$ mv o1_mf_1_401_8rr62tq0_.arc /tmp
[tier1@localhost 2013_05_09]$ ls -lh o1_mf_1_401_8rr62tq0_.arc
ls: o1_mf_1_401_8rr62tq0_.arc: No such file or directory

How can I confirm there was an I/O problem?

Last Friday afternoon , our production support team ( this team manages application servers in my working environment) reported that arounnd 2:18 PM there were application queuing and some job time-out issues. Naturally, as a knee-jerk reaction, the problem was described as "Database is slow". Later, some "SCSI trans" errors are found somewhere , which indicated there were some I/O problem. So my question is as a DBA, how can I confirm there is an I/O problem?

At first, I tried to look into the AWR data. I modified awr_io_file.sql script by Karl Arao , in hope to report Average time per read time for the data file during the problem period. However, the script was running more than 20 min without results. I killed the job and decided to research on this mining AWR data approach later. BTW, one drawback I am aware of is that even I can get this data, it will be averaged over 15 min (our AWR snapshot interval). Averaging could hide the real problem.

After googling a while, I found that 'sar' utility in my Solaris platform can shed some light on I/O issue in the recent past history.

If we do 'man sar', we can found the following description about sar -f option:

sar [-aAbcdgkmpqruvwy] [-e time] [-f filename]  [-i sec]  [-s time]

sar  extracts  data  from  a  previously  recorded filename,
either the one specified by the -f option  or,  by  default,
the    standard    system    activity    daily   data   file
/var/adm/sa/sadd for the current day dd.  The  starting  and
ending  times  of the report can be bounded using the -e and
-s arguments with time specified in the  form  hh[:mm[:ss]].
The  -i option selects records at sec second intervals. Oth-
 erwise, all intervals found in the data file are reported.

This is the first time that I am aware of that there are 'standard system activity daily data file'
at /var/adm/sa/. As of now, I can see the following files:

myservnm:/var/adm/sa [orclsid] $ ls -l 
total 2177984
-rw-r--r--   1 sys      sys      241097472 Feb 18 23:55 sa18
-rw-r--r--   1 sys      sys      241097472 Feb 19 23:55 sa19
-rw-r--r--   1 sys      sys      241097472 Feb 20 23:55 sa20
-rw-r--r--   1 sys      sys      241097472 Feb 21 23:55 sa21
-rw-r--r--   1 sys      sys      78691536 Feb 22 07:45 sa22

So it looks like we have 5 days data.

What I did last Friday afternoon was:

sar -A  -e 14:28  -s 14:10   > ~/sar1410_1428.txt

If you don't specify the -f, it will extract from the current day's file, i.e. sa18 last Friday.
In the output file, we can see various info recorded at 5 min snapshot

For example, CPU utilization:

SunOS myservnm 5.10 Generic_142900-05 sun4u    02/18/2011
 
14:10:02    %usr    %sys    %wio   %idle
14:15:03      17      14       0      69
14:20:04      10      11       0      78
14:25:05      20      18       0      62
 
Average       16      15       0      70

In the device activity section, I can find at 14:20 snapshot some lines have very high avserv time.
while normally I saw avserv < 30 ms in my system. for examples:

14:10:02   device        %busy   avque   r+w/s  blks/s  avwait  avserv
 
14:15:03   sd7               1     0.0       1      12     0.0     9.0
           sd7,e             1     0.0       1      12     0.0     9.2
           sd8              32     0.3      46     426     0.0     7.4
....
14:20:04   sd7               1     0.0       1      16     0.0     9.1
           sd7,e             1     0.0       1      16     0.0     9.3
...          
sd1133,e          1     0.0       1      26     0.0    12.9
           sd1134           44     0.8       3      73    78.6   238.1
           sd1134,e         44     0.8       3      73    79.1   239.6
           sd1135           42     0.4       1      20     0.0   485.3
           sd1135,e         42     0.4       1      20     0.0   494.7
           sd1136           42     0.4       0      25     0.0  1000.9
           sd1136,e         42     0.4       0      25     0.0  1042.2
           sd1142            1     0.0       1      40     0.0    13.0
...
  sd1204,e          4     0.0       3      81     0.0    16.0
           sd1205           43     0.4       1      22     0.0   440.3
           sd1205,e         43     0.4       1      22     0.0   448.0
           sd1206           43     0.8       2      60    85.3   254.3
           sd1206,e         43     0.8       2      60    85.9   256.1
           sd1207            1     0.0       1      25     0.0    12.7

This webpage  describes the meaning of some sar options and report fields.
This article called "Identifying disk I/O-bound systems" has information about intepreting sar report.

I am happy I have another tool under my tool-belt.

Open a HTML file from Cygwin command line

Cygwin allows me have a UNIX-link enviroment inside my Windows XP environment. As a Cygwin user, sometimes I would like to open a HTML file from Cygwin command line. To achieve this, first of all I create the following file and put under ~/bin.

---- start of ie.bat -----
@start "" /b "C:\Program Files\Internet Explorer\iexplore.exe" %*
---- end of ie.bat  ------

Secondly, in Cygwin, if we type pwd, we get UNIX-like current path name, i.e. with forward slashs, for example:

$ pwd
/cygdrive/c/Denis_Files/VZ2009

I need to convert it to Windows path name, so I create a shell script as follows:

$ cat ~/bin/pwdw
pwd | sed s#/cygdrive/c#c:# |sed  -e 's#\/#\\#g'

Now if I type 'pwdw' in the Cygwin command windows, I got:

$ pwdw
c:\Denis_Files\VZ2009

To start a HTML file, such as AWR report, what I need to do is:

ie.bat 'c:\Denis_Files\VZ2009\awrrpt_20101003_1300_1330.html'

Just a small tip that makes my life easier :-).

Email from Unix server with attachment and message body

I followed this article "Creating email with a text message and an attachment from standard UNIX" to test the email, however for some reason, I am not successful, may be my OS is Sun Solaris. What the article described is AIX.

I tried the following script, found that I got what I want. unix_attachment.lst file appeared as an attachment and the content of message.txt appeared in the email body.

#!/bin/ksh 
# uuencode the attachment file
uuencode unix_attachment.lst unix_attachment.lst | mailx -s "Message with Attachment -Hello

`cat message.txt`
" denis@email.com 

Email attachment from Unix server

I found that I need to do this. Googled, found this document: Creating email with a text message and an attachment from standard UNIX.

I tested on my server, if only need the attachment, I can do:

$ uuencode pfile_sac.txt pfile_sac.txt | mailx -s "pfile_sac" myname@mycompany.com

Solaris Performance Monitoring & Tuning - iostat , vmstat & netstat

This is a reading note from an article on: http://www.adminschoice.com/docs/iostat_vmstat_netstat.htm

iostat
=========
iostat -xtc 5 2

The values to look from the iostat output are:

* Reads/writes per second (r/s , w/s)
* Percentage busy (%b)
* Service time (svc_t)

If a disk shows consistently high reads/writes along with , the percentage busy (%b) of the disks is greater than 5 percent, and the average service time (svc_t) is greater than 30 milliseconds, then one of the following action needs to be taken

1.)Tune the application to use disk i/o more efficiently by modifying the disk queries and using available cache facilities of application servers .

2.) Spread the file system of the disk on to two or more disk using disk striping feature of volume manager /disksuite etc.

3.) Increase the system parameter values for inode cache , ufs_ninode , which is Number of inodes to be held in memory. Inodes are cached globally (for UFS), not on a per-file system basis

4.) Move the file system to another faster disk /controller or replace existing disk/controller to a faster
one.

vmstat
========
A. CPU issues:

Following columns has to be watched to determine if there is any cpu issue

* Processes in the run queue (procs r)
* User time (cpu us)
* System time (cpu sy)
* Idle time (cpu id)

Problem symptoms:
-----------------
1.) If the number of processes in run queue (procs r) are consistently greater than the number of CPUs on the system it will slow down system as there are more processes then available CPUs .

2.) if this number is more than four times the number of available CPUs in the system then system is facing shortage of cpu power and will greatly slow down the processess on the system.

3.) If the idle time (cpu id) is consistently 0 and if the system time (cpu sy) is double the user time (cpu us) system is facing shortage of CPU resources.

B. Memory Issues:

Memory bottlenecks are determined by the scan rate (sr) . The scan rate is the pages scanned by the clock algorithm per second. If the scan rate (sr) is continuously over 200 pages per second then there is a memory shortage

netstat
=======
netstat -rn

unix tool vmstat - show how the machine is utilzed

This article showed how to interpret the vmstat on AIX
Automated Oracle Tuning Initial Procedures - Part 2

Solaris OS commands

Some command under Solaris

# prtdiag -- Show some hardware info
# swap –s 查看交换区空间大小
# df -k tmp 查看tmp空间大小
# prtconf grep Memory size 查看内存大小
# df –k 查看系统磁盘空间
# isainfo –kv 查看系统内核是否是64位
# pkginfo -i SUNWarc SUNWlibms SUNWil0f SUNWbtool SUNWsprot SUNWi1cs SUNWhea SUNWsprox

This article describes how to set swap memory:
Note: swap files != SWAP memory

Setting SWAP Memory Under Solaris