An access troubleshooting-TNS-03505, ORA-12154, TNS-12560, dynamic registration, firewall, tnsping tracking, tns-03505tnsping
To access the database through Oracle Net, You need to configure the listener and tnsnames. ora. Next, you will encounter a series of problems...
1. Add listener Configuration. The default listener. ora file includes:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521 ))
)
)
ADR_BASE_LISTENER =/opt/app/ora11g
To create a listener name, add the following:
DCSOPEN =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = 172.101.19.57) (Port = 1521 )))
Or
DCSOPEN =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dcsopen2Node) (PORT = 1521 ))
)
)
ADR_BASE_DCSOPEN =/opt/app/ora11g
Or
DCSOPEN =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = 172.101.19.57) (Port = 1521 )))
SID_LIST_DCSOPEN =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/opt/app/ora11g/product/11.2.0/dcsopen)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dcsopen)
(ORACLE_HOME =/opt/app/ora11g/product/11.2.0/dcsopen)
(SID_NAME = dcsopen)
)
)
ADR_BASE_DCSOPEN =/opt/app/ora11g
Or
DCSOPEN =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.101.19.57) (PORT = 1521 ))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC ))
)
)
)
SID_LIST_DCSOPEN =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/opt/app/ora11g/product/11.2.0/dcsopen)
(PROGRAM = extproc)
)
)
ADR_BASE_DCSOPEN =/opt/app/ora11g
2. Add the native tnsnames. ora file Configuration:
Dcsopen =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.101.19.57) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dcsopen)
)
)
3. If tnsping dcsopen is used for testing, an error is returned:
Ora11g> tnsping dcsopen
TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 08-JAN-2015 00:38:55
Copyright (c) 1997,200 9, Oracle. All rights reserved.
Used parameter files:
/Opt/app/ora11g/product/11.2.0/dcsopen/network/admin/sqlnet. ora
TNS-03505: failure to resolve name
4. Use sqlplus to log on to the test. An error is returned:
Ora11g> sqlplus dcsopen/dcsopen1 @ dcsopen
SQL * Plus: Release 10.2.0.1.0-Production on Mon Sep 13 23:11:00 2010
Copyright (c) 1982,200 5, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS: cocould not resolve the connect identifier specified
Enter user-name:
5. Check the listener status,
Ora11g> lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0-Production on 08-JAN-2015 01:55:32
Copyright (c) 1991,200 9, Oracle. All rights reserved.
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 07-JAN-2015 20:19:09
Uptime 0 days 5 hr. 36 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener. ora
Listener Log File/opt/app/ora11g/diag/tnslsnr/dcsopen2Node/listener/alert/log. xml
Listening Endpoints Summary...
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = dcsopen2Node) (PORT = 1521 )))
...
The dcsopen configuration is not displayed.
6. Enable trace to view the cause of tnsping failure:
Create a sqlnet. ora File:
# Sqlnet. ora Network Configuration File:/opt/oracle/102/network/admin/sqlnet. ora
# Generated by Oracle configuration tools.
NAMES. DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
Trace_level_client = 16
Trace_directory_client =/opt/app/ora11g/product/11.2.0/dcsopen/network/admin
Trace_unique_client = on
Trace_timestamp_client = on
Diag_adr_enabled = off
Tnsping. trace_directory =/opt/app/ora11g/product/11.2.0/dcsopen/network/admin
Tnsping. trace_level = admin
View the tnsping. trc file after an error is reported:
Tail: tnsping. trc: file truncated
TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 07-JAN-2015 19:21:31
Copyright (c) 1997,200 9, Oracle. All rights reserved.
--- Trace configuration information follows ---
New trace stream is/opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsping. trc
New trace level is 6
--- Trace configuration information ends ---
--- Parameter source information follows ---
Attempted load of system pfile source/opt/app/ora11g/product/11.2.0/dcsopen/network/admin/sqlnet. ora
Parameter source loaded successfully
-> Parameter table load results follow <-
Successful parameter table load
-> Parameter table has the following contents <-
Diag_adr_enabled = off
Tnsping. trace_level = admin
Trace_level_client = 16
NAMES. DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
Tnsping. trace_directory =/opt/app/ora11g/product/11.2.0/dcsopen/network/admin
Trace_unique_client = on
Trace_directory_client =/opt/app/ora11g/product/11.2.0/dcsopen/network/admin
Trace_timestamp_client = on
--- Parameter source information ends ---
--- Log configuration information follows ---
Log stream will be "standard output"
Log stream validation not requested
--- Log configuration information ends ---
Nlstdipi: entry
Nlstdipi: exit
Nnfun2awanm: entry
Nnfgiinit: entry
Nncpcin_maybe_init: default name server domain is [root]
Nnfgiinit: Installing read path
Nnfgsrsp: entry
Nnfgsrsp: Obtaining path parameter from names. directory_path or native_names.directory_path
Nnfgsrdp: entry
Nnfgsrdp: Setting path:
Nnfgsrdp: checking element TNSNAMES
Nnfgsrdp: checking element EZCONNECT
Nnfgsrdp: Path set
Nnfun2a: entry
Nlolgobj: entry
Nnfgrne: entry
Nnfgrne: Going though read path adapters
Nnfgrne: Switching to TNSNAMES adapter
Nnftboot: entry
Nlpaxini: entry
Nlpaxini: exit
Nnftmlf_make_local_addrfile: entry
Nnftmlf_make_local_addrfile:
Construction of local names file failed
Nnftmlf_make_local_addrfile: exit
Nlpaxini: entry
Nlpaxini: exit
Nnftmlf_make_system_addrfile: entry
Nnftmlf_make_system_addrfile: system names file is/opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsnames. ora
Nnftmlf_make_system_addrfile: exit
Nnftboot: exit
Nnftrne: entry
Nnftrne: Original name: dcsopen
Nnfttran: entry
Nnfgrne:
Query unsuccessful, skipping to next adapter
Nnfgrne: Switching to EZCONNECT adapter
Nnfhboot: entry
Nnfhboot: exit
SnlinGetAddrInfo: entry
SnlinGetAddrInfo:
Getaddrinfo () failed with error-3
SnlinGetAddrInfo: exit
Nnfgrne: Query unsuccessful, skipping to next adapter
Nnfgrne: exit
Nnfun2a:
Address for name "dcsopen" not found
Nnfun2awanm: Getting the path of sqlnet. ora
Nnfun2awanm: Getting the path of local and system tnsnames. ora
Nnfun2awanm: exit
Nlse_term_audit: entry
Nlse_term_audit: exit
You can see the following errors:
Construction of local names file failed
Query unsuccessful, skipping to next adapter
Getaddrinfo () failed with error-3
Address for name "dcsopen" not found
Intuitively, The dcsopen listener is not identified.
Later I found an article about MOS (Client Connections Fail With TNS-12154/ORA-12154 (Document ID 1150680.1 ))
The reason for This situation may be: This means Oracle Net is
Unable to read the fileCorrectly or entry inside the file.
Solution:
Rebuild the TNSNAMES. ORA file, using the GUI Net Manager tool, Is the recommend solution. this will ensure there are no mistakes in the net admin file, for example, brackets, tab, spacing, etc. also ensure the tnsnames. ora file can be read by the oracle user.
6. The figure netca is used to create the dcsopen listener. The error persists. After a series of tests, the file is determined.
Listener. ora:
# Listener. ora Network Configuration File:/opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener. ora
# Generated by Oracle configuration tools.
DCSOPEN =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.101.19.57) (PORT = 1521 ))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC ))
)
)
)
SID_LIST_DCSOPEN =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/opt/app/ora11g/product/11.2.0/dcsopen)
(PROGRAM = extproc)
)
)
ADR_BASE_DCSOPEN =/opt/app/ora11g
Where:
(1) refer to the experience of eygle,
Use Dynamic Registration Service.
(When the instance is started, the background process PMON registers the Database Service Information in the listener. Under the dynamic registration mechanism, the SID_LIST part of the original listener is no longer needed .)
(2) There is also a SID_LIST above. This is the default PLSExtProc that is configured for external stored procedure calls. A simple listener is configured as described above.
Start the listener. The following message is displayed:
Ora11g> lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0-Production on 08-JAN-2015 01:55:32
Copyright (c) 1991,200 9, Oracle. All rights reserved.
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 07-JAN-2015 20:19:09
Uptime 0 days 5 hr. 36 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener. ora
Listener Log File/opt/app/ora11g/diag/tnslsnr/dcsopen2Node/listener/alert/log. xml
Listening Endpoints Summary...
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = dcsopen2Node) (PORT = 1521 )))
Services Summary...
Service"
Dcsopen"Has 1 instance (s ).
Instance"
Dcsopen", Status
READY, Has 1 handler (s) for this service...
Service "dcsopenXDB" has 1 instance (s ).
Instance "dcsopen", status READY, has 1 handler (s) for this service...
The command completed successfully
The native test tnsping is normal.
7. Then, use netca to add the tnsnames. ora file:
# Tnsnames. ora Network Configuration File:/opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsnames. ora
# Generated by Oracle configuration tools.
DCSOPEN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.101.19.57) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dcsopen)
)
)
The local test sqlplus... @ dcsopen is normal.
8. Access the database dcsopen from another machine, modify the tnsnames. ora file, and run tnsping dcsopen to report the following error:
Ora10g@localhost.localdomain $ tnsping dcsopen.
TNS Ping Utility for Linux: Version 10.2.0.1.0-Production on 08-JAN-2015 00:51:37
Copyright (c) 1997,200 5, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.101.19.57) (PORT = 1521) (CONNECT_DATA = (service_name = dcsopen )))
TNS-12560: TNS: protocol adapter error
Run sqlplus... @ dcsopen and report the following error:
SQL * Plus: Release 10.2.0.1.0-Production on Thu Jan 8 00:58:14 2015
Copyright (c) 1982,200 5, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS: protocol adapter error
First, check whether the port is enabled and execute telnet to report an error:
Ora10g@localhost.localdomain $ telnet 172.101.19.57 1521
Trying 172.101.19.57...
Telnet: connect to address 172.101.19.57:
No route to host
If the port is not opened, the following error occurs:
Ora10g@localhost.localdomain $ telnet 172.27.19.56 1521
Trying 172.27.19.56...
Telnet: connect to address 172.27.19.56:
Connection refused
Is it a firewall problem ???
Disable the firewall on the slave Database Server:
[Root @ dcsopen2Node ~] # Service iptables stop
Iptables: Flushing firewall rules: [OK]
Iptables: Setting chains to policy ACCEPT: nat mangle filter [OK]
Iptables: Unloading modules: [OK]
Run the following command from a remote machine:
Ora10g@localhost.localdomain $ telnet 172.101.19.571521
Trying 172.101.19.57...
Connected to 172.101.19.57.
Escape character is '^]'.
It indicates that the port has been opened. More importantly, it is clear that it is a firewall problem.. As a result, add a new line to the/etc/sysconfig/iptables file to allow access from port 1521:
[Root @ dcsopen2Node sysconfig] # vi iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
* Filter
: Input accept [0: 0]
: Forward accept [0: 0]
: Output accept [0: 0]
-A input-m state -- state ESTABLISHED, RELATED-j ACCEPT
-A input-p icmp-j ACCEPT
-A input-I lo-j ACCEPT
-A input-m state -- state NEW-m tcp-p tcp -- dport 22-j ACCEPT
-A input-m state -- state NEW-m tcp-p tcp -- dport 1521-j ACCEPT
-A input-j REJECT -- reject-with icmp-host-prohibited
-A forward-j REJECT -- reject-with icmp-host-prohibited
COMMIT
Start Firewall:
[Root @ dcsopen2Node sysconfig] # service iptables start
Iptables: Applying firewall rules: [OK]
Or service iptables restart
Access from a remote machine:
Ora10g@localhost.localdomain $ sqlplus dcsopen/dcsopen1 @ dcsopen
SQL * Plus: Release 10.2.0.1.0-Production on Thu Jan 8 01:11:12 2015
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Normal.
Summary:
1. Use the netca tool to create listener. ora and tnsnames. ora configuration files.
2. My troubleshooting logic is to first ensure that the local machine can be accessed by tnsping and sqlplus... @ xxx, and then the remote machine can be viewed.
Remote Access to tnsping and sqlplus error TNS-12560: TNS: protocol adapter error, indicating that the connection between the two machines may be faulty, rather than listening to their own problems.
3. The telnet error is No route to host, not Connection refused. It is not easy to think that the network port is not open. Because it indicates that you can access another remote machine normally, you should consider whether it is a firewall problem. In this case, you can disable the firewall and test whether this problem occurs.
4. The trace of tnsping is used to set the sqlnet. ora configuration file.
5. Use of Dynamic Registration. Benefits: Simplified Listener Configuration, failover (RAC) during connection, and Server Load balancer (RAC) during running ).
6. Some posts said that the $ TNS_ADMIN environment variable needs to be set during the solution. Although there are two Oracle versions on the same machine, they can be accessed without being configured. This is not a key issue.
Some posts mentioned sqlnet. NAME. the DEFAULT_DOMAIN parameter indicates that the trail log is tnsping. trc looks at the default name server domain is [root], but it can be accessed even if it is not set later. This shows that this is not a key issue.
A helpful post for me during the solution:
Http://blog.itpub.net/7199859/viewspace-374281/
Http://blog.itpub.net/519536/viewspace-673794/
Http://blog.sina.com.cn/s/blog_9151e7300101ksui.html
Http://www.cnblogs.com/chinaairforce1/archive/2009/10/22/1588103.html
Http://blog.csdn.net/huzia/article/details/21526043
7.The most important conclusion: Don't give up!
Auxiliary knowledge:
Run xhost + as the root user before running netca. Otherwise, the following message is displayed:
Java. lang. NullPointerException
At oracle. ewt. lwAWT. BufferedApplet. <init> (Unknown Source)
At oracle.net. ca. NetCA. <init> (NetCA. java: 420)
At oracle.net. ca. NetCA. main (NetCA. java: 406)
The problem persists.:
Compared to the following configuration, no difference was found, which is strange:
Manual creation:
Dcsopen =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.101.19.57) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dcsopen)
)
)
Tool creation:
DCSOPEN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.101.19.57) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dcsopen)
)
)
