Oracle

Oracle Fun with Predicate pushdown

Posted by

I had some fun recently with a Oracle database choosing a poor execution plan.

The problem was with a view which had a column which was explicitly cast to a value.

For example:

create table vw_temp
as
select
cast(ID) as NUMBER(19,0) as ID,
Name varchar2(50)
from very_large_table a
join large_table b on a.ID = b.ID
where Name = ‘whatever’ ;

Oracle in this case was unable to use the ability to push predicates down and make the joins more optimized.

So the moral of the story is be careful if you are doing casts/converts or any function which will change the column in a view.

Have Fun

For more info about predicate push down have a read of this blog entry
https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
Or this short entry in the documenation
http://www.oracle.com/pls/db102/to_URL?remark=ranked&urlname=http:%2F%2Fdownload.oracle.com%2Fdocs%2Fcd%2FB19306_01%2Fserver.102%2Fb14211%2Foptimops.htm%23i55050

Oracle RAC on EC2 redux

Posted by

I was reading some RSS feeds the other day and noticed that Jeremy Schneider over at Ardent Performance Computing was working on getting Oracle RAC working on Amazon EC2.
http://www.ardentperf.com/2011/03/04/byo-oracle-rac-on-ec2/
He looks to have solved the whole Virtual IP issue by using another instance. Nice solution!

When I get a spare moment (don’t believe for a minute that the lack of posts means I am not busy) it would be good to take the scripts and get the whole Oracle RAC working in Amazon EC2 finally!

Other News:

I have had the chance to play with some columnar databases, Vertica and Ingres VectorWise and the performance is good. I used the TPC-H benchmark to test to a scale 20 (small only due to a lack of disk space). So the results are nothing like the recent Scale 100 benchmarks that Ingres VectorWise did but useful nonetheless. Sadly I can’t publish any scripts or results as the IP is owned by my current employer.

Currently I am focused on improving my skills in predictive modeling and analytics. This is using the data rather than just supporting/recovering and hand-holding the data a.k.a. being a DBA.

Listening to trance, in the zone and most definitely Having Fun!

Paul

Is EC2 useful as a database server

Posted by

4

Plenty of people have been excited by the prospect of Amazon EC2 and the ability to scale out your databases as load increases from your original configuration. I noticed Morgan Tocker and Carl Mercier are going to be presenting on this topic at the upcoming MySQL Conference

However almost immediately people are worried about the lack of persistent of data across instance terminations.
In a sense people are wanting dedicated hosting services instead of what EC2 really is.

You need to think of Amazon EC2 in a electricity generation metaphor.
Coal, Nuclear Fission and Gas provide the base load electricity which is on 24×7.
Gas and Hydro can act as peak load generation as well. So at peak periods when the requirement for more electricity is higher, electricity generators can switch on these extra resources to cope.

Amazon EC2 is the same, it is there to service peak load.
Either you are using Amazon EC2 as a base load server or you are using a dedicated hosting service to provide base load. You add additional server resources during peak periods as required.
As a dedicated hosting service EC2 is not actually the cheapest option out there. There are plenty of dedicated hosting providers who will give you and your application and database, cheaper base load capacity. That said, many people choose to run both application and database servers on EC2 as base load servers and the uptime of these instances is good.

What this means is that using EC2 as base load means you must implement additional protections for your data to provide persistence. This may be in the form of clustering technologies and replication technologies or both. So running EC2 as a base load database server adds complexity. This is why numerous companies have sprung up as a result of this complexity. They are essentially providing a method for companies to pay for someone else to deal with it.

The hidden value here is, in adopting a more thorough attitude to data persistence and redundancy, your database is more robust. So if or when your dedicated hosting provider has an outage, your architectural design is already in a position to handle it.

The danger is, you see any ongoing performance issue (a demand for addition base load) as solved by throwing hardware at it. Rather than reviewing whether the demand is justified or whether it can be reduced through tuning the application, database or architecture.

Update: Added Carl as co-presenter at the MySQL conference.

Have Fun

Paul

Oracle 11G on EC2 using silent install

Posted by

3

The buzz around the Oracle community was the upcoming release of the new Oracle database.
No code names required, everyone knew it would be 11 something, Alex Gorbachev even ran a poll on the new letter.

Oracle decided to continue down the Grid path, hence the new version was Oracle 11g.

The software is officially released to more than privileged beta testers. You can get it here.

So given I had a spare moment before I start on the MySQL 5.1 Cluster replication build. I decided to download the software and give it a whirl.
As I have mentioned in the past, once you get a hang for using response files you will never go back to the slow java based GUI installer, especially over slow links.

Make sure you read the Linux install guide, the main thing is to get the software packages, users and kernel parameter setup.

I have attached the following process I used to install Oracle 11g on EC2.

Have Fun

Paul



Get the software, accept the license on the website and then copy your
cookie.txt file to the EC2 instance.


wget --load-cookies=cookies.txt http://download.oracle.com/otn/linux/oracle11g/linux_11gR1_database.zip
--07:32:09--  http://download.oracle.com/otn/linux/oracle11g/linux_11gR1_database.zip
          => `linux_11gR1_database.zip'
Resolving download.oracle.com... 208.111.133.54, 208.111.133.53, 208.111.133.52
Connecting to download.oracle.com|208.111.133.54|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1,844,533,232 (1.7G) [application/zip]

100%[====================================================================================>] 1,844,533,232    8.03M/s    ETA 00:00

07:36:05 (7.47 MB/s) - `linux_11gR1_database.zip' saved [1844533232/1844533232]


Nice option provided by Alex Gorbachev

cat pkgs

binutils
compat-libstdc++
elfutils-libelf
elfutils-libelf-devel
glibc
glibc-common
glibc-devel
gcc
gcc-c++
libaio-devel
libaio
libgcc
libstdc++
libstdc++-devel
make
sysstat
unixODBC
unixODBC-devel

Use RPM to determine what packages are installed and versions

for pkg in `cat pkgs` ; do rpm -q $pkg --queryformat '%{NAME} %{VERSION} %{ARCH}\n' ; done

binutils 2.15.92.0.2 i386
package compat-libstdc++ is not installed
elfutils-libelf 0.97.1 i386
package elfutils-libelf-devel is not installed
glibc 2.3.4 i686
glibc-common 2.3.4 i386
glibc-devel 2.3.4 i386
gcc 3.4.6 i386
package gcc-c++ is not installed
package libaio-devel is not installed
libaio 0.3.105 i386
libgcc 3.4.6 i386
libstdc++ 3.4.6 i386
package libstdc++-devel is not installed
make 3.80 i386
package sysstat is not installed
package unixODBC is not installed
package unixODBC-devel is not installed

Create a shell script to install packages


cat install_pkgs

yum install binutils \
compat-libstdc++ \
elfutils-libelf \
elfutils-libelf-devel \
glibc \
glibc-common \
glibc-devel \
gcc \
gcc-c++ \
libaio-devel \
libaio \
libgcc \
libstdc++ \
libstdc++-devel \
make \
sysstat \
unixODBC \
unixODBC-devel

./install_pkgs

Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Parsing package install arguments
No Match for argument: compat-libstdc++
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for glibc to pack into transaction set.
glibc-2.3.4-2.36.i686.rpm 100% |=========================| 126 kB    00:00
---> Package glibc.i686 0:2.3.4-2.36 set to be updated
---> Downloading header for gcc-c++ to pack into transaction set.
gcc-c%2B%2B-3.4.6-8.i386. 100% |=========================|  36 kB    00:00
---> Package gcc-c++.i386 0:3.4.6-8 set to be updated
---> Downloading header for binutils to pack into transaction set.
binutils-2.15.92.0.2-22.i 100% |=========================|  41 kB    00:00
---> Package binutils.i386 0:2.15.92.0.2-22 set to be updated
---> Package libaio-devel.i386 0:0.3.105-2 set to be updated
---> Package unixODBC-devel.i386 0:2.2.11-1.RHEL4.1 set to be updated
---> Downloading header for gcc to pack into transaction set.
gcc-3.4.6-8.i386.rpm      100% |=========================|  43 kB    00:00
---> Package gcc.i386 0:3.4.6-8 set to be updated
---> Downloading header for glibc-devel to pack into transaction set.
glibc-devel-2.3.4-2.36.i3 100% |=========================|  94 kB    00:00
---> Package glibc-devel.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for glibc-common to pack into transaction set.
glibc-common-2.3.4-2.36.i 100% |=========================| 646 kB    00:01
---> Package glibc-common.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for libstdc++ to pack into transaction set.
libstdc%2B%2B-3.4.6-8.i38 100% |=========================|  34 kB    00:00
---> Package libstdc++.i386 0:3.4.6-8 set to be updated
---> Package sysstat.i386 0:5.0.5-15.0.1.el4 set to be updated
---> Downloading header for elfutils-libelf to pack into transaction set.
elfutils-libelf-0.97.1-4. 100% |=========================| 7.4 kB    00:00
---> Package elfutils-libelf.i386 0:0.97.1-4 set to be updated
---> Downloading header for libgcc to pack into transaction set.
libgcc-3.4.6-8.i386.rpm   100% |=========================|  34 kB    00:00
---> Package libgcc.i386 0:3.4.6-8 set to be updated
---> Downloading header for libstdc++-devel to pack into transaction set.
libstdc%2B%2B-devel-3.4.6 100% |=========================|  67 kB    00:00
---> Package libstdc++-devel.i386 0:3.4.6-8 set to be updated
---> Downloading header for elfutils-libelf-devel to pack into transaction set.
elfutils-libelf-devel-0.9 100% |=========================| 7.4 kB    00:00
---> Package elfutils-libelf-devel.i386 0:0.97.1-4 set to be updated
---> Package unixODBC.i386 0:2.2.11-1.RHEL4.1 set to be updated
--> Running transaction check
--> Processing Dependency: glibc-headers = 2.3.4-2.36 for package: glibc-devel
--> Processing Dependency: elfutils-libelf = 0.97.1-3 for package: elfutils
--> Processing Dependency: glibc = 2.3.4-2.25 for package: glibc-headers
--> Processing Dependency: cpp = 3.4.6-8 for package: gcc
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for glibc-headers to pack into transaction set.
glibc-headers-2.3.4-2.36. 100% |=========================| 127 kB    00:00
---> Package glibc-headers.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for elfutils to pack into transaction set.
elfutils-0.97.1-4.i386.rp 100% |=========================|  12 kB    00:00
---> Package elfutils.i386 0:0.97.1-4 set to be updated
---> Downloading header for cpp to pack into transaction set.
cpp-3.4.6-8.i386.rpm      100% |=========================|  36 kB    00:00
---> Package cpp.i386 0:3.4.6-8 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package                 Arch       Version          Repository        Size
=============================================================================
Installing:
elfutils-libelf-devel   i386       0.97.1-4         base               52 k
gcc-c++                 i386       3.4.6-8          base              2.4 M
libaio-devel            i386       0.3.105-2        base              9.9 k
libstdc++-devel         i386       3.4.6-8          base              8.6 M
sysstat                 i386       5.0.5-15.0.1.el4  update            104 k
unixODBC                i386       2.2.11-1.RHEL4.1  base              829 k
unixODBC-devel          i386       2.2.11-1.RHEL4.1  base              772 k
Updating:
binutils                i386       2.15.92.0.2-22   base              2.8 M
elfutils-libelf         i386       0.97.1-4         base               38 k
gcc                     i386       3.4.6-8          base              4.4 M
glibc                   i686       2.3.4-2.36       base              5.9 M
glibc-common            i386       2.3.4-2.36       base               16 M
glibc-devel             i386       2.3.4-2.36       base              1.9 M
libgcc                  i386       3.4.6-8          base               63 k
libstdc++               i386       3.4.6-8          base              282 k
Updating for dependencies:
cpp                     i386       3.4.6-8          base              1.6 M
elfutils                i386       0.97.1-4         base              136 k
glibc-headers           i386       2.3.4-2.36       base              585 k

Transaction Summary
=============================================================================
Install      7 Package(s)
Update      11 Package(s)
Remove       0 Package(s)
Total download size: 46 M
Is this ok [y/N]: y
Downloading Packages:
(1/18): glibc-2.3.4-2.36. 100% |=========================| 5.9 MB    00:16
(2/18): gcc-c++-3.4.6-8.i 100% |=========================| 2.4 MB    00:06
(3/18): binutils-2.15.92. 100% |=========================| 2.8 MB    00:07
(4/18): libaio-devel-0.3. 100% |=========================| 9.9 kB    00:00
(5/18): unixODBC-devel-2. 100% |=========================| 772 kB    00:02
(6/18): gcc-3.4.6-8.i386. 100% |=========================| 4.4 MB    00:12
(7/18): glibc-devel-2.3.4 100% |=========================| 1.9 MB    00:05
(8/18): glibc-headers-2.3 100% |=========================| 585 kB    00:01
(9/18): elfutils-0.97.1-4 100% |=========================| 136 kB    00:00
(10/18): glibc-common-2.3 100% |=========================|  16 MB    00:44
(11/18): libstdc++-3.4.6- 100% |=========================| 282 kB    00:00
(12/18): sysstat-5.0.5-15 100% |=========================| 104 kB    00:00
(13/18): elfutils-libelf- 100% |=========================|  38 kB    00:00
(14/18): libgcc-3.4.6-8.i 100% |=========================|  63 kB    00:00
(15/18): libstdc++-devel- 100% |=========================| 8.6 MB    00:23
(16/18): elfutils-libelf- 100% |=========================|  52 kB    00:00
(17/18): unixODBC-2.2.11- 100% |=========================| 829 kB    00:02
(18/18): cpp-3.4.6-8.i386 100% |=========================| 1.6 MB    00:04
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
 Updating  : libgcc                       ####################### [ 1/29]
 Updating  : glibc-common                 ####################### [ 2/29]
 Updating  : glibc                        ####################### [ 3/29]
Stopping sshd:[  OK  ]
Starting sshd:[  OK  ]
 Updating  : binutils                     ####################### [ 4/29]
 Updating  : elfutils-libelf              ####################### [ 5/29]
 Updating  : glibc-headers                ####################### [ 6/29]
 Updating  : glibc-devel                  ####################### [ 7/29]
 Updating  : libstdc++                    ####################### [ 8/29]
 Installing: libstdc++-devel              ####################### [ 9/29]
 Updating  : elfutils                     ####################### [10/29]
 Installing: unixODBC                     ####################### [11/29]
 Updating  : cpp                          ####################### [12/29]
 Updating  : gcc                          ####################### [13/29]
 Installing: gcc-c++                      ####################### [14/29]
 Installing: libaio-devel                 ####################### [15/29]
 Installing: unixODBC-devel               ####################### [16/29]
 Installing: sysstat                      ####################### [17/29]
 Installing: elfutils-libelf-devel        ####################### [18/29]
 Cleanup   : glibc                        ####################### [19/29]
 Cleanup   : binutils                     ####################### [20/29]
 Cleanup   : gcc                          ####################### [21/29]
 Cleanup   : glibc-devel                  ####################### [22/29]
 Cleanup   : glibc-headers                ####################### [23/29]
 Cleanup   : elfutils                     ####################### [24/29]
 Cleanup   : glibc-common                 ####################### [25/29]
 Cleanup   : libstdc++                    ####################### [26/29]
 Cleanup   : elfutils-libelf              ####################### [27/29]
 Cleanup   : libgcc                       ####################### [28/29]
 Cleanup   : cpp                          ####################### [29/29]

Installed: elfutils-libelf-devel.i386 0:0.97.1-4 gcc-c++.i386 0:3.4.6-8 libaio-devel.i386 0:0.3.105-2 libstdc++-devel.i386 0:3.4.6-8 sysstat.i386 0:5.0.5-15.0.1.el4 unixODBC.i386 0:2.2.11-1.RHEL4.1 unixODBC-devel.i386 0:2.2.11-1.RHEL4.1
Updated: binutils.i386 0:2.15.92.0.2-22 elfutils-libelf.i386 0:0.97.1-4 gcc.i386 0:3.4.6-8 glibc.i686 0:2.3.4-2.36 glibc-common.i386 0:2.3.4-2.36 glibc-devel.i386 0:2.3.4-2.36 libgcc.i386 0:3.4.6-8 libstdc++.i386 0:3.4.6-8
Dependency Updated: cpp.i386 0:3.4.6-8 elfutils.i386 0:0.97.1-4 glibc-headers.i386 0:2.3.4-2.36
Complete!

Checking compat-libstdc++ issue

yum search compat-libstdc

Searching Packages:
Setting up repositories
Reading repository metadata in from local files


compat-libstdc++-33.i386                 3.2.3-47.3             base
Matched from:
compat-libstdc++-33


compat-libstdc++-296.i386                2.96-132.7.2           base
Matched from:
compat-libstdc++-296
The compat-libstdc++-296 package contains 2.96-RH compatibility standard
C++ libraries.


compat-libstdc++-33.i386                 3.2.3-47.3             installed
Matched from:
compat-libstdc++-33


compat-libstdc++-296.i386                2.96-132.7.2           installed
Matched from:
compat-libstdc++-296
The compat-libstdc++-296 package contains 2.96-RH compatibility standard
C++ libraries.


Checking what environment variables are set as the Oracle unix user:

env|grep ORA

ORACLE_SID=TRAIN
ORACLE_BASE=/home/oracle
ORACLE_HOME=/home/oracle/product/10.2.0/db_1

Make the new software ORACLE_HOME:

mkdir -p /home/oracle/product/11.1.0/db_1

Check kernel parameters:

cat /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1


# Oracle Specific

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Unzip the downloaded file:

unzip linux_11gR1_database.zip

...


Copy and edit a response file:

cp directory_unzipped/database/install/response/ 11G_SE.rsp

diff se.rsp 11G_SE.rsp

36c36
 FROM_LOCATION="/mnt/downloads/database/stage/products.xml"
44c44


> ORACLE_BASE="/home/oracle"
52c52


> ORACLE_HOME="/home/oracle/product/11.1.0/db_1"
60c60


> ORACLE_HOME_NAME="Ora11gR1HOME1"
92c92
 SHOW_SPLASH_SCREEN=FALSE
409c409


> s_globalDBName="TRAIN"
416c416


> s_dbSid="TRAIN"
563,564c563,564



> s_superAdminSamePasswd="xxxxxx"
> s_superAdminSamePasswdAgain="xxxxxx"
709c709


> n_configurationOption=3

Unset ORACLE_HOME:

[oracle@domU-12-31-36-00-25-E2 response]$ export ORACLE_HOME=
[oracle@domU-12-31-36-00-25-E2 ~]$ export LD_LIBRARY_PATH=
[oracle@domU-12-31-36-00-25-E2 response]$ env|grep ORA
ORACLE_SID=TRAIN
ORACLE_BASE=/home/oracle
ORACLE_HOME=

Run the install using -silent -responseFile options:

./runInstaller -silent -responseFile /mnt/downloads/database/install/response/11G_SE.rsp

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 6433 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 895 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2007-08-11_08-21-09AM. Please wait ...[oracle@domU-12-31-36-00-25-E2 database]$ Oracle Universal Installer, Version 11.1.0.6.0 Production
Copyright (C) 1999, 2007, Oracle. All rights reserved.

You can find the log of this install session at:
/home/oracle/oraInventory/logs/installActions2007-08-11_08-21-09AM.log
.................................................................................................... 100% Done.


Loading Product Information
.............................................................................................................. 100% Done.


Starting execution of Prerequisites...
Total No of checks: 14

Performing check for CertifiedVersions
Checking operating system requirements ...
Expected result: One of enterprise-4,enterprise-5,redhat-4,redhat-5,SuSE-10,asianux-2,asianux-3
Actual Result: redhat-4
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for Packages
Checking operating system package requirements ...
Checking for make-3.80; found make-1:3.80-6.EL4-i386.   Passed
Checking for binutils-2.15.92.0.2; found binutils-2.15.92.0.2-22-i386.  Passed
Checking for gcc-3.4.5; found gcc-3.4.6-8-i386. Passed
Checking for libaio-0.3.105; found libaio-0.3.105-2-i386.       Passed
Checking for libaio-devel-0.3.105; found libaio-devel-0.3.105-2-i386.   Passed
Checking for libstdc++-3.4.5; found libstdc++-3.4.6-8-i386.     Passed
Checking for elfutils-libelf-devel-0.97; found elfutils-libelf-devel-0.97.1-4-i386.     Passed
Checking for sysstat-5.0.5; found sysstat-5.0.5-15.0.1.el4-i386.        Passed
Checking for libgcc-3.4.5; found libgcc-3.4.6-8-i386.   Passed
Checking for libstdc++-devel-3.4.5; found libstdc++-devel-3.4.6-8-i386. Passed
Checking for unixODBC-2.2.11; found unixODBC-2.2.11-1.RHEL4.1-i386.     Passed
Checking for unixODBC-devel-2.2.11; found unixODBC-devel-2.2.11-1.RHEL4.1-i386. Passed
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for Kernel
Checking kernel parameters
Checking for semmsl=250; found semmsl=250.      Passed
Checking for semmns=32000; found semmns=32000.  Passed
Checking for semopm=100; found semopm=100.      Passed
Checking for semmni=128; found semmni=128.      Passed
Checking for shmmax=536870912; found shmmax=536870912.  Passed
Checking for shmmni=4096; found shmmni=4096.    Passed
Checking for shmall=2097152; found shmall=2097152.      Passed
Checking for file-max=65536; found file-max=65536.      Passed
Checking for VERSION=2.6.9; found VERSION=2.6.16-xenU.  Passed
Checking for ip_local_port_range=1024 - 65000; found ip_local_port_range=1024 - 65000.  Passed
Checking for rmem_default=4194304; rmem_default=1048576.        Failed <<<< rmem_max="4194304;" rmem_max="1048576." wmem_default="262144;" wmem_default="262144." wmem_max="262144;" wmem_max="262144." atleast="2.3.4-2.19" passed 
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The kernel parameters do not meet the minimum requirements (see above).
Recommendation: Perform operating system specific instructions to update the kernel parameters.

========================================================
Performing check for GLIBC
Checking Recommended glibc version
Expected result: ATLEAST=2.3.4-2.19
Actual Result: 2.3.4-2.36
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for TotalMemory
Checking physical memory requirements ...
Expected result: 922MB
Actual Result: 1700MB
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for SwapSpace
Checking available swap space requirements ...
Expected result: 2550MB
Actual Result: 895MB
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The system does not have the required swap space.
Recommendation: Make more swap space available to perform the install.

========================================================
Performing check for DetectIfDHCPAssignedIP
Checking Network Configuration requirements ...
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The install has detected that the primary IP address of the system is DHCP-assigned.
Recommendation: Oracle supports installations on systems with DHCP-assigned public IP addresses.  However, the primary network interface on the system should be configured with a static IP address in order for the Oracle Software to function properly.  See the Installation Guide for more details on installing the software on systems configured with DHCP.

========================================================
Performing check for CheckPathForOtherOracleHomes
Checking PATH environment variable...
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The installer has detected that the PATH environment variable includes other ORACLE_HOME locations in it.
Recommendation: Restart the installer after removing other ORACLE_HOME locations from PATH environment variable.

========================================================
Performing check for CheckTempDiskSpace
Checking for sufficient diskspace in TEMP location...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for CheckLdLibraryPath
Checking LD_LIBRARY_PATH environment variable...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for OracleBase
Validating ORACLE_BASE location (if set) ...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for OracleHomeSpace
Checking Oracle Home path for spaces...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for DetectAnyInvalidASMHome
Checking for proper system clean-up....
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for CompatibilityChecks_DB
Checking for Oracle Home incompatibilities ....
Actual Result: NEW_HOME
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
PrereqChecks complete


Analyzing dependencies
.................................................................................................................. 100% Done.


-----------------------------------------------------------------------------
Summary
Global Settings
    Source: /mnt/downloads/database/stage/products.xml
    Oracle Base: /home/oracle
    Oracle Home: /home/oracle/product/11.1.0/db_1 (Ora11gR1HOME1)
    Installation Type: Standard Edition
Product Languages
   English
Space Requirements
   / Required 3.38GB (includes 224MB temporary) : Available 6.14GB
New Installations (116 products)
   Oracle Database 11g 11.1.0.6.0
   Enterprise Edition Options 11.1.0.6.0
   Oracle Database 11g 11.1.0.6.0
   Oracle Text 11.1.0.6.0
   Oracle Net Services 11.1.0.6.0
   Oracle Enterprise Manager Console DB 11.1.0.5.0
   Oracle Net Listener 11.1.0.6.0
   HAS Files for DB 11.1.0.6.0
   Oracle Call Interface (OCI) 11.1.0.6.0
   Enterprise Manager Agent 10.2.0.3.1
   Oracle Programmer 11.1.0.6.0
   Oracle Database Gateway for ODBC 11.1.0.6.0
   Oracle JVM 11.1.0.6.0
   Database Configuration and Upgrade Assistants 11.1.0.6.0
   Oracle XML Development Kit 11.1.0.6.0
   Generic Connectivity Common Files 11.1.0.6.0
   Oracle Multimedia 11.1.0.6.0
   Oracle Multimedia Locator 11.1.0.6.0
   Oracle Internet Directory Client 11.1.0.6.0
   Oracle Database Utilities 11.1.0.6.0
   Secure Socket Layer 11.1.0.6.0
   PL/SQL 11.1.0.6.0
   Oracle Recovery Manager 11.1.0.6.0
   Oracle Net 11.1.0.6.0
   Assistant Common Files 11.1.0.6.0
   Installation Common Files 11.1.0.6.0
   Enterprise Manager plugin Common Files 11.1.0.5.0
   Oracle LDAP administration 11.1.0.6.0
   SQL*Plus 11.1.0.6.0
   HAS Common Files 11.1.0.6.0
   Oracle Help for the  Web 2.0.14.0.0
   Oracle UIX 2.2.20.0.0
   Precompiler Common Files 11.1.0.6.0
   Oracle Clusterware RDBMS Files 11.1.0.6.0
   Cluster Verification Utility Common Files 11.1.0.6.0
   Oracle Wallet Manager 11.1.0.6.0
   Oracle Security Developer Tools 11.1.0.6.0
   XML Parser for Java 11.1.0.6.0
   Enterprise Manager Minimal Integration 11.1.0.6.0
   Oracle Database User Interface 2.2.13.0.0
   SQL*Plus Files for Instant Client 11.1.0.6.0
   Oracle ODBC Driver 11.1.0.6.0
   Required Support Files 11.1.0.6.0
   Database SQL Scripts 11.1.0.6.0
   OLAP SQL Scripts 11.1.0.6.0
   PL/SQL Embedded Gateway 11.1.0.6.0
   Oracle Globalization Support 11.1.0.6.0
   Character Set Migration Utility 11.1.0.6.0
   Oracle Locale Builder 11.1.0.6.0
   Secure Socket Layer 11.1.0.6.0
   Oracle Java Client 11.1.0.6.0
   Oracle JDBC/THIN Interfaces 11.1.0.6.0
   Oracle Multimedia Client Option 11.1.0.6.0
   Oracle Universal Connection Pool 11.1.0.6.0
   Oracle Notification Service 11.1.0.5.0
   Oracle Code Editor 1.2.1.0.0I
   Oracle Ultra Search Server Rdbms 11.1.0.6.0
   Oracle Help For Java 4.2.9.0.0
   Oracle Containers for Java 11.1.0.6.0
   JAccelerator (COMPANION) 11.1.0.6.0
   Database Workspace Manager 11.1.0.6.0
   SQLJ Runtime 11.1.0.6.0
   Oracle Core Required Support Files 11.1.0.6.0
   Platform Required Support Files 11.1.0.6.0
   Oracle Ice Browser 5.2.3.6.0
   Oracle Application Express 11.1.0.6.0
   Oracle SQL Developer 11.1.0.6.0
   Oracle JDBC Server Support Package 11.1.0.6.0
   regexp 2.1.9.0.0
   Enterprise Manager Common Files 10.2.0.3.1
   Installation Plugin Files 11.1.0.6.0
   Oracle JDBC/OCI Instant Client 11.1.0.6.0
   XML Parser for Oracle JVM 11.1.0.6.0
   Oracle XML Query 11.1.0.6.0
   Oracle Starter Database 11.1.0.6.0
   Sample Schema Data 11.1.0.6.0
   Precompiler Required Support Files 11.1.0.6.0
   Parser Generator Required Support Files 11.1.0.6.0
   Oracle Multimedia Locator RDBMS Files 11.1.0.6.0
   Oracle Globalization Support 11.1.0.6.0
   Oracle Multimedia Annotator 11.1.0.6.0
   Oracle Multimedia Java Advanced Imaging 11.1.0.6.0
   Oracle Database 11g Multimedia Files 11.1.0.6.0
   Agent Required Support Files 10.2.0.3.1
   Oracle 11g Warehouse Builder Server 11.1.0.6.0
   Oracle Ultra Search Server 11.1.0.6.0
   Oracle Ultra Search Middle-Tier 11.1.0.6.0
   Oracle Ultra Search Common Files 11.1.0.6.0
   Perl Interpreter 5.8.3.0.4
   RDBMS Required Support Files 11.1.0.6.0
   Oracle Display Fonts 9.0.2.0.0
   RDBMS Required Support Files for Instant Client 11.1.0.6.0
   Enterprise Manager Agent Core Files 10.2.0.3.1
   Enterprise Manager Common Core Files 10.2.0.3.1
   Enterprise Manager Grid Control Core Files 10.2.0.3.1
   Enterprise Manager Database Plugin -- Agent Support 11.1.0.5.0
   Enterprise Manager Database Plugin -- Management Service Support 11.1.0.5.0
   Enterprise Manager Repository Core Files 10.2.0.3.1
   Enterprise Manager Database Plugin -- Repository Support 11.1.0.5.0
   Provisioning Advisor Framework 10.2.0.3.1
   XDK Required Support Files 11.1.0.6.0
   Oracle RAC Required Support Files-HAS 11.1.0.6.0
   SQL*Plus Required Support Files 11.1.0.6.0
   Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
   Oracle Extended Windowing Toolkit 3.4.47.0.0
   Bali Share 1.1.18.0.0
   Buildtools Common Files 11.1.0.6.0
   Oracle Net Required Support Files 11.1.0.6.0
   SSL Required Support Files for InstantClient 11.1.0.6.0
   LDAP Required Support Files 11.1.0.6.0
   Oracle ODBC Driverfor Instant Client 11.1.0.6.0
   Oracle Configuration Manager 10.2.6.0.0
   Oracle Universal Installer 11.1.0.6.0
   Oracle One-Off Patch Installer 11.1.0.6.0
   Installer SDK Component 11.1.0.6.0
   Sun JDK 1.5.0.1.1
-----------------------------------------------------------------------------


Installation in progress (Sat Aug 11 08:21:39 EDT 2007)
...............................................................   7% Done.
...............................................................  14% Done.
...............................................................  21% Done.
...............................................................  29% Done.
...............................................................  36% Done.
...............................................................  43% Done.
...............................................................  50% Done.
...............................................................  58% Done.
...............................................................  65% Done.
...............................................................  72% Done.
...............................................................  80% Done.
........................                                         82% Done.
Install successful

Linking in progress (Sat Aug 11 08:27:00 EDT 2007)
.                                                                83% Done.
Link successful

Setup in progress (Sat Aug 11 08:29:03 EDT 2007)
..................................                              100% Done.
Setup successful

End of install phases.(Sat Aug 11 08:29:16 EDT 2007)
WARNING:
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
/home/oracle/product/11.1.0/db_1/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts

The installation of Oracle Database 11g was successful.
Please check '/home/oracle/oraInventory/logs/silentInstall2007-08-11_08-21-09AM.log' for more details.

cd /home/oracle/product/11.1.0/db_1
./root.sh



Oracle RAC on NFS: Install clusterware

Posted by

Continuing on with building an Oracle RAC cluster on NFS using Amazon EC2, following this build guide.

The setup will be two Oracle RAC nodes and one NFS node. This means that the NFS node is a Single Point Of Failure (SPOF) however this is not meant to be a production system rather a system to provide a environment to perform testing and learning.

I looked at various build guides for enabling High Availability NFS, essentially multiple nodes providing NFS using DRBD, however this requires a block device again. There is a new product released called ElasticDrive which provides a NBD driver for S3. I will be going back and using this product to see if I can get both Openfiler and OCFS2 working with it later.

A bit later…

I got to the point of installing the clusterware and it yet another EC2 related snag!
The lack of a 2nd ethernet device and the inability to setup a Virtual IP address (vip). Whilst I was able follow this post to fake another ethernet device by copying /etc/sysconfig/network-scripts/if-cfg-eth0 to if-cfg-eth0:1, the inability to setup up an virtual IP is going to kill this.

If you have been following this long list of posts on trying to get Oracle RAC working on EC2 this is pretty frustrating to say the least.
I thought about this for a while and did some reading of the EC2 forums. The next attempt I am going to run Qemu inside the Amazon VM (Virtual Machine). So a VM within a VM.
Qemu can convert VMware images to Qemu images so I can go back to using the good Oracle RAC on VMware install guides published by Howard Rogers at the Dizwell site.

Once I have the Oracle RAC install built under VMware I can convert that to run in EC2.

The bonus if this works is

  1. I can run multiple mini VMs inside one EC2 (Xen) VM.
  2. When Amazon get direct support for VMware happening I can reuse those VMware images directly.
  3. If I want to setup virtualization on another hosting services I am already halfway there.
  4. I can try pointing these Qemu Oracle RAC VMs at external shared disk or NFS and possibly run up a 4 node RAC cluster on one or two EC2 VMs

So that is where I am at. The work done so far with NFS is not lost as I mentioned. I have demostrated that you can provide more storage as required any application which requires more than the normal 150G /mnt mount point provided by EC2.

More later

Have Fun

Paul

Oracle RAC on NFS: Configure NFS server

Posted by

As I mentioned in Openfiler posts, until I can expose a block device within the Xen VM, using ASM to manage the files won’t be happening.

So rather than spin my wheels I have been setting up a NFS server to share the 147 Gig mountpoint /mnt as the shared disk.

Essentially this is the first part of the Oracle RAC on NFS guide. When I wanted a more indepth guide to setting up NFS Server and NFS clients I used this NFS HOWTO.

So I fired up my trusty CentOS 4 base install, got the necessary packages installed, made a hack to get a service running without the sunrpc module and bingo, the NFS server is ready for the next stage of the Oracle RAC build.

Helpful hints:

  1. Install nmap and nfs-utils: yum install nmap nfs-utils
  2. Add all the hostnames to /etc/hosts as it makes it easy to have all the config files using those names rather than hardcoded IP addresses.
  3. To get around the nasty missing sunrpc module when you start NFS use this forum post essentially comment out the exit 1 in this line /sbin/modprobe sunrpc #|| exit 1 in the file /etc/inint.d/rpcidmapd
  4. Add this line rpc_pipefs /var/lib/nfs/rpc_pipefs rpc_pipefs defaults 0 0 to /etc/fstab on the NFS server.
  5. Always run exportfs -rav so you can get a verbose output for the directories which are going to be shared via NFS
  6. nmap `hostname` is your friend for determining which ports to open from within ec2 security groups.
  7. rpcinfo -p hostname will tell you whether everything is setup and ready to go.

The other benefit of course like the Openfiler, is that I now can spawn a NFS server node to provide extra storage for Oracle RAC and whatever else needs it, for example a shared backup location for temporary Oracle RMAN backups for practicing building Standby databases using RMAN Duplicate command.

Have Fun

Paul

OCFS2 on Xen on EC2

Posted by

Unfortunately OCFS2 requires access to block devices to enabling clustering.

This is something that Openfiler via iscsi was looking like being able to do, unfortunately I think it requires the ability to boot the Xen image with block devices exported into the virtual machine.
http://wiki.rpath.com/wiki/Xen_DomU_Guide

I have been trying to get Openfiler working without success see these articles.
http://blog.dbadojo.com/2007/07/openfiler-fun.html
http://blog.dbadojo.com/2007/07/openfiler-fun-sequel.html
http://blog.dbadojo.com/2007/07/openfiler-on-ec2-much-more-work-and.html

If anyone has any ideas I would love to here them as comments on the blog.

Enormaly are working on a s3 block device which might work.

Given the MySQL 5.1 NDB cluster took all of 15 minutes to setup, and has the ability with enough data nodes to withstand the loss of any one machine it is looking more favourable at the moment.

If you are talking OCFS2 you are looking at Oracle RAC probably on ASM. Given the speed of the interconnect is ultra-important for the shared database cache the current bandwidth would be interesting. Testing and sandbox yes, production no way.
The other issue with OCFS2 is that the box exposing or providing the block device has to be redundant as well otherwise storage becomes a single point of failure.

Have Fun

Paul

My background is as a Database Specialist/DBA, I support Oracle, SQLserver and MySQL
with my mates at Pythian.

Oracle OCFS2 clusterware install on Amazon EC2

Posted by

Like I mentioned in my previous posts. I am using Jeff Hunter’s Oracle RAC build guide to get an Oracle RAC install on Amazon EC2.

I have been testing various steps which are independent on the other steps in the guide.

So last night I played around with getting Oracle OCFS2 (Oracle Cluster File system) installed onto a CentOS 4 EC2 image. This is step 16 in the build guide.

Downloading and/or reading the User Guide and reviewing the FAQ are also a good option.

Downloaded the appropriate RPMs from Oracle’s website and they installed fine.

I am time constrained, but I wanted to attempt the console (using X windows) and also the o2cb_ctl to create the configuration required.
The Oracle documentation for OCFS2 has some man pages for the various tools which can also been installed.

Note: Amazon EC2 is running Xen. uname -a was saying it was SMP, however only the single processor RPM (EL) worked, which is fair enough, given that is what Amazon specifies that i.e. a single virtual 1.7 Ghz CPU.

I have attached the screen dump of what I did.

I have made an image of Openfiler and got that running. So the next step is to fire up that, get OCFS2 running and seeing the shared disk provided by that.
Stay tuned…

Have Fun

Paul


[root@domU-12-31-36-00-30-A2 mnt]# rpm -Uvh ocfs2-2.6.9-42.EL-1.2.4-2.i686.rpm \
> ocfs2-tools-1.2.4-1.i386.rpm
Preparing...                ########################################### [100%]
  1:ocfs2-tools            ########################################### [ 50%]
  2:ocfs2-2.6.9-42.EL      ########################################### [100%]


[root@domU-12-31-36-00-30-A2 mnt]# /etc/init.d/o2cb configure
Configuring the O2CB driver.

This will configure the on-boot properties of the O2CB driver.
The following questions will determine whether the driver is loaded on
boot.  The current values will be shown in brackets ('[]').  Hitting
 without typing an answer will keep that current value.  Ctrl-C
will abort.

Load O2CB driver on boot (y/n) [n]: y
Cluster to start on boot (Enter "none" to clear) [ocfs2]:
Specify heartbeat dead threshold (>=7) [7]: 61
Specify network idle timeout in ms (>=5000) [10000]:
Specify network keepalive delay in ms (>=1000) [5000]:
Specify network reconnect delay in ms (>=2000) [2000]:
Writing O2CB configuration: OK
Loading module "configfs": OK
Mounting configfs filesystem at /sys/kernel/config: OK
Loading module "ocfs2_nodemanager": OK
Loading module "ocfs2_dlm": OK
Loading module "ocfs2_dlmfs": OK
Creating directory '/dlm': OK
Mounting ocfs2_dlmfs filesystem at /dlm: OK
Checking O2CB cluster configuration : Failed
[root@domU-12-31-36-00-30-A2 mnt]# ps -ef|grep ocds
root      2922  2732  0 08:20 ttyp1    00:00:00 grep ocds
[root@domU-12-31-36-00-30-A2 mnt]# ps -ef|grep ocfs
root      2924  2732  0 08:20 ttyp1    00:00:00 grep ocfs
[root@domU-12-31-36-00-30-A2 mnt]# uptime
08:20:43 up 27 min,  2 users,  load average: 0.00, 0.00, 0.00
[root@domU-12-31-36-00-30-A2 mnt]# /etc/init.d/o2cb status
Module "configfs": Loaded
Filesystem "configfs": Mounted
Module "ocfs2_nodemanager": Loaded
Module "ocfs2_dlm": Loaded
Module "ocfs2_dlmfs": Loaded
Filesystem "ocfs2_dlmfs": Mounted
Checking O2CB cluster ocfs2: Offline

Getting Oracle installed on EC2 using CentOS4

Posted by

Like I mentioned in my first post. My aim is to get an Oracle RAC cluster running on Amazon EC2.

I hope to use this setup as a training ground and see the process of installing and getting Oracle RAC running as a specific to EC2 it will useful in a wider sense.

I choose to go with CentOS 4.4 which is basically Red Hat Enterprise minus some bits. It is not certified by Oracle.

Luckily someone had already done the hard yards on getting an CentOS image created as a public image file.
So I copied my local cookies file and used wget to download the Oracle 10.2G install from Oracle.

The bandwidth between Amazon and Oracle made this a quick download and reasonably cheap.

I have gone off using the GUI installer over remote links, so I am reasonably comfortable with the silent installer using a response file.
eg. ./runInstaller -silent -responseFile my_ee.rsp

Basically followed the Oracle 10G Release 2 install guide made sure the prequisite linux packages were installed, copied and edited the EE.rsp (Enterprise Edition) response file and it was done.

Making a Oracle 10G EE install allowed me to play around with physical and logical standbys to my heart’s content. At the moment I still have to manually edit the tnsnames.ora and listener.ora files to get the new hostname (changes for each instance).

In the future I will rebuild the image with a build script so that it does this automatically.

Have Fun

Paul

Oracle RAC on Amazon EC2

Posted by

2

I have looked at Jeff Hunter’s complete install guide for Oracle RAC on home PCs in the past.
Whilst I could go and spend the money I thought I would give Amazon EC2 a whirl given I signed up to the beta. I had signed up back in 2006, however only found out at the start of 2007 that you needed to sign up for Amazon s3 (storage). As soon as I did that I was accepted to the beta.

Given each Amazon EC2 image is running virtual image based on Xen it should be fun.

Stay tuned for the install fun.

Have Fun

Paul