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