Oracle Tracing file info ....
v$diag_info
-- alert log info set linesize 400 col ALERT_LOG for a100 col PATH for a100 select di.value ||'/'|| 'alert_' || i.instance_name || '.log' ALERT_LOG from v$diag_info di, v$instance i where di.name = 'Diag Trace';set linesize 300 col NAME for a25 col VALUE for a80 select * from GV$DIAG_INFO ;define Days=1 set pages 999 lines 100 SELECT to_char(originating_timestamp,'DD-MM-RR HH24:MI:SS') "Time", message_text FROM X$DBGALERTEXT WHERE originating_timestamp > systimestamp - &Days AND regexp_like(message_text, '(TNS-|ORA-|error)');--Last Hour 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 - 1/24) --- 1hr AND message_text LIKE '%ORA-%'))) ORDER BY inst_id, originating_timestamp;
All the trace files go into the Automatic Diagnostic Repository (ADR) by default set linesize 300 pagesize 300 col name form a25 col value form a80 wrap select * from v$diag_info order by name /
--- alert log file set linesize 300 pagesize 300 col name form a25 col value form a80 wrap select * from v$diag_info where 1=1 and NAME='Diag Trace' order by name /
-- trace file info col adr_home for a80 col trace_filename for a50 select adr_home,trace_filename,change_time from v$diag_trace_file order by change_time desc fetch first 5 rows only ; --- set linesize 300 pagesize 300 col filename for a100 col component_id for a30 select distinct component_id,filename from v$diag_alert_ext order by 1,2; -- Ora- error info set linesize 300 pagesize 300 col message_text for a100 col sdate for a30 select to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS') sdate ,message_text from v$diag_alert_ext where message_text like '%ORA-%' and originating_timestamp > sysdate- 2 order by originating_timestamp;
set linesize 300 pagesize 300 col message_text for a80 wrap col detailed_location for a30 col problem_key for a20 col originating_timestamp for a45 select originating_timestamp,detailed_location,message_level,message_text,problem_key from v$diag_alert_ext where message_level=1 --AND MESSAGE_TEXT LIKE '%ORA-00600%' ORDER BY ORIGINATING_TIMESTAMP DESC;
set pagesize 200 linesize 300
column adr_home format a40
column message_text format a80
col originating_timestamp for a15
select call_monitor,
adr_home,
inst_id,
to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS') originating_timestamp,
message_text
from (select adr_home,
inst_id,
ORIGINATING_TIMESTAMP,
message_text,
dense_rank() over (PARTITION BY adr_home order by ORIGINATING_TIMESTAMP DESC NULLS LAST) as call_monitor
from v$diag_alert_ext)
where ORIGINATING_TIMESTAMP > systimestamp - INTERVAL '1' hour
order by ORIGINATING_TIMESTAMP;
set linesize 500
define beg=111
define end=222
col originating_timestamp for a37
col message_text for a50 wrap
col PROBLEM_KEY for a20
col DETAILED_LOCATION for a20
col COMPONENT_ID for a20
col CLIENT_ID for a10
col MODULE_ID for a10
select
to_char(originating_timestamp,'yyyy-mm-dd hh24:mi:ssxff TZR') originating_timestamp
,message_text
,inst_id
,component_id
-- ,host_id
-- ,host_address
,message_type
,message_level
,message_group
,client_id
,module_id
,process_id
,user_id
,detailed_location
,problem_key
from --sys.x$dbgalertext
v$diag_alert_ext
where 1=1
-- and originating_timestamp between &beg and &end
-- and originating_timestamp > sysdate -1
and originating_timestamp >sysdate - interval '2400' minute
and ( problem_key is not null
or message_text like '%ORA-%'
or message_text like '%WARNING%'
or message_text like '%FATAL%'
)
/
select to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
MESSAGE_TEXT
from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP between sysdate - interval '10' hour and sysdate
and trim(COMPONENT_ID)='tnslsnr';
set pagesize 400 linesize 200 long 99999
column ADR_HOME format a40
column "mylog.xml" format a180
select
xmlelement(noentityescaping "msg",
xmlattributes( alt.originating_timestamp as "time",
alt.organization_id as "org_id",
alt.component_id as "comp_id",
alt.message_id as "msg_id",
alt.message_type as "type",
alt.message_group as "group",
alt.message_level as "level",
alt.host_id as "host_id",
alt.host_address as "host_addr",
alt.process_id as "pid_id",
alt.version as "version"
),
xmlelement("txt", message_text)
) as "mylog.xml"
from
x$dbgalertext alt;
where
rownum < = 30;
====
col TRACE_FILE for a70
col RAC_SID for a15
select
i.instance_number inst_id
, s.sid
, s.serial#
, p.spid pid
, s.sid||','||s.serial#||',@'||i.instance_number rac_sid
, (select value from v$diag_info where name='Default Trace File') trace_file
from
v$session s
, v$instance i
, v$process p
where
s.sid=(select sid from v$mystat where rownum=1)
and s.paddr=p.addr
=============
set linesize 100 pagesize 300
column trace_file format a80
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
set linesize 300 pagesize 300
col MESSAGE_TEXT for a100
select substr(MESSAGE_TEXT, 1, 150) message_text,to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD') err_timestamp, count(*) cnt
from X$DBGALERTEXT
where (upper(MESSAGE_TEXT) like '%ORA-%'
or upper(MESSAGE_TEXT) like '%ERROR%')
and cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - 7
group by substr(MESSAGE_TEXT, 1, 150), to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD')
order by to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD');
set linesize 700 pagesize 500
define beg=111
define end=222
col originating_timestamp for a37
col message_text for a50 wrap
col PROBLEM_KEY for a20
col DETAILED_LOCATION for a20
col COMPONENT_ID for a20
select
to_char(originating_timestamp,'yyyy-mm-dd hh24:mi:ssxff TZR') originating_timestamp
,message_text
,inst_id
,component_id
,host_id
,host_address
,message_type
,message_level
,message_group
,client_id
,module_id
,process_id
,user_id
,detailed_location
,problem_key
from --sys.x$dbgalertext
v$diag_alert_ext
where 1=1
-- and originating_timestamp between &beg and &end
and originating_timestamp > sysdate -1
and ( problem_key is not null
or message_text like '%ORA-%'
or message_text like '%WARNING%'
or message_text like '%FATAL%'
)
/
==========
select TRACE_FILENAME
from V$DIAG_TRACE_FILE
where 1=1
and CHANGE_TIME >sysdate -1/24
order by 1;
select PAYLOAD
from V$DIAG_TRACE_FILE_CONTENTS
where TRACE_FILENAME = 'rdc_ora_98948.trc'
order by LINE_NUMBER;
===============
from
https://www.pro-oracle.com/2021/09/oracle-database-19c-modify-adr-purging.html
Modify the ADR Purging Policies
set variable !!!
export ORACLE_HOME=/u01/app/19.0.0/grid
export ORACLE_SID=+ASM2
# Set desired values for the ADR Purging Policies in days
SHORTP_POLICY_DAYS=30
LONGP_POLICY_DAYS=30
SHORTP_POLICY_HOURS=$(($SHORTP_POLICY_DAYS * 24))
LONGP_POLICY_HOURS=$(($LONGP_POLICY_DAYS * 24))
for i in `adrci exec="show homes;" | tail -n +2`; do
if adrci exec="set home $i; show control;" | grep -q "1 row fetched"; then
adrci exec="set home $i; set control \(SHORTP_POLICY = $SHORTP_POLICY_HOURS, LONGP_POLICY = $LONGP_POLICY_HOURS\);"
adrci exec="set home $i; show control;" | awk '
FNR==2 {print $0}
FNR==3 {print $0}
FNR==4 {printf "%20s %20s\n", $2, $3}
FNR==5 {printf "%20s %20s\n", $2, $3}
FNR==6 {printf "%20s %20s\n", $2, $3}
END {print "\n"}'
fi
done
===============
[grid@rac02 ~]$ vi diagpolicy.sh
[grid@rac02 ~]$ chmod 777 diagpolicy.sh
[grid@ibrac02 ~]$ ./diagpolicy.sh
ADR Home = /u01/app/grid/diag/rdbms/_mgmtdb/-MGMTDB:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 720
ADR Home = /u01/app/grid/diag/asm/+asm/+ASM2:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 720
ADR Home = /u01/app/grid/diag/crs/ibrac02/crs:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 720
ADR Home = /u01/app/grid/diag/asmtool/user_grid/host_2670455502_107:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 720
ADR Home = /u01/app/grid/diag/asmtool/user_root/host_2670455502_107:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 720
ADR Home = /u01/app/grid/diag/asmtool/user_root/host_2670455502_110:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 720
ADR Home = /u01/app/grid/diag/apx/+apx/+APX2:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 720
ADR Home = /u01/app/grid/diag/kfod/ibrac02/kfod:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 720
adrci> set home diag/crs/rac02/crs
adrci> show control
ADR Home = /u01/app/grid/diag/crs/ibrac02/crs:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME SIZEP_POLICY PURGE_PERIOD FLAGS PURGE_THRESHOLD
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- -------------------- -------------------- -------------------- --------------------
3635384382 720 720 2022-11-18 05:42:26.304673 -05:00 2022-11-17 07:38:41.279001 -05:00 2022-11-18 05:34:43.772602 -05:00 1 2 110 1 2018-02-02 16:45:19.253492 -05:00 18446744073709551615 0 0 95
1 row fetched
split alter logfile date wise sed -n -e '\#2024-07-31#,\#2024-08-01#w first.out' alert_rac1.log sed -n -e '\#2024-08-01#,\#2024-08-01#w Alert01-08-2024.out' alert_rac1.log cat Alert01-08-2024.out
