ebs 12.2 clone fails in the FMW pre-requisite section

The goal of this post is to give details on the issue which we had encountered when we were trying to do a clone for EBS 12.2.6 to target running on OEL 8.  Source was running on OEL7. As part of cloning steps, we need to run adcfgclone.pl appsTier dualfs for configuring the file system. As part of the process, the steps were failing on the pre checks for FMW. It was erroring out with

=====================================

Expected result: One of oracle-7,redhat-7,redhat-6,oracle-6,oracle-5,enterprise-5.4,enterprise-4,enterprise-5,redhat-5.4,redhat-4,redhat-5,SuSE-10,SuSE-11

Actual Result: oracle-8.5

Check complete. The overall result of this check is: Failed <<<<

=======================================

OS being used was OEL8 and thus it shouldn't have been an issue. Upon deep dive into the configuration files, i found that the pre checks for FMW use the file refhost.xml which basically resides under EBSapps/comn/clone/prereq/webtier/Scripts/prereq/linux64. So as a workaround, i added the copied the contents for oracle-7 and made it look like as oracle-8 and pasted it at the top of the file.

Then i reran the adcfgclone again and now it passed the prechecks for FMW. The intention of the post is just a way for workaround and it should not be a solution.

IMPDP for one single table in multitenant database in a different custom schema

Few days back, we got a request to import one table to our PDB. The main point to note is import will run through sys user and it has to be imported under a custom schema. The source was 19c and target DB version was 12c. The dump file along with log files were shared with us. Now, the environment where we need to do the import consists of the DB running on VM DB system on OCI. It consists of one root and one pluggable container. 

                Image source:https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89236

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 15 11:36:36 2022

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

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 RPPDB                       READ WRITE NO

SQL>

The table to be imported already exist in our system and it was owned by an another schema FUSION. We created a par file, the contents of which are given below

cat impdp.par

tables=FUSION."table name"

logfile=impdp_tables_expdp.log

parallel=4

encryption_password=****

dumpfile=tables-expdp-%U.dmp

directory=IMPORT

job_name=job_NDE

table_exists_action=truncate

Now using the par file, run the IMPDP command

impdp \"sys/*****@RPPDB as sysdba\" parfile=impdp.par

The post was an small example on how we can do a import of one single table in a multitenant Database. Hope you find it useful. Till then happy learning.

 

FAILED: enableSSH adop ebs 12.2

We recently upgraded our EBS 12.1.3 to EBS 12.2.10 and multi node deployment was done. One was secondary internal nodes and 2 were external DMZ nodes. SSH was manually enabled using native authentication by using the command ssh-keygen. And then configurations were done by copying the primary application tier backups.  For details on how to setup the SSH, you can follow the EBS 12.2 documentation https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm#adsshsetup

Now, in order to sync the patch file system and run file system for all the nodes, we ran the command adop phase=fs_clone in the primary node. Ideally it should have done the ssh to all the nodes and perform the sync. But in our case, inspite of having the manual SSH setup done earlier, adop was not working. It gave an error during validation phase:-SEVERE: com.jcraft.jsch.JSchException: Algorithm negotiation fail.

  at com.jcraft.jsch.Session.receive_kexinit(Session.java:510)

Now in EBS 12.2, either you can setup manual SSH or use the perl script.

 perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl enablessh -contextfile=<Context_file> -hosts=<node1>,<node2>.

We cannot club both of them together. If manual setup has been done, then no need to run the perl script. For all the other application nodes, the bash profile was pointing to EBS 12.1.3 environment and thus adop SSH validation was failing. We removed that part first from the bash profile and repointed it to use EBS 12.2. Adop still failed. Then we remember, someone changed the application OS password after the SSH keys generation. The EBS 12.2 maintenance guide suggests that if the OS password is changed, we need to regenerate the keys again. Then copy the .pub to the authorized_keys file in all the other nodes. Upon doing the changes again, adop phase=fs_clone validation worked fine and rest patching cycle went through.

Through this post, i wanted to highlight my experience. Hope it helps someone.

References:-https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm#adsshsetup

important considerations for EBS database upgrade to 19C

 Nowadays, there is a huge demand for EBS DB upgrade to 19c because 19C database is the latest long term release. You can refer the below diagram on the Database releases and Support Timelines.

I did back to back EBS DB upgrade to 19c which you can refer in my previous posts https://samappsdba.blogspot.com/2020/12/upgrade-EBS-12.2-database-12c12.1.0.2-to-19c19.3-Part-1.html. Now, the purpose of this post is to make everyone aware of the different sort of issues which you can encounter while doing the upgrade and also, i have tried my best to provide the solutions. Also, i have  highlighted some basic terminologies which if known, will ease up the upgrade.

1. Source DB will become the PDB.

Your source Database will be converted into PDB and which then will be attached to CDB. So if your source DB sid is PROD, then your PDB will also be PROD.

2. Running hcheck.sql

This mainly reports the data dictionary issues and thus if you see any errors, you should seek an assistance from Oracle Support. This usually takes time, thus it is required to do this step in advance.

3. CDB listener

As part of 19c upgrade and multitenant conversion, the listener should be started with the CDB name.

4. Characterset Issue

While doing the upgrade, please note down the source characterset and make sure while creating the CDB, the same characterset options are choosen. If there are character sets mismatch, then you will get an error while doing the conversion to multitenant DB.

You can use the below query:-

select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

 In order to know more about the various issues and their solutions, you can through the  metalink note 19c Database with Oracle E-Business Suite R12 Known Issues & Solutions (Doc ID 2662860.1)

 

I hope you find this note useful. Happy upgrade to 19c.

Oracle E-Business Suite 12.2.10 Configuration for DMZ

 This post is about on how we can configure DMZ for Oracle E-Business Suite 12.2.10. Now what is DMZ? From the first tone, it sounds to be something related to Defence where you have a line or border beyond there are restrictive access. In Oracle terminology, the DMZ, which stands for DeMilitarized Zone consists of the portions of a corporate network that are between the corporate intranet and the Internet. The DMZ can be a simple one segment LAN or it can be broken down into multiple regions . The main benefit of a properly-configured DMZ is better security. In the event of a security breach, only the area contained within the DMZ is exposed to potential damage, while the corporate intranet remains somewhat protected.

Source-Metalink

In my setup, the external DMZ node is accessible through LB and also the application file system is not shared between primary and the external node. I had to manually setup the SSH. Also, the setup was done on a cloned environment and thus update hierarchy setup was already in place. Now coming to the steps.

1. Create passwordless connection between internal and external node

ssh-keygen(in the primary node)

this will create two keys private and public keys

copy the public key content and paste it to external node application user's authorized_hosts file.

2. In the primary node:-

update hierarchy type--Already done as part of clone from PROD

3. Add the secondary node:

prerequistes:-

a. Patch admin server should be up and running

b. External node should be able to telnet external host(run & patch file system) admin port

example

telnet internal.example.com 7001--run file system

telnet internal.example.com 7002--patch file system

If the connection fails:

1. In the run file system config.xml, remove any deny and bounce the admin server. Follow the same for patch file system.

If the above doesn't work, then in the respective admin console, context filter in the run/patch file system should be set from the admin console and then stop it from console and start it from server

In the primary

a.run adpreclone in the run & patch file system and in database tier

b. copy the below directories to the external tier

Run File System (FS1)     :  /u01/install/APPS/fs1/EBSapps

Patch File System (FS2)   :  /u01/install/APPS/fs2/EBSapps

Non-Editioned File System(fs_ne)  :  /u01/install/APPS/fs_ne

In the external tier

perl adcfgclone.pl appsTier dualfs

                     Copyright (c) 2002, 2015 Oracle Corporation

                        Redwood Shores, California, USA

                        Oracle E-Business Suite Rapid Clone

                                 Version 12.2

                      adcfgclone Version 120.63.12020000.65

Enter the APPS password :

Enter the Weblogic AdminServer password :

Enter the password for DataSource ISGDatasource :

Do you want to add a node (yes/no) [no] : yes

Verifying: Run file system AdminServer is running

Verifying: Patch file system AdminServer is running

Running: Context clone...

Once the run and patch file system is configured, edit the context file to put the LB details and port etc

CONTEXT VARIABLES TO BE CHANGED FOR THE ABOVE LBR CONFIGURATION

<webentryurlprotocol oa_var="s_webentryurlprotocol">https</webentryurlprotocol>

<webentryhost oa_var="s_webentryhost">partners</webentryhost>

<webentrydomain oa_var="s_webentrydomain">example.com</webentrydomain>

<activewebport oa_var="s_active_webport">443</activewebport>

<login_page oa_var="s_login_page">https://partners.example.com:443/OA_HTML/AppsLogin</login_page>

<EndUserMonitoringURL oa_var="s_endUserMonitoringURL">https://partners.example.com:443/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</EndUserMonitoringURL>

<externURL oa_var="s_external_url">https://partners.example.com:443/OA_HTML/AppsLogin</externURL>

4. run autoconfig in the external tier

run file system

$ . ./u01/install/APPS/EBSapps.env run

$ $INST_TOP/admin/scripts/adautocfg.sh

patch file system

. ./u01/install/APPS/EBSapps.env patch

$ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=context.xml logfile=/tmp/patchctxupload.log

5. Sync Up the Context File and Update Configuration on All Nodes

 In the primary node

$ . ./u01/install/APPS/EBSapps.env run

$ perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

As part of node addition, all the nodes have the information of the managed servers of the other nodes. if these managed servers are not required, we need to delete the manage servers for the other nodes

perl <FND_TOP>/patch/115/bin/txkSetAppsConf.pl -contextfile=<CONTEXT_FILE> -configoption=removeMS -oacore=testserver1.example.com:7201 -forms=testserver2.example.com:7601

in the patch file system, repeat the above steps

In the external node

run file system

$ . ./u01/install/APPS/EBSapps.env run

$ perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

As part of node addition, all the nodes have the information of the managed servers of the other nodes. if these managed servers are not required, we need to delete the manage servers for the other nodes

perl <FND_TOP>/patch/115/bin/txkSetAppsConf.pl -contextfile=<CONTEXT_FILE> -configoption=removeMS -oacore=testserver1.example.com:7201 -forms=testserver2.example.com:7601

or 

from apps.conf and mod_ohs.conf file, the managed servers can be manually removed.

in the patch file system, repeat the above steps

6. Run autoconfig

On all the application nodes

run file system

run autoconfig

In the external nodes

In the primary node

Shut down the Admin Server and the Node Manager on the Patch Edition File System of the primary node as follows:

$ <ADMIN_SCRIPTS_HOME>/adadminsrvctl.sh stop

$ <ADMIN_SCRIPTS_HOME>/adnodemgrctl.sh stop

In the DB node

<RDBMS_OH>/appsutil/scripts/<CONTEXT_NAME>/adautocfg.sh

lsnrctl reload <ORACLE_SID>

7. Login to the internal application node

Profile option

"Node trust level" should be set to external for external server

update list of responsibilities to be visible in external node login page

"Responsibility trust level"  should be set to external for respective responsibilities

8. run autoconfig in internal and external node

    Bounce the services and check

Support implications for EBS R12.1.3|Upgrade to latest EBS 12.2

 If you are one of the customer who are still running Oracle EBS R12 and wanted to continue using Oracle E-Business Suite, now it is the time to upgrade and move on. As you might have read earlier, or even if you are not aware of,  EBS 12.1.3 premier support ending on Dec, 31st, 2021. For support policies on EBS, you can refer the below chart

What does it mean?

EBS 12.1.3 will move to  to Sustaining Support on January 1, 2022.

How does it impact me, if i still need some time to move to latest EBS 12.2.10

You have to use Market Driven Support(MDS) from Oracle by paying an extra amount. For more details, you can see the official Oracle Documentation https://www.oracle.com/a/ocom/docs/market-driven-support-for-e-business-suite.pdf

What sort of support provided by Oracle in Sustaining Support

Diagnostic assistance can be requested for EBS 12.1 issues and to download existing patches and documentation.  Sustaining Support does not include the following:

New updates, fixes, security alerts, data fixes, and critical patch updates

New tax, legal, and regulatory updates

New upgrade scripts

Certification with new third-party products/versions

Certification with new Oracle products

Thus, now it is the time if we still to leverage most favorite ERP which is being used by almost every corner of the world. If you need any assistance, you can visit my previous blog on how to upgrade EBS R12 to EBS 12.2 -https://samappsdba.blogspot.com/2017/10/oracle-e-business-suite-upgrade-from.html

References:-

https://blogs.oracle.com/ebstech/post/reminder-ebs-1213-moves-to-sustaining-support-on-jan-1-2022-updated

EBS 12.2.10: step by step multitenant cloning with 19c Database

 This post is not about how we normally do an EBS 12.2 cloning. You can search many blogs, articles over web. Well, in this post i am going to illustrate on how we can clone an EBS 12.2.10 environment where the Database is 19c multitenant with one CDB and one PDB. The main challenge comes when we want to do a restore the database and rename it based upon our target convention.

Did, any of the below questions come to your mind, when you are about to do these sort of clone

1. When we do a restore/recover the database which is multitenant in nature, then in the target, then how the database will be opened

2. Which script to use to achieve the multitenant clone for EBS

3. How to rename the PDB after clone

High level Steps:-

Run adpreclone on the source database and application nodes.

Back up the full Container Database (CDB) database with archives by using RMAN Hot backup and copy it to the target node.

Clean up the target database and application node.

Copy the source application binaries and database binaries to the target node.

Configure $Oracle_Home on the target database node.

Restore and recover the databases.

Perform post-restore steps on the Target database node.

Configure the application on the target application node.

Perform post-clone steps on the application node.

Start the target application services.

Steps:-

I am not going to explain the clone steps as there are already many blogs, articles available. I am going to jump directly on the main part on how to restore/recover the database. 

Create a pairs file:-

 cat pairsfile.txt

s_undo_tablespace=APPS_UNDOTS1

s_db_oh=/u02/oracle/ABCD/product/19c/db

s_dbhost=fjfjfjjfldw

s_dbSid=BORISPP2

s_pdb_name=PDB_name

s_cdb_name=cdb_name

s_base=/u02/oracle/ABCD/product/19c/base

s_dbuser=oracle

s_dbgroup=oinstall

s_dbhome1=/u03/oracle/ABCD/product/11.2.0.4/data

s_display=localhost:10.0

s_dbCluster=false

s_isDBCluster=n

s_dbport=1526

s_port_pool=6

This pairs file will be used to create the new context file.

Create the context file

perl adclonectx.pl \

contextfile=/u02/oracle/ABCD/product/19c/db/appsutil/PDB_0wbcvrjgtki7uqv.xml\

template=/u02/oracle/ABCD/product/19c/db/appsutil/template/adxdbctx.tmp \

pairsfile=/u02/oracle/ABCD/product/19c/db/appsutil/clone/pairsfile.txt

Configure the DB technology stack

perl adcfgclone.pl dbTechStack /u02/oracle/ABCD/product/19c/db/appsutil/PDB_0wbcvrjgtki7uqv.xml

Create the listener.ora and tnsnames.ora files for the target CDB by running the following commands:

1.Set the environment.

On UNIX:

$ cd /u02/oracle/ABCD/product/19c/db/appsutil

$ source ./txkSetCfgCDB.env -dboraclehome=/u02/oracle/ABCD/product/19c/db

Generate the listener.ora and tnsnames.ora.

$ cd <ORACLE_HOME>/appsutil/bin

$ perl txkGenCDBTnsAdmin.pl -dboraclehome/u02/oracle/ABCD/product/19c/db -cdbnameCDB \

-cdbsid=CDB -dbport=1526 -outdir=$ORACLE_HOME/appsutil/log \

where:

Parameter Description

Cdbname Name of the target container database.

Cdbsid Oracle SID of the target container database instance.

• For single-node database, the value is same as the cdbname.

• For Oracle RAC database, it is the Instance name of the target database.

Israc • Provide the value 'yes' for an Oracle RAC database.

• Provide the value 'no' for a single-node database.

virtualhostname Virtual hostname for the Oracle RAC database. For a single-node database, this parameter should not be used.

Start the listener for the target container database as follows:

On UNIX:

$ cd /u02/oracle/ABCD/product/19c/db/appsutil/scripts/PDB_0wbcvrjgtki7uqv.xml

./adcdblnctl.sh start CDB

Restore the database(Delete the existing datafiles if any before starting the process)

   Start the target in nomount using temporary init/spfile

 

 

set -x export TIMESTAMP=`date +%Y%m%d` export BACKUP_BASE=/u02/oracle/ABCD/product/19c/base export LOGFILE=/u02/oracle/ABCD/product/19c/base/DB_Duplicate-${TIMESTAMP}.log touch ${LOGFILE} rman log=${LOGFILE} << EOF connect auxiliary / run { allocate auxiliary channel aux1 device type disk; allocate auxiliary channel aux2 device type disk; allocate auxiliary channel aux3 device type disk; allocate auxiliary channel aux4 device type disk; allocate auxiliary channel aux5 device type disk; allocate auxiliary channel aux6 device type disk; allocate auxiliary channel aux7 device type disk; allocate auxiliary channel aux8 device type disk; allocate auxiliary channel aux9 device type disk; allocate auxiliary channel aux10 device type disk; allocate auxiliary channel aux11 device type disk; allocate auxiliary channel aux12 device type disk; set newname for datafile '/u03/oracle/ABCD/product/11.2.0.4/data/a_txn_ind02.dbf' to '/binaries/oracle/ABCD/data/a_txn_ind02.dbf'; set newname for datafile '/u03/oracle/ABCD/product/11.2.0.4/data/a_txn_ind10.dbf' to '/binaries/oracle/ABCD/data/a_txn_ind10.dbf'; set newname for datafile '/u03/oracle/ABCD/product/11.2.0.4/data/a_txn_ind15.dbf' to '/binaries/oracle/ABCD/data/a_txn_ind15.dbf'; set newname for datafile '/u03/oracle/ABCD/product/11.2.0.4/data/a_txn_ind21.dbf' to '/binaries/oracle/ABCD/data/a_txn_ind21.dbf'; set newname for datafile '/u03/oracle/ABCD/product/11.2.0.4/data/a_txn_ind33.dbf' to '/binaries/oracle/ABCD/data/a_txn_ind33.dbf'; set newname for datafile '/u03/oracle/ABCD/product/11.2.0.4/data/a_txn_ind38.dbf' to '/binaries/oracle/ABCD/data/a_txn_ind38.dbf'; set newname for datafile '/u03/oracle/ABCD/product/11.2.0.4/data/a_txn_ind46.dbf' to '/binaries/oracle/ABCD/data/a_txn_ind46.dbf'; set newname for datafile '/u03/oracle/ABCD/product/11.2.0.4/data/a_txn_ind47.dbf' to '/binaries/oracle/ABCD/data/a_txn_ind47.dbf'; duplicate database to 'CDB' backup location '/binaries/BKP_12210_TST002' nofilenamecheck; release channel aux1; release channel aux2; release channel aux3; release channel aux4; release channel aux5; release channel aux6; release channel aux7; release channel aux8; release channel aux9; release channel aux10; release channel aux11; release channel aux12; } EOF echo >> ${LOGFILE}

The DB will be open read write mode and the PDB name will have the source PDB name. Thus we will have to rename it based upon our target value

Rename the PDB

If any service already exists with the Target PDB name, delete it as follows:

[oracle@ABCD db]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 22 11:05:42 2021

Version 19.11.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> alter session set container="ABCDT02";

Session altered.

Run the following steps to rename the PDB.

[oracle@ABCD dbs]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 22 11:18:31 2021

Version 19.11.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> alter pluggable database "ABCDT02" close;

alter pluggable database "ABCDT02" close

*

ERROR at line 1:

ORA-65020: pluggable database ABCDT02 already closed

SQL> alter pluggable database "ABCDT02" unplug into '/u02/oracle/ABCD/product/19c/db/dbs/ABCDT02_PDBDesc.xml';

Pluggable database altered.

SQL> drop pluggable database "ABCDT02";

Pluggable database dropped.

SQL> create pluggable database "ABCDPP2" using '/u02/oracle/ABCD/product/19c/db/dbs/ABCDT02_PDBDesc.xml' NOCOPY SERVICE_NAME_CONVERT=('ebs_ABCDT02','ebs_ABCDPP2','ABCDT02_ebs_patch','ABCDPP2_ebs_patch');

Pluggable database created.

SQL> alter pluggable database "ABCDPP2" open read write;

Pluggable database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter pluggable database all save state instances=all;

Pluggable database altered.

SQL>

Run the library update script against the Oracle database.

cd <ORACLE_HOME>/appsutil/install/<CONTEXT_NAME>

$ sqlplus / as sysdba @adupdlib.sql <libext>

Store the target UTL_FILE_DIR values in the target database

You can refer my earlier blog https://samappsdba.blogspot.com/2020/12/upgrade-EBS-12.2-database-12c12.1.0.2-to-19c19.3-Part-1.html on how to do this.

Configure the target database

cd /u02/oracle/ABCD/product/19c/db/appsutil/clone/bin

$ perl adcfgclone.pl dbconfig contextfile

In every cloning methodology, database cloning is the most vital part and if that is complete, half of the work is done. The application clone can be configured by using the adcfgclone.pl appsTier dualfs option, the steps for which i have not covered here and which is very straightforward.

I hope this can be helpful to someone. Till then happy learning

References:-Cloning Oracle E-Business Suite Release 12.2 with Multitenant Database using Rapid Clone (Doc ID 2552208.1)