Sunday, 14 June 2020

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

Thursday, 11 June 2020

Connecting to a Database from SQLDeveloper fails with IO Error: The Network Adapter Could Not Establish The Connection

Issue: New Users were trying to access a 3-node RAC Oracle Database system using SQLDeveloper

Below is the connection string provided to user for Oracle Database Connection. RAC Virtual IP is provided to connect to Database.


TESTDB01=
       (DESCRIPTION=
               (ADDRESS=(PROTOCOL=tcp)(HOST=test001-vip1.domain.com)(PORT=1521))
               (ADDRESS=(PROTOCOL=tcp)(HOST=test002-vip1.domain.com)(PORT=1521))
               (ADDRESS=(PROTOCOL=tcp)(HOST=test003-vip1.domain.com)(PORT=1521))
           (CONNECT_DATA=
               (SERVICE_NAME=TESTDB01)
               (INSTANCE_NAME=TESTDB01)
           )
       )


User tried to connect using one of the VIP and got the below error in SQLDeveloper.
Status : Failure -Test failed: IO Error: The Network Adapter Could Not Establish The Connection.

Analysis:

Verified the Oracle Database Services and Listener Services are running fine.
Other Users were able to connect to SQLDeveloper using the same Connection String.

Solution:

User does not have network communication open to port 1521 on Oracle RAC Virtual IP.

ping  test001-vip1.domain.com
ping  test002-vip1.domain.com
ping  test003-vip1.domain.com

telnet test001-vip1.domain.com 1521
telnet test002-vip1.domain.com 1521
telnet test003-vip1.domain.com 1521

Ping to VIP address works fine but Telnet to port 1521 on the VIP address is giving "Connect Failed" Error.

When Network communication is opened on port 1521 on all the Oracle RAC Virtual IP servers/Addresses, User can connect to Oracle Database using SQLDeveloper.