Databases on one test server cannot be accessed, and port 1521 on the server can be remotely accessed, but the database cannot be connected through PL/SQL. Log on to the database server and run $ LSNRCTL statuslistening endpoints summary...
(Description = (address = (Protocol = TCP) (host = 192.168.155.100) (Port = 1525 )))
(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... is not registered with the service. Check database service configuration: SQL> show parameter service_namesservice_names string dg1.oracle.com, HR, oa SQL> show parameter parameters string dgsql> show parameter domian_namedb_domain string oracle.com check listener. orasid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = plsextproc)
(ORACLE_HOME =/u01/APP/Oracle/product/10.2.0/db_1)
(Program = EXTPROC)
)
) Listener =
(Description_list =
(Description =
(Address = (Protocol = TCP) (host = 192.168.155.100) (Port = 1521 ))
)
(Description =
(Address = (Protocol = IPC) (Key = extproc0 ))
)
) Check tnsnames. oraoa =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.155.100) (Port = 1521 ))
)
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = oa.oracle.com)
)
) HR =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.155.100) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = hr.oracle.com)
)
) DG =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.155.100) (Port = 1521 ))
)
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = dg1.oracle.com)
)
) Also did not find the problem, but through the TNS login: sqlplus user/[email protected] error: TNS-12514: TNS: Listener does not currently know of Service requested in connect descriptor manual registration service: SQL> the alter System Register Service still cannot register with listener. Then I suddenly thought of the local_listner parameter. SQL> show parameter local_listener (address = (Protocol = TCP) (host = 192.168.129.201) (Port = 1521), while listener actually uses the IP address 192.168.155.100. We found that this machine has two NICs with IP addresses 192.168.155.100 and 192.168.129.201. Some maintenance personnel wanted to bind the listener to the IP address 192.168.129.201, but the method was incorrect. Modify the local_listener parameter. SQL> alter system set local_listener = ''; then re-register the service. SQL> alter system register; check registration information, $ LSNRCTL statusservice "plsextproc" has 1 instance (s ).
Instance "plsextproc", status unknown, has 1 handler (s) for this service...
Service "dg1.oracle.com" has 1 instance (s ).
Instance "DG", status ready, has 4 handler (s) for this service...
Service "dgw.xpt.oracle.com" has 1 instance (s ).
Instance "DG", status ready, has 4 handler (s) for this service...
Service "hr.oracle.com" has 1 instance (s ).
Instance "DG", status ready, has 4 handler (s) for this service...
Service "oa.oracle.com" has 1 instance (s ).
The services defined in instance "DG", status ready, has 4 handler (s) for this service... service_names are all dynamically registered to listener. The remote PL/SQL connection to the database is successful. -- This article from: http://blog.sina.com.cn/s/blog_8345cdd90100v6wx.html
ORA-12514: TNS: Listener does not currently know of Service requested in connect Descriptor