We're here to help!

Create Standby database fails with ORA-12547 TNS:lost contact

Follow

Read our docs This article explains how to investigate an ORA-12547 error when trying to create a standby database. All services are up, the Primary database is running and it's possible to connect remotely.

Problem Statement

Create Standby database fails with ORA-12547 TNS:lost contact

Applicable Error Code

N/A

Affected Versions

v9

Affected Platforms

All

Description

The problem occurs during the prerequisites of Creating the Standby database (CLI) when it gives the error ORA-12547. All services are active, the Primary database is running and it is possible to connect remotely. 

Steps Performed

1.  We see these errors in the CSD tracefile on Primary

<<<< Dbvisit Standby terminated >>>> 
PID:29421
TRACEFILE:29421_dbvctl_csd_bdtecsup_202002261101.trc
SERVER:odabdtecsup
ERROR_CODE:2001
ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach)

 2. The Primary database alert.log confirms the TNS error

 VERSION INFORMATION:
 TNS for Linux: Version 18.0.0.0.0 - Production
 Oracle Bequeath NT Protocol Adapter for Linux: Version 18.0.0.0.0 - Production
 TCP/IP NT Protocol Adapter for Linux: Version 18.0.0.0.0 - Production
  Version 18.5.0.0.0
  Time: 26-FEB-2020 19:01:25
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
 
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
 
TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)PORT=xxx))

3.  Run the following commands on the Primary host as the Oracle user

 export ORACLE_HOME=/u01/app/18.0.0.0/grid
export ORACLE_SID=+ASM1
export LD_LIBRARY_PATH=/backup/dbvisit/standby/lib:/u01/app/18.0.0.0/grid/lib
export PATH=/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/usr/sbin:/sbin:/u01/app/18.0.0.0/grid/bin

echo "exit;" | sqlplus "/as sysdba"
echo "exit;" | sqlplus "/as sysasm"

 4.  It looks like some configuration problem in role separation of the Oracle and Grid users on the Primary host because as sysdba connects fine, but as sysasm not.

 [oracle@+ASM1 trace]$export ORACLE_HOME=/u01/app/grid/product/18.3.0.0/grid/
[oracle@+ASM1 trace]$echo "exit;" | sqlplus "/as sysasm"
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Feb 28 14:35:16 2020
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.
ERROR:
ORA-12547: TNS:lost contact

Enter user-name: Enter password:
ERROR:
ORA-12547: TNS:lost contact
Enter user-name: [oracle@+ASM1 trace]$

 5. When the ORACLE_HOME is set with "/" at the end of the variable, these symptoms occur. In the tracefile, the GRID_HOME is read as follow:

20200226 11:01:18 main::asm_is_up: start Asm_is_up=
20200226 11:01:18 main::cmn_set_oraenv: oracle_home=/u01/app/18.0.0.0/grid/
20200226 11:01:18 main::cmn_set_oraenv: oracle_sid =+ASM1
20200226 11:01:18 main::UTIL_UNIX_get_oracle_bintools: oracle_home=/u01/app/18.0.0.0/grid/
20200226 11:01:18 main::cmn_set_oraenv: ENV{ORACLE_HOME}    =/u01/app/18.0.0.0/grid/
20200226 11:01:18 main::cmn_set_oraenv: ENV{ORACLE_SID}     =+ASM1
20200226 11:01:18 main::cmn_set_oraenv: _LD_LIBRARY_PATH    =/backup/dbvisit/standby/lib
...
...
20200226 11:01:18 main::ora_db_connect: start instance=asm
20200226 11:01:19 main::ora_db_connect: connection failed:ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach)
20200226 11:01:19 main::ora_exec_sql: end: error_code=12547 error=ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach)
20200226 11:01:19 main::cmn_ora_check: START: ora_error=ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach)

 6. The setting of ORACLE_HOME for ASM is registered incorrectly somewhere. Output of oratab shows:

cat /etc/oratab

#
+ASM1:/u01/app/18.0.0.0/grid/

7.  Changed the entry to the following, without the / at the end :

#
+ASM1:/u01/app/18.0.0.0/grid

     8. The creation of the Standby Database runs smoothly. 

 

 

 

Have more questions? Submit a request

Comments