TROUBLESHOOTING GUIDE:
TNS-12203 error
---------------------------------------------------------------------------
Note: PLEASE READ
Because of ASCII diagrams, this document is best viewed with
Courier New or Lucida Console font. This is normally the default.
In Internet Explorer:
Set the Plain Text Font to Courier New or Lucida Console
In Netscape:
Set the Fixed Width Font to Courier New or Lucida Console 10pt.
Click "Use my default fonts, overriding document-specified fonts"
---------------------------------------------------------------------------
Contents
1............................................TNS-12203 from Windows 3.x
2............................................TNS-12203 from Windows 95/98/NT
3............................................TNS-12203 from Unix
4............................................Firewalls
5............................................TNS-12203 when Using Oracle Names
6............................................Configuration Files Examples
1
=============================================================================
=============================================================================
Windows 3.x
When troubleshooting items for resolving ORA/TNS-12203 errors on Windows 3.1
or Windows 3.11, it is helpful to have a copy of the TNSNAMES.ORA file
located in front of you. This file is normally located in the
ORAWIN\NETWORK\ADMIN directory. If the file is not there, skip to section 6.
1.1. Go to, or log on to, the server where Oracle resides.
If you have a Windows NT server, go to section 1.2.
If you have a Unix server, go to section 1.3.
1.2. Open a DOS window and type 'ipconfig' at the operating system prompt.
Your DOS window will show information similar to the following:
+-----------------------------------------------------------------------+
| Command Prompt [_][-][X] |
+-----------------------------------------------------------------------+
| Microsoft(R) Windows NT(TM) |
| (C) Copyright 1985-1996 Microsoft Corp. |
| |
| C:\>ipconfig |
| |
| Windows NT IP Configuration |
| |
| Ethernet adapter El90x1: |
| |
| IP Address. . . . . . . . . : 138.2.121.221 |
| Subnet Mask . . . . . . . . : 255.255.255.0 |
| Default Gateway . . . . . . : 138.2.121.1 |
| |
| C:\> _ |
+-----------------------------------------------------------------------+
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 1.4.
1.3. At the operating system prompt, type '/usr/sbin/ifconfig -a'. The
command will display the actual IP address of the server.
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 1.4.
Note: The ifconfig command may be different of different Unix platforms.
A 'man' on ifconfig will inform you of the correct switches to use
in order to display the IP address.
1.4. Open your listener.ora file in a text editor. If you do not have a
LISTENER.ORA, skip to section 6.
Replace the hostname with the IP address in your TCP address block.
If you are already using the IP address, and it matches the IP address
you found earlier, go to section 1.6. If not, make the change and save
the LISTENER.ORA file. Continue to section 1.5.
1.5. At the command prompt, restart the listener by typing 'lsnrctl stop'
(if you using Windows NT running Oracle8 (8.0.x), type 'lsnrctl80 stop')
followed by the command 'lsnrctl start' (if you are on Windows NT
running Oracle8, type 'lsnrctl80 start').
1.6. On the client, go to the command prompt and type 'ping <IP address>'
using the IP address of server you found earlier in this section. For
example:
> ping 138.2.158.34
You should get some response back stating that the ping was successful or
that the server is alive. If this case, continue to section 1.7. If you
get any errors, or messages stating that there was a timeout problem, or
that the host was unreachable, or the IP address was bad, contact your
network administrator and find out why you cannot ping the server. This is
cause of your TNS-12203 error.
1.7. You have verified that you can physically contact the server and that the
Listener is running. The next thing to do is verify the correctness of the
TNSNAMES.ORA file. Open the LISTENER.ORA file in a text editor. Make sure
the HOST parameter for the alias you are trying to connect with is set to
the IP address of the server - rather than using the hostname. You also
need to make sure the value of the PORT parameter in the TNSNAMES.ORA file
matches the value of the PORT parameter in the LISTENER.ORA file. After
you verify these two items, try a connection. If the connection
still fails, continue to the next section.
1.8. Go to the Windows operating system directory and open the WIN.INI file.
If you search for the word 'Oracle', you should find an Oracle section that
looks like this:
[Oracle]
ORA_CONFIG=c:\windows\oracle.ini
Although the path to the ORACLE.INI file may be different, if you do not
have this section, deinstall all SQL*Net products and reinstall them.
Go to the ORACLE.INI file specified by the path from the ORA_CONFIG section
and open the file in a text editor.
There are only 2 parameters in this file that can yield an ORA-12203 error.
They are ORACLE_HOME and TCP_VENDOR.
ORACLE_HOME should be set to the location of your current installation of
SQLNet. TCP_VENDOR should be set to a value that will load the DLLs of your
protocol stack. For example, if TCP_VENDOR=WINSOCK, you should have
MWINSOCK.DLL and MSOCKLIB.DLL in the ORAWIN\bin directory.
You van verify the correctness of TCP_VENDOR parameter by searching for the
value of TCP_VENDOR (i.e winsock, novlwp) in the VSL.INI file. The 2 DLLs
referenced in the VSL.INI file should be in your ORAWIN\bin directory.
1.9. Make sure the VSL.INI file is present in the WINDOWS home directory.
1.10 If the connection still fails, go to the ORAWIN\orainst directory and open
the WINDOWS.RGS file. Make sure the TCP protocol adapter is installed
by finding the line that looks something like this (note version numbers
may be different):
72 wintcp20 winnet20 "tcp23" "2.3.3.0.0" "Oracle TCP/IP Adapter"
If you do not have this, reinstall SQL*Net and make sure you install the
Oracle TCP Protocol Adapter.
1.11 Go back to the ORACLE.INI file and make sure you remove any SQLNET_DBNAME
parameters.
1.12 If you are running SQL*Net for Windows on Windows95/98/NT, make sure your
WINSOCK.DLL is in the correct location and of the correct size using the
chart:
Filename OS Location Size
------------+-------+---------------------------+--------------------
winsock.dll Win98 \windows 21k (21,504 bytes)
winsock.dll Win95 \windows 42k (42,080 bytes)
winsock.dll WinNT \winnt\system32 3k (2,880 bytes)
2
=============================================================================
=============================================================================
Windows95 Windows98 Windows NT:
When troubleshooting ORA/TNS-12203 errors on Windows 95, 98, or NT, it will be
helpful to have a copy of the TNSNAMES.ORA file located in front of you. This
file is normally located in the ORACLE_HOME\NETWORK\ADMIN directory (or NET80\ADMIN).
If the file is not there, go to section 6.
2.1. Go to, or log on to, the server where Oracle resides.
If you have a Windows NT server, go to section 2.2.
If you have a Unix server, go to section 2.3.
2.2. Open a DOS window and type 'ipconfig' at the operating system prompt.
Your DOS window will show information similar to the following:
+-----------------------------------------------------------------------+
| Command Prompt [_][-][X] |
+-----------------------------------------------------------------------+
| Microsoft(R) Windows NT(TM) |
| (C) Copyright 1985-1996 Microsoft Corp. |
| |
| C:\>ipconfig |
| |
| Windows NT IP Configuration |
| |
| Ethernet adapter El90x1: |
| |
| IP Address. . . . . . . . . : 138.2.121.221 |
| Subnet Mask . . . . . . . . : 255.255.255.0 |
| Default Gateway . . . . . . : 138.2.121.1 |
| |
| C:\> _ |
+-----------------------------------------------------------------------+
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 2.4.
2.3. At the operating system prompt, type '/usr/sbin/ifconfig -a'. This
command will display the actual IP address of the server.
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 2.4.
Note: The ifconfig command may be different of different Unix platforms.
A 'man' on ifconfig will inform you of the correct switches to use
in order to display the IP address.
2.4. Open your listener.ora file in a text editor. If you do not have a
LISTENER.ORA, skip to section 6. Replace the hostname with the IP address
in your TCP address block.
If you are already using the IP address, and it matches the IP address you
found earlier, go to section 2.5. If not, make the change and save the
LISTENER.ORA file. Continue to section 2.5.
2.5. At the command prompt, restart the listener by typing 'lsnrctl stop' (if
you are using Windows NT running Oracle8, type 'lsnrctl80 stop') followed by
command 'lsnrctl start' (if you are on Windows NT running Oracle8,
type 'lsnrctl80 start').
2.6. On the client, go to the command prompt and type 'ping <IP address>'
using the IP address of server you found earlier in this section. For
example:
> ping 138.2.158.34
You should get some response back stating that the ping was successful or
that the server is alive. If this is the case, continue to section 2.7. If
you get any errors, or messages stating that there was a timeout problem, or
that the host was unreachable, or the IP address was bad, contact your
network administrator and find out why you can't ping the server. This is
cause of your TNS-12203 error.
2.7. You have verified that you can physically contact the server and that the
Listener is running. The next thing to do is verify the correctness of the
TNSNAMES.ORA file. Locate the TNSNAMES.ORA file. This file is usually in the
ORACLE_HOME\network\admin (or net80\admin) directory. Open the file in a text
editor. Make sure the HOST parameter for the alias you are trying to connect
with is set to the IP address of the server - rather than using the host name.
You also need to make sure the value of the PORT parameter in the TNSNAMES.ORA
file matches the value of the PORT parameter in the LISTENER.ORA file. After
you verify these two items, try a connection. If the connection still fails,
continue to section 2.8.
2.8. Often, users unknowingly use an application designed for SQLNet 2.3, but only
have Net8 installed or vice versa. For example, if you are executing PLUS33W.EXE,
then make sure the TNSNAMES.ORA file is in the network\admin directory. If you
are using PLUS80W.EXE, make sure the TNSNAMES.ORA file is in the net80\admin
directory. If you have a 3rd-party application and are unsure which directory
the configuration files go in, place the TNSNAMES.ORA file and SQLNET.ORA file
in both the \network\admin and net80\admin directories.
2.9. We need to verify the file size of your WSOCK32.DLL. This DLL is the 32-bit
Winsock driver for the operating system. Use the table below to determine
if you are using the correct file:
Filename OS Location Size
------------+-------+---------------------------+--------------------
wsock32.dll Win98 \windows\system 40k (40,960 bytes)
wsock32.dll Win95 \windows 65k (66,560 bytes)
wsock32.dll WinNT \winnt\system32 20k (20,240 bytes)
If your wsock32.dll does not match the appropriate file as listed above,
then reinstall your operating system's TCP/IP stack. If your DLLs are okay,
continue to section 2.10.
2.10 There may be a syntactical problem with the configuration files.
Please check the syntax as described in section 6.
2.11 Other causes of ORA-12203 include the unintentionally Installation of Oracle
Parallel Server Option as discussed in <Note:67259.1>.
3
=============================================================================
=============================================================================
TNS-12203 from Unix:
The most common cause of getting a TNS-12203 from a Unix client is a linking
problem. Rather than finding out if an executable is linked with the SQL*Net
TCP/IP Protocol Adapter, it is usually just easier to manually relink.
If relinking is not an issue, then we will continue the troubleshooting from
the point of view of a TCP/IP problem.
3-1. At the operating system prompt, type:
$ which make
You should be using '/usr/ccs/bin/make'. If not, prepend the directory
'/usr/ccs/bin' to PATH eg.
$ PATH=/usr/ccs/bin
PATH;export PATH
Next, go to the directory where the make file (.mk) for the executable
is being used. For example, if you are using SQL*Plus, go the
$ORACLE_HOME/sqlplus/lib directory. If you are using a 3rd party executable
find the directory where its make file is located. At the operating system
prompt relink the executable. For example, if you are using SQLPlus,
type the following command:
$ make -f ins_sqlplus.mk install
After the executable has recompiled, try the connection again. If the
connection still fails, please continue to the next section.
3.2. Go to, or log on to, the server where Oracle resides.
If you have a Windows NT server, go to section 3.3.
If you have a Unix server, go to section 3.4.
3.3. Open a DOS window and type 'ipconfig' at the operating system prompt.
Your DOS window will show information similar to the following:
+-----------------------------------------------------------------------+
| Command Prompt [_][-][X] |
+-----------------------------------------------------------------------+
| Microsoft(R) Windows NT(TM) |
| (C) Copyright 1985-1996 Microsoft Corp. |
| |
| C:\>ipconfig |
| |
| Windows NT IP Configuration |
| |
| Ethernet adapter El90x1: |
| |
| IP Address. . . . . . . . . : 138.2.121.221 |
| Subnet Mask . . . . . . . . : 255.255.255.0 |
| Default Gateway . . . . . . : 138.2.121.1 |
| |
| C:\> _ |
+-----------------------------------------------------------------------+
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 3.5.
3.4. At the operating system prompt, type '/usr/sbin/ifconfig -a'. This
command will display the actual IP address of the server. You will be
referring to this IP address throughout the paper so you may want to
jot it down. Continue to section 3.5.
Note: The ifconfig command may be different of different Unix platforms.
A 'man' on ifconfig will inform you of the correct switches to use
in order to display the IP address.
3-5. Open the LISTENER.ORA file in a text editor. If you do not have a
LISTENER.ORA, skip to section 6. Replace the hostname with the IP
address in your TCP address block.
If you are already using the IP address, and it matches the IP address you
found earlier, go to section 3.6. If not, make the change and save the
LISTENER.ORA file. Continue to section 3.6.
3.6. At the command prompt, restart the Listener by typing 'lsnrctl stop' (if
you are on NT running Oracle 8, type 'lsnrctl80 stop') followed by the command
'lsnrctl start' (if you are on NT running Oracle 8, type 'lsnrctl80 start').
3.7. On the client, go to the command prompt and type 'ping <IP address>'
using the IP address of server you found earlier in this section. For
example:
> ping 138.2.158.34
You should get some response back stating that the ping was successful or
that the server is alive. If this is the case, continue to section 3.8. If
you get any errors, or messages stating that there was a timeout problem, or
that the host was unreachable, or the IP address was bad, contact your
network administrator and find out why you cannot ping the server. This is
cause of your TNS-12203 error.
3.8. You have verified that you can physically contact the server and that the
Listener is running. The next thing to do is verify the correctness of the
TNSNAMES.ORA file. Locate the TNSNAMES.ORA file. This file is usually in the
$ORACLE_HOME/network/admin directory. Open the file in vi. Ensure the
HOST parameter for the alias you are trying to connect with is set to the IP
address of the server - rather than using the host name. You also need to
make sure the value of the PORT parameter in the TNSNAMES.ORA file matches the
value of the PORT parameter in the listener.ora file. Also set the TNS_ADMIN
environment variable to point to the directory the LISTENER.ORA file is
located. For more information on TNS_ADMIN see <NOTE:111942.1> "Setting
the TNS_ADMIN environment variable". After you have verified these items,
try a connection.
4
=============================================================================
=============================================================================
Firewalls:
Firewalls, Windows NT, and Redirections
On Windows NT, when a connect request comes in to the Listener, the Listener
spawns and Oracle thread. This thread is a listening thread, and is started
on a wild-card address meaning that the thread is listening for connections
on the current I.P. address, and an unused port number given to the thread by
the networking software. The Oracle thread will contact the Listener using
IPC and inform the Listener of its listening address, connection load and other
status information. The Listener sends back to the client a REDIRECT
address. This tells the client to reconnect to the newly spawned Oracle thread.
Since this Oracle thread is on a random port (a range of ports cannot be
defined), the firewall will not let the connection through. The resulting
error is usually a TNS-12203.
There are two ways to resolve this issue.
The first way is to use a firewall that has a SQL*Net proxy built into it. The
way this works is that the SQL*Net proxy starts another listening processes
(usually on port 1610). This causes the firewall to act as a Multi Protocol
Interchange. So, by using the TNSNAV.ORA file on the client, you connect to
port 1610 (the firewall). The firewall passes the connection to the server.
The server gives a redirect to the client. The client reconnects to the
firewall proxy on port 1610, and the firewall passes the connection to the
Oracle thread on the wild-card listening address. Here is what the connection
flow would look like:
1. Connect to proxy and pass connection to Listener
2. Send redirect to client
3. Connect to redirected address via the proxy
4. Oracle accepts the connection
Firewall
||
+------+ <--------2--------||-------2------ +---------+
|client| || |listener |(port=1521)
+------+ --------1------> proxy ----1------> +---------+
A \ /||\
| \---------3-------/ || \-----3------> +---------+
| || | oracle |(port=xxxx)
+--------------4---------||-------4------- +---------+
The second way to resolve this issue is to upgrade the server to 8.0.x and use
the USE_SHARED_SOCKET parameter in the Windows NT Registry. With this method, it
does not matter what kind of firewall you have.
The syntax for this parameter is: USE_SHARED_SOCKET = TRUE
Place the parameter in the registry under HKEY_LOCAL_MACHINE\Software\Oracle
Restart Oracle and the Listener for the parameter to take effect.
Here's how USE_SHARED_SOCKET works. The Listener binds and creates a socket
on the address specified in the LISTENER.ORA file. On this socket, there is a
LISTEN state active that is used by the Listener. When a new connection comes
in to the Listener, the Listener spawns an Oracle thread on the listening port
(i.e. 1521). This happens over and over again so that you have a Listener and
several established connections using port 1521. Pictorially, the scenario looks
like this:
+---<O>--------<O>----<O>---<O>--+
| |
| This square represents <O>
| a listening socket for |
| port 1521. |
<O> |
| <O> = oracle thread <O>
| <L> = listener |
| |
+-<O>-------<L>--<O>------<O>----+
The operating system then does a poll() or a select() on the socket to test for
any data. If any of the threads have data, a signal handler is used to contact
the application and inform it of the new data.
The disadvantage of USE_SHARED_SOCKET is that if the Listener shuts down, all
connections are dissolved (ie. terminated).
Finally, a very common question concerning the Listener and port numbers is why
different port numbers show up in the LISTENER.LOG file. What you are seeing
is the client's source port and client's source IP address. Here's how this
relates to your firewall.
If you want to make a TCP connection to a server (let's say with TELNET), you need
to create a socket. To create a socket, you need 4 pieces of information: a
source IP and port, and a destination IP and port. So using TELNET as an
example (the listening port for the TELNET process is 23 on the server):
source destination
source destination
+-----------+---------------+
IP |138.2.12.8 |185.45.67.53 |
+-----------+---------------+
port | xx | 23 |
+-----------+---------------+
Notice the source port is labeled as 'xx'. The networking software on the client chooses
at random, or in sequential order, a valid port (between 1024 and 65535) so the client
can send and receive data. This is what you are seeing in the LISTENER.LOG file.
Will this be a problem with the firewall?
No - the firewall will restrict incoming connections, but will freely let any
connection on any port out (which is okay). Here is what it might look like:
Firewall
<-------------||---------\
<-------------||---------\\
[CLIENT]----------------->|| \---[SERVER]
<-------------||---------//
<-------------||---------/
5
=============================================================================
=============================================================================
Oracle Names
When using Oracle Names, a TNS-12203 would normally be caused by bad information
being loaded from the region data, or incorrect registration when using Dynamic
Discovery Option/Enhanced Discovery Option (DDO/EDO).
Almost any other reason would be caused by a problem that can be fixed using
information from the other sections of this bulletin. Another reason would be
if the client were physically not able to contact the nameserver.
5.1. If you are using a names database with names, continue to section 5.2. If
you are using EDO or DDO, continue to section 5.12.
5-2. When you are at the client and trying to connect, you normally supply a
connect string (such as PROD or PROD.WORLD). Log on to the server where the
nameserver resides and start the Names Control Utility by typing in 'namesctl'
at the command prompt (use 'namesctl80' for Names v8.x on WinNT).
At the NAMESCTL command prompt, you need to run the 'query' command to see
what TNS address is associated with the connect information you are providing.
The syntax for the 'query' command is:
NAMESCTL> query <alias> a.smd
For example:
NAMESCTL> query PROD a.smd
What you should get back is address information that may look like this:
Total response time: 0 seconds
Response status: normal, successful completion
Authoritative answer: yes
Number of answers: 1
TTL: 1 day
Answers:
data type is "a.smd"
Syntax is ADDR:
...(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=otcsco1)
(PORT=1521)))(CONNECT_DATA=(SID=V732)))
Look at the address shown on your screen and make sure the host and port number
are correct for what you are trying to connect to. If they are not, this is
the cause of the 12203 error and you will need to use the Network Manager or
the Net8 Assistant to repopulate the nameserver's region database with correct
connection information.
5.3. Now that you know what host and port the client is going to try to
contact, you need to make sure the client can physically use these addresses.
5.4. Go to, or log on to, the server where Oracle resides.
If you have an NT server, go to section 5.5.
If you have a Unix server, go to section 5.6.
5.5. Open a DOS window and type 'ipconfig' at the operating system prompt.
Your DOS window will show information similar to the following:
+-----------------------------------------------------------------------+
| Command Prompt [_][-][X] |
+-----------------------------------------------------------------------+
| Microsoft(R) Windows NT(TM) |
| (C) Copyright 1985-1996 Microsoft Corp. |
| |
| C:\>ipconfig |
| |
| Windows NT IP Configuration |
| |
| Ethernet adapter El90x1: |
| |
| IP Address. . . . . . . . . : 138.2.121.221 |
| Subnet Mask . . . . . . . . : 255.255.255.0 |
| Default Gateway . . . . . . : 138.2.121.1 |
| |
| C:\> _ |
+-----------------------------------------------------------------------+
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 5.7.
5.6. At the operating system prompt, type '/usr/sbin/ifconfig -a'. This
command will display the actual IP address of the server.
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 5.7.
Note: the ifconfig command may be different of different Unix platforms.
A 'man' on ifconfig will inform you of the correct switches to use in
order to display the IP address.
5.7. Open your listener.ora file in a text editor. If you do not have a
LISTENER.ORA, skip to section 6. Replace the HOST name with the IP address in
your TCP address block.
If you are already using the IP address, and it matches the IP address you
found earlier, go to section 5.8. If not, make the change and save the
LISTENER.ORA file. Continue to section 5.8.
5.8. At the command prompt, restart the listener by typing 'lsnrctl stop' (if
you are using Windows NT running Oracle8, type 'lsnrctl80 stop') followed
by command 'lsnrctl start' (if you are on Windows NT running Oracle 8, type
'lsnrctl80 start').
5.9. On the client, go to the command prompt and type 'ping <IP address>'
using the IP address of server you found earlier in this section. For
example:
> ping 138.2.158.34
You should get some response back stating that the ping was successful or
that the server is alive. If this is the case, continue to section 5.11. If
you get any errors, or messages stating that there was a timeout problem, or
that the host was unreachable, or the IP address was bad, contact your
network administrator and find out why you cannot ping the server. This is
cause of your TNS-12203 error.
5.10 Try to ping the nameserver from the client. The IP address you use to ping
should be the same in your client's SQLNET.ORA or SDNS.ORA.
5.11 If you have gone through all of the above steps and the connection still
fails with the TNS-12203, it is possible that a firewall exists between the
client and the server. Please read section 4 on Firewalls.
5.12 With EDO or DDO, the Listener is registering with the nameserver.
It is likely the Listener is registering bad or wrong information to the
nameserver.
Go to the LISTENER.ORA file on one of the servers.
For DDO, ensure the following exists in the LISTENER.ORA file:
use_plug_and_play_listener=on
global_dbname
a TCP address block in the LISTENER.ORA
It is best if the TCP address block actually uses the IP address for the HOST
parameter. You will also want to make sure that the server can ping the host
'oranamsrvr0'.
For EDO, you want to make sure the following exists in the LISTENER.ORA file:
global_dbname
a TCP address block in the LISTENER.ORA
names.preferred_server
It is best if the TCP address block actually uses the IP address for the HOST
parameter. Esure the names.preferred_server parameter is pointing to the correct
name server. If you are in doubt, use the IP address in the names.preferred_server
address block.
The names.preferred_server parameter can be omitted if you have an SDNS.ORA (Windows)
or .sdns.ora (Unix) file on the server where the Oracle Listener resides. This file
resides in the ORACLE_HOME/network/names directory and contains a list of known
nameservers. Ensure the IP addresses or hostnames in the file are correct.
6
===============================================================================
Section 6.
Go to the section that describes your problem:
6.1: You created the TNSNAMES.ORA file by hand and are unsure of syntax.
6.2: You are missing your TNSNAMES.ORA file.
6.3: You are missing your LISTENER.ORA file.
6.1: This section depicts 3 examples of a TNSNAMES.ORA file entry. The first
two have been created incorrectly.
#---INCORRECT EXAMPLE 1---#
DEV1.WORLD =
<TAB><TAB>(DESCRIPTION =
<TAB><TAB><TAB>(ADDRESS_LIST =
<TAB><TAB><TAB><TAB>(ADDRESS =
<TAB><TAB><TAB><TAB>(COMMUNITY = SAMPLE_COMMUNITY)
<TAB><TAB><TAB><TAB>(PROTOCOL = TCP)
<TAB><TAB><TAB><TAB>(HOST = <SERVER>)
<TAB><TAB><TAB><TAB>(PORT = 1521)
<TAB><TAB><TAB>)
<TAB><TAB>)
<TAB><TAB><TAB>(CONNECT_DATA =
<TAB><TAB><TAB>(SID = <SID>)
<TAB><TAB>)
<TAB>)
#---INCORRECT EXAMPLE 2---#
DEV1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAMPLE_COMMUNITY)
(PROTOCOL = TCP)
(HOST = <SERVER>)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = <SID>)
)
)
#---CORRECT EXAMPLE---#
DEV1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAMPLE_COMMUNITY)
(PROTOCOL = TCP)
(HOST = <SERVER>)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = <SID>)
)
)
6.2: Use Notepad or vi to create a TNSNAMES.ORA file using the "correct
example" syntax in section 6-1. This file normally resides in the
ORACLE_HOME\network\admin directory.
6.3: You can create the LISTENER.ORA file by using the sample below:
listener=
(address_list=
(address=
(protocol=tcp)
(host=otcsol1) <-- the hostname/ip address of your server
(port=1521) <-- what port you want the Listener to listen on
)
)
startup_wait_time_listener=0
connect_timeout_listener=10
sid_list_listener=
(sid_list=
(sid_desc=
(sid_name = ORCL)
(oracle_home = /usr/product/oracle/7.3.4)
)
)
TNS-12203 error
---------------------------------------------------------------------------
Note: PLEASE READ
Because of ASCII diagrams, this document is best viewed with
Courier New or Lucida Console font. This is normally the default.
In Internet Explorer:
Set the Plain Text Font to Courier New or Lucida Console
In Netscape:
Set the Fixed Width Font to Courier New or Lucida Console 10pt.
Click "Use my default fonts, overriding document-specified fonts"
---------------------------------------------------------------------------
Contents
1............................................TNS-12203 from Windows 3.x
2............................................TNS-12203 from Windows 95/98/NT
3............................................TNS-12203 from Unix
4............................................Firewalls
5............................................TNS-12203 when Using Oracle Names
6............................................Configuration Files Examples
1
=============================================================================
=============================================================================
Windows 3.x
When troubleshooting items for resolving ORA/TNS-12203 errors on Windows 3.1
or Windows 3.11, it is helpful to have a copy of the TNSNAMES.ORA file
located in front of you. This file is normally located in the
ORAWIN\NETWORK\ADMIN directory. If the file is not there, skip to section 6.
1.1. Go to, or log on to, the server where Oracle resides.
If you have a Windows NT server, go to section 1.2.
If you have a Unix server, go to section 1.3.
1.2. Open a DOS window and type 'ipconfig' at the operating system prompt.
Your DOS window will show information similar to the following:
+-----------------------------------------------------------------------+
| Command Prompt [_][-][X] |
+-----------------------------------------------------------------------+
| Microsoft(R) Windows NT(TM) |
| (C) Copyright 1985-1996 Microsoft Corp. |
| |
| C:\>ipconfig |
| |
| Windows NT IP Configuration |
| |
| Ethernet adapter El90x1: |
| |
| IP Address. . . . . . . . . : 138.2.121.221 |
| Subnet Mask . . . . . . . . : 255.255.255.0 |
| Default Gateway . . . . . . : 138.2.121.1 |
| |
| C:\> _ |
+-----------------------------------------------------------------------+
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 1.4.
1.3. At the operating system prompt, type '/usr/sbin/ifconfig -a'. The
command will display the actual IP address of the server.
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 1.4.
Note: The ifconfig command may be different of different Unix platforms.
A 'man' on ifconfig will inform you of the correct switches to use
in order to display the IP address.
1.4. Open your listener.ora file in a text editor. If you do not have a
LISTENER.ORA, skip to section 6.
Replace the hostname with the IP address in your TCP address block.
If you are already using the IP address, and it matches the IP address
you found earlier, go to section 1.6. If not, make the change and save
the LISTENER.ORA file. Continue to section 1.5.
1.5. At the command prompt, restart the listener by typing 'lsnrctl stop'
(if you using Windows NT running Oracle8 (8.0.x), type 'lsnrctl80 stop')
followed by the command 'lsnrctl start' (if you are on Windows NT
running Oracle8, type 'lsnrctl80 start').
1.6. On the client, go to the command prompt and type 'ping <IP address>'
using the IP address of server you found earlier in this section. For
example:
> ping 138.2.158.34
You should get some response back stating that the ping was successful or
that the server is alive. If this case, continue to section 1.7. If you
get any errors, or messages stating that there was a timeout problem, or
that the host was unreachable, or the IP address was bad, contact your
network administrator and find out why you cannot ping the server. This is
cause of your TNS-12203 error.
1.7. You have verified that you can physically contact the server and that the
Listener is running. The next thing to do is verify the correctness of the
TNSNAMES.ORA file. Open the LISTENER.ORA file in a text editor. Make sure
the HOST parameter for the alias you are trying to connect with is set to
the IP address of the server - rather than using the hostname. You also
need to make sure the value of the PORT parameter in the TNSNAMES.ORA file
matches the value of the PORT parameter in the LISTENER.ORA file. After
you verify these two items, try a connection. If the connection
still fails, continue to the next section.
1.8. Go to the Windows operating system directory and open the WIN.INI file.
If you search for the word 'Oracle', you should find an Oracle section that
looks like this:
[Oracle]
ORA_CONFIG=c:\windows\oracle.ini
Although the path to the ORACLE.INI file may be different, if you do not
have this section, deinstall all SQL*Net products and reinstall them.
Go to the ORACLE.INI file specified by the path from the ORA_CONFIG section
and open the file in a text editor.
There are only 2 parameters in this file that can yield an ORA-12203 error.
They are ORACLE_HOME and TCP_VENDOR.
ORACLE_HOME should be set to the location of your current installation of
SQLNet. TCP_VENDOR should be set to a value that will load the DLLs of your
protocol stack. For example, if TCP_VENDOR=WINSOCK, you should have
MWINSOCK.DLL and MSOCKLIB.DLL in the ORAWIN\bin directory.
You van verify the correctness of TCP_VENDOR parameter by searching for the
value of TCP_VENDOR (i.e winsock, novlwp) in the VSL.INI file. The 2 DLLs
referenced in the VSL.INI file should be in your ORAWIN\bin directory.
1.9. Make sure the VSL.INI file is present in the WINDOWS home directory.
1.10 If the connection still fails, go to the ORAWIN\orainst directory and open
the WINDOWS.RGS file. Make sure the TCP protocol adapter is installed
by finding the line that looks something like this (note version numbers
may be different):
72 wintcp20 winnet20 "tcp23" "2.3.3.0.0" "Oracle TCP/IP Adapter"
If you do not have this, reinstall SQL*Net and make sure you install the
Oracle TCP Protocol Adapter.
1.11 Go back to the ORACLE.INI file and make sure you remove any SQLNET_DBNAME
parameters.
1.12 If you are running SQL*Net for Windows on Windows95/98/NT, make sure your
WINSOCK.DLL is in the correct location and of the correct size using the
chart:
Filename OS Location Size
------------+-------+---------------------------+--------------------
winsock.dll Win98 \windows 21k (21,504 bytes)
winsock.dll Win95 \windows 42k (42,080 bytes)
winsock.dll WinNT \winnt\system32 3k (2,880 bytes)
2
=============================================================================
=============================================================================
Windows95 Windows98 Windows NT:
When troubleshooting ORA/TNS-12203 errors on Windows 95, 98, or NT, it will be
helpful to have a copy of the TNSNAMES.ORA file located in front of you. This
file is normally located in the ORACLE_HOME\NETWORK\ADMIN directory (or NET80\ADMIN).
If the file is not there, go to section 6.
2.1. Go to, or log on to, the server where Oracle resides.
If you have a Windows NT server, go to section 2.2.
If you have a Unix server, go to section 2.3.
2.2. Open a DOS window and type 'ipconfig' at the operating system prompt.
Your DOS window will show information similar to the following:
+-----------------------------------------------------------------------+
| Command Prompt [_][-][X] |
+-----------------------------------------------------------------------+
| Microsoft(R) Windows NT(TM) |
| (C) Copyright 1985-1996 Microsoft Corp. |
| |
| C:\>ipconfig |
| |
| Windows NT IP Configuration |
| |
| Ethernet adapter El90x1: |
| |
| IP Address. . . . . . . . . : 138.2.121.221 |
| Subnet Mask . . . . . . . . : 255.255.255.0 |
| Default Gateway . . . . . . : 138.2.121.1 |
| |
| C:\> _ |
+-----------------------------------------------------------------------+
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 2.4.
2.3. At the operating system prompt, type '/usr/sbin/ifconfig -a'. This
command will display the actual IP address of the server.
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 2.4.
Note: The ifconfig command may be different of different Unix platforms.
A 'man' on ifconfig will inform you of the correct switches to use
in order to display the IP address.
2.4. Open your listener.ora file in a text editor. If you do not have a
LISTENER.ORA, skip to section 6. Replace the hostname with the IP address
in your TCP address block.
If you are already using the IP address, and it matches the IP address you
found earlier, go to section 2.5. If not, make the change and save the
LISTENER.ORA file. Continue to section 2.5.
2.5. At the command prompt, restart the listener by typing 'lsnrctl stop' (if
you are using Windows NT running Oracle8, type 'lsnrctl80 stop') followed by
command 'lsnrctl start' (if you are on Windows NT running Oracle8,
type 'lsnrctl80 start').
2.6. On the client, go to the command prompt and type 'ping <IP address>'
using the IP address of server you found earlier in this section. For
example:
> ping 138.2.158.34
You should get some response back stating that the ping was successful or
that the server is alive. If this is the case, continue to section 2.7. If
you get any errors, or messages stating that there was a timeout problem, or
that the host was unreachable, or the IP address was bad, contact your
network administrator and find out why you can't ping the server. This is
cause of your TNS-12203 error.
2.7. You have verified that you can physically contact the server and that the
Listener is running. The next thing to do is verify the correctness of the
TNSNAMES.ORA file. Locate the TNSNAMES.ORA file. This file is usually in the
ORACLE_HOME\network\admin (or net80\admin) directory. Open the file in a text
editor. Make sure the HOST parameter for the alias you are trying to connect
with is set to the IP address of the server - rather than using the host name.
You also need to make sure the value of the PORT parameter in the TNSNAMES.ORA
file matches the value of the PORT parameter in the LISTENER.ORA file. After
you verify these two items, try a connection. If the connection still fails,
continue to section 2.8.
2.8. Often, users unknowingly use an application designed for SQLNet 2.3, but only
have Net8 installed or vice versa. For example, if you are executing PLUS33W.EXE,
then make sure the TNSNAMES.ORA file is in the network\admin directory. If you
are using PLUS80W.EXE, make sure the TNSNAMES.ORA file is in the net80\admin
directory. If you have a 3rd-party application and are unsure which directory
the configuration files go in, place the TNSNAMES.ORA file and SQLNET.ORA file
in both the \network\admin and net80\admin directories.
2.9. We need to verify the file size of your WSOCK32.DLL. This DLL is the 32-bit
Winsock driver for the operating system. Use the table below to determine
if you are using the correct file:
Filename OS Location Size
------------+-------+---------------------------+--------------------
wsock32.dll Win98 \windows\system 40k (40,960 bytes)
wsock32.dll Win95 \windows 65k (66,560 bytes)
wsock32.dll WinNT \winnt\system32 20k (20,240 bytes)
If your wsock32.dll does not match the appropriate file as listed above,
then reinstall your operating system's TCP/IP stack. If your DLLs are okay,
continue to section 2.10.
2.10 There may be a syntactical problem with the configuration files.
Please check the syntax as described in section 6.
2.11 Other causes of ORA-12203 include the unintentionally Installation of Oracle
Parallel Server Option as discussed in <Note:67259.1>.
3
=============================================================================
=============================================================================
TNS-12203 from Unix:
The most common cause of getting a TNS-12203 from a Unix client is a linking
problem. Rather than finding out if an executable is linked with the SQL*Net
TCP/IP Protocol Adapter, it is usually just easier to manually relink.
If relinking is not an issue, then we will continue the troubleshooting from
the point of view of a TCP/IP problem.
3-1. At the operating system prompt, type:
$ which make
You should be using '/usr/ccs/bin/make'. If not, prepend the directory
'/usr/ccs/bin' to PATH eg.
$ PATH=/usr/ccs/bin
PATH;export PATH
Next, go to the directory where the make file (.mk) for the executable
is being used. For example, if you are using SQL*Plus, go the
$ORACLE_HOME/sqlplus/lib directory. If you are using a 3rd party executable
find the directory where its make file is located. At the operating system
prompt relink the executable. For example, if you are using SQLPlus,
type the following command:
$ make -f ins_sqlplus.mk install
After the executable has recompiled, try the connection again. If the
connection still fails, please continue to the next section.
3.2. Go to, or log on to, the server where Oracle resides.
If you have a Windows NT server, go to section 3.3.
If you have a Unix server, go to section 3.4.
3.3. Open a DOS window and type 'ipconfig' at the operating system prompt.
Your DOS window will show information similar to the following:
+-----------------------------------------------------------------------+
| Command Prompt [_][-][X] |
+-----------------------------------------------------------------------+
| Microsoft(R) Windows NT(TM) |
| (C) Copyright 1985-1996 Microsoft Corp. |
| |
| C:\>ipconfig |
| |
| Windows NT IP Configuration |
| |
| Ethernet adapter El90x1: |
| |
| IP Address. . . . . . . . . : 138.2.121.221 |
| Subnet Mask . . . . . . . . : 255.255.255.0 |
| Default Gateway . . . . . . : 138.2.121.1 |
| |
| C:\> _ |
+-----------------------------------------------------------------------+
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 3.5.
3.4. At the operating system prompt, type '/usr/sbin/ifconfig -a'. This
command will display the actual IP address of the server. You will be
referring to this IP address throughout the paper so you may want to
jot it down. Continue to section 3.5.
Note: The ifconfig command may be different of different Unix platforms.
A 'man' on ifconfig will inform you of the correct switches to use
in order to display the IP address.
3-5. Open the LISTENER.ORA file in a text editor. If you do not have a
LISTENER.ORA, skip to section 6. Replace the hostname with the IP
address in your TCP address block.
If you are already using the IP address, and it matches the IP address you
found earlier, go to section 3.6. If not, make the change and save the
LISTENER.ORA file. Continue to section 3.6.
3.6. At the command prompt, restart the Listener by typing 'lsnrctl stop' (if
you are on NT running Oracle 8, type 'lsnrctl80 stop') followed by the command
'lsnrctl start' (if you are on NT running Oracle 8, type 'lsnrctl80 start').
3.7. On the client, go to the command prompt and type 'ping <IP address>'
using the IP address of server you found earlier in this section. For
example:
> ping 138.2.158.34
You should get some response back stating that the ping was successful or
that the server is alive. If this is the case, continue to section 3.8. If
you get any errors, or messages stating that there was a timeout problem, or
that the host was unreachable, or the IP address was bad, contact your
network administrator and find out why you cannot ping the server. This is
cause of your TNS-12203 error.
3.8. You have verified that you can physically contact the server and that the
Listener is running. The next thing to do is verify the correctness of the
TNSNAMES.ORA file. Locate the TNSNAMES.ORA file. This file is usually in the
$ORACLE_HOME/network/admin directory. Open the file in vi. Ensure the
HOST parameter for the alias you are trying to connect with is set to the IP
address of the server - rather than using the host name. You also need to
make sure the value of the PORT parameter in the TNSNAMES.ORA file matches the
value of the PORT parameter in the listener.ora file. Also set the TNS_ADMIN
environment variable to point to the directory the LISTENER.ORA file is
located. For more information on TNS_ADMIN see <NOTE:111942.1> "Setting
the TNS_ADMIN environment variable". After you have verified these items,
try a connection.
4
=============================================================================
=============================================================================
Firewalls:
Firewalls, Windows NT, and Redirections
On Windows NT, when a connect request comes in to the Listener, the Listener
spawns and Oracle thread. This thread is a listening thread, and is started
on a wild-card address meaning that the thread is listening for connections
on the current I.P. address, and an unused port number given to the thread by
the networking software. The Oracle thread will contact the Listener using
IPC and inform the Listener of its listening address, connection load and other
status information. The Listener sends back to the client a REDIRECT
address. This tells the client to reconnect to the newly spawned Oracle thread.
Since this Oracle thread is on a random port (a range of ports cannot be
defined), the firewall will not let the connection through. The resulting
error is usually a TNS-12203.
There are two ways to resolve this issue.
The first way is to use a firewall that has a SQL*Net proxy built into it. The
way this works is that the SQL*Net proxy starts another listening processes
(usually on port 1610). This causes the firewall to act as a Multi Protocol
Interchange. So, by using the TNSNAV.ORA file on the client, you connect to
port 1610 (the firewall). The firewall passes the connection to the server.
The server gives a redirect to the client. The client reconnects to the
firewall proxy on port 1610, and the firewall passes the connection to the
Oracle thread on the wild-card listening address. Here is what the connection
flow would look like:
1. Connect to proxy and pass connection to Listener
2. Send redirect to client
3. Connect to redirected address via the proxy
4. Oracle accepts the connection
Firewall
||
+------+ <--------2--------||-------2------ +---------+
|client| || |listener |(port=1521)
+------+ --------1------> proxy ----1------> +---------+
A \ /||\
| \---------3-------/ || \-----3------> +---------+
| || | oracle |(port=xxxx)
+--------------4---------||-------4------- +---------+
The second way to resolve this issue is to upgrade the server to 8.0.x and use
the USE_SHARED_SOCKET parameter in the Windows NT Registry. With this method, it
does not matter what kind of firewall you have.
The syntax for this parameter is: USE_SHARED_SOCKET = TRUE
Place the parameter in the registry under HKEY_LOCAL_MACHINE\Software\Oracle
Restart Oracle and the Listener for the parameter to take effect.
Here's how USE_SHARED_SOCKET works. The Listener binds and creates a socket
on the address specified in the LISTENER.ORA file. On this socket, there is a
LISTEN state active that is used by the Listener. When a new connection comes
in to the Listener, the Listener spawns an Oracle thread on the listening port
(i.e. 1521). This happens over and over again so that you have a Listener and
several established connections using port 1521. Pictorially, the scenario looks
like this:
+---<O>--------<O>----<O>---<O>--+
| |
| This square represents <O>
| a listening socket for |
| port 1521. |
<O> |
| <O> = oracle thread <O>
| <L> = listener |
| |
+-<O>-------<L>--<O>------<O>----+
The operating system then does a poll() or a select() on the socket to test for
any data. If any of the threads have data, a signal handler is used to contact
the application and inform it of the new data.
The disadvantage of USE_SHARED_SOCKET is that if the Listener shuts down, all
connections are dissolved (ie. terminated).
Finally, a very common question concerning the Listener and port numbers is why
different port numbers show up in the LISTENER.LOG file. What you are seeing
is the client's source port and client's source IP address. Here's how this
relates to your firewall.
If you want to make a TCP connection to a server (let's say with TELNET), you need
to create a socket. To create a socket, you need 4 pieces of information: a
source IP and port, and a destination IP and port. So using TELNET as an
example (the listening port for the TELNET process is 23 on the server):
source destination
source destination
+-----------+---------------+
IP |138.2.12.8 |185.45.67.53 |
+-----------+---------------+
port | xx | 23 |
+-----------+---------------+
Notice the source port is labeled as 'xx'. The networking software on the client chooses
at random, or in sequential order, a valid port (between 1024 and 65535) so the client
can send and receive data. This is what you are seeing in the LISTENER.LOG file.
Will this be a problem with the firewall?
No - the firewall will restrict incoming connections, but will freely let any
connection on any port out (which is okay). Here is what it might look like:
Firewall
<-------------||---------\
<-------------||---------\\
[CLIENT]----------------->|| \---[SERVER]
<-------------||---------//
<-------------||---------/
5
=============================================================================
=============================================================================
Oracle Names
When using Oracle Names, a TNS-12203 would normally be caused by bad information
being loaded from the region data, or incorrect registration when using Dynamic
Discovery Option/Enhanced Discovery Option (DDO/EDO).
Almost any other reason would be caused by a problem that can be fixed using
information from the other sections of this bulletin. Another reason would be
if the client were physically not able to contact the nameserver.
5.1. If you are using a names database with names, continue to section 5.2. If
you are using EDO or DDO, continue to section 5.12.
5-2. When you are at the client and trying to connect, you normally supply a
connect string (such as PROD or PROD.WORLD). Log on to the server where the
nameserver resides and start the Names Control Utility by typing in 'namesctl'
at the command prompt (use 'namesctl80' for Names v8.x on WinNT).
At the NAMESCTL command prompt, you need to run the 'query' command to see
what TNS address is associated with the connect information you are providing.
The syntax for the 'query' command is:
NAMESCTL> query <alias> a.smd
For example:
NAMESCTL> query PROD a.smd
What you should get back is address information that may look like this:
Total response time: 0 seconds
Response status: normal, successful completion
Authoritative answer: yes
Number of answers: 1
TTL: 1 day
Answers:
data type is "a.smd"
Syntax is ADDR:
...(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=otcsco1)
(PORT=1521)))(CONNECT_DATA=(SID=V732)))
Look at the address shown on your screen and make sure the host and port number
are correct for what you are trying to connect to. If they are not, this is
the cause of the 12203 error and you will need to use the Network Manager or
the Net8 Assistant to repopulate the nameserver's region database with correct
connection information.
5.3. Now that you know what host and port the client is going to try to
contact, you need to make sure the client can physically use these addresses.
5.4. Go to, or log on to, the server where Oracle resides.
If you have an NT server, go to section 5.5.
If you have a Unix server, go to section 5.6.
5.5. Open a DOS window and type 'ipconfig' at the operating system prompt.
Your DOS window will show information similar to the following:
+-----------------------------------------------------------------------+
| Command Prompt [_][-][X] |
+-----------------------------------------------------------------------+
| Microsoft(R) Windows NT(TM) |
| (C) Copyright 1985-1996 Microsoft Corp. |
| |
| C:\>ipconfig |
| |
| Windows NT IP Configuration |
| |
| Ethernet adapter El90x1: |
| |
| IP Address. . . . . . . . . : 138.2.121.221 |
| Subnet Mask . . . . . . . . : 255.255.255.0 |
| Default Gateway . . . . . . : 138.2.121.1 |
| |
| C:\> _ |
+-----------------------------------------------------------------------+
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 5.7.
5.6. At the operating system prompt, type '/usr/sbin/ifconfig -a'. This
command will display the actual IP address of the server.
You will be referring to this IP address throughout the paper so you
may want to jot it down. Continue to section 5.7.
Note: the ifconfig command may be different of different Unix platforms.
A 'man' on ifconfig will inform you of the correct switches to use in
order to display the IP address.
5.7. Open your listener.ora file in a text editor. If you do not have a
LISTENER.ORA, skip to section 6. Replace the HOST name with the IP address in
your TCP address block.
If you are already using the IP address, and it matches the IP address you
found earlier, go to section 5.8. If not, make the change and save the
LISTENER.ORA file. Continue to section 5.8.
5.8. At the command prompt, restart the listener by typing 'lsnrctl stop' (if
you are using Windows NT running Oracle8, type 'lsnrctl80 stop') followed
by command 'lsnrctl start' (if you are on Windows NT running Oracle 8, type
'lsnrctl80 start').
5.9. On the client, go to the command prompt and type 'ping <IP address>'
using the IP address of server you found earlier in this section. For
example:
> ping 138.2.158.34
You should get some response back stating that the ping was successful or
that the server is alive. If this is the case, continue to section 5.11. If
you get any errors, or messages stating that there was a timeout problem, or
that the host was unreachable, or the IP address was bad, contact your
network administrator and find out why you cannot ping the server. This is
cause of your TNS-12203 error.
5.10 Try to ping the nameserver from the client. The IP address you use to ping
should be the same in your client's SQLNET.ORA or SDNS.ORA.
5.11 If you have gone through all of the above steps and the connection still
fails with the TNS-12203, it is possible that a firewall exists between the
client and the server. Please read section 4 on Firewalls.
5.12 With EDO or DDO, the Listener is registering with the nameserver.
It is likely the Listener is registering bad or wrong information to the
nameserver.
Go to the LISTENER.ORA file on one of the servers.
For DDO, ensure the following exists in the LISTENER.ORA file:
use_plug_and_play_listener=on
global_dbname
a TCP address block in the LISTENER.ORA
It is best if the TCP address block actually uses the IP address for the HOST
parameter. You will also want to make sure that the server can ping the host
'oranamsrvr0'.
For EDO, you want to make sure the following exists in the LISTENER.ORA file:
global_dbname
a TCP address block in the LISTENER.ORA
names.preferred_server
It is best if the TCP address block actually uses the IP address for the HOST
parameter. Esure the names.preferred_server parameter is pointing to the correct
name server. If you are in doubt, use the IP address in the names.preferred_server
address block.
The names.preferred_server parameter can be omitted if you have an SDNS.ORA (Windows)
or .sdns.ora (Unix) file on the server where the Oracle Listener resides. This file
resides in the ORACLE_HOME/network/names directory and contains a list of known
nameservers. Ensure the IP addresses or hostnames in the file are correct.
6
===============================================================================
Section 6.
Go to the section that describes your problem:
6.1: You created the TNSNAMES.ORA file by hand and are unsure of syntax.
6.2: You are missing your TNSNAMES.ORA file.
6.3: You are missing your LISTENER.ORA file.
6.1: This section depicts 3 examples of a TNSNAMES.ORA file entry. The first
two have been created incorrectly.
#---INCORRECT EXAMPLE 1---#
DEV1.WORLD =
<TAB><TAB>(DESCRIPTION =
<TAB><TAB><TAB>(ADDRESS_LIST =
<TAB><TAB><TAB><TAB>(ADDRESS =
<TAB><TAB><TAB><TAB>(COMMUNITY = SAMPLE_COMMUNITY)
<TAB><TAB><TAB><TAB>(PROTOCOL = TCP)
<TAB><TAB><TAB><TAB>(HOST = <SERVER>)
<TAB><TAB><TAB><TAB>(PORT = 1521)
<TAB><TAB><TAB>)
<TAB><TAB>)
<TAB><TAB><TAB>(CONNECT_DATA =
<TAB><TAB><TAB>(SID = <SID>)
<TAB><TAB>)
<TAB>)
#---INCORRECT EXAMPLE 2---#
DEV1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAMPLE_COMMUNITY)
(PROTOCOL = TCP)
(HOST = <SERVER>)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = <SID>)
)
)
#---CORRECT EXAMPLE---#
DEV1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAMPLE_COMMUNITY)
(PROTOCOL = TCP)
(HOST = <SERVER>)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = <SID>)
)
)
6.2: Use Notepad or vi to create a TNSNAMES.ORA file using the "correct
example" syntax in section 6-1. This file normally resides in the
ORACLE_HOME\network\admin directory.
6.3: You can create the LISTENER.ORA file by using the sample below:
listener=
(address_list=
(address=
(protocol=tcp)
(host=otcsol1) <-- the hostname/ip address of your server
(port=1521) <-- what port you want the Listener to listen on
)
)
startup_wait_time_listener=0
connect_timeout_listener=10
sid_list_listener=
(sid_list=
(sid_desc=
(sid_name = ORCL)
(oracle_home = /usr/product/oracle/7.3.4)
)
)
扫一扫
636
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
