DBAToolZ

The key to scalability is to know where your bottlenecks are. And the only way to know them is by instrumenting your code.

December 15th, 2010 | Tags: scalability instrumentation | Category: Operations | Leave a comment

Just finished dealing with “Hard Limit: maximum user processes” error on Open Solaris 10 while installing 11gR2:

Oracle Metalink was useless — total waste of time — I hate that site now, it’s gone completely into the crapper.

SOLUTION (thanks to David D’Acquisto advice):

1) edit /etc/system as follows:

set shmsys:shminfo_shmmax=12025908428
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set shmsys:shminfo_shmmin=1
set max_nprocs=30000
set maxuprc=16384

2) setup project for oracle user (if it’s already setup then just ignore the duplicate error while running projadd command):

projadd -U oracle user.oracle
projmod -s -K "project.max-sem-ids=(priv,100,deny)" user.oracle
projmod -s -K "process.max-sem-nsems=(priv,256,deny)" user.oracle
projmod -s -K "project.max-shm-memory=(priv,12025908428,deny)" user.oracle
projmod -s -K "project.max-shm-ids=(priv,100,deny)" user.oracle
projmod -s -K "process.max-file-descriptor=(priv,65536,deny)" user.oracle

3) bounce the box:

init 6

Here’s how to check for the setting:

## before above changes were applied
##
$ kstat|grep v_proc
        v_proc                          16362
$
 
$ kstat |grep v_maxup
        v_maxup                         16357
        v_maxupttl                      16357
$
 
## after changes/reboot
##
 
$ kstat|grep v_proc
        v_proc                          30000
$
 
$ kstat |grep v_maxup
        v_maxup                         16384
        v_maxupttl                      29995
$

NOTE: the setting above are based on 16gb of RAM if yours is less/more — adjust as per David’s formula.

February 15th, 2010 | Tags: 11gR2, Installs, maxuprc, max_nprocs, solaris | Category: Installs | Comments (12)

Back in April 2008 I tried to get Django 0.96.1 working on Oracle and soon ran into some issues that clearly indicated to me it was a bit too early to invest into this framework. In fact, that experience, is what turned me heavily onto PHP/OCI8. But I kept Python in mind all this time because I really liked how structured the language was. PHP’s flexibility for WEB development is awesome, but it falls short as a scripting language when it comes to working with OS, networks and stuff we DBAs like to automate.

Few weeks ago I finally dived into python trying to adapt it as a replacement for some of the CORE shell scripts in my monitoring framework. I wanted to switch to SOA model where remote agents would talk to the management server via HTTP instead of SQL*Net. I quickly wrote the client side but then got stuck making a decision as to which language to write the server side component of SOA. Option a) was to continue using Oracle Application Server and mod_plsql, b) switch to PHP/Apache, c) adapt python.

After carefully weighting my options I decided to give python a chance since I wanted to learn what it’s capabilities were for the WEB applications. Django is the up and coming python framework, some even call it python application server. It recently went into production with v1, but it’s actually been around for over 4 years used as a private framework “by a fast-moving online-news operation“. It’s clean, easy to install and best of all it has great documentation and following.

I completed Django install in one day, which included compiling Python, cx_Oracle and mod_wsgi from source. All in all the process went fairly well but I kept wishing there was a good step by step writeup available online for getting it all setup with Oracle. There are couple of key compiler flags that make your life with python and Oracle a lot easier, but it was nowhere to be found and I learned about them by trial and error. I documented the whole process and attaching it here — hope this helps!

NOTE: This writeup assumes you have access to current Oracle Linux Support via ULN (linux.oracle.com) and your up2date was setup with ULN. It also assumes you are running 32bit Oracle Enterprise Linux or Red HAT equivalent:

[pyweb@oraemgc ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux AS release 4 (October Update 5)
Kernel \r on an \m
 
[pyweb@oraemgc ~]$ uname -a
Linux oraemgc 2.6.9-55.0.0.0.2.ELsmp #1 SMP Wed May 2 14:59:56 PDT 2007 i686 i686 i386 GNU/Linux

Upgrade python to 2.6

First up2date tk and tcl pkgs to solve “INFO: Can’t locate Tcl/Tk libs and/or headers” error while compiling python:

up2date tk
up2date tcl

Download python source code:

wget http://www.python.org/ftp/python/2.6.4/Python-2.6.4.tgz
gunzip Python-2.6.4.tgz
tar xvf Python-2.6.4.tar

Compile Python:

NOTE: see problems trying to build python 2.6 as a shared library and Shared Library Search Paths for explanation of LD_RUN_PATH.

cd Python-2.6.4
LD_RUN_PATH=/usr/local/lib; export LD_RUN_PATH
make clean
## shared is needed by mod_wsgi
./configure --enable-shared
make
make install
 
## FYI I ignored the following warning/error during make:
##
## Failed to find the necessary bits to build these modules:
## _sqlite3           _tkinter           bsddb185
## sunaudiodev
## To find the necessary bits, look in setup.py in detect_modules() for the module's name.
##
 
 
## BEFORE:
##
   [root@oraemgc Python-2.6.4]# python
   Python 2.3.4 (#1, Jul 27 2009, 07:32:09)
   [GCC 3.4.6 20060404 (Red Hat 3.4.6-11.0.1)] on linux2
   Type "help", "copyright", "credits" or "license" for more information.
   >>>
   [root@oraemgc Python-2.6.4]#
 
## AFTER UPGRADE:
##
   [root@oraemgc Python-2.6.4]# python
   Python 2.6.4 (r264:75706, Nov 24 2009, 19:31:16)
   [GCC 3.4.6 20060404 (Red Hat 3.4.6-8.0.1)] on linux2
   Type "help", "copyright", "credits" or "license" for more information.
   >>>
   [root@oraemgc Python-2.6.4]#

Install Oracle Instant client RPMs (needed for cx_Oracle)

up2date oracle-instantclient11.1-basic
up2date oracle-instantclient11.1-devel
up2date oracle-instantclient11.1-sqlplus

install cx_Oracle

Download cx_Oracle-5.0.2.tar.gz from http://cx-oracle.sourceforge.net/ (click on “Source Code only” link once on that page and it’ll redirect you to that file).

NOTE: In order to avoid runtime errors such as “python: error while loading shared libraries: libpython2.6.so.1.0: cannot open shared object file: No such file or directory” I am setting LD_RUN_PATH during compilation of cx_Oracle library.

gunzip cx_Oracle-5.0.2.tar.gz
tar xvf 10826.cx_Oracle-5.0.2.tar
cd ./cx_Oracle-5.0.2
 
## LD_RUN_PATH will hardwire the path to libclntsh.so.11.1 into cx_Oracle.so 
## at compile time similar to what I used with Python
##
export LD_RUN_PATH=/usr/lib/oracle/11.1/client/lib
rm -rf build
python setup.py build
python setup.py install

Quick test of cx_Oracle:

export ORACLE_HOME=/usr/lib/oracle/11.1/client
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH:.
export TNS_ADMIN=/u01/app/oracle/admin/scripts/mon/TNSADMIN
 
oraemgc.oracle-> sqlplus webproc/welcome@lmon
 
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 24 17:44:36 2009
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>
 
 
 
oraemgc.oracle-> python
Python 2.6.4 (r264:75706, Nov 24 2009, 17:36:09)
[GCC 3.4.6 20060404 (Red Hat 3.4.6-8.0.1)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>> import cx_Oracle
>>>
>>> connection = cx_Oracle.connect("webproc", "welcome", "lmon")
>>>
>>> cursor = connection.cursor()
>>> cursor.execute("""
... select * from dual""")
<__builtin__.OracleCursor on <cx_Oracle.Connection to webproc@lmon>>
>>> for column_1 in cursor:
...    print "Values:", column_1
...
Values: ('X',)
>>>

Install Django

Create user that will own django software:

useradd pyweb
mkdir -p /u01/app/pyweb
chown pyweb:pyweb /u01/app/pyweb
 
## [root@oraemgc cx_Oracle-5.0.2]# grep pyweb /etc/passwd
## pyweb:x:502:504::/u01/app/pyweb:/bin/bash
##
 
su - pyweb
 
vi .profile
------ add the following -------
# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi
 
 
export ORACLE_HOME=/usr/lib/oracle/11.1/client
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH:.
export TNS_ADMIN=/u01/app/oracle/admin/scripts/mon/TNSADMIN
 
 
 
## create django directory
##
cd
mkdir -p product
cd product
 
## download latest django code from their SVN
##
svn co http://code.djangoproject.com/svn/django/trunk/

Configure django with Python. The steps below are largely based on http://docs.djangoproject.com/en/dev/topics/install/#database-installation

## verify where site pkgs are located
##
cd /u01/app/pyweb/product/django-trunk
[pyweb@oraemgc django-trunk]$ python -c "from distutils.sysconfig import get_python_lib; print get_python_lib()"
/usr/local/lib/python2.6/site-packages
 
## link django with python interpreter
## and 
## create a symbolic link to the file django-trunk/django/bin/django-admin.py 
##
## as root
##
ln -s /u01/app/pyweb/product/django-trunk/django /usr/local/lib/python2.6/site-packages/django
ln -s /u01/app/pyweb/product/django-trunk/django/bin/django-admin.py /usr/local/bin
 
   ## [root@oraemgc cx_Oracle-5.0.2]# ls -l /usr/local/lib/python2.6/site-packages/
   ## total 304
   ## -rw-r--r--  1 root root    898 Nov 24 17:41 cx_Oracle-5.0.2-py2.6.egg-info
   ## -rwxr-xr-x  1 root root 297255 Nov 24 17:41 cx_Oracle.so
   ## lrwxrwxrwx  1 root root     42 Nov 24 18:07 django -> /u01/app/pyweb/product/django-trunk/django
   ## -rw-r--r--  1 root root    119 Nov 24 17:40 README
   ## [root@oraemgc cx_Oracle-5.0.2]#
   ## 
 
   ## [root@oraemgc cx_Oracle-5.0.2]# ls -l /usr/local/bin
   ## total 8772
   ## -rwxr-xr-x  1 root   root     111 Nov 24 17:36 2to3
   ## -rwxr-xr-x  1 oracle root    2854 May 13  2008 coraenv
   ## -rwxr-xr-x  1 oracle root    2417 May 13  2008 dbhome
   ## lrwxrwxrwx  1 root   root      62 Nov 24 18:09 django-admin.py -> /u01/app/pyweb/product/django-trunk/django/bin/django-admin.py
   ## -rwxr-xr-x  1 root   root      99 Nov 24 17:36 idle
   ## -rwxr-xr-x  1 oracle root    2947 May 13  2008 oraenv
   ## -rwxr-xr-x  1 root   root      84 Nov 24 17:36 pydoc
   ## -rwxr-xr-x  2 root   root 4451815 Nov 24 17:39 python
   ## -rwxr-xr-x  2 root   root 4451815 Nov 24 17:39 python2.6
   ## -rwxr-xr-x  1 root   root    1424 Nov 24 17:40 python2.6-config
   ## lrwxrwxrwx  1 root   root      16 Nov 24 17:40 python-config -> python2.6-config
   ## -rwxr-xr-x  1 root   root   18054 Nov 24 17:36 smtpd.py
   ## [root@oraemgc cx_Oracle-5.0.2]#
   ##

NOTE: Whenever you want to update your copy of the Django source code, just run the command svn update from within the django-trunk directory. When you do this, Subversion will automatically download any changes.

Install mod_wsgi
based on http://code.google.com/p/modwsgi/

Note that ‘daemon’ mode of mod_wsgi is only available if Apache 2.0 or 2.2 is being used on a UNIX platform and where the Apache run time library has been compiled with support for multithreading. Generally multithreading support is standard except for on some BSD variants.

up2date httpd
 
[root@oraemgc mod_wsgi-3.0]# /usr/sbin/httpd -version
Server version: Apache/2.0.52
Server built:   Nov 11 2009 19:32:25
[root@oraemgc mod_wsgi-3.0]#

Whatever version of Python is used, it must also have been compiled with
support for multithreading. To avoid a measure of memory bloat with your
Apache processes, Python should also have been compiled with shared library
support enabled. A number of Python binary packages for Linux systems are
not compiled with shared library support enabled. You should therefore
consider recompiling Python from source code with shared library support
enabled. If a shared library is not used, you will have problems trying
to use mod_wsgi on a server where mod_python is also being loaded.

FYI: I compiled python with “./configure –enable-shared” flag to support shared library and used LD_RUN_PATH=/usr/local/lib; export LD_RUN_PATH during compilation to avoid setting LD_LIBRARY_PATH during runtime (see “Upgrade python to 2.6″ step above)

If using a Linux system and you do not have the appropriate Apache “dev”
package installed, then “apxs” will not be able to be found when
“configure” is run. Also ensure that you have installed the correct “dev”
package for the version of Apache being used. For most Linux distributions,
the “dev” package for Apache 2 is “apache2-dev” where the corresponding
Apache package was “apache2″. Some systems however distinguish the “dev”
package based on which MPM is used by Apache. As such, it may also be
called “apache2-worker-dev” or “apache2-prefork-dev”. If using Apache 2,
do not mix things up and install “apache-dev” by mistake, which is the
“dev” package for Apache 1.3 called just “apache”.

[root@oraemgc mod_wsgi-3.0]# up2date --whatprovides /usr/sbin/apxs
httpd-devel-2.0.52-41.ent.6.0.1.i386
 
up2date httpd-devel

Get mod_wsgi-3.0.tar.gz from http://code.google.com/p/modwsgi/:

wget http://modwsgi.googlecode.com/files/mod_wsgi-3.0.tar.gz
 
gunzip mod_wsgi-3.0.tar.gz
tar xvf mod_wsgi-3.0.tar
cd mod_wsgi-3.0
 
make clean
./configure
make
make install

Apache Configuration:

[root@oraemgc mod_wsgi-3.0]# ls -l /etc/httpd/modules/mod_wsgi.so
-rwxr-xr-x  1 root root 290621 Nov 24 19:54 /etc/httpd/modules/mod_wsgi.so
 
vi /etc/httpd/conf/httpd.conf
--------- edit/add the following -----
LoadModule wsgi_module modules/mod_wsgi.so
LogLevel info
 
/etc/init.d/httpd stop
/etc/init.d/httpd start

Configure mod_wsgi for Django

NOTE: based on http://docs.djangoproject.com/en/dev/howto/deployment/modwsgi/#howto-deployment-modwsgi and http://code.google.com/p/modwsgi/wiki/IntegrationWithDjango

NOTE: my project is called “evnt” you can use any name you desire just change it below:

su - pyweb
mkdir -p www_top
cd www_top
django-admin.py startproject evnt
 
## this created evnt directory and the following files:
##
   ## [pyweb@oraemgc www_top]$ ls -lta evnt/
   ## total 20
   ## drwxrwxr-x  2 pyweb pyweb 4096 Nov 24 20:20 .
   ## drwxrwxr-x  3 pyweb pyweb 4096 Nov 24 20:20 ..
   ## -rw-r--r--  1 pyweb pyweb    0 Nov 24 20:20 __init__.py
   ## -rwxr-xr-x  1 pyweb pyweb  546 Nov 24 20:20 manage.py
   ## -rw-r--r--  1 pyweb pyweb 2818 Nov 24 20:20 settings.py
   ## -rw-r--r--  1 pyweb pyweb  538 Nov 24 20:20 urls.py
   ## [pyweb@oraemgc www_top]$
   ##

Setup custom directories to hold django/mod_wsgi configuration files

mkdir /u01/app/pyweb/www_top/evnt/apache
mkdir /u01/app/pyweb/www_top/evnt/media
 
vi /u01/app/pyweb/www_top/evnt/apache/django.wsgi
-------- add the following -----
import os, sys
sys.path.append('/u01/app/pyweb/www_top')
sys.path.append('/u01/app/pyweb/www_top/evnt')
os.environ['DJANGO_SETTINGS_MODULE'] = 'evnt.settings'
 
import django.core.handlers.wsgi
 
application = django.core.handlers.wsgi.WSGIHandler()

As root edit httpd.conf to configure your new django project with wsgi:

vi /etc/httpd/conf/httpd.conf
------------ add the following ----------
## EVNT django project
##
 
WSGIDaemonProcess oradba user=pyweb group=pyweb processes=2 threads=25 maximum-requests=10000
WSGISocketPrefix run/wsgi
 
Alias /evnt-media/ /u01/app/pyweb/www_top/evnt/media/
 
<Directory /u01/app/pyweb/www_top/evnt/media>
Order deny,allow
Allow from all
</Directory>
 
WSGIScriptAlias /evnt /u01/app/pyweb/www_top/evnt/apache/django.wsgi
 
<Directory /u01/app/pyweb/www_top/evnt/apache>
Order deny,allow
Allow from all
WSGIProcessGroup oradba
</Directory>

Setup TNS_ADMIN for httpd:

vi /etc/sysconfig/httpd
--- add the following ---
TNS_ADMIN=/u01/app/oracle/admin/scripts/mon/TNSADMIN; export TNS_ADMIN

Bounce httpd

/etc/init.d/httpd restart
 
[root@oraemgc logs]# ps -ef | grep pyweb
pyweb      939   936  0 20:40 ?        00:00:00 /usr/sbin/httpd
pyweb      940   936  0 20:40 ?        00:00:00 /usr/sbin/httpd
pyweb      941   936  0 20:40 ?        00:00:00 /usr/sbin/httpd
root      1099 17430  0 20:40 pts/4    00:00:00 grep pyweb
[root@oraemgc logs]#

Test setup by going to http://yourhostname.com/evnt which should produce the following page:

End.

What’s next? Keep configuring your project by writing code to access Oracle, here’s a link to some examples which I am currently working on: Using the Django Framework with Oracle 11g.

December 3rd, 2009 | Tags: cx_Oracle, django, mod_wsgi, python | Category: python | Leave a comment

As you probably know by now, Oracle retired perfectly fine Classic Metalink for the Flash based “My Oracle Support“. How do you like it so far? Have you been getting the dreaded errors such as “A server connection error occurred. You cannot continue Please try again later.”:

November 10th, 2009 | Tags: Metalink, My Oracle Support | Category: Operations | Comments (6)

I just finished installing Oracle Enterprise Linux (OEL) 5 update 4 (5.4) and ran into the following issue installing oracle-validated from ULN Network:

[root@hercules1 rhn]# up2date oracle-validated
 
Fetching Obsoletes list for channel: el5_i386_latest...
########################################
 
Fetching Obsoletes list for channel: el5_i386_oracle...
########################################
 
Fetching rpm headers...
########################################
 
Name                                    Version        Rel
----------------------------------------------------------
oracle-validated                        1.0.0          18.el5            i386
 
 
Testing package set / solving RPM inter-dependencies...
There was a package dependency problem. The message was:
 
Unresolvable chain of dependencies:
glibc-headers  2.5-42                    requires kernel-headers
glibc-headers-2.5-42                     requires kernel-headers >= 2.2.1
oracle-validated  1.0.0-18.el5           requires kernel-headers
 
 
The following packages were added to your selection to satisfy dependencies:
Package                                Required by
----------------------------------------------------------------------------
cpp-4.1.2-46.el5_4.1.i386               gcc-4.1.2-46.el5_4.1                    cpp
gcc-4.1.2-46.el5_4.1.i386               oracle-validated-1.0.0-18.el5           gcc
gcc-c++-4.1.2-46.el5_4.1.i386           oracle-validated-1.0.0-18.el5           gcc-c++
libgcc-4.1.2-46.el5_4.1.i386            gcc-4.1.2-46.el5_4.1                    libgcc
libstdc++-4.1.2-46.el5_4.1.i386         libstdc++-devel-4.1.2-46.el5_4.1        libstdc++
libstdc++-4.1.2-46.el5_4.1.i386         gcc-c++-4.1.2-46.el5_4.1                libstdc++
libstdc++-devel-4.1.2-46.el5_4.1.i386   oracle-validated-1.0.0-18.el5           libstdc++-devel
 
 
 
## note that if you try to manually up2date kernel-headers it will fail:
##
   ## 
   ## 
   ## [root@hercules1 rhn]# up2date kernel-headers
   ## 
   ## Fetching Obsoletes list for channel: el5_i386_latest...
   ## 
   ## Fetching Obsoletes list for channel: el5_i386_oracle...
   ## 
   ## Fetching rpm headers...
   ## ########################################
   ## 
   ## Name                                    Version        Rel
   ## ----------------------------------------------------------
   ## 
   ## The following Packages were marked to be skipped by your configuration:
   ## 
   ## Name                                    Version        Rel  Reason
   ## -------------------------------------------------------------------------------
   ## kernel                                  2.6.18         164.6.1.0.1.el5Pkg name/pattern
   ## kernel-headers                          2.6.18         164.6.1.0.1.el5Pkg name/pattern
   ## 
   ## 
   ## The following packages you requested were marked to be skipped by your configuration:
   ## kernel-headers
   ## [root@hercules1 rhn]#

Solution? Use “–force” flag calling up2date:

   ## [root@hercules1 rhn]#
   ## [root@hercules1 rhn]# up2date --force kernel-headers
   ## 
   ## Fetching Obsoletes list for channel: el5_i386_latest...
   ## 
   ## Fetching Obsoletes list for channel: el5_i386_oracle...
   ## 
   ## Name                                    Version        Rel
   ## ----------------------------------------------------------
   ## kernel-headers                          2.6.18         164.6.1.0.1.el5   i386
   ## 
   ## 
   ## Testing package set / solving RPM inter-dependencies...
   ## ########################################
   ## kernel-headers-2.6.18-164.6 ########################## Done.
   ## Preparing              ########################################### [100%]
   ## 
   ## Installing...
   ##    1:kernel-headers         ########################################### [100%]
 
 
## Now oracle-validated works
##
 
[root@hercules1 rhn]# up2date oracle-validated
 
Fetching Obsoletes list for channel: el5_i386_latest...
 
Fetching Obsoletes list for channel: el5_i386_oracle...
 
Fetching rpm headers...
########################################
 
Name                                    Version        Rel
----------------------------------------------------------
oracle-validated                        1.0.0          18.el5            i386
 
 
Testing package set / solving RPM inter-dependencies...
########################################
compat-db-4.2.52-5.1.i386.r ########################## Done.
compat-gcc-34-3.4.6-4.i386. ########################## Done.
compat-gcc-34-c++-3.4.6-4.i ########################## Done.
elfutils-libelf-devel-0.137 ########################## Done.
gcc-4.1.2-46.el5_4.1.i386.r ########################## Done.
gcc-c++-4.1.2-46.el5_4.1.i3 ########################## Done.
gdb-6.8-37.el5.i386.rpm:    ########################## Done.
glibc-devel-2.5-42.i386.rpm ########################## Done.
glibc-headers-2.5-42.i386.r ########################## Done.
libXp-1.0.0-8.1.el5.i386.rp ########################## Done.
libaio-devel-0.3.106-3.2.i3 ########################## Done.
libstdc++-devel-4.1.2-46.el ########################## Done.
oracle-validated-1.0.0-18.e ########################## Done.
sysstat-7.0.2-3.el5.i386.rp ########################## Done.
unixODBC-2.2.11-7.1.i386.rp ########################## Done.
unixODBC-devel-2.2.11-7.1.i ########################## Done.
cpp-4.1.2-46.el5_4.1.i386.r ########################## Done.
elfutils-libelf-devel-stati ########################## Done.
libgcc-4.1.2-46.el5_4.1.i38 ########################## Done.
libgomp-4.4.0-6.el5.i386.rp ########################## Done.
libstdc++-4.1.2-46.el5_4.1. ########################## Done.
Preparing              ########################################### [100%]
 
Installing...
   1:libgcc                 ########################################### [100%]
   2:libstdc++              ########################################### [100%]
   3:unixODBC               ########################################### [100%]
   4:compat-db              ########################################### [100%]
   5:libgomp                ########################################### [100%]
   6:cpp                    ########################################### [100%]
   7:sysstat                ########################################### [100%]
   8:libXp                  ########################################### [100%]
   9:gdb                    ########################################### [100%]
  10:libstdc++-devel        ########################################### [100%]
  11:glibc-headers          ########################################### [100%]
  12:glibc-devel            ########################################### [100%]
  13:unixODBC-devel         ########################################### [100%]
  14:libaio-devel           ########################################### [100%]
  15:compat-gcc-34          ########################################### [100%]
  16:gcc                    ########################################### [100%]
  17:gcc-c++                ########################################### [100%]
  18:compat-gcc-34-c++      ########################################### [100%]
  19:elfutils-libelf-devel  ########################################### [100%]
  20:oracle-validated       ########################################### [100%]
  21:elfutils-libelf-devel-s########################################### [100%]
The following packages were added to your selection to satisfy dependencies:
 
Name                                    Version        Release
--------------------------------------------------------------
compat-db                               4.2.52         5.1
compat-gcc-34                           3.4.6          4
compat-gcc-34-c++                       3.4.6          4
elfutils-libelf-devel                   0.137          3.el5
gcc                                     4.1.2          46.el5_4.1
gcc-c++                                 4.1.2          46.el5_4.1
gdb                                     6.8            37.el5
glibc-devel                             2.5            42
glibc-headers                           2.5            42
libXp                                   1.0.0          8.1.el5
libaio-devel                            0.3.106        3.2
libstdc++-devel                         4.1.2          46.el5_4.1
sysstat                                 7.0.2          3.el5
unixODBC                                2.2.11         7.1
unixODBC-devel                          2.2.11         7.1
cpp                                     4.1.2          46.el5_4.1
elfutils-libelf-devel-static            0.137          3.el5
libgcc                                  4.1.2          46.el5_4.1
libgomp                                 4.4.0          6.el5
libstdc++                               4.1.2          46.el5_4.1
 
[root@hercules1 rhn]#

HTH,
- Vitaliy

November 5th, 2009 | Tags: OEL 5.4, oracle-validated | Category: Linux | Comments (2)

Oracle has just announced a new world record TPC-C benchmark result claiming that Oracle and Sun Are Faster than IBM: Proof Now Available. What’s really interesting is that they used Solaris OS instead of Oracle EL for this benchmark, specifically: “Oracle® Database 11g running on Sun SPARC® servers with CMT technology and the Sun Solaris Operating System“.

John Fowler (executive vice-president, Systems Group, Sun Microsystems) had this to say:

No other vendor today is shipping fully-integrated flash-based hardware and software that leverages a world-class operating system – Solaris – to deliver these breakthrough world record performance results.

This is getting very interesting … Goodbye Oracle Linux?

October 13th, 2009 | Tags: solaris, SUN vs Linux | Category: Linux | Comments (1)

Wow, that’s a handful — “restore which I didn’t do“, alright then why mention it? Well, here’s why. Imagine you get an email with a title “PROJECT-NAME – QA Schema Rollback?” You quickly read through it and realize that a developer messed up a very important heavily used schema on a QA system for a project that’s about to go live. A quick analysis of the situation reveals that a delete was issued on three tables setup with “cascade constraints” clause and it wiped out data from 11 detail tables. Why would you do that is another question, which I took up with the development team but I digress …

What do you do?

First thing that came to my mind was “flashback“! Sounds good, but what do you flashback? A schema? No such thing. A table? Maybe, but what about referential integrity in 11 detail tables? That’s OK it can be solved like this — I flashback the master tables first and then the 11 detail tables, it might work … Lets check the pre-reqs — undo_management=AUTO (check), undo_retention = 900 (not good — it’s been over 15 minutes). Alright, forget about flashing back anything (mental note to myself — increase undo_retention to few hours).

What’s next?

The next thing is point in time recovery. Quick check shows that the schema is fully contained in a single tablespace — very good, we can do a tablespace level recovery instead of the whole database. And since we are on 10gR2 how about fully automated RMAN TSPITR? Sounds good, lets see what we need for this to work:

• The target instance, containing the tablespace to be recovered
• The Recovery Manager client
• The control file and (optional) recovery catalog, used for the RMAN repository records of backup activity
• Archived redo logs and backup sets from the target database, which are the source of the reconstructed tablespace.
• The auxiliary instance, an Oracle database instance used in the recovery process to perform the actual work of recovery.
• The target time, the point in time or SCN that the tablespace will be left at after TSPITR
• The recovery set, which consists of the datafiles containing the tablespaces to be recovered;
• The auxiliary set, which includes datafiles required for TSPITR of the recovery set which are not themselves part of the recovery set. The auxiliary set typically includes:
• The auxiliary destination, an optional location on disk which can be used to store any of the auxiliary set datafiles, control files and online logs of the auxiliary instance during TSPITR. Files stored here can be deleted after TSPITR is complete.

A handful list, but doable. Alright, suppose I have all this (I did) and suppose I want to go through it, how does it work? Here’s how:

To perform TSPITR of the recovery set using RMAN and an automated auxiliary instance, you carry out the preparations for TSPITR described in “Planning and Preparing for TSPITR”, and then issue the RECOVER TABLESPACE command, specifying, at a minimum, the tablespaces of the recovery set and the target time for the point-in-time recovery, and, if desired, an auxiliary destination as well. RMAN then carries out the following steps:

1. If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
2. Takes the tablespaces to be recovered offline in the target database
3. Restores a backup control file from a point in time before the target time to the auxiliary instance
4. Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance. Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE)
5. Recovers the restored datafiles in the auxiliary instance to the specified time
6. Opens the auxiliary database with the RESETLOGS option
7. Exports the dictionary metadata about objects in the recovered tablespaces to the target database
8. Shuts down the auxiliary instance
9. Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
10. Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
11. Deletes all auxiliary set files.

At that point the TSPITR process is complete. The recovery set datafiles are returned to their contents at the specified point in time, and belong to the target database.

The first thing that should jump at you here is item# 2 — “Takes the tablespaces to be recovered offline in the target database” because that’s when the real downtime will begin. Why? Because realize that while some of the schema’s data might have been lost, at least in this particular case it’s not caused a compete outage yet, we still have a website connected to it and functioning. But as soon as I take the tablespace offline it will be a true outage and I will be under the gun to deliver it ASAP. ASAP == interruptions and stress, which as you know inevitably leads to mistakes. And mistakes made while performing RMAN’s TSPITR could cause quite serious issues, much bigger then you’ve started with.

Here’s one scenario where it’s very easy to make a mistake. Lest say the developer tells you that the schema was corrupted at t1. You go through with RMAN TSPITR and realize that he was wrong and that you need to redo RMAN TSPITR using t1-15 minutes. Can you do that? ………. The answer is Not unless you are using recovery catalog, here’s why:

Assume that you run TSPITR on a tablespace, and then bring the tablespace online at time t. Backups of the tablespace created before time t are no longer usable for recovery with a current control file. You cannot run TSPITR again on this tablespace to recover it to any time less than or equal to time t, nor can you use the current control file to recover the database to any time less than or equal to t. Therefore, you must back up the recovered tablespace as soon as TSPITR is complete.

It is extremely important that you choose the right target time or SCN for your TSPITR. As noted already, once you bring a tablespace online after TSPITR, you cannot use any backup from a time earlier than the moment you brought the tablespace online. In practice, this means that you cannot make a second attempt at TSPITR if you choose the wrong target time the first time, unless you are using a recovery catalog. (If you have a recovery catalog, however, you can perform repeated TSPITRs to different target times.)

For example, assume that you are not using a recovery catalog, and you run TSPITR on a tablespace, and then bring the tablespace online at 5PM on Friday. Backups of the tablespace created before 5PM Friday are no longer usable for recovery with a current control file. You cannot run TSPITR again on this tablespace with a target time earlier than 5PM Friday, nor can you use the current control file to recover the database to any time earlier than 5PM Friday. Your only option will be point-in-time recovery of your entire database using a restored control file.

Now that’s a bit of a problem isn’t it? What this means is that you might actually have just ONE SHOOT at getting it right. You miss it and you might be done, at least with using RMAN TSPITR, you can still recover full database using backup control file from before time t.

Considering all of these nuances it quickly became apparent to me that perhaps RMAN TSPITR is not the right solution in this case. It sure sounded great on paper. What’s not to like here, just one command like this:

RECOVER TABLESPACE users, tools 
     UNTIL LOGSEQ 1300 THREAD 1
      AUXILIARY DESTINATION '/disk1/auxdest';

Solves your issue! But it’s not as simple, it’s not just one command, there’s a lot more to this and even if you are willing to take the risk there are other limitations you need to be aware of. If any of the following exists in the tablespace to be recovered, then RMAN TSPITR will not work:

• tables with VARRAY
• nested tables
• external tables
• Snapshot logs and snapshot tables
• undo or rollback segments
• objects owned by SYS

You’d know if you had any Snapshot logs or tables because DBA’s typically set those up, the same thing applies to RBS/UNDO/SYS objects. Checking the rest would be easy:

SQL> col data_type format a30
SQL> r
  1  select count(*),DATA_TYPE from dba_tab_columns where owner='SCHEMA-IN-TABLESPACE'
  2* group by DATA_TYPE
 
  COUNT(*) DATA_TYPE
---------- ------------------------------
         2 LONG RAW
        72 NUMBER
         1 CLOB
        20 DATE
       200 VARCHAR2
         2 BLOB
 
6 rows selected.
 
SQL> select count(*) from dba_nested_tables where owner='SCHEMA-IN-TABLESPACE'
  2  ;
 
  COUNT(*)
----------
         0
 
SQL> select count(*) from dba_external_tables where owner='SCHEMA-IN-TABLESPACE'
  2  ;
 
  COUNT(*)
----------
         0
 
SQL>

You also have to consider that all the while you are thinking and researching, the database is still being accessed, and it’s entirely possible that changes are being made to the schema in question. If you restore it to the time in the past all of these changes will lost, so you’d have to actually take a quick backup of the schema as it is now before you perform RMAN TSPITR just in case you’ll need anything from it …

Also realize that all of this information you have to either know my heart or sift through very quickly because a decision needs to be made as to how to solve this issue. And unless you have recently practiced RMAN TSPITR on this specific tablespace in this specific database a conservative DBA, which all DBAs should be, would never make a decision to use RMAN TSPITR. I didn’t — I used RMAN backup set to recover the database on another host, exported the schema in question and imported it as another schema into the original database so that developers could reconstruct lost rows from it. My solution, while not the fastest was the sure thing and that’s what our job is all about.

October 7th, 2009 | Tags: RMAN, RMAN TSPITR | Category: Operations, RMAN | Comments (3)

You are probably wondering what is EVNT?

EVNT is an Oracle monitoring framework I wrote and have been using since 2002. My then boss encouraged me to devote few months of my time to automating all our monitoring needs. We analyzed our requirements, designed the framework and I wrote all the code in few months. In retrospect, this was the best time investment we’ve made — it’s still serving us well after all these years and numerous upgrades — we started with 8.1.7 and now are on 10gR2. Which brings me to why I started this post — I just wrote up EVNT install notes for 10.2.0.4.

I also heavily use OEM — it’s good for lots of things and not so good for others. Ironically, I still don’t trust OEM for critical database up-time monitoring. And if you too, feel that OEM is missing in some areas I encourage you to give EVNT a shoot — DOWNLOAD: EVNT – Event Monitoring Framework for Oracle. EVNT is especially useful to those DBAs that rely heavily on sqlplus/shell scripts for database monitoring.

September 30th, 2009 | Tags: EVNT, monitoring framework | Category: Operations | Leave a comment

It took Oracle buying SUN to finally come out with something worthy this relationship — Exadata Version 2. Oracle has killed two birds with one stone capitalizing on their superior Oracle Enterprise Linux and SUN’s x64 hardware, once again proving that it’s bet is on OEL not Solaris.

The pricing model allows incremental growth using the following stages:

Basic System – $110,000
Quarter Rack – $350,000
Half Rack – $650,000
Full Rack – $1.15M

Key Internals are as follows (per Full RACK):
8 Sun Fire X4170 DB Nodes per RACK
14 Sun Fire X4275 Storage Nodes per RACK
Combo of multiple Sun Quad Data Rate (QDR) Datacenter InfiniBand Switches 36 capable of 40Gb/sec

Oracle/SUN claim that by using X4275 they were able to significantly simplify storage solution eliminating complex SAN architectures. Here’s a brief overview of the architecture – Sun Oracle Database Machine and Exadata Storage Server. And here’s the presentation Larry Ellison gave at The Sun Oracle Database Machine Announcement.

So is Exadata Version 2 a final blow to Oracle/HP relationship? Time will tell, all I can say is that we couldn’t be happier with our HP hardware running Oracle Application Servers, Oracle RAC and various middle-tier solutions on Oracle EL.

September 22nd, 2009 | Tags: Exadata Version 2, InfiniBand, Linux, solaris, SUN, X4170, X4275 | Category: Operations | Leave a comment