Oracle Database Administration Scripts | DBA Bundle

If you were googling for a DBA scripts/tools to help you out with managing the mighty Oracle DB then you have landed on the right blog :-)
I know you're super busy and have no time for reading long articles, so let me jump to the bottom line, the DBA Bundle is a group of shell scripts bundled all together in one tar file, once deployed on the system it allows you to execute different tasks using one single Linux command alias. Its main goal is to simplify your day-to-day common/complex DBA tasks.

DOWNLOAD the latest version from this link:
https://www.dropbox.com/s/le5pxctq0w6v49u/DBA_BUNDLE7.tar?dl=0

 
After opening the link, click on the Down Arrow on the right side:

In case the Dropbox website is blocked in your area, or you cannot copy files directly to the server you want to operate the bundle from due to security restrictions; then do this small trick:

1- On the Server, create a new empty file with name DBA_BUNDLE7.txt:
     # touch DBA_BUNDLE7.txt

2- Open below link and Copy its content (Ctrl+a then Ctrl+c) and paste it in the DBA_BUNDLE7.txt file:

     https://gist.github.com/haballan/aa52fbb1f8e497db989f19ef046c7939

3- Execute this command to convert the text file to a tar file:
     # LC_ALL=C tr -cd 0-9a-fA-F < DBA_BUNDLE7.txt | xxd -r -p > DBA_BUNDLE7.tar

Then continue to follow below instructions ... 

What this Bundle can do?
In a nutshell, it's a multi-tool can easily get deployed on any Oracle server. It will help you out to open the database/ASM instance alertlog, Clusterware log, listener config file, display the Clusterware services status, display the tablespaces/ASM diskgroups utilization, display the active sessions, display the blocking locks, display the invalid objects, display audit records, gather database statistics, perform data export, take an RMAN/Cold backup and more and more of such activities using ONE single command from OS shell for each activity without the need to google for the commands you want to use!

How it works: First, download the bundle tar file and extract it under Oracle owner home directory, e.g. /home/oracle  
Second, go to the bundle extracted location and execute "aliases_DBA_BUNDLE.sh" script, using "." command
e.g.
.  aliases_DBA_BUNDLE.sh
This script will get you up and running with the bundle by doing two things:
1- Set the default database environment, so whenever you run any of common aliases like "alert, tns, bdump,..." it will automatically point to the respective files for the default database.
e.g. if you have two running instances (orcl & salesdb), and you want to open the alertlog for salesdb, just run ". aliases_DBA_BUNDLE.sh"  script, enter salesdb number from the displayed list and then type alert/vialert to view salesdb alertlog file.
2- Add the bundle aliases inside the user's profile to make it easy for you to call any script from any working directory using one command alias without the need to step under the bundle directory.
3-At the end it will display all the aliases along with their description in a tabular format. So, you don't have to memorize the aliases, later you can execute "bundle" command to swap between default databases or to view the aliases' description.

For interactive scripts, whenever you execute any of them you will be prompted for the database number to enter in case you have more than one up & running database on the same machine.

Scripts Description:
Now let me give you a brief description for each script in the bundle:

Generic Aliases	Associated with the Default Selected Database
Shell Alias	Description
bundle	Set a database as a default database (all generic aliases will be associated to this database)
alert	Open the Database Alertlog with tail -f
vialert	Open the Database Alertlog with vi editor
raclog	Open the Clusterware/Oracle Restart Alertlog
sql	Open sqlplus ‘/ as sysdba’
p	List all Running Database Instances (PMON Processes)
lsn	List Running Listeners
lis	Open listener.ora file with vi editor
tns	Open tnsnames.ora file with vi editor
pfile	Open the default instance PFILE with vi editor
spfile	Open the default instance SPFILE with view editor
oh	Go to $ORACLE_HOME directory
dbs	Go to $ORACLE_HOME/dbs
aud	Go to $ORACLE_HOME/rdbms/audit
bdump	Go to BACKGROUND_DUMP_DEST
network	Go to $ORACLE_HOME/network/admin
removebundle	Remove all the bundle Aliases from the System. (When you run it, let me know the reason)

Scripts With aliases
Script Name	Shell Alias	Description
table_info.sh	tableinfo	Show specific table’s important info (size, indexes, non indexed FK, constraints,...).
oradebug.sh	oradebug	Generate Hang Analysis report using oradebug tool in case of instance hang. (New script in V3.6)
active_sessions.sh	active	Show the current active sessions and their blocking sessions, along with long running operations + Current running jobs + long running queries. (New script in V3.6)
session_details.sh	session	List the Details of a specific user session. (If no input provided, it will list all sessions on the instance).
all_sessions_info.sh	sessions	List All connected sessions on all running instances [RAC DB] along with their distribution in details.
process_info.sh	spid	Show the DB Session details when providing its Unix PID.
sql_id_details.sh	sqlid	Show the details of a specific SQL STATEMENT by providing its SQL_ID and gives you the option of tuning it using SQL TUNING ADVISOR. Co-author: Farrukh Salman [Karkoor]
asmdisks.sh	asmdisks	Show ASM Diskgroups and their size, ASM disks, ASM disks mount points On OS.
tablespaces.sh	tbs	List All TABLESPACES, ASM Disk Groups and FRA (if was configured) allocated size and free space details.
datafiles.sh	datafiles	List All DATAFILES and their size.
export_data.sh	exportdata	Export Full DB|SCHEMA|TABLE data. (Gives you the option of using exp or expdp utility for the export).
RMAN_full.sh	rmanfull	Takes an online RMAN full backup for the database (gives you the option of number of channels/compressed/Encrypted backup type).
Archives_Delete.sh	archivedel	Delete all Archive logs older than (provided) number of days.
analyze_objects.sh	Analyze	Analyze All tables under a specific SCHEMA (using ANALYZE legacy command).
gather_stats.sh	gather	Gather STATISTICS on a SCHEMA or TABLE using DBMS_STATS. http://dba-tips.blogspot.ae/2014/09/script-to-ease-gathering- statistics-on.html
rebuild_table.sh	tablerebuild	Rebuild a table and its related indexes.
db_locks.sh	locks	List Blocking LOCKS details on the database (blocking users, blocking locks on objects, long running operations).
db_jobs.sh	jobs	List All database Jobs (dba_jobs + dba_scheduler_jobs + Auto Tune Tasks and current running jobs and their wait status) + job DDL & its history if provided its name/number.
invalid_objects.sh	invalid	List All invalid Objects on the DB + their compile statements.
biggest_100_objects.sh	objects	List the Biggest 100 Objects on the database.
object_size.sh	objectsize	Calculate any object size + its indexes size.
lock_user.sh	lockuser	Lock a specific DB User Account and expire the password.
unlock_user.sh	unlockuser	Unlock a specific DB User Account + the option of reset the account password.
audit_records.sh	audit	Retrieve AUDIT data for a DB user in a specific date or number of days back. http://dba-tips.blogspot.ae/2014/02/extract-oracle-audit-records- script.html
last_logon_report.sh	lastlogin	Show the last login date of ALL users in the database.
failed_logins.sh	failedlogin	Show the failed login attempts in the last provided n number of days.
parameter_val.sh	parm	Show the value of a Visible/Hidden initialization Parameter.
user_details.sh	userdetail	Generate the DDL Creation script for a DB user + its privileges important info about its schema and objects.
user_ddl.sh	userddl	Generate the DDL Creation script for a DB user + its privileges important info about its schema and objects.
object_ddl.sh	objectddl	Generate DDL script for a database Object + its granted permissions on it.
role_ddl.sh	roleddl	Generate DDL script for a database role.
start_tracing.sh	starttrace	Start TRACING an Oracle session activities in a trace file.  http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html
stop_tracing.sh	stoptrace	Stop TRACING an already traced Oracle session & provide the session’s trace file and its TKPROFED log version.  http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html
oracle_cleanup.sh	cleanup	Backup & Clean up All DBs & Listeners’ Logs. http://dba-tips.blogspot.ae/2014/02/oracle-logs-cleanup-script.html

Scripts Without aliases
Script Name	Description
dbalarm.sh	Monitors ALERTLOGs of ALL Databases and Listeners log running on the server and instantly report ORA- errors and TNS- errors that appears in these logs to the DBA E-mail Address by sending a detailed email to the DBA along with monitoring CPU, Filesystem/FRA/Tablespaces utilization, blocking locks. (you have to modify this parameter in line number 27 to point to your E-mail Address): MAIL_LIST="youremail@yourcompany.com" Note: sendmail service should be configured on the server. *The best way to use this script is by schedule it to run in the crontab every 5 minutes (or less). For more details: http://dba-tips.blogspot.ae/2014/02/database-monitoring-script- for-ora-and.html
dbdailychk.sh	Perform the following health checks on all running databases on the server: # CHECKING ALL DATABASES ALERTLOGS FOR ERRORS. # CHECKING ALL LISTENERS ALERTLOGS FOR ERRORS. # CHECKING CPU UTILIZATION. # CHECKING FILESYSTEM UTILIZATION. # CHECKING TABLESPACES UTILIZATION. # CHECKING FLASH RECOVERY AREA UTILIZATION. # CHECKING ASM DISKGROUPS UTILIZATION. # CHECKING BLOCKING SESSIONS ON THE DATABASE. # CHECKING UNUSABLE INDEXES ON THE DATABASE. # CHECKING INVALID OBJECTS ON THE DATABASE. # CHECKING FAILED LOGIN ATTEMPTS ON THE DATABASE. # CHECKING AUDIT RECORDS ON THE DATABASE. # CHECKING CORRUPTED BLOCKS ON THE DATABASE. # CHECKING FAILED JOBS IN THE DATABASE. # CHECKING ACTIVE INCIDENTS. # CHECKING OUTSTANDING ALERTS. # CHECKING DATABASE SIZE GROWTH. # CHECKING OS / HARDWARE STATISTICS. # CHECKING RESOURCE LIMITS. # CHECKING RECYCLEBIN. # CHECKING CURRENT RESTORE POINTS. # CHECKING HEALTH MONITOR CHECKS RECOMMENDATIONS THAT RUN BY DBMS_HM PACKAGE. # CHECKING MONITORED INDEXES. # CHECKING REDOLOG SWITCHES. # CHECKING MODIFIED INITIALIZATION PARAMETERS SINCE THE LAST DB STARTUP. # CHECKING ADVISORS RECOMMENDATIONS: Replace youremail@yourcompany.com template with your e-mail address. You can also customize the defined thresholds as per your preferences under THRESHOLD section inside the script. Last step, Schedule the script to run in the crontab e.g. one time early morning: 0 6 * * * /home/oracle/dbdailychk.sa For more details: http://dba-tips.blogspot.ae/2015/05/oracle-database-health-check- script.html
delete_standby_archives.sh	Deletes the applied Archives on STANDBY DATABASES older than N hours (specified by the user). To be customized and scheduled from the crontab. For more details: http://dba-tips.blogspot.ae/2017/01/script-to-delete-applied- archivelogs-on.html
COLD_BACKUP.sh	Takes a COLD BACKUP of a specific database (But the beauty of this script once it take the cold backup it will generate another script to help you to restore the taken cold backup easily) This script will perform the following activities: shutdown the database, take a cold backup, create a restore script (in case you want to restore this cold backup later) then it will automatically startup the database. For more Details: http://dba-tips.blogspot.ae/2014/02/cold-backup-script.html
SHUTDOWN_All.sh	SHUTDOWN ALL running Databases & Listeners on The server. Keep away from children :-)
schedule_rman_full_bkp.sh	Takes an RMAN Full backup for a specific database. Can be scheduled in the crontab. You MUST adjust the variables/channels/maintenance section to match your environment.
schedule_rman_image_copy_bkp.sh	Takes an RMAN Image/Copy for a specific database. Can be scheduled in the crontab. You MUST adjust the variables/channels/maintenance sections to match your env. Why consider RMAN image backups in your backup strategy? the answer is in this link: http://dba-tips.blogspot.ae/2011/11/switch-database-to-rman-copy-backup-and.html
delete_applied_archives_on_standby.sh	Deletes the applied archivelogs on a standby DB. For More Details: http://dba-tips.blogspot.com/2017/01/script-to-delete-applied-archivelogs-on.html
configuration_baseline.sh	Collects all kind of configuration baseline data for OS and all running DATABASES to help you track and control the changes on your environment. For more details: http://dba-tips.blogspot.com/2016/12/configuration-baseline-script-for-linux.html
backup_ctrl_spf_AWR.sh	Backup Controlfile as (Trace/RMAN BKP), Backup SPFILE and Generate AWR for full day This script can be scheduled in the crontab to run once a day. Script options/variables must be modified to match your environment. New Starting from V4.1
kill_long_running_queries.sh	Kill all queries running longer than 2.5 Hours(can be customized inside the script) by specific modules (to be specified inside the script) This script can be scheduled in the crontab. Script options/variables MUST be modified to get the killing criteria match your requirements. New Starting from V4.1
check_standby_lag.sh	If you have a standby DB then you can use this script on Primary DB site to report to you any LAG happens between the primary and the standby DB. The variables section at the top of the script must be populated by you to match your environment or the script will not be able to run. This script can be scheduled in the crontab to run every 5 minutes. New Starting from V4.1 This link will show you how to use this script: http://dba-tips.blogspot.ae/2017/11/shell-script-to-check-lag-sync-status.html

From time to time I'll keep updating this bundle with new scripts, fixing bugs and adding new features, so give this topic a visit at least once every 3 months to download the latest version.

As I mentioned inside each script, I'M SHARING THIS BUNDLE AND ITS SCRIPTS IN THE HOPE THAT IT WILL BE USEFUL FOR YOU, BUT WITHOUT ANY WARRANTY. ALL SCRIPTS IN THIS BUNDLE ARE PROVIDED "AS IS".
No one is perfect... that's why pencils have erasers.

Your suggestions, bug reporting, and comments are most welcome :-)

Lastly, A special thank you to the beautiful mind Abd El-Gawad Othman, without his support, suggestions, and encouragement I wouldn't be confident enough to share this bundle with you.

Script to Export Database | Schema | Table using (Data pump expdp or Legacy export exp)

Update on 05-Feb-2020: 
This post is not valid anymore. Sorry for inconvenience! 

A new script has been released, please follow this link to get the final script:
http://dba-tips.blogspot.com/2020/02/new-script-for-exporting-data.html



Creating a script to export the data is not a big deal, but importing the dump file is a time-consuming job for the DBA. export_data.sh script will not only do the export job, but it will help you out with the import steps when you decide to import the dumpfile, it lists the DDL statements and grants that you need to run before and after the import process in a separate script. I consider this function the most useful feature in this script.

The script gives you the options to export Full Database, Schema or table using either Legacy export utility exp or the modern Data Pump utility expdp.
This script was tested on 10g, 11g and 12c on Linux & SUN environments.

How it works:

First download the script from here:
https://www.dropbox.com/s/hk0pfo2tanop35r/export_data.sh?dl=0

Second run the script, if you have multiple running database on the server it will give you the option to select the database you want to export data from.

> It will ask you the location you want to save the dump file

WHERE TO SAVE THE EXPORT FILE [DUMPFILE]? [ENTER THE FULL PATH]

> It will ask you if you want to export the FULL DATABASE.

Do you want to EXPORT FULL DATABASE? [Y|N] [Y] [N TO EXPORT SCHEMA|TABLE]

    [If your answer is "no"]
      > It will ask you if you want to export a SCHEMA.

Do you want to EXPORT a SCHEMA? [Y|N] [Y] [N If you want to EXPORT TABLE]

          [If your answer is "no"]
             >It will go into the export table mode:
                [Enter the Owner then

Please Enter the TABLE OWNER:

                  Enter the Table name]

Please Enter the TABLE NAME:

> It will ask you to select the utility you want to perform the export with [expdp or exp]

WHICH EXPORT UTILITY YOU WANT TO USE: [1) DATAPUMP [EXPDP]]


====================================


1) DATAPUMP [EXPDP]


2) LEGACY EXPORT [EXP]

> Then let the script do the remaining steps:
    - If EXPDP was selected to export the data, the script will automatically calculate the degree of parallelism based on the number of core CPUs on the server. [By the way parallelism is a buggy feature in EXPDP]
    - It will create a user called DBA_BUNDLEEXP7, granting it dba privilege to use it in the export
       (I'm creating this user because using a sysdba user will disable functions like consistent=y during the export)
    - It will create a directory called EXPORT_FILES_DBA_BUNDLE pointing to the dump location
       you've entered earlier [this in case you selected expdp utility to perform the export job].
    - It will create a "BEFORE IMPORT SCRIPT" [In case you selected export Schema mode earlier]
       this script will include the [creation statement of  roles assigned to the user, user creation statement,
       grant privileges/roles, grant privileges the user has on other schemas objects.
     - It will create an "AFTER IMPORT SCRIPT" [In case you selected export Database mode earlier]
        this script will hint you all triggers owned by SYS user  [if exist]. These triggers will not be created
        during the import process.
     - It will create an "AFTER IMPORT SCRIPT" [In case you selected export Schema mode earlier]
        this script will include the [creation statements of public synonyms for user;s table [if exist],
        grant privileges on schema objects to other users [if exist], giving you a hint for the triggers owned
        by other users that pointing to the exported schema tables [if exist], recompile invalid objects.
     - It will create an "AFTER IMPORT SCRIPT" [In case you selected export Table mode earlier]
        this script will create the public synonyms for exported table [if exist].
     - The script will start the export job using DBA_BUNDLEEXP7 user.
     - Once the export job finish it will drop the DBA_BUNDLEEXP7 user.
     - At the end the script will list to you "Import Guidelines" including the
        BEFORE/AFTER import scripts locations. Finally, it will print the full path of the dumpfile.

If you still not OK with that introduction don't worry the script is self-explanatory :-)
Please note that COMPRESSION option is used by default in the export process.

At any stage, you can terminate the script by pressing [Ctrl+c]

This script is part of DBA BUNDLE, to read more about it please visit this link:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

The following is the code: [in case the download link is not working]

Extract Oracle Audit Records Script

Today I'll share with you one of my scripts I'm using to easily retrieve the audit records of an oracle database user.

Note: Auditing should be enabled on the database or the script will return no rows.
              To enable auditing please check this article:
              http://www.oracle-base.com/articles/10g/auditing-10gr2.php

Download the script from this link:

https://www.dropbox.com/s/24ht510venaogwn/audit_details.sh?dl=0

Once you run this script it will let you choose the database you want to retrieve data from (in case that you have multiple running databases on the server), then it will ask you to enter the username, and lastly will ask you to enter the number of days back you want to retrieve audit data or enter a specific date.

This script is very easy to use, it has been tested on Linux and SUN environments.

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

Here is the script code, in case the download link is not working with you:

Script to TRACE Oracle Session

In order to simplify the steps of enabling, stopping the trace on Oracle sessions and make the output trace data readable, I've written two scripts, start_tracing.sh will let you enable tracing on a specific session, and stop_tracing.sh to stop tracing the session and provide the original trace file along with another readable version of the trace file using TKPROF utility.

Download links:

start_tracing.sh
https://www.dropbox.com/s/dkhjteqckcuykqd/start_tracing.sh?dl=0

stop_tracing.sh
https://www.dropbox.com/s/41krahxx8m67uyg/stop_tracing.sh?dl=0

Those scripts are part of the DBA bundle which equipped with many of easy to use scripts that simplify day to day database administration tasks:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

start_tracing.sh


stop_tracing.sh

Oracle Logs CLEANUP Script

In this post, I'll share a script to backup & cleanup Oracle logs associated with a specific database on the Oracle server.

This script was tested on Linux & SUN environments.

You can download the script from here:
https://www.dropbox.com/s/eytsv5duxe95lrh/oracle_cleanup.sh?dl=0

Once you run it, it will ask you to select a database (in case you have multiple running databases on the server), then it will ask you the location you want to back up the logs, then it will start to clean up all logs under udump, bdump, cdump folder plus the audit logs and also will clean up the logs of the listener associated with the selected database.

Note: This script will backup and delete all logs and will keep the logs of the last 5 days only.

Note: it's recommended to test this script on a test environment before you run it on production.

Also, you can download the whole DBA bundle which having many other smart and easy to use scripts for database administration tasks:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

Below is the code of this script in case the download link didn't work:

Oracle Database COLD BACKUP Shell Script

Creating a script to take a cold backup of a database may be a time-consuming task, but creating a script to restore this cold backup later is certainly a daunting task.

The shell script I'm sharing will take a database cold backup and then will create a restore script (to be used in case you will restore the cold backup later).

Script download:
https://www.dropbox.com/s/sjibiupwic9oxt1/COLD_BACKUP.sh?dl=0

How it works:

The script checks the current running databases on the server and will ask you to select the number of the database you want to back up from the list (The database you want to back it up should be up and running in order to get the script know the database files location).

It will shut down the database, take a cold backup, create a script to restore the cold backup and finally startup the database.

This script is RAC aware, it will detect if your database is RAC or a standalone one, if it's a RAC DB the script will ensure that there is no other instances are currently running for the same database before starting the cold backup procedure.

Note: This script is not designed for databases having their files located on ASM  :-)

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

The script is part of the database administration bundle, this bundle includes more than 45 scripts covering day to day database administration tasks, you can download the DBA BUNDLE from here: 
[http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html.

Oracle Database Monitoring Script

The backbone monitoring solution for Oracle databases in my environment is dbalarm.sh script. It's a kind of "deploy and forget" script where it can monitor lots of database system components in one go.

The script can report/monitor Database, ASM, Clusterware, Golden Gate and OS main events, including:
  
- Database Monitoring:
  - Monitor the DB instance ALERT LOG file and report ORA and TNS errors.
  - Monitor TABLESPACES utilization.  - Monitor FLASH RECOVERY AREA (FRA) utilization.
  - Monitor ASM Disk Groups utilization.
  - Monitor BLOCKING LOCKS.
  - Monitor database named SERVICES. [Service names to be provided to SERVICEMON variable]
  - Monitor RMAN Backup Failure.
  - Monitor the database if it goes OFFLINE or gets HANGED.
  When the Paranoid mode is set to ON:
  - Startup/Shutdown events of the DB instances will be reported.
  - ALTER SYSTEM commands will be reported.
  - ALTER DATABASE commands will be reported.
  - EXPORT/IMPORT operations will be reported.

- Listener Monitoring:
  - Monitor the LISTENERS' LOG file and report TNS errors which plays a crucial rule in reporting connectivity security breaches, applications misconfiguration and network failures.

- Grid Infrastructure Monitoring:
  - Monitor the ASM alert log for ORA and TNS errors.
  - Monitor the Grid Infrastructure alert log for ORA and TNS errors.
  - Monitor the Grid Infrastructure alert log for Shutdown/Startup events.
  - Monitor the Grid Infrastructure alert log for Node eviction events.
  - Monitor the Grid Infrastructure alert log for Network IP conflict.
  - Monitor the Grid Infrastructure alert log for Heart Beat failures.
  - Monitor the Grid Infrastructure alert log for service failure events.

- OS/Hardware Staff:
  - Monitor the CPU for high utilization.
  - Monitor the Filesystems / Mount Points space utilization.
  - Monitor dmesg log for new entries which let you know what is going on on the OS side.

- Golden Gate Monitoring:
  - Monitor the Golden Gate log for Errors and Process ABENDED events.

How it works?

The script is coded to send only new errors that are triggered since the last execution of the script, once the error gets reported once, it won't be reported again unless it appears again in the alert log file.

In order to tailor the script to fulfill your requirements, you have to follow the following simple steps:

Step 1:

Download the script from this link:

https://www.dropbox.com/s/a8p5q454dw01u53/dbalarm.sh?dl=0

Step 2:

Open the script and change the E-mail address to your email address in the line# 60

EMAIL="youremail@yourcompany.com"

Note: sendmail service should be configured on your server to allow emails to be sent out from the DB machine.

Step 3:
By Oracle user:

In the crontab, schedule the script to run at least every 5 minutes:

# crontab -e

#Add this line: 

*/5 * * * * /home/oracle/dbalarm.sh

Note: /home/oracle/dbalarm.sh is the full path that points to dbalarm script where /home/oracle is the Oracle user home directory.

In case you will schedule this script to run from root user's crontab: [Not recommended]

# crontab -e

#Add this line to schedule the run of dbalarm.sh script every 5minutes:

*/5 * * * * su - oracle -c /home/oracle/dbalarm.sh

Now the only thing left here is to set back and relax and the script will report you all errors and all the breached predefined threshold.

One thing more, you can adjust the threshold inside the script as per your preferences by altering the below red colored values in THRESHOLDS section inside the script:

# #########################
# THRESHOLDS:
# #########################
# Modify the THRESHOLDS to the value you prefer:

HTMLENABLE=Y       # Enable HTML Email Format[DB]

FSTHRESHOLD=95 # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95  # THRESHOLD FOR CPU %UTILIZATION [OS]
TBSTHRESHOLD=95 # THRESHOLD FOR TABLESPACE %USED [DB]
FRATHRESHOLD=95 # THRESHOLD FOR FRA %USED [DB]
FSITHRESHOLD=95   # THRESHOLD FOR FILESYSTEM INODES %USED [OS]
ASMTHRESHOLD=95 # THRESHOLD FOR ASM DISK GROUPS [DB]
BLOCKTHRESHOLD=1          # THRESHOLD FOR BLOCKED SESSIONS count [DB]
WAIT_FOR_LOCK_THRES=1 # THRESHOLD FOR REPORTING BLOCKED SESSIONS BLOCKED FOR MORE THAN N MINUTES
LAST_MIN_BKP_CHK=5 # REPORT RMAN Backups FAILURE in the last N MINUTES. Should be the same interval as the execution interval of dbalarm script in crontab.
CHKRMANBKP=Y         # Enable/Disable Checking of RMAN Backup FAILURE. [Default Enabled]
CHKLISTENER=Y         # Enable/Disable Checking Listeners: [Default Enabled]
CHKOFFLINEDB=Y      # Enable/Disable Database Down Alert: [Default Enabled]
CHKGOLDENGATE=Y   # Enable/Disable Goldengate Alert: [Default Enabled]
CPUDIGMORE=Y          # Break down to DB Active sessions when CPU hit the threshold: [RECOMMENDED TO SET =N on VERY BUSY environments]
TIMEOUTDIGMORE=Y  # Enable/Disable the display of Network Errors when TIMEOUT error gets detected. [Default Enabled]

SERVICEMON=""       # Monitor Specific Named DB Services. e.g.  SERVICEMON="'ORCL_RO','ERP_SRVC','SAP_SERVICE'"

PARANOIDMODE=N    # Paranoid mode will report more events like export/import, instance shutdown/startup. [Default Disabled]
CHKASMALERTLOG=Y  # Enable/Disable Monitoring ASM instance ALERTLOG. [Default Enabled]
CHKCLSALERTLOG=Y  # Enable/Disable Monitoring GRID INFRASTRUCTURE ALERTLOG. [Default Enabled]
DEVICEDRIVERLOG=Y  # Enable/Disable Check "dmesg" Device Driver log for errors. [Default Enabled]

You can control script features like checking listener/offline databases/Golden Gate alert/show DB active sessions when CPU is high/monitor specific services by using Y to enable or N to disable it.

You can enable the script to do more checks like (reporting instance startup/shutdown, export/import operations, alter system/database commands and other major DB activities) when activating the Paranoid mode by setting its threshold PARANOIDMODE=Y

In addition, the script gives you the option to exclude specific database, tablespace, ASM Diskgroup, filesystm from having the script run against. You can do so by editing the following parameters:

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances dbalarm will IGNORE and will NOT run against:
# Use pipe "|" as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB="\-MGMTDB|ASM|APX"       #Excluded INSTANCES [Will not get reported offline].

# #########################
# Excluded TABLESPACES:
# #########################
# Here you can exclude one or more tablespace if you don't want to be alerted when they hit the threshold:
# e.g. to exclude "UNDOTBS1" modify the following variable in this fashion without removing "donotremove" value:
# EXL_TBS="donotremove|UNDOTBS1"
EXL_TBS="donotremove"

# #########################
# Excluded ASM Diskgroups:
# #########################
# Here you can exclude one or more ASM Disk Groups if you don't want to be alerted when they hit the threshold:
# e.g. to exclude "FRA" DISKGROUP modify the following variable in this fashion without removing "donotremove" value:
# EXL_DISK_GROUP="donotremove|FRA"
EXL_DISK_GROUP="donotremove"

# ################################

# Excluded FILESYSTEM/MOUNT POINTS:

# ################################

# Here you can exclude specific filesystems/mount points from being reported by dbalarm:

# e.g. Excluding: /dev/mapper, /dev/asm mount points:

EXL_FS="\/dev\/mapper\/|\/dev\/asm\/"                          #Excluded mount points [Will be skipped during the check].

Moreover, you can exclude specific ORA- TNS- errors in case you are OK with ignoring them, I've already excluded some minor frequent incoming errors to not get bothered about them:

# #########################
# Excluded ERRORS:
# #########################
# Here you can exclude the errors that you don't want to be alerted when they appear in the logs:
# Use pipe "|" between each error.

EXL_DB_ALERT_ERR="ORA-2396|TNS-00507|TNS-12502|TNS-12560|TNS-12537|TNS-00505"   #Excluded ALERTLOG ERRORS [Will not get reported].
EXL_LSNR_ERR="TNS-00507|TNS-12502|TNS-12560|TNS-12537|TNS-00505"  #Excluded LISTENER ERRORS [Will not get reported].
EXL_GRID_ALERT_ERR="donotremove"    #Excluded GRID INFRA ERRORS [Will not get reported].
EXL_GG_ERR="donotremove"                        #Excluded GoldenGate ERRORS [Will not get reported].
EXL_DMESG_ERR="donotremove|scsmd"     #Excluded OS DEVICE DRIVERS ERRORS [Will not get reported].

This script was tested on Linux environment.

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

If you're looking for a script to check the database health check on a daily basis, please follow this link: 
http://dba-tips.blogspot.ae/2015/05/oracle-database-health-check-script.html

More and more smart and "easy to use" scripts for database administration tasks can be found in the DBA Bundle:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

Finally, If you have an opinion/comment/suggestion please share it with me, your feedback is highly appreciated.

In case the download link is not working, you can find the full code below: