Showing posts with label 11i. Show all posts
Showing posts with label 11i. Show all posts

Sunday, August 06, 2017

How to Compile Forms in Oracle Applications Release 11i and Release 12?


Steps to compile forms  in  Oracle Applications Release 12.1.x and 12.2.x

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

frmcmp_batch userid=apps/<password> module=formname.fmb
output_file=$PROD_TOP/forms/US/<name>.fmx module_type=form batch=no compile_all=special

After execution of the above command, verify there are no compilation errors in output displayed and the fmx is created succesfully.


Steps to compile forms  in  Oracle Applications Release 11i

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

f60gen module=$AU_TOP/forms/US/<custom_form>.fmb  userid=apps/<passwd> output_file=$PRODUCT_TOP/forms/<customformname>.fmx module_type=form compile_all=yes


Tips:

Use command "f60gen help=y" or "frmcmp_batch.sh help=y" to find the optional parameters.

Using Compile_all= yes changes cached version in the source file in addition to compiling pl/sql in the utput fmx file.


Saturday, July 08, 2017

How to Restore context file in oracle applications?

Scenario: Your Applications context file is either accidentally deleted or Corrupted. You do not have any latest backup of context file available.

Below are the steps to recreate context file 

The Applications context file can be retrieved by running the adclonectx.pl script.

perl /clone/bin/adclonectx.pl retrieve

Above script prompts for Database connection Info, apps username/password , Available Context files and the location to which context files can be restored.When prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost and retrieve it to the default location as in $CONTEXT_FILE parameter.

The above command can be used only when INST_TOP the is still intact. Incase if INST_TOP is also lost, the Applications tier context file may be retrieved as follows:

Execute the following command on the Database tier:
perl /appsutil/clone/bin/adclonectx.pl retrieve

When prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost.Choose the location to any writeable location and copy the restored context file to the value specified in $CONTEXT_FILE parameter on Application Tier.

To retrieve the Database tier context file,

Execute the following command on the Database tier:
perl /appsutil/clone/bin/adclonectx.pl retrieve

When prompted for the context file to be retrieved, select the Database tier context file and retrieve it to the default location ($CONTEXT_FILE on DB node).




Monday, May 09, 2016

What is the difference between dbtechstack,dbtier and dbconfig modes in Cloning?

While Cloning Oracle Applications, adcgclone can be executed in 3 Modes on Database node.


1)perl adcfgclone.pl dbTier

 It configures the ORACLE_HOME on the target database node and  recreate the controlfiles.
 This is used in case of standby database/hot backups.   


2)perl adcfgclone.pl dbTechStack 

It configures the ORACLE_HOME on the target database tier node only. Relink the oracle home.

The below steps has to be performed manually
1. Create the Target Database control files.
2. Start the Target System Database in open mode
3. Run the library update script against the Database
cd $RDBMS_ORACLE_HOME/appsutil/install/[CONTEXT NAME]
sqlplus "/ as sysdba" @adupdlib.sql [libext]
 Where [libext] should be set to 'sl' for HP-UX, 'so' for any other UNIX platform, 
or 'dll' for Windows.

3)perl adcfgclone.pl dbconfig

It configures the database with context file. Database must be open while running adcfgclone in dbconfig mode.
e.g 
cd $RDBMS_ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbconfig <Context_file>

Where Target Context File is:
$RDBMS_ORACLE_HOME/appsutil/<SID_HOSTNAME>.xml

Sunday, April 10, 2016

What are the daily activities of Oracle Applications DBA?


  1. Maintaining Oracle Application File System and Database using AD Utilities.
  2. Starting, stopping and troubleshooting issues related to Application server components.
  3. Using adadmin to relink executables, generate forms/reports,generate jar files, compile/validate APPS schema.
  4. Analyzing patch pre-requisites and Applying the Patches using AD Patch Utility on Application file System and Technology Stack.
  5. Applying Mini Packs, Family Packs, Maintenance Pack and Rollup patches.
  6. Applying database patches using opatch utility and troubleshooting issues related to OPatch.
  7. System administration activities like creating users, assigning Responsibilities, defining custom managers.
  8. Changing configuration files as per the requirement.
  9. Changing APPS password and Application database users’ password for security.
  10. Performing Healthchecks.
  11. Splicing off-cycle products using adsplice utility.
  12. Cloning of Oracle Applications 11i from Production to Test Instance.
  13. Taking backups- physical, logical, RMAN and performing Restore, Recovery.
  14. Troubleshooting issues related to concurrent managers.
  15. Managing System Configuration using Autoconfig.
  16. Modifying initialization parameters as per the requirement-PFILE AND SPFILE.
  17. Reorganization of tables that are fragmented.
  18. Creating users and assigning appropriate roles, privileges.
  19. Managing tablespaces and data files.
  20. Monitoring alert log file.
  21. Troubleshooting ORA- errors.
  22. Analyzing AWR reports.


Thursday, April 07, 2016

How to enable Trace for Application User in Oracle Applications 11i/R12?


Enabling Tracing for a Application User makes diagnostics easier to analyze.Since only the sql operations performed by a specific User are Traced.


Steps to Enable Trace for Application User:

1.Login to Oracle Applications and select the System Administrator responsibility. 
Navigate to  Profile -->  System 

2.Search for Profile Option 'Initialization SQL Statement - Custom' .Also Specify the username whom you wish to enable tracing during Search.

3.
Under username column Update the below line

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'USER_TRACE_IDENTIFER' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Note : Level cane be 4,8,12 depends on your requirement 


Save the Changes done in Profile Option

4. Be careful while updating the Profile Option, Any incorrect Syntax may Prevent user from Logging in

5. Inform User to Re-login and Perform the Activity which needs to be traced.

6. Trace Files can be found in the directory specified by "user_dump_directory" parameter on the database server.
Use the identifier given in Profile Option to search the trace file in udump directory.

7. After the issue is reproduced and required trace files are collected for analysis, disable Tracing from Front End.
Its recommended to disable the trace immediately after the diagnostics are collected, as it will generate huge logfiles.

8. Navigate to system=> Profile form under System Administrator Responsibility.

Query for the username  and Profile Option name "Initialization SQL Statement - Custom"

Remove the values and make it blank.

Save the changes.


Thursday, March 24, 2016

Discoverer Login Fails with ORA-1017

Logging to discoverer url as sysadmin gives the following error.

ORA-1017: Invalid username/password
A connection error.  
- Oracle BI Discoverer is unable to authenticate using the password provided. This can happen due to an invalid password or because the password was lost while using back, forward, or refresh in your browser. Enter the password again to continue.
- Failed to connect to database - Unable to connect to Oracle Applications database (afscpgcs) 

Cause:

APPL_SERVER_ID parameter in .dbc file of Applications MT node(applmgr) and discoverer user is not matched


$diff test1_disco.dbc test1_mt.dbc

4c4
< APPL_SERVER_ID=178***********
---
> APPL_SERVER_ID=1A***********



Solution:

Copy the <SID>.dbc from $FND_TOP/secure or $FND_SECURE of Applications Middle tier to Disoverer user

Bounce Discoverer Services

Tuesday, February 23, 2016

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’);

Monday, February 08, 2016

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.

Thursday, February 04, 2016

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

Sunday, January 31, 2016

How to Check Patch Applied Status in Oracle Applications?

R11i,12.0.x,12.1.x
=============

From Oracle Applications Release 11i to 12.1.x, Patch application Status can be queried from ad_bugs or ad_applied_patches tables

Query to check whether patch is applied on a instance.

SQL>select bug_number,creation_date,last_update_date from apps.ad_bugs where bug_number='&patchnum';

SQL> select PATCH_NAME,CREATION_DATE from apps.ad_applied_patches where PATCH_NAME='&patchnum';

What is difference between above two Queries?


Suppose, we applied a patch which has multiple bugfixes.The details of all bugs fixed by the patch are listed in ad_bugs table.
In Contrast,ad_applied_patches table give info only about the patches which are applied through adpatch utility.


R12.2
=====

From Release 12.2, Online Patching utility(adop) is used for Patching application Filesystem.

Querying the data from ad_bugs or ad_applied_patches tables may not give correct information.Because, online patching can be aborted anytime prior to Cutover Phase.
ad_bugs or ad_applied_patches tables may contain entries for patches which are started through adop and later aborted.

The below query gives accurate Information about Patch Applied Status in R12.2

select AD_PATCH.IS_PATCH_APPLIED(\'$release\',\'$appltop_id\',\'$patch_no\',\'$language\') from dual;

example sql:
SELECT adb.bug_number,ad_patch.is_patch_applied('11i', 1045, adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in (22498647);

or for single app tier installations:
select ad_patch.is_patch_applied('R12',-1,22498647) from dual;

Sample Output:

EXPLICIT = applied
NOT APPLIED = not applied / aborted


Database
=========

Login to Database node as the database os user

Set environment using commands below

$export ORACLE_HOME=/u01/app/oracle/product/112

$export PATH=$PATH:$ORACLE_HOME/Opatch

$which opatch
==>It should give output like /u01/app/oracle/product/112/Opatch

$opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

== This gives list of all patches applied to the ORACLE_HOME


$opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc|grep 988765


==> To check if a particular patch is applied to the ORACLE_HOME



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;

===

Monday, January 18, 2016

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;

Saturday, December 26, 2015

Autoconfig Error during SETUP Phase (Oracle Applications DBA 11i)

Autoconfig failed during setup phase:

While running the script adgendbc.sh

Unable to update GUEST_USER_PWD in database to GUEST/ORACLE1 -
java.sql.SQLException: ORA-06550: line 1, column 323:
PLS-00201: identifier 'FND_VAULT.PUT' must be declared
ORA-06550: line 1, column 323:
PL/SQL: Statement ignored

Cause

AdminAppServer.java 115.32 calls fnd_app_server_pkg.update_server passing 5 parameters.
The versions of FND_APP_SERVER_PKG (AFSCASRS.pls/AFSCASRB.pls) accepts 5 parameters.

These version used in the database is older than the version at OS filesystem level.

Solution

1. Run following SQL query :

select text
from all_source
where name ='FND_APP_SERVER_PKG' and line < 7;

2. If the versions in the database are older than the ones at the OS level, go to the

$FND_TOP/patch/115/sql directory and recreate the spec/body by running :

- sqlplus apps/<passwd> @AFSCASRS.pls

- sqlplus apps/<passwd> @AFSCASRB.pls

3. Rerun Autoconfig.