Tuesday, January 18, 2011

Central TNSNAMES.ORA inside RAC using TNS_ADMIN [PRKP-1001 and CRS-0215 errors]

Is it possible to use a central tnsnames.ora on a shared file systems insite a Real Application Server. The answer to the question is YES. If the centralized tnsnames.ora is not correctly setup, it will produce the following errors [PRKP-1001 and CRS-0215 errors] at startup of the database or instance with SRVCTL. When using SQLPLUS to startup the database instance everything works as designed. Why SRVCTL is not working correctly is that, the TNS_ADMIN is not register into the Oracle Cluster Registry for the current database and instances.

The following steps in this blog will explain to you how you can solved the [PRKP-1001 and CRS-0215 errors] at startup of a database or instances by using SRVCTL statement with a centralized tnsnames.ora.

The first step is adding the TNS_ADMIN environment to the user profile where you have placed the tnsnames.ora file. This environment setting will be used by sqlplus.
export TNS_ADMIN=/oravar/oracle/admin
The TNS_ADMIN environment setting seems not be used by "SRVCTL start -d" statement. Using SRVCTL commando to startup the database or instances will result into an error.
srvctl start database -d JOORDS
PRKP-1001 : Error starting instance JOORDS1 on node joords_1
CRS-0215: Could not start resource 'ora.JOORDS.JOORDS1.inst'.
PRKP-1001 : Error starting instance JOORDS2 on node joords_2
CRS-0215: Could not start resource 'ora.JOORDS.JOORDS2.inst'.
What is now the problem that SRVCTL is not using the TNS_ADMIN environment that we have set into the user profile. Looking into the following logfile "$ORACLE_HOME/log/joords_rac1/racg/imon_JOORDS.log" we see the following information related to the error.
SQL> ORA-00119: invalid specification for system parameter LOCAL_LISTENER

2011-01-17 12:13:00.131: [    RACG][1221908800] [20265][1221908800][ora.JOORDS.JOORDS1.inst]: ORA-00132: syntax error or unresolved network name 'LISTENER_JOORDS_1'
ORA-01078: failure in processing system parameters
SQL> Disconnected
It seems that the system parameter LOCAL_LISTENER can not be solved, and results in a startup failure of the database or instances.

What is causing this problem.
SRVCTL works from the Oracle Cluster Registry information and does not know the TNS_ADMIN environment setting for the database and instances. Solving this problem is adding the TNS_NAMES environment setting to the Oracle Cluster Registry for the database and instances. The SRVCTL setenv statements to add the attribute TNS_ADMIN to the Oracle Cluster Rergistry have only to be executed from one node only.
srvctl getenv database -d JOORDS

srvctl setenv db -d  JOORDS -t TNS_ADMIN=/oravar/oracle/network

srvctl getenv database -d JOORDS
TNS_ADMIN=/oravar/oracle/network

srvctl setenv inst -d JOORDS -i  JOORDS1 -t TNS_ADMIN=/oravar/oracle/network
srvctl setenv inst -d JOORDS -i  JOORDS2 -t TNS_ADMIN=/oravar/oracle/network