Showing posts with label Application Level Issues and Solutions. Show all posts
Showing posts with label Application Level Issues and Solutions. Show all posts

Thursday, 7 September 2017

ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)

ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)


To BottomTo Bottom

In this Document
Symptoms
Cause
Solution
References



APPLIES TO:

Oracle EBS Applications Performance - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
10g or 11g Database with Apps 11i or R12


SYMPTOMS

Gathering statistics using concurrent jobs "Gather Schema Statistics / Gather Table Statistics" or running FND_STATS fails with "ORA-20005: object statistics are locked".
Example 1:
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=AR.AR_REV_REC_QT***ORA-20005: object statistics are locked (stattype = ALL)***
 Example 2:
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP stats on table GAT_REQ_QTBL is locked

CAUSE

This can happen with Advance Queue tables. In 10g, if a queue is created or recreated during the upgrade, automatic statistics gather is locked (disabled) on these queue.
The following statement can be used to check the tables which have statistics locked:
    select owner, table_name, stattype_locked
    from dba_tab_statistics
    where stattype_locked is not null; 

SOLUTION

Unlock statistics gathering on those queues running the commands below. To unlock all the tables in a schema at once:
exec dbms_stats.unlock_schema_stats('schema_owner'); 
OR
To unlock individual tables (need to run for all tables individually):
exec dbms_stats.unlock_table_stats('table_owner','table_name');
Examples:
SQL> exec dbms_stats.unlock_schema_stats ('AR');
SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT'); 

Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)

To BottomTo Bottom

In this Document
Symptoms
Changes
Cause
Solution



APPLIES TO:

Oracle Applications Technology Stack - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle EBS Applications Performance - Version 12.1.1 to 12.1.1 [Release 12.1]
Oracle Applications DBA - Version 11.5.10.2 to 11.5.10.2 [Release 11.5.10]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 11.1.0.6 to 11.1.0.8



SYMPTOMS

"Gather Schema Statistics" program reported following errors in request log files:
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #4: ERROR: While GATHER_TABLE_STATS:  object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***


CHANGES

Problem started after database has been upgraded to 11G.

CAUSE

There are two reasons for that error message:
1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and
it fails with ora-20001 errors.

The following SQL should return one row, not two:
SQL>select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

Since there are two rows in histograms table, FND_STATS creates following command to gather statistics on table 'JE_BE_LINE_TYPE_MAP' :
 dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME
=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SOURCE SIZE 254 FOR
COLUMNS SOURCE SIZE 254');
 Above command will work on 9i and 10G databases but it will fail with ora-20001 errors on 11G.

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.
You can use following SQL to identify. SQL will prompt for table name, use table name from the errors. In above examples you can use FII_FIN_ITEM_HIERARCHIES.
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name ='&TABLE_NAME'
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null;

SOLUTION


Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the applsys user.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.
-- identify duplicate rows

select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;

-- Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
  (
   select hc.table_name, hc.column_name
   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
   where hc.table_name  ='&TABLE_NAME'
   and hc.table_name= tc.table_name (+)
   and hc.column_name = tc.column_name (+)
   and tc.column_name is null
  );

commit;

Query to find responsibilities for particular user


Query to find responsibilities for particular user


select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = upper('&user_name') and furg.user_id = fu.user_id 
and furg.responsibility_id = fr.responsibility_id and fr.language = userenv('LANG');

Query to find all responsibilities of a user

Query to find all responsibilities of a user


-------------------------------------------------------------------------------
-- Query to find all responsibilities of a user
-------------------------------------------------------------------------------
SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",      
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('AMOHSIN')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
 ORDER BY frt.responsibility_name;

Thursday, 31 August 2017

Error ORA-20100 .tmp Creation For FND_FILE Failed

APPLPTMP - this parameter directly update from $CONTEXT_FILE for usr tmp location issue

Difference between APPLPTMP and APPLTMP Directories in EBS (Doc ID 1355735.1)

Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests (Doc ID 392635.1)

Issue 


**Starts**18-AUG-2017 11:26:54
**SQL error and free**18-AUG-2017 11:27:14
ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.
Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with wri18-AUG-2017 11:27:14

While i checked cd $APPLPTMP parameter in $CONTEXT_FILE 
it shows some different location instead of my current server location

Solution

1.shut down application tier

2.So i have update $CONTEXT_FILE  with below /usr/tmp


grep APPLPTMP $CONTEXT_FILE

<APPLPTMP oa_var="s_applptmp" osd="UNIX">/usr/tmp</APPLPTMP>

3.run autoconfig application tier

Saturday, 19 August 2017

FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed

APPLPTMP - this parameter directly update from $CONTEXT_FILE for usr tmp location issue

Difference between APPLPTMP and APPLTMP Directories in EBS (Doc ID 1355735.1)

Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests (Doc ID 392635.1)

Issue 


**Starts**18-AUG-2017 11:26:54
**SQL error and free**18-AUG-2017 11:27:14
ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.
Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with wri18-AUG-2017 11:27:14

While i checked cd $APPLPTMP parameter in $CONTEXT_FILE 
it shows some different location instead of my current server location

Solution

1.shut down application tier

2.update $CONTEXT_FILE  with "/usr/tmp"


grep APPLPTMP $CONTEXT_FILE


<APPLPTMP oa_var="s_applptmp" osd="UNIX">/usr/tmp</APPLPTMP>

3.run autoconfig in application tier

Wednesday, 16 August 2017

Apps user account locked solution

R12: Clone fails with error "ORA-28000 The account is locked" (Doc ID 1114244.1)


SYMPTOMS

The clone of an E-Business Suite Instance of Release 12 fails with following error:
...
Exception occurred: java.sql.SQLException: ORA-28000: the account is locked
ORA-28000: the account is locked

...

CAUSE

The issue is caused by the APPS Database account, which is locked.

The following script allows you to check the number of failed logins, which can lock the apps user :
select profile , resource_name, limit 
from dba_profiles 
where resource_name ='FAILED_LOGIN_ATTEMPTS';

Example output :


DEFAULT FAILED_LOGIN_ATTEMPTS : 10
AD_PATCH_MONITOR_PROFILE FAILED_LOGIN_ATTEMPTS : 3

For additional information, please review Note 114930.1 - 'Oracle Password Management Policy'.


SOLUTION

To implement the solution, please test the following steps in a Development instance and migrate accordingly:

1. Ensure that you have taken a backup of your Environment.

2. Increase the number of failed login which locks apps user (optional):
alter profile AD_PATCH_MONITOR_PROFILE limit failed_login_attempts unlimited;
alter profile default limit failed_login_attempts unlimited password_lock_time 1/1440;
3. Unlock the locked APPS User account :
alter user apps account unlock;

4. Rerun the failed cloning commands.

5. Migrate the solution as appropriate to other Environments.

Thursday, 20 July 2017

While Apply GST patch 19287293 Locked object

1.

select c.owner,c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine 
from v$locked_object a , v$session b, dba_objects c 
where b.sid = a.session_id 
and a.object_id = c.object_id;

OutPut

APPLSYS FND_CONCURRENT_QUEUES TABLE 417 62192 INACTIVE orapreprod dev.tbzoriginal.local
APPLSYS FND_CONCURRENT_REQUESTS TABLE 418 46553 INACTIVE orapreprod dev.tbzoriginal.local

---------------------------------------------------------


Solution
----------


Hi, 

Please execute the following: 

ALTER SYSTEM KILL SESSION '417,62192'; 
ALTER SYSTEM KILL SESSION '418,46553'; 

Check if the 2 locks have been released: 

select c.owner,c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine 
from v$locked_object a , v$session b, dba_objects c 
where b.sid = a.session_id 
and a.object_id = c.object_id; 

Retry the patching. 

Monday, 17 July 2017

FNDCPASS apps password change in application

FNCPASS CHANGE
--------------------------

First change system password in DB.
alter user system identified by clonemanager

FNDCPASS apps/welcome 0 Y system/clonemanager SYSTEM APPLSYS cloneapps
FNDCPASS apps/cloneapps 0 Y system/clonemanager USER sysadmin clonewelcome

Form Compile


Form Compile
--------------------
/kzn02/Oracle/PROD/scripts


frmcmp_batch userid=apps/kzn123 module=XXMISC_RECEIPT_REF.fmb output_file=$XXKZJ_TOP/forms/US/XXMISC_RECEIPT_REF.fmx module_type=form batch=no compile_all=special


Thursday, 13 July 2017

EBS Currently connected user details


#to check users connected to the instance via toad;
================================================================;
select SID,SERIAL#,STATUS,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM,SQL_ID,SQL_EXEC_START,PREV_EXEC_START,MODULE,ACTION,CLIENT_IDENTIFIER 
FROM V$SESSION  
WHERE  PROGRAM LIKE 'toad.exe%';

#to check users connected to EBS;
=================================;
select distinct fu.user_name User_Name, fu.email_address emailid,
fu.description, TO_CHAR(IC.FIRST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"First Accessed",
TO_CHAR(IC.LAST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"Last Accessed", ic.nls_territory, FVL.RESPONSIBILITY_NAME "RESPONSIBILITY NAME", fr.RESPONSIBILITY_KEY "Responsibility Key", ic.function_type,ic.time_out, fu.user_id, fu.employee_id,
ic.responsibility_application_id, ic.responsibility_id, ic.org_id, ic.counter, fr.menu_id, ic.disabled_flag
from apps.fnd_user fu,
apps.fnd_responsibility fr, apps.icx_sessions ic, apps.fnd_responsibility_VL FVL
where fu.user_id = ic.user_id AND
FR.RESPONSIBILITY_KEY=FVL.RESPONSIBILITY_KEY AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect > sysdate - (ic.time_out/60)/96
order by "Last Accessed" desc;