Thursday, September 14, 2017

EBS/RAC -- setting TNS_ADMIN for srvctl , SQLNET.sqlnet.allowed_logon* , no matching protocol erros.

This post is actually a weird one :) , but I find it useful.
It is like a mix , as it is about EBS , it is about RAC, it is about SQLNET.sqlnet.allowed_logon* parameters, it is about IFILE settings and  it is about srvctl..
I m writing this one, because I was in the field dealing with the similar issues in almost every EBS-Exadata migrations.
In this post, I won't give all the instructions and definitions releated with the thing that I want to explain. That is, I will suppose that the readers of this post already know the following:
What local listener does, what RAC means, what the "srvctl" utility is , what the sqlnet.ora and the TNS_ADMIN env variable do, and ofcourse what the parameters SQLNET.sqlnet.allowed_logon* are used for.

Let's jump into our topic.
As you may know, we have the autoconfig utility in EBS environments.
This autoconfig utility regenerates some certain db related files, when it is run on the db tier.
Today, I will concantrate on sqlnet.ora.
My followers may recognize this from my earlier posts, but today, I 'm writing about something different, actually.
We know autoconfig regenerates sqlnet.ora in $ORACLE_HOME/network/admin/<context_name> directory and we know that we will lost the things that we manually write there. (after a dbtier autoconfig).
That's why, we know (it is also documented), we need to use IFILES.
So far so good.
What we also know is, in RAC we use the listeners that are running from the GRID home.
This is not a must but it is a recommended thing.
So our local listeners are  running from the GRID homes and we use IFILES in our TNS configuration files stored in GRID Homes and make Oracle to see the actual sqlnet.ora files that are maintained in our EBS RDBMS homes.
At the end of the day, we make Oracle to see what is stored in the IFILE. The actual tns configuration files which are maintained in the RDBMS home, right in the directory:"$ORACLE_HOME/network/admin/<context_name>".
Note that, these files are not regenerated by autoconfig so it is safe.

Well. For sqlnet.ora and other files, this story applies.
However; we need one more thing to do, if we are running our database in a RAC environment.
That is the cluster registration.
I mean we want our TNS_ADMIN directory to point to the GRID Home.
We want the following actually..
TNS_ADMIN -> GRID_HOME/network/admin ->  RDBMS_HOME/context_name/sqlnet.ora -> RDBMS_HOME/context_name/sqlnet_ifile.

In order to get this, we include the IFILE setting in GRID_HOME/sqlnet.ora , which points to the RDBMS_HOME/context_name.
the sqlnet.ora in RDBMS_HOME/context_name has  the IFILE setting for pointing the sqlnet_ifile stored in RDBMS_HOME/context_name. (this comes  by default.)

So far so good.
But at this point, we need to set the TNS_ADMIN right?
We can set it in our terminal using export TNS_ADMIN, but it will not work for srvctl.
In RAC, we mostly use srvctl to start listeners and databases.

So, we execute srvctl setenv to set this TNS_ADMIN environment variable.
This way, we make srvctl utility be aware of our TNS_ADMIN setting.

But for what will we set the TNS_ADMIN env variable? For listener or for database?
At the first glance , we may think that we must set it for the listener, for the local one... for the local one, using a command like srvctl setenv listener -l listener_name -T TNS_ADMIN=$GRID_HOME/network/admin

"However, we must actually set it for db." --Actually this info made me writing this post :)"

This is because; if we set the TNS_ADMIN for listener , then it is overwritten.

Here is the info from Oracle Support:
Ref: Dynamic Registration and TNS_ADMIN (Doc ID 181129.1)

At instance startup, PMON picks up the TNS_ADMIN environment variable (in  the same way that the listener does in Section (a) above). When PMON subsequently registers this instance, this value of TNS_ADMIN is passed to the listener; causing PMON's TNS_ADMIN value to overwrite the value the listener currently has.
If TNS_ADMIN is not set when PMON starts, then after registration, the listener's TNS_ADMIN value is cleared (ie, behaves as if not set).

So, this is for those who are trying to fix the "no matching protocol errors" -> you should make the sqlnet allowed_logon_* settings in the sqlnet_ifile that is stored in RDBMS_HOME/network/admin/context_name and then  set the TNS_ADMIN using a command like "srvctl setenv database -d DB_NAME -T TNS_ADMIN=blabla"... (not the listener)

Well. This is the tip of the day :)  Take care.

No comments :

Post a Comment