Oracle Database The solution Rollup for common errors of TNS _oracle

TNS is part of Oracle NET and is a tool designed to manage and configure Oracle databases and client connections, and in most cases the client and the database must configure TNS to communicate. This article focuses on the solution to the common errors of TNS in Oracle databases as follows:

1, Ora-12541:tns: No listener

Cause: The listener is not started or the listener is corrupted. If the former, use the command net start Oracleorahome10gtnslistener (the name may differ), and if it is the latter, use the "Net Configuration Assistant" Tool Wizard, "Listener Configuration" Add a listener (basically do not write any information, all the way OK.) You may need to delete all the listeners before adding them!)

2, Ora-12500:tns: The listener could not start the dedicated server process or ORA-12560:TNS: Protocol adapter error

Reason: Oracle's database service is not started. Use the command net start oracleserviceoradb (ORADB is the database name). If it is still unresolved, continue looking down.

3. If the database service fails to start, it is most likely that its registry key value is corrupted and the best practice is the following two steps:

(1) ORADIM-DELETE-SID oradb Delete database service items
(2) ORADIM-NEW-SID oradb New Database service items
Note: If there is an error in this process, restart the computer!

4, Ora-12154:tns: can resolve service name

Reason: Oracle's Network service name is not configured correctly. Use the "Local Network Service name configuration" Net8 Configuration Assistant Tool Wizard to configure TNS. If it is still unresolved, continue looking down.

5, Ora-1034:tns:oracle is not available

Cause: Oracle's database service started correctly, but the database did not open!
To use the command:
(1) SVRMGRL start Service Manager
(2) Connect internal to internal identity landing
(3) Startup Open database

6, Ora-12560:tns: Protocol adapter error (stubborn)

Reason: Unknown.
Solution: Must kill skill--opens "The Windows Task Manager", kills the ORACLE.exe and the ORADIM.exe process, writes own Ora_startup.bat, executes!
Ps:

(1) Ora_startup.bat:

net start Oracleorahome81tnslistener
net start oracleserviceoradb

SVRMGRL generally not, but sometimes it is not, the concrete steps see step 5th.

(2) Ora_shutdown.bat:

net stop Oracleorahome81tnslistener
net stop oracleserviceoradb

(3) Oracle related service names see service names that begin with Oracle in the services of administrative tools

1, TNS-12154 Error or ORA-12154
feature: Sql*net did not find a connection string or alias
Reason 1: (1) The Tnsnames.ora file was not found, and the default path for this file is:

Windows 95/98 client
sql*net 2.x-orawin95networkadmin
net8-orawin95net80admin
net8i- Oracleora81networkadmin
Windows NT client
sql*net 2.x-orantnetworkadmin
net8-orantnet80admin
Net8i-oracleora81networkadmin
UNIX Client
$ORACLE _home/network/admin
or/etc
or/var/opt/oracle

. Setting Tns_admin on the client
Set tns_admin in registry hkey_local_machinesoftwareoracle. Tns_admin point to Tnsnames.ora file location
Measures: Check file location as described above
Reason 2: (2) TNSNAMES. The content in the Ora file is not well-formed
Measures: Check the file format, the standard format is as follows:

DEV1. World = (
DESCRIPTION = (address_list = (Address
= (PROTOCOL = TCP) (Host = 145.45.78.56) (Port = 1521
)) (Connect_data = (SID = ORCL))

Reason 3: (3) TNSNAMES. Ora is inconsistent with Sqlnet.ora
Measures: Sqlnet. The ORA format is as follows:

Trace_level_client = Off
sqlnet. Authentication_services = (NONE)
NAMES. Directory_path = (TNSNAMES)
AUTOMATIC_IPC = Off

Because there is no names.default_domain=world, the Dev1.world connection string cannot be connected and can be increased in Tnsnames.ora

DEV1 =
(DESCRIPTION = (Address_list =
(address = (PROTOCOL = TCP) (Host = 145.45.78.56) (Port = 1521))
(CON Nect_data = (SID = ORCL))

You can connect at this time.

2, NL-00462 Error or ORA-00462
feature: The Listener cannot start
Reason: LISTENER. Ora file content errors, such as parentheses mismatch, parameter name errors, extra spaces, and so on.
Action: Re-edit a file

3, NL-00405 Error or ORA-00405
Features: LISTENER. The Ora file is unreadable or cannot be found
Reason: LISTENER. Ora file content errors, such as parentheses mismatch, parameter name errors, extra spaces, and so on.
Action: Make sure the file location is $oracle_home/network/admin under the default directory, or set the environment variable Tns_admin point to the location of the file.

4, TNS-01155 Error or ORA-01155
Features: LISTENER. Sid_list_listener variable Assignment in ora file is not correct
Reason: LISTENER. Ora file content error, Sid_list_listener variable assignment is not correct.
Measures: To ensure the correct value in the file, $ORACLE _home/network/admin/samples directory has a sample file for reference.

5, TNS-12537, TNS-12560, TNS-00507 Error
Features: LISTENER. An invalid protocol adapter was referenced in the Ora file
Reason: LISTENER. Procotol variable Assignment in ora file is not correct.
Measures: To ensure the correct value in the file, the general use of TCP protocol, $ORACLE _home/network/admin/samples directory has a sample file, available for reference. Check protocol with adapters Tnslsnr

6, TNS-12203 Error
feature: Cannot connect to target
Cause: Whether the listener is started or if the alias is configured
Action: Check whether the listener is started, such as Lsnrctl status; Check tnsnames.ora alias configuration

7, TNS-12533 Error
Feature: Cannot connect to target
Reason: illegal address parameter,
Measures: Check the address parameters of the Tnsnames.ora
1 use tnsping net_service_name to test whether the network service name is configured correctly!!
2 Use the Sqlplus program to test the network service name, such as Sqlplus system/manager@test. If you cannot connect to the database, the test Network service name (NET services) in the Tnsname.ora file is followed by the value of the Sqlnet.ora file Names.default_domain parameter.
Under what circumstances will the Oracle automatically set the Names.default_domain parameter?

The typical environment in which this happens is for Windows clients ' My computer à properties à the computer name à change à other ... à set the ' primary DNS suffix ' in the primary DNS suffix of this computer, because in this case the client is installed in Sqlnet. The Names.default_domain parameter is automatically set in the Ora file, which may also occur when the computer is joined to a domain to install an Oracle client, and you can try if you have a condition.

I usually manually modify the Tnsnames.ora file when I set up Oracle clients, but there are many people who prefer the graphical tool configuration, which eventually modifies the Tnsnames.ora file, but it sometimes causes other problems:

When configuring the network service name with the Oracle graphics configuration software ' net Assistant ' or ' Net Configuration assistant ', if you have set the ' primary DNS Suffix ', but there is no ' primary DNS suffix ' in the name of the network service name written in the graphics configuration software, and if you just write test, the graphics configuration software will automatically add ' primary DNS suffix ' to the test. Testserver.com, and exists in Tnsnames.ora, regardless of whether there are names.default_domain parameters in your Sqlnet.ora file. At this point, the graphical tool to test the connection is passed, but if there is no names.default_domain parameter in the Sqlnet.ora file at this time, Instead of the test you type in the graphics configuration software, you should use the test.testserver.com in the Tnsnames.ora when using the network service name. The solution is:

<1> you can set up names.default_domain= testserver.com in the Sqlnet.ora file, where you can connect to the database with test or test.testserver.com
<2> do not set Names.default_domain parameters in the Sqlnet.ora file. In the Tnsnames.ora file, remove the. testserver.com in test.testserver.com, where you can connect to the database with test.