Experiments from the Field..Based on True Stories

Preface

Applies to:

Troubleshooting Details

The Problem:

The Approach:

sandbox:dev> FNDCPASS apps/$apps_pass 0 Y system/$sys_pass USER GVERMA welcome1
Log filename    : L2355077.log

Report
filename : O2355077.out

sandbox:dev> more L2355077.log
+---------------------------------------------------------------------------+
Application
Object Library: Version : 11.5.0 - Development

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
 module:

+---------------------------------------------------------------------------+

Current system time is 06-JUN-2007 11:21:16

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Concurrent
request completed successfully
Current system time is 06-JUN-2007 11:21:16

+---------------------------------------------------------------------------+

172.18.2.149 - - [06/Jun/2007:13:09:34 -0400] "GET 
/OA_HTML/AppsLocalLogout.jsp?returnUrl=%2FOA_HTML%2FAppsLocalLogin.jsp%3FcancelUrl%3D%
2FOA_HTML%2FAppsLocalLogin.jsp HTTP/1.1" 302 293
172.18.2.149 - - [06/Jun/2007:13:09:34 -0400] "GET 
/OA_HTML/AppsLocalLogin.jsp?cancelUrl=/OA_HTML/AppsLocalLogin.jsp&langCode=US&username=GVERMA
HTTP/1.1" 200 6910
172.18.2.149 - - [06/Jun/2007:13:09:41 -0400] "POST /OA_HTML/fndvald.jsp HTTP/1.1" 302 260
172.18.2.149 - - [06/Jun/2007:13:09:42 -0400] "GET /OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE HTTP/1.1" 200 1430
172.18.2.149 - - [06/Jun/2007:13:13:34-0400] "GET /OA_HTML/fndvald.jsp HTTP/1.1" 200 190
172.18.2.149 - - [06/Jun/2007:13:31:09 -0400] "GET /OA_HTML/AppsLocalLogin.jsp HTTP/1.1" 200 6647

It was as if the flow of control was being snapped totally after the login page, without any further activity at the OA Framework level at all.

Since forms URL was working and the full set of responsibilities was visible, one of the possible resources which was being accessed could have been the workflow tables for tying up responsbilities with workflow roles.

Based on this, a quick check to Workflow related tables revealed this error:

SQL> select count(1) from WF_LOCAL_USER_ROLES;

select count(1) from WF_LOCAL_USER_ROLES
      
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 231, block # 235273)
ORA-01110:
data file 1254:
'/ORACLE/data/datafiles/dev/data11/aol_data17.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

One of the options was to re-import the APPLSYS.WF_LOCAL_USER_ROLES from Production, which would have involved a downtime.

A better analysis would be to find out the extent of damage done to the table. For this, the DBMS_REPAIR package would be useful. Reporting not only indicates what is wrong with a block, but also identifies the associated repair directive.

The entire list of options include:

There are two levels of block corruption: Hard and Soft. Soft corrupted blocks can be set to be ignored while reading the table's contents.

Create the necessary aixiliary tables for identifying corrupt blocks in the table in question:

Check the specified objects and populates the repair table with information about corruptions and repair directives.Validation consists of block checking all blocks in the object. 

See the number of corrupted blocks:

Check the level of corruption -- either Soft of Hard:

This procedure fixes the corrupt blocks in specified objects based on information in the repair table that was previously generated by the check_object procedure.

Prior to effecting any change to a block, the block is checked to ensure the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is effected, the associated row in the repair table is updated with a fix timestamp.

SQL> exec DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'APPLSYS',
OBJECT_NAME=> 'WF_LOCAL_USER_ROLES',  
OBJECT_TYPE => dbms_repair.table_object,  
REPAIR_TABLE_NAME => 'REPAIR_TABLE',  
FIX_COUNT=> :c); 
PL/SQL procedure successfully completed.

Enable the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skip applies to the table and its indexes.

The corrupt blocks get skipped now:

At this stage, the DB verify utility can also be used to see the corrupted blocks' information:

The Effect:

As a result of this workaround, the affected users were able to login through the self service applications URL and also able to see their assigned responsbilities.

Additionally, the apache access log showed the following depicting that application logic was flowing as intended:

The Learning

• The regular debugging mechanisms may not be helpful in troubleshooting the issue at hand and one may have to rely on some basic checks
  
• Sometimes simple things like block corruption to basic workflow tables can cause critical user functions to not work
• Oracle supplied packages like DBMS_REPAIR may be used to analyze and fix such block corruptions, especially when it is acceptable to skip soft corrupted blocks