Proactive Oracle DBA

This is a series of posts.
I am working this to share some of the many scripts I schedule to automatically run to alert me of any current or potential issues. If there are others you can recommend then please do. If you can share, advise, or improvement on anything, please share that as well.

Eventually there will be a post for all of these.

Email me if you can't wait 

1. Checking the Alert Log
2. Backups and monitoring the results
1. RMAN Full backup script
2. RMAN Incremental backup script
3. RMAN Archivelogs only backup script
3. RMAN Validation script
4. RMAN Catalog Maintenance script
5. Checking for corruption after Validation
1. Export datapump expdp (as well as RMAN backups)
2. Deletion of audit files
3. Updated Monitor Server Disk Space
4. Original Monitor Server Disk Space
5. Checking for failed logins
6. Checking for blocked sessions
7. Checking for locked accounts
8. Check Tablespace usage
9. Monitor for account expiration date
6. PL/SQL Jobs
1. Check for failed jobs
2. Monitor invalid objects, attempt to recompile, otherwise send email

Please comment if there are others you think I should add or improvements to the existing version I am using. 

Updated Monitor Server Disk Space

Monitoring of potential issues includes knowing when any volumes reach a certain capacity. It can take time to add space or free it up so being notified before an issue crops up is imperative for any DBA.

This is an updated version of the more complicated original version immediately below this link. I like and now use this more simplified version. 

1. Set THRESH parameter to the percent of how full a volume is before an email is sent.
2. Update the 2nd line (none|cdrom|qgscanner) to remove volumes you do not care about and do not want to be notified of.

I use cron to check during business hours of disk space issues.
# ----------------------------------------------
# -- Monitor disk space of drives and shares ---
# ----------------------------------------------
5,15,25,35,45,55 8-16 * * 1-5 /oracle/scripts/diskAlert.sh

diskAlert.sh

#!/bin/sh

THRESH=95;
df -PH | grep -vE 'none|cdrom|qgscanner' | awk '{ print $5 " " $1 }' | while read output;
do
  echo $output
  usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1  )
  partition=$(echo $output | awk '{ print $2 }' )
  if [[ $usep -ge $THRESH ]]; then
    echo -e "$(date) Threshold $THRESH% exceeded. \nRunning out of space on \"$partition ($usep%)\" on $(hostname)" |
    mail -s "Alert: `uname -n` low on disk space $usep%" email@company.com
  fi
done


Example of email when volume exceeds thhreshold:

Alert: halfmoon low on disk space 98%

Fri Dec  8 05:40:03 PST 2019 Threshold 95% exceeded.

Running out of space on "sharename.com:/mydrive20/LarryEllison (98%)" on halfmoon

Unix - Automatically notifiy when disk space is running low

It's good to be forewarned when one of your volumes or diskspace is running low before it actually runs out (100% used). I have seen many databases hang when any one of the volumes fills up for what ever reason. This script runs continuously in the background and checks for available disks space based on a threshold limit you set in the script.

Your system administrator will have their own method, but as the DBA you may want to know in advance of the system administrator and not have access to their tools.

First off I would like to thank Andy Denslow for the original check_diskspace code. I have made some modifications over time.

There are two scripts: run_check_diskspace.sh and check_diskspace.sh.

run_check_diskspace.sh is executed in the backgroup and every 30 minutes will call check_diskspace.sh.

check_diskspace.sh will perform the df -k command and if the threshold is met or passed will send you an email.

You can edit the sleep parameter to the disired time interval you wish to check. 1800 secs = 30 minutes.
Run in cron may be more desired.

First run_check_diskspace.sh
# ------------------------------------
# run_check_diskspace.sh
# ------------------------------------
#!/usr/bin/sh
x=0
while [ x -lt 1 ] ; do
/u01/app/oracle/scripts/monitor/check_diskspace.sh
sleep 1800
done


Now for check_diskspace.sh

# ------------------------------------
# check_diskspace.sh
# ------------------------------------
#!/bin/sh
############################
# This script is used to check the disk
# space of local filesystems and send
# alerts when the threshhold has been reached
#
# Original Base Code: Andy Denslow 1995
# Updated: Howard Hackworth 2004
############################
# -- At what point do we send page?
PAGELIMIT=95
# -- At what point do we send email warning of low diskspace?
LIMIT=85
## -- Uncomment the following one line for use with pager
# PAGER='1234567@somelocation.net'
# -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# -- Who do I send email to?
MAIL='howard_hackworth@sgxpharma.com'
TEMP=/tmp/diskspace.tmp
df -kl | sed "s/%/ /g" | sed "/Filesystem/d" | sed "/cdrom/d" >$TEMP
cat $TEMP while read LINE
do
SIZE=`echo $LINE | awk '{print $5}'`

if [ "$SIZE" -gt $LIMIT ]; then
SYSTEM=`echo $LINE | awk '{print $6}'`

echo "DISKSPACE WARNING *** `hostname`: $SYSTEM is $SIZE% full" | /usr/bin/mailx -s \
"Diskspace Warning `hostname`: $SYSTEM is $SIZE% full" $MAIL
elif [ "$SIZE" -gt "$PAGELIMIT" ]; then

## -- Uncomment for use with a pager instead of email:
## --
# echo "DISKSPACE CRITICAL *** `hostname`: $SYSTEM is $SIZE% full" | /usr/bin/mailx -s # "CRITICAL Diskspace ALERT! `hostname`: $SYSTEM is $SIZE% full" $PAGER
##
echo "`hostname`:$SYSTEM is $SIZE% full" | /usr/bin/mailx -s \
"CRITICAL Diskspace ALERT! `hostname`: $SYSTEM is $SIZE% full" $MAIL
fi
done
rm $TEMP
# -- end check_diskspace.sh
# ----------------------------

To execute run from the command line the following command:
/scripts/monitor% ./run_check_diskspace.sh &
I also include this in my dbora script as follows:
su - $ORA_OWNER -c nohup /u01/app/oracle/scripts/monitor/run_check_diskspace.sh &
This is an example of the email you will receive when the disk threshold is reached.

Subject: Diskspace Warning hostname.yourdomain.com: /u02 is 91% full
DISKSPACE WARNING *** hostname.yourdomain.com: /u02 is 91% full

Check for corruption after RMAN validate script

Weekly check the Oracle views for detecting block corruption. Run after Validation script although the Validation script should be enough to report any block corruption.

check_corruption_views.sh

#!/bin/tcsh -f
source ~oracle/.cshrc
setenv VDATE `date '+%A %m/%d/%y %X'`
setenv MAILGRP "email@company.com"

sqlplus -SILENT user/password@orcl < /oracle/scripts/check_corruption_views.sql

cat check_corruption.out | /bin/mailx -s "${ORACLE_SID} - `uname -n` : Check Corruption Views $VDATE" \
$MAILGRP

exit

Oracle RMAN Validation script

Along with RMAN Maintenance on your RMAN catalog, it is critical to run periodic RMAN Validation on your backups. This helps to ensure there is no corruption and that valid backups are available if and when they are needed.

Any block corruptions are visible in the V$DATABASE_BLOCK_CORRUPTION view as well as the RMAN output. 

To check for physical and logical corruption. The Logical keyword includes checks for logical corruption.

BACKUP VALIDATE CHECK LOGICAL DATABASE

Validates the contents of the backup files. Include commands for Controlfiles and Archivelog files.
RESTORE DATABASE VALIDATE
RESTORE CONTROLFILE VALIDATE
RESTORE ARCHIVELOG ALL VALIDATE

RUN {
  BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
  RESTORE DATABASE VALIDATE;
  RESTORE CONTROLFILE VALIDATE;
  RESTORE ARCHIVELOG ALL VALIDATE;
}

Here is the entire script. Run once a week from cron. Highlighted in yellow are lines that require updating to match your environment.

validate_backups.sh

#!/bin/sh
# ------------------------------------------------------------------
# Determine the user which is executing this script.
# ------------------------------------------------------------------

CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`

# ------------------------------------------------------------------
# Put output in .out. Change as desired.
# Note: output directory requires write permission.

# RMAN_LOG_FILE=${0}.out
RMAN_LOG_FILE=${0}.`date +%Y%m%d_%H%M%S`.out

# ------------------------------------------------------------------
# You may want to delete the output file so that 

# backup information does not accumulate.

# If not, delete the following lines.
# ------------------------------------------------------------------

if [ -f "$RMAN_LOG_FILE" ]
then
        rm -f "$RMAN_LOG_FILE"
fi

# -----------------------------------------------------------------
# Initialize the log file.
# -----------------------------------------------------------------
set NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
set echo on
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE

# ------------------------------------------------------------------
# Log the start of this script.
# ------------------------------------------------------------------

echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

# -----------------------------------------------------------------
# Replace below, with the Oracle home path.
# -----------------------------------------------------------------
ORACLE_HOME=/oracle/app
export ORACLE_HOME

# ------------------------------------------------------------------
# Replace below, with the Oracle SID of the target database.
# ------------------------------------------------------------------
ORACLE_SID=orcl
export ORACLE_SID
 

# ------------------------------------------------------------------

# Replace below, with the Oracle DBA user id (account).
# ------------------------------------------------------------------

ORACLE_USER=oracle
# ------------------------------------------------------------------
# Set the target connect string.
# Replace "sys/manager", below, with the target connect string.
# ------------------------------------------------------------------

TARGET_CONNECT_STR=/
CATALOG_CONNECT_STR=catalog_login/password@catalog;

# ------------------------------------------------------------------
# Set the Oracle Recovery Manager name.
# ------------------------------------------------------------------

RMAN=$ORACLE_HOME/bin/rman

# ------------------------------------------------------------------
# Print out the value of the variables set by this script.
# ------------------------------------------------------------------

echo >> $RMAN_LOG_FILE
echo   "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo   "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo   "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo   "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE

# -----------------------------------------------------------------
# NOTE: This script assumes that the database is properly opened.

# If desired, this would be the place to verify that.
# -----------------------------------------------------------------

echo >> $RMAN_LOG_FILE

#
# -----------------------------------------------------------------
# Set initial script start time
c_begin_time_sec=`date +%s`

CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR catalog $CATALOG_CONNECT_STR msgno append << EOF
RUN {
  BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
  RESTORE DATABASE VALIDATE;
  RESTORE CONTROLFILE VALIDATE;
  RESTORE ARCHIVELOG ALL VALIDATE;
}
EOF
"
# Initiate the command string
/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?


c_end_time_sec=`date +%s`
echo >> $RMAN_LOG_FILE
v_total_execution_time_sec=`expr ${c_end_time_sec} - ${c_begin_time_sec}`
echo "Script execution time is $((v_total_execution_time_sec / 60)) Minutes" >> $RMAN_LOG_FILE
echo "Validation Script ran in record time." >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

# -----------------------------------------------------------------
# Log the completion of this script.
# ------------------------------------------------------------------
if [ "$RSTAT" = "0" ]
then
    LOGMSG="ended successfully"
else
    LOGMSG="ended in error"
fi

echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

#
# Email log file and status to DBA
cat $RMAN_LOG_FILE | mailx -s "$((v_total_execution_time_sec / 60)) Mins. ${ORACLE_SID} on `uname -n` Validation Script $LOGMSG - EXIT Status = $RSTAT" howard@email.com

exit $RSTAT

 

Oracle RMAN Maintenance script

When using an RMAN Catalog to backup your database (Highly Recommended), it is important to run Maintenance scripts on it to clean up old backups and to maintain your catalog. This is the entire bash script called from cron one a week. Usually on a Sunday. Highlighted in yellow are lines you would need to customize to your environment.

The portion of the RMAN script is here. Below this is the entire script.

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
LIST INCARNATION OF DATABASE dev;
REPORT UNRECOVERABLE;
REPORT SCHEMA;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP OF DATABASE;
DELETE NOPROMPT EXPIRED BACKUP OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP OF CONTROLFILE;
LIST BACKUP OF DATABASE SUMMARY;
REPORT NEED BACKUP;
REPORT OBSOLETE ORPHAN;
REPORT OBSOLETE;
DELETE NOPROMPT OBSOLETE;

RMAN Maintenance script.

maintenance.sh

#!/bin/sh
# ------------------------------------------------------------------
# Determine the user which is executing this script.
# ------------------------------------------------------------------

CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`

# ------------------------------------------------------------------
# Put output in .out. Change as desired.
# Note: output directory requires write permission.

# RMAN_LOG_FILE=${0}.out
RMAN_LOG_FILE=${0}.`date +%Y%m%d_%H%M%S`.out

# ------------------------------------------------------------------
# You may want to delete the output file so that backup information does
# not accumulate.  If not, delete the following lines.
# ------------------------------------------------------------------

if [ -f "$RMAN_LOG_FILE" ]
then
        rm -f "$RMAN_LOG_FILE"
fi

# -----------------------------------------------------------------
# Initialize the log file.
# -----------------------------------------------------------------
set NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
set echo on
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE

# ------------------------------------------------------------------
# Log the start of this script.
# ------------------------------------------------------------------

echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

# ------------------------------------------------------------------
# Replace below, with the Oracle home path.
# ------------------------------------------------------------------
ORACLE_HOME=/oracle/app
export ORACLE_HOME

# ------------------------------------------------------------------
# Replace below, with the Oracle SID of the target database.
# ------------------------------------------------------------------

ORACLE_SID=orcl
export ORACLE_SID

# ------------------------------------------------------------------
# Replace below, with the Oracle DBA user id (account).
# ------------------------------------------------------------------

ORACLE_USER=oracle
# ------------------------------------------------------------------
# Set the target connect string.
# Replace "sys/manager", below, with the target connect string.
# ------------------------------------------------------------------

TARGET_CONNECT_STR=/
CATALOG_CONNECT_STR=catalog_login/password@catalog;

# ------------------------------------------------------------------
# Set the Oracle Recovery Manager name.
# ------------------------------------------------------------------

RMAN=$ORACLE_HOME/bin/rman

# -----------------------------------------------------------------
# Print out the value of the variables set by this script.
# -----------------------------------------------------------------


echo >> $RMAN_LOG_FILE
echo   "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo   "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo   "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo   "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE

# ------------------------------------------------------------------
# NOTE: This script assumes that the database is properly opened.

# If desired,
# this would be the place to verify that.
# ------------------------------------------------------------------

echo >> $RMAN_LOG_FILE

#
# ------------------------------------------------------------------
# Set initial script start time
c_begin_time_sec=`date +%s`

CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR catalog $CATALOG_CONNECT_STR msgno append << EOF
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
LIST INCARNATION OF DATABASE dev;
REPORT UNRECOVERABLE;
REPORT SCHEMA;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP OF DATABASE;
DELETE NOPROMPT EXPIRED BACKUP OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP OF CONTROLFILE;
LIST BACKUP OF DATABASE SUMMARY;
REPORT NEED BACKUP;
REPORT OBSOLETE ORPHAN;
REPORT OBSOLETE;
DELETE NOPROMPT OBSOLETE;
EOF
"
# Initiate the command string
/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?


c_end_time_sec=`date +%s`
echo >> $RMAN_LOG_FILE
v_total_execution_time_sec=`expr ${c_end_time_sec} - ${c_begin_time_sec}`
echo "Script execution time is $((v_total_execution_time_sec / 60)) Minutes" >> $RMAN_LOG_FILE
echo "Maintenance Script ran in record time." >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

# ------------------------------------------------------------------
# Log the completion of this script.
# ------------------------------------------------------------------
if [ "$RSTAT" = "0" ]
then
    LOGMSG="ended successfully"
else
    LOGMSG="ended in error"
fi

echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

#
# Email log file and status to DBA
cat $RMAN_LOG_FILE | mailx -s "$((v_total_execution_time_sec / 60)) Mins. ${ORACLE_SID} on `uname -n` Maintenance Script $LOGMSG - EXIT Status = $RSTAT" howard@email.com

exit $RSTAT

 

Oracle backup scripts and monitoring the results

Oracle on Linux Redhat
Backing up your database must include Full, Incremental, and maybe Archivelog backups.
I like to do a weekly Full (Friday) and daily Incremental backups (Monday - Thursday). I also like to do daily Archivelog backups to keep the Archivelog destination from filling up on unexpectedly heavy transaction days.

RMAN Full backup script
RMAN Incremental backup script
RMAN Archivelog backup only script

As well it is important to run weekly RMAN catalog Maintenance and RMAN Validation script.

Archivelog backups script

file name archivelogs_only.sh

Call from crontab at your desired iteration.

Here it is called everyday at 15:00

#Oracle Archivelog backups

0 15 * * * /oracle/scripts/archivelogs_only.sh

archivelogs_only.sh

#!/bin/sh

# ----------------------------------------------------
# Determine the user which is executing this script.
# ----------------------------------------------------

CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`

# Set begin time of script
c_begin_time_sec=`date +%s`

# ----------------------------------------------------
# Put output in .out. Change as desired.
# Note: output directory requires write permission.

RMAN_LOG_FILE=${0}.`date +%Y%m%d_%H%M%S`.out

# ----------------------------------------------------
# You may want to delete the output file so that

# backup information does not accumulate.

#  If not, delete the following lines.
# ----------------------------------------------------

if [ -f "$RMAN_LOG_FILE" ]
then
        rm -f "$RMAN_LOG_FILE"
fi

# ----------------------------------------------------
# Initialize the log file.
# ----------------------------------------------------
set NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
set echo on
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE

# ----------------------------------------------------
# Log the start of this script.
# ----------------------------------------------------

echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

# ----------------------------------------------------
# Replace below, with the Oracle home path.
# ----------------------------------------------------

ORACLE_HOME=/oracle/app/
export ORACLE_HOME

# ----------------------------------------------------
# Replace below, with the Oracle SID of

# the target database.
# ----------------------------------------------------

ORACLE_SID=ORCL
export ORACLE_SID

# ----------------------------------------------------
# Replace below, with the Oracle DBA user id (account).
# -----------------------------------------------------

ORACLE_USER=oracle
# -----------------------------------------------------
# Set the target connect string.
# Replace "/", below,

# with the target connect string.
# -----------------------------------------------------

TARGET_CONNECT_STR=/
CATALOG_CONNECT_STR=catalog_login/password@sid_of_catalog;

# -----------------------------------------------------
# Set the Oracle Recovery Manager name.
# -----------------------------------------------------

RMAN=$ORACLE_HOME/bin/rman

# -----------------------------------------------------
# Print out the value of the variables set by this script.
# -----------------------------------------------------


echo >> $RMAN_LOG_FILE
echo   "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo   "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo   "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo   "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE

# -----------------------------------------------------
# NOTE: This script assumes that the database

# is properly opened. If desired,
# this would be the place to verify that.
# -----------------------------------------------------

echo >> $RMAN_LOG_FILE

# ---------------------------------------------------------------------------

# Call Recovery Manager to initiate the backup.

# ---------------------------------------------------------------------------

c_begin_time_sec=`date +%s`

CMD_STR="

ORACLE_HOME=$ORACLE_HOME

export ORACLE_HOME

ORACLE_SID=$ORACLE_SID

export ORACLE_SID

$RMAN target $TARGET_CONNECT_STR catalog $CATALOG_CONNECT_STR msgno append << EOF

# $RMAN target $TARGET_CONNECT_STR msgno append << EOF

RUN {

sql 'alter system archive log current';

# backup archive logs

BACKUP

    filesperset 25

    ARCHIVELOG ALL;

DELETE NOPROMPT ARCHIVELOG

# Update how many days to keep Archivelog files on disk.

# This is one day.

    UNTIL TIME 'SYSDATE - 1';

}

EOF

"

# Initiate the command string

/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE

RSTAT=$?

c_end_time_sec=`date +%s`

echo >> $RMAN_LOG_FILE

v_total_execution_time_sec=`expr ${c_end_time_sec} - ${c_begin_time_sec}`

echo "Script execution time is $((v_total_execution_time_sec / 60)) Minutes" >> $RMAN_LOG_FILE

echo "Archive Log script record time. Amazing!" >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE

# ---------------------------------------------------------------------------

# Log the completion of this script.

# ---------------------------------------------------------------------------

if [ "$RSTAT" = "0" ]

then

    LOGMSG="ended successfully"

else

    LOGMSG="ended in error"

fi

echo >> $RMAN_LOG_FILE

echo Script $0 >> $RMAN_LOG_FILE

echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE

#

# Email log file and status to DBA

cat $RMAN_LOG_FILE | mailx -s "$((v_total_execution_time_sec / 60)) Mins. ${ORACLE_SID} on `uname -n` Archive Log Backup $LOGMSG - EXIT Status = $RSTAT" Your_email@Company.com

exit $RSTAT

Incremental backups

Same as Full Backup script with the following exception.
ORA_FULL=1 to ORA_FULL=0
and 
ORA_INCR=0 to ORA_INCR=1


See portion of script below

#     schedule type is                BACKUP_TYPE is
#     ----------------                --------------
# Automatic Full                     INCREMENTAL LEVEL=0
# Automatic Differential Incremental INCREMENTAL LEVEL=1
# Automatic Cumulative Incremental   INCREMENTAL LEVEL=1 CUMULATIVE
#
# Note that we use incremental level 0 to specify full backups.
# That is because, although they are identical in content, only
# the incremental level 0 backup can have incremental backups of
# level > 0 applied to it.
# ----------------------------------------------------

ORA_FULL=0;
ORA_INCR=1;
ORA_CINC=0;

Oracle - Check for failed login attempts

A great security measure is to monitor who is trying to unsuccessfully log into your database. Whether it's an innocent developer who forgot their password, someone trying to use an account that is not their's, or worse - some outside intrusion hacker or snooper just trying to get in. You should be aware when it happens so you can understand why and put an end to it. I like to run this scripts through cron several days a week. A quick look at it ensures me there are no outsiders or alerts me to such.

Script consists of a cron script and an sql script on the host server.
failed_logins.sh

#!/bin/tcsh -f
source ~oracle/.cshrc
setenv VDATE `date '+%m/%d/%y %A %X'`
setenv MAILGRP "email@company.com"
setenv scrpt [script location]

sqlplus -SILENT user/password@ORCL < ${scrpt}/failed_logins.sql

cat failed_logons.out | /bin/mailx -s "${ORACLE_SID} - `uname -n` : Logins attempted Report for $VDATE" \
$MAILGRP
exit

SQLPLUS script
failed_logins.sql

set termout off
spool failed_logons.out
set linesize 232 tab off trimspool on
set pagesize 105
set pause off
set echo on
set feedb off
set heading off

column "EVENT_TIMESTAMP" format a40
column "OS_USERNAME" format a15
column "USERHOST" format a15
column "DBUSERNAME" format a10
column "CLIENT_PROGRAM_NAME" format a35
column "ACTION_NAME" format a10
column "UNIFIED_AUDIT_POLICIES" format a20

select 'Login Report for '||TO_CHAR(sysdate,'DAY DD-MON-YYYY HH24:MI:SS') "Title" from dual
/

select 'EVENT_TIMESTAMP------------------OS_USERNAME--USERHOST---DBUSERNAME--CLIENT_PROGRAM_NAME----ACTION_NAME--POLICY' from dual;
                                                             
SELECT TO_CHAR(event_timestamp,'YYYY-MM-DD HH24:MI:SS') EVENT_TIMESTAMP,
os_username OS_USERNAME,
userhost USERHOST,
dbusername DBUSERNAME,
client_program_name CLIENT_PROGRAM_NAME,
action_name ACTION_NAME,
unified_audit_policies UNIFIED_AUDIT_POLICIES
FROM unified_audit_trail
where action_name = 'LOGON'
and unified_audit_policies = 'ORA_LOGON_FAILURES'
and trunc(event_timestamp) > trunc(sysdate)-10
ORDER BY event_timestamp desc
/
spool off