Howdy,

We have a 4-node 9.2.0.4 RAC on Red Hat Ent. Linux 3 running. We want to
use load balancing for users and a dedicated node for nightly batch
processing. Our users tnsnames.ora looks like this:

USERS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
(LOAD_BALANCE = on)
(FAILOVER = on)
)
(CONNECT_DATA =
(SERVICE_NAME = cluster.domain.de)
(FAILOVER_MODE =
(TYPE = session)
(METHOD = basic)
)
)
)

Since the users connect via the service_name, they will also get
sessions on the last, fourth node which is not even mentioned in the
address list which prevents me from setting things like a really big
sort_area_size for this node.

How can I prevent the users from being connected to the 4th node?

Cheers,
steffen

On Fri, 13 Aug 2004 12:09:04 +0200, Steffen Roegner wrote
(in article <411c9341_1@news.arcor-ip.de>):

> Howdy,
>
> We have a 4-node 9.2.0.4 RAC on Red Hat Ent. Linux 3 running. We want to
> use load balancing for users and a dedicated node for nightly batch
> processing. Our users tnsnames.ora looks like this:
>
> USERS =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
> (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
> (ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
> (LOAD_BALANCE = on)
> (FAILOVER = on)
> )
> (CONNECT_DATA =
> (SERVICE_NAME = cluster.domain.de)
> (FAILOVER_MODE =
> (TYPE = session)
> (METHOD = basic)
> )
> )
> )
>
> Since the users connect via the service_name, they will also get
> sessions on the last, fourth node which is not even mentioned in the
> address list which prevents me from setting things like a really big
> sort_area_size for this node.
>
> How can I prevent the users from being connected to the 4th node?
>
> Cheers,
> steffen

Hi Steffen,
prevent the fourth instance from registrating at the remote listeners should
help.

With kind regards,
Ronald
http://ronr.nl/unix-dba

Steffen Roegner wrote:

> Howdy,
>
> We have a 4-node 9.2.0.4 RAC on Red Hat Ent. Linux 3 running. We want to
> use load balancing for users and a dedicated node for nightly batch
> processing. Our users tnsnames.ora looks like this:
>
> USERS =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
> (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
> (ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
> (LOAD_BALANCE = on)
> (FAILOVER = on)
> )
> (CONNECT_DATA =
> (SERVICE_NAME = cluster.domain.de)
> (FAILOVER_MODE =
> (TYPE = session)
> (METHOD = basic)
> )
> )
> )
>
> Since the users connect via the service_name, they will also get
> sessions on the last, fourth node which is not even mentioned in the
> address list which prevents me from setting things like a really big
> sort_area_size for this node.
>
> How can I prevent the users from being connected to the 4th node?

You can't. You're trying to do a 'spare nodes' configuration when you have
more than 2 nodes, and that's not do-able in a nice and easy way. (In a
2-node RAC, you can set active_instance_count=1 to give yourself an active
node and a spare. You can't set that parameter to 3 however!).

The only thing you can do is to 'hide' the fourth node from the users. That
simply means making sure it hasn't registered itself with any of the three
listeners you mention in the ADDRESS_LIST section of your tnsnames. You
will want to remove entries that fourth instance may have in the spfile
init.ora for REMOTE_LISTENER.

In other words, it all comes down to network configuration.

Regards
HJR

Howard J. Rogers wrote:
> Steffen Roegner wrote:
>
>
>>Howdy,
>>
>>We have a 4-node 9.2.0.4 RAC on Red Hat Ent. Linux 3 running. We want to
>>use load balancing for users and a dedicated node for nightly batch
>>processing. Our users tnsnames.ora looks like this:
>>
>>USERS =
>> (DESCRIPTION =
>> (ADDRESS_LIST =
>> (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
>> (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
>> (ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
>> (LOAD_BALANCE = on)
>> (FAILOVER = on)
>> )
>> (CONNECT_DATA =
>> (SERVICE_NAME = cluster.domain.de)
>> (FAILOVER_MODE =
>> (TYPE = session)
>> (METHOD = basic)
>> )
>> )
>> )
>>
>>Since the users connect via the service_name, they will also get
>>sessions on the last, fourth node which is not even mentioned in the
>>address list which prevents me from setting things like a really big
>>sort_area_size for this node.
>>
>>How can I prevent the users from being connected to the 4th node?
>
>
> You can't. You're trying to do a 'spare nodes' configuration when you have
> more than 2 nodes, and that's not do-able in a nice and easy way. (In a
> 2-node RAC, you can set active_instance_count=1 to give yourself an active
> node and a spare. You can't set that parameter to 3 however!).
>
> The only thing you can do is to 'hide' the fourth node from the users. That
> simply means making sure it hasn't registered itself with any of the three
> listeners you mention in the ADDRESS_LIST section of your tnsnames. You
> will want to remove entries that fourth instance may have in the spfile
> init.ora for REMOTE_LISTENER.
>
> In other words, it all comes down to network configuration.
>
> Regards
> HJR

Thanks, Ronald and Howard for your indeed helpful replies.
In fact, removing the listener entries of all remote listeners
from the tnsnames.ora of the fourth node did the job of preventing
it from registering with these remote listeners - no need to deal
with the REMOTE_LISTENER parameter.

Thanks again!
SR

--
.... Steffen Roegner, Oracle dba
-------------------------------
http://www.sroegner.de, http://www.gagabut.de

Steffen Roegner wrote:

>
> Thanks, Ronald and Howard for your indeed helpful replies.
> In fact, removing the listener entries of all remote listeners
> from the tnsnames.ora of the fourth node did the job of preventing
> it from registering with these remote listeners - no need to deal
> with the REMOTE_LISTENER parameter.
>
> Thanks again!
> SR

It amounts to the same thing, I guess. But you are relying on an error not
causing drama. If, for example, you set LOCAL_LISTENER=ABC, and "ABC" is
not an entry in your tnsnames.ora, then boring old single-instance Oracle
cannot actually start up. It throws an error complaining about the
inability to resolve the service name (which it needs to do to find the
local listener you told it to register with).

Now clearly, Oracle have decided that remote listener registrations,
obviously at the mercy of networking links that might be down, should not
be policed so rigorously.

But that could just have been an oversite on their part, too. And one they
might fix in a future release... at which point, your workaround won't work
any longer!

So whilst your tnsnames.ora method works, it's not "proper", and can't be
considered reliable as a result.

Regards
HJR

Howard J. Rogers wrote:

> Steffen Roegner wrote:
>
>>
>> Thanks, Ronald and Howard for your indeed helpful replies.
>> In fact, removing the listener entries of all remote listeners
>> from the tnsnames.ora of the fourth node did the job of preventing
>> it from registering with these remote listeners - no need to deal
>> with the REMOTE_LISTENER parameter.
>>
>> Thanks again!
>> SR
>
> It amounts to the same thing, I guess. But you are relying on an error not
> causing drama. If, for example, you set LOCAL_LISTENER=ABC, and "ABC" is
> not an entry in your tnsnames.ora, then boring old single-instance Oracle
> cannot actually start up. It throws an error complaining about the
> inability to resolve the service name (which it needs to do to find the
> local listener you told it to register with).
>
> Now clearly, Oracle have decided that remote listener registrations,
> obviously at the mercy of networking links that might be down, should not
> be policed so rigorously.
>
> But that could just have been an oversite on their part, too. And one they
> might fix in a future release... at which point, your workaround won't
> work any longer!
>
> So whilst your tnsnames.ora method works, it's not "proper", and can't be
> considered reliable as a result.
>
> Regards
> HJR


I should learn how to spell oversight, too.

:-)
HJR

Howard J. Rogers wrote:
> It amounts to the same thing, I guess. But you are relying on an
error not
> causing drama. If, for example, you set LOCAL_LISTENER=ABC, and "ABC" is
> not an entry in your tnsnames.ora, then boring old single-instance Oracle
> cannot actually start up. It throws an error complaining about the
> inability to resolve the service name (which it needs to do to find the
> local listener you told it to register with).
>
> Now clearly, Oracle have decided that remote listener registrations,
> obviously at the mercy of networking links that might be down, should not
> be policed so rigorously.
>
> But that could just have been an oversite on their part, too. And one they
> might fix in a future release... at which point, your workaround won't work
> any longer!
>
> So whilst your tnsnames.ora method works, it's not "proper", and can't be
> considered reliable as a result.
>
> Regards
> HJR
>

Howard,

We are talking about one node out of that I want to prevent from
registering with remote listeners here. I have LOCAL_LISTENER set to
sth. like LISTENER_NODE4 as this is the regarding entry in the local
tnsnames.ora. What would be an appropriate entry in REMOTE_LISTENER?
I do not want the instance to remotely register and the value is
supposed to be a 'network_name' so I think it has to remain empty right?

Best regards
Steffen

--
.... Steffen Roegner, Oracle dba
-------------------------------
http://www.sroegner.de, http://www.gagabut.de

Steffen Roegner wrote:

> Howard,
>
> We are talking about one node out of that I want to prevent from
> registering with remote listeners here.

I understand that. It's called a 'spare node configuration', and as I said,
the only way to do it when instances are > 2 is to not set the
remote_listener parameter for the spare node(s) (and also make sure no
client tnsnames.ora files make reference to the spare node(s) directly, of
course).

In other words, you can make a spare node either by feeding it useless and
incorrect information about where, remotely, to register (which is what you
did). Or you can not give it any information about remote locations at all
(which is what I was trying to say was the better way of proceeding).

> I have LOCAL_LISTENER set to
> sth. like LISTENER_NODE4 as this is the regarding entry in the local
> tnsnames.ora.

Local_listener is irrelevant to the matter at hand. I only mentioned it last
time because it gets policed: set it to some value XXX, and if the local
tnsnames.ora cannot resolve XXX into a valid host and port, the instance
cannot start.

Remote_listener, *at the moment*, does NOT work like that: you can (and have
done, if I read your last post correctly) set remote_listener to complete
nonsense. That has the same effect as not setting it at all (because the
spare node doesn't now know how to find the other listeners to register
with), but it is not strictly kosher.

> What would be an appropriate entry in REMOTE_LISTENER?

Null.

> I do not want the instance to remotely register and the value is
> supposed to be a 'network_name' so I think it has to remain empty right?

It's an optional parameter. It's therefore legitimate to leave it null. And
that's the correct entry to make when registration with non-local listeners
is to be stopped.

Regards
HJR

Howard J. Rogers wrote:
> Steffen Roegner wrote:
>
>
>>Howard,
>>
>>We are talking about one node out of that I want to prevent from
>>registering with remote listeners here.
>
>
> I understand that. It's called a 'spare node configuration', and as I said,
> the only way to do it when instances are > 2 is to not set the
> remote_listener parameter for the spare node(s) (and also make sure no
> client tnsnames.ora files make reference to the spare node(s) directly, of
> course).

We have exactly one client making direct reference to this spare node -
this is our batch processing using ORACLE_SID. Would you consider this
to be wrong (although it works for us in our attempt to dedicate the node)?

> In other words, you can make a spare node either by feeding it useless and
> incorrect information about where, remotely, to register (which is what you
> did). Or you can not give it any information about remote locations at all
> (which is what I was trying to say was the better way of proceeding).

Now I get the point (sorry for being that slow). And I did not unveil
all of the configuration information because I had no idea of what was
important to know ...

In the tnsnames.ora we also have this entry

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node4)(PORT = 1521))
)

and as you might know already, 'LISTENERS_RAC' is the value of my
REMOTE_LISTENER Parameter. When I removed the single entries of the
remote listeners, I did the same for this address list on node4, which
now only consists of node4. I think I will reset that value as you
recommended.

> Remote_listener, *at the moment*, does NOT work like that: you can (and have
> done, if I read your last post correctly) set remote_listener to complete
> nonsense. That has the same effect as not setting it at all (because the
> spare node doesn't now know how to find the other listeners to register
> with), but it is not strictly kosher.

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node4)(PORT = 1521))
)

This is what we currently have for REMOTE_LISTENER on node4 - I did not
like that too much either ...

Thank you for waisting time on this
Steffen

> Regards
> HJR


--
.... Steffen Roegner, Oracle dba
-------------------------------
http://www.sroegner.de, http://www.gagabut.de

Steffen Roegner wrote:

> Howard J. Rogers wrote:
>> Steffen Roegner wrote:
>>
>>
>>>Howard,
>>>
>>>We are talking about one node out of that I want to prevent from
>>>registering with remote listeners here.
>>
>>
>> I understand that. It's called a 'spare node configuration', and as I
>> said, the only way to do it when instances are > 2 is to not set the
>> remote_listener parameter for the spare node(s) (and also make sure no
>> client tnsnames.ora files make reference to the spare node(s) directly,
>> of course).
>
> We have exactly one client making direct reference to this spare node -
> this is our batch processing using ORACLE_SID. Would you consider this
> to be wrong (although it works for us in our attempt to dedicate the
> node)?

Absolutely not. A "spare" node doesn't mean a useless piece of metal. It
means a fully-fledged RAC instance ready to do whatever you ask of it...
just so long as it's not used by regular users under ordinary
circumstances. So, your setup is absolutely fine. Having a spare for some
batch processing is *exactly* what this sort of configuration is intended
for.

Everything else in your post looked fine, too.

Best regards
HJR