Improved remote session debugging (DBMS_DEBUG_JDWP)

“Improving the PL/SQL Debugger:

In prior releases, it was necessary to change the application to include calls to start and stop debugging. With this improvement, one database session can start debugging, and stop debugging a different session.”

“With these improvements, if a problem occurs in a long running test or in a production environment, it is possible to investigate the problem from another session.” Oracle Database 12c Release 2 (12.2) New Features

Let’s take a closer look :

TEST ENV : ORACLE 12.2.0.1/OEL6/SQL Developer  Version 4.1.5.21

Debugging a remote session the old way :

This is already well explained in this blog post Sqldeveloper remote debugger

“1. Developer – compiles a PL/SQL code in debug mode
2. Developer – starts remote listener debugger
3. Developer – sets breakpoint(s) in code
3. Client – establishes connection with remote listener debugger. This can be done as well inside code of a debugged code.
4. Client – starts code
5. Developer – waits and traps code executed by client. So developer will take over control on execution of code”

In previous release the debugged session (Client) has to call “DBMS_DEBUG_JDWP.CONNECT_TCP( ‘remote_listener_debugger_ip’, ‘port’ );” to establishes connection with remote listener debugger.This may need a change of the application code.

Debugging a remote session the new way :

Starting with oracle 12.2.0.1 the client does not need any more to call DBMS_DEBUG_JDWP.CONNECT_TCP( ‘remote_listener_debugger_ip’, ‘port’ ) to connect to the listener debugger.The debugger session (Developer/DBA) can enable debugging just by calling  DBMS_DEBUG_JDWP.CONNECT_TCP( ‘remote_listener_debugger_ip’, ‘port’ ,’session_sid’,’session_serial#’).Here is a quick overview :

1-Create procedure (debug mode) and put a break point on it

 

2-Start the listener debugger :

3- Check for debuggable session using :  v$plsql_debuggable_sessions

4-Establishes connection between the client and the remote listener debugger

5-That’s it you can now take control of the program when the client execute it !

 

That’s it 😀