a set of DG built by my friends, after a long time, it has been reported as the following error:

ORA-12514: TNS:listener does not currentlyknow of service requested in connect descriptor

PING[ARC2]: Heartbeat failed to connect tostandby ‘PD’. Error is 12514.

The most common reason for the

error is static registration, and then DG parameter.

but the parameter here, which I’ve looked at for a long time, is not a problem:

SQL> show parameter dest_2

VALUE

the NAME, TYPE,

———————————————– ——————————

db_create_online_log_dest_2 string

log_archive_dest_2 string SERVICE = PD VALID_FOR = (ONLINE_L

OGFILE PRIMARY_ROLE) DB_UNIQUE

_NAME = PD

During

, I also asked my friend to do a lot of tests, including the reconstruction of password files, thought over the possible problems of DG, but there were still problems.

view related processes: there are no RFS processes in the secondary library, and no LNS processes in the primary library.

SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;

the PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

——— ———— ——————– ———- ———-

the ARCH CONNECTED 0 0 0 0

the ARCH CONNECTED 0 0 0 0

the ARCH CONNECTED 0 0 0 0

the ARCH CONNECTED 0 0 0 0

later, I asked my friends to take a look at the tnsping PD again. In fact, I had already asked my friends to take this test at the very beginning.

tnsping is normal, that is, the test network is passable and the listener is normal. This is also something our tnsping can do, but tnsping can’t check whether the service_name or SID in the TNsnames.ora file is correct, so we have friends post both files as well.

connected to this configuration with sqlplus, which also worked.

problem looks very strange, because DG itself has few parameters, I think about all the questions I can think of, but it doesn’t work, then I see the test result written by my friend:

[oracle @ DB11g_ST trace] $tnsping PD

TNS Ping Utility for Linux: Version 11.2.0.1.0-production on 31-jul-2013 16:11:18

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

informs the parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (TCP) PROTOCOL = (HOST = DB11g) (1521) PORT =) (CONNECT_DATA = (= DEDICATED SERVER) (SERVICE_NAME = former)))

OK (10 msec)

is interested in the HOST here, I suspected the problem of firewall before, but the firewall was closed, so I asked my friend to change this to IP address, and it was OK.

SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;

the PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

——— ———— ——————– ———- ———-

the ARCH CONNECTED 0 0 0 0

the ARCH CLOSING 1, 156, 2049, 1600

the ARCH CONNECTED 0 0 0 0

the ARCH CONNECTED 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 1 157 27 1

this time, the RFS process also appears.

The /etc/hosts file of the

friend host library is copied directly from the past. As follows:

/root @ DB11g_ST ~ # more/etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 DB11g localhost. Localdomain localhost

192.168.0.89 DB11g_ST

192.168.0.88 DB11g

problem is here, DB11g here has a loopback address: 127.0.0.1. Later, my friend commented this out and used the alias test, which worked fine.

is a big part of this problem, where we’ve been thinking about databases and what configurations of databases might cause this problem. Until the root cause of the problem is finally found, which is caused by the configuration of /etc/hosts.

, another friend told me another thing yesterday, they have a materialized view, they can’t refresh, the table is only 2G, it’s not too big. But it just doesn’t refresh. Later is also a long time to study, and finally locate the problem is the firewall, there is a ban on the transmission of large packets, the two sides of the firewall to remove this rule.

due to the process of this fault, we need to reflect that when we deal with the fault, do not always limited to the database level, maybe other configuration, will also cause this problem. In the process of troubleshooting, think of expanding our thinking, not in their own lane thinking rao too long.