SQL TRACE ENABLE FOR BAD QUERY

. bdrsat_test.env

IN node2 :

ssh oracle@
-bash-4.1$ . oraenv
ORACLE_SID = [BDRSAT1] ? BDRSAT2
ORACLE_HOME = [/home/oracle] ? /oracle/dbbase/product/11.2.0.4/dbhome


SELECT INST_ID,SID,SERIAL#,OSUSER,username, machine, status,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS'),PROGRAM FROM GV$SESSION where username='DS_EMIWEBRS' and osuser='tj2ee02' order by 7;


SELECT INST_ID,SID,SERIAL#,OSUSER,username, machine, status,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS'),PROGRAM FROM GV$SESSION where username='DS_EMIWEBRS';

SELECT OWNER, TRIGGER_NAME, STATUS FROM DBA_TRIGGERS WHERE TRIGGER_NAME LIKE 'SET_TRACE_ON_LOGON%';

SQL> SELECT OWNER, TRIGGER_NAME, STATUS FROM DBA_TRIGGERS WHERE TRIGGER_NAME LIKE 'SET_TRACE_ON_LOGON%';

OWNER			       TRIGGER_NAME		      STATUS
------------------------------ ------------------------------ --------
SYS			       SET_TRACE_ON_LOGON_JDELCAST    DISABLED
SYS			       SET_TRACE_ON_LOGON_JDURAN      DISABLED
SYS			       SET_TRACE_ON_LOGON_JDURAND     DISABLED
SYS			       SET_TRACE_ON_LOGON_SR2048687   DISABLED
SYS			       SET_TRACE_ON_LOGON6	      DISABLED
SYS			       SET_TRACE_ON_LOGON	      DISABLED
SYS			       SET_TRACE_ON_LOGON_5	      DISABLED
SYS			       SET_TRACE_ON_LOGON_1	      DISABLED
SYS			       SET_TRACE_ON_LOGON_7	      DISABLED



SQL> select dbms_metadata.get_ddl('TRIGGER','SET_TRACE_ON_LOGON_1', 'SYS') from dual;


DBMS_METADATA.GET_DDL('TRIGGER','SET_TRACE_ON_LOGON_1','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SYS"."SET_TRACE_ON_LOGON_1"
AFTER LOGON ON DATABA

SQL> SQL> SET LONG 9999999
SQL> /

DBMS_METADATA.GET_DDL('TRIGGER','SET_TRACE_ON_LOGON_1','SYS')
--------------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER "SYS"."SET_TRACE_ON_LOGON_SR4054621"
AFTER LOGON ON DATABASE
DECLARE
MODUL VARCHAR2(50);
--	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF USER IN ('CCHICOMA') AND SYS_CONTEXT('USERENV', 'OS_USER')='xt2054'
THEN
   EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER='''||USER||'_'||SYS_CONTEXT ('USERENV', 'OS_USER')||'SR4054621''';
   EXECUTE IMMEDIATE 'alter session set max_dump_file_size=unlimited';
   EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
 NULL;
END;


SQL> ALTER TRIGGER "SYS"."SET_TRACE_ON_LOGON_1" DISABLE;


SQL> ALTER TRIGGER "SYS"."SET_TRACE_ON_LOGON_1" ENABLE;

Trigger altered.

SQL> SELECT OWNER, TRIGGER_NAME, STATUS FROM DBA_TRIGGERS WHERE TRIGGER_NAME LIKE 'SET_TRACE_ON_LOGON%';

OWNER			       TRIGGER_NAME		      STATUS
------------------------------ ------------------------------ --------
SYS			       SET_TRACE_ON_LOGON_SR2048687   DISABLED
SYS			       SET_TRACE_ON_LOGON_JDURAND     DISABLED
SYS			       SET_TRACE_ON_LOGON_JDURAN      DISABLED
SYS			       SET_TRACE_ON_LOGON_JDELCAST    DISABLED
SYS			       SET_TRACE_ON_LOGON_7	      DISABLED
SYS			       SET_TRACE_ON_LOGON_5	      DISABLED
SYS			       SET_TRACE_ON_LOGON_1	      ENABLED
SYS			       SET_TRACE_ON_LOGON6	      DISABLED
SYS			       SET_TRACE_ON_LOGON	      DISABLED


sql>SELECT INST_ID,SID,SERIAL#,OSUSER,username, machine, status,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS'),PROGRAM FROM GV$SESSION where username='DS_EMIWEBRS';
  

ask the user to run the application.


SQL>  SELECT INST_ID,SID,SERIAL#,OSUSER,username, machine, status,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS'),PROGRAM FROM GV$SESSION where username='DS_EMIWEBRS';

SQL> !srvctl status database -d BDRSAT


show parameter dump


 cd /oracle/product/oracle/diag/rdbms/bdrsat/BDRSAT1/trace
 ls -trl *SR3391903*

[oracle@rsdcedbadm01 trace]$ date
Tue Feb 18 11:42:58 PET 2020


[oracle@rsdcedbadm01 trace]$ tkprof BDRSAT1_ora_377872_DS_EMIWEBRS_AdministratorSR3391903.trc BDRSAT1_ora_377872_DS_EMIWEBRS.txt

[oracle@rsdcedbadm01 trace]$ ls -trl BDRSAT1_ora_377872_DS_EMIWEBRS.txt

[oracle@rsdcedbadm01 trace]$ cat BDRSAT1_ora_377872_DS_EMIWEBRS.txt  copy and paste in the desktop and share to the team.