Resolve Error ORA-28500 while accessing Heterogenous Database link (ORACLE TO MSSQL)

After creating a heterogenous database link from Oracle database  to MSSQL Server, getting error message "ORA-28500"

SQL>select sysdate from dual@DBLINK_MSSQL;
                         *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host
Name and Port Number. {08001}
ORA-02063: preceding 2 lines from MSSQL_INTG


Reason

$ telnet MSSQLDB 1433
MSSQLDB/1433: Name or service not known

$ telnet 10.x.x.x 1433
Trying 10.x.x.x...
Connected to 10.x.x.x (10.x.x.x).
Escape character is '^]'.
^]
telnet> logout
^CConnection closed by foreign host.


I am able to telnet to MSSQL database Server using IP address , but cannot telnet to MSSQL server using hostname


Solution

Contact your Network admin to allow access to 1433 using Servername (MSSQLDB)

(or)

Change the below configuration

1. Set Oracle Home to point to your Gateway Oracle HOME

2. Navigate to $ORACLE_HOME/dg4msql/admin

Change below values in initdg4msql.ora

changed HS_FDS_CONNECT_INFO from MSSQLDB to IP address

#
HS_FDS_CONNECT_INFO=10.x.x.x:1433//wfcdb
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


3. Restart the listener in Gateway Oracle HOME

4. Verify the database link is working fine.

SQL>select sysdate from dual@DBLINK_MSSQL;

==>Above Query should return current system date.

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';