Oracle Fun with Predicate pushdown

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

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

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

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

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

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

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.