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
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.
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.
Comments