Tag Archives: Oracle

Limiting the Degree of Parallelism via Resource Manager and a gotcha

This might be something very obvious for the reader but I had an interesting revelation recently when implementing parallel_degree_limit_p1 in a resource consumer group. My aim was to prevent users mapped to a resource consumer group from executing any query in parallel. The environment is fictional, but let’s assume that it is possible that maintenance operations for example leave indexes and tables decorated with a parallel x attribute. Another common case is the restriction of PQ resource to users to prevent them from using all the machine’s resources.

This can happen when you perform an index rebuild for example in parallel to speed the operation up. However the DOP will stay the same with the index after the maintenance operation, and you have to explicitly set it back:

SQL> alter index I_T1$ID1 rebuild parallel 4;

Index altered.

SQL> select index_name,degree from ind where index_name = 'I_T1$ID1';

INDEX_NAME		       DEGREE
------------------------------ ----------------------------------------
I_T1$ID1		       4

SQL>

Continue reading

OUGN Spring meeting 2012

I had the great pleasure to spend the better part of last week at the Norwegian Oracle User Group’s spring conference. Martin Nash and I helped promote the Real Application Cluster platform on the attendees’ laptop in a program called RAC Attack.  RAC Attack has its home on the wikibooks website http://racattack.org where the whole program is documented and available for self-study. The purpose of the hands-on labs which Jeremy Schneider started a few years ago is to allow users to get practical experience installing Oracle Linux, Grid Infrastructure and the RDBMS binaries before creating a two node database. Following the database creation a practical session ensues which explains certain HA concepts with RAC such as session failover. We are planning on greatly enhancing the lab session as we go along. If you have any suggestions about what you would like to see covered by us then please let us know!

Continue reading

Provision Oracle RDBMS software via RPM

I have always asked myself why Oracle doesn’t package their software as an RPM-surely such a large organisation has the resources to do so!

Well the short answer is they don’t give you an RPM, except for the XE version of the database which prompted me to do it myself. The big problem anyone faces with RPM is that the format doesn’t seem to support files larger than 2GB. Everybody knows that the Oracle database installation is > 2G which requires a little trick on our side. And the trick is not even obscure in any way as I remembered: some time ago I read an interesting article written by Frits Hoogland about cloning Oracle homes. It’s still very relevant and can be found here:

http://fritshoogland.wordpress.com/2010/07/03/cloning-your-oracle-database-software-installation/

Now that gave me the idea:

  1. You install the oracle binaries on a reference host
  2. Apply any patches and PSUs you need
  3. Wrap the oracle home up in a tar-ball just the way Frits describes by descending into $ORACLE_HOME and creating a tar archive of all files, excluding those ending in “*.log”, network config files in $ORACLE_HOME/network/admin and anything in $ORACLE_HOME/dbs. We don’t want to proliferate our database initialisation files …
  4. You make that tarball available on a central repository and export that with CIFS/NFS or whatever other mechanism you like
  5. Mount this exported file system in /media, so that /media/db11.2.0.3/ has the database.tar.gz file available
  6. Install the RPM

Simple! Piet de Visser would be proud. Continue reading

Database Gateway for Sybase

Before starting to write this article I was wondering if it was of any use to anyone. Who in their right state of mind would use the Database Gateway for xxx, be it Sybase or Adabase or whatever othere database was to be used. If you have to, simply configure another data source in your code and read data from where you need it. Note that I said read! Ever since Piet de Visser’s presentation about simplicity I have been a great supporter of the simplicity approach. Or, like James Morle likes to quote, simplicity is the ultimate form of sophistication.

Transparent Gateways desupported

So, anyway, I have been asked to link Sybase ASE 15.0.3 to an Oracle 10.2.0.4 database, all on Solaris 10/SPARC 64bit. A quick proof of concept had to be performed. Initially I started out with Transparent Gateway for Sybase, the name of the product for 10g Release 2. I should have known something was wrong when the link to download the software was broken and I had to manually copy & paste it. Well to cut a long story short, 10.2 gateways are desupported since 2008! I wasted a whole 2 hours before that came up on MOS. The workaround is to use > 10.2 software for this, and I went for 11.2. MOS states that this is possible for > 9.2.0.7, > 10.1.0.4 and > 10.2.0.3. My database was 10.2.0.4 which means I’m fine.

Continue reading

It is indeed possible to install Oracle 11.2 on OpenSolaris

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.