EBS 12.2: Autoconfig/Sqlplus connection is faling with ORA-28547 on 19c

Issue: 

Receiving   "ORA-28547: connection to server failed, probable Oracle Net admin error"  while running autoconfig (or) connecting via sqlplus  after sourcing CDB (or) PDB env file.

Cause:

Missing $ORACLE_HOME/nls/data/9idata directory

Solution:

 

On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the following command to create the $ORACLE_HOME/nls/data/9idata directory.

$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl

After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 19c Oracle home.

Now re-run the autoconfig & make sure it completes without any issues.

How to Download oracle softwares using wget on Linux servers

a. Please install wget utility in  linux server where you want to download. Most of the Linux  flavors will have this installed by default.

b.Please install the add-on  export-cookies-txt  in firefox (for chrome ,Please use cookies.txt)








c. After installing the above add-on ,you can login to OTN,edelivery or MOS with your credentials. Please download any one of the file you want to download and cancel immediately .

d. Save the cookies.txt  as instructed  below.

e.Transfer the cookies.txt  to Linux server.(for example to /root)
f..copy the link location of the files which needs to be downloaded.

g. Please execute the below command to download the necessary files.

wget --load-cookies=/root/cookies.txt https://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_database_1of2.zip

Note:

1. If you are receiving any errors related to certificate ,please use --no-check-certificate option. and to save a  file under a different name, pass the -O option followed by the chosen name.

Ex:
wget --load-cookies=/root/cookies.txt https://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_database_1of2.zip -O jay.zip


2. Sometimes cookies may expire within 30 mins. In this case, you may have to re-initiate the download in browser (and cancel it immediately)  and copy the cookies.txt once again.

3. Optionally (but highly recommended) you can compare the checksum of downloaded files and checksum value in the site to make sure there is no corruption in the files.

[root@jaysv01 ~]#  cksum linuxamd64_12102_database_1of2.zip
839029806 1673544724 linuxamd64_12102_database_1of2.zip

[root@jaysv01 ~]#  cksum linuxamd64_12102_database_2of2.zip
1187131466 1014530602 linuxamd64_12102_database_2of2.zip

Terraform - count vs for_each

 

Count:

Before Terraform 0.12.6, the only way to create multiple instances of the same resource was to use a count parameter. One of the problems with this approach  is ordering. Count is maintaining the  array numeric index (list) to perform it's operations.If  there is a change in the order, terraform wants to destroy/re-create that object.

Code snippet has been given below to explain the difference between count and for_each. Given snippet has been taken from block volume provisioning & attachment module.

Phase 1: Provision the block volumes  

Below code will provision three block volumes("MyVolume1","MyVolume2","jay")  and attach the same to the defined compute instance. 

Variables.tf

variable "block_display_name" {
type = "list"
default = ["MyVolume1","MyVolume2","jay"]
}

variable "block_size" {
type = "list"
default = ["50","60","80"]
}

block.tf

resource "oci_core_volume" "gol_blockvolume" {
  count =  "${var.vol_count}"
   availability_domain = "${data.oci_identity_availability_domain.ad.name}"
  compartment_id      = "${var.compartment_id}"
  display_name        = "${var.block_display_name[count.index]}"
  size_in_gbs         = "${var.block_size[count.index]}"
 }

resource "oci_core_volume_attachment" "gol_attachment" {
  count =  "${var.vol_count}"
  depends_on = ["oci_core_volume.gol_blockvolume"]
  attachment_type = "iscsi"
  instance_id     = "${data.oci_core_instances.gol_instances.instances.*.id[0]}"
  volume_id       = "${oci_core_volume.gol_blockvolume.*.id[count.index]}"
}

Let us run terraform plan to review  the resource actions.

Terraform Variables - Input variables

Input variables serve as parameters for a Terraform module,allowing aspects of the module to be customized without altering the module's own source code, and allowing modules to be shared between different configurations.

Let us explore the different types supported by terraform.

Strings:

strings are just a sequence of characters. If no type is specified, then Terraform assumes a variable is a string.

Declaration:

variable "tag_nm" {
  type = "string" 
  default = "environment"
}

A string variable can then be used in resource plans by using the interpolation below..

Interpolation:

tag_name =  "${var.tag_nm}"

We can escape interpolation with double dollar signs: $${tag_nm} will be rendered as a literal ${tag_nm}.

Lists:

Another type of Terraform variables lists.

A list value is an ordered sequence of strings indexed by integers starting with zero

Declaration:

variable "block_display_name" {

type = "list"

default = ["MyVolume2","jay"]

}

Lists can be used in the resource plans similarly to strings, but we need to denote the index of the value.Interpolation of list variables would be.

Interpolation:

block_name= "${var.block_display_name[0]}" --  It will return MyVolume2

Terraform  in built function can be used to get the index value. By combining count attribute and length function ,we can loop over the resources.

count = length(var.block_display_name)

block_name  = var.block_display_name[count.index]

Maps:

A map value is a lookup table from string keys to string values. This is useful for selecting a value based on some other provided value.

Declaration:

variable "instance_image_ocid" {

  type = "map"

  default = {

    linux6   = "ocid1.image.oc1.iad.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

    windows ="ocid1.image.oc1.iad.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

  }

}

Interpolation:

image_ocid  = "${var.instance_image_ocid["linux6"]}"

Boolean

It is recommended for now to specify boolean values for variables as the strings "true" and "false".. 

variable "is_available" {

  default = true

}

The above example boolean can be used similarly to a string variable by simply marking down the correct variable. Interpolation also similar to string type.

Ref: https://www.terraform.io/docs/configuration/variables.html

EBS R12.2 | OAInfo.jsp returns HTTP 403 Forbidden | Security: Allowed Resources

Issue :

OAInfo.jsp is commonly used to setup a health Check On A BIG-IP Loadbalncer to monitor E-Business Suite JVM's . But please be aware that OAInfo.jsp has been deprecated in R12.1, the jsp is still functional for the purposes of health check.

But after we upgraded ebs t0 12.2.8, we are getting 403 message in access log.

"GET /OA_HTML/OAInfo.jsp HTTP/1.1" 403 54

Cause:

There is a significant difference in the security features from 12.2.6+ & It brings allowed Resources feature  which is part of 12.2.7+. Due to this enhancement, it disables all the deprecated jsps  which affects oainfo.jsp as well.

Solution:

Action Plan 1:

This  action plan will allow all the deprecated resources

.

a.       Access the page http://ebs.jay.com/OA_HTML/OAInfo.jsp  & it should return 403 error message  (or) Requested resource or page is not allowed in this site

b.       Please set profile option Security: Allowed Resources to  ALL  which will whitelist all the jsps

c.        Bounce the apache & oacore services.  (Please bounce the services  on all application tiers)

d.        Now try to access the http://ebs.jay.com/OA_HTML/OAInfo.jsp   &  it should display the page with below text which will return status code 200 in access log -

“This JSP is no longer supported. To view the information that this JSP provided, please log in to Oracle E-Business Suite, and select the About this Page link."”

Action Plan2:

This action plan will allow only oainfo.jsp page

a.       Access the page http://ebs.jay.com/OA_HTML/OAInfo.jsp & it should return 403 error message  (or) Requested resource or page is not allowed in this site

b.       cd $FND_TOP/secure & vi allowed_extensions_custom.conf

c.        Add the /OA_HTML/OAInfo.jsp entry in  allowed_extensions_custom.conf

d.       java oracle.apps.fnd.security.resource.WLDataMigration MODE=custom INPUT_FILE=$FND_TOP/secure/allowed_extensions_custom.conf  DBC=$FND_SECURE/{SID}.dbc

e.       Bounce the apache & oacore services. (Please bounce the services on all application tiers)

f.         Now try to access the http://ebs.jay.com/OA_HTML/OAInfo.jsp   &  it should display the page with below text which will return status code 200 in access log.

 

“This JSP is no longer supported. To view the information that this JSP provided, please log in to Oracle E-Business Suite, and select the About this Page link."”

Begin your Journey with Terraform | Setting up terraform on OCI

This post covers the necessary steps to be followed for setting up terraform master server (where we are going to store and execute our scripts).




Step1: Provision the compute instance:

a. Provision the Bastion Server on OCI : Use Oracle Cloud Developer Image for provisioning

R12.2 Mobile Apps Setup - Reference docs

Implementation & Admin guide:

Install Process for the Oracle Mobile Supply Chain Applications/MSCA/MWA on Mobile Apps for Oracle E-Business Suite Release (Doc ID 2163603.1)

Oracle Mobile Supply Chain Applications for Oracle E-Business Suite Release Notes (Doc ID 2108155.1)

https://docs.oracle.com/cd/E18727_01/doc.121/e64384.pdf  -- Admin Guide


What is MSCA ?

http://norcaloaug.com/seminar_archive/2014_training_day_pres/3_6_Jain.pdf

EBS R12 Workflow Mailer : Problem getting the HTML content: javax.net.ssl.SSLHandshakeException

Issue:

Workflow notificaiton mailer is not sending emails to recipients and getting below exception in logs.

 Problem getting the HTML content -> oracle.apps.fnd.wf.mailer.NotificationFormatter$FormatterSAXException:
Problem obtaining the HTML content -> oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed:sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

Cause:

Due to self-signed certificate

Solution:

a. copy the  root and intermediate certificates of  EBS login url (LB URL)  to app server.

b.  Import the certificate into cacerts

1.cd $COMMON_TOP/util/jdk32/jre/lib/security
2.chmod u+w cacerts
3.keytool -import -alias ApacheRootCA -file ca.crt -v -keystore cacerts
4.keytool -import -alias ApacheInterA -file ca.crt -v -keystore cacerts
5.chmod u-w cacerts

c. Please follow the step b (2-5) in the below locations as  well.

$COMMON_TOP/util/jdk64/jre/lib/security/
<s_fmw_jdktop>/jre/lib/security

s_fmw_jdktop - grep the value from context_file

d. Import the root & inter ca in the below file.

cat ca.crt >> <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt
cat intca.crt >> <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt

e. Bounce the workflow services.

Workaround:
Alternatively , We can Change the "WF: Workflow Mailer Framework Web Agent" [WF_MAIL_WEB_AGENT] profile option to point to the physical Web Server host address - http://<host>.<domain>:<port> and rebuild the workflow mailer queue.

Import private key and certificate into JKS

a. Import (or)Convert the private key and certificate to PKCS12

openssl pkcs12 -export  -inkey /u02/certficates/private.key -in /u02/certficates/server.cer -certfile /u02/certficates/interCA.cer -out testkeystore.p12

Please provide the export password and re-enter to verify .

Note: you may receive an error like "Error unable to get issuer certificate getting chain."  if the certificate is self-signed. In this case, Please concatenate the openssl cacerts with your own root certificate (ca-cert) into one file and use that as parameter for -CAfile. Example:

cat ca-cert /etc/ssl/certs/ca-certificates.crt > consol_cacerts.crt
then use consol_cacerts.crt  as the parameter to the -CAfile option in the openssl command.

b. Convert p12 to JKS

keytool -importkeystore -srckeystore testkeystore.p12 -srcstoretype pkcs12 -destkeystore mwa.jks -deststoretype JKS

Please provide the JKS password and re-enter to verify.
Supply the export password which had been set in step a.


c. View the certificates and private key entry

keytool -list -v -keystore mwa.jks

How to prevent ssh timeout in linux?

By sending a “null packet” between the client and the server at a specified interval that is smaller than the timeout value, we can avoid SSH timeout. It doesn’t matter if the packet is sent from the client or the server, as long as there is some communication going on between the two.

Prevent SSH timeout on the server side:

Please  add the following to your SSH daemon config in /etc/ssh/sshd_config on your servers to prevent the clients to time out – so they don’t have to modify their local SSH config:

ClientAliveInterval 120
ClientAliveCountMax 720

This will make the server send the clients a “null packet” every 120 seconds and not disconnect them until the client have been inactive for 720 intervals (120 seconds * 720 = 86400 seconds = 24 hours).

Check the value through command line ...

[root@oam network-scripts]# sshd -T | grep -i client
clientaliveinterval 120
clientalivecountmax 720

Prevent SSH timeout on the client side:

on many commodity firewalls, connections are killed after as little as 300 seconds. To avoid having your SSH sessions become unresponsive after e.g. 5 minutes, do the following:

Linux Client:
If we are on Linux, you can edit your local SSH config file in ~/.ssh/ssh_config  (or) /etc/ssh/ssh_config and add the following line:

ServerAliveInterval 120
This will send a “null packet” every 120 seconds on your SSH connections to keep them alive.


Windows clients:

Putty - On Windows (PuTTY)
In your session properties, go to Connection and under Sending of null packets to keep session active, set Seconds between keepalives (0 to turn off) to e.g. 300 (5 minutes).

Mobaxterm - You can check the "Enable SSH keepalive" box under "Settings" --> "Configuration" --> "SSH" tab.