ADRCI usage examples

Usage example 1:

[oracle@host ~]$ adrci
adrci> show homes
set home diag/rdbms/orcl/orcl
# Output with enter to editor
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00'"
# Output to termnal without enter to editor
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00'" -term
# Show all 'ORA-%' and 'TNS-%' messages
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00' AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%')" -term
# Show all 'ORA-%' and 'TNS-%' messages with exclude from output specified
show alert -p "ORIGINATING_TIMESTAMP > '2013-08-16 00:00:00' AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%') AND (MESSAGE_TEXT NOT LIKE '%TNS-12502%' AND MESSAGE_TEXT NOT LIKE '%TNS-12537%' AND MESSAGE_TEXT NOT LIKE '%ORA-609%')" -term
show alert -tail -f
exit

Usage example 2:

[oracle@host ~]$ adrci
adrci> show homes
set home diag/rdbms/orcl/orcl
show alert -tail -f
exit

Incident & Problem

adrci>
show problem
show incident
show incident -mode detail -p "incident_id=6201"
show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc

Creation of Packages & ZIP files to send to Oracle Support

adrci>
ips create package problem 1 correlate all
ips generate package 2 in "/home/oracle"

Managing, especially purging of tracefiles

adrci>
show tracefile -rt
show control
set control (SHORTP_POLICY = 360)
set control (LONGP_POLICY = 2190)
show control

# Purge tracefiles manually
purge -age 2880 -type trace
show tracefile -rt

# Purge log directories for listener ADRCI Home
for i in `adrci exec="show homes"|grep listener`;do
# Purge ADRCI Homes log directories older 60 days ago
#for i in `adrci exec="show homes"|sed '1d'`;do
  du -hs $ORACLE_BASE/$i | sort -rh
  du -hs $ORACLE_BASE/$i/* | sort -rh
  du -hs $ORACLE_BASE/$i/trace | sort -rh

  # Purge listener log directory older 60 days ago
  # (60 days * 24h * 60 mins = 86400 mins)
  # -age  - The data older than  ago will be purged
  echo "adrci exec=\"set home $i;purge -age 86400\""
  adrci exec="set home $i;purge -age 86400";
  adrci exec="set home $i;show control;set control \(SHORTP_POLICY = 180\);set control \(LONGP_POLICY = 720\);show control";

  du -hs $ORACLE_BASE/$i | sort -rh
  du -hs $ORACLE_BASE/$i/* | sort -rh
  du -hs $ORACLE_BASE/$i/trace | sort -rh
done

# Check ADRCI Homes policy
for i in `adrci exec="show homes"|sed '1d'`;do
  adrci exec="set home $i;show control;";
done

# Set ADRCI Homes policy
for i in `adrci exec="show homes"|sed '1d'`;do
  adrci exec="set home $i;show control;set control \(SHORTP_POLICY = 180\);set control \(LONGP_POLICY = 720\);show control";
done

Read data from clob

Read data from clob:

declare
  v_body clob;
  offset number := 1;
  v_str varchar(32767);
  amount number := 32767;
begin
  v_body := rpad('Big clob data ', 32767 * 4, 'Big clob data ');
  while(offset <= dbms_lob.getlength(v_body)) loop
    dbms_lob.read(v_body, amount, offset, v_str);

    -- Operate with v_str:
    --dbms_output.put_line(v_str);

    offset := offset + amount;
  end loop;
end;

Capture any failed SQL queries using database trigger in Oracle

Создаем таблицу куда будем записывать запросы с ошибками:

CONNECT SYSTEM;

DROP TABLE servererror_log;

CREATE TABLE servererror_log (
  error_datetime  TIMESTAMP,
  error_user      VARCHAR2(30),
  db_name         VARCHAR2(30),
  error_stack     VARCHAR2(2000),
  call_stack      VARCHAR2(2000),
  SID             NUMBER,
  SQL_ID          VARCHAR2 (13),
  sql_statement   VARCHAR2(2000),
  sql_statement_all   CLOB,
  CLIENT_INFO  VARCHAR2(256 BYTE),
  CURR_SCHEMA  VARCHAR2(256 BYTE),
  CURR_USER    VARCHAR2(256 BYTE),
  CURR_DB_NAME VARCHAR2(256 BYTE),
  HOST         VARCHAR2(256 BYTE),
  IP           VARCHAR2(256 BYTE),
  OSUSER       VARCHAR2(256 BYTE),
  SESSID       VARCHAR2(256 BYTE),
  SESS_USER    VARCHAR2(256 BYTE),
  TERMINAL     VARCHAR2(256 BYTE)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTEM.SERVERERROR_LOG TO SCOTT;

Выдаем явный грант на V_$SESSION пользователю SYSTEM для того чтобы триггер log_server_errors указанный ниже скомпилировался.

CONNECT SYS AS SYSDBA;
GRANT SELECT ON V_$SESSION TO SYSTEM;

Триггер базы данных на SERVERERROR(в условии WHEN можем исключить ошибки которые не нужно логгировать, в данном примере - это ORA-25254):

CONNECT SYSTEM;

DROP TRIGGER log_server_errors;

CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE WHEN (
     ORA_SERVER_ERROR(1)<>25254 /*ORA-25254: time-out in LISTEN while waiting for a message*/
   )
DECLARE
  v_sql_statement VARCHAR2(32767);
  sql_text ora_name_list_t;
  n        pls_integer;
  cursor c_user is
    select vs.sid                                     sid,
           vs.sql_id                                  sql_id,
           sys_context('USERENV','CLIENT_INFO')       client_info,
           sys_context('USERENV','CURRENT_SCHEMA')    curr_schema,
           sys_context('USERENV','CURRENT_USER')      curr_user,
           sys_context('USERENV','DB_NAME')           db_name,
           sys_context('USERENV','HOST')              host,
           sys_context('USERENV','IP_ADDRESS')        ip,
           sys_context('USERENV','OS_USER')           osuser,
           sys_context('USERENV','SESSIONID')         sessid,
           sys_context('USERENV','SESSION_USER')      sess_user,
           sys_context('USERENV','TERMINAL')          terminal
      from dual
    cross join v$session vs
    where 1 = 1     
      and sys_context('USERENV','SESSIONID') = audsid
  ;
  user_rec c_user%rowtype;
BEGIN
  open c_user;
  fetch c_user into user_rec;
  close c_user;

  n := ora_sql_txt(sql_text);
      
  FOR i IN 1..n LOOP
    v_sql_statement := v_sql_statement || sql_text(i);        
  END LOOP;
  INSERT INTO servererror_log(
    error_datetime, error_user, db_name,
    error_stack, call_stack,
    SID, SQL_ID,
    sql_statement, sql_statement_all,
    CLIENT_INFO, CURR_SCHEMA, CURR_USER, CURR_DB_NAME,
    HOST, IP, OSUSER, SESSID,
    SESS_USER, TERMINAL
  ) VALUES(
    systimestamp, sys.login_user, sys.database_name,
    ora_server_error_msg(1)/*dbms_utility.format_error_stack*/,
    dbms_utility.format_call_stack,
    user_rec.sid, user_rec.sql_id,
    substrb(v_sql_statement, 1, 2000),
    v_sql_statement,
    user_rec.client_info, user_rec.curr_schema, user_rec.curr_user, 
    user_rec.db_name,     user_rec.host,        user_rec.ip, 
    user_rec.osuser,      user_rec.sessid,      user_rec.sess_user, 
    user_rec.terminal
  );
  --commit;
END;
/

Выбираем запросы с ошибками(см. поля SID, SQL_STATEMENT, SQL_STATEMENT_ALL):

CONNECT SCOTT;

select * from system.servererror_log
 --where ERROR_USER = 'SCOTT'
 order by error_datetime desc
;

Дополнительные команды которые возможно пригодятся для трассировки сессии:

CONNECT SYSTEM;

ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;
...
  select * from system.servererror_log;
...

ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;

--After Logon Trigger for Tracing on Schema:
CONNECT SCOTT;

CREATE OR REPLACE TRIGGER SCOTT_AFTER_LOGON_TRG_00
  AFTER LOGON ON SCOTT.SCHEMA 
DECLARE
 sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
BEGIN
  IF (USER = 'SCOTT') THEN
    execute immediate sqlstr;
  END IF;
END;
/

How to viewing Oracle bind variable values?

  View V$SQL_BIND_CAPTURE has been introduced to report information on bind variables used by SQL cursors.

  Following query uses the sql_id address that should be specified for each unique SQL statement:

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col KILL_SERIAL_SID format a52
col sql_text format a50 wrap
col name format a15 wrap
col datatype_string format a30
col value_string format a30 wrap

SELECT
   ss.serial#,
   ss.sid,
   --ALTER SYSTEM KILL SESSION '573,31517' IMMEDIATE
   'ALTER SYSTEM KILL SESSION ''' || ss.sid || ',' || ss.serial# || ''' IMMEDIATE;' kill_serial_sid,   
   b.name,
   b.position,
   b.datatype_string,
   b.value_string,
   a.sql_text
FROM
  v$sql_bind_capture b,
  v$sqlarea          a,
  v$sql s,
  v$session ss
WHERE 1 = 1
AND s.sql_id = a.sql_id   
AND b.sql_id = a.sql_id
-- Join v$session to v$sqlarea 
--AND ss.sql_address = a.address 
--AND ss.sql_hash_value = a.hash_value
--
-- Join v$session to v$sql 
--AND ss.SQL_ADDRESS = s.ADDRESS
-- joining on SQL_ID is much more efficient
AND ss.sql_id = s.sql_id
-- To capture only active session with specified SQL_ID
--AND ss.STATUS = 'ACTIVE'
AND b.sql_id = '&sql_id' --'4453zzgmqssxr'
;

You can get the SQL ID from the v$sql view:

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col sql_text format a130 wrap
col bind_name format a35 wrap
col bind_string format a50 wrap

select
  sql_id,
  t.sql_text SQL_TEXT,
  b.name BIND_NAME,
  b.value_string BIND_STRING
from
  v$sql t
  join v$sql_bind_capture b
  using (sql_id)
where
  b.value_string is not null
  and sql_id='&sql_id' --'f8pavn1bvsj7t'
;

How to viewing in SQL Monitor of Enterprise Manager 11g
   Offline Analysis of Long Running SQL using SQL Monitoring Active Reports

Using 10046 trace for tracing bind variable values
  You can run a 10046 level 4 trace to debug code with bind variables and see the bind variable values as it steps through the code:

EXECUTE sys.dbms_system.set_ev ('||SID||','||SERIAL#||',10046,4,'''')

Bind Capture History

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col name format a50 wrap
col value_string format a50 wrap

VARIABLE sql_id VARCHAR2(50)
EXEC :sql_id := '4453zzgmqssxr'

select snap_id, name, position, value_string, last_captured
  from dba_hist_sqlbind
 where sql_id = :sql_id
 order by snap_id desc;

Reading alert log via SQL in Oracle 11g

X$DBGALERTEXT показывает последний log.xml

X$DIAG_ALERT_EXT показывает объединенное содержимое для файлов log_XX.xml, включая log.xml.

connect sys as sysdba

select * from X$DBGALERTEXT where rownum <= 20;


set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170

SELECT ORIGINATING_TIMESTAMP, message_text FROM X$DIAG_ALERT_EXT
where 1 = 1
  and originating_timestamp >= sysdate - 30
  and message_text like 'ALTER SYSTEM%'
  and message_text not like 'ALTER SYSTEM ARCHIVE LOG%'
;

Select ORA-XXXXX and TNS-XXXXX errors last 10 minutes:

set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170

SELECT inst_id, originating_timestamp, MESSAGE_TEXT
    FROM TABLE (
            gv$ (
               CURSOR (
                  SELECT inst_id, originating_timestamp, MESSAGE_TEXT
                    FROM v$diag_alert_ext
                   WHERE     originating_timestamp > (SYSDATE - 10/1440)
                         AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%')
                         AND MESSAGE_TEXT NOT LIKE '%TNS-12502%'
                      )
                )
               )
ORDER BY inst_id, originating_timestamp;