#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    2
    Rep Power
    0

    ORA-12543 TNS:destination host unreachable


    I am passing through a router where only the port 1521 is open. TNSPing and ping are working fine.

    here is my LISTENER.ORA:
    SAVE_CONFIG_ON_STOP_LISTENER = TRUE
    STARTUP_WAIT_TIME_LISTENER = 0
    LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521)))
    CONNECT_TIMEOUT_LISTENER = 10
    #note I have increased timeout with no success

    here is my TNSNAMES.ORA
    CONTRG =(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = y.y.y.y)(PORT = 1521)))
    (CONNECT_DATA = (SID = TEST)))

    Is there a .ORA file that must be configured to force the llistner to use only port 1521. (other than TNSNames.ORA and Lisner.ORA.) If so which one and how exactly is it done?

    I ran a sniffer to see what was getting through the router. I noticed that the client on x.x.x.x trys to use a soccet 2771. (This soccet changes randomely at each attempt. I am only opened at 1521) Note that ICMP is opened for all cases. The following results are observed.

    SOURCE DESTINATION PROT PORT
    X.X.X.X Y.Y.Y.Y TCP 2771 > 1521 [SYN]
    Y.Y.Y.Y X.X.X.X TCP 1521 > 2771[SYN,ACK]
    X.X.X.X Y.Y.Y.Y TCP 2771 > 1521 [ACK]
    X.X.X.X Y.Y.Y.Y TNS Request connect (1)
    Y.Y.Y.Y X.X.X.X TCP 1521 > 2771 [ACK]
    Y.Y.Y.Y X.X.X.X TNS Response redirect (5)
    Y.Y.Y.Y X.X.X.X TCP 1521 > 2771 [FIN,ACK]
    X.X.X.X Y.Y.Y.Y TCP 2771 > 1521 [ACK]
    X.X.X.X Y.Y.Y.Y TCP 2771 > 1521 [FIN,ACK]
    X.X.X.X Y.Y.Y.Y TCP 2772 > 1229 [SYN]
    Y.Y.Y.Y X.X.X.X TCP 1521 > 2771 [SYN]
    X.X.X.1 X.X.X.X ICMP Destination unreachable
    X.X.X.X Y.Y.Y.Y TCP 2772 > 1229 [SYN]
    X.X.X.X Y.Y.Y.Y TCP 2772 > 1229 [SYN]
    X.X.X.1 X.X.X.X ICMP Destination unreachable

    Thanks in advance
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    2
    Rep Power
    0

    Cool Solution to firewall/router


    Oracle Connections and Firewalls When the Oracle client makes a connection to the database e.g. (sqlplus userid/password@alias), it compares the alias name supplied in the sqlplus line and looks for a match in the TNSNAMES.ORA file or Names server. Once it obtains the address for the database server, a connection attempt is made to the server from the client. The Listener is contacted on the database server and port redirection can take place depending on the platform, configuration of the INIT<SID>.ORA file and/or the Oracle product being used. The underlying network layer on the server will obtain a free port from the Operating System (OS) and send back to the client via the Listener the new port assignment. The client will then try to connect to the database on a new port. This is where connection failure normally occurs. A remote Oracle client making a connection to an Oracle database can fail if there is a firewall installed between the client and the server and if port redirection is taking place. The firewall will block the connection to the new port when the Oracle client connects to the database - the client typically fails with Oracle errors ORA-12203 or ORA-12535. The client connection failure is due to port redirection from the Database Server's operating system. Port redirection requires the client to connect to the database using a different port than originally configured in the configuration files. Oracle Multi-Threaded Server (MTS) on Unix platforms, (without specifying the address with the ports in the INIT<SID>.ORA file), Oracle Secure Sockets Layer (SSL) and Windows NT/2000 platforms will cause port redirection. A Net8 level 16 client trace file can verify if the problem is a firewall issue. In the SQLNET.ORA file on the client add the following lines: trace_level_client = 16 trace_file_client = client trace_directory_client = a valid directory and path Save the changes to the SQLNET.ORA file and try connecting with SQL*Plus once to force the error. This will create a trace file. Here are several excerpts from a level 16-trace file of what to look for. The initial packets sent to the listener on port 1521 in trace file. niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=server1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=v815.world) (CID=(PROGRAM=D:\V815\BIN\SQLPLUSW.EXE)(HOST=server1)(USER=system)))) nladget: entry nladget: exit nscall: entry nscall: connecting... nsc2addr: entry nttbnd2addr: entry nttbnd2addr: port resolved to 1521 The received packet from the listener telling the client to use 1729 port (search for "NSPTRD"). nscon: recving a packet nsprecv: entry nsbal: entry nsbgetfl: entry nsbgetfl: normal exit nsmal: entry nsmal: 44 bytes at 0xb892d0 nsmal: normal exit nsbal: normal exit nsprecv: reading from transport... nttrd: entry nttrd: socket 232 had bytes read=64 nttrd: exit nsprecv: 64 bytes from transport nsprecv: tlen=64, plen=64, type=5 nsprecv: packet dump nsprecv:00 40 00 00 05 00 00 00 |.@......| nsprecv:00 36 28 41 44 44 52 45 |.6(ADDRE| nsprecv:53 53 3D 28 50 52 4F 54 |SS=(PROT| nsprecv:4F 43 4F 4C 3D 74 63 70 |OCOL=tcp| nsprecv:29 28 48 4F 53 54 3D 31 |)(HOST=1| nsprecv:33 38 2E 32 2E 32 31 33 |38.2.213| nsprecv:2E 36 31 29 28 50 4F 52 |.61)(POR| nsprecv:54 3D 31 37 32 39 29 29 |T=1729))| <- port change nsprecv: normal exit nscon: got NSPTRD packet nscon: got 54 bytes connect data nscon: exit (0) The client resolving the connection to port 1729. nscall: connecting... nsc2addr: entry nttbnd2addr: entry nttbnd2addr: port resolved to 1729 nttbnd2addr: using host IP address: 138.2.213.61 nttbnd2addr: exit nsc2addr: normal exit You can see the send packets sent from the client on port 1521 (or your port if different) to the Listener. There will be receive packets returned from the server to the client reflecting a new port assignment. Then the client will send packets again from the client only this time to a different port. The connection will then fail at this point in the trace file. The port that is assigned to the client is randomly chosen by the operating system and cannot be modified. It can be any free port available (usually above port 1024) that the server determines is not is use by any other software or hardware device. Once it is determined that the problem is the firewall causing the connection to fail, the next step is to select a solution to resolve the issue. As both Oracle and firewall are working correctly, there are several solutions to correct the problem to allow the clients to connect to the database.

    Solution: Firewall Vendor The first solution is to contact the firewall vendor and see if they have an upgrade to allow for Oracle connectivity with OS port redirection. If the firewall software can be upgraded, it is the best solution to follow.

    Solution: Oracle Connection Manager The second solution is available from Oracle Net8 (8.0.x and above), and requires the configuration of Oracle Connection Manager (CMAN) to allow the clients to connect through a firewall - [NOTE:2077721.6] discusses CMAN in detail including configuration examples). CMAN is an executable that allows clients to connect despite a firewall being in place between the client and server. CMAN is similar to the Listener i that it reads a CMAN.ORA configuration file, which contains an address that Oracle Connection Manager listens for incoming connections, usually default ports of 1610 or 1630. CMAN starts similar to the Listener and will enter a LISTEN state (see netstat). The Oracle client must be Net8 or above and have the following in the TNSNAMES.ORA file. cmantest = (description = (address_list = (address = <- first address is to CMAN (protocol=tcp) (host=hostname or ip of cman) (port=1610) ) (address= <- second address is to Listener (protocol=tcp) (host=hostname or ip of listener) (port=1521) ) ) (connect_data = (sid = sidname)) (source_route = yes) <- This tells the client that it is using ) CMAN and it must take the first two addresses listed. When the client contacts Oracle Connection Manager, CMAN completes the connection with the second address the client brought with it. The second address will point to the host machine where the listener is running. CMAN then uses that address to 'pass' the connection to the Listener and the connection to the database is established. All redirection takes place between CMAN and the database, therefore the Oracle Connection Manager installation should be placed after the firewall but before the database. Several documents exist in MetaLink (http://metalink.oracle.com) that explain Oracle Connection Manager and provide more detailed configurations. This note serves to explain the connection process with firewalls, not to go into detail regarding Oracle Connection Manager.

    Solution: USE_SHARED_SOCKET A third solution for Windows NT/2000 servers is to set USE_SHARED_SOCKET to TRUE in the Windows registry (see [NOTE:124140.1]). This allows the OS to share the listening port (e.g. 1521) and clients then remain using that same port when connecting to the database - no port redirected takes place. Unix platforms can have problems connecting to a database through firewalls if they have implemented Multi-Threaded Server (MTS). MTS Dispatchers will redirect connection ports like the Windows Platforms mentioned above.

    Solution: Setting MTS ports A workaround for this is to specify the port in the MTS parameters of the INIT.ORA file (see [NOTE:1016349.102]). This allows the Dispatcher to use the port specified and will not select a random port. Ensure the user-defined MTS port/s are opened on the firewall. The following example shows the ports set to 2450 and 3125. Set these parameters according to your individual systems. This solution will also work for Windows. Example: mts_dispatchers="(address=(protocol=tcp)(host=hostname)(port=2450))(dispatchers=1)" mts_dispatchers="(address=(protocol=tcp)(host=hostname)(port=3125))(dispatchers=1)" Opening ports 2450 and 3125 on the firewall will allow clients to connect to Oracle. SSL Using SSL will cause Port redirection. The workaround is to select and set the ports using MTS in the INIT<SID>.ORA .

IMN logo majestic logo threadwatch logo seochat tools logo