Quick reference: pre-installation steps of Oracle database 10g and 11g (non RAC) on RHEL 5

1. Users, groups and directories

# groupadd oinstall

# groupadd dba

# groupadd oper

# useradd -g oinstall -G dba,oper oracle

# passwd oracle

# mkdir -p /u01/app/oracle

# chown oracle:oinstall /u01/app

# chown oracle:oinstall /u01/app/oracle

2. Packages (This needs an yum repository to be configured ready. If you do not have it, refer steps here to create)

# yum install compat-libstdc++* elfutils* gcc-c++ libaio-devel libXp sysstat unixODBC-devel

3. Kernel parameters

/etc/sysctl.conf:

fs.aio-max-nr = 1048576

fs.file-max   = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem    = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default =  262144

net.core.rmem_max     = 4194304

net.core.wmem_default =  262144

net.core.wmem_max     = 1048586

To make these effect immediately

# sysctl -p

4. Shell limits

/etc/pam.d/login:

session    required     pam_limits.so

/etc/security/limits.conf:

oracle  soft    nproc    2047

oracle  hard    nproc   16384

oracle  soft    nofile   1024

oracle  hard    nofile  65536

/etc/profile:

if [ $USER = "oracle" ]; then

  ulimit -u 16384 -n 65536

fi

Yet another blog post to create database manually (Oracle version 10.2)

1. Environment variables

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

$ export PATH=$ORACLE_HOME/bin:$PATH

$ export ORACLE_SID=testdb

2. Required directories

$ mkdir -p /dbdir/testdb/{control,data,redo,arch}

$ mkdir -p /dbdir/testdb/admin/{a,b,c,u}dump

3. Entries in pfile $ORACLE_HOME/dbs/inittestdb.ora

db_name                    = testdb

control_files              = (/dbdir/testdb/control/control.dbf)

log_archive_dest_1         = "LOCATION=/dbdir/testdb/arch"

log_archive_dest_state_1   = enable

sga_target                 = 500M

pga_aggregate_target       = 100M

undo_management            = AUTO

undo_tablespace            = undotbs

audit_file_dest            = /dbdir/testdb/admin/adump

background_dump_dest       = /dbdir/testdb/admin/bdump

core_dump_dest             = /dbdir/testdb/admin/cdump

user_dump_dest             = /dbdir/testdb/admin/udump

4. Creating database

$ sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount;

SQL> CREATE DATABASE testdb

   USER SYS IDENTIFIED BY secret1

   USER SYSTEM IDENTIFIED BY secret2

   DATAFILE '/dbdir/testdb/data/system1.dbf' SIZE 200M

      EXTENT MANAGEMENT LOCAL

   SYSAUX DATAFILE '/dbdir/testdb/data/sysaux1.dbf' SIZE 50M

   DEFAULT TABLESPACE worktbs

      DATAFILE '/dbdir/testdb/data/work1.dbf' SIZE 50M

   DEFAULT TEMPORARY TABLESPACE temptbs

      TEMPFILE '/dbdir/testdb/data/temptbs1.dbf' SIZE 50M

   UNDO TABLESPACE undotbs

      DATAFILE '/dbdir/testdb/data/undotbs1.dbf' SIZE 50M

   LOGFILE GROUP 1 ('/dbdir/testdb/redo/redo1.log') SIZE 50M,

           GROUP 2 ('/dbdir/testdb/redo/redo2.log') SIZE 50M

   ARCHIVELOG

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

;

5. Post database creation steps

$ sqlplus / as sysdba

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL> connect system/secret2

SQL> @?/sqlplus/admin/pupbld.sql

SQL> exit

$ echo "$ORACLE_SID:$ORACLE_HOME:N" >> /etc/oratab

YUM repository creation for RHEL 5 (a step by step method)

All the below steps have to be performed as "root" user

1. Choose a directory where you would like to stage all the RPMs. This directory occupies around 3GB disk space. In this example, I am using directory /dump/rpm_repo (but you can choose directory of your choice). Create directory using the command,

 # mkdir -p /dump/rpm_repo

2. Mount Linux installation DVD to a directory (for example /media/dvd)

 # mkdir /media/dvd

 # mount /dev/dvd /media/dvd

Verification:

The command

 # ls /media/dvd

should show files in DVD

3. Copy RPM files in DVD to staging directory you have created in step 1.

 # cp -rv /media/dvd/Server/* /dump/rpm_repo

4. Now change directory to staging directory /dump/rpm_repo, to create RPM repository data.

 # cd /dump/rpm_repo

5. Install createrepo package to have createrepo command in the system that we use in next step.

 # rpm -ivh createrepo*

If you get message "package already installed", you can ignore it.

Verification:

The command

 # rpm -q createrepo

should show version of package installed instead of the message "package not installed".

6. Create RPM repository using the command (observe . at the end of the command, which means create repository with packages in current directory)

 # createrepo -g repodata/comps-rhel5-server-core.xml .

This command may take some time to finish based on your system speed.

Now repository is ready, and we have to share it. We can use NFS, FTP or HTTP for this. In this example, we share it through FTP.

7. Install FTP server package using the command

 # rpm -ivh /dump/rpm_repo/vsftpd*

Verification:

The command

 # rpm -q vsftpd

should show version of package installed instead of the message "package not installed".

8. Create a mount point of staging directory in /var/ftp, so FTP server can share this directory.

 a) Create /var/ftp/rhel_repo

     # mkdir /var/ftp/rhel_repo

 b) Edit /etc/fstab and add this entry at the end for the mount point (Caution: Doing wrong things while editing this file, may make your OS not bootable!).

     /dump/rhel_repo  /var/ftp/rhel_repo    auto   bind  0 0

 c) Now, mount /var/ftp/rhel_repo using the command

     # mount /var/ftp/rhel_repo

Verification:

The commands

 # ls /var/ftp/rhel_repo

should show files that you have in /dump/rhel_repo

Note: You can skip this complicated step (8) if you choose your directory in step (1) as /var/ftp/rhel_repo instead of /dump/rhel_repo. But you have to make sure that /var directory should have enough disk space to store 3GB of packages (which is less likely in a typical work environment).

9. Start FTP service and make it to be auto started after reboot.

 # service vsftpd start

 # chkconfig vsftpd on

Verification:

a) Find the IP address of the system using the command

 # ifconfig

b) Now verify FTP server configuration using the command

 # lftp <IP Address>:/rhel_repo -e "ls;exit"

For example,

 # lftp 192.168.1.15:/rhel_repo -e "ls;exit"

This should show all the files you have in /dump/rhel_repo

Now your YUM repository FTP server is ready to use. Configure YUM repository information like below on the system where you want to install the packages.

1. Create a new file /etc/yum.repos.d/myrhel.repo with following contents

[myrhel]

baseurl=ftp://<IP address of YUM repository server>/rhel_repo

gpgcheck=0

For example,

[myrhel]

baseurl=ftp://192.168.1.15/rhel_repo

gpgcheck=0

You can replace your choice of name instead of "myrhel" above.

2. Now you can install any package that is available in repository with command

For example you can try installing a package named "sysstat" using the command,

# yum install sysstat

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

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