Oracle Database's ADR purge command is superior for purging alert log entries. Just be forewarned on how to actually use it since the desired or perceived outcome is not always apparent.
Alert Log Scraping with Oracle’s ADRCI Utility looks at extracting Oracle alert errors from the alert log and
Rotating Oracle’s Alert Log with ADRCI discussed taking those extracted errors and making backup copies of them by day in separate directories. However, we still have a bunch of alert log information lying around, either in the log.xml file or in separate files under the diag directory structure. Towards the end of the second article there was mention of purging the old data from the alert log with a “PURGE –AGE” command that took the form of:
PURGE –AGE 7200 –TYPE ALERT;
While the syntax is correct the desired or perceived outcome is not always apparent. For this reason, let’s examine a simple example, looking what the alert log (log.xml) contains, issuing the purge command, and then looking again at the alert log.
1. First, we can access the ADRCI utility, set our homepath.
[root@ludwig ~]# adrci
ADRCI: Release 11.1.0.6.0 - Beta on Thu Apr 8 17:32:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/opt/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/db11/db11
diag/tnslsnr/ludwig/listener
adrci> set homepath diag/rdbms/db11/db11
2. Now look at the alert log at the top and bottom to get an idea of what dates are contained within it.
adrci> show alert
2010-03-28 22:08:27.498000 -06:00
Thread 1 advanced to log sequence 105
Current log# 3 seq# 105 mem# 0: /opt/app/oracle/oradata/db11/redo03.log
2010-03-29 02:00:00.112000 -06:00
Clearing Resource Manager plan via parameter
adrci> show alert –tail
2010-04-08 17:31:50.373000 -06:00
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption...
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
2010-04-08 17:31:51.758000 -06:00
Opening with internal Resource Manager plan
Starting background process FBDA
3. Issue the purge command to attempt to remove data from the alert log. As the manuals state, purging is done for diagnostic data that is older than the amount of time (minutes) given in the purge command. So let’s attempt purging for diagnostic data that is over 1 day old (1440 minutes).
adrci> PURGE -age 1440 -type ALERT
4. Now all we need to do is look again at the alert log to see if there are any changes.
adrci> show alert
2010-03-28 22:08:27.498000 -06:00
Thread 1 advanced to log sequence 105
Current log# 3 seq# 105 mem# 0: /opt/app/oracle/oradata/db11/redo03.log
2010-03-29 02:00:00.112000 -06:00
Clearing Resource Manager plan via parameter
adrci> show alert –tail
2010-04-08 17:31:50.373000 -06:00
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption...
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
2010-04-08 17:31:51.758000 -06:00
Opening with internal Resource Manager plan
Starting background process FBDA
Very quickly, from the show alert command, we can see that the purge did not do what we might have expected or wished for--that it would remove data from the alert log for us. After all, the manuals state that the purge command will purge diagnostic data in the current ADR home according to purging policies (1440 minutes). So what is really happening here is the question that needs to be answered. In a nutshell, the purge command only acts upon diagnostic data at the file level; not looking inside any file and the data it contains. Just because the alert log contains diagnostic data older than 1440 minutes, the file itself has been accessed and updated within one day so it is not purged.
Granted, if we changed our purge command to purge data that is older than one minute, clearly our alert log (log.xml) will be purged (removed). We can see this in another very simple example by looking at the files in the alert diagnostic data before and then after issuing the purge command:
1. Look at the timestamp on the current alert log (log.xml).
[root@ludwig ~]# ls -l /opt/app/oracle/diag/rdbms/db11/db11/alert/log.xml
-rw-r----- 1 root root 2047 Apr 8 20:10 /opt/app/oracle/diag/rdbms/db11/db11/alert/log.xml
2. Check the current timestamp.
[root@ludwig ~]# date
Thu Apr 8 20:12:00 MDT 2010
3. Access the ADRCI utility and issue the purge command for purging anything older than one minute.
[root@ludwig ~]# adrci
ADRCI: Release 11.1.0.6.0 - Beta on Thu Apr 8 20:12:04 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/opt/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/db11/db11
diag/tnslsnr/ludwig/listener
adrci> set homepath diag/rdbms/db11/db11
adrci> PURGE -age 1 -type ALERT
adrci> exit
4. Look and see if the alert log (log.xml) file still exists.
[root@ludwig ~]# ls -l /opt/app/oracle/diag/rdbms/db11/db11/alert/log.xml
ls: /opt/app/oracle/diag/rdbms/db11/db11/alert/log.xml: No such file or directory
Since there wasn’t any diagnostic data written to the alert log (log.xml) during the time of our little example, when we issue the purge command the whole alert log is removed from disk.
This may or may not be the desired result we want depending on how we rotate or want to clean alert logs from our system. Before I offer a suggestion, or you conclude there is really one additional piece of information you need to know.
Since many of us like to rotate alert logs or save alert information, it is good to know, since ADRCI works on the file level, what files are affected by the purge command. We know that the log.xml file (the default alert log file) is affected but let’s perform a little experiment to nail this down a bit.
Here we will create four additional alert log files; simulating typical naming conventions that I’ve often seen used when rotating alert log files; and see how they are affected by the purge command. Again we will list the files within the diag/alert area, check the date, issue the purge command, and then see what has happened to the files.
1. Look at the timestamp on the current simulated alert log files created. Here you can see that I’ve created an alert<date>.log, alert<date>.xml, a log<date>.xml, and anyfilename for the file names.
[root@ludwig alert]# ls -lar
total 40
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:44 log.xml
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:44 log.2010-04-10.xml
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:44 any.file.name
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:44 alert.2010-04-10.xml
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:44 alert.2010-04-10.log
2. Check the current timestamp.
[root@ludwig ~]# date
Thu Apr 8 21:48:42 MDT 2010
3. Access the ADRCI utility and issue the purge command for purging anything older than one minute.
[root@ludwig ~]# adrci
ADRCI: Release 11.1.0.6.0 - Beta on Thu Apr 8 20:12:04 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/opt/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/db11/db11
diag/tnslsnr/ludwig/listener
adrci> set homepath diag/rdbms/db11/db11
adrci> PURGE -age 1 -type ALERT
adrci> exit
4. Take a look and see if the alert log (log.xml) file still exists.
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:44 any.file.name
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:44 alert.2010-04-10.xml
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:44 alert.2010-04-10.log
Very plainly you can see that three of the files were not affected by the purge command. The only files that were affected had a format where they were prefixed with “log” and had a suffix of “xml”. In my simple example I specifically used “log.” and “.xml” but you can have any file name that begins with “log” and ends with “xml”. For instance all of the following filenames work with the purge command.
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:56 logabcxml
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:56 log.abcxml
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:56 logabc.xml
-rw-r----- 1 oracle oinstall 2047 Apr 8 21:56 logxml
And, as a warning, it probably isn’t advisable to name any file or directory under the diag alert directory structure with the log/xml pattern. For instance, if you were to create a directory, call it logxml, and then ran the purge command you’d get the following error:
adrci> PURGE -age 1 -type ALERT
DIA-48119: error encountered when attempting to remove a file [/opt/app/oracle/diag/rdbms/db11/db11/alert/logxml] [0]
DIA-48190: OS unlink system call failure
Linux Error: 21: Is a directory
Additional information: 1
The purge command within the ADRCI utility is very powerful and can help aid in the rotation of alert logs. Just remember that it only works at the file level and if that file has been accessed in the number of minutes you specify in the purge command. Also remember that since it acts on the file level do not, at least at this time, create structures that match the pattern the purge command is looking for--in particular anything prefixed with log and suffixed with xml
Ex:
the below command for purging with 2 days retension
Before
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rootvg-u0190lv
30G 27G 1.5G 95% /u0190
adrci exec="purge -age 2880"
After:
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rootvg-u0190lv
30G 24G 5.1G 83% /u0190
--To Purge 7 days old incidents
purge -age 10080 -type incident
--To Purge 7 days old trace
purge -age 10080 -type trace
