How to automatically start STRACE or TRUSS with PL/SQL

Why to Start STRACE and TRUSS with PL/SQL

There are times when a STRACE or TRUSS tracing utilities would help diagnose an issue when the database interacts with the operating system. For example, either of these two tracing utilities is useful when issues exist with the UTL_FILE package encountering errors accessing files on the operating system. Starting either of these utilities is fairly simple with an interactive session. Usually, a second session is started to obtain the process id of the first session then the second session will invoke the trace.

If an automated process continually logs into the Database, does some work then disconnects, obtaining a trace is near impossible because a session would end before the required process id is obtained to invoke the trace. As a result, this sample code is a way to turn on the tracing from within its own process.

Requirements

The associated schema will require SELECT privileges against the Dictionary along with certain Java permissions. On the operating system will exist a shell script used to invoke the trace utility.

Configuration

Certain operating systems may have truss and others may have strace and some even have both. The parameters are essentially the same for the tracing being used here.

The important point is to determine which is going to be used (truss or strace) and file the exact location of the executable. To do so, use the “which” command.

From the OS:

$ which strace
/usr/bin/strace

Once the complete path and executable are obtained (/usr/bin/strace) it can be used within the shell script. Create a file named myshell.sh at the OS prompt. The “cat myshell.sh” command is used to show the content of the file used in this example. For this example, the shell script (myshell.sh) is kept in the /tmp directory. If kept in any other directory then the reference needs to change in the commands used when granting Java privileges within the database.

$ cat myshell.sh
#!/bin/csh -f
/usr/bin/strace -o /tmp/$1 -p $2 >& /dev/null &

Don’t forget to change permissions on this shell script in order for it to be executed.

$ chmod 755 myshell.sh

Connect to the database as SYSTEM or SYSDBA to create the test user and grant the required privileges. For this example, a new user named TESTCASE is created. Notice the directory references for strace and myshell.sh in the following commands as they will need to be changed to reference the proper location on the machine being used.

SQL> create user testcase identified by tiger;
SQL> grant connect, resource to testcase;
SQL> execute dbms_java.grant_permission( 'TESTCASE', 'SYS:java.io.FilePermission', '/usr/bin/strace', 'execute' );
SQL> execute dbms_java.grant_permission( 'TESTCASE', 'SYS:java.io.FilePermission', '/tmp/myshell.sh', 'execute' );
SQL> execute dbms_java.grant_permission( 'TESTCASE', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '' );
SQL> execute dbms_java.grant_permission( 'TESTCASE', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '' );
SQL> GRANT CREATE ANY DIRECTORY TO TESTCASE;
SQL> GRANT SELECT ANY DICTIONARY TO TESTCASE;
SQL> GRANT CREATE ANY PROCEDURE TO TESTCASE;

How to use

Once the above steps are completed, connect to the database as the new user (TESTCASE) to build the Java Stored Procedure. This procedure will execute the shell script on the operating system to invoke the trace utility.

Connect as the database TESTCASE to build the following objects. The first object is the Java Stored Procedure which when executed simply turns around and executes the operating system command passed in by the calling procedure which in this case is the shell script created above.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand2" AS
import java.lang.Runtime;
import java.lang.Process;
import java.io.*;
public class OSCommand2{
public static int Run2(String Command){
   try{
   Process p = Runtime.getRuntime().exec(Command);
   p.waitFor();
   System.out.println("Process exit code is: " + p.exitValue());
   System.out.println("Done!");
   return (p.exitValue());
   }
   catch (Exception e){
      System.out.println("Error running command: " + Command + "\n" + e.getMessage());
      return (-1);
    }
  }
}
/

This is the PL/SQL wrapper used to call the above Java Stored Procedure.

CREATE OR REPLACE FUNCTION host_command2 (p_command IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'OSCommand2.Run2 (java.lang.String) return int';
/

The following stored procedure is what actually starts the trace. When executed it does a SELECT from DUAL using SYSDATE and the SSSSS format to obtain the seconds past midnight. This value will be used for the name of the resulting trace file name produced. This was done so that if numerous traces were produced they could easily be sorted to locate the most recent output. Of course, that logic can be changed to use the process id of the database session as well or any other method. The key is to avoid duplicate filenames.

Then next SELECT is done to obtain the process id of the current session. The schema name (TESTCASE) is hard-code. If using a different schema then this value needs to change. The code can be altered to use the USER function to be more dynamic.

Once the two SELECTs are execute, the following is passed to the HOST_COMMAND2 function which is the PL/SQL wrapper to the Java Stored Procedure:

/tmp/myshell.sh DB' || v1 || '.out '|| v2

It passes the entire directory reference and shell script name (/tmp/myshell.sh) and concatenates ‘DB’ with the seconds and appends ‘.out’. This will produce a trace file that looks something like “DB45914.out” . The v2 is the variable holding the process id which is the last parameter.

CREATE OR REPLACE PROCEDURE START_TRACE_HERE AS
v1 VARCHAR2(10);
v2 NUMBER;
v3 NUMBER;
BEGIN
   select to_char(sysdate,'SSSSS') into v1 from dual;
   select b.spid into v2 from v$session a, v$process b where a.username='TESTCASE' and a.paddr=b.addr;
   dbms_output.put_line ( '/tmp/myshell.sh DB' || v1 || '.out '|| v2 );
   V3 := host_command2 ('/tmp/myshell.sh DB' || v1 || '.out '|| v2 );
   DBMS_OUTPUT.PUT_LINE(V3);
END;
/

The following creates the Directory Alias used in by the procedure calling the UTL_FILE function.

CREATE OR REPLACE DIRECTORY UTL_TEST AS '/tmp';

The following stored procedure is used to simply to provide something to trace as it uses UTL_FILE to interact with the operating system.

CREATE OR REPLACE PROCEDURE UTL_FILE_TEST AS
   fHandle UTL_FILE.FILE_TYPE;
   vTextIn varchar2(25);
BEGIN
   fHandle := UTL_FILE.FOPEN('UTL_TEST','utlout9.txt','w');
   vTextIn := 'Hello World';
   UTL_FILE.PUT_LINE(fHandle,vTextIn);
   UTL_FILE.FCLOSE(fHandle);
END;
/

Sample Output

To see it all working, execute the following three calls. The first call (SET SERVEROUTPUT ON) turns on the output to the screen. This is not required when running in an automated process. The second call (START_TRACE_HERE) turns on the tracing. The third call (UTL_FILE_TEST) runs the stored procedure to execute a UTL_FILE call.

SQL> set serveroutput on
SQL> execute START_TRACE_HERE;
SQL> execute UTL_FILE_TEST;

If an automated process is being used, then the call to START_TRACE_HERE would need to be incorporated into the process in some manner. For example, if the UTL_FILE_TEST was the automated process, the call to start the trace could be placed within.

CREATE OR REPLACE PROCEDURE UTL_FILE_TEST AS
   fHandle UTL_FILE.FILE_TYPE;
   vTextIn varchar2(25);
BEGIN
   START_TRACE_HERE;   ---Add the call to start the trace.
   fHandle := UTL_FILE.FOPEN('UTL_TEST','utlout9.txt','w');
   vTextIn := 'Hello World';
   UTL_FILE.PUT_LINE(fHandle,vTextIn);
   UTL_FILE.FCLOSE(fHandle);
END;
/

Notice within the shell script that is used to call the trace that the output files are written to the /tmp directory. When the tracing is completed and executed a few times, there should be files in the /tmp directory that begin with DB followed by some number and have the extension ‘out’.

$ ls /tmp/DB*.out
DB459416.out
DB499416.out
DB556983.out
DB606123.out