Oracle PRE-Production RMAN Backup Script for both L0 and L1 backups with MD5SUM checksum

Oracle PRE-Production RMAN Backup Script for both L0 and L1 backups with MD5SUM checksum

Advantages.

1) Helps in tracking the backups date-time as per the backup folders.

2) help to check the MD5 check sum of the files when restorations from the tape backups.

# LEVEL 0 BACKUPS (Friday)

59 19 * * 5  nohup /home/oracle/scripts/backups/backup_script_v7.ksh FSDEV 0 4 "/backups" "zafrullakhan001@gmail.com" 1>&2 >> /tmp/FSDEV_nohup_rmanrun.log &

00 15 * * 5  nohup /home/oracle/scripts/backups/backup_script_v7.ksh FSTST 0 4 "/backups" "zafrullakhan001@gmail.com" 1>&2 >> /tmp/FSTST_nohup_rmanrun.log &

59 23 * * 5  nohup /home/oracle/scripts/backups/backup_script_v7.ksh FSUAT 0 4 "/backups" "zafrullakhan001@gmail.com" 1>&2 >> /tmp/FSUAT_nohup_rmanrun.log &


# LEVEL 1 BACKUPS ( Monday and Wednesday )

59 20 * * 1,3  nohup /home/oracle/scripts/backups/backup_script_v7.ksh FSDEV 1 4 "/backups" "zafrullakhan001@gmail.com" 1>&2 >> /tmp/FSDEV_nohup_rmanrun.log &

00 17 * * 1,3  nohup /home/oracle/scripts/backups/backup_script_v7.ksh FSTST 1 4 "/backups" "zafrullakhan001@gmail.com" 1>&2 >> /tmp/FSTST_nohup_rmanrun.log &


59 22 * * 1,3  nohup /home/oracle/scripts/backups/backup_script_v7.ksh FSUAT 1 4 "/backups" "zafrullakhan001@gmail.com" 1>&2 >> /tmp/FSUAT_nohup_rmanrun.log &



#!/bin/ksh
set -xv
#########################################################
## Author: zafrullakhan001@gmail.com
## Version: 7.0:
#########################################################

### Initialize Variables Begin ####
export DB_NAME=$1;
export BKP_LEVEL=$2;
export PARALLEL=$3;
export FOLDER_PATH=$4;
export DBA_EMAIL="$5";
export PROCESS_NUMBER=$$;
export DATE_FOLDERS="$(date +%F-%H:%M:%S)-L${BKP_LEVEL}";
mkdir -p "${FOLDER_PATH}/rman/${DB_NAME}/BACKUPS/$DATE_FOLDERS/LOGS/";
ORACLE_HOSTNAME=${hostname}; export ORACLE_HOSTNAME
ORACLE_UNQNAME=$DB_NAME; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=$DB_NAME; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
LOG_DATE=`date +%d%m%Y`
LOGFILE="${FOLDER_PATH}/rman/$DB_NAME/BACKUPS/$DATE_FOLDERS/LOGS/${ORACLE_UNQNAME}_${PROCESS_NUMBER}_LEVEL${BKP_LEVEL}_`date +%d%m%Y`.log"
TRACEFILE="${FOLDER_PATH}/rman/$DB_NAME/BACKUPS/$DATE_FOLDERS/LOGS/${ORACLE_UNQNAME}_${PROCESS_NUMBER}_LEVEL${BKP_LEVEL}_TRACE_`date +%d%m%Y`.trc"
TIMETRACKER="${FOLDER_PATH}/rman/$DB_NAME/BACKUPS/$DATE_FOLDERS/LOGS/${ORACLE_UNQNAME}_${PROCESS_NUMBER}_TIME_TRACK_L${BKP_LEVEL}_`date +%d%m%Y`.log"

### Initialize Variables End ####

### Check if oracle is running and accepting connection ####
check_stat=$(ps -ef|grep -i ${DB_NAME}|grep pmon|wc -l);
oracle_num=$(expr $check_stat);
if [ $oracle_num -lt 1 ]
then
exit 0
fi

#*************************************************************
# Test to see if Oracle is accepting connections
#*************************************************************
$ORACLE_HOME/bin/sqlplus -s / as sysdba<<! > /tmp/check_$ORACLE_SID.ora
  select * from v\$database;
exit
!

#*************************************************************
# If not, exit and e-mail . . .
#*************************************************************

check_stat=$(cat /tmp/check_$ORACLE_SID.ora|grep -i ORA-|wc -l);
oracle_num=$(expr $check_stat);
if [ $oracle_num -ne 0 ]
then
  cat /tmp/check_$ORACLE_SID.ora|mailx -s "$ORACLE_SID is down! RMAN Can not proceed for taking Backup" $DBA_EMAIL;
exit 16
fi
### End Oracle Connection Tet ####
echo -e "  ${ORACLE_UNQNAME} LEVEL ${BKP_LEVEL} BACKUP START AND END TIME DETAILS" > $TIMETRACKER
echo  "====================================================" >> $TIMETRACKER
date >>$TIMETRACKER
rman target / catalog RMANCAT/<password>@RMANDB USING $DB_NAME $BKP_LEVEL $PARALLEL "'$FOLDER_PATH'" "'$DATE_FOLDERS'" trace=$TRACEFILE log=$LOGFILE <<EOF
run {
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '&4/rman/&1/BACKUPS/&5/&1_snapshot_ctrl_file.ctl';
configure controlfile autobackup off;
crosscheck backup;
crosscheck archivelog all;
CONFIGURE DEVICE TYPE DISK PARALLELISM &3 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '&4/rman/&1/BACKUPS/&5/L&2_%T_%d_%s_%p_%U';
backup incremental level &2 device type disk as compressed backupset database tag = 'weekly0';
sql 'alter system archive log current';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '&4/rman/&1/BACKUPS/&5/L&2_ARCH_%T_%d_%s_%p_%U';
backup device type disk as compressed backupset archivelog all delete input tag L&2_ARCHIVE_LOGS;
delete noprompt obsolete device type disk;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '&4/rman/&1/BACKUPS/&5/L&2_CNTL_%T_%d_%s_%p_%U';
backup current controlfile tag L&2_CNT_FILE_BKP;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '&4/rman/&1/BACKUPS/&5/L&2_SPFILE_%T_%d_%s_%p_%U';
sql "begin dbms_backup_restore.refreshagedfiles; end;";
backup spfile tag L&2_SP_FILE_BKP;
}
EOF
date >> $TIMETRACKER

echo -e "\n====================================================\n\n" >> $TIMETRACKER
echo -e "\n  HISTORICAL RMAN BACKUP DETAILS " >> $TIMETRACKER
echo -e "====================================================\n" >> $TIMETRACKER
sqlplus -s \"/ as sysdba\"  @/home/oracle/scripts/backups/rman_backup_info.sql >> $TIMETRACKER
echo -e "\n====================================================\n\n" >> $TIMETRACKER
echo -e "  ORA- ERROR DETAILS If ANY? " >> $TIMETRACKER
echo -e "====================================================\n" >> $TIMETRACKER
cat $TRACEFILE | grep ORA-  >> $TIMETRACKER
echo -e "====================================================\n\n" >> $TIMETRACKER
echo -e "  FEW LINES of LOG FILE " >> $TIMETRACKER
echo -e "====================================================\n" >> $TIMETRACKER
tail -20 $TRACEFILE >> $TIMETRACKER
echo -e "====================================================\n\n" >> $TIMETRACKER

error_count=$(cat $TRACEFILE | grep ORA- | wc -l);

if  [ $error_count -gt 0 ]; then
        mailx -s "RMAN Level ${BKP_LEVEL} BACKUP FAILED FOR $DB_NAME ${LOG_DATE}" -r noreply@clientxyz.com  ${DBA_EMAIL} < $TIMETRACKER
else
        mailx -s "RMAN Level ${BKP_LEVEL} BACKUP SUCCESS FOR $DB_NAME ${LOG_DATE}" -r noreply@clientxyz.com  "${DBA_EMAIL}" < $TIMETRACKER
        nohup find ${FOLDER_PATH}/rman/${DB_NAME}/BACKUPS/$DATE_FOLDERS/ -type f|xargs md5sum >> ${FOLDER_PATH}/rman/${DB_NAME}/BACKUPS/$DATE_FOLDERS/MD5SUM.txt &
fi
exit

Oracle Production RMAN Backup Script for both L0 and L1 backups

# Level 0 Backup # FSPRD Friday 18:00 Hrs.

00 18 * * 6  nohup /home/oracle/scripts/backupscripts/backup_script_v5.ksh FSPRD 0 8 "/backups" "zafrullakhan001@gmail.com" >> /tmp/FSPRD_nohup_rmanrun.log 1>&2 &

# Level 1 Backup # FSPRD Monday, Tuesday, Wednesday, Thursday, Saturday, Sunday 18:00 Hrs.

00 18 * * 1,2,3,4,5,0  nohup /home/oracle/scripts/backupscripts/backup_script_v5.ksh FSPRD 1 8 "/backups"  "zafrullakhan001@gmail.com"  >> /tmp/FSPRD_nohup_rmanrun_l1.log 1>&2 &


RMAN Backup Script Starts

#!/bin/ksh
#############################################################################
#  RMAN Backup Script
#  Author : Zafrulla Khan Oracle / Mysql / SQL Server / PostgreSQL /  MangoDB DBA. 
#  Description: RMAN Backup Script which acts as both for Level 0 and Level 1 
#  Scheduled to Run L0 Backups on Saturdays
#  Scheduled to Run L1 Backups (Incr) Every Week-days
#  Email will help to know the status of the RMAN Backup Script like Success / Failure
#############################################################################

set -xv
export DB_NAME=$1;
export BKP_LEVEL=$2;
export PARALLEL=$3;
export FOLDER_PATH=$4;
export DBA_EMAIL="$5";
export PROCESS_NUMBER=$$;
mkdir -p "${FOLDER_PATH}/rman/${DB_NAME}/BACKUPS/LOGS/";
ORACLE_HOSTNAME=pldandbd1; export ORACLE_HOSTNAME
ORACLE_UNQNAME=$DB_NAME; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=$DB_NAME; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
LOG_DATE=`date +%d%m%Y`
LOGFILE="${FOLDER_PATH}/rman/$DB_NAME/BACKUPS/LOGS/${ORACLE_UNQNAME}_${PROCESS_NUMBER}_LEVEL${BKP_LEVEL}_`date +%d%m%Y`.log"
TRACEFILE="${FOLDER_PATH}/rman/$DB_NAME/BACKUPS/LOGS/${ORACLE_UNQNAME}_${PROCESS_NUMBER}_LEVEL${BKP_LEVEL}_TRACE_`date +%d%m%Y`.trc"
TIMETRACKER="${FOLDER_PATH}/rman/$DB_NAME/BACKUPS/LOGS/${ORACLE_UNQNAME}_${PROCESS_NUMBER}_TIME_TRACK_L${BKP_LEVEL}_`date +%d%m%Y`.log"

echo -e "  ${ORACLE_UNQNAME} LEVEL ${BKP_LEVEL} BACKUP START AND END TIME DETAILS" > $TIMETRACKER
echo  "====================================================" >> $TIMETRACKER
date >>$TIMETRACKER
rman target / catalog RMANCAT/<password>@RMANDB USING $DB_NAME $BKP_LEVEL $PARALLEL "'$FOLDER_PATH'" trace=$TRACEFILE log=$LOGFILE <<EOF
run {
sql "begin dbms_backup_restore.refreshagedfiles; end;";
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '&4/rman/&1/BACKUPS/&1_snapshot_ctrl_file.ctl';
configure controlfile autobackup off;
crosscheck backup;
crosscheck archivelog all;
CONFIGURE DEVICE TYPE DISK PARALLELISM &3 BACKUP TYPE TO BACKUPSET;
sql 'alter system archive log current';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '&4/rman/&1/BACKUPS/&2_ARCH_%T_%d_%s_%p_%U';
backup device type disk as compressed backupset archivelog all delete input tag L&2_ARCHIVE_LOGS;
delete noprompt obsolete device type disk;
configure controlfile autobackup on;
}
EOF

date >> $TIMETRACKER
echo -e "\n====================================================\n\n" >> $TIMETRACKER
echo -e "\n  HISTORICAL RMAN BACKUP DETAILS " >> $TIMETRACKER
echo -e "====================================================\n" >> $TIMETRACKER
sqlplus -s \"/ as sysdba\"  @/home/oracle/scripts/backupscripts/rman_backup_info.sql >> $TIMETRACKER
echo -e "\n====================================================\n\n" >> $TIMETRACKER

echo -e "  ORA- ERROR DETAILS If ANY? " >> $TIMETRACKER
echo -e "====================================================\n" >> $TIMETRACKER
cat $TRACEFILE | grep ORA-  >> $TIMETRACKER
echo -e "====================================================\n\n" >> $TIMETRACKER

echo -e "  FEW LINES of LOG FILE " >> $TIMETRACKER
echo -e "====================================================\n" >> $TIMETRACKER
tail -20 $TRACEFILE >> $TIMETRACKER
echo -e "====================================================\n\n" >> $TIMETRACKER

error_count=$(cat $TRACEFILE | grep ORA- | wc -l);
if  [ $error_count -gt 0 ]; then
        mailx -s "RMAN Archive Log BACKUP FAILED FOR $DB_NAME ${LOG_DATE}" -r noreply@clientxyz.com  ${DBA_EMAIL} < $TIMETRACKER
else
        mailx -s "RMAN Archive Log BACKUP SUCCESS FOR $DB_NAME ${LOG_DATE}" -r noreply@clientxyz.com  ${DBA_EMAIL} < $TIMETRACKER
fi

exit

Script Ends.

MySQL first Database and first User creation.

MySQL User and Database Creation. Step-by-Step.

# Connect to mysql database as root user.

mysql -h localhost -u root -p

# Create database myfirstdb; 

CREATE DATABASE IF NOT EXISTS myfirstdb;

# Create user; 

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

# Grant Specific Privileges to only myfirsdb

GRANT USAGE ON myfirstdb.* TO 'myfirstuser'@'localhost' IDENTIFIED BY 'mypassword' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

# Grant Specific Privileges to only myfirsdb

GRANT ALL PRIVILEGES ON myfirstdb.* TO 'myfirstuser'@'localhost';

Automate Oracle DR Physical Standby Database (Incremental Backup and Restore)

Hey,

There was a requirement to keep the DR in sync using the incremental backup and restore. As because the bandwidth between the two datacenters was not sufficient to cope up with the LWGR and ARCH Transfer method during busy business hours.

So, I had to do this task repeatedly every day. Soon, I thought to automate this as it was becoming the daily task for synchronizing the DR database using the production incremental backups.

Here is how it goes to automate the DR Sync Using Production Incremental Backups.

Steps to Automate this using KSH Scripts and sshpass command on Linux OS.

On FSPRD: 

Scripts to be placed in the /home/oracle/script/

/home/oracle/scripts/syncdrincr.ksh
/home/oracle/scripts/getdrscn.sql

#!/bin/ksh

set -vx
##########################################################################################
# Client  : XYZ 
# Name    : SYNC DR Using Incremental Backup and Restore.
# Author  : zafrulla khan (Manager PwC SDC Bangalore)
# Date    : Jul-13-2015
# Usage   : syncdrincr.ksh
# Deployed: Jul-13-2015
# Version : 2.0
##########################################################################################


# ORACLE ENV Settings.

ORACLE_HOSTNAME=$(hostname); export ORACLE_HOSTNAME
ORACLE_UNQNAME=FSPRD; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
LOG_DATE=`date +%d%m%Y`


# Get the Lowest SCN Number from FSPRDDR Database to take the RMAN backups from FSPRD

sqlplus -s sys/<password>@FSPRDDR as sysdba @/home/oracle/scripts/getdrscn.sql > /home/oracle/scripts/drscn.log
error_count=$(cat /home/oracle/scripts/drscn.log | grep ORA- | wc -l);

# Check if there was any issues getting the SCN details from FSPRDDR

if  [ $error_count -gt 0 ]; then
mailx -s "RMAN Not able to get connected to DR Database FSPRDDR to Fetch SCN ${LOG_DATE}" -r noreply@xyz.com  emailaddr@pwc.com < /home/oracle/scripts/drscn.log
exit 1;
fi

# Remove any Newlines and extra blank lines from the results.

export DRSCN=$(cat /home/oracle/scripts/drscn.log| grep -v "^\$")
echo $DRSCN

# remove the old backups from the folder /backups/rman/standby on production database server (FSPRD)

export ORACLE_SID=FSPRD
cd /backups/rman/standby && rm -f Standby_Inc_*


# Run the RMAN Backups from the SCN which you obtained above which is avaialble in the variable DRSCN

rman target / USING $DRSCN trace=/tmp/incremental_fsprd.trc log=/tmp/incremental_fsprd.log <<EOF
run {
BACKUP INCREMENTAL FROM SCN &1 DATABASE FORMAT '/backups/rman/standby/Standby_Inc_%U' tag 'STANDBY_INC';
}
EOF

# Check if there was any issues completing the RMAN Incremental backups 

error_count=$(cat /tmp/incremental_fsprd.trc | grep ORA- | wc -l);

if  [ $error_count -gt 0 ]; then

mailx -s "RMAN Incr Level from SCN $DRSCN BACKUP FAILED FOR FSPRD ${LOG_DATE}" -r noreply@xyz.com  emailaddr@pwc.com < /tmp/incremental_fsprd.log 

else

 mailx -s "RMAN Incr Level from SCN $DRSCN BACKUP SUCCESS FOR FSPRD ${LOG_DATE}" -r noreply@xyz.com  emailaddr@pwc.com < /tmp/incremental_fsprd.log

 # Script recoverdr.ksh will execute on DR System (FSPRDDR) invoked using sshpass and ssh parameter as shown.

 sshpass -p '<password>' ssh -q -t oracle@tt.zz.yy.xx /home/oracle/scripts/recoverdr.ksh 1>&2 > /tmp/scp_transfer_dr.log

fi



getdrscn.sql

set heading off;
select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY'; 

exit

Short Oracle RMAN Incremental Backup procedure to Sync Standby Database Quickly.

Oracle RMAN Incremental Backup to Sync Standby Database.

Step 1: ON FSPRDDR (STANDBY DATABASE)

        ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 2: ON FSPRDDR CAPTURE THE SCN Number.
       
        select min(f.fhscn) from x$kcvfh f, v$datafile d
        where f.hxfil =d.file#
        and d.enabled != 'READ ONLY'    


Step 3: ON FSPRD (Primary Database) From the SCN Number obtained from Step 2
       
        Note: Remove the old backups from the /backups/rman/standby/ folder before executing the rman incremental backup command.
       
        BACKUP INCREMENTAL FROM SCN <SCN NUMBER from STEP 2> DATABASE FORMAT '/backups/rman/standby/Standby_Inc_%U' tag 'STANDBY_INC';
               
        cd /backups/rman/standby/
        du -sh
           
       
Step 4:  ON FSPRDDR pull the backups from FSPRD Server.

         Login to oracle on FSPRDDR Server. (STANDBY DATABASE)
       
         Remove the old backups from the /backups/rman/standby/ on DR Standby Server
       
         nohup sshpass -p '<password>'  scp oracle@primaryserver:/backups/rman/standby/* .  > /tmp/nohup_standy_copy 1>&2 &

   
       
Step 5: ON FSPRDDR (STANDBY DATABASE)

        rman target /
       
        CATALOG START WITH '/backups/rman/standby';   

        DR-DB[oracle@pldsjdbdr1 standby] 2015-07-01 21:12:47$ rman target /
        Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 1 21:13:46 2015
        Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
        connected to target database: FSPRD (DBID=1101052898, not open)
       
        RMAN> CATALOG START WITH '/backups/rman/standby';
        Do you really want to catalog the above files (enter YES or NO)? yes
        cataloging files...
        cataloging done
       
step 6:

        RECOVER DATABASE NOREDO;
       
Step 7: GET the New Updated SCN on FSPRDDR (STANDBY DATABASE)
        sqlplus / as sysdba
       
        select min(f.fhscn) from x$kcvfh f, v$datafile d
        where f.hxfil =d.file#
        and d.enabled != 'READ ONLY'    
       
           
Step 8: ON FSPRD execute the following SQL Statement to find out the GAP           
       
       
    select to_char(current_scn) from v$database; // FSPRD

    set lines 200
    SELECT SCN_TO_TIMESTAMP(98793218462) "FSPRD", SCN_TO_TIMESTAMP(NEW DR SCN from Step 7) "FSPRDDR" FROM dual;
    FSPRD                                               FSPRDDR
    ------------------------------------   -------------------------------------------
    02-JUL-15 08.11.29.000000000 PM                     02-JUL-15 07.06.14.000000000 PM