Purging Oracle traces with ADRCI



ADRCI is an Oracle command-line tool used to manage Oracle Database diagnostic data.

$ adrci

ADRCI: Release 12.1.0.2.0 - Production on Fri Sep 29 14:28:26 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/oracle/BBDD1/saptrace"

We should set adrci_home we want to work with, in case of show control command and other is needed to work with only one ADR Home..

adrci> show home
ADR Homes:
diag/rdbms/bbdd1/BBDD1
diag/tnslsnr/hostname10p/listener_bbdd1

If we try to work without selecting one... this will hapen:
adrci> show control
DIA-48448: This command does not support multiple ADR homes

So i selected one on them:

adrci> set home diag/rdbms/bbdd1/BBDD1
adrci> show home
ADR Homes:
diag/rdbms/bbdd1/BBDD1
adrci>  show control

and now, we can see 

adrci>  show control

ADR Home = /oracle/BBDD1/saptrace/diag/rdbms/bbdd1/BBDD1:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
4147224502           720                  8760                 2017-05-08 11:29:59.425712 +02:00        2017-09-26 09:11:17.904064 +02:00                                                 1                    2                    82                   1                    2017-05-08 11:29:59.425712 +02:00
1 rows fetched

SHORTP_POLICY--> Number of hours after which to purge ADR contents that have a short life, witch means: Trace files, Core dump files and Packaging information)
LONGP_POLICY--> Number of hours after which to purge ADR contents that have a long life, witch means: Incident information, Incident dumps and Alert logs

So in this example, trace files kill keep 720 hours (30 days) and alert log 8760 hours (365 days)

So as we don´t have enough free space to allocate so many trace files, we are going to modify SHORTP_POLICY and LONGP_POLICY

adrci> set control (SHORTP_POLICY = 168)
adrci> set control (LONGP_POLICY = 720)
and now configuration is:
adrci> show control

ADR Home = /oracle/BBDD1/saptrace/diag/rdbms/bbdd1/BBDD1:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
4147224502           168                  720                  2017-09-29 14:50:07.926183 +02:00        2017-09-26 09:11:17.904064 +02:00

So now, oracle will retain 7 days trace files and 30 alert log.

But, maybe you want to purge files without set a purge policy, only to purge just now, because filesystem is empty, you can do
(be careful, in this case time is in nimutes, not in hours like SHORTP_POLICY or LONGP_POLICY)
adrci> purge -age 8640  -type trace
---purge trace files older that 6 days
adrci> purge -age 1440 -type alert
---purge trace files older that 1 day

But this is only half truth, because this command only works with files and not with his contents... which means that this command are going not to analyze your alert.log and clean lines older than a date. It only will delete the file if the last modification date is older that a date you said and if the file match with this mask log*.xml...


so if you rename every day the alert maintaining this mask

$ mydate=`date '+%d%m%Y_%H%M%S'`
$ mv log.xml log_${mydate}.xml
$ ls -ltr
total 14072
-rwxrwxr-x   1 oracle     oinstall   7149505 Sep 29 15:12 log_29092017_153603.xml
-rw-rw----   1 oracle     oinstall      1289 Sep 29 15:36 log.xml


$ adrci

ADRCI: Release 12.1.0.2.0 - Production on Fri Sep 29 15:37:20 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/oracle/BBDD1/saptrace"
adrci> purge -age 3 -type alert
adrci> exit
$ ls -ltr
total 4
-rw-rw----   1 oracle     oinstall      1289 Sep 29 15:36 log.xml
You can see that after the purge execution, the only file i has in my system is log.xml.. so a workaround of this is to schedule daily in your crontab a line like this 
30 0 * * * mv /your_path/log.xml /your_path/log_`date '+%d%m%Y_%H%M%S'`.xml 2>>/dev/null

And finally, i had one bad new, i try to do same with file alert_${ORACLE_SID}.log moving it to:
mv alert_${ORACLE_SID}.log alert_${ORACLE_SID}_`date '+%d%m%Y_%H%M%S'`.log
and purging it, but it did not work, so in case of alert.log i fear you should to purge it with operating system commands...
Next week, i´m going to tell you who to purge alert.log file effectively..

Comments

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more