my recent reads..

Jun 24, 2007 Database

Check LOCAL_LISTENER if you run RAC!

Had a case recently with a 10gR2 RAC install. Everything seemed to have been setup to spec, but we were seeing clients occasionally getting ORA-12545 errors and failing to connect, and things getting even worse during failover testing.

After investigating and solving this, it was painfuly obvious how easy the configuration issue can sneak into a RAC install, which prompts me to blog about it now. Bottom line: if you are installing RAC or are responsible for managing a RAC database, I strongly suggest you swivel over right now and 'show parameter LISTENER'!

So back to our case.... looking at a sqlnet client trace, it was apparent that the client was being redirected to the server hostname, not one of the RAC virtual addresses. Two problems:

  1. the client couldn't resolve the server hostname since it wasn't in DNS or the client hosts file, and
  2. the client shouldn't be connecting to the server hostname anyway!

The client tnsnames.ora and the servers' tnsnames.ora and listener.ora files had all been checked and were setup with only references the the RAC virtual addresses, so where was the reference to the 'physical' hostname coming from? Well the answer is the LOCAL_LISTENER server parameter.

You would think however that if your client connection descriptor (taken from tnsnames.ora for example) only referenced virtual addresses, you would be safe, right? Not the case, and having a solid understanding of how the listener works is critical to knowing why.

The problem stems from the way that in 10g instances automatically register with the listener, and it is very easy to fall into this trap if you haven't paid very close attention to section "9 Understanding the Oracle Real Application Clusters Installed Configuration" in the platform-specific cluster installation guides.

If you have a DEDICATED server config, then the LOCAL_LISTENER parameter is used for the instance registration with the listener. If you are using a default listener on port of 1521, then DBCA will not automatically set the LOCAL_LISTENER. Section "9.8 Configuring the Listener File (listener.ora)" describes how to manually set a correct LOCAL_LISTENER value, but if you haven't done that, it will default to a connection string that refers to the physical host address (not virtual address).

But you might think "connecting to the physcial host address can't be too bad, can it?". Well yes, there are two problems you can see:
  1. Clients may not be able to resolve the host address if you don't have that in DNS, and more importantly
  2. In a failover situation, clients will not follow the virtual IP.

Even then, you might think this would be a very rare problem, because client's tnsnames or other naming is always telling them to connect to the virtual address anyway.

Again, not so. It can be very common for the client to get a connection to the physical host address even if the tnsnames tells them to connect to the virtual address, because of RAC workload management and listener redirects.

Lets take an example of a RAC service called SVC with two instances SVC1 and SVC2 running on host1 and host2 (with virtual addresses host1_vip and host2_vip). The client tnsnames would look something like this:
SVC = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = host1_vip)(PORT = 1521)) 
    (ADDRESS = (PROTOCOL = TCP)(HOST = host2_vip)(PORT = 1521)) 
    (LOAD_BALANCE = yes) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = SVC)
      (FAILOVER_MODE=(TYPE=select)(METHOD=basic)(RETRIES=10)(DELAY=1)) 
    ) 
  )


The diagram shows how a connection is handled through the listener in the case where the LOCAL_LISTENER is not set correctly. The flows go like this:
  1. The client selects a virtual host from the address list and attempts a connection, in this case to the listener on host1_vip.
  2. The listener selects a preferred instance to handle the session for the service SVC. If the local SVC1 instance is down, or if it thinks instance SVC2 is better able to service the request it sends a listener redirect to the client. This redirect will be to the physical hostname (host2)
  3. If the client is not able to resolve "host2", you will see an ORA-12545 at this point. If it can resolve the address, then the client establishes a connection to the listener on the host2 address. If SVC2 is running, you should now have a good connection to the database. However, consider now what happens if host2 fails. CRS will ensure that the host2_vip will shift over to host1, but the client is connected to host2 address and you will get into a TCP timout situation. Maybe your client will eventually detect the dead connection and attempt to reconnect (using VIPs), but at best the user application will have stuttered for a significant period of time (depending on your tcp and sqlnet settings).



Now consider what should happen, with a correctly configured LOCAL_LISTENER.
  1. The client selects a virtual host from the address list and attempts a connection, in this case to the listener on host1_vip.
  2. The listener selects a preferred instance to handle the session for the service SVC. If the local SVC1 instance is down, or if it thinks instance SVC2 is better able to service the request it sends a listener redirect to the client. This redirect will be to the virtual hostname (host2_vip)
  3. We get a good connection established to SCV2 on host2_vip. Again, consider now what happens if host2 fails.
  4. CRS will ensure that the host2_vip will shift over to host1, and the client connection follows this VIP. There will be a slight interruption to communication (depending on how quickly the VIP take-over occurs, and also other factors such if you are connecting through a NAT router, the NATting tables will need to update). Depending on how you have configured FAILOVER_MODE (session, select), you should find that very soon your database connection is alive and you can continue working.


Fortuntely there is an easy fix: update the LOCAL_LISTENER parameter to reference the virtual address.

Oracle have a couple of notes on the issue (342419.1, 333159.1) and how to setup LOCAL_LISTENER. Note however, a the time of writing this (and I'm trying to get it fixed), note 342419.1 does not exactly describe the fix correctly.

It mentions to set the LOCAL_LISTENER using the command like this (where server tnsnames.ora has an instance-connect string using VIP address called 'LISTENER_LXDB0036' ):
Alter system set LOCAL_LISTENER= 'LISTENER_LXDB0036' scope=both;

However, this sets a server parameter that would be picked up on all instances. So there are actually two choices:
  1. change the tnsnames.ora file on each server to have a different, instance-specific definition of 'LISTENER_LXDB0036' , or
  2. I think the preferred way: set a SID-specific parameter by adding the SID parameter to the alter system command (assuming the instance name is SVC1 in this case):
    Alter system set LOCAL_LISTENER= 'LISTENER_LXDB0036' scope=both SID='SVC1';

Note that the listener registration is is only a problem if the database was created with a lister on default port. Haven't seen it myself, but apparently if you use DBCA to create a database with a non-default listener, then the LOCAL_LISTENER entry is explicitly set with correct reference to VIP.

So the message for today. If you are installing RAC or are responsible for managing a RAC database, I strongly suggest you swivel over right now and 'show parameter LISTENER'. Make sure you don't have an HA problem lurking in your system just waiting to bite you and the very worst moment!

Postscript: I submitted a request for change, and note 342419.1 has now been updated to reflect a more correct solution as of 25-Jun-2007.

Postscript 2: This issue is apparently addressed in 11g. Thanks for the report on this karlarao.
Postscript 2b: As Mark Twain would have said, "The reports of a solution are greatly exaggerated" - apparently 11gR2 still does not set a FQDN by default. Time to crack out a test...
read more and comment..

Jun 21, 2007 iX2007

iX2007 day 2 pm - SOA track

Ended up catching the SOA track which was moderated and introduced by Ng Beng Lim from NCS. Oracle, Microsoft and BEA all had a failrly standard pitch with a heavy emphasis on governance (although slightly different ways of modelling the overall journey).


Jurgen Coppens (Accenture) did a refreshingly different approach with a focus on overall transformation (to be expected;) but in particular value realization. Presented some good models, like my sketch to the right, and also advocated a pragmatic approach of focusing on the quick wins in the early stage.

One impression I got from all the speakers however is that they all called for a rational architecture and governance process that is strongly tied to business strategy. In fact this is a fundamental assumption. I have this impression however that for many companies are not really ready to address the question of whether to adopt SOA or not, since they are still have not established the foundation architecture and governance competency. Baby steps.

Anyway that's a wrap for the conference. As I mentioned in my first post, this was the first that had caught my eye for some time, and it lived up to the promise.

read more and comment..

Jun 21, 2007 iX2007

iX2007 day 2 am - some nice provocative points of view

Morning sessions for day 2 of iX2007 have wrapped. Some highlights...

Douglas Merrill, Google, delivered a provocative point of view on the implications on Enterprise IT. Talked of the changing expectations of users and what they will demand and use in the enterprise. Yes its Web 2.0, but also more broadly the impact of an increasingly IT-literate workforce.
For IT, it means embracing new ways of conceiving enterprise solutions. And if SMEs are running their own infrastructure and applications, they are probably wasting their money; they will be exploiting Software as a Service.

Bit disappointed in David Willis' (Gartner) presentation. Did a good job of summarising the trends but nothing earth-shattering.

Louis Broome gave us great insight into the benefits Microsoft are getting run production (podcasts, video) inhouse. Cost-savings are one aspect (75% or so). Amazing to hear that their latest studio only cost US$13k to setup, and their 4.5 headcount are producing close to 1000 hours of video a year (thats with post production, compositing and the like).

Jeremiah Owyang argued that the corporate website is rapidly becoming irrelevant, being sidelined with the rise of 'disruptive' social network tools (from a marketeer's perspective). Go to the corporate website for product specs etc, but if you want buying advice and opinion, you are more likely to trust your peer networks. Corporate employees are in a way contributing to this with the rise of blogging etc, where they blur the corporate boundary with informal communication (bidirectional).

A good morning. Now looking forward to the tracks; looks like I'll hop between SOA and Security, although I'd also like to catch eGovernment!

read more and comment..

Jun 20, 2007 iX2007 Deep thoughts

iX2007 Singapore - day 1 deep thoughts


iX2007 started today here in Singapore. The first conference that really caught my eye for quite some time. Today was keynote day.

iN2015 Update - Optimism in the air?
Interesting to see Mr Chan Yeng Kit (iDA) presenting his update on Singapore's iN2015. When you look at the progress being made in key areas like digital infrastructure upgrade and undergraduate admissions in ICT programmes, I think it reflects accurately the "mood" in the industry, which I'd summarise as "optimistic, with increasing confidence". SARS, dot.bomb and the Asian financial crisis are still recent memories but slowly receeding into history ...
Business Forum - a Truely Interactive Panel Discussion!
The Business Forum ran as a panel discussion involving Pek Chew Yai (SiTF), Lim Chin Hu (Frontline), Craig Gledhill (Cisco) and Phey Teck Moh (Pacific Internet). Robert Chew (Accenture) did a great job as moderator by feeding the discussion with comments coming from the chatroom. I've got to say that this resulted in the most interactive and interesting panel discussion I've ever experienced in this part of the world. Amazing to see the realtime flow from keyboard to chatroom then having a direct impact on the discussion. I think The Digital Movement cashed in on a really good bet by setting up the chat (running 37 Signals Campfire) and providing SMS and free wireless access. We may be too paisei here to step up to a microphone, but in the chatroom the speech runs free and wild!

Great to see the conference assuming a web persona; Jeremiah Owyang has already blogged on his Day 1 thoughts.
Singapore - Producers or Consumers?
All this enthusiasm to use the technologies at hand to enhance the conference experience really underscored for me one of the topics that got a bit of an airing during the panel: are we clear on our aspirations as both consumers and producers? I think so much of the focus in iN2015 is geared towards the consumption ... improving our already-great infrastructure, ensuring we have an ICT-savy workforce. I think we are still struggling with the production aspect, the irony being that there appears to be a huge amount of energy and creativity pent up in Singapore just waiting to be unleashed on the production side. How many of the grads from SMU, NUS, NTU and the like are destined to face the choice of (a) stay in Singapore but end up in a less exciting role than they had hoped for, or (b) head overseas to pursue their dreams? I guess they can take comfort that going overseas is at least a realistic option once again!

Meaning no disrespect to any of the pioneers doing amazing work in Singapore, ICT and Digital Media production is still niche here. And I don't see too many signs this is changing. There seems to be a defacto assumption that its good enough to be a "hub". After all, that is what has made Singapore so successful in logistics, corporate and financial services. But I wonder if that necessarily has to be the case for ICT? Its almost like we are afraid to dream the big dream.

I'll throw a hypothetical out there:
  • Singapore has a skilled workforce, an attractive base for foreign workers, great infrastructure, stable and transparent government (in most things;), cultural ties to India, China and the West, high proficiency in English in addition to many other languages, and lower salary costs than most western countries.
  • So why don't we see, for example, large software companies basing development centres here?
  • Sure, the prevailing view is that if its not in China or India, you must be crazy. But what would be so crazy, for example, of a US company maintaining corporate HQ in the US, development centres in China or India, but running R&D, product management and lead development out of Singapore?
  • Personally I think the net result would be a huge benefit for the company concerned. I think you would also find managing offshore development in India and China from Singapore surprisingly effective.
  • And as a result, all these creative, innovative technopreneurs being groomed in Singapore would find much more opportunity to do big things in their own backyard.


Solutions? Well of course, in true Singaporean style, we could say the gahmen needs to do more to set a bold vision, attract and support ventures of this nature. That means EDB, SiTF and iDA. But I think the real change needs to come from within the ICT community in Singapore (inlcuding those working in the multinationals). Be big, be bold, be best. Believe.
Tomorrow...
Anyway, looking forward to the track sessions tomorrow. My only problem is that I want to be in 3-4 places at the one time. Hard to decide between the 6 tracks: digital media, eGovernment, security, SOA, wireless and eLearning. I think I want to participate in them all ... now is there a technology solution for that?


read more and comment..