Enable - Disable Cron Jobs in Linux/AIX/Unix with single command line input

 Here in this we are using "#outage" to the start of line for all lines in Cron to disable and vice-versa.

Disable :-

crontab -l | sed -e 's/^/#outage/' | crontab


Enable:-
crontab -l | sed -e 's/^#outage//' | crontab


Hope this helps......

Mysql- Master - Slave Replication setup.

Below steps should be done in same outage. Take Master server down and take backup and restore to the slave side to make sure they both are look-alike as per Data.


1) Add below parameters in /etc/my.cnf for Master-side Mysql Instance and for Slave-side Mysql Instance repectively.

Master-side Configuration:
# To enable MySQL replication, you need to enable binary logging.

sync_binlog=1  # enable "1" if replication is used for master else comment this to disable
binlog_checksum = NONE
binlog_format = STATEMENT
log-bin=/mysql/log/hostname-bin.log
log-bin-index=/mysql/log/hostname-bin.index

Slave-side Configuration:

skip-slave-start   # it will not start replication automatically when mysql is started.
relay-log=/mysql/log/slave-relay-bin
relay-log-index=/mysql/log/slave-relay-bin.index


2) stop mysql on Master side and Slave side.
systemctl stop mysqld

3) start mysql on Master side and Slave side.
4) On Master-Side:
   create user 'repl_user'@'Slave-IP' identified by 'Password#';
   grant replication slave on *.* to 'repl_user'@'Slave-IP' identified by 'Password#';
   flush privileges;
   FLUSH TABLES WITH READ LOCK;
   show variables like 'sql_log_bin';
   set sql_log_bin=0;  --- to disable binary logging    we are doing this to disable replication for sometime till we setup slave with master backup and config etc.
   -- when ready to run command on step 5 on Slave-side
   unlock tables;
   reset master;
   show master status\G; --- note down the details for below command on slave side.
 
     
5) On Slave_side:
CHANGE MASTER TO MASTER_HOST='MAster-IP',MASTER_USER='repl_user',MASTER_PASSWORD='Password#',MASTER_LOG_FILE='hostname-bin.000001',MASTER_LOG_POS=50;
start slave;
show slave status\G;
show processlist\G; ---- check and see if you see replication process is running on both sides.

6) create a dummy table(and insert rows) on Master-Side and see if it got created on Slave-side.

MySQL - Session Alert script

Below scripts are needed to find the Current Sessions live in Mysql instance:


Two sql scripts you need in the same folder as shell script:- which are in RED color in the script.

1) session_count.sql

tee ./logs/session.log
select count(*) from information_schema.processlist;
notee;

2) session_details.sql

tee ./logs/session_details.html
select user,SUBSTR(HOST,1,INSTR(HOST,':')-1) as "Host",db,command,state,count(*) as "Number of sessions" from information_schema.processlist group by user,SUBSTR(HOST,1,INSTR(HOST,':')-1),db,command,state;
notee;

Code for the Shell Script:-

**************************************************

#!/bin/ksh

#########################################################################
# PROGRAM NAME: session_count.sh   for MySQL
#
# USAGE: ./session_count.sh
#
# Siva
#########################################################################

COMMAND=$0
PGM=${COMMAND##*/}
DATE=$(date "+%Y%m%d%H%M")
hostname=`hostname -s`
MAILTO=Name@domain.com
PWD=/scripts/sessions  # put script dir path if you want to run from "crontab"
repdir=$PWD
LOG=$repdir/logs

if [ ! -d $LOG ]; then
mkdir  $LOG
else
rm $LOG/session.log
if [ -f $LOG/session_details.html ]; then
rm $LOG/session_details.html
fi
fi

####### ******************** Below list of changes are req:

conf_file=/etc/my.cnf
BACKUP_USER=root
BACKUP_PASS=`cat /oracle/.orapasswd/.backupusr`

Vald_sess_C=1  ---- Set the Valid sessions number for the mark
log_ret=15

Logfile=mysql_Session_"$hostname"_$DATE.log
echo " started $DATE" >>$LOG/$Logfile

#### Changes are done:

####### ******************** run Active file:

if [ -f $LOG/bkp_active_file ]
 then
   echo "previous mysql session_count.sh script is still running. confirm before running again" | mailx -s "Mysql-Session Status: $hostname - Failed to execute" $MAILTO
   exit 0
fi

###

touch $LOG/bkp_active_file

mysql -Ns -u root -p$BACKUP_PASS < $repdir/session_count.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1

####### ******************** Check the session count:

Check_Sess_C=`cat $LOG/session.log`

if [[ $Check_Sess_C > $Vald_sess_C ]]
then
mysql -H -u root -p$BACKUP_PASS < $repdir/session_details.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1

####### ******************** Email Output to Mail:

( echo "Subject: AUTO-Gen - HOST: $hostname - MySQL Sessions are High - Details: "
  echo "To: OracleDBAs@unfi.com"
  echo "From:  <$hostname> "
   echo "MIME-Version: 1.0"
   echo "Content-Type: text/html; charset=US-ASCII"
echo "<html>"
echo "<body>"
   echo "<font size="+2">" "<font color="red">"
   echo "<u>"
   echo "Action Needed: Mysql sessions are high - Count - $Check_Sess_C ."
   echo "</u>"
   echo "<br/>"
   echo "<font size="-1">" "<font color="black">"
   echo "<pre>"
   cat $LOG/session_details.html
   echo  "<pre>"
   echo "Successful end of $repdir/session_count.sh"
echo "</body>"
echo "</html>"
 ) | /usr/sbin/sendmail $MAILTO

### End of Email

fi

####### ******************** Purge Log directory:

if [ ! -d $LOG ]; then
mkdir  $LOG
else
/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;
fi

####### ******************** Remove run active file:

if [ -f $LOG/bkp_active_file ]
 then
rm $LOG/bkp_active_file
echo " removed run-active file" >> $LOG/$Logfile
fi

exit

### All steps are Done.

*********************************************************************

Hope this helps.................................................

virtual indexes

Thought this would be  handy in testing the performance. By creating virtual indexes in a session you can check whether the performance of the bad sql can be improved or not before we implement it permanently.
this is feature is available from 11g.

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

create index vir_idx1 on Table_1 (col_1,col_2,col_3) nosegment;

once the Virtual Index is created run the bad sql and check the plan for the performance boost. if this work then create an index permanently.

Hope this helps.....

Mysql: mysql Enterprise backup (MEB) script

Here is the script to backup Mysql database using MEB. 

copy this below code in to script and edit to the correct directory paths and run it.

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

#

COMMAND=$0

PGM=${COMMAND##*/}

DATE=$(date "+%Y%m%d%H")

hostname=`hostname -s`

MAILTO=XXXxx@gmail.com

PWD=/opt/scripts  # put script dir path if you want to run from "crontab"

#PWD=`pwd`      # enable this and disable above one if you want to run in nohup mode.

repdir=$PWD

LOG=$repdir/dblogs

##*********** Below list of changes are req: 

conf_file=/etc/my.cnf

BACKUP_USER=root

BACKUP_PASS=`cat /opt/.orapasswd/.backupusr`

bkp_type=$1

ret_time=$2

Logfile=mysql-MEB_"$bkp_type"_"$hostname"_$DATE.log

log_ret=15

echo " started $DATE" >>$LOG/$Logfile 

#### Changes are done:

##***************** run Active file:

if [ -f $LOG/active_file ]

 then

   echo "previous mysql backup is still running. confirm before running again" | mailx -s "Mysql-Backup: $hostname - Failed for $bkp_type backup" $MAILTO

   exit 0

fi

## Validate Log directory:

if [ ! -d $LOG ]; then

mkdir  $LOG

else 

/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;

fi

#******* Checking the input parameter: 

if [ $# = 0 ]

then

echo "Please enter the first parameter for backup type (FULL) and second parameter as a number in days for backup retention" |tee -a $LOG

exit 1

fi

##************ setting the backup-dir path for the Backup location:

bkup_dir=/backup_folder/$hostname/meb/$bkp_type

if [ ! -d $bkup_dir ]; then

mkdir -p $bkup_dir 

fi

echo " Mysql-backup: $bkp_type files are located under $bkup_dir" >>/$LOG/$Logfile

echo ""

####### End of backup-dir path setup

touch $LOG/active_file

##************ Running Backup from above inputs:

echo "Running $bkp_type Backup from above inputs"

if [ $bkp_type = 'FULL' ] ; then

   /opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=$conf_file --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$bkup_dir backup-and-apply-log >>$LOG/$Logfile 2>&1

else

echo "Incorrect input parameter - please enter a correct backup type" |tee -a $LOG/$Logfile

fi

####

 

##********* check Any errors in the logfile:

error_chk=$(cat $LOG/$Logfile | awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /WARNING|warnings|ERROR/{print buf,$0}' | wc -l)

if [[ $error_chk -eq 0 ]]

  then

     echo "Mysql Backup for backup-type $bkp_type completed successfully - no errors $error_chk " >>$LOG/$Logfile

   else

    echo "Mysql Backup for backup-type $bkp_type was unsuccessful - errors $error_chk. check and run again" >>$LOG/$Logfile

   echo "Mysql Backup for backup-type $bkp_type completed but has errors - $error_chk . please check" | mailx -s "Mysql-Backup - $bkp_type : Completed with errors" $MAILTO

fi

####

##******** remove run active file:

if [ -f $LOG/active_file ]

 then

rm $LOG/active_file 

echo " removed run-active file" |tee -a $LOG/$Logfile

fi

echo "Mysql-backup: All steps are done" |tee -a $LOG/$Logfile

exit 0;

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

hope this helps........................................

Mysql release version EOL support details..

To check release version EOL support:

http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf


To check release versions: similar

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/


Hope this helps....

Mysql upgrade from 5.7.XX to 5.7.27

Below you can find the steps to upgrade Mysql 5.7.xx to 5.7.27.

Here we are using In-Place Upgrade. which means we are upgrading the mysql version on the existing server only which is completely different from logical upgrade.

1)

First download right version of Mysql software. do this.

Go to Patches and downloades -> click product or family -> enter Mysql in product and check release you want(5.7.27) -> platform that you need (here it is linux) -> select description  and enter 5.7.27 -> search.

download the zip version(here it is linux)

p30076436_570_Linux-x86-64.zip

2)  Unzip p30076436_570_Linux-x86-64.zip

3) check what rpm's are installed on your server for mysql by doing this
rpm -qa  mysql* or use rpm -V mysql*

4) when you are about to upgrade. Take VM snapshot or take a backup of mysql data.

5) login into mysql and check below variables setting.

show variables like 'innodb_fast_shutdown';

6) run below command to set below variable.
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"

7)
mysqladmin -u root -p  shutdown   or systemctl stop mysqld  (as root)
ps -ef| grep mysqld

8) use below command to upgrade all the rpm's(with dependencies). check the list you have to upgrade and edit the below command.  run as "root "

rpm -Uhv mysql-commercial-{server,libs,libs-compat,client,common,embedded,embedded-compat}-5.7.27-1.1.el7.x86_64.rpm

9) once rpms are installed with no errors. start mysql 

systemctl start mysqld    --- as root (you can use sudo commands if you have to)

monitor mysqld.log... if you see this below messages.. ignore for now and proceed to next step.

(  output from mysqld.log

 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

)

10) run mysql upgrade command and monitor all tables. they should resturn "OK" and "Upgrade process completed successfully"  in the end.

mysql_upgrade -u root -p

12) do this step only if you seeing the above similar warnings only after mysql_upgrade.

 drop table mysql.innodb_index_stats;

 drop table mysql.innodb_table_stats;

and run the mysql_upgrade -u root -p

12) Post checks after upgrade.

select version();

show databases;

select count(*) from table_name;

13). bounce mysql.

Hope this helps....

Auto - CPU Parallel calculation

Below is the script to get the available CPU's from the allotted servers based on Number of Instances on that server:


Script:-

total_cpu=`lscpu | grep ^'CPU(s)' | awk '{print $2}'`
cpu_used=$(top -b -n2 -p 1 | fgrep "Cpu(s)" | tail -1 | awk -F'id,' -v prefix="$prefix" '{ split($1, vs, ","); v=vs[length(vs)]; sub("%", "", v); printf "%s%.1f%%\n", prefix, 100 - v }' | sed 's/%//g')

used_cpus=`echo "$total_cpu*$cpu_used*0.01"|bc`
avail_cpus=`echo "$total_cpu-$used_cpus" | awk '{print int($1)}'`

num_inst=`pgrep -al pmon |egrep -v '(^#|\+|\-)' |awk '{print $2}' |wc -l`

if [[ "$avail_cpus" -le 1 ]]; then
 echo "consider Default"
 CHNLS=1
 echo $CHNLS
  else
     if [[ "$num_inst" -lt 2 ]] ; then
       echo "consider 90 percent"
       avail_cpu2con=`echo "$avail_cpus*0.9"|bc`
       CHNLS=`echo $avail_cpu2con | awk '{print int($1)}'`
       echo $CHNLS

      else
          if [[ "$num_inst" -ge 2 ]] && [[ "$num_inst" -le 4 ]] ; then
             echo "consider 75 percent"
             avail_cpu2con=`echo "$avail_cpus*0.75"|bc`
             CHNLS=`echo $avail_cpu2con | awk '{print int($1)}'`
             echo $CHNLS
           else
              echo " consider 60 percent"
              avail_cpu2con=`echo "$avail_cpus*0.60"|bc`
              CHNLS=`echo $avail_cpu2con | awk '{print int($1)}'`
              echo $CHNLS
         fi
    fi
fi

Hope this helps..... suggest if any improvements are needed.

resize alert log for each database using script.

This script automatcally back's up exisitng alert log to a location you need(BKP_ALT_DIR)  and resize the alert log to 0.



code of the script:


COMMAND=$0
PGM=${COMMAND##*/}
DATE=$(date "+%Y%m%d")
hostname=`hostname -s`
PWD=`pwd`
repdir=$PWD
LOG=$repdir/logs

################ Change Retention Here ###########

BKP_ALT_DIR=/backup/2_week/$hostname/ALERT_BKP

############ Done ########################


# Log directory checks:

if [ ! -d ${LOG} ]
 then
    mkdir -p $LOG
  else
    rm $LOG/*log
fi

if [ ! -d ${BKP_ALT_DIR} ]
 then
    mkdir -p $BKP_ALT_DIR
fi


# copying alert log files to desired folder:

  for db in `cat /etc/oratab| egrep -v '(^#|\+|\-)'|cut -f1 -d: -s|egrep -v 'OMS'|egrep -v 'AGENT'`
       do
         db_check=`pgrep -al pmon|grep $db |cut -f3,4 -d_`
          if [ -n "$db_check" ]
              then
              echo $db
              db_sid=$db_check
              l_db=`echo $db | tr [A-Z] [a-z]`
               alert_dir=/opt/oracle/diag/rdbms/$l_db/$db_check/trace
                  cd $alert_dir
                  echo $alert_dir
                  echo "$BKP_ALT_DIR"
                  cp alert_$db_check.log $BKP_ALT_DIR/alert_"$db_check"_$DATE.log
                echo "alert_$db_check.log copy is done for $db" >>$LOG/DB_up_list.log
            ready=0
              while [ $ready -eq 0 ]
                   do
                 if [ `lsof | grep /opt/oracle/diag/rdbms/$l_db/$db_check/trace/alert_$db_check.log |grep -v tail |wc -l` != 0 ];
                           then
                         echo "`lsof | grep /opt/oracle/diag/rdbms/$l_db/$db_check/trace/alert_$db_check.log |grep -v tail |wc -l`"
                            ready=0
                              sleep 5
                    else
                            ready=1
                  fi
              done
                  echo "resetting alert_$db_check.log for $db" >>$LOG/DB_up_list.log
                  > alert_$db_check.log
                  echo "alert_$db_check.log reset for $db is done" >>$LOG/DB_up_list.log
           else
               echo "$db" >>$LOG/exclude_list.log
          fi
   done

echo "completed:" >>$LOG/DB_up_list.log


Hope this helps......

How To Know the Current Patches released

Steps to find what current patches released as of Date:

Oracle Database:

1. Login to oracle support
2. Type 1454618.1 and hit search
3. Select  the option you need (ex: Oracle database PSU,SPU(CPU),Bundle Patches (versions 12.1 & lower).
4. then select 12.1.0.2 (if 12c)
5. then select the related patch you need from the list.

Oracle Enterprise Manager:

1) Checking for future releases:

NOTE:793512.1 - Release Schedule of Enterprise Manager Releases and Patchsets (10g, 11g, 12c, 13c)

2) To find Current installed OMS list of patches:

How to Determine the Patch Set Update (PSU) Applied to the Enterprise Manager 13c Cloud Control OMS and the Bundle Patch applied to Plug-ins and Agent (Doc ID 2319892.1)

For the EM 13c Cloud Control OMS, issue this command to find the effective PSU level of the EM 13c OMS:

<OMS Oracle Home>/OPatch/opatch lsinventory | grep -i 'EMBP Patch Set Update'

To see the current version according to the Plugin Bundle Patches for the EM 13c OMS plug-ins, use the following:

<OMS Oracle Home>/OPatch/opatch lsinventory | grep -i 'Plugin Bundle Patch'

For the EM 13c Cloud Control Agent, issue this command to the the current Bundle Patch level applied:

<OMS Oracle Home>/OPatch/opatch lsinventory | grep -i 'EM-AGENT Bundle Patch'

Other helpful Doc id's:

Doc ID 822485.1

Enterprise Manager 13.2 Master Bundle Patch List (Includes Plugins: 13.2.1, 13.2.2, 13.2.3) (Doc ID 2219797.1)

Hope this helps.