Loading...
Version 4.3 is now available. It's packed up with the following features:

New Scripts:
goldengate_lag_mon.sh For monitoring goldengate LAG, more details: http://dba-tips.blogspot.ae/2018/05/linux-shell-script-to-monitor-and.html

New Enhancements:
- gather_stats.sh script for gathering statistics can now run in the background (nohup mode).
- Paranoid mode feature has been added to dbalarm.sh script for reporting EXPORT/IMPORT, ALTER SYSTEM, ALTER DATABASE, instance STARTUP/SHUTDOWN and other DB Major activities, for more details on this feature: http://dba-tips.blogspot.ae/2018/05/dbalarm-script-for-monitoring-oracle-db.html
- oracle_cleanup.sh script for cleaning up database & listener logs is now giving the option to the user to archive Audit Log files as well.
- Added more parameters to dbdailychk.sh & dbdailychk_html.sh (HTML version) to let the user have more control over the report contents (e.g. including/excluding SQL Tuning Advisor, Memory Advisor, Segment Advisor, Job details and credentials changes results in/from the health check report).

To download DBA Bundle v4.3:
https://www.dropbox.com/s/xn0tf2pfeq04koi/DBA_BUNDLE4.tar?dl=0

To read the full article about the bundle features and how to use it:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
This script can monitor GoldenGate lag whenever it happens based on the pre-defined LAG threshold inside the script.
It's highly recommended to deploy this script on all (source & destination) replication servers in order to detect the lag on all (Extract, Pump, and Replicat) processes.

This script is not designed to monitor the replicated data inside the tables it totally relies on the native GoldenGate GGSCI console.

This script should be executed/scheduled by the GoldenGate installation owner OS user.                                                 

How it works:

First, Download the script:
https://www.dropbox.com/s/l4dqzicviuaawt6/goldengate_lag_mon.sh?dl=0

Second, Adjust the following parameters:

MAIL_LIST="youremail@yourcompany.com"
Replace "youremail@yourcompany.com" pattern with your e-mail.


# ###########################################
# Mandatory Parameters To Be Set By The User:
# ###########################################
ORACLE_HOME= # ORACLE_HOME path of the database where GoldenGate is running against.
GG_HOME=           # GoldenGate Installation Home path. e.g. GG_HOME=/goldengate/gghome

Please note that ORACLE_HOME & GG_HOME are mandatory to be adjusted by the user, in case you missed setting them, the script will automatically try to guess the right values, but this is not accurate, most probably the script may not work.


# ################
# Script Settings:
# ################
# LAG THRESHOLD in minutes: [If reached an e-mail alert will be sent. Default 10 minutes]
LAG_IN_MINUTES=10

Here you define the LAG threshold in minutes (it's 10 minutes by default).


# Excluded Specific PROCESSES NAME:
# e.g. If you want to exclude two replicate processes with names REP_11 and REP_12 from being reported then add them to below parameter as shown:
# EXL_PROC_NAME="DONOTREMOVE|REP_11|REP_12"
EXL_PROC_NAME="DONOTREMOVE"

In case you want to exclude specific (Extract, Pump, or Replicat) processes, because let's say they are used for testing the replication or always stopped, you can add them to the above parameter as shown in the blue color example.

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
GitHub version:
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
I've introduced a new mode called "Paranoid" mode to the dbalarm script (which is used to monitor the Oracle database and listener logs along with monitoring locks, Space, and other events). When the paranoid  mode gets enabled, the script will monitor the following additional activities/events on the Oracle database:

- EXPORT/IMPORT activities whenever they start.
- ALTER SYSTEM commands.
- ALTER DATABASE commands.
- Instance STARTUP/SHUTDOWN events.
- Other Major DB activities.

This mode is disabled by default to let the notifications focused on reporting errors. In order to enable this mode, you have to set PARANOIDMODE parameter from N to Y:

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

PARANOIDMODE=Y    # Paranoid mode will report more events like export/import, instance shutdown/startup. [Default Disabled]

To download and read the full list of features of dbalarm script, please visit:
http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
I've re-coded the gather statistics shell script for Oracle to run in nohup mode by default, thereby allowing the DBA to close the session without interrupting the gather statistics operation.

Script download link:
https://www.dropbox.com/s/fku4hf082xah1bm/gather_stats.sh?dl=0

For more details on how to use the script, please visit the original post:
http://dba-tips.blogspot.com/2014/09/script-to-ease-gathering-statistics-on.html
And here is the GitHub version:
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
It's crucial to any DBA to get to know about long running queries that can impact the performance on his/her database environment.
Badly coded reports along with developers' bad habit of using "select *" to navigate data on big tables enforce the DBA to have a kind of an early warning tool to get him aware of such performance threats before it exaggerates.

If you're running Oracle on Linux, then this script is made for you.
Download link:
https://www.dropbox.com/s/a1wn1j1squjf1qx/DBA_BUNDLE2_6Feb2016.tar?dl=0

The use of this script is so easy, the script will run against all instances on the server reporting any active sessions for more than 60 minutes (the default), you can adjust that time by changing the following parameter under THRESHOLDS section to the number of minutes you want:
EXEC_TIME_IN_MINUTES=60        # Report Sessions running longer than N minutes (default is 60 min).

Also, you can control the number of long active session if reached, then the email will be triggered. by using the following parameter:
LONG_RUN_SESS_COUNT=0        # The count of long running sessions, report will not appear unless reached (0 report all long running sessions).

Last, you must provide your good email address in order to get a detailed report of long running queries/active sessions by email:
MAIL_LIST="youremail@yourcompany.com"

This script can be scheduled in the crontab to run every 5/10 minutes.

In the upcoming post, I'll share a script that automatically kills long active sessions connected by specific programs like SQL Developer or Toad. Let's make the life of our beloved developers easy as they always make our life easy :-)

Report Long Running Queries | Long Active Sessions Script (github version):
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
In the previous post, I've shared a script reports long running sessions/queries. In this post I'll explain another simple script that kills long running sessions connecting from specific programs e.g. SQL Developer

The script can be tuned to target any specific programs, but I'm mainly defaulting the script to target SQL Developer, as it's widely used by the developers, moreover, it doesn't maintain disconnecting the running session properly when the user close the session's window, keeping the "supposed to be closed" session query running in the database!

Download link:
https://www.dropbox.com/s/ke01v10bu0bat84/kill_long_running_queries.sh?dl=0

How to use:

The script is designed to be scheduled in the crontab to run every let's say 5 minutes to kill specific sessions based on the killing criteria which you can identify by editing the following parameters:

export ORACLE_SID=orcl
You have to provide the name of the instance you want to run the script against e.g. orcl

MAIL_LIST="youremail@yourcompany.com"
Provide your email to get a detailed report after each successful session kill.

export MODULE_NAME="'SQL Developer'"
You have to provide the module name that appears in v$session which you are targeting its sessions, in order to provide more than one module you have to enclose each module name between single quote putting comma "," in between.
e.g. To include SQL Developer and Toad sessions in the killing criteria you have to provide the values to MODULE_NAME parameter like the following:
export MODULE_NAME="'SQL Developer','Toad'"

To get an idea of module names that are connecting to your database run this statement:
select distinct(module_name) from gv$active_session_history;

export DURATION="2.5"
Duration parameter is the session's elapsed active time in hours which if reached the session will be a candidate for kill. It's set to 2 hours and half as a default value. of course you have to change it to whatever fits your requirements.

export REPORT_ONLY="N"
This parameter if set to "N" to enable the real kill of the candidate session, if it's set to "Y" it will not do the actual kill but will ONLY report the candidate session to your email. This parameter is helpful during testing phase, you've to test this script first by setting this parameter to "Y", it will keep reporting to you the candidate sessions (to be killed) without killing them, once you're confident with the script accuracy, set it to"N" to start killing the candidate sessions.

Ultimately, although I'm not keen to share such scripts that may cause problems if they are not accurately configured , I'm sharing this script in the hope that you will use it wisely, and in order to do so, you have to test and test and test such scripts before you deploy them on production.
By your use of this script you acknowledge that you use it at your own risk.
Github version:
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Fixed reported bugs inside (dbdailychk.sh, dbalarm.sh, export_data.sh, report_long_runing_queries.sh) scripts.

New Features:
Added Goldengate log monitoring feature to dbalarm.sh script [Disabled by default].
Added new aliases:
 - raclog to open the alert logfile for Clusterware/Oracle Restart.
 - asmlert to open the alert logfile for ASM instance.

To download DBA Bundle V4.2:
https://www.dropbox.com/s/xn0tf2pfeq04koi/DBA_BUNDLE4.tar?dl=0

To visit the main article that explains in details all bundle features:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
DBA Bundle V4.1 is now released with the following new scripts:

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

Many scripts are packed with new features like:

RMAN_full.sh
dbalarm.sh
dbdailychk.sh
schedule_rman_full_bkp.sh
active_sessions.sh
sql_id_details.sh


SQLPLUS is now more friendly for DBAs when invoked using "sql" alias:
It will display the following info when login:
 - Fundamental info about the DB you've connected to.
 - Your current session details (OSPID, SID, SERIAL#).
 - The prompt will show the time, instance_name you're connecting to and the connected user.
This will help you track your session log accurately plus minimize human mistakes.
 - The format of common columns in DBA_* & V$ views will be well fitted in your display.

To download DBA Bundle V4.1:
https://www.dropbox.com/s/qb48d82us2y00xd/DBA_BUNDLE4.1.tar?dl=0

To visit the main article that explains in details all bundle features:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
I've got numerous requests asking to get the original health check report script to send its report in HTML format. In this post, I'll explain how to do so.

1- Download sendEmail executables that will allow the script to send the report as an attachment:
http://caspian.dotconf.net/menu/Software/SendEmail/

2- Extract sendEmail package files under the home directory of the Oracle Binaries Owner e.g. /home/oracle:
# cd /home/oracle
# tar xvf sendEmail-v1.56.tar

3- Download the HTML version of Oracle Health Check Report script:
https://www.dropbox.com/s/5wh557fddiydsip/dbdailychk_html.sh?dl=0

Open the script with "vi" editor and replace "youremail@yourcompany.com" with your right email address at line# 90:
e.g. 
MAIL_LIST="john.smith@abc.com"

Please note that due to the limitation of the HTML options for OS commands, I've excluded some checks e.g. Local Filesystem & Clusterware Services from being reported by this script [HTML version].

In case you don't like the HTML version and want to use the original version [and I encourage you to do so :-)] please visit the original post and download the text-based version of this script:
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
This Linux shell script runs from the PRIMARY DB server and checks the lag/synchronization status between Primary and Standby Oracle DBs and reports the lag [if found] to the user.

Download the script:
https://www.dropbox.com/s/a9k3rn928vj3ooc/check_standby_lag.sh?dl=0

How to use the script:
Unfortunately, this script will not work unless you manually modify the variables' value in brown color to match your environment: [Those variables are at the begging of the script]

# Here you replace youremail@yourcompany.com with your Email address:
EMAIL_RECEIVER="youremail@yourcompany.com"
export EMAIL_RECEIVER

# Replace ${ORACLE_SID} with the Primary DB instance SID:
ORACLE_SID=${ORACLE_SID}
export ORACLE_SID

# Replace STANDBY_TNS_ENTRY with the Standby Instance TNS entry you configured in the primary site tnsnames.ora file: e.g. orcl_standby
DRDBNAME=STANDBY_TNS_ENTRY
export DRDBNAME

# Replace ${ORACLE_HOME} with the ORACLE_HOME path on the primary server:
ORACLE_HOME=${ORACLE_HOME}
export ORACLE_HOME

# Log Directory Location:
LOG_DIR='/tmp'
export LOG_DIR

# Here you replace SYSPASS with user SYS password on the standby DB:
CRD='SYSPASS'
export CRD

# Replace "5" with the number of LAGGED ARCHIVELOGS if reached an Email alert will be sent to the receiver:
LAGTHRESHOLD=5
export LAGTHRESHOLD

Reminder: This script runs from the primary DB server NOT from the standby server.
Hope you find it useful.

This script is part of DBA Bundle scripts: http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html
Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview