ORA-00604: error occurred at recursive SQL level 1

ORA-12705: Cannot access NLS data files or invalid environment specified

You might see this combination of error messages in a situation where an Oracle 12c listener is used to service an Oracle11G database, and static service registration is used in the listener (without specifying the NLS_ORA10 variable) while connection to the database using the NLS_LANG parameter.

DYNAMIC SERVICE REGISTRATION:

First we have a look when ‘dynamic registration is used’:
Local listener is specified in the database:

The service is automatically registered in the listener, as can be seen with the service command, I will only highlight a few variables here:

The environment settings ORACLE_HOME and ORA_NLS10 are set correctly, we will not encounter any issues in this situation.

STATIC SERVICE REGISTRATION:

The local_listener parameter is unset to ensure no dynamic registration exists within the listener for the instance. Some additional required settings are added to the listener.ora to enable the static service registration:

Let’s run our test (we see the error especially when we specify the language ENGLISH in the NLS_LANG parameter):

Test 1:

Just try to connect using the client default settings, no issues:

Test 2:

Try connect using when environment setting NLS_LANG=ENGLISH_AUSTRALIA is set

OOPS seems something is wrong. The ORA-00604 is pointing us to the database server something is wrong there… let’s see…

Of course, the static listener registration is incomplete, we miss the ORA_NLS10 environment setting, lets change it and reload the listener:

Test 3:

Let’s repeat the client test.

Test3 – result – okay…

Now the funny part….lets revert the changes we made in the listener just before we started Test3… So remove the ORA_NLS10 parameter from the static listener configuration and reload the listener.

Test 4:

Let’s repeat the client test.

Okay, so the configuration on client/listener/database server are the same as in Test2, but now we are able to connect with the NLS_LANG setting ENGLISH_AUSTRALIAN

!! Until we restart the database !!

Test 5:

Database is restarted and we repeat the client test….

We are back to the square 1…..and know how to resolve it (add the correct ORA_NLS10 parameter to the listener configuration), but I’d like to check something else.

Let’s start a connection on the database server, with all environment settings in place (eg. ORACLE_HOME and ORA_NLS10 pointing to the oracle 11G location).

Okay, the ‘client connection’ on the database itself went fine, now let’s check client again:

Test 6:

Let’s repeat the client test.

Okay, so the configuration on client/listener/database server are the same as in Test2, but now we are able to connect with the NLS_LANG setting ENGLISH_AUSTRALIAN

Until we restart the database again, so back to square 1…..

The reason why we might see this behavior seems to be caused by Oracle NLS Runtime Library (according to the documentation: “Oracle Database globalization support is implemented with the Oracle NLS Runtime Library (NLSRTL). The NLS RTL provides a comprehensive suite of language-independent functions that perform proper text and character processing and language-convention manipulations. Behavior of these functions for a specific language and territory is governed by a set of locale-specific data that is identified and loaded at runtime.”

Conclusion:
Static listener registration on an Oracle 12C listener with and Oracle11G database REQUIRES the correct ORACLE_HOME and ORA_NLS10 parameters to be specified, otherwise it might result in strange behavior on the client when using ENGLISH in the NLS_LANG client setting !!!

Why specific ENGLISH ?  Not sure about that. The size of the language file seems to be different between Oracle11G and Oracle12C, some other combinations (not using language ENGLISH) seems to work as long as the sizes of the files are the same (although md5sum will tell us the files are different):

Problematic languages found: