Database Upgrade : Database connect failed with: ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach)

Today, we we were doing database upgrade from 11.2.0.4 to 19c. 19c binaries installation was clean, while upgrade 107 phases was clean to until we got error during datapatch. Let me provide the error reported – Database connect failed with: ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach)

[2020-07-11 15:54:27]   orchestration_summary:
/oracle/oraclebase/cfgtoollogs/sqlpatch/sqlpatch_23658776_2020_07_11_15_54_27/sqlpatch_summary.json
[2020-07-11 15:54:27]   orchestration_progress:
/oracle/oraclebase/cfgtoollogs/sqlpatch/sqlpatch_23658776_2020_07_11_15_54_27/sqlpatch_progress.json
[2020-07-11 15:54:27]
[2020-07-11 15:54:27] Connecting to database...[2020-07-11 15:54:27]
[2020-07-11 15:54:27] Error: prereq checks failed!
[2020-07-11 15:54:27] Database connect failed with: ORA-12547: TNS:lost
contact (DBD ERROR: OCIServerAttach)
[2020-07-11 15:54:27]

ISSUE

Error: prereq checks failed! is known issue when oracle binaries are unable to find the central Inventory. And you need to re-built the central inventory to resolve the issue during the upgrade. But here this was not the case. It gave us error “Database connect failed with: ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach)”

I thought something is wrong with database. So I checked the alert log for same. But there was no error / issue reported.

My Next step was to check if opatch lsinventory is working for me. And are we getting any error while executing lsinventory. Here it worked for me. But there was something weird with output. Let me show you. look at “/oracle/oracle/Orahome1//oraInst.loc”

./opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/oracle/Orahome1
Central Inventory : /oracle/oraInventory
    from           : /oracle/oracle/Orahome1//oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location :
/oracle/oracle/Orahome1/cfgtoollogs/opatch/opatch2020-07-11_15-36-21PM_1.log

Cause and Solution

Initially, we didn’t worried about it. And we kept on checking the connection and sqlnet.ora parameters. But later we came to know that’s the only issue. .profile had a wrong entry. Let me share you output of my system .profile. There was one mistake in below entry and that was additional “/” after Orahome1 while exporting ORACLE_HOME.

cat .profile
export ORACLE_HOME=/oracle/oracle/Orahome1/
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=FINUAT

Due to which datapatch failed to connect to database. Now we removed the additional “/” and we from .profile and executed the profile and tried to connect to database. And now we were getting – “TNS : LOST CONTACT”

Updated Profile entry –

cat .profile
export ORACLE_HOME=/oracle/oracle/Orahome1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=FINUAT

This is how we executed Profile –

cd ~
ls -ltra .profile
. ./.profile

Now we weren’t able to connect to database

sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 12 01:02:42 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-12547: TNS:lost contact

Here is the catch, now you have to shut the database with setting ORACLE_HOME as old path Then again start the database with proper entries.

export ORACLE_HOME=/oracle/oracle/Orahome1/
sqlplus / as sysdba
shut immediate;
exit
export ORACLE_HOME=/oracle/oracle/Orahome1
sqlplus / as sysdba
startup
exit

Now everything is proper. Lets run the failed datapatch command

cd $ORACLE_HOME/OPatch

./datapatch -verbose

Now it will be solved. And datapatch will be successful.

Reference –> support.oracle.com

19.x: datapatch failed with " ORA-12547: TNS:lost contact (DBD ERROR:
OCIServerAttach) " (Doc ID 2650624.1)