haballan’s gists

haballan / delete_applied_archives_on_standby.sh

Last active November 15, 2021 12:25

Shell Script To Delete applied archivelogs on standby DB http://dba-tips.blogspot.com/2017/01/script-to-delete-applied-archivelogs-on.html

	# #################################################################################################
	# [VER 1.4]
	# This Script deletes Applied Archives older than Specified N hours on STANDBY DATABASE
	# This script will run by default against ALL running STANDBY DATABASES.
	# Please read the following instructions on how to use this script:
	# - You can set MAIL_LIST variable to your E-mail to receive an email alert if archives
	# are not applied.
	# e.g. MAIL_LIST="john.smith@abc.com"
	# - You can specify the candidate archives for deletion older than N hours by setting
	# LAST_N_HOURS variable to the number of hours.

haballan / check_standby_lag.sh

Last active April 21, 2021 14:04

Shell Script To check the lag / synchronization status between Primary and Standby Oracle DBs http://dba-tips.blogspot.ae/2017/11/shell-script-to-check-lag-sync-status.html

	#!/bin/bash
	# #########################################################################################
	# This script MUST run from the Primary DB server.
	# It checks the LAG between Primary & Standby database
	# To be run by ORACLE user
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 29-10-2015 # # # # #
	# Modified: 21-04-2021 allowed a DBA user to be used for SQLPLUS login instead of SYSDBA
	# 21-04-2021 Simplified the Variables section for the end user.

haballan / report_long_runing_queries.sh

Last active February 3, 2021 21:38

Shell Script To monitor long running queries (running for more than 1 hour [Default]) | For Oracle database on Linux. How to use: http://dba-tips.blogspot.ae/2018/04/report-long-running-queries-long-active.html

	# ##################################################################################
	# Checking long running queries run by specific user
	# [Ver 1.2]
	#
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 09-03-17 # # # # #
	# Modified: 21-01-19 Enhanced the fetch for ORACLE_HOME.
	# 19-02-19 Enabled the HTML report version.
	# 06-07-20 New calculation method for 12c+ updated.

haballan / kill_long_running_queries.sh

Last active October 19, 2022 16:08

Shell Script To Kill long running queries that run from specific program e.g. SQL Developer | You have to specify ORACLE_SID, DURATION, MODULE_NAME inside the script. http://dba-tips.blogspot.com/2022/08/script-for-killing-long-running-queries.html

	# ################################################################################################################
	# Kill QUERIES/SESSIONS running for more than N minutes based on specific criteria.
	# ################################################################################################################
	VER="[2.5]"
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 11-01-18 # # # # #
	#
	# Modified: 17-08-22 Included the following filters:
	# INCLUDE_USERS & EXCLUDE_USERS They are MUTUALLY EXCLUSIVE, only one to be set at a time.

haballan / gather_stats.sh

Last active November 8, 2021 17:01

Linux Shell Script to gather statistics of a whole schema or a table and its indexes in nohup mode

	# #################################################################################################################
	# V. 2.2
	# Backup & Gather Statistics On SCHEMA\|TABLE.
	# To be run by ORACLE user
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 02-02-2014 # # # # #
	# Modified: 02-05-2018 Re-coded the script to run the gather stats commands in the background [nohup mode].
	# 14-01-2021 Checking the statistics LOCK status before gathering statistics.
	# 31-05-2021 Added the option of gathering STALE statistics only instead of gathering all statistics.

haballan / goldengate_lag_mon.sh

Last active February 3, 2021 21:37

This shell script monitors goldegate lag based on predefined LAG threshold inside the script - More Details: http://dba-tips.blogspot.ae/2018/05/linux-shell-script-to-monitor-and.html

	# ####################################################################################################################
	# This Script Detects GOLDENGATE LAG If The LAG Hits the Pre-defined Threshold
	# [Ver 1.0]
	#
	# To get this script work you need to Define the following variables:
	# ORACLE_HOME # Must be set to the ORACLE_HOME path of the database where GoldenGate is running against.
	# GG_HOME # Should be set to the Goldengate installation home directory path.
	# LAG=xxxx # The number of minutes of lag, if reached an email alert will be sent [10 minutes is the default].
	# EXL_PROC_NAME="DONOTREMOVE\|REP11\|REP12" In case you want to exclude specific processes e.g. REP11 & REP12
	# LOG_DIR # The location of script logs [/tmp by default].

haballan / dbdailychk_html.sh

Last active September 22, 2021 11:39

Shell Script To Perform Oracle Database Health Check (Performs multiple DB health checks and report them in one HTML report to the user via E-mail) More details: http://dba-tips.blogspot.ae/2017/11/oracle-database-health-check-report-in.html

	# ##################################################################################################################################
	# DATABASE DAILY HEALTH CHECK MONITORING SCRIPT
	VER="[6.2]"
	# ===================================================================================================
	# CAUTION: THIS SCRIPT MAY CAUSE A SLIGHT OVEARHEAD, DO NOT RUN IT TOO FREQUENT, ONCE A DAY IS IDEAL.
	# ===================================================================================================
	# ***********
	# How To Use:
	# ***********
	# 1- Set your Email by modifying this parameter below: EMAIL="youremail@yourcompany.com"

haballan / schedule_rman_full_bkp.sh

Last active March 29, 2022 09:18

Script to be used on the crontab to schedule an RMAN Full Backup (Variables in the first section must be modified before using the script)

	# ##############################################################################################
	# Script to be used on the crontab to schedule an RMAN Full Backup
	VER="[1.3]"
	# ##############################################################################################
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 04-10-17 # # # # #
	#
	# Modified:
	# 10-03-19 Add the option of deleting old CONTROLFILE AUTOBACKUP files.

haballan / schedule_rman_image_copy_bkp.sh

Last active July 29, 2021 11:58

Script to be used on crontab to schedule an RMAN Image/Copy Backup (Variables in the first section must be modified before use)

	# ##############################################################################################
	# Script to be used on crontab to schedule an RMAN Image/Copy Backup
	VER="[1.2]"
	# ##############################################################################################
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 01-10-17 # # # # #
	#
	# Modified: 02-10-17
	# 10-03-19 Add the option of deleting old CONTROLFILE AUTOBACKUP files.

haballan / rebuild_table.sh

Last active October 19, 2022 16:06

SCRIPT TO REBUILD A GIVEN TABLE AND ITS INDEXES USING ONLINE FEATURES DBMS_REDEFINITION/ONLINE REBUILD IF AVAILABLE OR OFFLINE REBUILD AS A FINAL RESORT http://dba-tips.blogspot.com/2019/05/rebuild-table-script-and-reclaim-wasted.html

	# #########################################################################################################################################################
	# Ver 3.2
	# SCRIPT TO REBUILD A GIVEN TABLE AND ITS INDEXES USING ONLINE FEATURES DBMS_REDEFINITION/ONLINE REBUILD IF AVAILABLE OR OFFLINE REBUILD AS A FINAL RESORT
	# #########################################################################################################################################################
	#
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 16-06-14 # # # # #
	#
	# Modified: 17-06-14 Inform the user if the DB is in Force Logging Mode.

Mahmmoud ADEL haballan