Martins Blog

Trying to explain complex things in simple terms

Archive for February, 2010

Watch those environment variables

Posted by Martin Bach on February 26, 2010

One of my colleagues was about to install another ORACLE_HOME to our 3 node production cluster, with limited time available to him, as always on Saturdays. To make matters worse, OUI wouldn’t play ball and complain that the ORACLE_HOME location selected isn’t sharable. Pardon me? None of the file systems on the cluster are actually shared file systems and neither did we see this problem before. After selecting which nodes to install the software to, OUI opened a window with this message (taken from the logfile)

The datafile storage location for Oracle Real Application Clusters should be on a shared file system Read the rest of this entry »

Posted in Linux, Oracle, RAC | Tagged: , , | 2 Comments »

RAC service weirdness

Posted by Martin Bach on February 22, 2010

I ran into a problem starting one out of 14 services registered to my database this weekend. All of it happened after a restart of the servers following a redhat upgrade from 5.3 to 5.4. One of the servers had a file system corruption which required some extensive fsck’ing so only 2 out of 3 nodes were started-this implies that some of the services started on the available node rather than the preferred one.

Not a biggie in this situation, after all we are interested in restoring service to the users. After a couple of hours the first node eventually finished the file system check and came up. CRS automatically started the instance and all looked good. Since we were still in the downtime window I decided against checking each of the 14 services to see if they run on the correct node but rather decided to stop them all and restart them-the intention was to have them all restart on the correct node. Read the rest of this entry »

Posted in Linux, RAC | Tagged: , , , | 1 Comment »

It is indeed possible to install Oracle 11.2 on OpenSolaris

Posted by Martin Bach on February 16, 2010

After I set up OpenSolaris 2009.06 (see my previous posts) on my openSuSE 11.2 dom0 I was keen to set up Oracle 11.2 single instance on ZFS. ZFS is a new-ish filesystem and successor to ufs no Solaris 10. I like it a lot for simplicty and ease of use. Piet de Visser should love it too :)

But before I could start working on this I needed to do some prep work.

Storage preparation on the dom0

Very simple-I created 2 more zero padded files in my domU base directory and added those to the domU configuration file, as in:

disk = [
 "file:/m/xen/osol/system,xvda,w",
 "file:/m/xen/osol/oracle_bin,xvdb,w",
 "file:/m/xen/osol/oracle_data,xvdc,w",
]
vif = [ "mac=00:16:3e:1b:e8:18,bridge=br1,script=vif-bridge" ]

Be sure to add a MAC address to your configuration file or otherwise Solaris will cry out loud the next restart claiming the new storage pool has been used with a different system. That’s not a problem (can be fixed with zpool import -f) but it’s not pretty either.

Addition of new storage pool

This is really simple! First of all you need to find out which of the disks presented to the system are the new ones. I only used 1 disk for the rpool (default storage pool), so I tried to identify this one first:

root@opensolaris:~# zpool status rpool
 pool: rpool
 state: ONLINE
 scrub: none requested
config:

 NAME        STATE     READ WRITE CKSUM
 rpool       ONLINE       0     0     0
 c7t0d0s0    ONLINE       0     0     0

errors: No known data errors

Then I identified the disks on the system:

root@opensolaris:~# format
Searching for disks...done

AVAILABLE DISK SELECTIONS:
 0. c7t0d0 <DEFAULT cyl 4095 alt 0 hd 128 sec 32>
 /xpvd/xdf@51712
 1. c7t1d0 <Unknown-Unknown-0001-10.00GB>
 /xpvd/xdf@51728
 2. c7t2d0 <Unknown-Unknown-0001-10.00GB>
 /xpvd/xdf@51744
Specify disk (enter its number): ^C
root@opensolaris:~#

So, not really surprisingly, c7t1d0 and c7t2d0 were the new disks. I created a new storage pool “oraclepool” with these 2 disks (not recommended for production!)

root@opensolaris:~# zpool create oraclepool c7t1d0 c7t2d0

root@opensolaris:~# zpool status -v
 pool: oraclepool
 state: ONLINE
 scrub: none requested
config:

 NAME        STATE     READ WRITE CKSUM
 oraclepool  ONLINE       0     0     0
 c7t1d0      ONLINE       0     0     0
 c7t2d0      ONLINE       0     0     0

errors: No known data errors

 pool: rpool
 state: ONLINE
 scrub: none requested
config:

 NAME        STATE     READ WRITE CKSUM
 rpool       ONLINE       0     0     0
 c7t0d0s0    ONLINE       0     0     0

errors: No known data errors

With that done, I created a few file systems:

  • oraclepool/binaries
  • oraclepool/oradata
zfs create oraclepool/oradata
zfs create oraclepool/binaries

By default these are mounted to poolname/fs name, i.e. /oraclepool/oradata which isn’t too convenient. Luckily, zfs allows you to easily change the mountpoint without touch /etc/vfstab (which isn’t recommended anyway).

So, enter these commands to change the mountpoints:

zfs set mountpoint=/u01 oraclepool/binaries
zfs set mountpoint=/u01/oradata oraclepool/oradata

The final layout prior to the installation was as follows:

zfs set mountpoint=/u01 oraclepool/binaries
root@opensolaris:~# zfs list
NAME                       USED  AVAIL  REFER  MOUNTPOINT
oraclepool                7.98G  11.6G    19K  /oraclepool
oraclepool/binaries       6.68G  11.6G  6.68G  /u01
oraclepool/oradata        1.30G  11.6G  1.30G  /u01/oradata
rpool                     5.27G  2.55G  77.5K  /rpool
rpool/ROOT                3.01G  2.55G    19K  legacy
rpool/ROOT/opensolaris    3.01G  2.55G  2.87G  /
rpool/dump                 256M  2.55G   256M  -
rpool/export              2.22M  2.55G    21K  /export
rpool/export/home         2.20M  2.55G  1.63M  /export/home
rpool/export/home/martin   584K  2.55G   584K  /export/home/martin
rpool/swap                   2G  4.08G   474M  -

Oracle user creation

Create the oracle user as always, I did the following:

  • groupadd oinstall
  • groupadd dba
  • useradd -g oinstall -G dba -d /export/home/oracle -s `which bash` -m oracle
  • chown -R oracle:oinstall /u01
  • projadd -U oracle -K “project.max-shm-memory=(priv,4096MB,deny)” user.oracle
  • projmod -s -K “project.max-sem-ids=(priv,256,deny)” user.oracle

Set static IP

Edit /etc/hosts to include your hostname, then edit /etc/nwam/llp to include your network interface, the keyword “static” and the ip/netmask.

My example uses:

root@opensolaris:~# cat /etc/nwam/llp
xnf0    static 192.168.99.11/24

Change Swap

It’s necessary to increase swap space or otherwise the ld will fail during the “linking phase”.  I increased to 2G from 512M:

root@opensolaris:~# zfs get volsize rpool/swap
NAME        PROPERTY  VALUE    SOURCE
rpool/swap  volsize   512M     -
root@opensolaris:~# zfs set volsize=2G rpool/swap
root@opensolaris:~# zfs get volsize rpool/swap
NAME        PROPERTY  VALUE    SOURCE
rpool/swap  volsize   2G       -

Oracle Installation

We need to create a symlink for -lcrypto, otherwise one of the shared libraries won’t link with a missing reference to “-lcrypto”:

ln -s /lib/amd64/libcrypto.so /usr/sfw/lib/amd64

Run the installer in silent mode – I modified one of the response files and simply executed ./runInstaller -silent -debug -fore -responseFile /path/to/responseFile.rsp -ignoreSysPrereqs

Database Creation

I ran dbca in silent mode as oracle as follows:

cd /u01/app/oracle/product/11.2.0/dbhome_1/
export ORACLE_HOME=`pwd`
cd bin
./dbca -silent -createDatabase -gdbName orcl \
  -templateName General_Purpose.dbc -emConfiguration none \
  -datafileDestination /u01/oradata -sysPassword xxx \
  -systemPassword xxx -storageType FS -initParams  \
  filesystemio_options=setall

Success!

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> !cat /etc/release
 OpenSolaris 2009.06 snv_111b X86
 Copyright 2009 Sun Microsystems, Inc.  All Rights Reserved.
 Use is subject to license terms.
 Assembled 07 May 2009

SQL>

Now off to learn dtrace…

Reference

Praise where praise is due-I got some good pointers from the pythian blog.

Posted in 11g Release 2, solaris | Tagged: , , , , , | 1 Comment »

Build your own RAC system part V – add RDBMS home

Posted by Martin Bach on February 15, 2010

This article concludes my short series about how to build your own RAC system-I might explore an extended distance cluster with 11.2 soon so stay tuned for more material. As you could read in my previous posts, I have installed a cluster on 2 (now 3) nodes, with a fresh 11.2 Grid Infrastructure installation. I then wanted to extend my RDBMS home to the 3 node.

As I use to say-it’s easy to extend RAC once the cluster layer is in place and working. In my case, that was a given so I continued with the extension.

I knew from extending the Grid Infrastructure that addNode.sh was headless and silent-you need to pass appropriate parameters and it will do it all for you. In my case I logged in as oracle to the first node and changed directory to $ORACLE_HOME/oui/bin. There I tried the following, and it worked without problems! My cluster is made up of rac11gr2node{1,2,3}, the RDBMS software was already present on nodes 1 and 2. I should actually have run a cluvfy but was too lazy (I wouldn’t do this on any non-lab environment!)

Read the rest of this entry »

Posted in 11g Release 2, Linux, RAC | Leave a Comment »

Server Pool experiments in RAC 11.2

Posted by Martin Bach on February 12, 2010

I spent Wednesday at UKOUG RAC & HA SIG and it was one of the best events I ever attended. Great  audience, and great feedback. One question I was particularly interested in was raised during my presentation, regarding server pools. I have now finally had the chance to experiment with this exciting new feature, my findings are in the blog post. I’ll see if the automatic assignment of nodes to pools works as advertised as well, but that’s for another post. Already this one turned out to be a monster!

Setup

I have installed a 3 node RAC cluster on Oracle Enterprise Linux 5 update 4 32bit to better understand server pools. I have read a lot about the subject, but as always, first hand experience pays off more than just reading. My environment uses GPnP, essentially it is the environment I described in part 2 of my build your own RAC 11.2 system, extended by another node. Read the rest of this entry »

Posted in 11g Release 2, Linux, RAC | Tagged: , , , | 10 Comments »

Check for non-successful connection attempts in listener.log

Posted by Martin Bach on February 9, 2010

This could become a regular question from your security team-can you find out if someone tried to mess with the listener when trying to connect? Often you see hackers target port 1521 and sending random data garbage through the wire. The listener initially accepts the connection but closes it when it doesn’t receive data it expects.

This is another reason why unix/linux is way cooler than Windows.

Let’s assume you need to find if there were any unsuccessful connection entries in the listener.log for a given day. First of all-how do they have to look if they are successful? Typical entries are as follows:

1 08-FEB-2010 04:49:54 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=testserv) *
   (ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=4307)) * establish * testserv * 0
2 08-FEB-2010 04:49:55 * service_update * dev1 * 0
3 08-FEB-2010 04:49:57 * service_update * dev3 * 0

The important bit is at the end-the “0” means “normal, successful completion”. If there is a problem, you would therefore assume there is an Oracle error number from the TNS range (>12000).

Awk is the swiss army knife to find such results, and this is how you could use it (apologies in advance for my poor command of awk-if you know a better way please let me know!)

$> grep "08-FEB" listener.log | awk  '{ if ( $NF != 0 ) print $0 }'

The built-in variable NF is the last of all the fields which are enumerated from $1. So in summary, this little snippet does the following:

  1. Find all lines for a given day (here: February 8th) in the $ORACLE_HOME/network/log/listener.log file
  2. Print the lines where the last field is not equal to 0

This can easily be wrapped up into a nagios check to be executed by NRPE-if in case of doubt: simplify (to quote Piet de Visser).

So next time you get output such as:

1 09-FEB-2010 16:25:56 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.36.135)
   (PORT=3929)) * establish * <unknown sid> * 12525

… something fishy might be going on. oerr ora <number> gives you more information about what happened.

By the way this is gawk-3.1.5-14.el5 on RHEL 5.3.

Posted in Linux, Oracle, Security | 5 Comments »

Upgrade ASM 10.2 to 11.2 single instance

Posted by Martin Bach on February 8, 2010

This post describes how to upgrade an existing single instance ASM installation to the latest and greatest, Oracle 11.2. The most noteworthy change is that ASM is no longer installed using RDBMS installer but rather the Grid Infrastructure.

Huh-installing CRS for single instance? That at first sounded like a bit of an overkill but Oracle left us with no choice. As you can see later, it’s not as bad as it seems, I have to say I rather like the Oracle Restart feature (see my previous blog post).

So-as always-start by downloading the necessary software linux.x64_11gR2_grid.zip and unzip it somewhere convenient.

Preparation

I recommend getting some metadata from the ASM instance just in case:


SQL> select name,state from v$asm_diskgroup;

NAME			       STATE
------------------------------ -----------
DATA			       MOUNTED
FRA			       MOUNTED
LOG			       MOUNTED

SQL> select name,path from v$asm_disk;

NAME                           PATH
------------------------------ -------------------
DATA1                          ORCL:DATA1
LOG1                           ORCL:LOG1
FRA1                           ORCL:FRA1

SQL> show parameter asm

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups			     string	 DATA, FRA, LOG
asm_diskstring			     string	 ORCL:*
asm_power_limit 		     integer	 1
SQL>

Read the rest of this entry »

Posted in 11g Release 2, Automatic Storage Management, Linux | Tagged: , , , , , | 3 Comments »

OpenSolaris 2009.06 domU on opensuse 11.2 dom0

Posted by Martin Bach on February 5, 2010

I was curious to get started with opensolaris and quite eager to install it as a domU on a Linux dom0. There would have been little problem to do it the other way around. Actually, I could have installed opensolaris on my Toshiba R600 too!

Then I tried out a number of current linux distributions, but except for openSuSE none had a dom0 kernel out of the box which really is a shame. Seems I need to look more closely into KVM with virtio support.

Read the rest of this entry »

Posted in Linux, Xen | 10 Comments »