Adpreclone and Adcfgclone

 Adpreclone and Adcfgclone

Adpreclone.pl script prepare the source system to be cloned by collecting information about source

system. Create a cloning stage area,generate template and driver from existing files that contain source specific hard coded value.

When you run “adpreclone.pl dbTier” on DB side

Following directories will be created in the ORACLE_HOME/appsutil/clone/Jlib, db, data where “Jlib” relates to libraries “db” will contain the techstack information, “data” will contain the information related to datafiles and required for cloning.

1) Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv

2) Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/Sid_context.xml

3) Prepare database for cloning:  This includes creating database control file script and datafile location information file at

$ORACLE_HOME/appsutil/templateadcrdbclone.sql, dbfinfo.lst

4) Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driverdata.drv

5)Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsutil

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

When you run “adpreclone.pl appsTier” On Apps Side

This will create stage directory at $COMMON_TOP/clone. This also run in two steps.

Techstack:  Creates template files for Oracle_iAS_Home/appsutil/template and Oracle_806_Home/appsutil/template

Creates Techstack driver files for IAS_ORACLE_HOME/appsutil/driver/instconf.drv and 806_ORACLE_HOME/appsutil/driver/instconf.drv

APPL_TOP preparation:  It will create application top driver file at $COMMON_TOP/clone/appl/driver/appl.drv-Copy JDBC libraries and $COMMON_TOP/clone/jlib/classes111.zip

what Perl adcfgclone.pl dbTechStack do?

Perl adcfgclone.pl dbTechStack will do below things.

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Start SQL*NET listener

what Perl adcfgclone.pl dbTier do?

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Recreate controlfile

6)Configure database

7)Start SQL*NET listener

==

cd $ORACLE_HOME/appsutils/clone/bin

perl adcfgclone.pl dbTier pwd=apps

This will use the templates and driver files those were created while running adpreclone.pl on source system and has been copied to target system.

Following scripts are run by adcfgclone.pl dbTier for configuring techstack

adchkutl.sh — This will check the system for ld, ar, cc, and make versions.

adclonectx.pl — This will clone the context file. This will ceate a new context file as per the details of this instance.

runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv

Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME

For data on database side, following scripts are runDriver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv

Create database adcrdb.zipAutoconfig is runControl file creation adcrdbclone.sql

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

Run adcfgclone.pl for dbTier.

what Perl adcfgclone.pl appsTier do?

perl adcfgclone.pl appsTier will do below things.

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Create INST_TOP

6)Configure APPL_TOP

7)Start Apps Processses

==

On Application Side

cd $COMMON_TOP/clone/bin/

perl adcfgclone.pl appsTier pwd=apps

Following scripts are run by adcfgclone.pl:

Creates context file for target adclonectx.pl

Run driver files $ORACLE_HOME/appsutil/driver/instconf.drv and $IAS_ORACLE_HOME/appsutil/driver/instconf.drv

Relinking of Oracle Home $ORACLE_HOME/bin/adlnk806.sh and $IAS_ORACLE_HOME/bin/adlnkiAS.sh

At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.

When we run adcfgclone.pl which script it will call?

It will call adclone.pl which is located at $AD_TOP/bin .

When we run perl adpreclone.pl dbTier why it requires apps password?

It requires a database connection to validate apps schema.

When do you run adpreclone on Production?

If any changes made to either TechStack,database or any patches applied.

How do we find adpreclone is run in source or not ?

 If clone directory exists under $RDBMS_ORACLE_HOME/appsutil for oracle user and $COMMON_TOP/clone for applmgr user.

When we run perl adpreclone.pl appTier why it will not prompt for apps password?

It doesn’t require db a connection.

adcfgclone on database node we had three modes

perl adcfgclone.pl dbTier

It will configure the ORACLE_HOME on the target database tier node and  recreate the controlfiles.

This is specially used in case of standby database and/or hot backups. It will take care of all the steps.

perl adcfgclone.pl dbTechStack

It will configure the ORACLE_HOME on the target database tier node only. Relink the oracle home.

perl adcfgclone.pl dbconfig

It is used to configure the database with  context file. Database should be in open mode.

adcfgclone.pl appsTier dualfs

DUALFS – new feature is introduced in the latest AD-TXK Delta 7.

This feature will create both the filesystems fs1 and fs2 during the cloning process.

Steps to run SQL Tuning Advisor from Database.

 Dear Folks,

As we already know, we can run tuning advisor again SQL_ID in OEM and implement through it. But some times, we can't implement recommendations.

We have gone through same phase in OEM where we couldn't implement SQL profile through OEM. Hence we have decided to run from DB.

Here are the steps

19c

===

ONPROD [****@servername ~]$ export ORACLE_PDB_SID=OLBUI

NONPROD [****@servername ~]$ sqlplus / as sysdba

SQL> set long 1000000000

Col recommendations for a200

SQL> SQL> DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '2jy3n3dtmntxm',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '2jy3n3dtmntxm_tuning_task_1',

 2  3  4  5  6  7  8  9 description => 'Tuning task for statement 2jy3n3dtmntxm');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

10  11  12 DECLARE

*

ERROR at line 1:

ORA-13780: SQL statement does not exist.

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 257

ORA-06512: at "SYS.DBMS_SQLTUNE", line 771

ORA-06512: at line 4

>>If you receive above error.Use AWR snap IDS between SQL_ID Runs.

SQL> SELECT SNAP_ID FROM DBA_HIST_SQLSTAT

WHERE SQL_ID='2jy3n3dtmntxm'

ORDER BY SNAP_ID; 2  3

  SNAP_ID

----------

   3140

   3141

   3142

   3143

   3144

   3145

   3146

   3147

8 rows selected.

SQL> declare

l_sql_tune_task_id varchar2(100);

 2  3 begin

 4  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

 begin_snap => 3140,

 end_snap => 3147,

 sql_id => '2jy3n3dtmntxm',

 scope => dbms_sqltune.scope_comprehensive,

 time_limit => 10800,

 task_name => '2jy3n3dtmntxm_tuning_task',

 description => 'tuning_in_OLBUI');

dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

end;

/ 5  6  7  8  9  10  11  12  13  14

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2jy3n3dtmntxm_tuning_task');

PL/SQL procedure successfully completed.

Once above done. Please execute following SQL query to get recommendations.

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('2jy3n3dtmntxm_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24

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

FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 82.5%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

    '2jy3n3dtmntxm_tuning_task', task_owner => 'SYS', replace =>

    TRUE);

Executed same in PROD and performance was improved.

SQL> show user

USER is "SYS"

SQL> exec dbms_sqltune.accept_sql_profile(task_name =>'2jy3n3dtmntxm_tuning_task', task_owner => 'SYS', replace =>TRUE);

PL/SQL procedure successfully completed.

After implemented above recommendation, Performance was improved for SQL. 

Thanks.

adcfgclone dbTechStack failing with ouicli.pl INSTE8_APPLY 1

 Dear Folks,

Recently, we have came across issue while configuring database ( 12.1.0.2) and failing with below error.

  [APPLY PHASE]

  AutoConfig could not successfully execute the following scripts:

 Directory: /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/perl/bin/perl -I /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/perl/lib/5.14.1 -I /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/perl/lib/site_perl/5.14.1 -I /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/appsutil/perl /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/appsutil/clone

 ouicli.pl               INSTE8_APPLY       1

AutoConfig is exiting with status 1

WARNING: RC-50013: Fatal: Instantiate driver did not complete successfully.

/u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/appsutil/driver/regclone.drv

1)

Checked if jre folder doesn't exist under $ORACLE_HOME/appsutil  and found it was there.

/u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/appsutil/jre/bin/java

Able to get java -version output 

java version "1.7.0_45"

OpenJDK Runtime Environment (rhel-2.4.3.3.el6-x86_64 u45-b15)

OpenJDK 64-Bit Server VM (build 24.45-b08, mixed mode)

So this case doesn't suit for this issue 

2) Exported below env varaible and ran adcfg clone. But we got same issue.

export PATH=$ORACLE_HOME/perl/bin:$PATH

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

Finally, have found DB home already registered in inventory.xml file.

Hence, we have moved file and ran adcfgclone and completed successfully.

Thanks.

Oacore Services Showing Error While Starting

 Dear Folks,

We faced issue recently that OACORE services were started as ADMIN than in RUNNING mode.

Error in log file.

Failed to load webapp: /OA_HTML because of DeploymentException: java.lang.NullPointerException

at weblogic.servlet.internal.WebAppModule.prepare(WebAppModule.java:397)

Fix

==

$FND_TOP/bin/txkrun.pl -script=ChkEBSDependecies -server=ALL_SERVERS

Compile JSP

cd $FND_TOP/patch/115/bin

ojspCompile.pl --compile --flush

stop and start oacore

admanagedsrvctl.sh stop oacore_server1

admanagedsrvctl.sh start oacore_server1

admanagedsrvctl.sh stop oacore_server3

admanagedsrvctl.sh start oacore_server3

Thanks.

Deduction report performance in R12 instance.

 Dear Folks,

One of our customer had an experience with program "Deduction Report" which taking long time to complete. Investigated AWR report during the time period and didn't have anything to drill down. Hence we lodged an SR with oracle and they give simple SQL which simply change VIEW on problematic table.

But before execute this SQL in prod. Make sure it is tested in non-prod instance.

Fix : 

===

OWNER      OBJECT_NAME     CREATED     OBJECT_TYPE     LAST_DDL_TIME  STATUS

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

APPS      PAY_US_DEDUCTIONS_RE 25-NOV-08    VIEW         15-AUG-20    VALID

        PORT_RBR_V

SQL> select name from v$database;

NAME

---------

KTMI

SQL> @PAY_US_DEDUCTIONS_REPORT_RBR_V1.sql

View created.

SQL>

OWNER      OBJECT_NAME          CREATED     OBJECT_TYPE     LAST_DDL_TIME  STATUS

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

APPS      PAY_US_DEDUCTIONS_REPORT_RBR_V 25-NOV-08    VIEW         03-DEC-20    VALID

SQL : PAY_US_DEDUCTIONS_REPORT_RBR_V1.sql

====

CREATE OR REPLACE FORCE VIEW "APPS"."PAY_US_DEDUCTIONS_REPORT_RBR_V" ("CONSOLIDATION_SET_ID", "CONSOLIDATION_SET_NAME", "PAYROLL_ID", "PAYROLL_NAME", "PAYROLL_EFFECTIVE_START_DATE", "PAYROLL_EFFECTIVE_END_DATE", "PAYROLL_ACTION_ID", "PAYROLL_ACTION_EFFECTIVE_DATE", "PAYROLL_ACTION_DATE_EARNED", "TIME_PERIOD_ID", "PERIOD_NAME", "PERIOD_NUM", "PERIOD_TYPE", "PERIOD_START_DATE", "PERIOD_END_DATE", "ASSIGNMENT_ACTION_ID", "ASSIGNMENT_ID", "TAX_UNIT_ID", "GRE", "PERSON_ID", "EMPLOYEE_NUMBER", "FULL_NAME", "ORGANIZATION_ID", "ORGANIZATION_NAME", "LOCATION_ID", "LOCATION_CODE", "LOCATION_DESCRIPTION", "ASSIGNMENT_SEQUENCE", "ASSIGNMENT_NUMBER", "ELEMENT_TYPE_ID", "ELEMENT_NAME", "ELEMENT_DESCRIPTION", "BUSINESS_GROUP_ID", "PRIMARY_BALANCE_ID", "HOURS_BALANCE_ID", "CLASSIFICATION_ID", "CLASSIFICATION_NAME", "PRIMARY_BALANCE", "NOT_TAKEN_BALANCE", "ARREARS_BALANCE", "ACCRUED_BALANCE", "TOTAL_OWED") AS 

  SELECT /*+ ordered no_concat index(paa PAY_ASSIGNMENT_ACTIONS_N50) use_nl(ppa pap) 

           use_nl(ppa pcs) use_nl(ppa ptp) use_nl(ppa paa paas papp) use_nl(paa prb)

           use_nl(paa hou1) use_nl(paas hou) use_nl(paas hrl) use_hash(pet)

           use_hash(pba) use_nl(pdb) use_nl(pbad) */  

        ppa.consolidation_set_id consolidation_set_id

       ,pcs.consolidation_set_name consolidation_set_name

       ,ppa.payroll_id payroll_id

       ,pap.payroll_name payroll_name

       ,pap.effective_start_date payroll_effective_start_date

       ,pap.effective_end_date payroll_effective_end_date

       ,ppa.payroll_action_id payroll_action_id

       ,ppa.effective_date payroll_action_effective_date

       ,ppa.date_earned payroll_action_date_earned

       ,ptp.time_period_id time_period_id

       ,ptp.period_name period_name

       ,ptp.period_num period_num

       ,ptp.period_type period_type

       ,ptp.start_date period_start_date

       ,ptp.end_date period_end_date

       ,paa.assignment_action_id assignment_action_id

       ,paa.assignment_id assignment_id

       ,paa.tax_unit_id tax_unit_id

       ,hou1.name gre

       ,paas.person_id person_id

       ,papp.employee_number employee_number

       ,papp.full_name full_name

       ,paas.organization_id organization_id

       ,hou.name organization_name

       ,paas.location_id location_id

       ,hrl.location_code location_code

       ,hrl.description location_description

       ,paas.assignment_sequence assignment_sequence

       ,paas.assignment_number assignment_number

       ,pet.element_type_id element_type_id

       ,pet.element_name element_name

       ,pet.description element_description

       ,pba.business_group_id business_group_id

       ,pet.element_information10 primary_balance_id

       ,pet.element_information11 hours_balance_id

       ,pec.classification_id classification_id

       ,pec.classification_name classification_name

       ,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)

                                                   ,'ASG_GRE_RUN'

                                                   ,paa.assignment_action_id

                                                   ,paa.assignment_id

                                                   ,NULL

                                                   ,paa.tax_unit_id

                                                   ,pba.business_group_id

                                                   ,NULL) primary_balance

       ,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)

                                                    || ' NOT TAKEN'

                                                   ,'ASG_GRE_RUN'

                                                   ,paa.assignment_action_id

                                                   ,paa.assignment_id

                                                   ,NULL

                                                   ,paa.tax_unit_id

                                                   ,pba.business_group_id

                                                   ,NULL) not_taken_balance

       ,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)

                                                    || ' ARREARS'

                                                   ,'ASG_GRE_RUN'

                                                   ,paa.assignment_action_id

                                                   ,paa.assignment_id

                                                   ,NULL

                                                   ,paa.tax_unit_id

                                                   ,pba.business_group_id

                                                   ,NULL) arrears_balance

       ,decode (sign (to_number (decode (pet.element_information11

                                        ,NULL

                                        ,NULL

                                        ,pay_us_dedn_pkg.pay_us_tot_owed (paa.assignment_id

                                                                         ,pet.element_type_id

                                                                         ,ppa.effective_date

                                                                         ,ppa.date_earned))))

               ,NULL

               ,NULL

               ,- 1

               ,NULL

               ,0

               ,NULL

               ,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)

                                                            || ' ACCRUED'

                                                           ,'ASG_GRE_ITD'

                                                           ,paa.assignment_action_id

                                                           ,paa.assignment_id

                                                           ,NULL

                                                           ,paa.tax_unit_id

                                                           ,pba.business_group_id

                                                           ,NULL

                                                           ,pec.classification_name

                                                           ,'ENTRY_ITD'

                                                           ,NULL

                                                           ,pet.element_type_id)) accrued_balance

       ,to_number (decode (pet.element_information11

                          ,NULL

                          ,NULL

                          ,pay_us_dedn_pkg.pay_us_tot_owed (paa.assignment_id

                                                           ,pet.element_type_id

                                                           ,ppa.effective_date

                                                           ,ppa.date_earned))) total_owed

FROM    pay_payroll_actions ppa

       ,pay_consolidation_sets pcs

       ,pay_all_payrolls_f pap

       ,per_time_periods ptp

       ,pay_assignment_actions paa

       ,hr_all_organization_units hou1

       ,per_all_assignments_f paas

       ,hr_organization_units hou

       ,hr_locations_all hrl

       ,per_all_people_f papp

       ,pay_run_balances prb

       ,pay_balance_attributes pba

       ,pay_bal_attribute_definitions pbad

       ,pay_defined_balances pdb

       ,pay_element_types_f pet

       ,pay_element_classifications pec

WHERE   ppa.action_type IN ('Q','R','V'

                           ,'B','I')

AND     ppa.action_status = 'C'

AND     ppa.consolidation_set_id = pcs.consolidation_set_id

AND     ppa.payroll_id IS NOT NULL

AND     ppa.payroll_id = pap.payroll_id

AND     nvl (ppa.date_earned

            ,ppa.effective_date) BETWEEN pap.effective_start_date

                                                      AND     pap.effective_end_date

AND     ppa.time_period_id = ptp.time_period_id

AND     paa.payroll_action_id = ppa.payroll_action_id

AND     paa.action_status = 'C'

AND     paa.tax_unit_id = hou1.organization_id

AND     paas.assignment_id = paa.assignment_id

AND     nvl (ppa.date_earned

            ,ppa.effective_date) BETWEEN paas.effective_start_date

                                                      AND     paas.effective_end_date

AND     paas.organization_id = hou.organization_id

AND     paas.location_id = hrl.location_id

AND     pbad.attribute_name IN ('PAY_US_PRE_TAX_DEDUCTIONS','PAY_US_AFTER_TAX_DEDUCTIONS','PAY_US_EMPLOYER_LIABILITY')

AND     pbad.legislation_code = 'US'

AND     pba.attribute_id = pbad.attribute_id

AND     prb.assignment_action_id = paa.assignment_action_id

AND     prb.defined_balance_id = pba.defined_balance_id

AND     pba.defined_balance_id = pdb.defined_balance_id

AND     pba.business_group_id = pet.business_group_id

AND     (

                nvl (translate (pet.element_information10

                               ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

                               ,'01234567890000000000000000000000000000000000000000000000000000')

                    ,'0') = pdb.balance_type_id

        OR      nvl (translate (pet.element_information11

                               ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

                               ,'01234567890000000000000000000000000000000000000000000000000000')

                    ,'0') = pdb.balance_type_id

        OR      nvl (translate (pet.element_information12

                               ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

                               ,'01234567890000000000000000000000000000000000000000000000000000')

                    ,'0') = pdb.balance_type_id

        OR      nvl (translate (pet.element_information13

                               ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

                               ,'01234567890000000000000000000000000000000000000000000000000000')

                    ,'0') = pdb.balance_type_id

        )

AND     nvl (ppa.date_earned

            ,ppa.effective_date) BETWEEN pet.effective_start_date

                                                      AND     pet.effective_end_date

AND     pec.legislation_code = 'US'

AND     pec.classification_name IN ('Pre-Tax Deductions','Involuntary Deductions','Voluntary Deductions'

                                   ,'Employer Liabilities')

AND     pet.classification_id = pec.classification_id

AND     paas.person_id = papp.person_id

AND     nvl (ppa.date_earned

            ,ppa.effective_date) BETWEEN papp.effective_start_date

                                                      AND     papp.effective_end_date

GROUP BY ppa.consolidation_set_id

        ,pcs.consolidation_set_name

        ,ppa.payroll_id

        ,pap.payroll_name

        ,pap.effective_start_date

        ,pap.effective_end_date

        ,ppa.payroll_action_id

        ,ppa.effective_date

        ,ppa.date_earned

        ,ptp.time_period_id

        ,ptp.period_name

        ,ptp.period_num

        ,ptp.period_type

        ,ptp.start_date

        ,ptp.end_date

        ,paa.assignment_action_id

        ,paa.assignment_id

        ,paa.tax_unit_id

        ,hou1.name

        ,paas.person_id

        ,papp.employee_number

        ,papp.full_name

        ,paas.organization_id

        ,hou.name

        ,paas.location_id

        ,hrl.location_code

        ,hrl.description

        ,paas.assignment_sequence

        ,paas.assignment_number

        ,pet.element_type_id

        ,pet.element_name

        ,pet.description

        ,pba.business_group_id

        ,pet.element_information10

        ,pet.element_information11

        ,pec.classification_id

        ,pec.classification_name;

Thanks.

ORA-20001: APP_FND_01972 Error in FND_USER_REPS_GROUPS_API

 Dear Folks,

One of our customer had an experience with following error when he was trying to add/remove end date on a responsibility to user.

Oracle error-20001: ORA-20001: APP_FND_01972 Errron in FND_USER_REPS_GROUPS_API. Update_Assignment:

FIX :- 

Oracle error-20001:APP-FND-01972: When Trying To End Date A Responsibility Assigned To A User (Doc ID 1987250.1)

1. Run the program "Workflow Directory Services User/Role Validation" once with the following parameters:

Fix Dangling User/Roles

Default Batchsize=10000

Fix Dangling User/Roles=Yes

Add Missing User/Role Assignments=No

2. After it completes, run program "Workflow Directory Services User/Role Validation" again with the following parameter:

Add Missing User/Role Assignments

Default Batchsize=10000

Fix Dangling User/Roles=No

Add Missing User/Role Assignments=Yes

TNS-12547: TNS:lost contact

Dear Team, 

Recently we have experienced connectivity issue between application node to DB node on database port which set to 1526.

Though listener is up and running on db node, we couldn't able to connect to apps and says "TNS lost Contact". 

Tried to ping from application node and eventually failed and telnet connection immediately closed.

NONPROD [******* ~]$ telnet kialampnldb01.******.**** 1526

Trying 10.174.129.6...

Connected to kialampnldb01.******.****.

Escape character is '^]'.

Connection closed by foreign host.

NONPROD [******* ~]$ tnsping KAMPK

TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 06-NOV-2020 21:26:48

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:

/u01/app/*******/KAMPK/fs1/inst/apps/KAMPK_kialampnlap01/ora/10.1.2/network/admin/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=kialampnldb01.******.****)(PORT=1526)) (CONNECT_DATA= (SERVICE_NAME=KAMPK) (INSTANCE_NAME=KAMPK)))

TNS-12547: TNS:lost contact

NONPROD [******* ~]$

===========

Hence, its clearly know, we might have unwanted entries in sqlnet.ora file. Hence we checked on DB node and found below two entries.

tcp.validnode_checking = yes

tcp.invited_nodes=(kialampnldb01.******.****)

We commeneted above in sqlnet.ora and restarted listener then application was able to connect to DB and able to telnet.

To know about tcp parameters in sqlnet.oracle. kindly see below oracle document.

https://docs.oracle.com/en/database/oracle/oracle-database/18/netrf/parameters-for-the-sqlnet-ora-file.html#GUID-5C3AB641-7541-4CE9-BC9E-BA5DD30616A8

5.2.75 TCP.INVITED_NODES