The Oracle RDBMS produces a significant number of trace and log files, which record current status and other information related to the condition of the database system. Part of the administration of an ORACLE RDBMS is to remove these files from the OS layer once they become obsolete. This article covers the creation of shell script to remove such files from the Oracle database server in a UNIX or LINUX environment.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Dec 15 18:08:39 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Use the SQLPLUS command show to see the location of your
background_dump_dest directory.

SQL> show parameter background_dump_dest

NAME TYPE VALUE
———————————— ———– ——————–
background_dump_dest string /orcl/admin/orcl/bdump
SQL>

4. Exit out of SQLPLUS.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>

5. Change directories to the location of you back

mylinux:> cd /orcl/admin/orcl/bdump
mylinux:>

6. Execute the following statement to see how many trace files exist greater than 3 days old.

find . -name “*trc” -mtime +3 -exec ls -lrt {} \;

mylinux:> find . -name “*trc” -mtime +3 -exec ls -lrt {} \;
…
…
…
-rw-r—– 1 oracle dba 943 Jul 12 13:00
./orcl_m001_11864.trc
-rw-r—– 1 oracle dba 811 Jul 12 23:00
./orcl_m001_17140.trc
-rw-r—– 1 oracle dba 897 Jul 13 13:00
./orcl_m001_7152.trc
-rw-r—– 1 oracle dba 789 Jul 13 23:00
./orcl_m001_29058.trc
mylinux:>

7. Execute the following command to remove all file which are older than 3 days: find . -name “*trc” -mtime +3 -exec rm {} \;

mylinux:> find . -name “*trc” -mtime +3 -exec rm {} \;
mylinux:>

8. This shell command can be placed in an executable shell script and executed through CRON to automatically delete files older then three days. Note: The example below will remove trace files from your current directory ending in trc. You will have to replace the find “.” with find “directory_structure” to remove files in a specific directory.

Example: You wish to remove all files older then 3 days in directory
/opt/oracle/db_1/bdump the command would be.

find /opt/oracle/db_1/bdump -name “*trc” -mtime +3 -exec rm {} \;

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com