Tuesday, August 4, 2020

Gmail Integration with Oracle SOA Server 12c

We can configure Email Integration with SOA UMS Adapter to send the email notification and it has below steps to follow.

a.      Gmail Pre-requisites Step

b.      Get the Certificate from Gmail

c.      Load the Certificate into KeyStore

d.      Configure Email Driver with UMS Adapter

e.      Restart the Admin and Managed Server

f.       Test the Email Notification

Gmail Pre-Requisite Step

Here, we are following Gmail Configuration for Email Server Integration and below are pre-requisites need to be performed.

a.      Create a Gmail Account

b.      Login to gmail with gmail account created

c.      Check that IMAP is enabled in mail

·        On your computer, open Gmail.

·        In the top right, click Settings   See all  settings.

·        Click the Forwarding and POP/IMAP tab.

·        In the "IMAP access" section, select Enable IMAP.

·        Click Save Changes.


d.      Open below URL in the same browser and make sure that ‘Less Secure App Access’ Turned On

https://myaccount.google.com/lesssecureapps

 

Get the certificate from Gmail

Outgoing mail server is SMTP configuration and use the below statement to get the certificate from smtp server using openssl client from your server.

openssl s_client -connect smtp.gmail.com:465 > gmail-smtp-cert.pem


It will create a file with certificate, copy the below highlighted content into a notepad for adding into keystore.



Incoming Mail Server is IMAP configuration and use the below statement to get the certificate from IMAP server using openssl client from your server

                        openssl s_client -connect imap.gmail.com:993 > gmail-imap-cert.pem


 It will create a file with certificate, copy the below highlighted content into a notepad for adding into keystore.



Load the Certificate into Keystore

 Modifying the $DOMAIN_HOME/bin/setDomainEnv.sh script, remove the following from EXTRA_JAVA_PROPERTIES

-Djavax.net.ssl.trustStore=${WL_HOME}/server/lib/DemoTrust.jks

DemoTrust.jks is the Test Keystore provided by Weblogic and we will not use it for adding certificates.

 



 Open the Enterprise Manager to Load the Certificate in the Domain Keystore.

        a. Login with Admin User


        b. Navigate to Domain --> Security --> Keystore

    
        c. Select System --> Trust and click 'Manage'


        d. Click 'Import' to add the smtp certificate


        e. Enter the details
                Certificate Type : Trusted Certificate
                Alias : smtp.gmail.com
                Copy paste the certificate of smtp.gmail.com copied in the notepad to this window.
                Click ok


        f. Click on 'Import' to add the impap certificate
                Enter the details
                    Certificate Type : Trusted Certificate
                    Alias : imap.gmail.com
                    Copy paste the certificate of imap.gmail.com copied in the notepad to this window.
                Click 'ok'

        
           g. Certificates are added to the System/Trust Keystore


Configure Email Driver with UMS Adapter

        Open Enterprise Manager --> User Messaging Driver - email --> Email Driver Properties

    
        Click on 'Create' to enter the Email Driver properties

    
        Enter the details
            Capacity : SEND
            Sender Address : EMAIL:<email_address>

        
        Email Receiving Protocol : IMAP
        Debug Mode : Select the check box
        Message Folder : INBOX
        Outgoing Mail Server : smtp.gmail.com
        Outgoing Mail Server Port : 465
        Outgoing Mail Server Security : SSL
        Outgoing User Name : Enter email address
        Outgoing Password : 
                Type of Password : Use cleartext password
                Password : Enter Password
        Incoming Mail Server : imap.gmail.com
        Incoming Mail Server Port : 993
        Enable SSL : Select the checkbox



           
        Click 'Test' and verify the Configuration is valid

        
        Monitor soa_server1.out log file to verify the connectivity is successful.

Configure SOA Workflow Properties

    Open Enterprise Manager --> soa-infra --> SOA Administration --> Workflow Properties


        Enter the email address and click Apply


Restart the Admin Server and Managed Servers

    a. Stop soa_server1 Managed Server

    b. Stop Admin Server

    c. Stop Node Manager

    e. Start Node Manager

    f. Start Admin Server

    g. Start soa_server1 Managed Server

Test the Email Notification

    Open Enterprise Manager --> soa_infra --> Service Engines --> Human Workflow

    
        Click on 'Notification Management' tab

    
        Click on 'Send Test Notification' button

    
        Enter the Test notification details
            Send to : Enter email address
            Channel : Email
            Subject : Enter the Subject
            Content : Enter the Email body

    
        Check the soa_server1.out file for email sending activity

    
        Can monitor the status as 'SENT'

    
        Open email client and verify the email received.

Happy Development and Delivery !!!


Monday, March 30, 2020

ERROR: ORA-12638: Credential retrieval failed

Please follow the steps mentioned in this post for detailed steps, or edit the below entry to fix the issue.

Edit the sqlnet.ora file (path: $ORACLE_HOME/network/admin) and change the below entry from

SQLNET.AUTHENTICATION_SERVICES = (NTS)
To
               SQLNET.AUTHENTICATION_SERVICES = (NONE)


Configure Oracle Database 12c - Enterprise Manager Database Express

In this post, I am showing how we can access the Oracle Database Enterprise Manager.

Oracle Enterprise Manager Database Express will be installed while installing the Oracle Database 12c in the below installation step (follow here, for oracle DB 12c installation) , but when we access the ur we can see that enterprise manager is not accessible. For that, we have to set the https port configuration and can access the EM.

Step 1 : Connect the database using sqlplus

               sqlplus “/as sysdba”

Step 2: get the http port already configured

               SELECT DBMS_XDB_CONFIG.gethttpport FROM dual;

Step 3: Get the https port already configured

               SELECT DBMS_XDB_CONFIG.gethttpsport FROM dual;

Step 4: Set the https port

               EXEC DBMS_XDB_CONFIG.sethttpsport(5500);


Note: The DBMS_XDB_CONFIG package provides an interface for configuring Oracle XML DB and its repository. PL/SQL package DBMS_XDB_CONFIG is the Oracle XML DB resource application program interface (API) for PL/SQL for DBAs to configure their system. This API provides functions and procedures to access and manage Oracle XML DB Repository resources using PL/SQL. It includes methods for managing resource security and Oracle XML DB configuration.

Enterprise Manager URL is

https://<hostname>:<port>/em

Example is, 


https://desktop-627vpan:5500/em







Connect Oracle Database Remote in Windows Machine using sqlplus


In this post, I am showing that how we can connect to the Oracle Database which is running in remote machine using sqlplus. 

If we are not changing the sqlnet.ora file, we get the below error while trying connect remote database server from command prompt

ERROR: ORA-12638: Credential retrieval failed



Edit the sqlnet.ora file (path: $ORACLE_HOME/network/admin) and change the below entry from

SQLNET.AUTHENTICATION_SERVICES = (NTS)
To
               SQLNET.AUTHENTICATION_SERVICES = (NONE)

Option 1 : Edit tnsnames.ora file

               a.      Edit the tnsnames.ora file (path: $ORACLE_HOME/network/admin) and add the below entries to make the command with simple string.

<SID> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname of remote server>)(PORT = <port>))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <service name>)
    )
  )

Example are given below,

GFIFMWDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-627VPAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gfifmwdb)
    )
  )
             b.      Execute the below command

sqlplus "SYS/Welcome1@GFIFMWDB AS SYSDBA"

            c.      We can see that sqlplus is connected to the remote DBs server

    

       Option 2 : Without editing tnsnames.ora file

           a.      Execute below command by providing all the remote server information in the command

sqlplus "sys/Welcome1@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=DESKTOP-627VPAN)(Port=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=GFIFMWDB)))" as sysdba

          b.      We can see that sqlplus is connected to the remote DBs server



Sunday, March 29, 2020

Oracle Database 12c -12.2.0.1.0 on Windows


In this post, I am showing how to install Oracle Database 12c - 12.2.0.1.0 in Windows Operating System for General Purpose and Transactional Processing Database for Oracle Fusion Middleware Product RCU and Fusion Middleware as well as Non-Fusion Middleware applications.

Oracle Database Installation steps are categorized into below table for easy checklist and can perform each action step by step and need to update the status as “Done” once the corresponding section is completed.

Oracle Database Setup
Step
Task
Status
1
Overview and Pre-requisite checkup
 Done
2
Download Oracle Software Binary
 Done
3
Install Oracle Database Software
 Done
3.1
Run Setup
 Done
3.2
Create and Configure a database
 Done
4
Verify Installation
 Done
4.1
View Oracle Services
 Done
4.2
View Oracle Home
 Done
4.3
View tnsnames.ora file
 Done
5
Connect to the Oracle Database
 Done
6
Summary
 Done

Oracle Database Setup

Below are the server details that I have used for the Oracle Database installation setup.

Server Details
CPU Core
2
RAM
15 GB
Application Disk
400 GB
Operating System
Windows 10 Enterprise
Static IP/Dynamic IP
Dynamic IP
Local IP
192.168.0.180
Hostname
DESKTOP-627VPAN
Public IP
NO
Server/Desktop
Desktop
Physical/Virtual
Physical

Overview and Pre-requisite checkup

The Oracle Universal Installer (OUI) is used to install the Oracle Database software. OUI is a graphical user interface utility that will support on below,

       v  View the Oracle software that is installed
       v  Install new Oracle Database software
       v  Delete Oracle software that is no longer required

During the installation process, OUI will start the Oracle Database Configuration Assistant (DBCA) which can install a created default database that contains example schemas or can guide you through the process of creating and configuring a customized database. If we need to create the database after installation, we can run DBCA to create one or more databases.

Before installing the software, OUI performs several automated checks to ensure that computer fulfills the basic hardware and software requirements for an Oracle Database installation. Some of the requirements to install the software are:

       v  Minimum 2 GB of physical memory
       v  Sufficient/Enough virtual memory (swap)
       v  At least 10 GB of free disk space

Download Oracle Software Binary

Username: Oracle SSO User

Password: Oracle SSO Password

Sl No
Product
Version
Package Name
Package Size
1
Oracle Database 12c Enterprise Edition
12.2.0.1.0
V839963-01
2.79 GB




Extract the zip file for the binary files.



Install Oracle Database Software

Run Setup

       v  Install the Oracle Database.
o   Expand the database folder and Double-click setup.




o   Click Yes in the User Account Control window to continue with the installation.



o   The Configure Security Updates window appears. Enter email address and My Oracle Support password to receive security issue notifications via email. Deselect "I wish to receive security updates via My Oracle Support", if notifications are not required. Click Next to continue. Click "Yes" in the confirmation window to confirm your preference.



Create and Configure a database


o   The Select Installation Option window appears with the following options:
§  Select "Create and configure a database" to install the database, create database instance and configure the database.
§  Select "Install database software only" to only install the database software.
§  Select "Upgrade an existing database" to upgrade the database that is already installed.

In this post, we create and configure the database. Select the Create and configure a database option and click Next.


o   The System Class window appears. Select Desktop Class or Server Class depending on the type of system you are using. Select "Server class" and click "Next".


o   Select the “single instance database installation’ and Click “Next”.



o   Select “Advanced install” and click “Next”.


o   Select “Enterprise Edition” and click “Next”.



o   Select “Use Virtual Account” and click “Next”.



o   Enter Oracle Base and Oracle Software Location (or choose the default location provided in the widget) and click “Next”.



o   Select “General Purpose/Transaction Processing” and click “Next”.



o   Enter Global Database Name and SID (or choose the default provided in the widget) and select the checkbox “Create as Container Database”. Also, enter pluggable database name (or choose the default location provided in the widget) and click “Next”.


o   Provide memory (or choose the default provided in the widget) so that it will calculate SGA and PGA and select the tab “Character Set” tab



o   Select the character set as “Use Unicode (AL32UTF8)” which supports Arabic and required for Oracle Fusion Middleware RCU. Select “Sample Schemas” tab.


o   Deselect the checkbox “Install sample schemas in the database” and click “Next”.



o   Provide database storage file path (or choose the default location provided in the widget) and click “Next”.



o   Select the checkbox “Enable Recovery” and provide the path (or choose the default location provided in the widget). Click “Next”.



o   Select the radio button “use same passwords for all accounts” and enter the password with Capital letter, small letter and number. Click “Next” .



o   Pre-requisite check starts and will show the installer summary page. Save the response file and click “Install”.



o   Installation starts and will show the Finish page. Click “Close”.




Verify Installation

View Oracle Services

       v  Navigate to C:\Windows\system32 using Windows Explorer. Double-click services. The Services window appears, displaying a list of services.




                 v  Scroll down to view a list Oracle services. You see that most of the Oracle services are started successfully by the database.


View Oracle Home


View tnsnames.ora file




 Add the below entries to the tnsnames.ora file and save the file.

GFIFMWPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-627VPAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gfifmwpdb)
    )
  )

Connect to the Oracle Database


sqlplus sys/Welcome1@gfifmwdb as sysdba



sqlplus sys/Welcome1@gfifmwpdb as sysdba



C:\Users\Gireesh>sqlplus "/ as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 29 17:29:05 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
gfifmwdb
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL>


C:\Users\Gireesh>sqlplus sys/Welcome1@gfifmwpdb as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 29 17:22:19 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> startup

Pluggable Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

C:\Users\Gireesh>sqlplus sys/Welcome1@gfifmwdb as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 29 17:24:56 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter pluggable database all close immediate;

Pluggable database altered.

SQL> exit

Summary

Oracle Database 12c - 12.2.0.1.0 has been installed on a Windows operating system and has been configured and tested from sqlplus.