03 December, 2019

Autoconfig is not generating scan alias in tnsnames.ora

When we run the autoconfig on application nodes TNSNAME.ORA IS ALSO CREATED BUT WITH VIRTUAL IP ENTRY IN HOST FIELD IT SHOULD BE WITH SCAN LISTENER ENTRY.

FND_DATABASES is a table which is part of the Net Services Topology Model. It gets updated or data gets extracted from it when adconfig runs. So according to the bug if s_update_scan is set to FALSE in the context file, then adconfig will extract the entries from FND_DATABASES.

Customer will need to keep in mind that if they clear the topology data (FND_CONC_CLONE.SETUP_CLEAN) the data in FND_DATABASES will be lost. In that case if they clear the topology data they would have to set s_update_scan to TRUE before running adconfig to reseed the topology tables.

This is expected behaviour in a RAC installation and the process works as following (this method is designed to synchronize scan parameters between the Context File and the FND_DATABASES table):

1. If s_update_scan is set to TRUE, The scan params from the context file are copied to FND_DATABASES and s_update_scan is reset to FALSE.


2. If s_update_scan is set to FALSE, The scan params are copied from FND_DATABASES to the context file.

Workaround:

1. Check the scan name and port from fnd_database table

select DB_NAME,DB_DOMAIN,SCAN_NAME,SCAN_PORT from apps.fnd_databases;

DB_NAME  DB_DOMAIN            SCAN_NAME             SCAN_PORT
-------- -------------------- -------------------- ----------
DEV    abc.com


2. If you find the scan name and port number are null, then update them manually.

update apps.fnd_databases set SCAN_NAME='hostname-scan',SCAN_PORT=1525 where DB_NAME='DEV';

Then commit;

3. Check the values again 

select DB_NAME,DB_DOMAIN,SCAN_NAME,SCAN_PORT from apps.fnd_databases;

DB_NAME  DB_DOMAIN            SCAN_NAME             SCAN_PORT
-------- -------------------- -------------------- ----------
DEV    abc.com                hostname-scan           1525 

4. Now run the autoconfig on application nodes. It should create tnsnames.ora with scan entry.





06 November, 2019

How to check RMAN backup status

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

31 October, 2019

How to find the current SCN, SCN and Archive Log

Oracle SCN (System Change Number) is a mechanism to maintain data consistency in Oracle database. A stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.

Checkpoint (CKPT) occurs when all modified database buffers in the Oracle SGA are written out to datafiles by the database writer (DBWn) process. It is a data structure that defines an SCN in the redo thread of a database. Checkpoints are recorded in the control file and each datafile header, and are a crucial element of recovery.

How to find current SCN in the database

Method-I
column current_scn format 99999999999999999999;
select current_scn from v$database; 

Method-II
select to_char(dbms_flashback.get_system_change_number) from dual;

Archive Log and SCN:

col first_change# for 999999999999999999
col next_change# for 999999999999999999
select name, thread#, sequence#, status, first_time, next_time 
from v$archived_log 
where &scn between FIRST_CHANGE# and NEXT_CHANGE#;

SCN and Timestamp:
SQL>select scn_to_timestamp(281840233193) as timestamp from dual;

TIMESTAMP
---------------------------------------------------------------------------
04-MAY-15 10.01.49.000000000 AM
SQL>col scn for 99999999999999999999;
SQL>select timestamp_to_scn(to_timestamp('05/04/2015 10:25:01','MM/DD/YYYY HH24:MI:SS')) as scn from dual;

                  SCN
---------------------
         281842111354
SQL>select timestamp_to_scn(to_timestamp('05042015102501','MMDDYYYYHH24MISS')) scn from dual; 
 SCN
---------------------
         281842111354

Other options are to query smon_scn_time and v$log_history. smon_scn_time is owned by SYS and have data worth of 5 days.

R12.2 Patching info

ADOP Patching:

adop phases:

prepare       : Prepare the instance for online patching.
apply         : Apply patch(es) to the Patch Edition. 
finalize      : Ready the instance for cutover. 
abort         : Abort the patching cycle. 
cutover       : Promote the Patch Edition to Run Edition. 
cleanup       : Drop obsolete objects and seed data from Old Editions.
actualize_all : Actualize all objects in the Patch Edition. 
cleanup_full  : Cleanup and drop Old Editions. 
abandon       : yes|no - Abandon failed patches.



adop patch log directory:

<INSTALL BASE>/fs_ne/EBSapps/log/adop


adop patch process cycle steps:

Download any required technology patches and unzip the contents. The patch contents may be unzipped into  $NE_BASE/EBSapps/patch.

1. Prepare the system for patching.

   source <EBS_ROOT>/EBSapps.env run

   $ adop phase=prepare

2. Apply technology patches to the Oracle Home under the Patch f/s using the information below.

   source <EBS_ROOT>/EBSapps.env patch

3. Apply any Oracle E-Business Suite patches planned for this patching cycle

   $ adop phase=apply patches=<patch_list>

4. After all patches have been successfully applied, complete the patching cycle.

   $ adop phase=finalize
   $ adop phase=cutover

Note: Below steps can be done when applications are up and running

5. source <EBS_ROOT>/EBSapps.env run
 
   $ adop phase=cleanup

6. To complete the process and synchronize the technology level between patch and run f/s.

   $ adop phase=fs_clone



adop hotpatch steps (we cannot abort hotpatch and abondon cannot be done):

Hotpatch which can apply directly on run fs

$ adop phase=apply patches=<patch_list> hotpatch=yes

After hotpatch please run phase=cleanup and phase=fs_clone to sync the run fs with patch fs to prepare for next patching cycle



adop re-apply patch forcefully:

If we try to re-apply the patch which is already applied/exists then adop patch terminates
with below message and by default it takes N when prompted. to overcome this we need to
re-apply patch with options=forceapply.
This Patch seems to have been applied already.
Would you like to continue anyway  [N] ? N *


$ adop phase=apply patches=<patch list> hotpatch=yes options=forceapply



adop deal with "Continue As If It Were Successful" error:

$ adop phase=apply patches=<patch list> abandon=no restart=yes flags=autoskip



To define workers in adop:



$ adop phase=apply patches=<patch list> workers=5



To define patchtop in adop:



$ adop phase=apply patches=<patch list> patchtop=<patch location base>



adop merge patch:



$ adop phase=apply patches=<patch list> merge=yes



Restarting adop From A Failed Session:




 $ adop phase=abort
 $ adop phase=cleanup cleanup_mode=full
 $ adop phase=fs_clone

Then reapply the patch



adop apply for language patch:



$ adop phase=apply patches=18023722_ESA:u18023722.drv



adop non-interactive with patch top and define driver:



$ adop phase=apply options=nocopyportion patchtop=$XLA_TOP/patch/115 patches=driver:xla5584908.drv



adop Steps to follow to skip the failed workers:



1. Use adctrl and select option#8 (This will not be visible) to skip thefailed jobs
2. Restart adop using "restart=yes" parameter

** If the failed jobs are numerous, then better to re-start this patch once again with autoskip option.

ie.  adop restart=no abandon=yes flags=autoskip

This command will restart the patch once again from starting onwards and will skip all the failures if any comes. But make sure to review the log file at the end of the patch application that you have skipped the failures that you want to.




Weblogic Server Smart Update Patching :

Steps to apply weblogic server smart update patch:


Refer Note:
How to Apply WebLogic Server (WLS) Patches Using Smart Update [Video] (Doc ID 876004.1)

1.Download the patch
2.Copy the files (for example, E5W8.jar and WGQJ.jar) and the patch-catalog_xxx.xml from the zip file to the target machine. You do not need the readme file.
Path: $FMW_HOME/utils/bsu/cache_dir

3.cd $FMW_HOME/utils/bsu

To install patch:
./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -patchlist=7FC9 -verbose -install

To Verify:
./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -status=applied -verbose -view | grep 7FC9

To Rollback:
./bsu.sh -remove -patchlist=7FC9 -prod_dir=$FMW_HOME/wlserver_10.3 -verbose

We can also apply the Smart Update patch in graphical (GUI) mode.




Steps to apply opatch on FMW Web Tier HOME :


Set the Environment as below (replace <INSTALL_BASE> path as required):

$ export ORACLE_HOME=$FMW_HOME/webtier
$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ opatch lsinventory


Apply opatch:

$ opatch apply




Steps to apply opatch on FMW oracle_common HOME :


Set the Environment as below:

$ export ORACLE_HOME=$FMW_HOME/oracle_common
$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ opatch lsinventory

Apply opatch:

$ opatch apply




Queries :

Query to check if a patch is applied in Oracle EBS R12.2.x:

In Oracle E Business Suite (ebs erp) R12.2.x you cannot query the AD_BUGS table to check if patches have been applied..
The AD_BUGS table may have entries for patches that were applied but later the patching cycle was aborted (not really applied).


The way to check whether a patch is really applied is to use the AD_PATCH.IS_PATCH_APPLIED PL/SQL function.

Usage:

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 (20034256);

or for single app tier installations:

select ad_patch.is_patch_applied('R12',-1,20034256) from dual;

Expected results:

EXPLICIT = applied
NOT APPLIED = not applied / aborted

Note: If you are sure patch is applied, but showing as not applied then do the following workaround.


1. Start adadmin after source the RUN FS env.
2. Select "2. Maintain Applications Files menu" in "AD Administration Main Menu".
3. In "Maintain Applications Files", select "4. Maintain snapshot information".
4. Select "2. Update current view snapshot" in the "Maintain Snapshot Information".
5. Select "1. Update Complete APPL_TOP" in the "Maintain Current View Snapshot Information".


Query to check current AD patchset:

SELECT a.application_short_name, b.patch_level
FROM fnd_application_vl a,fnd_product_installations b
WHERE a.application_id = b.application_id
  and application_short_name = 'AD';



Query to check patches applied correctly and in the expected sequence:



1.1.Run this sql statement:

   select * from ad_adop_session_patches order by end_date desc;

1.2. Run this piece of sql code:

   set pagesize 200;
   set linesize 160;
   column adop_session_id format 999999999999;
   column bug_number format a15;
   column status format a15;
   column applied_file_system_base format a23;
   column patch_file_system_base format a23;
   column adpatch_options format a15;
   column node_name format a15;
   column end_date format a15;
   column clone_status format a15;

   select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYS   TEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS
   from ad_adop_session_patches
   order by end_date desc;


Below are possible values of STATUS column:

N - Not Applied In the current node but applied in other nodes
R - Patch Application is going on.
H - Patch failed in the middle. (Hard Failure)
F - Patch failed in the middle but user tried to skip some failures.
S - Patch Application succeeded after skipping the failed jobs.
Y - Patch Application succeeded.
C - Reserved for clone and config_clone. Indicates clone completed



Query to Check AD and TXK C Patch levels:



SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

24 October, 2019

Active Users Concurrent Program Never Completes After Clone

Recently we did a clone (R12). Everything worked as expected, except for Active Users Concurrent program which never completed when submitted. The status remains Running – Normal for hours together. But “Prints environment variable values” concurrent program completed within no time.
This is how the issue was approached and resolved.
1. This is not a concurrent manager (FNDLIBR) issue, since the concurrent managers came up without any problem. – This eliminates concurrent manager to be part of the problem.
2. How about running some other concurrent program viz., Users of a responsibility or Active Users of a Responsibility. Same behavior as Active Users – request never completes.
3. Now it is clear that the problem is with concurrent programs which has (Oracle Reports) .rdf defined as an executable, since “Prints environment variable values” completed successfully.
Thinking it could be a clone issue, i recompiled all the reports using adadmin, still the same issue.
To get more information, i enabled trace for Active users concurrent program and resubmitted it. Strange, it didnt even generate a trace file, but a session had been established on the database. Checking for the sql being run by the session, returns nothing.

4. Decided to run the report manually from the command prompt. 
Atlast ….
Error: [cache directory location] does not exist.
Where is this defined ?
Checking $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf revealed that the cacheDir value defined (same as the missing directory) in this file did not physically exists on the server. Created the cache directory and
Re-ran the report manually from command prompt, Guess what, the active users concurrent request completed successfully and even generated a trace file.

17 August, 2019

Drop SQL Profile

Step-1: Check below SQL Profile: SYS_SQLPROF_7cd65e0000

SQL> select name from dba_sql_profiles;


SQL> select NAME,CATEGORY,TYPE,STATUS,TASK_ID,TASK_EXEC_NAME from dba_sql_profiles where NAME like '%7cd65e0000%';


Step-2: Drop below SQL Profile: SYS_SQLPROF_7cd65e0000

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_7cd65e0000');


SQL> select NAME,CATEGORY,TYPE,STATUS,TASK_ID,TASK_EXEC_NAME from dba_sql_profiles where NAME like '%7cd65e0000%';


Drop SQL Plan directives:
==========================

Step-2: Drop SQL Plan directives:

SQL> EXEC DBMS_SPD.flush_sql_plan_directive;

PL/SQL procedure successfully completed.

SQL> BEGIN
FOR rec IN (SELECT directive_id did FROM DBA_SQL_PLAN_DIRECTIVES)
LOOP
DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE (directive_id => rec.did);
END LOOP;
END;   2    3    4    5    6
  7  /

PL/SQL procedure successfully completed.

SQL>

Step-3:
Set the optimizer_adaptive_features to FALSE. They can Ref. the DOC ID 396009.1 for the recommended parameters for EBS.
--

SQL> show parameter optimizer_adaptive_features

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features          boolean     TRUE
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH;


Optimizer Parameters


Optimizer Parameters
 
ð  Release 12 uses cost based optimization. The following optimizer  parameters must be set as shown, and should not be changed.
 
ð  It is recommended to disable the adaptive optimizer features: adaptive plans,  automatic re-optimization, and SQL plan directives. 

ð  Post 12.1.0.2.170831 (Aug 2017) Bundle Patch for Windows Platforms or higher

ð  Remove optimizer_adaptive_features from pfile/spfile and replace it with optimizer_adaptive_plans=FALSE  and optimizer_adaptive_statistics=FALSE

ð  For more details Refer MOS Doc ID 1594274.1(Table 1.5 - Footnote 1).

_optimizer_autostats_job=FALSE #MP Turn off automatic statistics.
optimizer_adaptive_features = FALSE #MP 

Parallel Execution and Oracle RAC parameters


Parallel Execution and Oracle RAC parameters

ð  It is recommended to set the parameters PARALLEL_FORCE_LOCAL on each instance, to ensure that parallel requests do not span instances.

ð  As of 11gR2, EBS customers must set the value of this parameter to TRUE and then are no longer required to set parallel_instance_groups and instance groups for the purpose of preventing inter-instance sql parallelism in RAC environments.


parallel_force_local=TRUE


TEMP_UNDO_ENABLED


TEMP_UNDO_ENABLED


ð  TEMP_UNDO_ENABLED helps to reduce the amount of redo caused by DML on global temporary tables.

ð   Setting to TRUE may cause serious issues, such as ORA-55526, for distributed transactions (Ref. Bug 20712819). 

ð  The recommended value for systems using distributed transactions is currently FALSE (Pending ER 24286334). 

ð  If not using distributed transactions, TRUE will improve performance by eliminating REDO on permanent UNDO.


temp_undo_enabled = FALSE


Database Password Case Sensitivity

Database Password Case Sensitivity


ð  The default value of this parameter is TRUE, i.e. passwords are case-sensitive at the database level.

ð  Although the parameter is deprecated in Database 12c, it still needs to be explicitly set for Oracle E-Business Suite.

ð  To enable case-sensitivity of database passwords in Oracle E-Business Suite (supported with Oracle E-Business Suite Release 12.1.1 or higher), set the parameter to TRUE and follow the steps in Doc ID 1581584.1. Also, if on a release prior to Release 12.2,  apply patch 12964564.

ð  To disable case-sensitivity, set the parameter to FALSE.

sec_case_sensitive_logon = FALSE

PGA_AGGREGATE_LIMIT feature in 12c limits PGA memory usage


PGA_AGGREGATE_LIMIT feature in 12c limits PGA memory usage

·         The default value of PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter.
·          It will not exceed 120% of the physical memory size minus the total SGA size.



·         PGA_AGGREGATE_LIMIT cannot be set below its default value. If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
·         If total PGA memory usage is over PGA_AGGREGATE_LIMIT value. The sessions or processes that are consuming the most untunable PGA memory will be terminated.

·         Recommended value for PGA_AGGREGATE_LIMIT is 0.

·         pga_aggregate_limit = 0

08 August, 2019

FS_CLONE issues


FS_CLONE issues

Issue 1:

There is not enough space on the volume you have specified.
Oracle Universal Installer has detected that you currently have 0 MB available on the chosen volume. 750 MB of space is required for the software.

Solution:

Rapidwiz File System Upgrade Fails With "There is not enough space on the volume you have specified." Even Though There Is Sufficient Space (Doc ID 1942808.1)

ð   As per note id, we have requested SA team to reduce the mount point((/test/applmgr) size from 4TB to 2TB

Issue 2:

ERROR: RC-50410: Fatal: OUI Registration failed with an Abnormal Termination error during call from ouicli.pl.

Solution:

Fs_Clone fails with "ERROR: RC-50410: Fatal: OUI Registration failed with an Abnormal Termination error" (Doc ID 2095966.1)

ð  As per node id, we have unset the $ENV


Issue 3:

[UNEXPECTED]Error 1 occurred during Blocking ports on hostname01
[UNEXPECTED]Error 1 occurred during Blocking ports on hostname02

Patch filesystem services were running on hostname01 and hostname02

Solution:

ð   Issue got resolved after clearing the services related to patch filesystem(fs1).

01 August, 2019

REP-3000: Internal error starting Oracle Toolkit.


Error Message:

REP-3000: Internal error starting Oracle Toolkit.


We have noticed that Active Users program is failing with above error message.
Only report related programs are failing (like Active Users) all other programs are completing normal.

We suspected that this is due to DISPLAY/VNC session issue.

When we checked CONTEXT_FILE the DISPLAY setting was set to "hostname:1"

Solution:

DISPALY setting is set to hostname:1, So we need to start the vnc session with port 1.


Xlib: connection to "hostname:1.0" refused by server
Xlib: No protocol specified

Xlib: connection to "znohsvtvc513:1.0" refused by server
Xlib: No protocol specified

REP-3000: Internal error starting Oracle Toolkit.
REP-3000: Internal error starting Oracle Toolkit.

I have encountered  above issue when i started vnc session with my GUID.

After some time i realized that it should be started from "applmgr" user.

After starting vncserver :1 as "applmgr" user issue got resolved.





An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_linga_hostname. There may be a network configuration problem, or the TNS listener on node FNDFS_linga_hostname may not be running. Please contact your system administrator.

Error Message: 

Encountered below error while opening Concurrent request log/out file.

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_linga_hostname. There may be a network configuration problem, or the TNS listener on node FNDFS_linga_hostname may not be running. Please contact your system administrator.


Solution:

==> Added tns entry in MT node to fix the issue 



FNDFS_linga_hostname=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domain.com)(PORT=10911))
            (CONNECT_DATA=
                (SID=FNDFS)
            )
        )

vncserver: couldn't find "xauth" on your PATH.

Unable to start vnc session and encountering below error.


bash-3.2$ vncserver :0
vncserver: couldn't find "xauth" on your PATH.
bash-3.2$ which xauth
no xauth



Solution:

Exported below path and started the vnc server.

-bash-3.2$ export PATH=$PATH:/usr/openwin/bin:/usr/X11/bin

12 July, 2019

REP-0001: Unable to find the Report Builder message file. Please verify your installation.

Error: REP-0001: Unable to find the Report Builder message file.  Please verify your installation.


Active User and all other report related concurrent programs are completing with error with below error message.


REP-0001: Unable to find the Report Builder message file.  Please verify your installation.


Solution:

We have noticed that application environment is set to 1013 Oracle Home instead of 1012.

When we export ORACLE_HOME=/ABCD/product/1012 and executed report command from backend, it went successful.

But when we submit Active users it is again completing with same error message.

Finding:

After running Print Environment Variable program(Parameter: ORACLE_HOME) , it is still pointing to 1013 oracle home.

Modified the .bash_profile, env files to point 1012 oracle home and bounced the Application to resolve this issue.




11 July, 2019

The Concurrent Manager Process that was running this request has exited abnormally


Error : The Concurrent Manager Process that was running this request has exited abnormally




When I checked at back end for associated SID for the concurrent request, i couldn't find it.

Unable to cancel a concurrent request out of the queue that is stuck.

Cause

Tried to Cancel a concurrent request. Used the "Cancel Request" button from the Administer > Concurrent > Manager form.

Got the following message:

Request xxxxxx can no longer be cancelled. The Concurrent Manager Process that was running this request has exited abnormally. The ICM will mark this request as completed with error.


Solution

Manually cancel the request out of the queue with the following SQL against the offending
request id(s). This can be safely done while managers are up and running:

SQL> UPDATE fnd_concurrent_requests
  2  SET phase_code = 'C', status_code = 'X'
  3  WHERE request_id = '28979017';

1 row updated.


SQL> commit;



25 June, 2019

How to apply weblogic patch in R12.2

How to apply weblogic patch in R12.2:
============================

1. Copy the patches and move to the below location :

$FMW_Home/utils/bsu/cache_dir


2. Unzip patches in above location. When you unzip the patch you will get the jar file named with 4 character.
Ex : B47X. Use these jar files while applying patch.

3. Command to apply weblogic patch in R12.2 :

bash-3.2$ bsu.sh -install -patch_download_dir=$FMW_Home/utils/bsu/cache_dir -patchlist=B47X -prod_dir=$FMW_HOME/wlserver_10.3
Checking for conflicts................
Conflict(s) detected - resolve conflict condition and execute patch installation again
Conflict condition details follow:
Patch B47X is mutually exclusive and cannot coexist with patch(es): YIJF,LL4G,D33T,VFS8,Y5AP


4. Command to rollback the conflicts :

bsu.sh -remove -patchlist=YIJF -prod_dir=$FMW_HOME/wlserver_10.3
bsu.sh -remove -patchlist=LL4G -prod_dir=$FMW_HOME/wlserver_10.3
bsu.sh -remove -patchlist=D33T -prod_dir=$FMW_HOME/wlserver_10.3
bsu.sh -remove -patchlist=VFS8 -prod_dir=$FMW_HOME/wlserver_10.3
bsu.sh -remove -patchlist=Y5AP -prod_dir=$FMW_HOME/wlserver_10.3


Regular Errors:
===============

Error-1 :
=========

bash-3.2$ bsu.sh -install -patch_download_dir=$FMW_Home/utils/bsu/cache_dir -patchlist=B47X -prod_dir=$FMW_HOME/wlserver_10.3
Exception in thread "main" Exception in thread "Thread-0" java.lang.OutOfMemoryError: Java heap space
        at org.apache.xmlbeans.impl.store.Cur.createElementXobj(Cur.java:257)


Error-2:
=========

bash-3.2$ bsu.sh -install -patch_download_dir=$FMW_Home/utils/bsu/cache_dir -patchlist=B47X -prod_dir=$FMW_HOME/wlserver_10.3
Checking for conflicts...........Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
        at java.util.Arrays.copyOf(Arrays.java:2367)


Fix :
======

Update MEM_ARGS parameter in bsu.sh file after taking backup under $FMW_HOME/utils/bsu/
#!/bin/sh

JAVA_HOME="/a01/appsclone/CLONE/fs2/EBSapps/comn/util/jdk"

MEM_ARGS="-Xms2048m -Xmx2048m -XX:+UseParallelGC"

"$JAVA_HOME/bin/java" ${MEM_ARGS} -jar patch-client.jar $*


06 May, 2019

Set profile value at different levels from backend

Set profile value at SITE Level

declare 
   v_check            boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
   --exception
   --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'SITE'              
                                         , x_level_value              => null              
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

 Set profile value at Application Level

declare 
   v_check           boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_appl_name    varchar2(4)   := 'AR';
   v_appl_id         number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
   select application_id
     into v_appl_id
     from fnd_application
    where application_short_name = 'AR';
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'APPL'               
                                         , x_level_value              => v_appl_id              
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

 Set profile value at Responsibility Level

declare 
   v_check            boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_resp_name    varchar2(240)   := 'Purchasing Super User';
   v_resp_id         number;
   v_resp_app_id number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
    select responsibility_id      
         , application_id   
      into v_resp_id      
         , v_resp_app_id   
    from fnd_responsibility_tl  
    where responsibility_name = v_resp_name ;
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'RESP'               
                                         , x_level_value              => v_resp_id             
                                         , x_level_value_app_id   => v_resp_app_id) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end; 



 Set profile value at User Level

declare 
   v_check           boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_user_name    varchar2(240)   := 'XX1234';
   v_user_id         number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
    select user_id       
      into v_user_id       
      from fnd_user  
     where user_name = v_user_name ;
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'USER'               
                                         , x_level_value              => v_user_id             
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end; 

OEM Agent Installation

./agentDeploy.sh AGENT_BASE_DIR=/u001/oracle/product/agent13c OMS_HOST=oem-dev.xxx.com EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=*****...