11g ADRCI using tips and Purging trace & dump files

Some useful commands to use ADRCI for trace monitoring:

$ adrci

adrci> set editor vi (Setting editor. )

adrci> show alert ( Alert log file will be opened in your editor. )

adrci> show alert -tail ( Similar to Unix tail command )

adrci> show alert -tail 200 ( Similar to Unix Command tail -n 200 )

adrci> show alert -tail -f ( Similar to Unix command tail -f )

adrci> show alert -tail 100 -f ( Similar to tail -n 100 -f )

n  If you want to see the lists of all the "ORA-" error, then run following command.

$ adrci
adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"

n  last day adrci monitor

adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1\\\""

n  last hour adrci monitor

adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1/24\\\""

n  check for recent incidents from adrci with

adrci exec=”show incident -all -orderby CREATE_TIME”

Purging trace and dump files with 11g ADRCI

In previous versions of Oracle prior to 11g, we had to use our own housekeeping scripts to purge the udump, cdump and bdump directories.

In Oracle 11g, we now have the ADR (Automatic Diagnostic Repository) which is defined by the diagnostic_dest parameter.

So how are unwanted trace and core dump files cleaned out in 11g automatically?

This is done by the MMON background process.

There are two time attributes which are used to manage the retention of information in ADR. Both attributes correspond to a number of hours after which the MMON background process purges the expired ADR data.

LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings.

SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files

The ADRCI command show control will show us what the current purge settings are as shown below.

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki:

*************************************************************************

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

1095473802           720                  8760                 2010-07-07 08:46:56.405618 +08:00        2010-08-22 22:14:11.443356 +08:00                                                 1                    2                    76                   1                    2010-07-07 08:46:56.405618 +08:00

Setting up Short Term Policty:

In this case it is set to the defaults of 720 hours (30 days) for the Short Term and 8760 hours (One year) for the long term category. We can change this by using the ADRCI command ‘set control’

In this example we are changing the retention to 15 days for the Short Term policy attribute (note it is defined in Hours)

adrci> set control (SHORTP_POLICY =360)

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki:

*************************************************************************

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

1095473802           360                  8760                 2010-08-27 09:36:09.385370 +08:00        2010-08-22 22:14:11.443356 +08:00                                                 1                    2                    76                   1                    2010-07-07 08:46:56.405618 +08:00

Purging Tracefile Manually

We can also manually purge information from the ADR using the ‘purge’ command from ADRCI (note this is defined in minutes and not hours!).

In this example we are purging all trace files older than 6 days. We see that the LAST_MANUPRG_TIME column is now populated.

adrci> purge -age 8640 -type TRACE 

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki:

*************************************************************************

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

1095473802           360                  8760                 2010-08-27 09:36:09.385370 +08:00        2010-08-22 22:14:11.443356 +08:00        2010-08-27 09:50:07.399853