Wednesday, August 9, 2017


Step by Step to apply patches in Oracle Apps R12.2. by using adop utility

Applying patch in ORACLE EBS R12.2 is different than R12.1.
I would like to share here the steps for applying patching in r12.2 with concepts and details.

From R12.2.0 onward oracle has introduced online patching functionality called ADOP.

Oracle EBS 12.2 introduces Online Patching, a new feature that greatly reduces the downtime that was needed in previous releases for application of Release Update Packs (RUPs), Critical Patch Updates (CPUs), and other patches and bug fixes of various kinds.

Note: The classic patching model is designed to minimize downtime by running as fast as possible, using whatever resources are needed. In contrast, the online patching model is designed to minimize downtime by allowing patching operations to be performed while users remain on the system.

As we know in R12.2, There are 3 File System Installed.

1: FS1 – (RUN_EDITION) Production file system that is used by online users when system is being patched.

2: FS2 – (PATCH_EDITION) Exact copy of production(fs1) used by the patching tools. Changes do not affect the running application(RUN_EDITION).

When patching is completed and we do CUTOVER, then patching tool swaps the FS1 and FS2 file systems.
It means, FS2 file system will become FS1 , which will be access by the users.


3: FS-NE (Non-Edition) – This is a non-edition file system which stores data that is stored in a file system like log file, report file and patch top files.

There are below 5 Phases of ADOP(online patching):

1) PREPARE
2) APPLY
3) FINALIZE
4) CUTOVER
5) CLEANUP



Steps:-
1. Download the patch and unzip on patch_top.

 eg:  [appldev@test Desktop]$ cd $PATCH_TOP
        [appldev@test patch]$ pwd
        /u01/dev/devapps/apps/fs_ne/EBSapps/patch

2. Must go through Readme.html or readme.txt.

3. Prepare the system for patching

a. we can run the .env file . or the adop utility sets its own environment.
b. Then run the below command for prepare the System for patching:

$ adop phase=prepare

4. Wait for the prepare phase to be complete.
Please check the status from below points:

$ adop -status

Enter the APPS username: apps
Enter the APPS password:

5. After completion Prepare phase, go to next phase called Apply phase:

$adop phase=apply patches=1234567

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials...


6 . After completion of apply phase, goto next phase called Finalize phase:
i.e like Ready the instance for cutover

$ adop phase=finalize

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials...


7. After completion of Finalize phase, goto next phase called Cutover:

$ adop phase=cutover

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials...


Note: Keep checking Status after every Phases.

8. After completion of Cutover phase, run next Cleanup Phase
We can do this step after instance is released to Customer.

$adop phase=cleanup

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:


9. After completion of Cleanup Phase, run next Fs Clone
This is an option step.

$ su appldev
$ . /u01/dev/devapps/apps/EBSapps.env

Please choose the environment file you wish to source [R/P]: R

$adop phase=fs_clone


Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:



Note:
If there are some reason either the
prepare or apply phase failed or any problems then you can abort this patching cycle.


$adop phase=abort

But always remember, after running abort, you must always run a full cleanup as:

$adop phase=cleanup cleanup_mode=full

So, this will remove all columns that were added by the patches but are no longer needed because of the abort.

And If that columns are not removed, then that may create problems in a later patching cycle.

Monday, July 3, 2017


Workflow notification mailer setup in oracle apps R12 - Office 365

Check where telnet services are enabled to mailserver

[root@vis Desktop]# telnet smtp.office365.com 587
Trying 132.245.41.98...
Connected to smtp.office365.com.

if not install tel net using yum or rpm

[root@vis Desktop]# yum install telnet

outbound
---------
1) Configure the Mailer Outbound with the given SMTP server from backend as  

  a. Run the $FND_TOP/sql/afsvcpup.sql script to set below parameters

   # cd $FND_TOP/sql
   # sqlplus apps/apps @afsvcpup.sql 

  b. Component Id: 10006  SSL Trust store: 10243 
     /u01/vis/visapps/apps/fs1/EBSapps/comn/util/jdk64/jre/lib/security/cacerts

  c. Component Id: 10006   Outbound Server Name: 10079 
     server name: smtp.office365.com

  d. Component Id: 10006  OutboundUser: 10302
     Username: workflowtest-elh@expolanka.com

  e. Component Id: 10006  Outbound Security Protocol: 10341 
     Connection Security: STARTTLS

  f. Component Id: 10006  Outbound Thread Count: 10086 
     Outbound Thread Count:  1 

2) Set the Outbound user password by running the script wfmlrpwupd_out.sql as below. 
   sqlplus apps/apps @wfmlrpwupd_out.sql  <Outbound_pwd> <Mailer_comp_ID>
   *** copy the wfmlrpwupd_out.sql to user home and set the permission.

3) Now disable the Inbound Processing from backend 
   
 a. Component Id: 10006 , Inbound Thread Count: 10082 
    Inbound Thread Count: 0

4) Set Reply-to Address
   Component Id: 10006 , Reply-to Address: 10089    
   Reply-to Address: workflowtest-elh@expolanka.com

* Try out test mailer and see if the test mail goes. 

* Once the above is successful then go for the inbound processing.  


5) Go to OAM and start all try to see if the mailer comes up for the outbound processing.

inbound 
----------

1) Configure the Mailer Inbound with given IMAP server from backend as below 

 a. Run the $FND_TOP/sql/afsvcpup.sql script to set below parameters

   # cd $FND_TOP/sql
   # sqlplus apps/apps @afsvcpup.sql 

 b. Component Id: 10006   Inbound Server: Name10069   
    Server Name:  outlook.office365.com:143

 c. Component Id: 10006  Username : 10054
    Username: workflowtest-elh@expolanka.com

 d. Component Id: 10006  Inbound Security Protocol: 10340 
    IMAP Security Protocol: STARTTLS

 e. Component Id: 10006  Inbound Thread Count: 10082 
    Inbound Thread Count: 1 

2)  Set the Inbound user password by running the script wfmlrpwupd_in.sql as below. 
     
     # sqlplus apps/apps @wfmlrpwupd_in.sql
      <Inbound_pwd> <Mailer_comp_ID>          comp_id: 10006

      *** copy the wfmlrpwupd_out.sql to user home and set the permission.

3) Now stop the Notification Mailer component from Service Components UI page and then start it 

4) Verify that Mailer is running fine now and Inbound is enabled and all the Inbound parameters are correctly set 

5) Respond to some email notifications and verify that Mailer is processing the Inbound messages 

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

Purge WF_NOTIFICATION_OUT queue cd $FND_TOP/patch/115/sql
sqlplus apps/ @wfntfqup.sql APPS (appspwd) APPLSYS
e.g sqlplus apps/apps @wfntfqup.sql APPS apps APPLSYS

This purges the WF_NOTIFICATION_OUT queue and rebuilds 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.
Only notifications with mail_status = 'MAIL' and status = 'OPEN' will be re-enqueued in the WF_NOTIFICATION_OUT queue and sent by the mailer.

Setup Test address/override address for WF
Below is the script to update the override address from backend. You do not need the verification code to set the override address using the below script

update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address'
);


Workflow From Address Update :
sqlplus apps/ @$FND_TOP/sql/afsvcpup.sql

Enter Component Id: 10006

Enter the Comp Param Id to update : 10065

Enter a value for the parameter : Lenovo DEV Workflow Mailer"

set overriding address:
update FND_SVC_COMP_PARAM_VALS
set parameter_value = 'Configuration.Workflows@abc.com'
where PARAMETER_ID = 10057;


Scipt to see workflow configuration
SQL> select p.parameter_id,p.parameter_name,v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;


SQL to monitor (check status) of Workflow Notification Mailer (Java)

SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;

sqlplus -s system/${PASSWD}@${ORACLE_SID} << SQLEND >${script}/apps/wf_status.out
set linesize 90
col COMPONENT_NAME format a50
col COMPONENT_status format a20
spool ${script}/apps/wf_status.lst
select COMPONENT_ID,COMPONENT_NAME,COMPONENT_STATUS from apps.fnd_svc_components
where COMPONENT_ID not in (10000,10001,10020,10021,10022) and COMPONENT_STATUS <> 'RUNNING';

select mail_status, count(*) from apps.wf_notifications
where status = 'OPEN'
and mail_status in ('MAIL','ERROR')
group by mail_status;
spool off"



To see error message for a workflow notification
SQL> select ERROR_MESSAGE from wf_item_activity_statuses_v WHERE NOTIFICATION_ID = 7377659;

Here are steps/events for Oracle Workflow Notification Outbound Processing(eMail from Oracle Applications Workflow to Users)

1.When workflow Engine determines that a notification message must be sent, it raises an event in BES (Business Event System) oracle.apps.wf.notifications.send
Event is raised with Notification ID (NID) as event key
2. There is seeded subscription to this Event
3. Event is placed on WF_DEFERRED agent
4.Event is dequeued from WF_DEFERRED and subscription is processed
5. Subscription places event message to WF_NOTIFICATION_OUT agent.
6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (If Test Address/Override Address is set then email is sent to Test Address

E-Mail Notification is sent if all below conditions are true
a) Notification status is OPEN or CANCELED and
b) Notification mail_status is MAIL or INVALID and
c) Recipient Role has a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred Agent Listener is running
e) Workflow Notification Mailer is running

To check a) & b) run below query
SELECT status, mail_status FROM wf_notifications WHERE notification_id = ‘&NID’;

mail_status >> SENT means mail has gone out of mailer to user

To check c) run below query
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(’&recipient_role’);

To check d) & e) Use OAM (Oracle Application Manager)

Monday, June 26, 2017


Cloning fails at 50% with RC-00118 or RC-50004 after creating control files



-------------------ADX Database Utility Finished---------------
RC-00118: Error occurred during creation of database
Raised by oracle.apps.ad.clone.ApplyDatabase

StackTrace:
java.lang.Exception: Control file creation failed
at oracle.apps.ad.clone.ApplyDatabase.doConf(ApplyDatabase.java:635)
at oracle.apps.ad.clone.ApplyDatabase.doApply(ApplyDatabase.java:473)
at oracle.apps.ad.clone.ApplyDatabase.<init>(ApplyDatabase.java:366)
at oracle.apps.ad.clone.ApplyDBTier.<init>(ApplyDBTier.java:110)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
at java.lang.Thread.run(Unknown Source)
====================================================================




Cause:

The listener.ora contains the source listener name

This was originally resolved in CloneContext.java (12.1 - 120.92.12010000.16) but is present again in version: 120.92.12010000.24

This is reported in Bug :12598630 - R12 CLONING FAILS AT 50% DUE TO LISTENER.ORA CONTAINING SOURCE NAME


Workaround:

The Workaround is to make the change on the temporary stage context file prior to the file copy:

1. edit CTXORIG.xml either after adpreclone.pl or before adcfgclone.pl
2. change the s_db_listener value from Source to Target
3. copy files to target
4. run perl adcfgclone.pl dbTier

If the Clone has already failed, the workaround/solution would be:

1. correct the s_db_listener value in the Target context file
2. shutdown the target Database
3. run: perl adcfgclone.pl dbtier /path/to/contextfile.xml



Change Host Name in Red Hat and Oracle Linux

Make sure you are logged in as root and move to /etc/sysconfig and open the network file in vi.
cd /etc/sysconfig
vi network
Look for the HOSTNAME line and replace it with the new hostname you want to use. In this example I want to replace localhost with redhat9.
HOSTNAME=vis.expolanka.com
When you are done, save your changes and exit vi. Next we will edit the /etc/hosts file and set the new hostname.
vi /etc/hosts
In hosts, edit the line that has the old hostname and replace it with your new one.
192.168.1.110     vis.expolanka.com    vis
Save your changes and exit vi. The changes to /etc/hosts and /etc/sysconfig/network are necessary to make your changes persistent (in the event of an unscheduled reboot).
Now we use the hostname program to change the hostname that is currently set.
hostname vis.expolanka.com 
And run it again without any parameters to see if the hostname changed.
hostname
Finally we will restart the network to apply the changes we made to /etc/hosts and /etc/sysconfig/network.
service network restart


You can follow either the first option OR the second one.


Type the following command to enter GUI mode.
[root@localhost ~]# system-config-network
  • Go to “DNS Configuration”.
  • Modify your preferred hostname and click OK.
  • Click ” Save & Quit”.
  • Save and reboot your server.



Wednesday, June 14, 2017


Disable log_archive_dest_3

disable log_archive_dest_3

[root@ebiz Desktop]# su - oraebiz
[oraebiz@ebiz ~]$ 
[oraebiz@ebiz ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 11:53:35 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter log_archive_config;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_25     string
log_archive_dest_26     string
log_archive_dest_27     string
log_archive_dest_28     string
log_archive_dest_29     string
log_archive_dest_3     string SERVICE=prodstb2 NOAFFIRM ASYN
C VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE) DB_UNIQUE_NAME=PR
ODSTB2

SQL> select dest_name,status,error from v$archive_dest_status where dest_id=3;

DEST_NAME
--------------------------------------------------------------------------------
STATUS  ERROR
--------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_3
BAD PARAM


SQL> alter system set log_archive_dest_3='';

System altered.

You could also disable it by setting the state to DEFER:

SQL> alter system set log_archive_dest_state_3=defer;






Scheduling Workflow Mailer Stop and Start events as good practice

The Workflow Notification Mailer runs on the generic service called Workflow Mailer Service, which in turn uses the Standard workshift, active 24 hours every day. This means the notification mailer runs non-stop. Not allowing the mailer to restart periodically may present some issues:
  • The corresponding log file corresponding to the Workflow Mailer Service will grow several gigabytes large and eventually the OS will error due to I/O file handling.
  • Some SMTP servers and IMAP servers used for outbound and inbound processing respectively, have time limits to the sessions connected to them. If the mailer reaches those time limits it will encounter a connection error and will be abnormally brought down.
  • Similarly the same might happen to the session the mailer establishes to retrieve HTML and framework content from the application server. This is less likely though but it has been seen.
  • The size of the INBOX folder in the IMAP server won't get expunged as long as the mailer does not refreshes the session. For a purged INBOX folder it takes the mailer to restart - and for parameter Expunge Inbox on Close to be set to Yes.
In order for the issues above to be minimized, a good practice is to schedule the Mailer to automatically go down and start up periodically, daily preferably. This can be done by using the event schedule feature provided in the Workflow Mailer configuration. This can be done as follows:First, connect to EBS using the responsibility Workflow Administrator Web Applications and go to Workflow ManagerThen edit the Notification Mailer configuration. Continue going through the train stages until you reach the Schedule Events stage:



In the screen shot above the stopping event is set to happen at 11:50 PM and the starting event at 11:55 PM, and these are set to repeat every using 1440 minutes as the interval. If no interval value is specified the stop/start events are raised just once

Monday, June 12, 2017


R12.0.6+ : Oracle Payables Invoice Data Collection Test (Doc ID 732163.1)

APPLIES TO:

Oracle Payables - Version 12.0.6 and later
Information in this document applies to any platform.

MAIN CONTENT

12.0.6 - 12.2 Legacy: :SQLAP:Invoice Data Data Collection


Description

This diagnostic test will display all of the data for all of the tables used to process an invoice in Payables.

This test is applicable for all Payables invoice and payment issues.

No validation will be done on the data collected, therefore no error or warning messages will be provided.

EBS Applicable Releases: 12.0.6 - 12.2
Application Name: Oracle Payables
Test Group: Invoice
Test Name: Invoice Data
Note: Click Here to Get Latest XML File(APListXml.xml 120.x.support.version uploaded on 16-Jun-2014).  Please review the README.txt file included in the zip file for installation instructions. 
RECOMMENDED:  Install and use the standalone version of this diagnostic.  It provides many advantages to the diagnostic framework version referenced in this document.  Please see Doc ID 1677861.1 for more details on the advantages it provides and for instructions on how to install and run.


Usage Instructions


To execute the test, do the following:
  1. Start Oracle E-Business Suite
  2. Connect to responsibility Application Diagnostics
  3. Select the Diagnose menu option
  4. Click button Select Application and select Application "Payables"
  5. Scroll down to group "Invoice"
  6. Select test name "Invoice Data"
  7. Input Parameters (* required)
    • Responsibility Id (LOV) *  
    • Search Criteria (LOV)  
    • Supplier Name (LOV)  
    • Invoice Id (LOV) *  
    • Include GL Tables (Yes or No) (LOV)  
    • Include Related Data (Yes or No) (LOV)  
    • Max Rows Displayed  
  8. Output contains
    • Ledger Definition
    • Secondary Ledgers
    • Setup
    • Summary
    • Trial Balance
    • Invoice
    • Payment
    • Tax
    • Accounting
    • 11.5 AX Accounting ( if exists )
    • 11.5 AP Accounting ( if exists )
    • General Ledger
    • 11.5 MRC ( if exists )
    • Supplier
    • Purchasing
    • Encumbrance
    • Prepayment
    • Payment Document and Bank
    • Cash Management
    • Treasury Confirmation
    • Currency
    • Argentina Localizations (if applicable)

 RMAN Recovery Catalog About Recovery Catalog RMAN recovery catalog Is another database which is out of your normal databases or which is o...