Search

OakieTags

Who's online

There are currently 0 users and 41 guests online.

Recent comments

Affiliations

July 2011

phpMyAdmin 3.4.3.1…

I’m not a major user of mySQL, but I’ve been a dabbler for quite a few years as I use it for my website.

For my day-to-day poking around I tend to use phpMyAdmin. I don’t always keep on top of phpMyAdmin upgrades, but today I upgraded to version 3.4.3.1, which comes with a new default theme. I know it’s rather shallow, but what a difference a pretty interface makes. :)

Cheers

Tim…




phpBB 3.0.9 Released…

phpBB 3.0.9 has been released. You can read the announcement here.

Cheers

Tim…




Fastest £1,000 server – what happened?

A couple of people have asked me recently what happened to that “fastest Oracle server for a grand” idea I had last year, after all I did announce I had bought the machine.

{Update – it came back.}
Well, a couple of things happened. Firstly, what was a small job for a client turned into a much more demanding job for a client – not so much mentally harder as time-consuming harder and very time consuming it was. So the playing had to go on hold, the client comes first. The server sat in the corner of the study, nagging me to play with it, but it remained powered down.
Secondly, when the work life quietened down last month and I decided to spend a weekend getting that server set up I hit an issue. I turned on the server and it turned itself straight off. It than rested for 5 seconds and turned itself back on for half a second – and then straight off. It would cycle like that for as long as I was willing to let it.

OK, duff power switch, mother board fault, something not plugged in right, PSU not reaching stable voltage… I opened the case and checked everything was plugged in OK and found the manufacturer had covered everything with that soft resin to hold things in place. I pressed on all the cards etc in hope but no, it was probably going to have to go back. It is still in warranty, the manufacturer can fix it.

So I rang the manufacturer and had the conversation. They were not willing to try and diagnose over the phone so I had to agree to ship it back to them to be fixed {I did not go for on-site support as the only time I did, with Evesham Micros, they utterly refused to come out to fix the problem. Mind you, it turns out they were counting down the last week or two before going bust and, I suspect, knew this}. I shipped it back and the waiting began. Emails ignored, hard to get on touch over the phone. Over three weeks on and they only started looking at the machine last Friday (they claim).

On the positive side, this delay means that solid state storage is becoming very affordable and I might be able to do some more interesting things within my budget.
On the bad side the technology has moved on and I could get a better server for the same money now, but that is always the case. Mine does not have the latest Sandy Bridge Intel processor for example. Also, I have time now to work on it, I hope not to have time next month as I’d like to find some clients to employ me for a bit!

I better go chase the manufacturer. If it is not fixed and on its way back very, very soon then they will be off my list of suppliers and I’ll be letting everyone know how good their support isn’t.

RAC hack session – Tuesday – July 11 2011

I will be conducting a 1-hour deep dive session about RAC LMS process (and about LGWR processes too if time permits) using advanced UNIX utilities. Read Tanel’s blog entry for details:
RAC hack session

See you there!

What Would Cause a NO_INDEX Hint to Not Work as Expected?

July 11, 2011 Recently, the following search keywords were used to access an article on my site, and that search triggered an idea for another blog article: no_index hint oracle 10g not working  In Oracle Database, hints are directives that must be obeyed (with a couple of minor exceptions that include bugs).  I started wondering [...]

Using Connection Manager to protect a database

I have known about Oracle’s connection manager 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.

In summary, my customer is running DR and UAT in the same data centre. Now for technical reasons they rely on RMAN to refresh UAT, no array mirror splits on the SAN (which would be way faster!) possible. The requirement is to prevent an RMAN session with target=UAT and auxiliary=DR from overwriting the DR databases, all of which are RAC databases on 11.2.The architecture included 2 separate networks for the DR hosts and the UAT hosts. DR was on 192.168.99.0/24 whereas UAT was on 192.168.100.0/24. A gateway host with two NICs connects the two. Initially there was a firewall on the gateway host to prevent traffic from UAT to DR, but because of the way Oracle connections work this proved impossible (the firewall was a simple set of IPTABLES rules). After initial discussions I decided to look at connection manager more closely as that is hailed as a solution to Oracle connectivity and firewalls.

Thinking more closely about the problem I realised that the firewall + static routes approach might not be the best one. So I decided to perform a test which didn’t involve IPTABLES or custom routes, and rely on CMAN only to protect the database. A UAT refresh isn’t something that’s going to happen very frequently, which allows me to shut down CMAN, and thus prevent any communication between the two networks. This is easier than maintaining a firewall-remember the easiest way to do something is not to do it at all.

Overview of Connection Manager

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.

Technically speaking Oracle database instances require the initialisation parameters local_listener and remote_listener to be set. In RAC databases, this is usually the case out of the box, however, in addition to the SCAN, the remote_listener must include the CMAN listener as well-an example is provided in this document.

Installing Connection Manager

Connection Manager is now 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”.

From there on it’s exactly the same as any other client installation.

Testing

A quick test with 2 separate networks reveals that the concept actually works. 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

As you can see CMANDB is on a different network than the other hosts.

Connection manager has been installed on host “cman”, with IP 192.168.99.224. The listener process has been configured to listen on port 1821. 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=1821))
(rule_list=
(rule=(src=*)(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. The only connection possible is to the database host. The cmon service must be allowed or otherwise the startup of the connection manager processes will fail.

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

Its routing table is defined as follows:

[root@cman ~]# route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
192.168.100.0   0.0.0.0         255.255.255.0   U     0      0        0 eth1
192.168.101.0   0.0.0.0         255.255.255.0   U     0      0        0 eth2
192.168.99.0    0.0.0.0         255.255.255.0   U     0      0        0 eth0
169.254.0.0     0.0.0.0         255.255.0.0     U     0      0        0 eth2
[root@cman ~]

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

[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 = 1821)(HOST = 192.168.99.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))
)
)
  • 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

The host had only one network interface, connecting 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)

The only change to the system was the addition of a default gateway. Unfortunately the CMAN process cannot listen on more than one IP address:

# route add default gw 192.168.100.224 eth0

The following routing table was in use during the testing:

[root@cmandb ~]# route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
192.168.100.0   0.0.0.0         255.255.255.0   U     0      0        0 eth0
169.254.0.0     0.0.0.0         255.255.0.0     U     0      0        0 eth0
0.0.0.0         192.168.100.224 0.0.0.0         UG    0      0        0 eth0

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

(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=23589))
"cmgw000" established:0 refused:0 current:0 max:256 state:ready

(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 
(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

(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59541))
The command completed successfully.
CMCTL:cman1>

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=1821)(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.

[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=1821)(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))

Ratio of Redo bytes to Datablocks writes

Someone recently asked me what’s the ratio of redo generation to datablock writes, and of course I answered it depends, but like so many things it’s nice to have rules of thumb like random 8k block reads off spindle are around 6ms and sequential redo writes around 3ms  – just points of references or yard sticks, so I decided to browse through AWR stats. I looked at 1000 AWR snapshots. Each snapshot  data gave the averages for a 1 hour period. The snapshots came from 7 databases. I then plotted the distribution:

The ratio is on the left y-axis and % on the bottom x-axis. The majority of times block write bytes is 2-4x more than redo generated bytes.

Less than 2% of ratios are below 1 or above 5.

Initially I plotted each database’s ratio as a lines. It was quite a mess:

 

Here is the query I used

 

SET markup HTML on
spool hist.html
select   write/redo from
( select
dbid,  to_char( begin_time +1/48,'YYYY/MM/DD HH24'),
sum(decode(metric_name,'Redo Generated Per Sec',average,0))  redo,
sum(decode(metric_name, 'Physical Write Bytes Per Sec' ,average,0))  write
from     dba_hist_sysmetric_summary
where    metric_name in
( 'Redo Generated Per Sec',
'Physical Write Bytes Per Sec'
)
group by  dbid,  to_char( begin_time +1/48,'YYYY/MM/DD HH24')
)
order by  1
;
spool off
SET markup HTML off

I don’t see any clear documentation on “Physical Write Bytes Per Sec” so I wonder exactly what I/O is included in this statistics. Clearly this statistics doesn’t include redo because it’s value can be less than redo writes.  The statistic “physical write IO requests” is documented as buffer cache block writes and direct block writes thus I’m betting these are the writes covered by “Physical Write Bytes Per Sec” .

It would be interesting to break down this data into different data operations such as many changes on one block, single change across many blocks, hot back up mode, etc

 

Troubleshooting Oracle Exadata

The crew at My Oracle Support (MOS) [@myoraclesupport] have an excellent starting point for troubleshooting Oracle Exadata. I’d recommend to add this one to your MOS bookmarks.

Oracle Database Machine and Exadata Storage Server Information Center [ID 1306791.1]

LIOs

Randolf Geist has started a short series on Logical I/Os and other buffer visits.


Friday Philosophy – Why do I work with Oracle Technology?

As an Oracle Expert {*cough* bear with me, despite the lack of humility} I make a living based on my skills and knowledge about Oracle Technology. But why Oracle?

I was prompted to think about this as a side issue to a discussion within the OakTable network, about being aligned with companies – and it was suggested we are aligned with Oracle {my personal feeling is strongly that we are not – we are independent of Oracle Corp}.

How many people reading this blog woke up one morning and thought “Hey, I’m going to review all database technologies, work out the best one and work with that!” And then, after investigations, threw their weight behind Oracle? I certainly did not. I joined a company called Oracle UK almost as a way to escape my then management structure. I thought they were a teletext company {see the “about me” tab if you want a bit more detail on that}. I remain working with Oracle technology primarily because that is what I know the most about. It is by luck that I had stumbled into a technical area that went from strength to strength.

Don’t get me wrong, I think some Oracle technology is very, very good. Most of it is OK and some of it is, well, neither of those two. It is much better than the technology I came from (a language called MUMPS) which, though I still have fond memories of, I decided not to return to when the opportunity came up a couple of years into my Oracle life.

I think it is fair to say that I do not work within the Oracle sphere because I am dedicated to the Larry Ellison world vision {if I could work out what it is, but it seems to have less and less to do with a relational database and more and more about being the IT answer to all business needs, for good or bad}. I work in it because it is where I am and it is good enough technology to not demoralise me too much, plus there is enough work to pay for the cat food and the beer. I also suspect most of you are like me – you work with Oracle Technology because you woke up one day and realised that was where you were. Of course, this probably applies to 95% of people in 95% of jobs.

If I was to have the chance to choose my working career again, would I do the same? Would I work with Oracle technology? Well, it is one of the largest technologies around and so it provides a good source of work. It is probably not a bad choice if you are starting out right now. Actually, if I was to play this game again I would probably not be aiming for computer technology at all, I would hope to be brave enough to aim for what I really wanted to do and try to do medicine and become a surgeon {I did not as I feared my woodwork skills were so poor I would end up a GP, which strikes me as a bloody awful job} or stick with the genetics, which I still love. But if it had been IT? Hmmm, I think I would have gone lower level. I wish I knew how hardware really works. But then, how many jobs are there now in low-level firmware?

So I am in the world of Mr Ellison’s RDBMS and happy there. But importantly, I feel independent of Oracle. I can like what I like about the technology and dislike what I don’t and I can say which is which, just to link back to the topic of being aligned to a company. That is a small part of why I went back to being and independent consultant too. I do not feel obligated to support a company as they pay my wage. I suppose I feel obligated to be not-negative about a company that employs my services, but that can be another topic another day.