Process to Delete and Rename Old Alert Log Entries in Oracle 11g R2

1. Locate the Alert Log File:

The alert log file in Oracle 11g R2 is typically found in the directory specified by the DIAGNOSTIC_DEST initialization parameter. For older systems or specific setups, you might see references to BACKGROUND_DUMP_DEST. The exact path can be:

• UNIX: /oracle/<SID>/saptrace/background
• Windows: <drive>:\oracle\<SID>\saptrace\background

You can check this by querying:

SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';

2. Renaming the Alert Log:

You can rename the current alert log file while the Oracle instance is running. After renaming, Oracle will automatically create a new alert log file when it needs to write new entries:

• UNIX:

  mv alert_<SID>.log alert_<SID>_<timestamp>.log

• Windows:

  ren alert_<SID>.log alert_<SID>_<timestamp>.log

Replace <SID> with your Oracle System Identifier and <timestamp> with a date or any identifier for archival.

3. Deleting Old Alert Log Entries:

Manual Deletion:

You can delete old alert log files manually if you've renamed them. This should be done with caution, as you might need this data for troubleshooting:

rm alert_<SID>_<old_timestamp>.log

Ensure you keep some history for troubleshooting purposes.

Using ADRCI (Automatic Diagnostic Repository Command Interpreter):

ADRCI allows for more systematic management of diagnostic data, including alert logs:

• Open ADRCI:

  adrci

• Set the home:

  set home diag/<path_to_your_adr_home>

• To purge old logs, you can use:

  purge -age <minutes>

  Here, <minutes> is how far back you want to keep the data. For instance, to purge all data older than 30 days (720 hours):

  purge -age 43200

Note that ADRCI primarily deals with XML logs, not text-based logs directly, but setting retention policies can help manage space.

4. Automating the Process:

For regular maintenance, consider scheduling a script to run these commands periodically. This could involve creating a shell script or a Windows batch file that renames the current log, deletes old logs, and possibly compresses or moves them to an archive location.

5. Additional Considerations:

• Ensure you have backups or copies of critical information.
• If you're using SAP with Oracle, be aware of different configurations or paths for logs as mentioned in some of the sources.
• Monitor the space used by these logs to prevent disk space issues, especially in environments where database activity is high.

Remember, these actions can be performed without shutting down the database, but always ensure you have recent backups or copies of logs for troubleshooting.

The alert log for a database is always growing. For as long as the database is running, entries are being added. Left unchecked, this growth will result in all the space on the disk being consumed. At that point, Oracle won't be able to write anything more to the alert log, and database activity will halt. The solution is to delete the alert log periodically. Periodically deleting the alert log file carries with it one problem: You may need to refer to entries in the file that you just deleted. To combat that problem, you can rename the alert log file and embed the date as part of the name. For example:

NT: rename coinalrt.log coinalrt_991115.log

UNIX: mv coinalrt.log coinalrt_991115.log

Rename or delete Alert log file
You can rename or delete the alert log file while the database is running. There's no need to shut down the database first. After you've renamed the file, Oracle will create a new alert log the next time it writes out a log entry. With respect to the previous example, the next time Oracle writes a log entry, a new coinalrt.log file will be created. You should establish a retention schedule for alert log files; I usually keep them for one month, and rename them every week. Every time you rename a file, you should delete files containing entries that are older than your retention period. Keeping old alert log files around for a period of time is a good practice. In the next lesson, you will check the session high water mark for license compliance.