Maintain multilingual tables ORA-12705 ORA-01858

Akhilesh reported this error today:

While running Maintain multilingual tables from the Maintain Applications Database Entities menu, all
workers failed with below errros:-

ATTENTION: All workers either have failed or are waiting:

FAILED: file RLANLINS.sql on worker 1.
FAILED: file RGNLINS.sql on worker 2.
FAILED: file GLNLINS.sql on worker 3.
FAILED: file ALRNLINS.sql on worker 4.
FAILED: file AXNLINS.sql on worker 5.
FAILED: file XLANLINS.sql on worker 6.
FAILED: file AUNLINS.sql on worker 7.
FAILED: file SSPNLINS.sql on worker 8.
FAILED: file ADNLINS.sql on worker 9.
FAILED: file HXTNLINS.sql on worker 10.

ATTENTION: Please fix the above failed worker(s) so the manager.

Errors in the worker log are simillar as below :-

sqlplus -s APPS/***** @/erppgrp1/erpapp/appl/rla/11.5.0/sql/RLANLINS.sql

PL/SQL procedure successfully
completed.


MESG
--------------------------------------------------------------------------------
LANGUAGE=HUNGARIAN
PACKAGE=
SQLERRM=ORA-12705: Cannot access NLS data files or invalid environment specified


select to_date('ERROR')
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

I opened the sql $HXT_TOP/sql/HXTNLINS.sql to find out which sql was throwing the ORA-12705 error and found that this line was causing the issue:

ALTER SESSION SET NLS_LANGUAGE = 'HUNGARIAN';

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

This was happening from 806 ORACLE_HOME. Seeing this, Akhilesh remembered a similar issue with NLS before and tried to run this command from 10.2.0 ORACLE_HOME logged in as SYSDBA:

ALTER SESSION SET NLS_LANGUAGE = 'HUNGARIAN';

Session Altered

After this when we tried to re-execute in 806 home, it worked there too.

ALTER SESSION SET NLS_LANGUAGE = 'HUNGARIAN';

Session Altered.

We tried exiting the session and tried with new sessions, and it worked fine.

Akhilesh said that he had faced this issue in a different context and the issue would re-occur once we bounce the instance. He said that in the other place, this error stopped after upgrading to 10.2.0.4.

However this seems very strange and we'll revisit this once the instance is booted. I have also taken truss outputs of 806 sqlplus process and 1020 sqlplus process:

10.2.0 truss:

read(0, 0xFFFFFFFF7CDFCA20, 1024) (sleeping...)
read(0, " A L T E R S E S S I".., 1024) = 47
open("/erppgrp1/oracle/10.2.0/nls/data/9idata/lx40011.nlb", O_RDONLY) = 12
read(12, "03\0\0\0\n 03\0\0\0 N a".., 92) = 92
read(12, "\0\0\0\0\0\001 (\0\001 ,".., 19980) = 19973
close(12) = 0
write(10, "01 &\0\006\0\0\0\0\011 i".., 294) = 294
read(11, "02 !\0\006\0\0\0\0\0\b\0".., 2064) = 545
write(1, "\n", 1) = 1
lseek(3, 512, SEEK_SET) = 512
read(3, "019E\0\0\0\0\0\0\0\0\0\0".., 512) = 512
lseek(3, 1024, SEEK_SET) = 1024
read(3, "\016\0 -\0 @\0 R\0 g\07F".., 512) = 512
lseek(3, 3584, SEEK_SET) = 3584
read(3, "\015\0 S\0\0\086\0 T\0\0".., 512) = 512
lseek(3, 512, SEEK_SET) = 512
read(3, "019E\0\0\0\0\0\0\0\0\0\0".., 512) = 512
lseek(3, 1024, SEEK_SET) = 1024
read(3, "\016\0 -\0 @\0 R\0 g\07F".., 512) = 512
lseek(3, 3584, SEEK_SET) = 3584
read(3, "\015\0 S\0\0\086\0 T\0\0".., 512) = 512
write(1, " S e s s i o n a l t e".., 16) = 16
write(1, "\n", 1) = 1
write(1, "\n", 1) = 1
write(1, " S Q L > ", 5) = 5
read(0, 0xFFFFFFFF7CDFCA20, 1024) (sleeping...)
read(0, " e x i t\n", 1024) = 5
write(10, "\0\r\0\006\0\0\0\0\003\t".., 13) = 13
read(11, "\011\0\006\0\0\0\0\0\t\0".., 2064) = 17
lseek(4, 512, SEEK_SET) = 512
read(4, "17A5\0\0\0\0\0\0\0\0\0\0".., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\016\0 *\0 R\0 h\081\09E".., 512) = 512
lseek(4, 4608, SEEK_SET) = 4608
read(4, "\00F\0A0\0\0\0 b\0A1\0\0".., 512) = 512
lseek(4, 512, SEEK_SET) = 512
read(4, "17A5\0\0\0\0\0\0\0\0\0\0".., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\016\0 *\0 R\0 h\081\09E".., 512) = 512
lseek(4, 4608, SEEK_SET) = 4608
read(4, "\00F\0A0\0\0\0 b\0A1\0\0".., 512) = 512
write(1, " D i s c o n n e c t e d".., 95) = 95
write(1, " W i t h t h e P a r".., 52) = 52
write(10, "\0\n\0\006\0\0\0\0 @", 10) = 10
close(10) = 0
close(11) = 0
close(8) = 0
close(6) = 0
close(5) = 0
close(3) = 0
close(4) = 0
_exit(0)


8.0.6 truss:
read(0, 0xFF1F6FA0, 1024) (sleeping...)
read(0, " a l t e r s e s s i o".., 1024) = 44
write(9, "\092\0\006\0\0\0\0\011 i".., 146) = 146
read(9, "01C3\0\006\0\0\0\0\0\b\0".., 2064) = 451
close(3) = 0
close(4) = 0
open("/erppgrp1/erpapp/8.0.6/sqlplus/mesg/sp1hu.msb", O_RDONLY) Err#2 ENOENT
open("/erppgrp1/erpapp/8.0.6/sqlplus/mesg/sp1us.msb", O_RDONLY) = 3
fcntl(3, F_SETFD, 0x00000001) = 0
lseek(3, 0, SEEK_SET) = 0
read(3, "1513 "011303\t\t\0\0\0\0".., 256) = 256
open("/erppgrp1/erpapp/8.0.6/sqlplus/mesg/sp2hu.msb", O_RDONLY) Err#2 ENOENT
open("/erppgrp1/erpapp/8.0.6/sqlplus/mesg/sp2us.msb", O_RDONLY) = 4
fcntl(4, F_SETFD, 0x00000001) = 0
lseek(4, 0, SEEK_SET) = 0
read(4, "1513 "011303\t\t\0\0\0\0".., 256) = 256
write(1, "\n", 1) = 1
lseek(3, 512, SEEK_SET) = 512
read(3, "01 >\0\0\0\0\0\0\0\0\0\0".., 512) = 512
lseek(3, 1024, SEEK_SET) = 1024
read(3, "\016\0 -\0 @\0 R\0 g\07F".., 512) = 512
lseek(3, 3584, SEEK_SET) = 3584
read(3, "\015\0 S\0\0\086\0 T\0\0".., 512) = 512
lseek(3, 512, SEEK_SET) = 512
read(3, "01 >\0\0\0\0\0\0\0\0\0\0".., 512) = 512
lseek(3, 1024, SEEK_SET) = 1024
read(3, "\016\0 -\0 @\0 R\0 g\07F".., 512) = 512
lseek(3, 3584, SEEK_SET) = 3584
read(3, "\015\0 S\0\0\086\0 T\0\0".., 512) = 512
write(1, " S e s s i o n a l t e".., 16) = 16
write(1, "\n", 1) = 1
write(1, "\n", 1) = 1
write(1, " S Q L > ", 5) = 5
read(0, 0xFF1F6FA0, 1024) (sleeping...)
read(0, " e x i t\n", 1024) = 5
write(9, "\01C\0\006\0\0\0\0\011 k".., 28) = 28
read(9, "\0\v\0\006\0\0\0\0\0\t", 2064) = 11
lseek(4, 512, SEEK_SET) = 512
read(4, "02B4\0\0\0\0\0\0\0\0\0\0".., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\013\0 '\0 5\0 M\0 ]\0 l".., 512) = 512
lseek(4, 5632, SEEK_SET) = 5632
read(4, "\00E\091\0\0\0 \\092\0\0".., 512) = 512
lseek(4, 512, SEEK_SET) = 512
read(4, "02B4\0\0\0\0\0\0\0\0\0\0".., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\013\0 '\0 5\0 M\0 ]\0 l".., 512) = 512
lseek(4, 6144, SEEK_SET) = 6144
read(4, "\010\0AB\0\0\0 h\0AC\0\0".., 512) = 512
write(1, " D i s c o n n e c t e d".., 146) = 146
write(1, "\n", 1) = 1
write(9, "\0\n\0\006\0\0\0\0 @", 10) = 10
close(9) = 0
lwp_sigmask(SIG_SETMASK, 0x00000002, 0x00000000) = 0xFFBFFEFF [0x0000FFFF]
sigaction(SIGINT, 0xFFBF93A0, 0xFFBF9440) = 0
lwp_sigmask(SIG_SETMASK, 0x00000000, 0x00000000) = 0xFFBFFEFF [0x0000FFFF]
close(6) = 0
close(5) = 0
close(3) = 0
close(4) = 0
_exit(0)

Will update this post when we learn the root cause.