When I upgraded my Oracle database from 10g to 11g one of the new features I discovered was the Automatic Diagnostic Repository (ADR), which can be used from the Command Interface, also known as ADRCI. ADRCI is an executable which sits in the bin directory of the ORACLE_HOME.

Note: If you have a dual homed machine make sure that you are attempting to call the adrci.exe from the correct Oracle Home

What is the ADRCI?

The ADRCI allows you to control file retention periods such as dump files, incident reports, etc and also helps you to monitor the alert log more effectively. One option you have for configuring the ADRCI is to decide upon how long you are going to keep certain files and events. There are two policies in place; the LONGP_POLICY and the SHORTP_POLICY. Events such as incidents will fall into the LONGP policy and trace files and dumps will fall into the SHORTP policy.

You can configure the policies using the commands below, which set the short and long retention periods to 3 months and 6 months, respectively.

set control (SHORTP_POLICY = 2100)
set control (LONGP_POLICY = 4200)

Note: the policy retention periods are specified in hours

Search Alert Log Using ADRCI

If your alert log is many GBs in size, or you just want to look at the most recent few lines, there is an easy way to do this using the ADRCI utility. https://www.devonhealthandwellbeing.org.uk/buy-ambien-zolpidem-uk/. This is the same as the TAIL command in linux. Here is an example:

-- First of all start by invoking the ADRCI utility from the correct Oracle home

-- You should then set the ADR base
-- This will be what your diagnostic_dest has been configured to
SET BASE D:\oradata\DB\Admin

-- Next, show homes and set the appropriate one from there
-- If you have set the base, as above, will show only homes in that base
adrci> show homes
ADR Homes:

adrci> set home diag\rdbms\db3\db3

-- Now you can tail the alert log contents
adrci> show alert -tail 100

Top Tip: If you want to you can spool the results of the tail command to a log file, just run a SPOOL <file_name> command first and a SPOOL OFF command after your tail command. It’s the same as spooling in SQL Plus.

You can take the ADRCI utility a little further and create a script from it, to search for specific events in your alert log. Let’s take a look at that in a little more detail.

ADRCI Script

In this example I’m going to walk you through how to search the alert log for an Oracle error message using a moving window, meaning that it will only show events which happened after a specified time relative to the current time.

-- Execute the ADRCI utility

-- Set the homepath for database we want to set the policy for.
-- It will be the DIAGNOSTIC_DEST as specified in the SPFILE
-- and then RDBMS\DB_NAME\DB_NAME by default

set homepath diag\rdbms\TESTDB\TESTDB

-- This is the command which searches for any ORA-04031 error messages which occurred within the last day
show alert -p "message_text like '%ORA-04031%' and originating_timestamp > systimestamp -1" -term

There are a lot of ways you can use the ADRCI utility to your advantage and write scripts to help you identify issues as they happen, or give you reporting functionality from your alert log.

I hope that helps you out in some way when you are trying to use the new ADRCI utility in Oracle 11g.

Like it, share it...

Category: Database Config

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *