Showing posts with label EBSO. Show all posts
Showing posts with label EBSO. Show all posts

Sunday, 13 March 2016

Autopatch error: The worker should not have status 'Running' or 'Restarted' at this point

If you are restarting a failed patch session in oracle applications, sometimes you may encounter the error

AutoPatch error:
The worker should not have status 'Running' or 'Restarted' at this point.

Telling workers to quit...

All workers have quit.

Connecting to APPS......Connected successfully.

AutoPatch error:

Error running SQL and EXEC commands in parallel

Cause:

1. adpatch or adop process was killed from OS level while patch is being applied

2. Database shutdown or terminated



Solution:

Using adctrl utility, use option 4 to change the worker status to Failed


Review the messages above, then press [Return] to continue.

                    AD Controller Menu
     ---------------------------------------------------

     1.    Show worker status

     2.    Tell worker to restart a failed job

     3.    Tell worker to quit

     4.    Tell manager that a worker failed its job

     5.    Tell manager that a worker acknowledges quit

     6.    Restart a worker on the current machine

     7.    Exit


Enter your choice [1] : 4

Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu : all

Status changed to 'Failed' for worker 1.
Status changed to 'Failed' for worker 2.
Status changed to 'Failed' for worker 3.
Status changed to 'Failed' for worker 4.
Review the messages above, then press [Return] to continue.



select option "1. Show Worker Status" , The worker status will be "Failed" 

Now select option 2. "2. Tell worker to restart a failed job" 


Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu : all

Status changed to 'Fixed, restart' for worker 1.
Status changed to 'Fixed, restart' for worker 2.
Status changed to 'Fixed, restart' for worker 3.
Status changed to 'Fixed, restart' for worker 4.


Restart adpatch 

In 11i, R12.1.X version of oracle applications

when applying patch using adpatch , Select option Yes When it prompts for

'Do you wish to Continue with Previous adpatch Session'

In R12.2.x, Use restart and abandon parameters


adop phase=apply patches=123456 restart=yes abandon=no 

To restart the patch session from where it failed.

ADOP Patching Fails with aipspv(): Error setting up PDI list for upgrade

While Applying patch on a 12.2.5 environment using online patch cycle in adop, Autopatch Failed with error

AutoPatch error:
Invalid password for schema "CUST1" (oracle_id = 20087)

AutoPatch error:
Failed getting PDI list for product 'xx1'

AutoPatch error:
aipspv(): Error setting up PDI list for upgrade

Root Cause
=========

Password for Application schema is not changed using FNDCPASS

Solution
========

1.Change the password of custom application with FNDCPASS 

Syntax for FNDCPASS

$ FNDCPASS apps/<PASSWORD> 0 Y system/<PASSWORD> ORACLE CUST1 <NEWPASSWORD>
Log filename : L********.log


Report filename : O*****.out

2. Verify the password change is successful

$ cat L*******.log
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.2

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

FNDCPASS:
+---------------------------------------------------------------------------+

Current system time is 10-MAR-2016 23:21:30

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

Working...
Password is changed successfully for user BOLINF.
FNDCPASS completed successfully.

+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 10-MAR-2016 23:21:30

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



Please Note, In 12.2.x Environments FNDCPASS must be executed on RUN Filesystem.

3. Restart the online patching cycle 

While restarting adop apply phase after a previously failed session, use options abandon and restart


adop phase=apply patches=123456 restart=yes abandon=no 

[Use this command if you wish to continue the previous patch session from the step where it failed]

adop phase=apply patches=123456 restart=no abandon=yes


[Use this command if you wish to start a fresh patch session]




If changing password using FNDCPASS does not work, analyse further details about database user and application user.

Check if the database user and application user are defined correctly using the sql queries below

SQL> select ORACLE_ID from FND_ORACLE_USERID where oracle_username like '%CUST1%';

 ORACLE_ID
----------
     2008

SQL> select application_id from fnd_application where application_short_name = 'XX1';

APPLICATION_ID
--------------
         2003

SQL> select * from fnd_product_installations where oracle_id=2008;


If the user details are not correct from the output of above sql, Please correct them.

Tuesday, 8 March 2016

ORA-12504 While running Autoconfig on Database Server

After Oracle Database upgrade from 11.2.0.3 to 11.2.0.4, Autoconfig errored on Database server with the below error

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /test1/oracle/product/11204/appsutil/install/test1_host1
      afdbprf.sh              INSTE8_PRF         1

  [APPLY PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /test1/oracle/product/11204/appsutil/install/test1_host1
      adcrobj.sh              INSTE8_APPLY       1


AutoConfig is exiting with status 2

====Running the failed script has given the ORA-12504 error

java.lang.OutOfMemoryError: Java heap space error in Output PostProcessor

Possible causes for Java Heap Space error in OPP

1. There is limited heap space in the JVM to process the report
2. The Java application running is out of memory
3. The scalable feature isn't enabled
4. An older version of the JRE is being used in the configuration, 
5. There is insufficient space in the designated temp directory for processing the report, and reports are embedded with images which require a lot of space that unavailable.


The log will contain a java stack error similar to the following:
java.lang.reflect.InvocationTargetException
Caused by: java.lang.OutOfMemoryError: Java heap space
at oracle.xdo.parser.v2.XMLDocument.createNodeFromType(XMLDocument.java:2869)
at oracle.xdo.parser.v2.XMLDocument.createNodeFromType(XMLDocument.java:3000)
at oracle.xdo.parser.v2.XMLDocument.createTextNode(XMLDocument.java:735)
at oracle.xdo.parser.v2.DocumentBuilder.characters(DocumentBuilder.java:553)
at oracle.xdo.parser.v2.NonValidatingParser.reportCharacters(NonValidatingParser.java:1680)
at oracle.xdo.parser.v2.NonValidatingParser.parseText(NonValidatingParser.java:1391)
at oracle.xdo.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1268)


How to resolve Java Heap Space error in Output Post Processor?


Solution 1
==========


1. Determine what the heap size per OPP process is currently:
select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

2. The default should be:
J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m

3. Increase the Heap Space per Process to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

4. Bring the managers down.
5. Please run the Concurrent Manager Recovery feature to address any Concurrent Manager / Concurrent Processing issues within the Oracle Application Manager.  NOTE: Regular use of the CP Analyzer in Note 1411723.1 can provide an Immediate Analysis and Output of a Concurrent Environment.
6. Bring the managers up again.

OR

Solution 2
==========
1. Log into applications with the System Administrator responsibility.
2. Navigate to Concurrent -> Program -> Define
3. Query the XML Publisher Template Re-Generator program
4. Set the following value for the Executable Options : -Xmx1024m
5. Save changes.
6. Retest the program.

OR


Solution 3
==========

Configure the XML Publisher Administrator Configuration settings.
1. As XML Publisher Administrator navigate to Administration->Configuration.
2. Under Temporary Directory pick a temporary file location on your concurrent processing node. This should be at least 5GB or 20x larger than largest XML data file you generate
3.  Under FO Processing, set:
o Use XML Publisher's XSLT processor set to True
o Enable scalable feature of XSLT processor set to False
o Enable XSLT runtime optimization set to True 

Thursday, 25 February 2016

Downtime Mode feature in ADOP Patching


Downtime mode is introduced in E-Business Suite Release 12.2, to minimize the duration of 12.2 upgrade process.This feature is available from AD.Delta.5 Release Update Pack.

Before applying patch in downtime mode, all Application services must be shutdown.Downtime mode does not use online patching cycle phases,hence it applied patches quickly compared to online patching. However, EBS application will be down for longer time in downtime mode.Make sure that no adop cycle is in progress while applying patch in downtime mode, Otherwise it will give error.

To apply patch in downtime mode. Follow the below steps

1. Shut down Application Services

2. Apply patch using the below command

 $ adop phase=apply patches=<patch_number> apply_mode=downtime

3. Startup Application Services


Following Scenarios require to apply patch in downtime mode in R12.2

1. All Patching that is part of 12.2 upgrade process. Once the upgrade is complete and users are online, all subsequent patching on a production system should use online mode, unless stated in patch ReadMe.

2. Single-node development or test environments, where production support and high availability are not required.

Limitations of downtime mode

1. We cannot test the patch application prior to cutover as in online patching cycle.
2. We cannot rollback patch applied using downtime mode.
3. Downtime mode is not applicable for Multi-node environments


Tuesday, 23 February 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’);

Sunday, 21 February 2016

What Happens during adop prepare Phase?


The online patching cycle in R12.2  which uses ADOP  utility applies patch in 5 Phases
The phases are

PREPARE
APPLY
FINALIZE
CUTOVER
CLEANUP

PREPARE PHASE:

1)Prepares the Filesystem

During Prepare phase,RUN and PATCH filesystems are synchronized.By Default, Synchronization is done in incremental mode.
Full Synchronization is also available, if needed.

Changing APPS Password in R12.2

APPS Password in R12.2 can be changed either using AFPASSWD or FNDCPASSS utility. AFPASSWD is an enhancement to FNDCPASS.
To change APPS password , follow the below steps

1. Shut down MT Services

2.Use the syntax below to change password.

$AFPASSWD -c apps -s APPLSYS

Sample 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.

Note that, to change APPS password we need to give APPLSYS username in AFPASSWD syntax.
APPLSYS and APPS share the same password. Changing APPLSYS password using AFPASSWD utility changes APPS Password also.

3. After the password is changed, start only adminserver on RUN filesystem  using the script adadminsrvctl.sh.

Update the “apps” password in WLS Datasource as follows:
Log in to WLS Administration Console.
Click Lock & Edit in Change Center.
In the Domain Structure tree, expand Services, then select Data Sources.
On the “Summary of JDBC Data Sources” page, select EBSDataSource.
On the “Settings for EBSDataSource” page, select the Connection Pool tab.
Enter the new password in the “Password” field.
Enter the new password in the “Confirm Password” field.
Click Save.
Click Activate Changes in Change Center.

4. Start all the application services using adstrtal.sh

Sunday, 31 January 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



Monday, 18 January 2016

R12.2 Filesystem Layout


  • File System 1 (FS1)--Stores a complete copy of all  Applications and Middle Tier code
  • File System 2 (FS2)--Stores a complete copy of all Applications and Middle Tier code 
  • File System Non-Editioned (FS_ NE)--Stores data that is written or read from the file system,Report Outputs,Log Files etc.
FS1 and FS2 Rotate Run and Patch Designation

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;