##############################################
### Option I ###############################
##############################################
Version de base de données : 11.2.0.4
- Créer le fichier shell :
Fichier shell : bd_truss.sh
#!/bin/sh
/usr/bin/nohup /usr/bin/truss -fldD -p $2 > /home/ora11204/$1 2>&1&
Cela va générer un fichier de trace qui sera placé dans le répertoire "/home/ora11204/" , Le nom du fichier est passé comme 1er paramètre , le 2ème paramètre correspond au "ID process".
- Demander à votre admin Unix de donner les privilèges SUID pour le truss, en cas l'owner de votre process à tracer ne soit pas le même.(Exemple : grid et home RDBMS installés avec des comptes séparés).
root@serveur:/root# ls -l /usr/bin/truss
-r-xr-xr-x 66 root bin 10052 Jul 6 2011 /usr/bin/truss
root@serveur:/root# chmod u+s /usr/bin/truss
root@serveur:/root# ls -l /usr/bin/truss
-r-sr-xr-x 66 root bin 10052 Jul 6 2011 /usr/bin/truss
- Créer un compte de test dans la base de données :
sqlplus / as sysdba
DROP USER EVTA CASCADE ;
CREATE USER EVTA IDENTIFIED BY EVTA ;
GRANT CONNECT, RESOURCE TO EVTA ;
- Octroyer les privilèges nécessaires
execute dbms_java.grant_permission( 'EVTA', 'SYS:java.io.FilePermission', '/usr/bin/truss', 'execute' );
execute dbms_java.grant_permission( 'EVTA', 'SYS:java.io.FilePermission', '/usr/bin/sh', 'execute' );
execute dbms_java.grant_permission( 'EVTA', 'SYS:java.io.FilePermission', '/home/ora11204/bd_truss.sh', 'execute' );
execute dbms_java.grant_permission( 'EVTA', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '' );
execute dbms_java.grant_permission( 'EVTA', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '' );
ALTER SESSION SET CURRENT_SCHEMA=EVTA ;
Note: Ces deux classes ont été obtenues du Site d'Oracle - Doc ID 109095.1
- Créer les classes Java
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "CaptureStream" AS
import java.util.*;
import java.io.*;
class CaptureStream implements Runnable {
private final InputStream is;
private final String type;
private final OutputStream redirect;
private boolean redirected = false;
CaptureStream(InputStream is, String type, OutputStream redirect)
{
this.is = is;
this.type = type + ">";
this.redirect = redirect;
}
CaptureStream(InputStream is, String type)
{
this(is, type, null);
}
CaptureStream(InputStream is)
{
this(is, " ", null);
}
public void run()
{
try {
PrintWriter pw = null;
if (redirect != null) {
pw = new PrintWriter(redirect);
redirected = true;
}
InputStreamReader isr = new InputStreamReader(is);
BufferedReader br = new BufferedReader(isr);
String line=null;
while ( (line = br.readLine()) != null) {
System.out.println(type + line);
if (redirected) {
pw.println(line);
}
}
if (redirected) {
pw.flush();
pw.close();
}
br.close();
isr.close();
} catch (IOException ioe) {
ioe.printStackTrace();
}
}
}
/
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ExecOSCmd" AS
import java.util.*;
import java.io.*;
public class ExecOSCmd {
public static void main (String args[])
{
int rc = 0;
if (args.length < 1) {
System.out.println("USAGE: java ExecOSCmd \'cmd\' ");
System.exit(1);
}
try {
String cmd = args[0];
FileOutputStream fos = null;
if (args.length == 2 ) {
fos = new FileOutputStream(args[1]);
}
Runtime rt = Runtime.getRuntime();
Process p = rt.exec(cmd);
CaptureStream err = new CaptureStream(p.getErrorStream(), "ERR");
Thread e = new Thread(err);
/* NOTE we do not join the error thread. If there was no
Error it may never return. We would wait forever for it to
return thus janging this process */
e.start();
CaptureStream out = new CaptureStream(p.getInputStream(),
"OUT", fos);
Thread o = new Thread(out);
o.start();
try {
rc = p.waitFor();
/* Handle exceptions for waitFor() */
} catch (InterruptedException intexc) {
System.out.println("Interrupted Exception on waitFor: " +
intexc.getMessage());
}
if (fos !=null) {
o.join(); // need to wait for the output to finish.
fos.flush();
fos.close();
}
System.out.println("ExitValue: " + rc);
} catch (Throwable t) {
System.out.println("ExitValue: " + rc);
t.printStackTrace();
}
}
}
/
CREATE OR REPLACE PROCEDURE executecmd (cmd VARCHAR2)
AS LANGUAGE JAVA NAME 'ExecOSCmd.main(java.lang.String[])';
/
En tant que sys :
SET LINESIZE 2000
SELECT OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = 'EVTA';
OBJECT_TYPE OBJECT_NAME
------------------- ---------------------------------------
JAVA CLASS CaptureStream
JAVA SOURCE CaptureStream
JAVA CLASS ExecOSCmd
JAVA SOURCE ExecOSCmd
PROCEDURE EXECUTECMD
SET LINESIZE 600
COL TYPE_NAME FORMAT A30
COL NAME FORMAT A30
COL ACTION FORMAT A30
SELECT * FROM DBA_JAVA_POLICY WHERE GRANTEE = 'EVTA';
KIND GRANTEE TYPE_SCHEMA TYPE_NAME NAME ACTION ENABLED SEQ
------ --------- ----------- ------------------------------ ------------------------------ ---------- -------- ---
GRANT EVTA SYS java.io.FilePermission /home/ora11204/bd_truss.sh execute ENABLED 193
GRANT EVTA SYS java.io.FilePermission /usr/bin/sh execute ENABLED 192
GRANT EVTA SYS java.io.FilePermission /usr/bin/truss execute ENABLED 191
GRANT EVTA SYS java.lang.RuntimePermission readFileDescriptor ENABLED 195
GRANT EVTA SYS java.lang.RuntimePermission writeFileDescriptor ENABLED 194
- Créer un trigger after logon
CREATE OR REPLACE TRIGGER SYS.TRIG_TRACE_JAVA
AFTER LOGON ON DATABASE
DISABLE
DECLARE
id1 VARCHAR2(30);
id2 NUMBER;
ls_username VARCHAR2(30);
ls_osuser VARCHAR2(30);
ls_program VARCHAR2(64);
ls_sid INTEGER;
ls_serial INTEGER;
ls_schemaname VARCHAR2(100);
ls_machine VARCHAR2(100);
ls_spid VARCHAR2(24);
v2 NUMBER;
result NUMBER;
CURSOR USAGERINFO IS
SELECT upper(A.username), A.osuser, upper(A.program), A.sid, A.serial#, A.schemaname, A.machine, B.SPID
FROM v$session A, v$process b
WHERE audsid = userenv('SESSIONID') and a.paddr=b.addr;
BEGIN
-- Obtenir les informations de l'usager en cours
OPEN USAGERINFO;
FETCH USAGERINFO
INTO ls_username, ls_osuser, ls_program, ls_sid, ls_serial, ls_schemaname, ls_machine, ls_spid;
CLOSE USAGERINFO;
if user in ('EVTA') then
select user, sys_context('USERENV','SID') into id1, id2 from dual;
execute immediate 'alter session set statistics_level=ALL';
execute immediate 'alter session set tracefile_identifier='''||replace(id1,'$','')||'_'||id2||'''';
execute immediate 'alter session set max_dump_file_size=40960000';
execute immediate 'alter session set EVENTS ''10046 trace name context forever, level 12''';
execute immediate 'BEGIN EVTA.executecmd('''||'/home/ora11204/bd_truss.sh trace_'||replace(id1,'$','')||'_'||id2||'_'||ls_spid||'.txt '||ls_spid||'''); END;' ;
end if;
END;
/
- Activer le trigger au moment dont vous aurez besoin
ALTER TRIGGER SYS.TRIG_TRACE_xxx ENABLE ;
Cela va activer deux types de traces à chaque connexion du compte "EVTA", un trace de bases de données et un trace OS , ces deux fichiers pourront vous aider à détecter certains problèmes.
