ORA-12170: TNS:Connect timeout occurred

You have long long-running replication. Probably, you have fetcher and MINE process is running on different server. The MINE process crashes regularly on error ORA-12170: TNS:Connect timeout occurred - last lines in a MINE log file. 

Problem Details

Problem Statement

Mine process crashed on ORA-12170

Applicable Error Code

2017/11/22 23:04:20 FATAL> FATAL-1: Could not connect as dbvrep to database BbDT8ioE2GpHH, error is ORA-12170: TNS:Connect timeout occurred (DBD ERROR: OCIServerAttach)

Affected Versions

Product versions which are affected

Affected Platforms

not applicable

Description

In a MINE logfile you can find this stack:

2017/11/22 23:03:51 INFO> OK-6902: Exec failed (attempt 1 of 3) on update DBRSCOMMON_HEARTBEAT set WALLCLOCK_DATE = SYSDATE, MINE_DATE = to_date(?,'MM/DD/YYYY HH24:MI:SS'), SOURCE_SCN = dbms_flashback.get_system_change_number, MINE_SCN = ? WHERE DDC_ID = ? AND MINE_PROCESS_NAME = ? : ORA-12571: TNS:packet writer failure (DBD ERROR: OCIStmtExecute)

2017/11/22 23:03:53 INFO> OK-6902: Exec failed (attempt 2 of 3) on update DBRSCOMMON_HEARTBEAT set WALLCLOCK_DATE = SYSDATE, MINE_DATE = to_date(?,'MM/DD/YYYY HH24:MI:SS'), SOURCE_SCN = dbms_flashback.get_system_change_number, MINE_SCN = ? WHERE DDC_ID = ? AND MINE_PROCESS_NAME = ? : ?

2017/11/22 23:03:54 INFO> OK-6902: Exec failed (attempt 3 of 3) on update DBRSCOMMON_HEARTBEAT set WALLCLOCK_DATE = SYSDATE, MINE_DATE = to_date(?,'MM/DD/YYYY HH24:MI:SS'), SOURCE_SCN = dbms_flashback.get_system_change_number, MINE_SCN = ? WHERE DDC_ID = ? AND MINE_PROCESS_NAME = ? : ?

2017/11/22 23:03:54 INFO> Trying to create support package (base error: FATAL-3537: Execute failed on SQL ' update DBRSCOMMON_HEARTBEAT set WALLCLOCK_DATE = SYSDATE, MINE_DATE = to_date(?,'MM/DD/YYYY HH24:MI:SS'), SOURCE_SCN = dbms_flashback.get_system_change_number, MINE_SCN = ? WHERE DDC_ID = ? AND MINE_PROCESS_NAME = ? ' with error 
) 
2017/11/22 23:03:54 INFO> OK-6901: Prepare failed (attempt 1 of 3) on select SEQUENCE, THREAD, RESETLOGS_ID, FIRST_SCN, NEXT_SCN, coalesce(ARCH_NAME,'_'), coalesce(ONLINE_NAME,'_'), READ_COUNT, coalesce(to_char(LAST_MINE_START,'YYYYMMDDHH24MISS'),'00000000000000'), coalesce(to_char(LAST_MINE_END,'YYYYMMDDHH24MISS'),'00000000000000') FROM DBRSMINE_REDO_LOG_HISTORY WHERE NEXT_SCN >= ? AND DDC_ID = ? AND MINE_PROCESS_NAME = ? ORDER BY 1 : ORA-12571: TNS:packet writer failure (DBD ERROR: OCIStmtExecute/Describe)

2017/11/22 23:03:54 INFO> Messages dumped to trace file. 
2017/11/22 23:03:56 INFO> Memory accounting dumped to trace file. 
2017/11/22 23:03:58 INFO> OK-6901: Prepare failed (attempt 2 of 3) on select SEQUENCE, THREAD, RESETLOGS_ID, FIRST_SCN, NEXT_SCN, coalesce(ARCH_NAME,'_'), coalesce(ONLINE_NAME,'_'), READ_COUNT, coalesce(to_char(LAST_MINE_START,'YYYYMMDDHH24MISS'),'00000000000000'), coalesce(to_char(LAST_MINE_END,'YYYYMMDDHH24MISS'),'00000000000000') FROM DBRSMINE_REDO_LOG_HISTORY WHERE NEXT_SCN >= ? AND DDC_ID = ? AND MINE_PROCESS_NAME = ? ORDER BY 1 :

2017/11/22 23:04:20 INFO> ------------------------------------------------------------ 
2017/11/22 23:04:20 INFO> Connect to database dbvrep_b7miko_sidbfrin@dbi:Oracle:SIDBFRINEMD failed with error ORA-12170: TNS:Connect timeout occurred. 
2017/11/22 23:04:20 INFO> Check database and listener are running. Check your TNS settings. Please check and/or update TNS_ADMIN in the DDC file to point to correct tnsnames.ora location. 
2017/11/22 23:04:20 INFO> _FORCE_LD_PATH_ALL="NO" 
2017/11/22 23:04:20 INFO> _FORCE_LD_PATH_SYSDBA="NO" 
2017/11/22 23:04:20 INFO> TNS_ADMIN="C:\app\oracle\product\11.2.0\dbhome_1\network\admin" 
2017/11/22 23:04:20 INFO> ORACLE_HOME="c:\windows\temp\par-53544543574f53574f30333024\cache-a375a518219553257d3686518eef3506eb8fa4ec" 
2017/11/22 23:04:20 INFO> NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252" 
2017/11/22 23:04:20 INFO> LD_LIBRARY_PATH="C:\Windows\TEMP\par-53544543574f53574f30333024\cache-a375a518219553257d3686518eef3506eb8fa4ec" 
2017/11/22 23:04:20 INFO> PATH="C:\Windows\TEMP\par-53544543574f53574f30333024\cache-a375a518219553257d3686518eef3506eb8fa4ec;C:\app\oracle\product\11.2.0\dbhome_1\bin;C:\Program Files\HP\NCU;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\app\oracle\product\41\ofs_1\FailSafe\Server;C:\Program Files (x86)\Common Files\Acronis\SnapAPI\;C:\Program Files (x86)\Acronis\CommandLineTool\;C:\Windows\System32\WindowsPowerShell\v1.0\;" 
2017/11/22 23:04:20 INFO> ------------------------------------------------------------ 
2017/11/22 23:04:20 FATAL> FATAL-1: Could not connect as dbvrep to database BbDT8ioE2GpHH, error is ORA-12170: TNS:Connect timeout occurred (DBD ERROR: OCIServerAttach)

Solution

As you can see, the MINE process tried 3 times with 1-second pause connect to a source database. You can modify this behavior by following variables:

By the default, the variable _PERLDB_RETRY_COUNT is set 3 and _PERLDB_RETRY_SLEEP is set to 1. You can adjust these variables appropriately.