Anuj Singh Oracle DBA: 21 Jan 2021

Thursday 21 January 2021

Oracle Tracing file info

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';






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"}'

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

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Thursday 21 January 2021

Oracle Tracing file info

Oracle Tracing file info ....

Modify the ADR Purging Policies

Oracle DBA

anuj blog Archive