Forum Stats

  • 3,824,915 Users
  • 2,260,439 Discussions
  • 7,896,345 Comments

Discussions

SQLDeveloper TNS:illegal ADDRESS parameters when start debugger

User_739AW
User_739AW Member Posts: 15 Green Ribbon
edited Jun 12, 2022 3:14PM in SQL Developer

I try to debug my procedure on oracle 19c inside docker container.

Before start debugger i was exposed port in docker 14000:4000 (1521 of course too) and configure in SQLDeveloper to use port range for debug 14000-14000 and prompt for host.

First i've got error:

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '0.0.0.0', '0' )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1

Fix it with script:

begin
    for x in(select '0.0.0.0' as ip from dual union all select 'localhost' as ip from dual union all select '127.0.0.1' from dual) loop
        dbms_network_acl_admin.append_host_ace(
            host=>x.ip,
            ace=> sys.xs$ace_type(privilege_list=>sys.xs$name_list('JDWP'),
            principal_name=> 'app',
            principal_type=>sys.xs_acl.ptype_db)
        );
    end loop;
end;

I've got this error and now i don't know what to do:

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '0.0.0.0', '0' )
ORA-30683: failure establishing connection to debugger
ORA-12533: TNS:illegal ADDRESS parameters
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1

N.B. : error message says that developer try to connect at port = 0 but if i change port in settings to different range, for example 14001-14001 then message will contain port 140001, I don't know why.

DBMS_DEBUG (not jdwp) works normally, but I want full functionality of debugger.

Comments

  • user9540031
    user9540031 Member Posts: 188 Silver Badge

    Hello,

    The first thing to understand when doing remote debug with SQL Developer and DBMS_DEBUG_JDWP, is that SQL Developer will create a local listener (using the specified TCP port, or one port from the configured range in Preferences), and that the database session to be run under control of the debugger will connect to that listener by calling dbms_debug_jdwp.connect_tcp. So the TCP connection here is from the database to SQL Developer, not the other way around.

    It helps to know the IP addresses of both endpoints:

    1/ The database session will connect to the IP address which it ordinarily sees as the IP address of the client; you may find that IP address by running the following query;

    select sys_context('USERENV', 'IP_ADDRESS') as client_ip_address from dual;

    And that's the IP address for which you need to create a host ACE by calling dbms_network_acl_admin.append_host_ace, as you did above (but for unrelated addresses).

    2/ Further, you may have to ensure that this connection from the DB inside your Docker container to SQL Developer on your local PC is not blocked by a local Firewall. If that happens, you may need the source address of the connection in order to configure thing properly—but you already know that IP address: it's the one used as the destination by TNS connections to your database (remember: client and server roles are just reversed here).

    Hope this helps...

    Regards,

  • user9540031
    user9540031 Member Posts: 188 Silver Badge
    edited Jun 13, 2022 7:47PM

    Oh, and the second argument of dbms_debug_jdwp.connect_tcp is of course not '0', it's the TCP port actually used by SQL Developer to create the aforementioned listener.

    Regards,

  • User_739AW
    User_739AW Member Posts: 15 Green Ribbon
    edited Jun 15, 2022 1:34PM

    Thanks for answers, but when i add my ip address which i get from call sys_context('USERENV', 'IP_ADDRESS') to host_ace nothing changed.

    Still when i try to start oracle sql developer debugger and enter 0.0.0.0 or my local ip address developer raise error:

    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.24.0.1', '0' )
    ORA-30683: failure establishing connection to debugger
    ORA-12533: TNS:illegal ADDRESS parameters
    ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
    ORA-06512: at line 1
    

    I see that port in error equal to zero, but seems developer raise this error when he can connect to this port and then something bad happen, because if I change port to 14001 in developer settings for debugger (only 14000 opened in my docker container) the error will change to

    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.24.0.1', '14001' )
    ORA-30683: failure establishing connection to debugger
    ORA-12541: TNS:no listener
    ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
    ORA-06512: at line 1
    

    Here also my tryout to call debugger from query window, from output i can only see that port 1521 oracle don't like and i know why )

    --ORA-30683: failure establishing connection to debugger ORA-12541: TNS:no listener
    CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '4000' )
    --ORA-30683: failure establishing connection to debugger ORA-12541: TNS:no listener
    CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '14000' )
    --ORA-30683: failure establishing connection to debugger ORA-12541: TNS:no listener
    CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.24.0.1', '4000' )
    --ORA-30683: failure establishing connection to debugger ORA-12537: TNS:connection closed
    CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.24.0.1', '1521' )
    

    Here my docker compose port forvarding config if it helps but it's kinda simple:

    services:
      db:
        ports:
          - 1521:1521
          - 14000:4000
    

    I work on mac os, no firewalls installed so only docker + sql developer in this chain.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,571 Employee

    skip all this and just fall back to dbms_debug

  • user9540031
    user9540031 Member Posts: 188 Silver Badge

    skip all this and just fall back to dbms_debug

    I second that.

    We'd like to help, but trying multiple combinations of IP addresses and ports—including values that the error message clearly labels as illegal (read: invalid)—is of little use if you don't understand what's going on.

    Meanwhile, you might try the following:

    1) Make sure you have set up a host ACE in the database enabling JDWP connections to 172.24.0.1—I guess you have done that, otherwise you'd still have that initial ORA-24247 exception.

    2) Start the remote debug listener on SQL Developer, and configure it to bind to 172.24.0.1:

    Right-click on the connection object, then Debug... --> Remote Debug... In the Listen for JPDA dialog, set the listener port to a port of your choosing, and set the local address to 172.24.0.1:

    Note that you may choose the listener port here, but not the local address: it should be 172.24.0.1, because that's what Docker assigned to your container's bridge network (if I'm guessing correctly).

    3) Make sure the debug listener is running: it must be listed in the Processes view (menu View --> Processes), e.g.:

    (In the above example I have a debug listener running, bound to port 4001 on the local address 192.168.56.1.)

    4) Finally, try to connect the to-be-debugged session to the debug listener by having it run the following call:

    dbms_debug_jdwp.connect_tcp('172.24.0.1', '4000');

    Make sure the arguments are exactly the same IP address and port as used in step 2, not something else.

    That might work—but I'm not making any promise here: I haven't tried it, it may not apply to your Docker compose setup, and in the end that may well be just yet-another fruitless naïve attempt...

    Failing that :

    • Could the Docker manuals (e.g. https://docs.docker.com/network/) help?
    • Look for solutions on Docker-related forums: at this stage, this is definitely a Docker networking/configuration how-to question—and there must be solutions for it, obviously—not really a SQL Developer question anymore.

    Good luck,

    Regards,


    P.S. Doesn't Mac OS come with a bundled local firewall, preconfigured with sane defaults in order to limit the attack surface out-of-the-box?

  • User_739AW
    User_739AW Member Posts: 15 Green Ribbon

    Thank you guys for trying to help.

    Okay, at this point i will try back to dbms_debug, but as i remember in my previous project there are some problems with it when i try to see oracle pl/sql and sql object type fileds (dbms_debug doesn't show them at all or doesn't support nested objects, can't remember and i know that all this types and packages should be compiled with debug flag).

    About last solution with start start remote debug: i can't start it on ip that i've got from sys_context('USERENV', 'IP_ADDRESS'). The immediate error is "The debugger is not able to listen for JPDA using specified parameter. Do you want to change the parameters?" and if click no - nothin happened (no processes in list). I only can start remote debug on localhost / 127.0.0.1 at this point, don't know why. I tried to enter different ports like 4000, 14000 and random free ports like 4500 but seems error connected with local address to listen = 172.24.0.1 in my case.

    About bundled firewall from mac os: it's kinda simple and it asks once to allow any connections for app and that's all, no special default rules for some ports and so on (like on windows). But i disabled it for short period of time and check that nothing changed.

    At this point i start to think that maybe my oracle in docker container has bad configuration, the image was created by devops in my company. I'm try to build image by myself if I found that dbms_debug isn't enough for my project and back to this question again.

    Thanks again.

  • user9540031
    user9540031 Member Posts: 188 Silver Badge

    Hi,

    SQL Developer refusing to bind to 172.24.0.1 meant that there wasn't a network interface with that IP address in the first place (so that was a naive attempt indeed).

    Specific details about networking in Docker Desktop for MacOS are documented in the following page: https://docs.docker.com/desktop/mac/networking/

    Have you tried with the host.docker.internal DNS name? (On the database side, you'd have to use that name both for creating the host ACE, and as the destination host of the JDWP connection; on SQL Developer's side, you would just specify the debug listener port, leaving the local address to blank.)

    Regards,

    User_739AW
  • User_739AW
    User_739AW Member Posts: 15 Green Ribbon

    host.docker.internal - that's it, thank you!

    Now it works like it should with any port (don't need to forward any of these port from docker image) and now I get how JDWP debugger works completely.