Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Mar 24, 2008

Oracle Housekeeping Script

By DatabaseJournal.com Staff



>>Script Language and Platform: Oracle
A commonly occuring error is the archiver error that occurs due to the archive dump destination being full. Another error is inability to write info in an audit file, which causes the user session to terminate. All of these problems are common for a DBA. Hence, the only way out is to have a script in place that monitors these destinations for lack of space. This script effectively does this by removing all of these files that are 120 days old.
WARNING!! THIS SCRIPT SHOULD ONLY BE RUN AS AN ORACLE USER......

Author: BHABANI SHANKAR MAHARANA OCP DBA 9i,OCP DBA 10g


#*************************************************************************************************************
#orahousekeeping.ksh
#Script Name: orahousekeeping.ksh
#Author:Bhabani S Maharana
#Date: 23-May-2006
#Version:1.0
#Info:This script removes all the audit,trace and archive files of Oracle 
#that are more than 120 days old.
# The timespan of the files can be changed by altering the value 120 in the script 
#to suit specific needs.
#**************************************************************************************************************
clear
sleep 2
tput cup 19 30
echo "WARNING!....This script should only be run as a ORACLE USER or ROOT:\c"
tput cup 20 30
echo "Do you want to run the Script(y/n):\c"
read ans
if [ "$ans" = "y" ];then
sleep 2
tput cup 22 30
echo "REMOVING OLD FILE LIST'S AND SCRIPTS"
rm -f removeaudit.ksh
rm -f removetrace.ksh
rm -f removearchive.ksh
rm -f audit.lst
rm -f trace.lst
rm -f archive.lst
tput cup 24 30
echo "FINDING ALL THE TRACE FILES,AUDIT FILES and ARCHIVE FILES 120 DAYS OLD" 
sleep 2
tput cup 26 30
echo "PREPARING PRIMARY LIST's"
find /u01/app/oracle/admin -name \*.aud -mtime +120 >audit.lst 
find /u01/app/oracle/admin -name \*.trc -mtime +120 >trace.lst
find /genius/orarch01 -name \*.arc -mtime +120 >archive.lst
sleep 2
tput cup 28 30
echo "EDITING PRIMARY LIST's"
cat audit.lst|sed 's/^/rm -f /g'>removeaudit.ksh 
cat trace.lst|sed 's/^/rm -f /g'>removetrace.ksh
cat archive.lst|sed 's/^/rm -f /g'>removearchive.ksh
tput cup 30 30
echo "ORACLE HOUSEKEEPING SCRIPTS PREPARED....."
echo " OLD ORACLE FILES REMOVAL STARTED:\c"
ksh removearchive.ksh
ksh removeaudit.ksh
ksh removetrace.ksh
echo "FILES REMOVED:\c"
fi

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued


Back to Database Journal Home



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM