In this article we are going to setup EBS Notification Mailer with gmail account.

Host Name	smtp.gmail.com
SSL Port	465
SSL required	Yes
User Name	workflowebs@gmail.com
Password	Your gmail password

Host Name	imap.gmail.com
SSL Port	993
SSL Required	Yes
User Name	workflowebs@gmail.com
Password	Your gmail password

Setup Steps:

Step 1. Create a GMAIL account
Step 2. Create 2 new folders as PROCESS and DISCARD



Step 3. Turn on less secure access using below URL by logging into account from browser
https://www.google.com/settings/security/lesssecureapps


Step 4: Check that IMAP is turned on
On your computer, open Gmail.
In the top right, click Settings Settings.
Click Settings.
Click the Forwarding and POP/IMAP tab.
In the "IMAP Access" section, select Enable IMAP.
Click Save Changes.





Step 5: Install GMAIL SSL Certificates into the Concurrent Manager Tier:

  a) Copy the following GMAIL SSL Certificate into a file called gamil.cer



-----BEGIN CERTIFICATE-----

MIIDWzCCAsSgAwIBAgIKaNPuGwADAAAisjANBgkqhkiG9w0BAQUFADBGMQswCQYD

VQQGEwJVUzETMBEGA1UEChMKR29vZ2xlIEluYzEiMCAGA1UEAxMZR29vZ2xlIElu

dGVybmV0IEF1dGhvcml0eTAeFw0xMTAyMTYwNDQzMDRaFw0xMjAyMTYwNDUzMDRa

MGgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpDYWxpZm9ybmlhMRYwFAYDVQQHEw1N

b3VudGFpbiBWaWV3MRMwEQYDVQQKEwpHb29nbGUgSW5jMRcwFQYDVQQDEw5pbWFw

LmdtYWlsLmNvbTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAqfPyPSEHpfzv

Xx+9zGUxoxcOXFrGKCbZ8bfUd8JonC7rfId32t0gyAoLCgM6eU4lN05VenNZUoCh

L/nrX+ApdMQv9UFV58aYSBMU/pMmK5GXansbXlpHao09Mc8eur2xV+4cnEtxUvzp

co/OaG15HDXcr46c6hN6P4EEFRcb0ccCAwEAAaOCASwwggEoMB0GA1UdDgQWBBQj

27IIOfeIMyk1hDRzfALz4WpRtzAfBgNVHSMEGDAWgBS/wDDr9UMRPme6npH7/Gra

42sSJDBbBgNVHR8EVDBSMFCgTqBMhkpodHRwOi8vd3d3LmdzdGF0aWMuY29tL0dv

b2dsZUludGVybmV0QXV0aG9yaXR5L0dvb2dsZUludGVybmV0QXV0aG9yaXR5LmNy

bDBmBggrBgEFBQcBAQRaMFgwVgYIKwYBBQUHMAKGSmh0dHA6Ly93d3cuZ3N0YXRp

Yy5jb20vR29vZ2xlSW50ZXJuZXRBdXRob3JpdHkvR29vZ2xlSW50ZXJuZXRBdXRo

b3JpdHkuY3J0MCEGCSsGAQQBgjcUAgQUHhIAVwBlAGIAUwBlAHIAdgBlAHIwDQYJ

KoZIhvcNAQEFBQADgYEAxHVhW4aII3BPrKQGUdhOLMmdUyyr3TVmhJM9tPKhcKQ/

IcBYUev6gLsB7FH/n2bIJkkIilwZWIsj9jVJaQyJWP84Hjs3kus4fTpAOHKkLqrb

IZDYjwVueLmbOqr1U1bNe4E/LTyEf37+Y5hcveWBQduIZnHn1sDE2gA7LnUxvAU=

-----END CERTIFICATE-----



   b) Save the file gmail.cer into $AF_JRE_TOP/lib/security/ directory

   c) Change permissions on gmail.cer to 755.

       $  chmod 755 gmail.cer

   d) Install the SSL Certificate into:

        $  keytool -importcert -trustcacerts -keystore $AF_JRE_TOP/lib/security/cacerts  -storepass changeit -alias gmail-lnx_chainnedcert -file gmail.cer 

   e) Now configure Work flow mailer using edit button and start the mailer services.





Step 6: Update override address to get mail on my inbox.

update fnd_svc_comp_param_vals
set    parameter_value = 'funoracleappsdba@gmail.com'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address');



Step 7: Run the Mailer command from the Concurrent Manager Tier to test the IMAP Connection:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=imap.gmail.com -Dport=993 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount= -Dpassword= -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer



Step 8: Run the Mailer command from the Concurrent Manager Tier to Test the SMTP Connection:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=smtp.gmail.com -Dport=465 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount= -Dpassword= -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer


Step 9: Send a test mail and verify. You might need to check your spam folder to get the mail. Add mail-id workflowebs@gmail as trusted contact.













REF:Using GMAIL's IMAP & SMTP Server Configuring the Oracle Workflow Mailer (Doc ID 2052059.1)

We can use below script to update override address from backend in Oracle Apps.

• Check the workflow services using the below query.

set pagesize 400
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

• Check  Current Override Email Address 

select fscpv.parameter_value
    from fnd_svc_comp_params_tl fscpt
    ,fnd_svc_comp_param_vals fscpv
    where fscpt.display_name = 'Test Address'
    and fscpt.parameter_id = fscpv.parameter_id; 

PARAMETER_VALUE
--------------------------------------------------------------------------------
xyz.abc@domain.com

• Update Overide address from back-end using the below query

update fnd_svc_comp_param_vals
set    parameter_value = 'newoverride@terex.com'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address');

In this article we are going to see how we can change our Public, Private,VIP and Scan IP's.

First we need to change the RAC configuration before we make any changes to server level IP's.

Changing Public and Private IPs in RAC Cluster.

Get the public and private Interconnect information

[grid@racnode1 ~]$ oifcfg getif
eth1  192.168.56.0  global  public
eth2  10.0.0.0  global  cluster_interconnect


Current IP Configurations

192.168.56.10   racnode1     
192.168.56.11   racnode2     
10.10.0.10      racnode1-priv
10.10.0.11      racnode2-priv
192.168.56.111   racnode1-vip 
192.168.56.112   racnode2-vip 
192.168.56.121   racdb-scan   
192.168.56.122   racdb-scan   
192.168.56.123   racdb-scan  

New IP Configurations that will done

10.1.1.30   racnode1     
10.1.1.31   racnode2     
20.20.20.21      racnode1-priv
20.20.20.22     racnode2-priv
10.1.1.40   racnode1-vip 
10.1.1.41   racnode2-vip 
10.1.1.50   racdb-scan   
10.1.1.51   racdb-scan   
10.1.1.52   racdb-scan 

Check current status of Cluster

crsctl stat res -t
[grid@racnode1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       racnode1                 STABLE
               ONLINE  ONLINE       racnode2                 STABLE
ora.PROD.dg
               ONLINE  ONLINE       racnode1                 STABLE
               ONLINE  ONLINE       racnode2                 STABLE
ora.asm
               ONLINE  ONLINE       racnode1                 Started,STABLE
               ONLINE  ONLINE       racnode2                 Started,STABLE
ora.net1.network
               ONLINE  ONLINE       racnode1                 STABLE
               ONLINE  ONLINE       racnode2                 STABLE
ora.ons
               ONLINE  ONLINE       racnode1                 STABLE
               ONLINE  ONLINE       racnode2                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       racnode2                 STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       racnode1                 STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       racnode1                 STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       racnode1                 169.254.112.86 10.10
                                                             .0.10,STABLE
ora.cvu
      1        ONLINE  ONLINE       racnode1                 STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       racnode1                 Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       racnode1                 STABLE
ora.prod.db
      1        ONLINE  ONLINE       racnode1                 Open,STABLE
      2        ONLINE  ONLINE       racnode2                 Open,STABLE
ora.racnode1.vip
      1        ONLINE  ONLINE       racnode1                 STABLE
ora.racnode2.vip
      1        ONLINE  ONLINE       racnode2                 STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       racnode2                 STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       racnode1                 STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       racnode1                 STABLE
--------------------------------------------------------------------------------



Stop below services. Make sure these services are stopped on all RAC nodes.
>srvctl stop database -d prod
>srvctl stop  mgmtdb
>srvctl stop MGMTLSNR
>srvctl stop nodeapps -f


Delete previous PUBLIC  IP Configuration

[grid@racnode1 ~]$ oifcfg getif
eth1  192.168.56.0  global  public
eth2  10.0.0.0  global  cluster_interconnect


 oifcfg  delif -global eth1

Redefine Public IP

oifcfg  setif -global eth1/10.1.1.0:public

Stop Cluster Services
crsctl stop cluster -all

Change system level IP  Public.
Make changes in /etc/hosts or DNS as configured.
crsctl start cluster -all

Verify the changes.
[grid@racnode1 ~]$ oifcfg getif
eth2  10.0.0.0  global  cluster_interconnect
eth1  10.1.1.0  global  public



Changing Private IP 

Note: Dont change Private and Public IP together.

After 11.2.0.2 we cannot delete private interconnect directly. We need to add a private interconnect and restart clusterware after system level IP's are changed then we will remove old private interconnect.

Redefine Private IP

# oifcfg  setif -global eth2/20.20.20.0:cluster_interconnect

Check new changes are done

[grid@racnode1 ~]$ oifcfg  getif
eth2  10.0.0.0  global  cluster_interconnect--Old Entry
eth1  10.1.1.0  global  public
eth2  20.20.20.0  global  cluster_interconnect--New Entry

crsctl stop cluster -all

Change system level IP of Private  interconnect.
Make changes in /etc/hosts or DNS as configured.
crsctl start cluster -all


Delete Old Private Interconnect

# oifcfg  delif -global eth2/10.0.0.0

[root@racnode1 grid]# oifcfg getif
eth2  20.20.20.0  global  cluster_interconnect
eth1  10.1.1.0  global  public


Changing VIP of RAC cluster

Current VIP details:
[grid@racnode1 ~]$ srvctl config nodeapps
Network 1 exists
Subnet IPv4: 192.168.56.0/255.255.255.0/eth1, static
Subnet IPv6: 
Ping Targets: 
Network is enabled
Network is individually enabled on nodes: 
Network is individually disabled on nodes: 
VIP exists: network number 1, hosting node racnode1
VIP Name: racnode1-vip
VIP IPv4 Address: 192.168.56.111
VIP IPv6 Address: 
VIP is enabled.
VIP is individually enabled on nodes: 
VIP is individually disabled on nodes: 
VIP exists: network number 1, hosting node racnode2
VIP Name: racnode2-vip
VIP IPv4 Address: 192.168.56.112
VIP IPv6 Address: 
VIP is enabled.
VIP is individually enabled on nodes: 
VIP is individually disabled on nodes: 
ONS exists: Local port 6100, remote port 6200, EM port 2016, Uses SSL false
ONS is enabled
ONS is individually enabled on nodes: 
ONS is individually disabled on nodes: 

Make sure nodeapps service are stopped
Make changes in /etc/hosts or DNS


Run below commands to change from root user

# srvctl modify nodeapps -n racnode1 -A 10.1.1.40/255.255.255.0/eth1
# srvctl modify nodeapps -n racnode2 -A 10.1.1.41/255.255.255.0/eth1


Verify changes are done 
[root@racnode1 grid]# srvctl config nodeapps
Network 1 exists
Subnet IPv4: 10.1.1.0/255.255.255.0/eth1, static
Subnet IPv6: 
Ping Targets: 
Network is enabled
Network is individually enabled on nodes: 
Network is individually disabled on nodes: 
VIP exists: network number 1, hosting node racnode1
VIP Name: racnode1-vip
VIP IPv4 Address: 10.1.1.40
VIP IPv6 Address: 
VIP is enabled.
VIP is individually enabled on nodes: 
VIP is individually disabled on nodes: 
VIP exists: network number 1, hosting node racnode2
VIP Name: racnode2-vip
VIP IPv4 Address: 10.1.1.41
VIP IPv6 Address: 
VIP is enabled.
VIP is individually enabled on nodes: 
VIP is individually disabled on nodes: 
ONS exists: Local port 6100, remote port 6200, EM port 2016, Uses SSL false
ONS is enabled
ONS is individually enabled on nodes: 
ONS is individually disabled on nodes: 

Start nodeapps
srvctl start nodeapps




Changing SCAN IP in RAC Cluster


Check the current status SCAN IP address on  DNS
[grid@racnode1 ~]$ nslookup racdb-scan
Server: 192.168.56.101
Address: 192.168.56.101#53

Name: racdb-scan.himvirtualdns.lab
Address: 192.168.56.121
Name: racdb-scan.himvirtualdns.lab
Address: 192.168.56.122
Name: racdb-scan.himvirtualdns.lab
Address: 192.168.56.123



Check the current status SCAN-VIP in the resource file

cd $GRID_HOME/bin

[grid@racnode1 ~]$ srvctl config scan
SCAN name: racdb-scan, Network: 1
Subnet IPv4: 192.168.56.0/255.255.255.0/eth1, static
Subnet IPv6: 
SCAN 0 IPv4 VIP: 192.168.56.121
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 1 IPv4 VIP: 192.168.56.122
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 2 IPv4 VIP: 192.168.56.123
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 


Update NEW SCAN IP address in the DNS server.

[root@racnode1 grid]# nslookup racdb-scan
Server: 192.168.56.101
Address: 192.168.56.101#53

Name: racdb-scan.himvirtualdns.lab
Address: 10.1.1.51
Name: racdb-scan.himvirtualdns.lab
Address: 10.1.1.52
Name: racdb-scan.himvirtualdns.lab
Address: 10.1.1.50



Stop Scan resource before modifying the CRS resource file.

[root@racnode1 grid]# srvctl stop scan_listener
[root@racnode1 grid]# srvctl stop scan
[root@racnode1 grid]# srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is not running
SCAN VIP scan2 is enabled
SCAN VIP scan2 is not running
SCAN VIP scan3 is enabled
SCAN VIP scan3 is not running


[root@racnode1 grid]# srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is not running
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is not running
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is not running


# srvctl modify scan -n racdb-scan

Verify that the change

# srvctl config scan

[root@racnode1 grid]# srvctl config scan
SCAN name: racdb-scan, Network: 1
Subnet IPv4: 10.1.1.0/255.255.255.0/eth1, static
Subnet IPv6: 
SCAN 0 IPv4 VIP: 10.1.1.50
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 1 IPv4 VIP: 10.1.1.51
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 2 IPv4 VIP: 10.1.1.52
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 


Start SCAN and the SCAN listener

# srvctl start scan
# srvctl start scan_listener

In this article, I am going to share the steps to connect 2 machines running on Virtual Box over different laptops.

I have 2 laptops with below Configuration. I thought to use them to make a multinode environment over virtual machines which I will use for future articles on EBS/RAC and other topics.



Laptop 1:

16 GB RAM
1 TB Hardisk
Model: FUJITSU A555
Host OS: Windows 10
Virtualization Tool: Oracle Virtual Box(OEL 6.4)


Laptop 2:

16 GB RAM
2 TB Hardisk
Model: Lenovo T430
Host OS: Linux Mint 18
Virtualization Tool: Oracle Virtual Box((OEL 6.4)


and an ethernet cable to connect both laptops.

Setup:

1. Plug in the ethernet cable.
2. On Laptop 1 Go to network adapter settings and select Ethernet adapter and enter static IP.

XML Reports Failing

While Running XML reports getting below error

Error:

 [6/14/18 12:02:58 PM] [main] Initialization Parameters: oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
[6/14/18 12:02:58 PM] [Thread-91] Service thread starting up.
[6/14/18 12:02:58 PM] [Thread-92] Service thread starting up.
[6/14/18 12:02:58 PM] [EXCEPTION] [OPPServiceThread0] java.sql.SQLException: ORA-24067: exceeded maximum number of subscribers for queue APPLSYS.FND_CP_GSM_OPP_AQ
ORA-06512: at "APPS.FND_CP_OPP_IPC", line 85
ORA-06512: at line 1

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.initAQ(OPPAQMonitor.java:567)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.init(OPPAQMonitor.java:543)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.initialize(OPPAQMonitor.java:91)
at oracle.apps.fnd.cp.opp.OPPServiceThread.init(OPPServiceThread.java:94)
at oracle.apps.fnd.cp.gsf.BaseServiceThread.run(BaseServiceThread.java:144)


SOLUTION:

• Stop OPP Manager

• Connect to Apps User

SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

  COUNT(*)
----------
   10

• Take a back up of the table :

SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_14jun18 as select * from applsys.FND_CP_GSM_OPP_AQTBL;

Table created.

• Connect to Applsys User and run below

SQL> exec dbms_aqadm.purge_queue_table('FND_CP_GSM_OPP_AQTBL', null, null);


PL/SQL procedure successfully completed.


SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

  COUNT(*)
----------
         0

• Connect as APPS user and run below

SQL>@$FND_TOP/patch/115/sql/afopp002.sql
Enter value for 1: APPLSYS
Enter value for 2: apps
Connected.

PL/SQL procedure successfully completed.

SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();


Start OPP Manager and Validate.