Martins Blog

Trying to explain complex things in simple terms

Archive for January, 2010

How to move OCR and voting disks into ASM

Posted by Martin Bach on January 29, 2010

Moving OCR and voting disk into ASM is the Oracle Grid Infrastructure or 11.2 way of storing essential metadata. During new installations this is quite simple since the work is done by OUI. When upgrading to 11.2 from a previous Oracle release, things look slightly different. Note that storing OCR and voting disk on raw or block devices-as we were doing it up to 11.1 is no longer possible except for migrated systems.

This is a transcript of some work I did to prove that it’s possible to move OCR and voting disk into ASM. It’s not been entirely simple though! Here’s my current setup after the migration of 10.2.0.4 -> 11.2.0.1.

Be warned though that this is very technical and even longer, but I decided to include the output of all commands just in case one of my readers comes across a similar problem but wants to find out if it’s the exactly same. Read the rest of this entry »

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

Compile DBD::Oracle 1.23 with Oracle 11.2 client

Posted by Martin Bach on January 28, 2010

This article discusses how to compile DBD::Oracle for Oracle client 11.2 x86-64 on Linux (Fedora 12 to be precise). I recommend not to mess around with your distribution’s perl, that’s why I will base it on ActivePerl 5.10 from Activestate.com. If you (and you system administrator don’t mind mangling your distribution’s perl you might skip the first bit and continue straight with the section DBD::Oracle.

This is particularly useful for installations on nagios servers to enable monitoring of Oracle databases directly through perl’s DBD::Oracle without those ugly “sqlplus -S….<<EOF” constructs I see far too often.

Active Perl

I downloaded active perl straight from their website, it’s version 5.10.1.1006 for glibc 2.3.3-291086. Download and unzip the file:

gunzip -cd ActivePerl-5.10.1.1006-x86_64-linux-glibc-2.3.3-291086.tar.gz | tar xvf -
cd ActivePerl*
sudo ./install.sh

I installed the distribution into /opt/ActivePerl-5.10, the default, and also generated the HTML documentation. Don’t forget to change your environment variables, such as PATH before using the new perl

DBD::Oracle

Download DBD::Oracle, version 1.23 was current at the time of this writing:

wget http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.23.tar.gz
cd DBD-Oracle-1.23
. oraenv # will set your LD_LIBRARY_PATH, $ORACLE_HOME and other variables needed.
export PATH=/opt/ActivePerl-5.10/bin:$PATH

From then on everything should be pretty smooth sailing:

perl Makefile.pl
Using DBI 1.607 (for perl 5.010001 on x86_64-linux-thread-multi) installed in /opt/ActivePerl-5.10/lib/auto/DBI/
Argument "6.55_02" isn't numeric in numeric ge (>=) at Makefile.PL line 61.

Configuring DBD::Oracle for perl 5.010001 on linux (x86_64-linux-thread-multi)

Remember to actually *READ* the README file! Especially if you have any problems.

Installing on a linux, Ver#2.6
Using Oracle in /u01/app/martin/product/11.2.0/client_1
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
Oracle version 11.2.0.1 (11.2)
Found /u01/app/martin/product/11.2.0/client_1/rdbms/lib/ins_rdbms.mk
Using /u01/app/martin/product/11.2.0/client_1/rdbms/lib/ins_rdbms.mk
...

I was a bit surprised to see the following warning during the execution of the perl Makefile.pl command. I decided

WARNING: Oracle /u01/app/martin/product/11.2.0/client_1/rdbms/lib/ins_rdbms.mk doesn’t define a ‘build’ rule.

WARNING: I will now try to guess how to build and link DBD::Oracle for you.
This kind of guess work is very error prone and Oracle-version sensitive.
It is possible that it won’t be supported in future versions of DBD::Oracle.
*PLEASE* notify dbi-users about exactly _why_ you had to build it this way.

I guess that’s owed at the relatively new Oracle client version. Execute make to build the module.

You should also test the build before proceeding. This proved to be a bit more tricky, you need to set some environment variables first. These are:

  • export PATH=/opt/ActivePerl-5.10/bin:$PATH
  • export ORACLE_DSN=”dbi:Oracle:<valid tnsnames.ora entry>”
  • export ORACLE_USERID=username/password

Don’t forget ORACLE_HOME and LD_LIBRARY_PATH etc, easiest done through a quick “source oraenv” in your session. With that set you can issue a “make test”. Edit Makefile line 1005 and set TEST_VERBOSE to 1 if you want more output from the tests which is useful for troubleshooting.

Finally, issue the “sudo make install” to install the DBD::Oracle package into your new perl distribution.

You’re done, congratulations!

Posted in Linux, Oracle, Perl | 3 Comments »

Policy managed databases

Posted by Martin Bach on January 26, 2010

This is a long overdue reply to an email Mark Bobak sent to the oracle-l mailing list. The questions Mark asked were regarding policy managed databases, a new 11g Release 2 feature. Since I have been very interested in the same topic (and also partly due to the fact that I am presenting about this at UKOUG) I dug a bit deeper.

I have to say that I am very intrigued by the concept of server pools and policy based databases even though the largest cluster I have is only 3 nodes (but very powerful ones), so it’s possibly not at all relevant for me.

The only other useful source of information besides the Oracle doc I found is this one is from the very good oracleracsig.org website:

Oracle Clusterware 11g Release 2 – a first glimpse under the hood

Read the rest of this entry »

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

RAC & HA SIG 10/02/2010

Posted by Martin Bach on January 21, 2010

Just a quick note that I am going to present at RAC and HA SIG early February 2010 about server consolidation to RAC in my current company.

Checking the agenda I could see a lot of very good other presentations so it might be worth coming along. The only downside is that it’s in Thames Valley Park (TVP), Oracle’s corporate headquarters in the UK. Which wouldn’t be bad in itself but the food on offer there is simply terrible compared to non-Oracle venues such as Baylis House in Slough or Great George Street in London.

The agenda is now online, have a look at this link:

http://www.ukoug.org/calendar/show_event.jsp?id=4547

Hoep to see you there!

Posted in Public Appearances | Tagged: , | Leave a Comment »

Upgrade Clusterware 10.2 to 11.2

Posted by Martin Bach on January 21, 2010

This post is about upgrading Oracle Clusterware 10.2.0.4 on Oracle Enterprise Linux 5.4 x86-64. The setup is as follows:

  • 3 node RAC system, hostnames: racupgrade1, racupgrade2, racupgrade3
  • Oracle Clusterware 10.2.0.4 with CRS bundle patch 4-(patch 8436582)
  • Oracle ASM 10.2.0.4.1, i.e. PSU 10.2.0.4.1 (patch 8576156)
  • Oracle RDBMS 10.2.0.4.1
  • Additionally I patched OPatch and applied one off patch 6079224 (“THE INSTANCE CAN NOT START DUE TO IPC SEND TIMEOUT”)

Please note that at the time of this writing CRS bundle patch 4 is superseded by Patch Set Update 2 (PSU 2) for CRS 10.2.0.4, and the latest RDBMS/ASM PSU is 10.2.0.4.3.

I use ASMLib for the ASM disks and initially bound CRS and OCR to raw devices (raw1 and raw2).

Read the rest of this entry »

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

screen fails with “Cannot open your terminal ‘/dev/pts/x’ – please check.”

Posted by Martin Bach on January 16, 2010

A quick note on how to get around this problem. Background: many shops uses individual operating system accounts for DBAs and keep the oracle password secret. Once connected, the user would sudo to oracle: “sudo su – oracle” which is explicitly allowed. The auditors can then trace who did what and when, otherwise the logins to oracle would be almost completely anonymous.

Here’s a sample session output to demonstrate the problem:

login as: mbh
mbh@prodbox's password:
Last login: Thu Jan 14 12:11:12 2010 from desktop001

RHN kickstart on 2009-07-27

[mbh@prodbox ~]$ sudo su - oracle
[oracle@prodbox ~]$ screen
Cannot open your terminal '/dev/pts/4' - please check.

This is slightly frustrating-starting the screen session with your account works fine, but then no one can follow up and connect to your session. The quick but insecure solution is as follows:after logging in as yourself, find out which tty you use:

[oracle@prodbox ~]$ w | grep mbh
mbh    pts/4    desktop001 12:14    0.00s  0.05s  0.07s sshd: mbh

Then grant permission to your tty to the world:

[mbh@prodbox ~]$ chmod a+rw /dev/pts/4

Alternatively, add the oracle user to group tty, which owns all the ttys.

Now sudo to oracle and start your screen sesssion:

[mbh@prodbox ~]$ sudo su - oracle
[oracle@prodbox ~]$ screen
[screen is terminating]

Also check the comment by Ariel for another solution. Anyway, check with your security team what method is most appropriate in your situation.

Posted in Linux | Tagged: , , , | 11 Comments »

Xen-based virtualisation with opensuse 11.2

Posted by Martin Bach on January 15, 2010

I recently upgraded my laptop’s opensuse 11.1 installation to 11.2, mainly because it has updated xen to version 3.4 which makes it one of the most modern distributions with xen support available. I did some research first about which linux distribution would best suit my needs. When I came across a post which said that Fedora 12 had no (official) kernel support for use as dom0 the decision was made. I know I could have used a debian clone (ubuntu 9.10 seemed quite attractive), but for personal reasons I preferred a RPM based system. I was very pleasently surprised that the Intel GMA 4500MHD graphics chipset finally found hardware acceleration, making it so much more enjoyable to browse the web.

Read the rest of this entry »

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

Hide SQL query in spool file

Posted by Martin Bach on January 14, 2010

This is a common problem I have: I need to generate a SQL script to be called from another SQL script in SQLplus. Best example: export user names, using this command for example:
select 'alter user ' || username || ' identified by values ''' || password || ''';' from dba_users;

To avoid the usually useful output in this case I used to set a few options, such as in:

set heading off pages 0 trimspool on lines 120 feedback off echo off termout off

Inside a shell script this could look as follows:

$ORACLE_HOME/bin/sqlplus / as sysdba <<EOF
 set heading off pages 0 trimspool on lines 120 feedback off echo off termout off
 spool /tmp/users.sql
 select 'alter user ' || name || ' identified by values ''' || password || ''';'
 from dba_users;
 exit
 EOF
However, the resulting file users.sql contains the SQL command! I didn’t find a “set” option in SQLPlus to get rid of it so I ended up “grep”ping for “^alter” to only have the SQL commands. Then I found out that I could as well use sqlplus -S (capital S) to achieve the same. Very handy!

Posted in Oracle | Tagged: , | 8 Comments »

Rename cloned diskgroup in Oracle 10.2

Posted by Martin Bach on January 12, 2010

Before I’ll start with this post, first a few words of warning (and this time I mean it!): even though the described method here works for me it is neither supported or even mentioned as possible in the Oracle documentation or metalink. Use at your own risk, and never with production data. I will not take responsibility for corrupted disk headers here :) Also, this post is for educational purpose only.

Now with that said, I’ll give you some background to this post. During a project I was involved in recently it was decided to upgrade the estate from 10.2.0.3 32bit on RHEL 3 to 10.2.0.4.1 on 64bit RHEL 5. The storage layer changed as well, from OCFS v1 (don’t ask!) to ASM. However, some of the processes that used to be possible such as cloning database LUNs on the SAN via a BCV copy would cause problems now when the cloned LUNs are to be presented to the same host as the source LUNs.

Read the rest of this entry »

Posted in Automatic Storage Management, Oracle, RAC | Tagged: , , , | 7 Comments »

Installation of Fedora 12 x86-64 on Dell Optiplex 760

Posted by Martin Bach on January 9, 2010

These office PCs are actually quite nice-not powerfull but neither the opposite with a Core 2 Duo processor and in my case 4 GB of memory. I think they might be able to take on more memory which could come in handy later. The machines come with an on-board graphics and sound device and almost no legacy connectors such as ps/2. What can I say-these things are what I am currently given at work so getting rid of Windows and using Linux was my choice. I also hoped I could run xen para-virtualised guests but that proved to be not so straight forward-I think I managed to do it but I havent rebooted the box yet into the paravirtualised kernel. And Fedora doesn’t come with a kernel-xen as opensuse does. But I am getting ahead of myself. The following sections may seem trivial but were quite problematic when I worked on them, it cost me a few hours to work out all the issues.

Read the rest of this entry »

Posted in Linux | Leave a Comment »