Case of configuring static listening to resolve ORA-12514 errors

Case of configuring static listening to resolve ORA-12514 errors
Today, when I configured Linux for DG, I encountered a problem: tnsname. ora file configuration is normal, tnsping is normal, listening is normal, but still reports ORA-12514 error: SQL> set lin 130 pages 130 SQL> select dest_id, error from v $ archive_dest; DEST_ID ERROR ---------- Listen 1 2 ORA-12514: TNS: listener does not currently know of service requested in connect descriptor 3 4 5 6 7 8 9 10 -- view master database listening SQL>! Lsnrctl status LSNRCTL for Linux: Version 10.2.0.5.0-Production on 08-OCT-2014 12:31:46 Copyright (c) 1991,201 0, Oracle. all rights reserved. connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = prd) (PORT = 1521) STATUS of the LISTENER ------------------------ Alias LISTENERVersion TNSLSNR for Linux: version 10.2.0.5.0-ProductionStart Date 08-OCT-2014 10: 34: 51 Uptime 0 days 1 hr. 56 min. 54 sec Trace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File/u01/app/oracle/product/10.2.0/db_1/network/admin/listener. oraListener Log File/u01/app/oracle/product/10.2.0/db_1/network/log/listener. logListening Endpoints Summary... (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = prd) (PORT = 1521) (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC0) Services Summary... service "PLSExt Proc "has 1 instance (s ). instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service... service "prd" has 1 instance (s ). instance "prd", status READY, has 1 handler (s) for this service... service "prdXDB" has 1 instance (s ). instance "prd", status READY, has 1 handler (s) for this service... service "prd_XPT" has 1 instance (s ). instance "prd", status READY, has 1 handler (s) for this servic E... the command completed successfully -- tnsnames. ora file content # tnsnames. ora Network Configuration File:/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames. ora # Generated by Oracle configuration tools. EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO ))) PRD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = prd) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd ))) STD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = std) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd ))) -- The master database tnsping tests whether the network service name is normal SQL>! Tnsping prd TNS Ping Utility for Linux: Version 10.2.0.5.0-Production on 08-OCT-2014 12:32:35 Copyright (c) 1997,201 0, Oracle. all rights reserved. used parameter files: Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = prd) (PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd) OK (10 msec) SQL>! Tnsping std TNS Ping Utility for Linux: Version 10.2.0.5.0-Production on 08-OCT-2014 12:32:43 Copyright (c) 1997,201 0, Oracle. all rights reserved. used parameter files: Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = std) (PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd) OK (20 msec) -- View standby database listening [oracle @ std ~] $ Lsnrctl status LSNRCTL for Linux: Version 10.2.0.5.0-Production on 08-OCT-2014 12:29:52 Copyright (c) 1991,201 0, Oracle. all rights reserved. connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = std) (PORT = 1521) STATUS of the LISTENER ------------------------ Alias LISTENERVersion TNSLSNR for Linux: version 10.2.0.5.0-ProductionStart Date 08-OCT-2014 09: 41: 41 Uptime 0 days 2 hr. 48 min. 11 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File/u01/app/oracle/product/10.2.0/db_1/network/admin/listener. oraListener Log File/u01/app/oracle/product/10.2.0/db_1/network/log/listener. logListening Endpoints Summary... (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = std) (PORT = 1521) (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC0) Services Summary... service "PLSExtProc" has 1 instance (s ). instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service... service "std" has 1 instance (s ). instance "prd", status READY, has 1 handler (s) for this service... service "std_XPT" has 1 instance (s ). instance "prd", status READY, has 1 handler (s) for this service... the command completed successfully -- view The remote archiving path of The slave Database SQL> set lin 130 pages 130 SQL> col error for a20
SQL> select dest_id, error, status from v $ archive_dest; DEST_ID error status ---------- ------------------ --------- 1 VALID 2 VALID 3 INACTIVE 4 INACTIVE 5 INACTIVE 6 INACTIVE 7 INACTIVE 8 INACTIVE 9 INACTIVE 10 INACTIVE 11 VALID -- slave database tnsping test whether the network server name is normal SQL>! Tnsping prd TNS Ping Utility for Linux: Version 10.2.0.5.0-Production on 08-OCT-2014 12:39:40 Copyright (c) 1997,201 0, Oracle. all rights reserved. used parameter files: Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = prd) (PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd) OK (20 msec) SQL>! Tnsping std TNS Ping Utility for Linux: Version 10.2.0.5.0-Production on 08-OCT-2014 12:39:44 Copyright (c) 1997,201 0, Oracle. all rights reserved. used parameter files: Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = std) (PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd) OK (10 msec) tnsping test is successful, which is strange. But the ORA-12514 error is obviously refers to the listening problem, resulting in service name cannot be resolved, because the master and slave databases use the same instance name, therefore, instance_name on both sides uses prd. At first glance, it seems to be normal, but the parsing does have a problem. What should I do?

Oracle Database listening is very slow, and hang basically handles faults

Dynamic and Static monitoring features of Oracle listeners

Non-default listeners and port configurations for placing an instance in the Oracle 11g RAC Environment

Configure and manage Oracle listener logs

Oracle error-ORA-12514: TNS: No listener

ORA-12514 listening error solved

Six connection problems and solutions for Oracle listeners

Oracle LISTENER does not listen to Oracle instances. Problem solved

Set the Oracle LISTENER password (LISTENER)

For more details, please continue to read the highlights on the next page:

1 2 Next Page