Friday, September 25, 2020

How to Splice a Custom Product in Oracle E-Business Suite R12.2 Using `adsplice`

Oracle E-Business Suite provides a utility called adsplice that allows you to integrate custom applications into the standard EBS framework. In this blog, we'll go through a real-time example of splicing a custom application PALANE_TOP into an EBS environment.

Prerequisites

Before running adsplice, ensure the following:

Your environment variables are correctly sourced.
You have the custom product control file (newprods.txt) ready in the appropriate admin directory.
You have backup access and proper DBA credentials

Step-by-Step: Running AD Splicer

Session Walkthrough

Here’s how the interactive session flows:

Confirm the APPL_TOP
The system prompts you to verify the current APPL_TOP path:
```
Is this the correct APPL_TOP [Yes] ?
```
Enter Log File Name
Provide a log file name or accept the default:
```
Filename [adsplice.log]:
```
Enter Application System Name
This is usually your EBS instance name:
```
Applications System Name [OMUAT]: OMUAT
```
Select File Types
You’ll be prompted to confirm the types of files you want installed:
```
Do you want to install DB files, Forms, Concurrent Programs, etc.?
```
Specify APPL_TOP Name
Example:
```
APPL_TOP Name [AT_omuatap]: AT_omuatap
```
Confirm Database Info
Confirm database name and enter SYSTEM and APPS user passwords securely.

Control File and Custom Product Registration

When prompted:

Please enter the name of your AD Splicer control file [newprods.txt]:

The control file (newprods.txt) should contain information about your custom product PALANE_TOP.

Warning Messages

You may see warnings like:

AD Splicer warning: Product Data File /.../admin/xxxprod.txt does not exist...

These are typically non-blocking if you're not using the mentioned modules.

Product Registration

The splicer creates the Oracle schema for PALANE_TOP, grants required privileges, and sets up directory structures:

sql
admin/sql
admin/driver
log, out, mesg

Sample generated files include:

sql/PALANE_TOPNLINS.sql
admin/sql/PALANE_TOPNLADD.sql

AutoConfig Execution

After successful splicing, AD Splicer prompts:

Do you wish to regenerate your environment file [Yes] ?

It then runs AutoConfig to apply the changes and updates all necessary configuration files.

appltst @ omuatap.oasiserp.com/u02/app/oracle/fs1/EBSapps/appl/admin >adsplice

Redwood Shores, California, USA

AD Splicer

Version 12.2.0

NOTE: You may not use this utility for custom development

unless you have written permission from Oracle Corporation.

Your default directory is '/u02/app/oracle/fs1/EBSapps/appl'.

Is this the correct APPL_TOP [Yes] ?

AD Splicer records your AD Splicer session in a text file

you specify. Enter your AD Splicer log file name or press [Return]

to accept the default file name shown in brackets.

Filename [adsplice.log] :

************* Start of AD Splicer session *************

AD Splicer version: 12.2.0

AD Splicer started at: Fri Sep 25 2020 23:30:47

APPL_TOP is set to /u02/app/oracle/fs1/EBSapps/appl

Change DB = YES.

Please enter the name of the Oracle E-Business Suite System that this

APPL_TOP belongs to.

The Applications System name must be unique across all Oracle

Applications Systems at your site, must be from 1 to 30 characters

long, may only contain alphanumeric and underscore characters,

and must start with a letter.

Sample Applications System names are: "prod", "test", "demo" and

"Development_2".

Applications System Name [OMUAT] : OMUAT *

NOTE: If you do not currently have certain types of files installed

in this APPL_TOP, you may not be able to perform certain tasks.

Example 1: If you don't have files used for installing or upgrading

the database installed in this area, you cannot install or upgrade

the database from this APPL_TOP.

Example 2: If you don't have forms files installed in this area, you cannot

generate them or run them from this APPL_TOP.

Example 3: If you don't have concurrent program files installed in this area,

you cannot relink concurrent programs or generate reports from this APPL_TOP.

Do you currently have or want to install files used for installing or upgrading

the database in this APPL_TOP [YES] ? YES *

Do you currently have or want to install Java and HTML files for HTML-based

functionality in this APPL_TOP [YES] ? YES *

Do you currently have or want to install Oracle E-Business Suite forms files

in this APPL_TOP [YES] ? YES *

Do you currently have or want to install concurrent program files

in this APPL_TOP [YES] ? YES *

Please enter the name Oracle E-Business Suite will use to identify this APPL_TOP.

The APPL_TOP name you select must be unique within an Oracle E-Business Suite

System, must be from 1 to 30 characters long, may only contain

alphanumeric and underscore characters, and must start with a letter.

Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".

APPL_TOP Name [AT_omuatap] : AT_omuatap *

You are about to install or upgrade Oracle E-Business Suite product tables

in your ORACLE database 'OMUAT'

using ORACLE executables in '/u02/app/oracle/fs1/EBSapps/10.1.2'.

Is this the correct database [Yes] ?

AD Splicer needs the password for your 'SYSTEM' ORACLE schema

in order to determine your installation configuration.

Enter the password for your 'SYSTEM' ORACLE schema:

The ORACLE username specified below for Application Object Library

uniquely identifies your existing product group: APPLSYS

Enter the ORACLE password of Application Object Library [APPS] :

AD Splicer is verifying your username/password.

The status of various features in this run of AD Splicer is:

<-Feature version in->

Feature Active? APPLTOP Data model Flags

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

CHECKFILE Yes 1 1 Y N N Y N Y

PREREQ Yes 6 6 Y N N Y N Y

CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N

PATCH_TIMING Yes 2 2 Y N N Y N Y

PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y

SCHEMA_SWAP Yes 1 1 Y N N Y Y Y

JAVA_WORKER Yes 1 1 Y N N Y N Y

CODELEVEL Yes 1 1 Y N N Y N Y

Reading product information from file...

Reading language and territory information from file...

Reading language information from applUS.txt ...

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/eaaprod.txt

does not exist for product "eaa".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/evmprod.txt

does not exist for product "evm".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/ipdprod.txt

does not exist for product "ipd".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/cssprod.txt

does not exist for product "css".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/meprod.txt

does not exist for product "me".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/xnmprod.txt

does not exist for product "xnm".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/xncprod.txt

does not exist for product "xnc".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/xnsprod.txt

does not exist for product "xns".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/okrprod.txt

does not exist for product "okr".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/cueprod.txt

does not exist for product "cue".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/ibaprod.txt

does not exist for product "iba".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/ozpprod.txt

does not exist for product "ozp".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/ozsprod.txt

does not exist for product "ozs".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/rlaprod.txt

does not exist for product "rla".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/vehprod.txt

does not exist for product "veh".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/rhxprod.txt

does not exist for product "rhx".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/imtprod.txt

does not exist for product "imt".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/ahmprod.txt

does not exist for product "ahm".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/okbprod.txt

does not exist for product "okb".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/okoprod.txt

does not exist for product "oko".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

AD Splicer warning:

Product Data File

/u02/app/oracle/fs1/EBSapps/appl/admin/amfprod.txt

does not exist for product "amf".

This product is registered in the database but the

above file does not exist in APPL_TOP. The product

will be ignored without error.

Reading database to see what industry is currently installed.

Reading FND_LANGUAGES to see what is currently installed.

Currently, the following language is installed:

Code Language Status

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

US American English Base

Your base language will be AMERICAN.

Setting up module information.

Reading database for information about the modules.

Saving module information.

Reading database for information about the products.

Reading database for information about how products depend on each other.

Reading topfile.txt ...

Saving product information.

*** Edition Enabled User ***

Please enter the directory where your AD Splicer control file is located.

The default directory is [/u02/app/oracle/fs1/EBSapps/appl/admin] :

Please enter the name of your AD Splicer control file [newprods.txt] :

Processing file /u02/app/oracle/fs1/EBSapps/appl/admin/newprods.txt...

Loading information for product 'xxoic'...

Warning: Because the database is in new tablespace mode, AD Splicer is ignoring the tablespace settings in your control file!

Successfully read file /u02/app/oracle/fs1/EBSapps/appl/admin/newprods.txt.

Verifying localizations...

Validating inter-product dependency information...

Re-organizing product information lists...

Adding new languages into FND_LANGUAGES...

Creating ORACLE usernames for products...

AD Splicer is creating or updating the ORACLE username PALANE_TOP

with password ***** for Oracle Support Diagnostic Tools tables.

New product PALANE_TOP spliced. Use FNDCPASS to change the password.

Granting privileges for tablespaces...

Granting required system privileges and creating APPS*DDL packages...

sqlplus -s PALANE_TOP/***** @/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adaddls.pls ***** PALANE_TOP *****'

Done running adaddls.pls for PALANE_TOP

sqlplus -s PALANE_TOP/***** @/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adaaddls.pls ***** PALANE_TOP *****'

Done running adaaddls.pls for PALANE_TOP

sqlplus -s PALANE_TOP/***** @/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adaddlb.pls ***** PALANE_TOP *****'

Done running adaddlb.pls for PALANE_TOP

sqlplus -s PALANE_TOP/***** @/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adaaddlb.pls ***** PALANE_TOP *****'

Done running adaaddlb.pls for PALANE_TOP

Registering ORACLE usernames/passwords.

**Inserting application information for new products...

**** Running Seed Table Prepare for FND_APPLICATION ****

**** Running Seed Table Prepare for FND_APPLICATION_TL ****

INSERT INTO FND_OAM_CONTEXT_CUSTOM (OA_VAR,CTX_TYPE,OA_TYPE,NAME,DEFAULT_VALUE,TITLE,DESCRIPTION) VALUES ('c_xxoic','A','PROD_TOP','c_xxoic','/u02/app/oracle/fs1/EBSapps/appl/xxoic/12.0.0','custom prod top xxoic','this is spliced custom prod top') Temporarily resetting CLASSPATH to:

"/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/java/adjava.zip:/u02/app/oracle/fs1/EBSapps/comn/util/jdk/lib/dt.jar:/u02/app/oracle/fs1/EBSapps/comn/util/jdk/lib/tools.jar:/u02/app/oracle/fs1/EBSapps/comn/util/jdk/jre/lib/rt.jar:/u02/app/oracle/fs1/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/u02/app/oracle/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-fmw/WEB-INF/lib/ebsFMWManifest.jar:/u02/app/oracle/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u02/app/oracle/fs1/FMW_Home/oracle_common/modules/oracle.uix_11.1.1/uix2.jar:/u02/app/oracle/fs1/EBSapps/comn/java/classes:/u02/app/oracle/fs1/EBSapps/10.1.2/forms/java:/u02/app/oracle/fs1/EBSapps/10.1.2/forms/java/frmall.jar:/u02/app/oracle/fs1/EBSapps/10.1.2/jlib/ewt3.jar:/u02/app/oracle/fs1/EBSapps/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar"

Calling /u02/app/oracle/fs1/EBSapps/comn/util/jdk/jre/bin/java ...

Running fnd_application_pkg.add_language ...

**Updating product information for new products...

Populating INSTALL_GROUP_NUM column to FND_ORACLE_USERID table.

**Updating product dependency information...

Recording modules in the database...done.

**Updating Data Groups...

Reading existing data groups from database.

Registering any new data groups.

Registering any new data group units.

Updating Data Group "Standard" to point to

APPS #1 Account (ORACLE ID = 900)

Updating Patch Advisor tables to include new product information ..

sqlplus -s APPS/***** @/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u02/app/oracle/fs1/EBSapps/appl/ad/12.0.0/sql/adpaseed.sql *****'

Done.

Saving module actions...done.

Saving product actions...done.

Making product top directory for product "xxoic"...

Directory will be /u02/app/oracle/fs1/EBSapps/appl/xxoic/12.0.0

Making directory "log" for product "xxoic"...

Making directory "out" for product "xxoic"...

Making directory "mesg" for product "xxoic"...

Making directory "sql" for product "xxoic"...

Creating stub file "sql/PALANE_TOPNLINS.sql" for product "xxoic"...

Making directory "admin/sql" for product "xxoic"...

Creating stub file "admin/sql/PALANE_TOPNLADD.sql" for product "xxoic"...

Making directory "admin/driver" for product "xxoic"...

Recording ADSPLICE action for product: xxoic

Do you wish to regenerate your environment file [Yes] ? yes

Checking if AutoConfig is enabled....

Running AutoConfig on : All products ...

The log file is located at:

/u02/app/oracle/fs1/EBSapps/appl/admin/OMUAT/log/autoconfig_1.log

Please check the log file for more details about the run of AutoConfig.

If you added new products, use FNDCPASS to change their default passwords.

AD Splicer is complete.

Errors and warnings are listed in the log file

/u02/app/oracle/fs1/EBSapps/appl/admin/OMUAT/log/adsplice.log

and in other log files in the same directory.

Post-Splice Actions

Check Log Files
- /u02/app/oracle/fs1/EBSapps/appl/admin/OMUAT/log/adsplice.log
- AutoConfig log path is also provided during the run.

Set Product Passwords
Use FNDCPASS to update the password for your new schema:

FNDCPASS apps/<apps_pass> 0 Y system/<system_pass> USER PALANE_TOP <new_pass>

Summary

The adsplice utility in Oracle EBS 12.2 allows seamless integration of custom applications into the framework by:

Registering the new product in FND tables.
Creating product schemas.
Setting up directory structures.
Running AutoConfig to finalize the configuration.

This approach ensures your custom product behaves like any standard EBS module, with full support for patches, concurrent programs, forms, and more.

Tuesday, September 8, 2020

Configuring Sendmail with a Smart Host and SMTP Authentication

In this blog post, we’ll examine how to edit the sendmail. mc file to configure Sendmail to relay mail through an external SMTP service, such as SMTP2GO.

This setup will be userfull when:

You want to send emails through a third-party SMTP provider.
Your server is blocked from sending mail directly on port 25.
You must authenticate with your SMTP provider using a specific port (e.g., 2525).

🔧 Step-by-Step Sendmail Configuration

Here’s a snippet from the sendmail.mc file:

#dnl define(`SMART_HOST', `smtp.your.provider')dnl

define(`SMART_HOST', `mail.smtp2go.com')dnl

FEATURE(`authinfo')dnl

define(`RELAY_MAILER_ARGS', `TCP $h 2525')dnl

dnl #

Let’s break it down:

Explanation of Each Line

1. Smart Host Definition

#dnl define(`SMART_HOST', `smtp.your.provider')dnl

This is a commented-out placeholder. You can use this format to set a different SMTP server if needed.

2. Actual Smart Host in Use

define(`SMART_HOST', `mail.smtp2go.com')dnl

This tells Sendmail to route all outbound mail through SMTP2GO.

3. Authentication Info Feature

FEATURE(`authinfo')dnl

This enables authentication support, allowing you to send email through a secure SMTP server that requires login credentials.

4. Relay Mailer Arguments

define(`RELAY_MAILER_ARGS', `TCP $h 2525')dnl

This specifies the port to use when connecting to the SMTP server. Here, we're using port 2525, a common alternative to port 25 (which is often blocked by ISPs).

5. Final Comment Line

dnl #

A dummy comment or placeholder, no operational effect.

Next Steps After Configuration

Once your sendmail.mc file is configured:

Rebuild sendmail configuration

m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf

Create the authinfo file Add your SMTP credentials in /etc/mail/authinfo:

AuthInfo:mail.smtp2go.com "U:root" "I:your_username" "P:your_password"

Hash the authinfo file

makemap hash /etc/mail/authinfo < /etc/mail/authinfo

Restart sendmail
```
systemctl restart sendmail
```

Test the Configuration

Send a test email using mail or sendmail to confirm that everything is working.

echo "Test Email from Sendmail via SMTP2GO" | mail -s "Sendmail Test" your@email.com

Final Thoughts

Using a smart host like SMTP2GO makes your email delivery more reliable and helps avoid issues with ISPs blocking direct mail traffic. With a few lines in sendmail.mc, and some authentication setup, you're good to go!

Saturday, July 18, 2020

Queryable inventory could not determine the current opatch status. Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'

oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >./datapatch -verbose

SQL Patching tool version 12.2.0.0.0 on Fri May 8 16:09:48 2020

Connecting to database...OK

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_5359.lst

catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts

catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any

Bootstrapping registry and package to current versions...done

Queryable inventory could not determine the current opatch status.

Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'

for the complete error.

Prereq check failed, exiting without installing any patches.

[i] User below query to check the sqlpatch status

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

select dbms_sqlpatch.verify_queryable_inventory from dual

ERROR at line 1:

ORA-04063: package body "SYS.DBMS_SQLPATCH" has errors

[ii] Try tp compile the sqlpatch using prvtsqlpatch.plb

SQL> @prvtsqlpatch.plb

Session altered.

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY DBMS_SQLPATCH:

LINE/COL ERROR

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

226/9 PL/SQL: SQL Statement ignored
226/16 PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB
244/5 PL/SQL: SQL Statement ignored
250/25 PL/SQL: ORA-00932: inconsistent datatypes: expected CLOB got -

Session altered.

[iii] Drop sys.registery$sqlpatch table

SQL>create table registry$sqlpatch_org as select * from registry$sqlpatch ;

SQL> drop table sys.registry$sqlpatch

2 ;

Table dropped.

[iv]recreate the table by using the

SQL> @catsqlreg.sql

Session altered.

Table created.

View created.

Synonym created.

Grant succeeded.

PL/SQL procedure successfully completed.

Grant succeeded.

Synonym created.

Session altered.

SQL>

[v]compile the sqlpatch

SQL> @prvtsqlpatch.plb

Session altered.

Package body created.

No errors.

Session altered.

SQL>

[vi]Query the SQL patch

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------
ORA-20013: DBMS_QOPATCH ran mostly in non install area

[vii] select the datapatch directories and drop recreate the directories

SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME like '%OPATCH%';

DIRECTORY_NAME

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

DIRECTORY_PATH

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

OPATCH_LOG_DIR

/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

OPATCH_SCRIPT_DIR

/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

OPATCH_INST_DIR

/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

SQL> drop directory OPATCH_INST_DIR;

Directory dropped.

SQL> drop directory OPATCH_LOG_DIR;

Directory dropped.

SQL> drop directory OPATCH_SCRIPT_DIR;

Directory dropped.

SQL> create directory OPATCH_INST_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/OPatch';

Directory created.

SQL> create directory OPATCH_LOG_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch';

Directory created.

SQL> create directory OPATCH_SCRIPT_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch';

Directory created.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[Viii]check the datapath with prereq parameter

oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >./datapatch -prereq

SQL Patching tool version 12.2.0.0.0 on Fri May 8 18:38:26 2020

Connecting to database...OK

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_8130.lst

catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts

catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any

Determining current state...done

Adding patches to installation queue and performing prereq checks...done

Installation queue:

Nothing to roll back

The following patches will be applied:

20887355 ()

27264965 ()

SQL Patching tool complete on Fri May 8 18:39:02 2020

[IX] Run the datapatch with verbose

oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >./datapatch -verbose

SQL Patching tool version 12.2.0.0.0 on Fri May 8 18:40:04 2020

Connecting to database...OK

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_8716.lst

catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts

catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Patch 20204035 ():

Installed in binary and the SQL registry

Patch 20887355 ():

Installed in the binary registry only

Patch 22731026 ():

Installed in binary and the SQL registry

Patch 27264965 ():

Installed in the binary registry only

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

The following patches will be applied:

20887355 ()

27264965 ()

Installing patches...

Patch installation complete. Total patches installed: 2

Validating logfiles...

Patch 20887355 apply: SUCCESS

logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/20887355/18990781/20887355_apply_OMCLONE_2020May08_18_40_35.log (no errors)

Patch 27264965 apply: SUCCESS

logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/27264965/21812491/27264965_apply_OMCLONE_2020May08_18_40_38.log (no errors)

SQL Patching tool complete on Fri May 8 18:48:51 2020

Friday, May 8, 2020

Resolving Datapatch `ORA-20013` Error – DBMS_QOPATCH ran mostly in non install area

When applying Oracle Database patches using datapatch, you might run into an error that halts patching. One such issue occurs when the Queryable Inventory is not set up correctly, triggering this message:

ORA-20013: DBMS_QOPATCH ran mostly in non install area

In this post, we will examine the root cause and explain how to fix this issue by correcting directory objects in the database.

While running datapatch -prereq from the $ORACLE_HOME/OPatch directory, you see:This means that the DBMS_QOPATCH package is unable to read from the correct directories where patching information is stored.

oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >./datapatch -prereq
SQL Patching tool version 12.2.0.0.0 on Fri May 8 18:32:30 2020
Copyright (c) 2014, Oracle. All rights reserved.

Connecting to database...OK
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_7949.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'
for the complete error.

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 for information on how to resolve the above errors.

SQL Patching tool complete on Fri May 8 18:32:31 2020
oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >sqlplua
sh: sqlplua: not found.
oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 8 18:32:37 2020

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------
ORA-20013: DBMS_QOPATCH ran mostly in non install area

Step 1: Check the Current Directory Objects

SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME like '%OPATCH%';

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
OPATCH_LOG_DIR
/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

OPATCH_SCRIPT_DIR
/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

OPATCH_INST_DIR
/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

OPATCH_INST_DIR is pointing to a wrong or outdated path, such as $ORACLE_HOME/QOpatch instead of $ORACLE_HOME/OPatch.

Step 2: Drop the Invalid Directory Objects

SQL> drop directory OPATCH_INST_DIR;

Directory dropped.

SQL> drop directory OPATCH_LOG_DIR;

Directory dropped.

SQL> drop directory OPATCH_SCRIPT_DIR;

Directory dropped.

Step 3: Recreate the Directory Objects Correctly

SQL> create directory OPATCH_INST_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/OPatch';

Directory created.

SQL> create directory OPATCH_LOG_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch';

Directory created.

SQL> create directory OPATCH_SCRIPT_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch';

Directory created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Step 4: Re-run `datapatch -prereq`

oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >./datapatch -prereq

SQL Patching tool version 12.2.0.0.0 on Fri May 8 18:38:26 2020
Copyright (c) 2014, Oracle. All rights reserved.
Connecting to database...OK
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_8130.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
Nothing to roll back
The following patches will be applied:
20887355 ()
27264965 ()

SQL Patching tool complete on Fri May 8 18:39:02 2020
oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >

Conclusion

This issue occurs when the database cannot locate or access the patch-related directories. Ensuring that OPATCH_* directory objects point to the correct locations within $ORACLE_HOME is critical for a successful patch application.

Always refer to MOS Note 1609718.1 for official guidance on resolving queryable inventory errors.

It's a good habit to check and recreate OPATCH directories after cloning environments or copying ORACLE_HOMEs between servers.

Tuesday, February 18, 2020

ouafDatabasePatch.sh gives java.sql.SQLException: ORA-01017: invalid username/password; logon denied

java.sql.SQLException: ORA-01017: invalid username/password; logon denied

When we are trying to apply a patch in CCB we had came across the below error as invalid username and password, as the hashed password might not be in sync with the application layer and database layer, we need to perform the below steps to update the password and proceed through the patching

Step 1 trying to apply the database patch

[appccbt@omccbtst ORACLE]$ sh ouafDatabasePatch.sh

Enter the target database type (O/M/D) [O]: O

Enter the username that owns the schema: CISADM

Enter the password for the CISADM user:

Enter the name of the Oracle Database Connection String: omccbtst.<domain>.com:1590:OMTRAIN

Couldn't connect to database ORACLE omccbtst.<domain>.com:1590:OMTRAIN CISADM : java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Step 2: update the database patch by using invokeDBUpdatePatch.sh command from the ouaf home/bin

Once the command had been executed we need to provide the new password as the input

[appccbt@omccbtst bin]$ sh invokeDBUpdatePatch.sh -p
invokeDBUpdatePatch.sh: option requires an argument -- p
INFO: Loaded file:/u06/app/oracle/product/fmw/ouaf/OMTCCB/splapp/standalone/config/hibernate.properties from classpath: {hibernate.connection.password=ENCKS(EaodgSKfOmYz0giX+yN/8QliK1eRwWl5FLkIC9BYFWk=), hibernate.ucp.min_size=1, hibernate.ucp.connection_wait_timeout=5, hibernate.query.substitutions=true 'Y', false 'N', hibernate.cache.use_second_level_cache=false, hibernate.show_sql=false, hibernate.ucp.inactive_connection_timeout=300, hibernate.jdbc.batch_size=30, hibernate.jdbc.fetch_size=100, hibernate.ucp.max_size=30, hibernate.max_fetch_depth=2, hibernate.connection.release_mode=on_close, hibernate.ucp.max_idle_time=0, hibernate.connection.username=CISADM, hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver, hibernate.ucp.max_statements=50, hibernate.connection.provider_class=com.splwg.shared.common.UCPConnectionProvider, hibernate.ucp.jmx_enabled=false, hibernate.transaction.factory_class=org.hibernate.transaction.JDBCTransactionFactory, hibernate.dialect=org.hibernate.dialect.Oracle10gDialect, hibernate.connection.url=jdbc:oracle:thin:@//omccbtst.<domain>.com:1590/OMTRAIN, hibernate.query.factory_class=org.hibernate.hql.internal.classic.ClassicQueryTranslatorFactory}
INFO: Loaded file:/u06/app/oracle/product/fmw/ouaf/OMTCCB/splapp/standalone/config/spl.properties from classpath: {spl.runtime.cobol.sql.cursoredCache.maxRows=10, com.oracle.ouaf.system.keystore.hmac_key_alias=ouaf.system.hmac, spl.tools.loaded.applications=base,ccb,cm, spl.runtime.environ.init.dir=/u06/app/oracle/product/fmw/ouaf/OMTCCB/etc, spl.runtime.environ.SPLEBASE=/u06/app/oracle/product/fmw/ouaf/OMTCCB, com.oracle.ouaf.system.keystore.mode=CBC, spl.geocodeDatasource.url=, calendar.japanese.eras=name:Taisho,abbr:T,since:1912-07-30,endDate:1926-12-24,japAbbr:大正;name:Showa,abbr:S,since:1926-12-25,endDate:1989-01-07,japAbbr:昭和; name:Heisei,abbr:H,since:1989-01-08,endDate:,japAbbr:平成, ouaf.batch.onlineLogDir=/u06/app/oracle/product/fmw/ouaf/sploutput/OMTCCB, com.oracle.ouaf.system.truststore.passwordFileName=/u06/app/oracle/product/fmw/ouaf/OMTCCB/ks/.ouaf_truststore_pass, com.oracle.ouaf.flush.jndi.provider.url=t3s://omccbtst.<domain>.com:8004, com.oracle.ouaf.flush.jms.disabled=true, com.oracle.ouaf.truststore.type=JCEKS, spl.runtime.cobol.cobrcall=false, com.oracle.ouaf.flush.jms.connection=jms/OUFlushConnectionFactory, com.oracle.ouaf.system.keystore.file=/u06/app/oracle/product/fmw/ouaf/OMTCCB/ks/.ouaf_keystore, com.oracle.ouaf.flush.jms.requestTopic=jms/OUFlushRequestTopic, jmx.remote.x.password.file=scripts/ouaf.jmx.password.file, com.oracle.ouaf.xsl.dir=file:////u06/app/oracle/product/fmw/ouaf/OMTCCB/splapp/xai/schemas, spl.runtime.environ.SPLOUTPUT=/u06/app/oracle/product/fmw/ouaf/sploutput/OMTCCB, spl.runtime.sql.highValue=, jmx.remote.x.access.file=scripts/ouaf.jmx.access.file, com.oracle.ouaf.system.database.disableTagging=false, spl.runtime.oracle.statementCacheSize=300, spl.runtime.cobol.sql.fetchSize=150, spl.runtime.options.allowSystemDateOverride=true, com.oracle.ouaf.system.keystore.passwordFileName=/u06/app/oracle/product/fmw/ouaf/OMTCCB/ks/.ouaf_storepass, com.oracle.XPath.LRUSize=, spl.geocodeDatasource.contextFactory=weblogic.jndi.WLInitialContextFactory, spl.runtime.service.extraInstallationServices=CILTINCP, com.oracle.ouaf.xsd.dir=file:////u06/app/oracle/product/fmw/ouaf/OMTCCB/splapp/xai/schemas, spl.runtime.options.isFCFEnabled=false, com.oracle.ouaf.system.keystore.padding=PKCS5Padding, spl.runtime.cobol.encoding=UTF8, spl.runtime.cobol.sql.cache.maxTotalEntries=1000, ouaf.database.session.setInstallationTimeZone=false, ouaf.accessiblity.features=false, com.oracle.XPath.flushTimeout=, spl.runtime.performSignedNumberValidation.C1=false , com.oracle.ouaf.flush.jms.responseTopic=jms/OUFlushResponseTopic, spl.geocodeDatasource.password=, spl.geocodeDatasource.user=, com.oracle.ouaf.system.truststore.file=/u06/app/oracle/product/fmw/ouaf/OMTCCB/ks/.ouaf_truststore, spl.runtime.utf8Database=true, com.oracle.ouaf.system.keystore.alias=ouaf.system, spl.runtime.cobol.sql.disableQueryCache=false, com.splwg.schema.newValidations.C1=false, com.splwg.schema.newValidations.F1=true, com.oracle.ouaf.system.keystore.type=JCEKS, com.oracle.ouaf.flush.jndi.factory.initial=weblogic.jndi.WLInitialContextFactory, spl.runtime.environ.isWebExpanded=false}
Enter the password (or hit ENTER to quit):
Re-enter the value:
- 2020-02-18 14:52:33,308 [main] INFO (shared.common.DBUpdatePatchingCredentials) DB Config Info record in the table CI_WFM_OPT has been successfully updated has been successfully updated with new password.
- 2020-02-18 14:52:33,311 [main] INFO (shared.common.DBUpdatePatchingCredentials) Committed changes.

[appccbt@omccbtst bin]$

Step 3 Run ouafDatabasePatch.sh command to apply the patch in CCB database

[appccbt@omccbtst ORACLE]$ sh ouafDatabasePatch.sh

Enter the target database type (O/M/D) [O]:

Enter the username that owns the schema: CISADM

Enter the password for the CISADM user:

Enter the name of the Oracle Database Connection String: omccbtst.<domain>.com:1590:OMTRAIN

Target Schema is a Production Schema

Ready to process patches, Do you want to continue? (Y/N): Y

Working Directory: OMTRAIN001

***********************************

Setting up language file: OMTRAIN001/CDXPatch.lang
exit value: 0

Applying 23621236 ...

Writing to log file: OMTRAIN001/log23621236.log

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

--Applying patch 23621236 at 02-18-2020 14:54:21 using $LastChangedRevision: 43302 $

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

--Copying language information

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

--Patch 23621236 applied successfully at 02-18-2020 14:54:21

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

Patch applied successfully..

0
[appccbt@omccbtst ORACLE]$

Friday, September 25, 2020

How to Splice a Custom Product in Oracle E-Business Suite R12.2 Using adsplice

Prerequisites

Step-by-Step: Running AD Splicer

Session Walkthrough

Control File and Custom Product Registration

Warning Messages

Product Registration

AutoConfig Execution

Post-Splice Actions

Summary

Tuesday, September 8, 2020

Configuring Sendmail with a Smart Host and SMTP Authentication

🔧 Step-by-Step Sendmail Configuration

Explanation of Each Line

1. Smart Host Definition

2. Actual Smart Host in Use

3. Authentication Info Feature

4. Relay Mailer Arguments

5. Final Comment Line

Next Steps After Configuration

Test the Configuration

Final Thoughts

Saturday, July 18, 2020

Queryable inventory could not determine the current opatch status. Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'

Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'

226/9 PL/SQL: SQL Statement ignored226/16 PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB244/5 PL/SQL: SQL Statement ignored250/25 PL/SQL: ORA-00932: inconsistent datatypes: expected CLOB got -

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;VERIFY_QUERYABLE_INVENTORY--------------------------------------------------------------------------------ORA-20013: DBMS_QOPATCH ran mostly in non install area

Friday, May 8, 2020

Resolving Datapatch ORA-20013 Error – DBMS_QOPATCH ran mostly in non install area

Step 1: Check the Current Directory Objects

Step 2: Drop the Invalid Directory Objects

Step 3: Recreate the Directory Objects Correctly

Step 4: Re-run datapatch -prereq

Conclusion

Tuesday, February 18, 2020

ouafDatabasePatch.sh gives java.sql.SQLException: ORA-01017: invalid username/password; logon denied

java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Couldn't connect to database ORACLE omccbtst.<domain>.com:1590:OMTRAIN CISADM : java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Step 3 Run ouafDatabasePatch.sh command to apply the patch in CCB database

How to Splice a Custom Product in Oracle E-Business Suite R12.2 Using `adsplice`

226/9 PL/SQL: SQL Statement ignored
226/16 PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB
244/5 PL/SQL: SQL Statement ignored
250/25 PL/SQL: ORA-00932: inconsistent datatypes: expected CLOB got -

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------
ORA-20013: DBMS_QOPATCH ran mostly in non install area

Resolving Datapatch `ORA-20013` Error – DBMS_QOPATCH ran mostly in non install area

Step 4: Re-run `datapatch -prereq`