Martins Blog

Trying to explain complex things in simple terms

Archive for July, 2011

Oracle RAC SIG presentation tips

Posted by Martin Bach on July 29, 2011

Yesterday I proudly presented a one hour training class about upgrading to Oracle 11.2 RAC at oracleracsig.org. This was the first time I presented using this facility and thought it might be useful for others to learn about the procedures and hopefully encourage other speakers to follow suit. It’s really straight forward and there is nothing to worry about! Especially if you are already familiar with webex, presenting should be a piece of cake. But I’m getting ahead of myself.

So how do you get to present?

Read the rest of this entry »

Posted in Public Appearances, RAC | Tagged: , , , | 1 Comment »

So you don’t see any disks when trying to install ASM?

Posted by Martin Bach on July 25, 2011

This is a post that highlights the difference between operating systems, but also the fact that sometime it is hard to break out of a habit once you got used to it. My background is that of a Linux enthusiast, even though I equally like Solaris and AIX but I have a little less exposure to those.

Background

I have recently been asked to look at RAC on SPARC, which I gladly did. The system I was given had the usual software stack for RAC at this customer’s site. It comprised of:

  • Solaris 10 Update 9 64bit on SPARC
  • EMC Power Path 5.1
  • EMC VMAX storage – 10x10G LUNs for a specific performance test

The Power Path configuration has already been in place when I got the machine, and I was allocated /dev/emcpower2[0-9] for my ASM testing. For the experienced Linux user who relies on device-mapper-multipath, the Power Path naming convention can be a bit confusing at first. For reference, the pseudo devices we are interested in for ASM are created under /dev/rdsk/-the “raw” device directory for “character” based access rather than the block device in /dev/dsk/.  By default, the Power Path devices are called “emcpower”, followed by a number and a letter (in SPARC). An example would be /dev/rdsk/emcpower20c. Read the rest of this entry »

Posted in 11g Release 2, Automatic Storage Management | 2 Comments »

Compiling iozone for Solaris 10 on SPARC

Posted by Martin Bach on July 21, 2011

I have started working with ZFS and its various ways of protecting disks from failure. It’s a low end setup at best, where a JBOD is used as an extension to a M-series server. Many JBODs come with SAS connectivity only, and no on-board intelligence so a host based solution has to be chosen to protect the lot from disk failures.

For Solaris, you can use ZFS amongst other solutions. Alternatively, ASM is a possibility. I couldn’t reproduce the exact setup, so I had to improvise. Still I wanted to find out how ZFS performs compared to ASM. For this purpose I used an EMC VMX and a Sun 5410 server which had 10 multipathed 10G LUNs presented to it via EMC Power Path 5.3.

To test the file system performance I decided to use both IOZone and Bonnie++. Bonnie++ is no problem, you can get it from Sun Freeware. Note that Oracle no longer produce the Companion CD, which leaves SunFreeware the only source for alternative packages. Read the rest of this entry »

Posted in solaris | 1 Comment »

Using Connection Manager to protect a database

Posted by Martin Bach on July 11, 2011

I have known about Oracle’s connection manager (CMAN) for quite a while but never managed to use it in anger. In short there was no need to do so. Now however I have been asked to help in finding a solution to an interesting problem.

A long story short is that access to databases has to be governed via a mechanism that worked well with firewalls and allowed to regulate access to databases based on rule sets. CMAN can do that, and a few other things that are out of scope of this article.

The architecture I use in this post includes a number of hosts. Simulating end users I created a VM named client, and it’s on the 192.168.99.0/24 network. The “router” is named CMAN in a fit of creativity, and it connects the 192.168.99.0/24 network to the database network on 192.168.100.0/24 network. The database server to which access is regulated via CMAN is named CMANDB. Crucially, there is no routing enabled on the CMAN host. In other words, there is no direct connection possible from the client to the database server. Additional protection of the database host could have been achieved by using firewall rules, but that was out of scope of this post.

Overview of Connection Manager 11.2

For those of you who aren’t familiar with CMAN, here’s a short summary (based on the official Oracle documentation).

Configuration of Oracle Connection Manager (CMAN) allows the clients to connect through a firewall [I haven’t verified this yet, ed]. CMAN is an executable that allows clients to connect despite a firewall being in place between the client and server. CMAN is similar to the Listener in that it reads a configuration file [called CMAN.ora, ed], which contains an address that Oracle Connection Manager listens for incoming connections. CMAN starts similar to the Listener and will enter a LISTEN state.

This solution [to the firewall issue with TCP redirects, ed] will make the REDIRECT happen inside the firewall and the client will not see it; CMAN comports as a proxy service between the client and the real database listener.

Interestingly, Connection Manager is fully integrated into the FAN/FCF framework and equally suitable for UCP connection pools.

Installing Connection Manager

Connection Manager is part of the Oracle client, and you can install it by choosing the “custom” option. From the list of selectable options, pick “Oracle Net Listener” and “Oracle Connection Manager” plus any others you might need.

From there on it’s exactly the same as any other client installation. You should probably consider applying the latest PSU to the client installation.

Testing

A quick test with 2 separate networks reveals how the concept actually works. As I said in the introductoin the following hosts are used:

  • cman: 192.168.99.224
  • cmandb: 192.168.100.225
  • client: 192.168.99.31

The networks in use are:

  • Public network: 192.168.99.0/24
  • Private network: 192.168.100.0/24

Crucially, CMANDB is on a different network than the other hosts with cman acting as the arbiter on 192.68.99.224.

As indicated, connection manager has been installed on host “cman”, with IP 192.168.99.224 and listens on port 1521. The corresponding cman.ora file has been configured as follows in $CLIENT_HOME/network/admin:

cman1 =
  (configuration=
    (address=
      (protocol=tcp)(host=192.168.99.224)(port=1521)
    )
    (rule_list=
      (rule=(src=192.168.99.224)(dst=127.0.0.1)(srv=cmon)(act=accept))
      (rule=(src=192.168.99.0/24)(dst=192.168.100.225)(srv=*)(act=accept))
    )
  )

The file has been left in this minimalistic state deliberately, please consult the documentation for options available. The CMAN listener – not to be confused with the net8 listener – listens on port 1521.

There are two minimalistic rules:

  • Rule number one is necessary to allow management access to Connection Manager. Without it, CMAN will not start.
  • Rule number two actually governs access to the database hosts on CMANDB

There might be another one needed if IPv6 is in use on the host. Should CMAN not start and complain about TNS-04014 and TNS-12529 check MOS 1059938.1 for a solution.

The gateway host has 2 network interfaces, one for each network:

[root@cman ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:16:3E:F2:34:56
          inet addr:192.168.99.224  Bcast:192.168.99.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1209 errors:0 dropped:0 overruns:0 frame:0
          TX packets:854 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:105895 (103.4 KiB)  TX bytes:147462 (144.0 KiB)
          Interrupt:17

eth1      Link encap:Ethernet  HWaddr 00:16:3E:52:4A:56
          inet addr:192.168.100.224  Bcast:192.168.100.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:334 errors:0 dropped:0 overruns:0 frame:0
          TX packets:151 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:36425 (35.5 KiB)  TX bytes:22141 (21.6 KiB)
          Interrupt:16

Configuration on host “CMANDB”

Note that host 192.168.100.225 is on the private network! The database CMANDB has its local and remote listener configured using the below entries in tnsnames.ora:

[oracle@cmandb admin]$ cat tnsnames.ora
LOCAL_CMANDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 192.168.100.225))
    )
  )

REMOTE_CMANDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 192.168.100.224))
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 192.168.100.225))
    )
  )

CMAN_LSNR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 192.168.99.224))
    )
  )

Translated into human language, this means:

  • local listener is set to local_cmandb
  • remote listener is set to remote_cmandb
  • CMAN_LSNR is used for a test to verify that a connection to the CMAN listener is possible from this host

It should be pointed out at this stage that this applies to single instance databases. If I find the time I’ll write a post about CMAN and RAC later.

The host had only one network interface, allowing connections to the CMAN host:

[root@cmandb ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:16:3E:12:14:51
          inet addr:192.168.100.225  Bcast:192.168.100.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:627422 errors:0 dropped:0 overruns:0 frame:0
          TX packets:456584 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:2241758430 (2.0 GiB)  TX bytes:32751153 (31.2 MiB)

After the database listener parameters have been changed to LOCAL_CMANDB and REMOTE_CMANDB, the alert log on the CMAN host recorded a number of service registrations. This is important as it allows Connection Manager to hand the connection request off to the database:

[oracle@cman trace]$ grep cmandb *
08-JUL-2011 10:19:55 * service_register * cmandb * 0
08-JUL-2011 10:25:28 * service_update * cmandb * 0
08-JUL-2011 10:35:28 * service_update * cmandb * 0
[...]
08-JUL-2011 11:15:10 * service_update * cmandb * 0
08-JUL-2011 11:15:28 * service_update * cmandb * 0
[oracle@cman trace]$

Additionally, the CMAN processes now know about the database service CMANDB:

CMCTL:cman1> show services
Services Summary...
Proxy service "cmgw" has 1 instance(s).
  Instance "cman", status READY, has 2 handler(s) for this service...
    Handler(s):
      "cmgw001" established:0 refused:0 current:0 max:256 state:ready
        <machine: 127.0.0.1, pid: 2298>
        (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=23589))
      "cmgw000" established:0 refused:0 current:0 max:256 state:ready
        <machine: 127.0.0.1, pid: 2294>
        (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=31911))
Service "cmandb" has 1 instance(s).
  Instance "cmandb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
      REMOTE SERVER
        (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=192.168.100.225)))
Service "cmandbXDB" has 1 instance(s).
  Instance "cmandb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
      DISPATCHER <machine: cmandb.localdomain, pid: 7167>
      (ADDRESS=(PROTOCOL=tcp)(HOST=cmandb.localdomain)(PORT=50347))
Service "cmon" has 1 instance(s).
  Instance "cman", status READY, has 1 handler(s) for this service...
    Handler(s):
      "cmon" established:1 refused:0 current:1 max:4 state:ready
      <machine: 127.0.0.1, pid: 2282>
     (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59541))
The command completed successfully.
CMCTL:cman1>

The previous output has been obtained from cmctl, the CMAN control utility.

Connectivity Test

With the setup completed it was time to perform a test from a third host on the (public) network. Its IP address is 192.168.99.31. The below TNSnames entries were created:

[oracle@client admin]$ cat tnsnames.ora
CMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PORT=1521)(HOST=192.168.99.224)(PROTOCOL=TCP))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cmandb)
      (SERVER = DEDICATED)
    )
  )

DIRECT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PORT=1521)(HOST=192.168.100.225)(PROTOCOL=TCP))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cmandb)
      (SERVER = DEDICATED)
    )
  )

The CMAN entry uses the Connection Manager gateway host to connect to database CMANDB, whereas the DIRECT entry tries to bypass the latter. A tnsping should show whether or not this is possible. Before any of the testing can take place it is necessary to start CMAN using “cmctl startup -c cman1”.

[oracle@client admin]$ tnsping direct
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JUL-2011 11:19:47

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PORT=1521)(HOST=192.168.100.225)(PROTOCOL=TCP))) (CONNECT_DATA = (SERVICE_NAME = cmandb) (SERVER = DEDICATED)))

TNS-12543: TNS:destination host unreachable

[oracle@client admin]$ tnsping cman

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JUL-2011 10:53:27

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PORT=1521)(HOST=192.168.99.224)(PROTOCOL=TCP))) (CONNECT_DATA = (SERVICE_NAME = cmandb) (SERVER = DEDICATED)))

OK (0 msec)

[oracle@client admin]$

Now that proves that a direct connection is impossible, and also that the connection manager’s listener is working. A tnsping doesn’t imply that a connection is possible though, this requires an end to end test with SQL*Plus:

[oracle@client admin]$ sqlplus system/xxx@cman

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 8 10:55:39 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
cmandb
cmandb.localdomain

The successful connection is also recorded in the CMAN log file:

Fri Jul 08 10:55:39 2011

08-JUL-2011 10:55:39 * (CONNECT_DATA=(SERVICE_NAME=cmandb)(SERVER=DEDICATED)(CID=(PROGRAM=sqlplus@client)(HOST=client)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.31)(PORT=16794)) * establish * cmandb * 0

(LOG_RECORD=(TIMESTAMP=08-JUL-2011 10:55:39)(EVENT=Ready)(CONN NO=0))

My idea to limit access to the database via rules seems to have been working.

Posted in 11g Release 2, Linux, Xen | Tagged: , , | 3 Comments »