Summary of various TNS errors-Summary of various errors in the listener and tnsnames. ora configuration file

Local name configuration: The local name can access the required database or server information with a simple name, and the information is saved to tnsnames. ora.

LOCALNAME: local name
ADDRESS: server ADDRESS
PROTOCOL: PROTOCOL used
HOST: IP Address
PORT: listening PORT
SERVICE_NAME: Database Service name
First, make sure that the format in the tnsnames. ora file is correct, such as =, space, and parentheses.
This can refer to one of my tnsnames. ora templates: http://blog.csdn.net/q947817003/article/details/11180027

When we issue a command: sqlplus bys/bys @ test, the following steps are generally taken to connect to the database:

1. First, find tnsnames. ora in the related directory of the sqlplus program.

2. Search for the string starting with test = in tnsnames. ora.

3. according to tnsnames. search for PROTOCOL = TCP (HOST = 192.168.1.211) in the test = string in ora and query the corresponding HOST. If it is an IP address, access it directly. If it is a domain name, it must be resolved to an IP address.

4. After communicating with the HOST described in HOST =, use the TCP protocol to connect to the PORT 1521 of the HOST according to the description in (PORT = 1521.

5. If the connection is successful, the listener is connected to the database. In this case, search for SERVICE_NAME = bys1 in the test = string in tnsnames. ora, and specify the bys1 service to connect to the listener again.

6. If the connection is successful and you have connected to the bys1 service of the listener, the listener will connect to the specific instance based on the instance corresponding to the Service in the listener. (In tnsnames. the test = string of ora can also be specified in the SERVICE_NAME = sentence. The specific instance is not specified in my experiment. If this parameter is not specified, the listener allocates the instance; if the listener contains multiple instances (such as RAC) under the same service, the listener dynamically distributes connections ).

7. If the connection is successful, the instance is connected. Next, the database will verify the correctness of the user name and password.

This experiment uses the above sqlplus bys/bys @ test to connect to the database to demonstrate errors in each step.

Statements about connecting to the database: sqlplus bys/bys@192.168.1.211: 1521/bys1 this is not using the tnsnames. ora file. Connect to the bys1 service on port 1521 of the host 192.168.1.211. For SQL plus connection to the database writing, for more details see: http://blog.csdn.net/q947817003/article/details/11180137

Lab environment: LINUX--OLE 5.8; Oracle Database 11g Enterprise Edition Release 11.2.0.1.

######################################## ######################################## ##

1. The tnsnames. ora file does not exist. In this case, the local name cannot be used for connection.

During testing:
TNSPING reported: TNS-03505: Failed to resolve name
SQLPLUS: ORA-12154: TNS: cocould not resolve the connect identifier specified

Lab data

[Oracle @ bys001 admin] $ ls
Listener. bak samples tnsnames. ora
Listener. ora shrept. lst
[Oracle @ bys001 admin] $ mv tnsnames. ora tnsnames. oraa
[Oracle @ bys001 admin] $ tnsping bys1
TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 08-NOV-2013 09:24:18
Copyright (c) 1997,200 9, Oracle. All rights reserved.
Used parameter files:
TNS-03505: failure to resolve name
[Oracle @ bys001 admin] $ sqlplus bys/bys @ bys1
SQL * Plus: Release 11.2.0.1.0 Production on Fri Nov 8 09:25:12 2013
Copyright (c) 1982,200 9, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS: cocould not resolve the connect identifier specified

######################################## ######################################## ##

2. In tnsnames. ora, the field description corresponding to the test place name is in the correct format, and the Database Listener is enabled. However, the local place name used for TNSPING or SQLPLUS is incorrect.

(This place name refers to the description field in tnsnames. ora:

Test =

(DESCRIPTION = here; that is, TEST in sqlplus bys/BYS @ TEST)

During testing:
TNSPING reported: TNS-03505: Failed to resolve name
SQLPLUS: ORA-12154: TNS: cocould not resolve the connect identifier specified
Lab data:
[Oracle @ bys001 admin] $ tnsping hello
TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 07-NOV-2013 23:41:29
Copyright (c) 1997,200 9, Oracle. All rights reserved.
Used parameter files:

TNS-03505: failure to resolve name
[Oracle @ bys001 admin] $ sqlplus bys/bys @ hello
SQL * Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:42:36 2013
Copyright (c) 1982,200 9, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS: cocould not resolve the connect identifier specified
######################################## ######################################## #########################

3. In tnsnames. ora, the field description corresponding to the test place name is in the correct format, and the Database Listener is enabled. However, the IP address specified by the HOST = field in tnsnames. ora does not exist or cannot be pinged.

During testing:

TNSPING and SQLPLUS errors are: TNS-12543: TNS: destination host unreachable

Lab data:

[Oracle @ bys001 admin] $ cat tnsnames. ora
# Tnsnames. ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames. ora
# Generated by Oracle configuration tools.
Bys1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.211) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bys1)
)
)
Test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.222) (PORT = 1521) manually change the ip address in the HOST to a non-existent ip address.
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bys1)
)
)
[Oracle @ bys001 ~] $ Ping 192.168.1.222
PING 192.168.1.222 (192.168.1.222) 56 (84) bytes of data.
From 192.168.1.211 icmp_seq = 2 Destination Host Unreachable
From 192.168.1.211 icmp_seq = 3 Destination Host Unreachable
[Oracle @ bys001 admin] $ tnsping test
TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 07-NOV-2013 23:08:12
Copyright (c) 1997,200 9, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.222) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys1 )))
TNS-12543: TNS: destination host unreachable
[Oracle @ bys001 admin] $ sqlplus bys/bys @ test
SQL * Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:08:21 2013
Copyright (c) 1982,200 9, Oracle. All rights reserved.
ERROR:
ORA-12543: TNS: destination host unreachable

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

Differences between listener. ora sqlnet. ora tnsnames. ora in Oracle databases

Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)

Oracle RAC listener Configuration (listener. ora tnsnames. ora)

Oracle: listener. ora, sqlnet. ora, tnsnames. ora configuration and example

Configure tnsnames. ora in the local Naming Service of Oracle

1 2 3 4 5 Next Page