April, 2018 - Apps DBA Diaries

Resolve ORA-28545 When trying to access database link between Oracle database and MSSQL

Heterogenous database link between Oracle database and MSSQL was created using the below command.

SQL> create public database link mssql connect to “integration” identified by “*****” using ‘dg4msql’;

Database link created.

Error Message

SQL> select sysdate from dual@mssql
2 ;
select sysdate from dual@mssql
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MSSQL

LISTENER_GTWY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclegtwy.dmn)(PORT = 1526))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
)
)
(SID_NAME=dg4msql)
(ORACLE_HOME=/orabin/gateway_mssql/product/12.1.0/tghome_1)
(ENV=”LD_LIBRARY_PATH=/orabin/gateway_mssql/product/12.1.0/tghome_1/dg4msql/driver/lib:/orabin/gateway_mssql/product/12.1.0/tghome_1/lib”)
(PROGRAM=dg4msql)

Resolution
Listener file on the Oracle Database gateway server should look like below

LISTENER_GTWY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =oraclegtwy.dmn)(PORT = 1526))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
)
)

SID_LIST_LISTENER_GTWY =————————>Missing
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=/orabin/gateway_mssql/product/12.1.0/tghome_1)
(ENV=”LD_LIBRARY_PATH=/orabin/gateway_mssql/product/12.1.0/tghome_1/dg4msql/driver/lib:/orabin/gateway_mssql/product/12.1.0/tghome_1/lib”)
(PROGRAM=dg4msql)
)
)

How to Resolve ORA-12514 error While accessing Oracle database link?

Our client had requirement to pull data from MSSQL server to Oracle Database. I installed Oracle Gateway Server for MSSQL and configured database link to access the MSSQL Database.
When trying to access a heterogenous database link between Oracle Database 12c and MSSQL database, some users were getting the below error
ORA-12154: TNS:could not resolve the connect identifier specified.

Analysis

Database link was created on the Oracle Database environment to access data from MSSQL Server.
Database link was created using below command

create public database link DBLINK_INTG connect to “MYSQL” identified by “Password” using ‘dg4msql’;

Error

SQL>select sysdate from dual@DBLINK_INTG;
ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 – “TNS:could not resolve the connect identifier specified”
*Cause: A connection to a database or other service was requested using
a connect identifier, and the connect identifier specified could not
be resolved into a connect descriptor using one of the naming methods
configured. For example, if the type of connect identifier used was a
net service name then the net service name could not be found in a
naming method repository, or the repository could not be
located or reached.
*Action:
– If you are using local naming (TNSNAMES.ORA file):
– Make sure that “TNSNAMES” is listed as one of the values of the
NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
(SQLNET.ORA)
– Verify that a TNSNAMES.ORA file exists and is in the proper
directory and is accessible.
– Check that the net service name used as the connect identifier
exists in the TNSNAMES.ORA file.
– Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
file. Look for unmatched parentheses or stray characters. Errors
in a TNSNAMES.ORA file may make it unusable.
– If you are using directory naming:
– Verify that “LDAP” is listed as one of the values of the
NAMES.DIRETORY_PATH parameter in the Oracle Net profile
(SQLNET.ORA).
– Verify that the LDAP directory server is up and that it is
accessible.
– Verify that the net service name or database name used as the
connect identifier is configured in the directory.
– Verify that the default context being used is correct by
specifying a fully qualified net service name or a full LDAP DN
as the connect identifier
– If you are using easy connect naming:
– Verify that “EZCONNECT” is listed as one of the values of the
NAMES.DIRETORY_PATH parameter in the Oracle Net profile
(SQLNET.ORA).
– Make sure the host, port and service name specified
are correct.

Solution

Recreate Heterogenous Database link using below command

create public database link DBLINK_INTG connect to “MYSQL” identified by “Password” using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Test1.domain.com)(PORT=1524))(CONNECT_DATA=(SID=dg4msql))(HS=OK))’

Oracle EBS Concurrent Manager Troubleshooting

In Oracle EBS version 12.2.6, Internal concurrent manager had an unexpected Outage.
ICM logfile has the error message ORA-04068: existing state of packages has been discarded
====================

Shutting down Internal Concurrent Manager : 30-JAN-2018 12:09:54

List of errors encountered:
…………………………………………………………………..

_ 1 _
Error in DBMS lock request by handle. (ROUTINE=AFPGMG) (REASON=ORACLE
error 4068 in afpdlrh

Cause: afpdlrh failed due to ORA-04068: existing state of packages has
been discarded
ORA-04061: existing state of package body “APPS.FND_DCP” has been
invalidate

==========================================
Issue Resolution

1. Shut down the Concurrent manager completely using adcmctl.sh

cd $ADMIN_SCRIPTS_HOME

adcmctl.sh stop apps/<appspassword>

2. Compile the invalids in database

SQL>alter package FND_DCP compile body;

OR

Run the utlrp.sql script to recompile all the invalid objects in the database.

3. Startup Concurrent manager using adcmctl.sh

cd $ADMIN_SCRIPTS_HOME

adcmctl.sh start apps/<appspassword>

4. Verify Oracle EBS Concurrent manager is running fine.

Command to check LUN details on a Linux server

Logical Unit Number (LUN) is part of Storage Management in Oracle Automated Storage Management (ASM).

Below command is useful to check the available LUN’s on a linux server.

As root user,

Run the command ls -la /dev/disk/by-id/

Sample Output below:

[root@erptestdb ~]# ls -la /dev/disk/by-id/
total 0
drwxr-xr-x 2 root root 240 Jan 29 16:17 .
drwxr-xr-x 6 root root 120 Jan 29 2018 ..
lrwxrwxrwx 1 root root 9 Jan 29 2018 scsi-3******************************** -> ../../sdd
lrwxrwxrwx 1 root root 10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdd1
lrwxrwxrwx 1 root root 9 Jan 29 2018 scsi-3******************************** -> ../../sdf
lrwxrwxrwx 1 root root 10 Jan 29 16:17 scsi-3********************************-part1 -> ../../sdf1
lrwxrwxrwx 1 root root 9 Jan 29 2018 scsi-3******************************** -> ../../sdc
lrwxrwxrwx 1 root root 10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdc1
lrwxrwxrwx 1 root root 9 Jan 29 2018 scsi-3********************************-> ../../sdb
lrwxrwxrwx 1 root root 10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdb1
lrwxrwxrwx 1 root root 9 Jan 29 2018 scsi-3******************************** -> ../../sde
lrwxrwxrwx 1 root root 10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sde1
[root@erptestdb ~]#

Oracle Applications Patching : ADOP Prepare phase failed due to missing information in FND_NODES.

Applying Patches to Oracle EBS and Oracle Database is one of the main activities for an Oracle database administration.We Often get errors during Oracle patching, which could be due to environment issue or a configuration issue or an Oracle Bug.

Checking Patch logfiles and Oracle Patching worker logfiles is very important to identify and troubleshoot the patching error.

Recently, while i am applying patch on a Production environment, Prepare phase in Oracle EBS online patching failed due to below error

Error Details:Information missing in FND_NODES table for one or more application tier nodes.

=================================================
ADOP (C.Delta.9)
Session ID: 19
Node: prodapp01
Phase: prepare
Log: /applmgr/Oracle/PRODAPP/fs_ne/EBSapps/log/adop/19/20171230_095636/adop.log
===========================================================================

Validating configuration on node: [prodapp01].
Log: /applmgr/Oracle/PRODAPP/fs_ne/EBSapps/log/adop/19/20171230_095636/prepare/validate/rwerpprodapp01
[ERROR]: Information missing in FND_NODES table for one or more application tier nodes. For details, refer to log file on the relevant node.
[WARNING]: There could be issues while validating the ports used for E-Business Suite instance against ports used in /etc/services. Refer the log file for more details.
[WARNING]: Either some of the required entries in /etc/hosts file might be missing (e.g. localhost or hostname) OR the file /etc/hosts could not be read.
[WARNING]: Found invalid cross references in FS config files.
[UNEXPECTED]Error occurred running “perl /applmgr/Oracle/PRODAPP/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl -contextfile=/applmgr/Oracle/PRODAPP/fs2/inst/apps/PRODAPP_prodapp01/appl/admin/PRODAPP_prodapp01.xml -patchctxfile=/applmgr/Oracle/PRODAPP/fs1/inst/apps/PRODAPP_prodapp01/appl/admin/PRODAPP_prodapp01.xml -phase=prepare -logloc=/applmgr/Oracle/PRODAPP/fs_ne/EBSapps/log/adop/19/20171230_095636/prepare/validate/prodapp01 -promptmsg=hide”
[UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on prodapp01

[STATEMENT] Please run adopscanlog utility, using the command

“adopscanlog -latest=yes”

to get the list of the log files along with snippet of the error message corresponding to each log file.

adop exiting with status = 1 (Fail)
=================================================

Analysis
The environment on which issue is reported has two middle Tier nodes. Primary node is the Master node and DMZ tier is the slave node.
Due to autoconfig failure on Oracle EBS Slave node, Support_Web column is not updated in FND_NODES after autoconfig execution.
SUPPORT_WEB column for DMZ node is null in FND_NODES

Solution

1. Bring down Middle Tier Services
2. Run autoconfig on all the Middle Tier nodes
3. Startup the Middle Tier Services
4. Verify that FND_NODES table has correct information
5. Restart adop Prepare Phase

How to Retreive password of a front end user in Oracle EBS?

Sometimes, an Oracle database administrator requires to login as the user to reproduce the issue and troubleshoot.It is easy to recover the password of front end users in Oracle EBS by creating the Decrypt package.

Please be cautious and do not misuse this script. This should be used in test environments only.

Create package and Package body

— Package
CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
/
–Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;
END get_pwd;
/

Execute the below Query to retrieve the password

SELECT usr.user_name,
get_pwd.decrypt
((SELECT (SELECT get_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name =
(SELECT SUBSTR
(fnd_web_sec.get_guest_username_pwd,
1,
INSTR
(fnd_web_sec.get_guest_username_pwd,
‘/’
)
– 1
)
FROM DUAL)),
usr.encrypted_user_password
) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = ‘&USER_NAME’;