Notes from the field: HTTP-503 ORA-604

An OLTP clone had the following issues:

adstrtal.sh and adstpall.sh while calling adapcctl.sh, adfrmctl.sh, adfmcctl.sh and adfmsctl.sh gave this error:

Cannot complete applications logon. You may have entered an invalid
applications password, or there may have been a database connect error.

PL/SQL ping option in System Administration responsibility failed with:

Service Temporarily Unavailable

The server is temporarily unable to service your request due to maintenance
downtime or capacity problems. Please try again later.

Forms interface did not launch through Self Service page, and generated this error in error_log_pls file present in $IAS_ORACLE_HOME/Apache/Apache/logs


[Thu Dec 14 01:05:25 2006] [error] mod_plsql:

/pls/$ORACLE_SID/fnd_icx_launch.launch HTTP-503 ORA-604

[Thu Dec 14 01:15:42 2006] [error] mod_plsql: /pls/$ORACLE_SID/OracleSSWA.Execute

HTTP-503 ORA-604

[Thu Dec 14 01:16:58 2006] [error] mod_plsql: /pls/$ORACLE_SID/OracleSSWA.Execute

HTTP-503 ORA-604


http://:/dev60cgi/f60cgi worked without issues. All services were up without issues.


Solution:

Following event was put in the init.ora file to trap ORA-604 error:

event = "604 trace name errorstack: 10046 trace name context forever, level 12"

From the trace, it was discovered that on SYS user a trigger of type 'AFTER EVENT' was created on triggering event: LOGON. The name of the trigger is ‘VALIDATE_INCOMING_SESSION’. This trigger checks for the incoming session’s osusername and matches with a custom table customschema.custom_nouser. If the osusername is not present in customschema.custom_nouser table’s NO_OSUSER column, it disallows access by raise_application_error(-20000,'You are not an authorized user of this database.');



SQL> desc customschema.custom_nouser

Name Null? Type
----------------------------------------- -------- ----------------------------
NO_OSUSER VARCHAR2(15)
YES_NO VARCHAR2(1)
USER_TYPE VARCHAR2(15)



Source of the trigger is given below

1 TRIGGER SYS.validate_incoming_session
2 AFTER LOGON ON DATABASE
3 DECLARE
4 v_username VARCHAR2 (30);
5 v_sid NUMBER;
6 v_serial NUMBER;
7 cursor1 INTEGER;
8 vdate DATE;
9 vsid NUMBER;
10 vosuser VARCHAR2 (30);
11 vusername VARCHAR2 (30);
12 vstatus VARCHAR2 (20);
13 vprogram VARCHAR2 (50);
14 v_terminal VARCHAR2 (30);
15 status INTEGER;
16 v_tmp NUMBER;
17 v_count NUMBER;
18 BEGIN
19 SELECT s.SID, s.terminal
20 INTO v_sid, v_terminal
21 FROM SYS.v_$session s, SYS.v_$process p
22 WHERE s.SID = (SELECT SID
23 FROM SYS.v_$mystat
24 WHERE ROWNUM = 1)
25 AND p.addr = s.paddr;
26
27 SELECT COUNT (*)
28 INTO v_tmp
29 FROM SYS.v_$session vs
30 WHERE vs.username IN ('APPLSYSPUB', 'APPS')
31 AND vs.terminal = v_terminal;
32
33 IF (v_tmp = 0)
34 THEN
35 SELECT COUNT (*)
36 INTO v_count
37 FROM (SELECT 1
38 FROM customschema.custom_nouser gn, SYS.v_$session vs
39 WHERE vs.SID = v_sid
40 AND vs.username IN ('APPS')
41 AND UPPER (vs.osuser) = gn.no_osuser(+)
42 AND NVL (gn.yes_no, 'N') = 'N'
43 AND vs.program NOT LIKE 'ADI%'
44 AND vs.program NOT LIKE 'GLDI%'
45 /*
46 AND ( program LIKE '%SQL%'
47 OR program LIKE '%sql%'
48 OR vs.program LIKE '%TOAD%'
49 OR vs.program LIKE '%toad%'
50 OR vs.program = NULL
51 OR vs.program LIKE '%PLUS%'
52 )
53 */
54 AND status <> 'KILLED'
55 UNION
56 SELECT 1
57 FROM customschema.custom_nouser cs, SYS.v_$session vs
58 WHERE vs.SID = v_sid
59 AND vs.username IN ('APPS')
60 AND UPPER (vs.osuser) = UPPER (cs.no_osuser)
61 AND cs.yes_no = 'N'
62 AND status <> 'KILLED');
63
64 IF (v_count > 0)
65 THEN
66 --insert into customschema.killed_sessions (sid,theosuser,theuser,thedate,theprog,thestat)
67 --values (vsid,vosuser,vusername,vdate,vprogram,vstatus);
68
69 raise_application_error(-20000,'You are not an authorized user of this database.');
70
71 END IF;
72 END IF;
73 END;

73 rows selected.


The osusername for the patch instance was not present in the table. This has been inserted in the table, which has fixed the issue.


This issue is very specific to this particular OLTP instance, because of this custom trigger. You may not find this in other instances. However, it is important to understand how the problem was diagnosed and solved, which is why I have taken the pain to document it.