Sometimes DBAs and SAs do not adequately plan and implement a location for storing archivelog files on disk, or there is more growth and activity than expected.
In these scenarios, it is sometimes convenient to have a script that checks for space in the primary location and that sends out warnings before the archivelog destination becomes full.
Additionally, you may want to implement within the script that the archivelog location automatically start an RMAN job to back up and delete the archivelogs to free up space.
Scripts such as this prove useful in chaotic environments that have issues with the archivelog destination’s filling up at unpredictable frequencies. If the archivelog destination fills up, the database will hang. In some environments, this is highly unacceptable.
You could argue that you should never let yourself get into this type of situation. Therefore, if you are brought in to maintain an unpredictable environment and you are the one getting the phone calls at 2 a.m., you may want to consider implementing a script such as the one provided in this section.
Before using the following script, change the variables within the script to match your environment.
The script will send a warning email when the threshold goes below the amount of space specified by the THRESH_GET_WORRIED variable and will run an RMAN backup of the archivelogs.
#!/bin/bash PRG=’basename $0′ DB=$1
# Set thresholds for getting concerned. THRESH_GET_WORRIED=2000000 # 2Gig from df -k MAILX=”/bin/mailx” MAIL_LIST=”[email protected] “
BOX=’uname -a | awk ‘{print$2}” #
loc=’sqlplus -s <<EOF CONNECT / AS sysdba
SET HEAD OFF FEEDBACK OFF
SELECT SUBSTR(destination,1,INSTR(destination,’/’,1,2)-1) FROM v\\$archive_dest WHERE dest_name=’LOG_ARCHIVE_DEST_1′; EOF’
# The output of df depends on your version of Linux/Unix,
# you may need to tweak the next line based on that output. Free_space=’df -k | grep ${loc} | awk ‘{print $4}”
echo box = ${BOX}, sid = ${DB}, Arch Log Mnt Pnt = ${loc} echo “free_space = ${free_space} K”
echo “THRESH_GET_WORRIED= ${THRESH_GET_WORRIED} K” #
if [ $free_space -le $THRESH_GET_WORRIED ]; then
$MAILX -s “Arch Redo Space Low ${DB} on $BOX” $MAIL_LIST <<EOF
Archive log dest space low running backup now, box: $BOX, sid: ${DB}, free space: $free_space EOF
# Run RMAN backup of archivelogs and delete after backed up rman nocatalog <<EOF connect target /backup archivelog all delete input; EOF elseecho no need to backup and delete, ${free_space} KB free on ${loc} fi#exit 0
If you are using an FRA for the location of your archivelog files, you can derive the archive location from the V$ARCHIVED_LOG view; for example,
SQL> select substr(name,1,instr(name,’/’,1,2)-1)
from v$archived_log where first_time =
(select max(first_time) from v$archived_log);
There are also a few other ways to manage this space when using FRA, and it is definitely another reason to use FRA instead of just setting a directory. The threshold can be determined using SQL from v$recovery_file_dest instead of looking at the file system.
SQL> select name, space_limit, space_used from v$recovery_file_dest;
The FRA size can be increased to accommodate more archivelogs until the space is freed up by a backup and delete or purge of old backups. The FRA destination can also be changed. It is easier to automate the process of increasing the FRA size and running a backup to make sure that the archivelog directory does not fill up. The following can be inserted into the previous script before the RMAN script:
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20G scope=both; 498
Leave a Reply