I’ve been to more than one RAC customer site and seen several different ways to misconfigure the listeners for a RAC cluster. This post describes how I usually configure the listeners and their associated instance parameters normally. This really has nothing to do with TAF or connection load balancing, those those features may not work as expected unless the underlying configuration is performed properly.

First, some background on how these items work is necessary. The TNS listener (tnslsnr process on *nix) process listens on a specific network address for connection requests to one of the services from one of the database instances that it services. When requested, it either spawns a server process (dedicated server environment) and connects the user to that process or forwards the connection request to a dispatcher (shared server environment) for service to the database service requested. Alternatively, if the listener knows of more than one instance providing the requested service, it may direct the client to an alternate listener (usually on a different node) that will service the request.

In any Oracle database configuration, listeners define the instances as local or remote (in single-instance environments, normally everything is local). You can see this behavior when examining the “lsnrctl services <listener_name>” output (lsnrctl syntax reference here). A listener’s services are those services that have been registered with it by instances. A listener will accept registration from any instance (this may be a weak point of security, but that’s another topic) and listeners have no outbound communication with any other entity in the Oracle environment (or beyond).

The remote_listener parameter specifies a list of listening endpoints that the local instance should contact to register its services. This list is usually defined in a TNS entry in the tnsnames.ora file and then the TNS alias set as the value of the remote_listener parameter. Here’s a sample of what that entry might look like:

LISTENERS_CLUSTERNAME=
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=node3-vip)(PORT=1521))
  )

The local_listener parameter is sometimes confusing. It defines where to connect to the local instance, but its most important function is related to remote listeners. The contents of the local_listener parameter are passed along to the remote listeners during remote registration so that when those remote listeners wish to refer a connection request to the local instance, they refer the client (requestor) to the proper listening endpoint so it can get connected. The local_listener should contain the ADDRESS section of the TNS entry and the HOST portion should reference the VIP address, like this: (ADDRESS=(PROTOCOL=TCP)(HOST=node3-vip)(PORT=1521))

To properly (manually) configure listeners in a RAC environment, I follow the steps like the ones below. Note that in most cases, the Oracle Net Configuration Assistant (netca) will do this for you as part of the database creation process.

  1. Create individual listener.ora files for each listener. Make sure that the HOST= lines in the listener.ora definition reference the VIP addresses (and only the VIP address). I prefer to specify IP addresses instead of hostnames or DNS names here to avoid possible lookup issues and/or confusion.
  2. Create a TNS entry (on each node) that looks like the one below to specify a single TNS entry that references all the listeners in the cluster. Note that the HOST= parts reference the VIP addresses of each node (I used names instead of IP addresses here to avoid reader confusion–I’d put in IP addresses in the HOST= attributes when using this for a real configuration.).
    LISTENERS_CLUSTERNAME =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
      )
  3. Set the remote_listener parameter in the instances (a global parameter, not an instance-specific parameter) to be the name of the TNS entry you created in the previous step. This is done with “alter system set remote_listener = 'LISTENERS_CLUSTERNAME';
  4. Set the local_listener parameter to be the ADDRESS string for the local instance. This parameter must be an instance-specific parameter with each instance having a similar, but unique value since each instance runs on a different HOST. If the local instance (called inst1 in the example here) runs on a node with the node VIP of 10.3.121.54, then set the local_listener parameter accordingly for each instance (it is instance-specific, so use the sid= syntax): alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=10.3.121.54)(PORT=1521))' sid='inst1';
  5. On each instance, you can run “alter system register;” to force immediate registration with the listeners. If you don’t do this, the listener registration will usually be updated within a minute or two anyway (automatically), but this command can help shorten debugging cycles when necessary.

One final tip is to use the lsnrctl program to get information about the current state of the listener. I find it helpful sometimes to get verbose information about the services and here’s how I do that. In this example, the listener name is LISTENER_NODE1 (listeners usually get named as LISTENER_<hostname>).

LSNRCTL> set curr listener_node1
Current Listener is listener_node1
LSNRCTL> set display verbose
Service display mode is VERBOSE
LSNRCTL> servic
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521)(IP=FIRST)))
Services Summary...
Service "myservice.world" has 2 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:blocked
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.dannorris.net)(PORT=1521))
  Instance "rac2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.dannorris.net)(PORT=1521))
Service "rac.world" has 2 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:blocked
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.dannorris.net)(PORT=1521))
  Instance "rac2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.dannorris.net)(PORT=1521))
Service "racXDB.world" has 2 instance(s).
  Instance "rac1", status READY, has 0 handler(s) for this service...
  Instance "rac2", status READY, has 0 handler(s) for this service...
Service "rac_XPT.world" has 2 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:blocked
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.dannorris.net)(PORT=1521))
  Instance "rac2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.dannorris.net)(PORT=1521))
The command completed successfully

If you have questions or if you’ve found alternate ways to configure listeners for RAC that works differently, please comment below so we can all learn some new things.

  • dougk

    Have you seen Metalink note 403743.1? “VIP failover will take a long time if IPC addresss is not he first address in the listener.ora.” This seems to be an issue under Solaris 64 when the public interfaces are configured with IPMP, as we have done in our RAC implementation.

  • http://www.dannorris.com/ Dan Norris

    @dougk: I hadn't encountered that personally as I haven't done a 64-bit
    RAC implementation on Solaris and the note appears to be a
    platform-specific issue. Thanks for the tip. Would you always recommend
    that there be an IPC listening endpoint that's the first one in the
    listener.ora then?

  • dougk

    Dan, I was hoping that you would have some experience with this. Our 3-node RAC is scheduled to go into production in three weeks time. I discovered the Metalink article and made the configuration change yesterday. The original Oracle generated listener definitions didn't have an IPC endpoint defined, so I added one. Of course the next step is testing, which won't happen until some time next week. To complicate matters we are also running Veritas SFRAC.

    If all goes well, we won't experience any 10 minute delays in fail-over as were seen during the last test cycle. That round of testing was done prior to configuring redundant network interfaces (public (IPMP) and private (Vcs PrivNIC)).

  • http://www.dannorris.com/ Dan Norris

    @dougk Sorry–I haven't had the opportunity to come face-to-face with
    that issue yet. I am interested in how your testing goes and if the
    workaround you found will address and resolve your issue. If you have
    time and don't mind, please let me know, either via email or a follow up
    posting here, how things went. Others finding this posting may want to
    see if the issue was finally resolved with the workaround you mentioned.
    Thanks!

  • SwatiDesai

    Hi,

    Really useful tips…and specifally step5 for the forcefully registration….

    But I have one issue on RAC setup that is I am not able to connect my RAC DAtabase and getting error like : ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor

    I used to connect with SQL* prompt like this :
    sqlplus “sys/sys123@newracdb as sysdba”

    I am just able to connect one time after that whenever I am trying to connect with DATABASE always getting this error .

    please let me know the possible causes…. I already try wtih ur tips even…

    Thanks…

  • http://www.dannorris.com/ Dan Norris

    @SwatiDesai This error is likely the result of a misconfigured
    local_listener or remote_listener parameter for one of the instances.
    You should double-check your configuration and verify that “lsnrctl
    services <listener_name>” shows the expected information (namely, a
    service with the proper list of instances below it).

    Hope that helps.

  • dougk

    After testing, I can confirm that adding the IPC entry to listener.ora was a good fix for our configuration (IPMP on the public interface). It reducted the fail-over wait time from 3.5 minutes to just under 30 seconds.

  • dougk

    Hi Dan,

    I completed the fail-over testing. Adding the IPC entry in listener.ora reduced fail-over time from 3.5 minutes to 30 seconds when both redundant public interface cables are disconnected. When the node fails-over, the vip moves to another node and the listener goes offline.

    When the public interfaces cables are reconnected, there is no fail-back. To get the vip to move back, the listener has to be restarted manually. It works this way both with and without the IPC entry in listener.ora.

    From your experience, is this normal behavior?

    /Doug

  • http://www.dannorris.com/ Dan Norris

    Most of the time, VIP failover happens when a node is down (i.e.
    rebooted or crashed), so the restarting you found as required happens
    naturally. It's been more than a year since I did the test you're
    describing, but I think that's the same outcome I found (on Linux). The
    VIP should relocate back to the proper member when service is restored,
    but the listener resource will likely need to be restarted manually to
    restore service.

  • Pinky

    Thanks for the informative post. Its great!

    However, I have a few dumb questions. And I would greatly appreciate if you could answer them.

    Background:
    In my 2-node RAC cluster, I see that someone (before me) had setup the listener with two hosts information as below:

    LISTENER_node1 =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521)(IP = FIRST))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.100)(PORT = 1521)(IP = FIRST))
    )
    )

    …in which the first entry is of VIP address from node1, and the second IP address is a Public IP for node1.
    Same goes to node2 with values; node2 and Public IP to 192.168.10.101

    Questions:
    1- First of all, what's the reason for inconsistency between teh two addresses, as one being hostname and the second an IP address? Any specific reason?

    2- Why would someone setup the listener pointing to both addresses on node1? Should it be 1 for node1 and second for node2?

    3- What does 'FIRST' mean at the end of each line?

    4- What is IPC protocol used for in listener and what is EXTPROC1 key?

    5- When I run the command “lsnrctl status” it shows me 2 handlers for a service on node1, and 1 handler on node2, and vice-versa on node2. Why do I see two handlers? Is it a discrepency?

    6- For some of my databases the service names are one word, and for some DBs, they show up as e.g. abcd.xyz.org. Why? How can I simply rename them to one word instead of something like a domain name?

    7- According to the discussion between you and 'dougk', should it be VIP or public IP that a listener should use?

    I would greatly greatly appreciate if you could answer these questions.

    Warmest Regards,
    Pinky

  • http://www.dannorris.com/ Dan Norris

    @Pinky: Great questions. Here are some responses:
    1 – No reason is apparent. Probably was created by a wizard or assistant somewhere. I prefer IP addresses personally as it eliminates one more layer of lookups and possible errors.
    2 – I think this may be the default NetCA setup. As mentioned, it is not my preferred configuration.
    3 – IP=FIRST is a poorly documented feature. See Metalink 300729.1. Basically, the listener does stupid things by default and IP=FIRST makes it do what it should have done in the first place :).
    4 – This listen endpoint is to support external procedures (judging by the name). If you don't use external procedures, you can remove this listening endpoint from the configuration as well.
    5 – To be certain, this is probably due to the way the local_listener and remote_listener parameters are configured. However, I don't think it's a problem. Most likely, only one of the handlers will be used normally (you should see 0 connections made to one of the two handlers on each node).
    6 – Check the domain_name parameter in the init (or sp) file. Those without domain names will show up as “one word” (without domain name) and those that have domain_name set will show up as you mentioned above.
    7 – Always VIP, never public IP. In his case, he had to have both VIP and an IPC listening endpoint (similar to the IPC endpoint you have shown above) in order to work around some platform-specific issue, but that's not normally the case on other platforms.

    Thanks for reading. Hope these help!
    Dan

  • Pinky

    Thank you very much for your input. I am grateful.

    However, I still have doubts about a few things that I orginally asked.

    1- So you are saying that other than specifying the addresses instead of hostnames they should be VIPs, unless I am using EXTPROC key in my listener file, and point all “HOST =” to VIPs of all nodes of cluster? What if I still use VIPs while having EXTPROC key set? How would I know whether I really need EXTPROC or not? If I remove EXTPROC from listener, where else do I have to propogate the changes?

    3- In my case, you would see that both addresses are declared as IP=FIRST. Isn't that ambiguous?

    4- I could not fully understand the point number 4. Could you please explin again?

    5- Let me paste exact results so you see what I was saying.

    Node1
    =====
    Service “prod_x.nipa.org” has 2 instance(s).
    Instance “prod1″, status READY, has 2 handler(s) for this service…
    Instance “prod2″, status READY, has 1 handler(s) for this service…

    Service “prod_x.nipa.org” has 2 instance(s).
    Instance “prod1″, status READY, has 1 handler(s) for this service…
    Instance “prod2″, status READY, has 2 handler(s) for this service…

    …Now referrig to what you were saying that even with two handlers, it should have only one connection (if I understood it right), how can I see how many connections a handler has?

    6- I tried to look for domain_name in parameter file, but couldn't find it, however, I found “service_names” which shows the two services supported by that instance. Where can I find the properties setup of the service names?

    Thank you very much for your help. I loved the way you answered my question in the previous reply. I woul dbe waiting for your replied again impatiently.

    Bundle of thanks!

    Pinky

  • http://www.dannorris.com/ Dan Norris

    1 – All HOST= parts of the listener.ora should reference VIPs regardless of the status of extproc support or other endpoints. See #4 below for external procedures info.
    2 – You didn't have a #2 :)
    3 – No. Per the ML note I cited, you'd have to specify IP=FIRST for each ADDRESS section.
    4 – EXTPROC IPC listening endpoints are for external procedures in the database. If you don't use them, you can remove this listening endpoint from the listener. There aren't many good ways to figure out if you use them. You could check DBA_LIBRARIES to see if there are libraries defined by you/application. There are usually some default libraries defined at installation time by Oracle. You could also remove it from the listener in your test environment and test the application to see if it continues to function correctly if you really aren't sure.
    5 – Sorry, I was thinking of the “lsnrctl services” output. Run that command and you'll probably see that connections are being made to one of the services and not the other.
    6 – I'm guessing that service_names includes “prod_x.nipa.org”. That's fine too. However, you could set domain_name = nipa.org and then set service_names to just “prod_x” and you'd get the same result. Not sure what properties you're looking for, but you should check out DBA_SERVICES in the database for information about the service characteristics (failover, load balancing goals, etc.) if you're on 10g or higher.

  • Pinky

    Dan you are wonderful. Thanks a lot.

    True that I didn't have #2, because you answered it before :-)

    About #6, its very wierd that I have two DBs on a cluster, and for one DB the db_domain parameter is specified as nipa.org and for that I don't have to include the domain name in the service name, but for 2nd DB on the same cluster, there is no db_domain parameter specified, and so I have to use that in service name.

    Questions:
    —————

    1- How come such in consistency allowed by the system even though both DBs exist on same two-node cluster, that one has a db_domain and the other doesn't? Another funny thing is that, on my dev environment, both DBs have db_domain set, and this causes a problem. (Please see at the end of this post)

    2- what if I take the db_domain information out from the parameter file?
    a) Would my DB crash?
    b) What elements need to be restarted after the change?

    Problem as mentioned in #1
    —————————————
    When I create a DB_link on the DB that has the db_domain parameter set, the db_link name automatically adds up the name of the domain. e.g. if i specify the name of link as “My_DB_Link”, it would make it as “My_DB_Link.nipa.org”. And because of this, one of my object (view) that uses it, becoems invalid. Although I tried to change the name of the automatically updated db_link in the view creation script, but it still doesn't work.

    Could you help me with that.

    Also, please tell me if this is the right forum to discuss that issue as its not related to TNS.

    Thanks again…
    Pinky

  • http://www.dannorris.com/ Dan Norris

    1 – The db_domain and service_names parameters can be different on different instances. In fact, service_names will very often be different for workload management reasons. However, db_domain is customarily the same. I think it is wise to standardize all your configurations and you should definitely use similar configs on dev, test, and prod for obvious reasons.
    2 – db_domain and service_names are both able to be modified on the fly. In RAC, you shouldn't modify service_names directly, but rather modify the services via the srvctl utility and modify their attributes via dbms_service calls. To see immediate effects of your changes, use “alter system register;” to force the smon process to re-register with the listener(s) immediately. It'd be very wise to make your test or dev env look like prod does right now, then practice the changes there before going to prod.

    I don't think I understand the dblink problem. If the problem is that the view refers to the wrong dblink name, then modify the view. You should be able to validate the dblink with “select username from user_users@dblink.nipa.org;”

    This isn't a forum…we're already stretching the real spirit of the “comments” on this blog entry. You should consult the OTN forums (forums.oracle.com) for detailed support issues like this one or join the Oracle-L list on freelists.org.

    Good luck!

  • Pinky

    Thanks for the good sites Dan,
    Could you tell by seeing the lsnrctl status results below that why prod_x.nipa.org has only one instance, whereas prod_y has two instances?

    Service “prod_x.nipa.org” has 1 instance(s).
    Instance “prodx1″, status READY, has 1 handler(s) for this service…

    Service “prod_y” has 2 instance(s).
    Instance “prody1″, status READY, has 2 handler(s) for this service…
    Instance “prody2″, status READY, has 1 handler(s) for this service…

    prod_x.nipa.org also used to have two instances, but after a restart yesterday, the other has gone…

    TIA
    Pinky

  • http://www.dannorris.com/ Dan Norris

    This is most likely due to either a) the configuration for the prod_x
    service (it isn't running on instance 2) or b) the configuration of the
    remote_listener parameter on the instances. You should review the
    whitepaper on services from Jeremy Schneider at
    http://www.ardentperf.com/pub/services-schneide… for more information.

    Dan

  • Fred

    Finally a usefull artical about listener configuration in rac environment.
    Anybody out there however who has experiance in setting it up in a environment with two different networks that are not connected.
    I use the VIPs on the first network but real host IPs on the second. On client side if the client is only on one network i use the appropriate addresses, if its on both however i use all 4 addresses. I think i saw some article somewhere that explains how to set up additional VIP addresses that i could use on the second network, if one network fails completely though i would still have the long wait, so the gain would only be for the one scenario not all. More important : How does the listener redirects the client to a listener on another node if it thinks thats better, i suppose it tells the client leave me alone and connect there, but if there in this case is the other network which is not available on this particular client, the connection would fail, correct ?

  • http://www.dannorris.com/ Dan Norris

    When the listener wishes to redirect a client to a different instance
    for connecting, the listener uses the information provided in the
    local_listener parameter that was sent by that remote instance when it
    registered.

    For example, if you have a two node cluster (nodeA and nodeB) with
    instances rac1 and rac2 on them, then rac2 will register with the
    listener on nodeA as well as the listener on its node (nodeB). When that
    registration happens, the rac2 instance will look up the remote_listener
    parameter to find out which listeners it has to register with. During
    those registrations, it will send the information in its local_listener
    parameter to the listeners with which it registers and it is the
    local_listener information held by the listeners that will be used to
    instruct clients where they have to go for redirection.

    In short, the local_listener isn't just for local connections–it is
    also used for telling remote clients where to find the local instance.

    Hope that helps.

  • Bashir Ahmad

    Do we must need to configure local_listner parameter, I know if listner is default, instance can register itself with listner without local_listner parameter, Does it work differently in RAC? or we can't use default listner in RAC. Please help me in this regard.
    Thanks

  • http://www.dannorris.com/ Dan Norris

    By default, the local_listener parameter will be set to the local host's primary IP address (the one that corresponds to the hostname in the hosts file). That's not desirable because if users are directed to that IP address, they won't be able to connect to the database because the listener *only* listens on the VIP, not on the default IP of the host. Any connections to IP addresses that are NOT the VIP address will result in a failure to connect. So, yes, the local_listener *must* be set if you follow my configuration guidelines above.

  • André Blanchard

    Hi,

    I have a strange behavior with my listener on my rac system.

    I have a two node RAC. When I stop the listener on node two and do a lsnrctl service listener_name on node one I get the following :

    Service “virlab” has 2 instance(s).
    Instance “virla1″, status READY, has 4 handler(s) for this service…
    Handler(s):
    “D001″ established:0 refused:0 current:0 max:972 state:ready
    DISPATCHER
    (ADDRESS=(PROTOCOL=tcp)(HOST=tau.dgeq.qc.ca)(PORT=50406))
    “D000″ established:0 refused:0 current:0 max:972 state:ready
    DISPATCHER
    (ADDRESS=(PROTOCOL=tcp)(HOST=tau.dgeq.qc.ca)(PORT=50405))
    “DEDICATED” established:0 refused:0 state:ready
    LOCAL SERVER
    “DEDICATED” established:0 refused:0 state:ready
    REMOTE SERVER
    (address=(protocol=tcp)(host=10.53.228.124)(port=1521))
    Instance “virla2″, status READY, has 3 handler(s) for this service…
    Handler(s):
    “D001″ established:0 refused:0 current:0 max:972 state:ready
    DISPATCHER
    (ADDRESS=(PROTOCOL=tcp)(HOST=psy.dgeq.qc.ca)(PORT=34066))
    “D000″ established:0 refused:0 current:0 max:972 state:ready
    DISPATCHER
    (ADDRESS=(PROTOCOL=tcp)(HOST=psy.dgeq.qc.ca)(PORT=34065))
    “DEDICATED” established:0 refused:0 state:blocked
    REMOTE SERVER
    (address=(protocol=tcp)(host=10.53.228.102)(port=1521))

    As you can see the dedicated handle for instance virla2 is blocked?

    My problem is that I have a system that required (arcsde from esri) that my connection be dedicated so I have the following entry in the tnsnames.ora file :

    geo =
    (DESCRIPTION =
    (address_list =
    (ADDRESS = (PROTOCOL = TCP)(HOST = psy.dgeq.qc.ca)(PORT = 1521))
    )
    (CONNECT_DATA =
    (instance_NAME = virla1)
    (service_name = virlab)
    (server = dedicated)
    )
    )

    So I get an ora-12520.

    Did you encounter a similar situation?

    André

  • Anupam Khaund

    How to Configure a Listener in a local database that connects to a Remote RAC Database

  • http://www.dannorris.com/ Dan Norris

    I don't understand what you're asking. Clients initiate connections to remote servers, not listeners. Listeners may redirect a client to another listener on a remote server, but listener on server1 will never actually connect to an instance on server2. In a cluster, if I make a connection request to the listener on server1 and the listeners have metrics indicating that I should connect to instance2 on server2, then instance1 will direct me to the listener on server2 where my client will request the same service and be granted connection.

    Hope that helps.

  • Pingback: Log Buffer #107: A Carnival of the Vanities for DBAs

  • rvk

    On production env we are running our 10.1.5 on linux 64bit.with 5 node RAC.

    When i check out with lsnrctl> status —– no listner message i got.

    but we are able to connect to db remotely

    thanks

  • http://www.dannorris.com/ Dan Norris

    You need to include the listener name. So, it should be “status
    LISTENER_NODENAME” if you follow the standard conventions used by NetCA
    during a typical install.

    Dan

  • vnkt

    Hi I have a situation where i need to develop a monitoring solution for checking the availability of oracle database instances on a rac system.
    I need to alert not only when the overall rac system is unavailable but also when one of the instances on a node is not available while overall the rac system is available(thro other isntances).
    I already have developed a monitoring solution for single instance oracle database where the script connects to an oracle instance and if unable to then raises alert. Can i have a similar approach for rac — where in i can specify the overall sid of the rac to check the overall avaialability and individual sid of each instance to check the individual availability ?

  • http://www.dannorris.com/ Dan Norris

    A few ideas come to mind. To answer your question, you could use a tns
    entry that references all instances/servers to check overall
    availability and also separately check each individual instance to
    determine availability.

    You might also consider using clusterware callouts to alert you.
    Scripts placed in ora_crs_home/racg/usrco will be run whenever
    clusterware detects a resource offline. That may be easier than
    writing your own custom polling method.

    Please excuse my typos, I sent this from my iPhone.

  • Roberto Pinel

    Hi, Good article, I have Oracle 11.1.0.7 with Rhel 5, 3 nodes, but I have a problem, I got the tns-12520 3 times a day, no one cant connect and I have to restar the database, The parameter process is 800 and I have the local_listener with the same address from tnsnames.ora in each server, the workaround that I use was to change the local_listener valur for the remote_listener value, and work fine, no more connection problems, but I know that is not correct, have you seen this problem before.

    Thanks

  • Roberto Pinel

    Hi, Good article, I have Oracle 11.1.0.7 with Rhel 5, 3 nodes, but I have a problem, I got the tns-12520 3 times a day, no one cant connect and I have to restar the database, The parameter process is 800 and I have the local_listener with the same address from tnsnames.ora in each server, the workaround that I use was to change the local_listener valur for the remote_listener value, and work fine, no more connection problems, but I know that is not correct, have you seen this problem before.

    Thanks

  • johannabartley

    Thank you for the code. It saved me a lot of time in front of the computer. Johanna

  • Jake Ramirez

    Hey Dan. This is Jake Ramirez in Vancouver WA. Wanted to know if I could call you about Cluster Listener config in 11gR2. Also need to talk to you about one more thing :)

  • http://www.dannorris.com/ Dan Norris

    Let’s keep the Q&A on listener issues here for all to benefit.

    Shoot me a mail on whatever else you need – my schedule is tight for the
    next 2 weeks, so a call is probably less likely to get answered than an
    email.

  • Steve

    Hi Dan, Nice post!

    Have couple of questions in reading your post. Most of our implementations at customer sites are done with DB in cold failover mode where we have DB vlan which only App nodes can talk to and OAM vlan or control vlan where back office application can only talk to. In these cases, we generally have the listener endpoints to listen on both DB VIP and OAM VIP and they fail over to other node in the cluster, if primary node fails for some reason. All works great. However, if we had to support RAC with similar support i.e having back office apps connect to DB via control vlan only, what would you recommended setup?

    And how does this work with SCAN listeners on RAC?

    Cold failover protected by 11gr2 Oracle Clusterware is even worse cause during failover, we fail just the DB and not SCANs or restart local listeners. In a typical cold failover solution, we failover db,listener and storage resources along with it, but with clusterware, we have asm clustered but DB running in cold failover mode.

    Appreciate your feedback.

  • http://www.dannorris.com/ Dan Norris

    For 11gR2, all the stuff above is automatically configured when the DB is started. No manual configuration needed for any of it. However, some of the status commands are helpful for troubleshooting and debugging.

    There’s exactly one SCAN allowed, so if your database server has more than one interface used for client connections, one can use the SCAN, the other will have to use the pre-11.2 method that lists all the VIPs (which you’ll have/want to create too).

    I have no experience with the cold failover, but if you use RAC One, it is mostly automated.

  • Andrew Kerber

    Admittedly this is rather late, but you should be sure and use the fqdn when using a name rather than ip for the remote and local listeners.  There is an oracle bug referencing this.  What it amounts to is that if you dont use the fqdn for the local listener, when you connect to the database from a different default domain, the oracle client will have trouble connecting if the local listener setting doesnt include an fqdn.