Use Gmail's SMTP and IMAP with Workflow Notification Mailer

To configure Gmail's SMTP there are some prerequisites.

1. GMAIL SMTP require authentication. EBS started supporting authentication based smtp servers from R12.1.3 onwards.

    Please check below Note:

   Oracle Workflow Release Notes for Release 12.1.3(Doc Id 1095146.1)

    We may get error "Autentication failed" while sending notification. To solve this error we need to apply  patch 16559330 on top of R12.1.3

    Please check below Note:

    Workflow Notification Mailer Is Not Sending Email Notifications Or Goes Down When Authentication is Enabled (Doc ID 1528345.1)

2. Get Gmail SMTP SSL certificate.

Connect to smtp.gmail.com:465

openssl s_client -connect smtp.gmail.com:465

Copy and save the lines between "-----BEGIN CERTIFICATE-----" and "-----END CERTIFICATE-----" into a file, say, gmail.cer

3. Import certificate in Java keystore.
   

•   Install the SSL certificate into the default JRE location or any other location using below command

• Installing into a dfeault JRE location in EBS instance

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

           Answer Yes when it ask "Trust this certificate? [no]:  yes"

4. Create gmail account.

5. Create PROCESSED and DISCARD folders (Labels in gmail) in gmail account.

6. Inbox of gmail account must be empty.

7. Configure Workflow notification mailer through OAM.

   

    

Creating Oracle Database Listener using Net Configuration Assistant.

Today we will see how to create Oracle Database Listener.

We will create listener as shown below.

Set the database environment.

export ORACLE_HOME=<Location of oracle home>
export TNS_ADMIN=$ORACLE_HOME/network/admin

Go to $ORACLE_HOME/bin directory

cd $ORACLE_HOME/bin

./netca

Select Listener Cofiguration option.

  

Select Add option.

 

Enter Listener Name as LISTENER_<SID>

Ex. LISTENER_MWDB

 Click on Next.

 Enter Port number which is not in use.

 Select No option.

 Click on Next.

Now check the listener status by executing below command.

[oracle@ebs ~]$ lsnrctl status LISTENER_MWDB

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 31-MAY-2013 21:01:51

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebs.oracle.com)(PORT=152 2)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_MWDB
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                31-MAY-2013 21:01:03
Uptime                    0 days 0 hr. 0 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/mwdb/10.2.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/mwdb/10.2.0/network/log/listener_mwdb. log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebs.oracle.com)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Static service registration v/s Dynamic service registration in Oracle Database Listener.

We connect to oracle database remotely using listener. To connect the database instance through listener we need to register service for particular database under listener. There is always confusion about how the listener serve the database connections and how to register service under listener. We can register database service with listener in 2 ways. 

1. Static registration.
2. Dynamic registration.

Before we go to register any service listener must be created, if no listener is created yet we need to create one. We can create listener using GUI utility netca under $ORACLE_HOME/bin.

I recommend GUI utility because it easy to use and no chance for syntax errors.

Once you created listener you can see the listener configuration in the listener.ora file at $ORACLE_HOME/network/admin location.

GUI utility netca starts listener by default.

Now we will see static service registration. 

It is always easy to use GUI utility. Oracle has provided another GUI utility called netmgr. It is also available at $ORACLE_HOME/bin. Using netmgr we can do static registration.

cd $ORACLE_HOME/bin

./netmgr

You will get GUI. You can see listener which you created earlier under 

Oracle Net Configuration > Local > Listeners

Click on listener and select Database Services from drop down as shown below.

  Enter Oracle Home Path and SID as shown below and Save Network Configuration.

Exit from netmgr.
You need to restart the listener for changes to take effect. You can see service under listener with status UNKNOWN.

You will see that below static registration section is added to the listener.ora file.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = prod)
      (GLOBAL_DBNAME = )
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
    )
  )



Now we will see Dynamic service registration. 

In Dynamic service registration again we have 2 scenarios depends on which port listener is running i.e either on 1521 or  any other port.

If listener is running on default port 1521 then database background process pmon dynamically register service with name defined in service_name init parameter.

Let see example.

Check the value for init parameter service_name.

You can check the value for service_name parameter as below.

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string        

SQL>

Here you will get the value as <SID> or <SID.DOMAIN>

If the listener is running on default port 1521 then check listener status, you will get service with status READY and with name as defined in init parameter.

If the listener is running on port other than default port then pmon will not register the service under listener unless we set local_listener init parameter.

So in this case if we want pmon to register service dynamically under listener which is running on port other than default port we need to set local_listener init parameter as below.

SQL>  alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<port>))))' scope=both;

So this local_listener init parameter let pmon register service on port defined in local_listener parameter where listener is actually running.

Service which registered statically will always have status UNKNOWN.

And Service which registered dynamically will always have status READY.