Common Errors in Oracle Database TNS

1. ORA-12541: TNS: No listener

Cause: the listener is not started or damaged. If it is the former, use the commandNetStart oracleorahome10gtnslistener (the name may be different); if it is the latter, use the "net configuration assistant" tool Wizard to "ListenProgramAdd a listener (basically no information needs to be written.OK. You may need to delete all listeners before adding them !)

2. ORA-12500: TNS: Listeners cannot start dedicated serversProcessOr ORA-12560: TNS: protocol adapter Error

Cause: Oracle Database Service is not started. Run the Net start oracleserviceoradb command (oradb is the database name. If the problem persists, proceed to the following section.

3. If the database service fails to be started, the registry key value may be corrupted. The best practice is to perform the following two steps:

1) oradim-delete-Sid oradb deletes database service items

2) oradim-New-Sid oradb adds database service items

Note: if an error occurs during this process, restart the computer!

4, ORA-12154: TNS: can resolve the service name

Cause: the network service name of Oracle is not correctly configured. Use "Local Network Service name configuration" in the "net8 configuration assistant" tool Wizard to configure TNS. If the problem persists, proceed to the following section.

5. ORA-1034: TNS: Oracle unavailable

Cause: the Oracle Database Service is correctly started, but the database is not opened!

Run the following command:

1) svrmgrl start the Service Manager

2) connect internal logs in as internal

3) Startup open the database

6. ORA-12560: TNS: protocol adapter error (stubborn)

Cause: unknown.

Solution: Be sure to activate the Windows Task Manager, kill oracle.exeand oradim.exe processes, write your own ora_startup.bat, and execute it!

PS:

1. ora_startup.bat:

NetStart oracleorahome81tnslistener

Net start oracleserviceoradb

Svrmgrl is generally not used, but sometimes it is indispensable. For detailed steps, see step 1.

2. ora_shutdown.bat:

Net stop oracleorahome81tnslistener

Net stop oracleserviceoradb

3. for Oracle service names, see service names starting with Oracle in "services" in "Administrative Tools"

1. TNS-12154 error or ORA-12154

Feature: SQL * Net does not find a connection string or Alias

Cause 1(1) The tnsnames. ora file is not found. The default path of this file is:

 

Windows 95/98Client  SQL * Net 2.x-orawin95networkadmin Net8-orawin95net80admin Net8i-oracleora81networkadmin WindowsNTClient SQL * Net 2.x-orantnetworkadmin Net8-orantnet80admin Net8i-oracleora81networkadmin UNIX Client $ ORACLE_HOME/Network/admin Or/etc Or/var/opt/Oracle

 

. Set tns_admin on the client

Set tns_admin. tns_admin to point to the location of the tnsnames. ora file in the Registry hkey_local_machinesoftwareoracle.

Measure: Check the file location according to the preceding instructions.

Cause 2(2) The content format in the tnsnames. ora file is incorrect.

Measure: 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 )))

 

Cause 3: (3) Inconsistent tnsnames. ora and sqlnet. ora

Measure: The sqlnet. 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. You can add

 

Dev1 = (Description = (address_list = (Address = (Protocol = TCP) (host = 145.45.78.56) (Port = 1521 ))) (CONNECT_DATA = (SID = orcl )))

 

In this case, you can connect.

2. NL-00462 error or ORA-00462

Feature: The Listener cannot be started.

Cause: the content of the listener. ora file is incorrect, such as mismatched brackets, incorrect parameter names, and redundant spaces.

Measure: re-edit the file.

3. NL-00405 error or ORA-00405

Feature: The Listener. ora file is not readable or cannot be found.

Cause: the content of the listener. ora file is incorrect, such as mismatched brackets, incorrect parameter names, and redundant spaces.

Measure: Make sure that the file is located in the default directory $ ORACLE_HOME/Network/Admin, or set the environment variable tns_admin to point to the location of the file.

4. TNS-01155 error or ORA-01155

Feature: The sid_list_listener variable value in the listener. ora file is incorrect.

Cause: the content of the listener. ora file is incorrect. The value of sid_list_listener variable is incorrect.

Measure: Make sure that the values in the file are correct. There is a sample file in the $ ORACLE_HOME/Network/admin/Samples Directory for your reference.

5. TNS-12537, TNS-12560, TNS-00507 Error

Feature: An invalid protocol adapter is referenced in the listener. ora file.

Cause: the value of the procotol variable in the listener. ora file is incorrect.

Measure: Make sure that the assignment value in the file is correct.TCPProtocol. The $ ORACLE_HOME/Network/admin/samples directory contains sample files for your reference. Use adapters tnslsnr to check protocol programs

6. TNS-12203 Error

Feature: cannot be connected to the target

Cause: whether the listener is started; whether the alias is configured

Measure: Check whether the listener is started, such as LSNRCTL status; check the alias configuration of tnsnames. ora.

7. TNS-12533 Error

Feature: cannot be connected to the target

Cause: InvalidAddressParameter,

Measure: Check the address parameter of tnsnames. ora.

1) Use tnsping net_service_name to test whether the network service name is correctly configured!

2) use the sqlplus program to test through the test network service name, such as sqlplusSystem/Manager @ test. If you cannot connect to the database, the test network service name (NetService) followed by the sqlnet. ora file names. default_domain parameter value,

 

Http://blog.csdn.net/hdhai9451/article/details/3957094