Apps DBA - Apps DBA Diaries

Autoconfig Error : ORA-01422: exact fetch returns more than requested number of rows

Issue: Autoconfig on EBS 12.2 environment fails while running script txkGenADOPWrapper.pl

Below is the error message from logfile

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

Script Name : txkGenADOPWrapper.pl

Script Version : 120.0.12020000.3

ERROR DESCRIPTION:

(*******FATAL ERROR*******

PROGRAM : (/test/app/fs1/inst/apps/test_mtnode1/admin/install/txkGenADOPWrapper.pl)

TIME : Wed Nov 25 04:22:34 2020

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

SQLPLUS error: buffer=

SQL*Plus: Release 10.1.0.5.0 – Production on Wed Nov 25 04:22:33 2020

SQL> SQL> Connected.

SQL> SELECT ad_zd_adop.get_node_type(‘mtnode1’) FROM DUAL

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at “APPS.AD_ZD_ADOP”, line 3010

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

Cause:

FND_NODES table has incorrect node information.

Found an extra row in FND_NODES table where node_name is null after clone.

Solution:

1. Clean up FND_NODES table

sqlplus apps/<password>

sql>exec FND_CONC_CLONE.SETUP_CLEAN;

2.Run Autoconfig on Database node(s).

cd $ORACLE_HOME/appsutil/bin

adconfig.sh

[Provide Database Context file and apps Password]

3. Run Autoconfig on apps tier

Source the RUN filesystem environment file.

cd $AD_TOP/bin

adconfig.sh

[Provide Applictions Context file and apps Password]

What is Split Brain Syndrome in Oracle RAC?

Split brain syndrome occurs when the Oracle RAC nodes are unable to communicate with each other via private interconnect, but the communication between client and RAC node is maintained. This can cause data Integrity issues when the same block is read or updated by two nodes and changes done from one node are overwritten by the other node because the block being changed is not locked.

When a node fails, the failed node is prevented from accessing all the shared disk devices and groups. This methodology is called I/O Fencing, Disk Fencing or Failure Fencing.

The node which first detects that one of the node is not accessible will evict that node from the RAC cluster group.This problem is solved by configuring the heartbeat connections through the same communication channels that are used to access the clients.

What causes Node eviction in Oracle RAC?

What is node eviction?

Node eviction in RAC is done when a heartbeat indicates that a node is not responding,

and the evicted node is re-started to make it a part of cluster.

Causes for RAC node eviction:

Node eviction on Oracle RAC environment can be due to any of the below reasons.

– A failure of any of the major hardware components (CPU, RAM, network interconnect).

– A server that is experiencing RAM swapping.

– When communications to the voting disk is interrupted, causing the disconnected node to be evicted and re-boot.

– Database or ASM hang condition.

Below is the list of important log files to review in case of a node eviction

– Clusterware alert log

– Database alert log

– CSSD agent logs

– CSSD monitor logs

– System Message logs (/var/log/messages)

What is Flashback feature in Oracle Database?

Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window.

Points to Remember

– Flashback Database command can be run either from SQLPLUS or using RMAN Utility.

– FLASHBACK DATABASE command can be used to rewind the database to a target time, SCN or a log sequence number.

– Flashback command works by undoing the changes made to the data files that exist when you run the command.

– Flashback can fix only logical failures, not physical failures.

– If the database control file is restored from backup or re-created, then all existing flashback log information is discarded.

– Avoid using FLASHBACK DATABASE with a target time or SCN that coincides with a NOLOGGING operation, it can cause block corruption.

How to enable Flashback Database feature in Oracle?

Prerequisites for Flashback Database and Guaranteed Restore Points

Flashback Database

Configure the following database settings before enabling Flashback Database:

-Your database must be running in ARCHIVELOG mode.

-You must have a fast recovery area enabled.

-For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area must be in a clustered file system or in ASM.

Guaranteed Restore Points

To use guaranteed restore points,the COMPATIBLE initialization parameter must be set to 10.2.0 or greater.

Steps to enable Flashback Database:

1. Connect to sqlplus as sysdba and set the desired value for Flashback retention target using below command.

SQL>ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

Here, Flashback retention target is set to window of 3 days(4320 Minutes), default value is 1 day (1440 Minutes).

2. Enable the Flashback Database feature for the whole database using the following command:

SQL>ALTER DATABASE FLASHBACK ON;

3.Use the following command to check if Flashback Database is enabled for your target database:

SQL>SELECT FLASHBACK_ON FROM V$DATABASE;

Oracle EBS 12.2.X Login fails with “Unable to create anonymous session. ICX_SESSION_CREATION_FAILED”

Issue:

In a 12.2.6 EBS environment, Login fails with below error

Unable to create anonymous session. ICX_SESSION_CREATION_FAILED (userid=6) exception oracle.apps.fnd.common.PoolException: Exception creating new Poolable object. Encountered a java exception with the message Exception creating new Poolable object. Cause:java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: java.lang.reflect.InvocationTargetException.

Analysis:

The error indicates that there are not enough resources to create a new JDBC session. oacore_server.log has the below error messages

####<Aug 08, 2017 3:43:05 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <[ACTIVE] ExecuteThread: ‘5’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <> <005dy^V9G5e9xW6RJMU4TB0000xv001lSB> <1591994585553> <BEA-000627> <Reached maximum capacity of pool “EBSDataSource”, making “0” new resource instances instead of “1”.>
####<Aug 08, 2017 3:43:07 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <pool-2-thread-1> <<anonymous>> <> <*******************> <887561> <BEA-000627> <Reached maximum capacity of pool “EBSDataSource”, making “0” new resource instances instead of “1”.>
####<Aug 08, 2017 3:43:15 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <[ACTIVE] ExecuteThread: ‘5’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <> <005dy^V9G5e9xW6RJMU4TB0000xv001lSB> <1591994595562> <BEA-000627> <Reached maximum capacity of pool “EBSDataSource”, making “0” new resource instances instead of “1”.>
####<Aug 08, 2017 3:43:15 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <pool-2-thread-1> <<anonymous>> <> <*******************> <955123> <BEA-000627> <Reached maximum capacity of pool “EBSDataSource”, making “0” new resource instances instead of “1”.>

Number of processes capacity for the Datasource “EBSDatasource” is exhausted.

Solution:

1. Increase the number of sessions in EBSDatasource

Click on Lock and Edit, Go to Connection Pool Tab, Increase the value in Number of sessions field.

Click on Activate Changes.

This change does not require any restart of services.

(or)

Alternate Solution:

2. Bounce oacore_server to release any inactive sessions and free up the resources

cd $ADMIN_SCRIPTS_HOME

admanagedsrvctl.sh stop oacore_server

admanagedsrvctl.sh start oacore_server

Query to check installed modules in Oracle EBS

The below query query gives the list of Installed/Not Installed/Shared Product Modules in Oracle E-Business Suite (EBS) application

set pages 20000;
col application_id for 9999;
col application_name for A50;
col status for A1;
col application_short_name for A10;
select fa.application_id,
fa.application_short_name,
fpi.status,
fatl.application_name
from
fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fatl
where
(
fa.application_id = fpi.application_id and
fa.application_id = fatl.application_id and
fatl.language = ‘US’
)
order by fa.application_short_name;

Check for values in Status column of the output, usually either I or N or S.

I – Installed
S – Shared
N – Not Licensed

Query to check AD and TXK Code level in a Oracle E-Business Suite environment

While preparing patch analysis on EBS environments, some times we find in README file that AD/ TXK level should be at a certain code level to apply the patch.We can use the below query to check AD and TXK Code level.

select ABBREVIATION, NAME, codelevel FROM AD_TRACKABLE_ENTITIES where abbreviation in (‘txk’,’ad’);

Sample Output

$sqlplus apps

SQL> select ABBREVIATION, NAME, codelevel FROM AD_TRACKABLE_ENTITIES where abbreviation in (‘txk’,’ad’);

ABBREVIATION NAME CODELEVEL
—————————— —————————————- ———-
ad Applications DBA C.11
txk Oracle Applications Technology Stack C.11