Saturday, November 13, 2010

Changing CPU frequency scaling governor policy on RHEL 5

After upgrading my processor to Intel Core i7 870 2.93GHz, I installed RHEL 5. To my surprise, /proc/cpuinfo shows CPU speed as 1.2GHz
# cat /proc/cpuinfo | grep "cpu MHz" | uniq
cpu MHz         : 1197.000

But my processor specification is 2.93GHz (with Turbo boost). I observed that processor speed increasing when there is load on machine. For example if I run this one terminal (to make a CPU work hard)
# cat /dev/urandom > /dev/null

And check speed of processor in another terminal, it shows
# cat /proc/cpuinfo | grep "cpu MHz"
cpu MHz         : 1197.000
cpu MHz         : 1197.000
cpu MHz         : 1197.000
cpu MHz         : 2927.000
cpu MHz         : 1197.000
cpu MHz         : 1197.000
cpu MHz         : 1197.000
cpu MHz         : 1197.000

One processor is working hard to dump random numbers into black hole. So, its speed increased to highest level. This is because, this processor supports frequency scaling. And RHEL 5 by default uses scaling governor policy as "ONDEMAND". It means CPUs run at low frequency and scale frequencies up when needed. 

To make all CPUs to run their highest speeds even when idle, scaling governor policy should be changed to "PERFORMANCE". The file /etc/sysconfig/cpuspeed contains an option for scaling governor policy.
GOVERNOR=ondemand
I changed this to
GOVERNOR=performance

And restarted cpuspeed daemon
# service cpuspeed restart
Disabling ondemand cpu frequency scaling:                  [  OK  ]
Enabling performance cpu frequency scaling:                [  OK  ]

Now, I can see that all processors are running at their highest speeds.
# cat /proc/cpuinfo | grep "cpu MHz"  | uniq
cpu MHz         : 2927.000

Saturday, October 23, 2010

crsstat –t detailed output

 crsstat –t shows truncated resource names like below
# /u01/app/crs/bin/crs_stat -t
Name           Type           Target    State     Host       
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    fire       
ora....RE.lsnr application    ONLINE    ONLINE    fire       
ora.fire.gsd   application    ONLINE    ONLINE    fire       
ora.fire.ons   application    ONLINE    ONLINE    fire       
ora.fire.vip   application    ONLINE    ONLINE    fire       
ora.racdb.db   application    ONLINE    ONLINE    water      
ora....b1.inst application    ONLINE    ONLINE    fire       
ora....b2.inst application    ONLINE    ONLINE    water      
ora....SM2.asm application    ONLINE    ONLINE    water      
ora....ER.lsnr application    ONLINE    ONLINE    water      
ora.water.gsd  application    ONLINE    ONLINE    water      
ora.water.ons  application    ONLINE    ONLINE    water      
ora.water.vip  application    ONLINE    ONLINE    water   

The resource name is truncated and dots are used in Name column.

To view complete names, here is a wrapper script, (saved as my_crs_stat.sh in my system)
command=/u01/app/crs/bin/crs_stat
format_string="%-35s %-15s %-10s %-20s \n"

printf "$format_string" "Name" "Type" "Target" "State"
printf "$format_string" | tr ' ' '-'

$command | cut -f 2 -d= | tr '\n' ',' | sed s/,,/:/g | tr ':' '\n' | while read line
do
    name=`echo $line | cut -f1 -d','`
    type=`echo $line | cut -f2 -d','`
  target=`echo $line | cut -f3 -d','`
   state=`echo $line | cut -f4 -d','`

  printf "$format_string" "$name" "$type" "$target" "$state"
done

Output of the script looks like below
# ./my_crs_stat.sh
Name                                Type            Target     State               
------------------------------------------------------------------------------------
ora.fire.ASM1.asm                   application     ONLINE     ONLINE on fire      
ora.fire.LISTENER_FIRE.lsnr         application     ONLINE     ONLINE on fire      
ora.fire.gsd                        application     ONLINE     ONLINE on fire      
ora.fire.ons                        application     ONLINE     ONLINE on fire      
ora.fire.vip                        application     ONLINE     ONLINE on fire      
ora.racdb.db                        application     ONLINE     ONLINE on water     
ora.racdb.racdb1.inst               application     ONLINE     ONLINE on fire      
ora.racdb.racdb2.inst               application     ONLINE     ONLINE on water     
ora.water.ASM2.asm                  application     ONLINE     ONLINE on water     
ora.water.LISTENER_WATER.lsnr       application     ONLINE     ONLINE on water     
ora.water.gsd                       application     ONLINE     ONLINE on water     
ora.water.ons                       application     ONLINE     ONLINE on water     
ora.water.vip                       application     ONLINE     ONLINE on water


Wednesday, October 20, 2010

Using "alter user" privilege, connecting to Oracle database as different user without knowing his/her password

Linux root user can use a command "su -" to work as different user without entering a password of that user. For example, being a root user, I can enter the command,

[root@myserver ~]# su - littleboy
[littleboy@myserver ~]$

I can work as normal user littleboy without knowing the password of user littleboy. It is just possible as I am root.

Now the question, is it possible to work as normal user if I have dba role without knowing normal user password on Oracle database? In simple terms how to su on Oracle database? Here is the way.

Consider the following setup, where user bigboy is dba, and littleboy is normal user.

$ sqlplus / as sysdba
SQL> create user littleboy identified by littleboy1;
User created.
SQL> grant connect, resource to littleboy;
Grant succeeded.
SQL> create user bigboy identified by bigboy1;
User created.
SQL> grant dba to bigboy;
Grant succeeded.

If bigboy wants to connect to database as littleboy without knowing password of littleboy, the steps are,

$ sqlplus bigboy/bigboy1
SQL> alter user littleboy grant connect through bigboy;
User altered.

Now bigboy can connect to database as littleboy user like this (as if bigboy switched user to littleboy), Note that user bigboy is entering his password, not littleboy's password.

$ sqlplus bigboy[littleboy]/bigboy1
SQL>

Here bigboy is a proxy user for littleboy. List of proxy users can be found from proxy_users view.

SQL> select proxy, client from proxy_users;
PROXY                          CLIENT
------------------------------ ------------------------------
BIGBOY                         LITTLEBOY

To revoke this connect through privilege for bigboy,

SQL> alter user littleboy revoke connect through bigboy;
User altered.

Actually to connect as different user, bigboy need not to have dba role, he just needs "alter user" privilege. Of course, "alter user" is a powerful privilege, because it allows bigboy to connect as any user who has dba privilege!

Monday, October 11, 2010

A script to make autoextend off for data files


To make sure that the policy of "no datafiles should be autoextend on", if there are any datafiles have autoextend on, I have make them autoextend off. To automate it, here is a simple script.

set serveroutput on;
declare
stmt varchar2(600);
begin
 for i in (select file_name from dba_data_files where AUTOEXTENSIBLE='YES')
 loop
  stmt := 'alter database datafile ''' || i.file_name || ''' autoextend off';
  dbms_output.put_line(stmt);
  execute immediate stmt;
 end loop;
end;
/