Autoconfig issues with DUAL table

Issue:

Users are unable to login to Applications

Environment:

Oracle EBS 11.5.10.2 on Linux 5

Observation:

Below errors are observed in OACoreGroup.0.stderr


[Jul 30, 2010 8:07:22 PM IST]:1280500642725:Thread[Thread-4698,10,main]:-1:-1:shine.apps.com:192.168.1.13:8102:16020:UNEXPECTED:[fnd.framework.OAException]:Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java.sql.SQLException: ORA-01403: no data found
ORA-06512: at line 1
;  (Could not lookup message because there is no database connection)

[Jul 30, 2010 8:07:22 PM IST]:1280500642725:Thread[Thread-4698,10,main]:-1:-1:shine.apps.com:192.168.1.13:8102:16020:UNEXPECTED:[fnd.framework.OAException]:Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java.sql.SQLException: ORA-01403: no data found
ORA-06512: at line 1

;  (Could not lookup message because there is no database connection)

Troubleshooting Process:

Autoconfig is failing with below error:

*******FATAL ERROR*******
PROGRAM : (/v03/oracle/DEV02/dev02comn/admin/install/DEV02_shine/txkGenExtSecConf.pl)
TIME    : Fri Jul 30 20:26:51 2010
FUNCTION: TXK::SQLPLUS::setConnectInfo [ Level 2 ]
MESSAGES:
Argument  must be a scalar

STACK TRACE
TXK::Error::abort('TXK::Error', 'HASH(0x8456578)') called at /v03/oracle/DEV02/dev02appl/au/11.5.0/perl/TXK/Util.pm line 291
TXK::Util::getScalarArg('TXK::Util', 'user', undef) called at /v03/oracle/DEV02/dev02appl/au/11.5.0/perl/TXK/SQLPLUS.pm line 259
TXK::SQLPLUS::setConnectInfo('TXK::SQLPLUS=HASH(0x80c7248)', 'HASH(0x8456584)') called at /v03/oracle/DEV02/dev02comn/admin/install/DEV02_shine/txkGenExtSecConf.pl line 180
eval {...} called at /v03/oracle/DEV02/dev02comn/admin/install/DEV02_shine/txkGenExtSecConf.pl line 64

As there are not many known hits in Oracle support with this error, tried troubleshooting the issue using OS utilities..

Before we start troubleshooting issue, it is important to consistently reproduce it. SO I tried to reproduce the error by executing only the perl file having issues:


/v02/oracle/DEV02/dev02ora/iAS/Apache/perl/bin/perl -I /v02/oracle/DEV02/dev02ora/iAS/Apache/perl/lib/5.00503:/v02/oracle/DEV02/dev02ora/iAS/Apache/perl/lib/site_perl/5.005:/v03/oracle/DEV02/dev02appl/au/11.5.0/perl /v03/oracle/DEV02/dev02comn/admin/install/DEV02_shine/txkGenExtSecConf.pl apps apps

Fortunately the issue reproduced we got the same exception again.

By taking a closer look at this issue, first it looks like a techsktack scripts issue. But this is a fatal error, which often mean that developer of this scripts did not plan that this situation could occur. Often some not so common errors or non-standard errors.

Also this error occurred at below stage:


FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]
MESSAGES:
Cannot find SESSION_ID: in connect buffer

This means, issues is generating from database tier and the session itself is not establishing the way this script expects.

Immediate check to perform would be to ensure that using sqlplus executable we can establish DB connection for standard users like apps, applsys, applsyspub etc.. In our case all these tests were successful.

Next step is to find out which statement in this sqlplus session is failing. We can always generate 10046 trace at DB level and extract the SQL. But it might not be the first choice for two reasons:

1. 10046 trace at DB level would be too lengthy and it is tough to extract exact sql statement quickly..

2. 10046 trace will have no information, if the issue is with in the SQL plus client and the statement causing issue did not reach database yet. ( SQLPlus semantic issues/logical issues etc…)

Next step was to find out what exactly happened before this exception was raised. OS tools like strace, truss are best utilities in these cases. When the seeded/packaged programs do not provide debug information, using these tools will provide complete details. However, output from these commands is often too detailed and will take huge efforts to identify correct source of error. Understanding of OS system calls and linux fundamentals would be handy in using these commands.

I’ve used strace to perform this debugging in this case:

output of this command is trimmed for readability.

strace /v02/oracle/DEV02/dev02ora/iAS/Apache/perl/bin/perl -I /v02/oracle/DEV02/dev02ora/iAS/Apache/perl/lib/5.00503:/v02/oracle/DEV02/dev02ora/iAS/Apache/perl/lib/site_perl/5.005:/v03/oracle/DEV02/dev02appl/au/11.5.0/perl /v03/oracle/DEV02/dev02comn/admin/install/DEV02_shine/txkGenExtSecConf.pl apps apps

execve("/v02/oracle/DEV02/dev02ora/iAS/Apache/perl/bin/perl", ["/v02/oracle/DEV02/dev02ora/iAS/Apache/perl/bin/perl", "-I", "/v02/oracle/DEV02/dev02ora/iAS/Apache/perl/lib/5.00503:/v02/oracle/DEV02/dev02ora/iAS/Apache/perl/lib/site_perl/5.005:/v03/oracle/DEV02/dev02appl/au/11.5.0/perl", "/v03/oracle/DEV02/dev02comn/admin/install/DEV02_shine/txkGenExtSecConf.pl", "apps", "apps"], [/* 363 vars */]) = 0
uname({sys="Linux", node="shine.apps.com", ...}) = 0
brk(0)                                  = 0x80c7000
open("/etc/ld.so.preload", O_RDONLY)    = 3
.
.
.
stat64("/v02/oracle/DEV02/dev02ora/8.0.6/bin/sqlplus", {st_mode=S_IFREG|0751, st_size=3789833, ...}) = 0
pipe([3, 4])                            = 0
fcntl64(3, F_GETFL)                     = 0 (flags O_RDONLY)
fstat64(3, {st_mode=S_IFIFO|0600, st_size=0, ...}) = 0
.
.
.
write(8, "whenever sqlerror exit failure\n", 31) = 31
write(8, "connect apps/apps@DEV02\n", 24) = 24
write(8, "set head off\n", 13)          = 13
write(8, "select \'SESSION_ID:\'||sys_guid()"..., 47) = 47
write(8, "exit success\n", 13)          = 13
fork()                                  = 28017
.
.
.
fstat64(1, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 4), ...}) = 0
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb75e7000
_llseek(1, 0, 0xbfff7324, SEEK_CUR)     = -1 ESPIPE (Illegal seek)
read(9, "Connected.\n\nno rows selected\n\n", 4096) = 30
read(9, "", 4096)                       = 0
close(9)                                = 0
.
.
.
write(1, "*******FATAL ERROR*******\nPROGRA"..., 1024*******FATAL ERROR*******
PROGRAM : (/v03/oracle/DEV02/dev02comn/admin/install/DEV02_shine/txkGenExtSecConf.pl)
TIME    : Fri Jul 30 20:27:22 2010
FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]
MESSAGES:
Cannot find SESSION_ID: in connect buffer

The above output more or less summarizes that one sql stamen executed inside DB has resulted in “no rows” and the process got aborted. SQL statement was  “select \’SESSION_ID:\’||sys_guid()”. This is not complete statement but we can get it from v$sqlarea pretty easily. Also it was confirmed that the DB connection was successful , otherwise error could have been different.

Below query in the database has produced the exact query for us:

SQL> select SQL_TEXT from v$sql where SQL_TEXT like '%SESSION_ID%';

SQL_TEXT
--------------------------------------------------------------------------------
select 'SESSION_ID:'||sys_guid() h1 from dual

When we used this query as apps.. it did not gave any results:

SQL> select 'SESSION_ID:'||sys_guid() h1 from dual;

no rows selected

same query when executed as applsys was successful..

SQL>  select sys_guid() from dual;

SYS_GUID()
--------------------------------
8C9D32F4F3752701E040A8C00D016F7F

It only means two things..

1. sys_guid() function depends on user context for its output or

2. we are not using the same set of objects in both queries.

Below queries revealed the real issue…

SQL> select * from dual;

no rows selected

SQL> select owner,object_name ,object_type,created from dba_objects where object_name like 'DUAL';

OWNER                          OBJECT_NAME                              OBJECT_TYPE        CREATED
------------------------------ ---------------------------------------- ------------------ ---------------
SYS                            DUAL                                     TABLE              27-MAY-04
PUBLIC                         DUAL                                     SYNONYM            27-MAY-04
APPS                           DUAL                                     TABLE              21-JUN-12

Ideally there should only be one dual table in database. Someone here has created Dual table in APPS schema with out any rows and it has resulted in all the issues.

All seeded programs use DUAL table heavily to extract output of functions easily. Unknowingly this will make DUAL table as one of the significant component of their program ,where as not much attention is paid on integrity of this table. This is just one example of what can happen:


SQL> desc dual

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ENAME                                              VARCHAR2(5)

Solution:

Now the solution was pretty straight forward. We have removed the bad DUAL table reran the autoconfig process. Everything went fine without any issues.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s