Analyze Oracle Database schema

Why is it important to analyze , and what is analyze anyway ?
well analyze is a method to gather statistics on table objects in order for the optimizer
to choose the best way for executing queries .
for example the optimizer may choose to use full table scan or to use table index ,
it does so by looking at the table statistics .
Oracle doesn’t gather statistics on schema’s all by it self and the DBA must do it
as part of database maintenance . its is wise to analyze your schema on regular basis’s ,
depend on the data changes . i will show you a small script that can help you analyze your schema .

# This script will call dbms_stats to Analyze SCHEMA_OWNER schema
ORACLE_SID=<sid name>
ORACLE_BASE=<path to oracle base>
ORACLE_HOME=<path to oracle home>
$ORACLE_HOME/bin/sqlplus -s " / as sysdba" <<eof1
spool /tmp/Analyzing.txt
exec dbms_stats.gather_schema_stats(ownname=>'SCHEMA_OWNER',estimate_percent=>5,cascade=>true);

convert html to csv

There are many scripts using perl,php,python etc. that will do this for you
but the way you are about to see will make you smile of the simplicity of it .
instead of going over the file line by line and search inside , i am going to use
a tool that is going to do that for me . this tool is lynx , the console browser .
and here is how it works :

lynx -dump file_name.html

now, lets say our table looks like this :

1 2 3 4
5 6 7 8
9 10 11 12

to create a csv file from it , one would do something like this :
use ‘tr’ command to fold all spaces

tr -s " "

now , lets use sed to do the rest of the work for us .
this sed command will remove the first space/tab from the beginning of the lines

sed  's/^[ t]*//'

this sed command will place comma “,” as delimiter instead of space delimiter

sed  's/ /,/g'

So in the end we will end up with a simple one line command that creates a csv from html

lynx -dump file_name.html | tr -s " "|sed -e 's/^[ t]*//' -e 's/ /,/g' > file_name.csv

* note : the method shown here can work as long as there are no spaces in cell data

tab completion for sqlplus

Every person who ever worked with bash tab auto complete
know how fast and convenient it is to use .
but when it comes to Oracle sqlplus under Linux there is no such thing as tab completion
on most cases even the arrow keys doesn’t work . in the simple following steps
we will fix all that by using a tool called rlwrap .
in this post i used CentOS because it is similar to RedHat
but before we can compile we need to solve some dependencies first .
rlwrap depends on GNU lib named readline , let compile this one first

yum install ncurses-devel.i386 libtermcap-devel.i386
tar -xzvf readline-6.2.tar.gz
cd readline-6.2/
make && make install
echo "/usr/local/lib/" >>/etc/

now we are ready to to compile rlwrap ,

tar -xzvf rlwrap-0.37.tar.gz
cd rlwrap-0.37/
make && make install

After rlwrap is installed we can start using it . note that it will save the commands
history under the user home directory so don’t use login with passwords .
using rlwrap is simple as starting it before running sqlplus
rlwrap sqlplus myuser/
but there will be no tab auto complete …. well here is the trick ,
rlwrap can take a lists of auto complete words list as a file .
so just create the a file containing all words you wish to auto complete and
just run it
rlwrap -f ~/my_completions sqlplus myuser/
note :
you can put all Oracle dictionary on that file ,all your schema objects
along with all PL/SQL commands

startup ubuntu in text mode

This common task turn out to be a pain in the … if you dont know how to do it .
Ubuntu unlike other Linux distributions moved from traditional sysvinit to Upstart .
i will not name all the differences but just one fact that setting a service to run in
requiered runlevel is not done by stop/start links in /etc/rcX.d anymore, but by init scripts .
Lets start . I use lxde as my desktop and lxdm as my desktop manager ,
i would like to have no Xserver on runlevel 3 ( runlevel 2 is Ubuntu default ) .
in order to do so i edit the file /etc/init/lxdm.conf and set the runlevels i wish lxdm
to start and stop . that is done by the commands
start on runlevel [2]
stop on runlevel [0136]

start on runlevel , was not found on the script so i added it .
now lxdm will start only on runlevel 2 and would stop on runlevel 0,1,3,6
Now boot into grub entry and append the runlevl to the kernel parameters
linux /boot/vmlinuz-2.6.38-8-generic root=/dev/sda1 ro quiet 3
to make it permanently you can create a new menu entry on /boot/grub/grub.cfg
You can read more on upstart at

Rotate Oracle logs

Oracle database logs doesn’t rotate by it self , and as time goes by, your
server may hold logs that are too big to read and takes too much storage space .
this can get your server to a maximum capacity , and in some cases crush your server .
The best thing i found is to use logrotate to handle this rotations .
there are 2 files that needs to be rotate ( depend on your infrastructure ) this files
are alert log and listener log . both can grow to unlimited size .
Create a new logrotate rule by edit a files
/etc/logrotate.d/oracle-alert and /etc/logrotate.d/oracle-listener
the oracle-alert file should point to the alert log usualy located under
here is an example of oracle-alert , that will rotate weekly and store for 4 files back
also it will compress that backups and create a new file with the correct permissions .
* note that Oracle will create a new alert log ,if the file is missing, upon next event

/opt/app/DB/diag/rdbms/example/example1/trace/alert_example1.log {
rotate 4
create 640 oracle oinstall

the next thing to handle is the listener , now the listener log cannot be remove just like that
if you do so , the listener would stop logging into that file . solving it with a special
commands that restart just the loger of the listener .
the location of the listener log is under $ORACLE_HOME/diag/tnslsnr/<database>/listener/trace/listener.log
This example shows how to weekly rotate and compress

/opt/app/DB/diag/tnslsnr/example/listener/trace/listener.log {
rotate 4
create 640 oracle oinstall
su - oracle "lsnrctl set Log_status off"
su - oracle "lsnrctl set Log_status on"

Installing Oracle RAC 11.2 under CentOS

—————– Installing Oracle RAC release 11.2 under CentOS —————-
This tutorial was tested under CentOS x86_64 kernel 2.6.18-194.3.1.el5
this version is old but it matches the ASMlib .
also this tutorial was deploy on one node only , if there are more nodes
in the cluster , a shared storage should be used . oracle support NAS,SAN,NFS,RAW DEVICES etc.
Table of content
1. Preparing OS properties
2. Creating users and groups
3. Installing and setting ASMLib
4. verifying Grid Infrastructure preparation
5. Installing Grid Infrastructure
6. Installing Database software
7. creating database
1. Preparing the operating system parameters
list of packages require to be installed on all nodes
the versions should be >=
compat-libstdc++-33-3.2.3 (32 bit)
glibc-2.5-12 (32 bit)
glibc-devel-2.5-12 (32 bit)
libaio-0.3.106 (32 bit)
libgcc-4.1.1 (32 bit)
libstdc++-4.1.1 (32 bit)
libstdc++-devel 4.1.1
yum command :

[root]# yum install compat-libstdc++-33.i386 compat-libstdc++-33.x86_64 elfutils-libelf.x86_64 elfutils-libelf-devel.x86_64 glibc.i686 glibc.x86_64 glibc-common.x86_64 glibc-devel.i386 glibc-devel.x86_64 libaio.i386 libaio.x86_64 libaio-devel.x86_64 libgcc.i386 libgcc.x86_64 libstdc++.i386 libstdc++.x86_64 libstdc++-devel.x86_64 binutils gcc gcc-c++ make -y

1.1 First thing is to set all recommended kernel parameters
edit the file /etc/sysctl.conf , and change/add this parameters

# Oracle Setting
kernel.sem=250        32000   100      128
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.file-max = 6815744

Then run the command sysctl -p as root
so that the new settings will be load into the kernel parameters
1.2 Setting shared partition permissions , in order for ASM/Oracle to gain ownership
In this step it depend on the partitions you have attached from the storage .
here we are using local partitions but its the same , we gonna use 3 partitions
sda6,7,8 where sda6 will be for voting and OCR as ASM , sda7 will be for undo and sda8 will hold the data

[[email protected]~]# fdisk -lDisk /dev/sda: 896.9 GB, 896998047744 bytes
255 heads, 63 sectors/track, 109053 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytesDevice Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        2550    20482843+  83  Linux
/dev/sda2            2551        5100    20482875   83  Linux
/dev/sda3            5101        6120     8193150   82  Linux swap / Solaris
/dev/sda4            6121      109053   826809322+   5  Extended
/dev/sda5            6121       18279    97667136   83  Linux
/dev/sda6           18280       18402      987966   83  Linux
/dev/sda7           18403       18525      987966   83  Linux
/dev/sda8           18526       30684    97667136   83  Linux

Create a udev rule for ownership and permissions , by creating a file /etc/udev/rules.d/51-oracle.permissions.rules
and entering this lines :

# OCR disks
KERNEL=="sda6" , OWNER="grid" GROUP="asmdba" , MODE="0660"
# UNDO disks
KERNEL=="sda7" , OWNER="grid" GROUP="asmdba" , MODE="0660"
# DATA disks
KERNEL=="sda8" , OWNER="grid" GROUP="asmdba" , MODE="0660"

after creating the file , the system must reboot (but first create grid user in step 2), after reboot the device should look like this :

[[email protected] app]# ls -l /dev/sda*
brw-r----- 1 root disk     8, 0 Mar 30 16:17 /dev/sda
brw-r----- 1 root disk     8, 1 Mar 30 16:17 /dev/sda1
brw-r----- 1 root disk     8, 2 Mar 30 16:17 /dev/sda2
brw-r----- 1 root disk     8, 3 Mar 30 16:17 /dev/sda3
brw-r----- 1 root disk     8, 4 Mar 30 16:17 /dev/sda4
brw-r----- 1 root disk     8, 5 Mar 30 16:17 /dev/sda5
brw-rw---- 1 grid asmdba 8, 6 Mar 30 16:17 /dev/sda6
brw-rw---- 1 grid asmdba 8, 7 Mar 30 16:17 /dev/sda7
brw-rw---- 1 grid asmdba 8, 8 Mar 30 16:17 /dev/sda8

1.2.1 Network settings ,
each node must have at least 2 interfaces , one for public and one for interconnect
also each node must have 3 hosts resolved for example
eth0 = (public)
eth0:0 = (vip) no need to set , but needs to be resolved
eth1 = (private)
etc/hosts should look like this : dbtest dbtest-vip dbtest-priv
1.3 DNS settings ,
for Oracle version 11.2 we need to set a SCAN ip and VIP in the DNS,
Oracle recommend that we set 3 ip for SCAN
SCAN ip should be part of the host domain for example IN A IN A IN A IN A IN A IN A
* its a good idea to set all the domains in DNS ,
* If you want to use GNS you need to have DHCP as well ( we are not going to use GNS here )
1.4 fake CentOS as Redhat
Setting OS as fake REDHAT , backup this files :
/etc/issue and /etc/redhat-release
edit this files and replace the CentOS with this line
Red Hat Enterprise Linux Server release 5 (Tikanga)
also install the dummy package redhat-release dummy
can be found here as spec file and compile it,
or you can download the RPM i had compile from that spec from this site here
and last thing is running this command , for the grid install process

[[email protected]~]# echo "redhat-release-5Server-5" > /tmp/.linux_release
[[email protected]~]# chattr +i /tmp/.linux_release

2. Creating users and groups ,
we need to create 2 users one for oracle grid system
and one for the database , lets call this users grid and oracle for simplicity .
first create the users , then create the groups :

groupadd oinstall
groupadd dba
groupadd asmadmin
groupadd asmdba
groupadd asmoper
groupadd oper
useradd -g oinstall -G dba,oper,asmdba oracle
useradd -g oinstall -G dba,asmoper,asmdba,asmadmin grid

2.1 setting ssh connections between all nodes with no password
all nodes must be free access via ssh from the install server , for that we need to create ssh keys
for both grid and oracle user , by running ssh-keygen and copying the certificates between all nodes
as append to the file ~/.ssh/authorized_keys
2.2 Setting up limits for oracle and grid users .
by default CentOS have a open file limit of 1024 and process ,
this limit will not do for oracle .
setting limits is done by editing the file /etc/security/limits.conf .
oracle and grid users should have unlimited processes and open files

grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

3. Download and install ASMlib can be found at
select the corrent kernel version
in our case
3.1 after installing ASM we need to configure and create our ASM partitions ,
make sure you choose the correct user and group for ASM

[[email protected]~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.Default user to own the driver interface [grid]:
Default group to own the driver interface [asmdba]:
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ][[email protected] app]# /etc/init.d/oracleasm listdisks
[[email protected] app]# /etc/init.d/oracleasm createdisk OCR /dev/sda6
Marking disk "OCR" as an ASM disk:                         [  OK  ]
[[email protected] app]# /etc/init.d/oracleasm createdisk UNDO /dev/sda7
Marking disk "UNDO" as an ASM disk:                        [  OK  ]
[[email protected] app]# /etc/init.d/oracleasm createdisk DATA /dev/sda8
Marking disk "DATA" as an ASM disk:                        [  OK  ]
[[email protected] app]# /etc/init.d/oracleasm listdisks
UNDO[[email protected] app]# ls -l /dev/oracleasm/disks/
total 0
brw-rw---- 1 grid asmdba 8, 8 Mar 31 10:33 DATA
brw-rw---- 1 grid asmdba 8, 6 Mar 31 10:33 OCR
brw-rw---- 1 grid asmdba 8, 7 Mar 31 10:33 UNDO

4. Unzip the grid infrastructure under grid user home directory
cd into grid folder and run the with parameters
./ stage -pre crsinst -n <node name>
you can also create a fixup file that can help on solving all problems
4.1 solving NTP issue
by edit the file /etc/sysconfig/ntpd
and adding -x to ntp options
OPTIONS=”-x -u ntp:ntp -p /var/run/”
5. run the Grid Infrastructure installer and follow this guide lines
create 2 directories , one for Grid and one for the Software , and set permissions
note that the grid and software has different homes !

[[email protected]~]# mkdir -p /opt/app/OCR/base
[[email protected]~]# mkdir -p /opt/app/OCR/software
[[email protected]~]# chown -R grid:oinstall /opt/app/OCR[[email protected]~]# mkdir -p /opt/app/DB
[[email protected]~]# chown -R oracle:oinstall /opt/app/DB

5.1 next steps are part of the Grid installer
a. choose “Install and configure Grid Infrastructure for cluster”
b. choose the “Advance installation”
c. choose English and next
d. set this settings :
Cluster name : dbtest_cluster
SCAN name :
SCAN port : 1521
* uncheck use of GNS
e. select the nodes you want to install
f. make sure the correct interfaces are set with public and private
g. choose the OCR to be installed on ASM
h. now we choose the ASM partition we dedicated for OCR and name the diskgroup OCR
i. set both password the same for SYS and ASMSNMP
j. do not install IPMI
k. set the os groups for management
OSDBA= asmdba
OSOPER= asmoper
l. set oracle base ( /opt/app/OCR/base ) , and software base ( /opt/app/OCR/software ) from stage 5.
m. set the Inventory folder ( i choose the default ) /opt/app/oraInventory
o. the check may fail on some parameters , fix and continue
p. you will be prompted for running from root terminal , all should come clean ,
and must be run on all nodes before you could continue .
before running the script , you must edit the file $OCR_HOME/lib/
and change this lines to support CentOS , or you will get an error

ADVM/ACFS is not supported on centos-release-5-5.el5.centos

so edit the file and make sure CentOS is one of the supported OS

if (($release =~ /^redhat-release/) || # straight RH
($release =~ /^enterprise-release/) || # Oracle Enterprise Linux
($release =~ /^centos-release/)) # CentOS

q. the install should now finish with no errors !
6. installing database , for that we need to it as oracle user ( very important )
this are the steps from the installer GUI
a. install the database software only , later we will create a database
b. choose the RAC and the nodes involved . and test ssh conectivity
c. choose English and continue
d. we use Standard edition
e. here we set the ORACLE_BASE as we did in the step 5 and ORACLE_HOME under ORACLE_BASE
in our case i choose
ORACLE_BASE = /opt/app/DB
ORACLE_HOME = /opt/app/DB/11.2.0
f. choosing OSDBA and OSOPER as dba,oper
g. fix any warning and continue (or ignore)
h. during install you will be request to run script as root user
7. creating ASM disk groups
by running as user grid $OCR_HOME/bin/asmca
we will create the +UNDO and +DATA
8. Create a database by
running the $ORACLE_HOME/bin/dbca as user oracle

How to build LDAP

In this HowTo we will build a simple LDAP tree ,
the scope of this how to is only seeting up LDAP server .
the system used in this how to is CentOS 5.5 i386
1. require packages :
2. building LDAP tree :
edit file /etc/openldap/slapd.conf
and put your domain and suffix , as well as the ldap root password
you can use use slappasswd for encryping the password for encrypting the password

suffix          "dc=CentOS"
rootdn          "cn=root,dc=CentOS"
rootpw          {SSHA}BbW/c1wp2uyM+mHR7EN+mVHkfHxBRXmg

* you can test the LDAP server configuration using
slaptest -u
3. create the database config file :
the easy way to do that is to copy the example

cp /etc/openldap/DB_CONFIG.example /var/lib/ldap/DB_CONFIG

now we can start building the tree

~]# service ldap start
Checking configuration files for slapd:
config file testing succeeded                       [  OK  ]
Starting slapd:                                            [  OK  ]

4. creating the base tree :
this tree will include the domain (suffix) users and groups
create an ldif base file ( you can use /usr/share/openldap/migration/ for that )
a simple base would look something like this ( lets call it base.ldif )

dn: dc=CentOS
dc: CentOS
objectClass: top
objectClass: domain 
dn: ou=People,dc=CentOS
ou: People
objectClass: top
objectClass: organizationalUnit
dn: ou=Group,dc=CentOS
ou: Group
objectClass: top
objectClass: organizationalUnit

now add it to the LDAP tree via ldapadd

~]# ldapadd -x -W -D "cn=root,dc=CentOS" -f base.ldif
Enter LDAP Password:
adding new entry "dc=CentOS" 
adding new entry "ou=People,dc=CentOS"
adding new entry "ou=Group,dc=CentOS"

once its finished we can start adding users and groups :
lets add two groups to our LDAP , by creating groups.ldif file

dn: cn=group1,ou=Group,dc=CentOS
objectClass: posixGroup
objectClass: top
cn: group1
userPassword: {crypt}x
gidNumber: 5000 
dn: cn=group2,ou=Group,dc=CentOS
objectClass: posixGroup
objectClass: top
cn: group2
userPassword: {crypt}x
gidNumber: 5001

now add this groups under the LDAP tree

~]# ldapadd -x -W -D "cn=root,dc=CentOS" -f groups.ldif
Enter LDAP Password:
adding new entry "cn=group1,ou=Group,dc=CentOS" 
adding new entry "cn=group2,ou=Group,dc=CentOS"

now lets add two users :
again create a users.ldif file ,
the password can be created via slappasswd

dn: uid=user1,ou=People,dc=CentOS
uid: user1
cn: My name is user1
objectClass: account
objectClass: posixAccount
objectClass: top
objectClass: shadowAccount
userPassword: {SSHA}cEcqMNFk1Jd1N1L7U1JdybZdsb+5qG2T
shadowLastChange: 14791
shadowMax: 99999
shadowWarning: 7
loginShell: /bin/bash
uidNumber: 6001
gidNumber: 6001
homeDirectory: /home/user1
gecos: My name is user1 
dn: uid=user2,ou=People,dc=CentOS
uid: user2
cn: My name is user2
objectClass: account
objectClass: posixAccount
objectClass: top
objectClass: shadowAccount
userPassword: {SSHA}cEcqMNFk1Jd1N1L7U1JdybZdsb+5qG2T
shadowLastChange: 14791
shadowMax: 99999
shadowWarning: 7
loginShell: /bin/bash
uidNumber: 6002
gidNumber: 6002
homeDirectory: /home/user2
gecos: My name is user2

you may wonder why there are so many entries we need to fill ,
well thats because each attribute we add to the LDAP , we will need to fill
all require entries .
lets add this users :

~]# ldapadd -x -W -D "cn=root,dc=CentOS" -f users.ldif
Enter LDAP Password:
adding new entry "uid=user1,ou=People,dc=CentOS" 
adding new entry "uid=user2,ou=People,dc=CentOS"

and that’s about it , in order to manage LDAP in a more friendly manner
you can use one of many ldap managment tools like phpldapadmin etc.

Eternal BASH History

Have you ever needed some command that you worked so hard for it
but it has being too long ago that it is already gone from Bash history ?
a simple solution is to use PROMPT_COMMAND Bash parameter .
how it works : PROMPT_COMMAND will execute the value as a command prior to issuing each primary prompt.
so if we set something like “history 1 >>~/.myhistory” , bash will write
the last history line to ~/.myhistory . nice ha
so in other words what can i do with that ?
well you can keep track of all commands , but there is a security issue
that needs to be taken , because this file saves all your history command ,
if you enter some passwords like running “mysql –password=blabla …”
it would be saved . so the first step would be to set permissions over the file
so only you can read it ( 0600 )

~$ chmod 600 ~/.myhistory

now lets put it all together by adding the line to your .bashrc

PROMPT_COMMAND='history 1 >> ~/.myhistory'

now when ever you login , the file will hold all your history ,
but now you see that every you press will write as the last command
as duplicate lines . the way i found to go around this is to simply remove duplicate
lines at login/logout . add this lines to ~/.bash_logout or ~/.bashrc

cat .myhistory |uniq >.myhistory2
mv -f .myhistory2 .myhistory 

Auto compleate service command

Have you ever wanted to have Tab to auto complete
any service and commands that comes with it ?
well i am going to show you how easy it is to do so .
all you need is to install a simple package that will do all the
hard work of typing for you .
the package name is bash-completion
once installed , you need to reload your profile and that’s it
just type service and tap will show you all services available
on your /etc/init.d . after you select the service you can hit tab once more
and it will display/complete the command associate with that service .
there is how ever another way of doing exactly that , by adding
words to bach tab completion . in this method all one needs to do is
run this command as root

~#complete -W "$(ls /etc/init.d/)" service 

the advantage of the package bash-completion is that it gives you some more
options as completion . for example if you use the cd command ,
it would auto complete only the directories listed under the current location .
without bash-completion the cd command + tab may show you files as well and
the best trick is that bash-completion also auto complete any commands related
to any service listed under /etc/init.d
choose what ever is best for you