Category: r12.1.x

Applying Patch R12.AD.B.7 fails with ORA-1031 while running ademusr.sql


While Applying Patch 21314548:R12.AD.B.DELTA.7 using adpatch, Patch worker failed at running the script ademusr.sql.
To begin troubleshooting the issue,First check the worker id which was running the failed script.
We can get this information from Patch logfile[adpatch.log or u21314548.log] or console output while applying the patch.


Check the worker logfile[adworkxxx.log] for the failed worker


Errors in workerlog
===================
ORA-01031 occurs while Granting SELECT on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role For Patch 20395533 (Doc ID 2093641.1)




GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role


AD Worker error:
The following ORACLE error:
ORA-01031: insufficient privileges
occurred while executing the SQL statement:


GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role


Error occurred in file
/test1/applmgr/1213/ad/12.0.0/patch/115/sql/ademusr.sql
with arguments ‘&systempwd &un_fnd &pw_fnd &un_apps &pw_apps’.


Time when worker failed: Fri Mar 04 2016 09:48:00




The error occurred because worker is running the scripts as apps user, and APPS user does not have enough privileges to grant DBA_USERS_WITH_DEFPWD


Solution
========


1.Run the below sql statements to grant the role as system user.


SQL> show user
USER is “SYS”
SQL> GRANT select on DBA_USERS_WITH_DEFPWD to SYSTEM with grant option;


Grant succeeded.


SQL> show user
USER is “SYSTEM”
SQL> GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role;


Grant succeeded.


2. Restart the failed worker using adctrl.


3. Monitor Patching until completion.


How to Unlock Objects Statistics?

While running Concurrent Program, Gather Schema Statitics the request failed with error
20005: object statistics are locked (stattype = ALL)***

Logfile:

start of log messages from FND_FILE


+———————————————————————-


—–+


In GATHER_SCHEMA_STATS , schema_name= APPS percent= 10 degree = 2 


internal_flag= NOBACKUP


Error #1: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.CZ_MESSAGE_QENTRIES***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #2: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.ECX_INQUEUE***ORA-20005: 


object statistics are locked (stattype = ALL)***


Error #3: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.ECX_IN_OAG_Q_TABLE***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #4: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.ECX_OUTQUEUE***ORA-20005: 


object statistics are locked (stattype = ALL)***


Error #5: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_CRMXRWD_REQ_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #6: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_CRMXRWD_RES_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #7: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_CRMXRWL_REQ_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #8: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_CRMXRWL_RES_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #9: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_GTREQ_QTBL***ORA-20005: 


object statistics are locked (stattype = ALL)***


Error #10: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_GTRES_QTBL***ORA-20005: 


object statistics are locked (stattype = ALL)***


Error #11: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_HM000A_REQ_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #12: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_HM000A_RES_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #13: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_JTF_REQ_Q_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #14: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_JTF_RES_Q_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #15: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_RAPID_BP_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #16: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_RAPID_B_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #17: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_RAPID_ER_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #18: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_RAPID_MP_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #19: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_FM_RAPID_M_QTBL***ORA-


20005: object statistics are locked (stattype = ALL)***


Error #20: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_IH_BULK_QTBL***ORA-20005: 


object statistics are locked (stattype = ALL)***


Error #21: ERROR: While GATHER_TABLE_STATS:


                        object_name=APPS.JTF_PF_LOGGING_TABLE***ORA-


20005: object statistics are locked (stattype = ALL)***


+———————————————————————-


—–+


End of log messages from FND_FILE


+———————————————————————-


—–+

Solution:

This error comes when analyzing tables in oracle. Basic issue in this error is  when you
import table without data i.e structure/schema only, oracle will lock table statistics.
You can view all the locked tables in schema by executing following query:


select table_name, stattype_locked 
from dba_tab_statistics 
where owner = ‘APPS’ and stattype_locked is not null;

Then how to unlock them, following query will help us to sort this issue:


select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name 
||”’);’ from dba_tab_statistics where owner = ‘MBS’ and stattype_locked is not null;

or you can do that for every single table:


exec DBMS_STATS.UNLOCK_TABLE_STATS(‘owner’,’table name’);

How to Purge Workflow Notifications from Mailer Queue, so that Email is not sent to Users?

What to do when Workflow Notification  Mailer was not running properly for a certain period of time and accumulated all the emails in Workflow Queue.
We can Purge the workflow Mails which are not required by updating mail_status=’SENT’ for unwanted notifications in wf_notifications table.

Here are a couple of reasons to Purge Workflow Notifications

a)The end user may not want to receive outdated email.
b)The Workflow Queue has accumulated a lot of emails during the issue period, and processing has become very slow.

Below are the steps to Purge Workflow Notifications which are not required

1.First Take a Backup of wf_notifications table  and stop the Workflow Notification Mailer

2. Verify notifications from WF_NOTIFICATIONS table that has potential for being sent

SQL> select notification_id, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     where status in (‘OPEN’, ‘CANCELED’)
     And Mail_Status In (‘MAIL’, ‘INVALID’)
     order by notification_id;
   
3. Narrow down the emails which are not to be sent by the Mailer from a specific date range.


SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     Where Status In (‘OPEN’, ‘CANCELED’)
     And Mail_Status In (‘MAIL’, ‘INVALID’)
     and begin_date < sysdate-30              — List only emails older than 30 days ago
     order by notification_id;

4. Set Mail_status=’SENT’ in WF_NOTIFICATIONS

SQL>update WF_NOTIFICATIONS set mail_status = ‘SENT’
where mail_status in (‘MAIL’,’INVALID’)
and Status In (‘OPEN’, ‘CANCELED’)
and begin_date < sysdate-30;      


SQL>Commit;

This will update  notifications ,which are older than 30 days and waiting to be sent by the mailer to SENT.
Therefore the notifications will not get emailed when the Mailer is restarted.

5.Run the script $FND_TOP/patch/115/sql/wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.  It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = ‘SENT” it will not enqueue these messages again.. Only the ones where mail_status = ‘MAIL’ and status = ‘OPEN’ will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer. (or CANCELED and INVALID if certain concurrent reports are run)

Example :
$ sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr

Example Syntax:
$ sqlplus apps/***@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps ***** applsys

6.Now start the Workflow Notification Mailer.

PROCESS and DISCARD Folders IMAP

While Troubleshooting workflow relates issues in EBS, we may need to clear PROCESS and DISCARD folders under workflow user.
The below commands are useful to move folders, connecting to imap

a)Shutdown Workflow Components
b) login to imap host(e.g server1.ebs.com) as applmgr user


  $ telnet server1.ebs.com 143
  
    You will get below messages:


    —————————-
Trying **.**.***.**


Connected to server1.ebs.com (**.**.***.**).
Escape character is ‘^]’.


* OK Dovecot ready.
    —————————-
  i login wfmgr *****
  

  i rename PROCESS process_<sys_date>
  
 i rename DISCARD discard_<sys_date>
  

 i create PROCESS
  
i create DISCARD

i subscribe PROCESS

 i subscribe DISCARD



 i unsubscribe process_<sys_date>

 i unsubscribe discard_<sys_date>
  

c) Startup Workflow Components

R12-Password Change Utility





Oracle has introduced a new utility to change the Oracle E-Business Suite passwords in R12.1.2.AFPASSWD is an enhanced version of FNDCPASS utility.
Differances between AFPASSWD and FNDCPASS


1) AFPASSWD only prompts for passwords required for the current operation. allowing separation of duties between applications administrators and database administrators.
Changing sysadmin Password using AFPASSWD prompts only for APPS Password. Changing APPLSYS password prompts for APPS and SYSTEM password.
Whereas, FNDCPASS requires SYSTEM and APPS password to change password for application and database users.


2) AFPASSWD can be run from the database tier as well as the application tier. Whereas, FNDCPASS can be run only on Concurrent Manager Node


3) When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.


Changing SYSADMIN Password  


Syntax:$AFPASSWD -f SYSADMIN
Output:Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.  Working…
Enter new password for user [SYSADMIN]:
Verify new password for user [SYSADMIN]:
Password is changed successfully for user SYSADMIN.
Password is changed successfully for user SYSADMIN.
AFPASSWD completed successfully.


Changing APPLSYS Password


Syntax: $AFPASSWD -c apps@PROD -s APPLSYS
Output: Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.
Enter the password for your ‘SYSTEM’ ORACLE schema:
Connected successfully to SYSTEM.  Working…
Enter new password for user:
Verify new password for user:
Working…
AFPASSWD completed successfully.


Usage


AFPASSWD
Usage:
AFPASSWD [-c [@]] -f
AFPASSWD [-c [@]] -o
AFPASSWD [-c [@]] -a
AFPASSWD [-c [@]] -l {TRUE|FALSE}
AFPASSWD [-c [@]] -L {TRUE|FALSE}
AFPASSWD [-c [@]] -s


Options:
-c [@]
Specify the connection string to be use, parameters


Application-manager user and/or TWO_TASK value,
this option can be use in combination with the others.
(If not provided, AFPASSWD will try to default the values
from the environment)
is the APPS schema owner.


-f
Change the password for an Application user.


-o
Change the password for an Oracle Applications Database user.


-a Modify ALLORACLE users.


-l {TRUE|FALSE}
Lock|Unlock account for a single Oracle Applications database user.


-L {TRUE|FALSE}
Lock|Unlock accounts for non-essential Oracle Applications database users


-s
Modify APPLSYS user. This requires the execution
of autoconfig (in ALL-TIERS) to distribute the
changes on your instance ie DB-TIER and ADMIN-TIER.


-h
Display this help.


Notes:
– AFPASSWD will prompt for the required passwords.
– AFPASSWD will prompt for new passwords twice for confirmation.
– Data entered for which contains spaces must be enclosed
in double quotes. For example, AFPASSWD -f “SCOTT TIGER”
– The only option permitted in combination with other options is the -c option.
Otherwise, there should only be one option per command (see usage above).
If additional options are provided on the command line, only the first valid
option after -c (if -c is present) will be executed.
$

Script to Check History of a concurrent Program


Sometimes, While troubleshooting Performance issues related to Concurrent Requests, we need to know the duration for request completion in the past. The script below gives the start time,end time and arguments for a given Concurrent Program

===

set pagesize 2000
set linesize 120
set wrap off
column user_concurrent_program_name format a45 noprint
column argument_text format a45 print
column user_name format a15
column start_time format a15
column end_time format a15
column comp_time format 9999.99


select request_id,
       user_concurrent_program_name,
       to_char(actual_start_date,’DD/MON HH24:MI:SS’) START_TIME,
       to_char(ACTUAL_COMPLETION_DATE,’DD/MON HH24:MI:SS’) END_TIME,
       (actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,user_name, status_code, phase_code
from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user
where fnd_concurrent_requests.concurrent_program_id = fnd_concurrent_programs_tl.concurrent_program_id
and user_concurrent_program_name like ‘%&%’
and fnd_concurrent_programs_tl.language=’US’
and requested_by=user_id
and actual_start_date >(sysdate-1)
order by actual_start_date desc,ACTUAL_COMPLETION_DATE desc;

===

How to Put Concurrent Requests in Hold Status?

During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity.
This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.

1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE=’P’ and hold_flag=’N’;
2) select count(*) from apps.conc_req_on_hold
3) update fnd_Concurrent_requests set hold_flag=’Y’ where PHASE_CODE=’P’ and hold_flag=’N’ and request_id in (select request_id from apps.conc_req_on_hold);

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

4) Commit;

To Release hold on Concurrent Requests patching, run the below sql :

5) update fnd_Concurrent_requests set hold_flag=’N’ where request_id in (select request_id from apps.conc_req_on_hold);

6)Commit the changes

 commit;