Category Archives: Articles

This pages contains all the articles of dba-scripts.com

Stop/Start all RAC databases at once

Introduction

Stopping all RAC databases running on an ORACLE_HOME at once and saving the state before shutting them down is really helpfull when you are doing patching on a server.

In my case I have databases running on some nodes and not the others, it’s getting complicated to keep track of which database is running where. With this method you will shutdown all databases at once and keep track of the databases running on that node before shutting them down.

Stopping all RAC databases of an ORACLE_HOME

This command will stop all databases running on the ORACLE_HOME specified and save the state before the shutdown in a state file.

srvctl stop home -oraclehome /opt/oracle/base/product/12.1.0/dbhome_1 -node node1 -statefile /tmp/node1_stop.dmp

Starting all RAC databases of an ORACLE_HOME

This command uses a state file to restart all databases previously running on a node.

srvctl start home -oraclehome /opt/oracle/base/product/12.1.0/dbhome_1 -node node1 -statefile /tmp/node1_stop.dmp

 

RMAN Backup script

This is a sample backup script I used, it has already a lot of options. Feel free to make any modification you want. If you add some good enhancements, let me know I can put them here so everybody can profit from them.

RMAN Backup script

#!/bin/bash
##############################################################################
#                            RMAN BACKUPS
##############################################################################
#
# Description: This script create a RMAN backup based on parameter passed
#
##############################################################################
#
# Author: Cyrille MODIANO
#
# Version : 1.0 (2016-07-12)
# Version : 1.1 (2016-12-15) - Added compression
# Version : 1.2 (2017-01-18) - Added parallelism and backup of a list of databases
#
##############################################################################

usage () {
echo "Usage : SID BACKUP_TYPE COMPRESSION PARALLELISM
        SID : SID, comma separated list of databases or ALL for all databases (running)
        BACKUP_TYPE : INCR, FULL, COLD or ARCH
        COMPRESS : COMPRESS or NOCOMPRESS to compress or not the backup
        PARALLEL : defines the number of channel to use

        exemple backup full : rman_backup.sh db1 FULL COMPRESS 16
        exemple backup arch : rman_backup.sh db1 ARCH NOCOMPRESS 2
"
}

##Variables definition
BASEDIR=$(dirname "$0")
BACKUP_BASE=/Data_Domain/oracle/prod/
LOGDIR=${BASEDIR}/log


DEST_EMAIL=example@example.com
export NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'
DATE=`date +"%Y%m%d_%H%M%S"`
PATH=$PATH:/usr/local/bin

# Create directorires if not exist
mkdir -p $BACKUP_BASE/
mkdir -p $LOGDIR
mkdir -p $BACKUP_BASE/autobackup


# Validating du number of parameters passed
if [ $# -lt 4 ]; then
    usage
    exit 1
fi

# Parameters provided
DB_LIST=$1
BACKUP_TYPE=$2
PARALLEL=$4

# Backup type validation
case $BACKUP_TYPE in
    FULL)
        LEVEL="incremental level 0"
    ;;
    INCR)
        LEVEL="incremental level 1"
    ;;
    ARCH)
        LEVEL=""
    ;;
    COLD)
        LEVEL=""
    ;;
    *)
        usage
    exit 1
    ;;
esac

# Compression validation
if [ $3 = 'COMPRESS' ]; then
    COMPRESS='AS COMPRESSED BACKUPSET'
else
    if [ $3 = 'NOCOMPRESS' ]; then
        COMPRESS=''
    else
        usage
        exit 1
    fi
fi

##backup function
function backup_database() {
    # Set Oracle Environment for database
    ORACLE_SID=$1
    ORAENV_ASK=NO
    . oraenv

    OUTPUT_SID=${ORACLE_SID}
    BACKUP_DIR=$BACKUP_BASE/${ORACLE_SID}
    LOGFILE=$LOGDIR/rman_backup_${ORACLE_SID}_${BACKUP_TYPE}_${DATE}.log
    

    # Controlfile backup directory
    CF_BACKUP="'$BACKUP_DIR/autobackup/cf_sp_%F'"
    FORMAT_DATA="format '${BACKUP_DIR}/data_%d_${BACKUP_TYPE}_bks%s_%T_%U.bck'"
    FORMAT_ARCHIVE="format '${BACKUP_DIR}/arch_%d_${BACKUP_TYPE}_bks%s_%T_%U.bck'"

    if [ $BACKUP_TYPE = 'COLD' ]; then
        sqlplus -s / as sysdba <<EOF
            shutdown immediate;
            startup mount;
            exit
EOF
        rman target /  << EOF >> $LOGFILE 2>&1
            CONFIGURE CONTROLFILE AUTOBACKUP ON;
            CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ${CF_BACKUP};
            CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM ${PARALLEL};
            run {
                backup ${COMPRESS} database $FORMAT_DATA;
                delete noprompt obsolete;
                }
            exit
EOF

        sqlplus -s / as sysdba <<EOF
            alter database open;
            exit
EOF

    else
        if [  $BACKUP_TYPE = 'ARCH' ]; then
            rman target /  << EOF >> $LOGFILE
                CONFIGURE CONTROLFILE AUTOBACKUP ON;
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ${CF_BACKUP};
                CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM ${PARALLEL};
                run {
                    backup ${COMPRESS} archivelog all $FORMAT_ARCHIVE delete input filesperset 10;
                    delete noprompt obsolete;
                    }
                exit
EOF
        else
            rman target /  << EOF >> $LOGFILE 2>&1
                CONFIGURE CONTROLFILE AUTOBACKUP ON;
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ${CF_BACKUP};
                CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM ${PARALLEL};
                run {
                    backup ${COMPRESS} archivelog all $FORMAT_ARCHIVE delete input filesperset 10;
                    backup ${COMPRESS} ${LEVEL} database $FORMAT_DATA include current controlfile;
                    backup ${COMPRESS} archivelog all $FORMAT_ARCHIVE delete input filesperset 10;
                    delete noprompt obsolete;
                    }
                exit
EOF
        fi
    fi

    # Validate Errors in the log.
    ERRORLIST=$(egrep "^RMAN-[0-9]*:|^ORA-[0-9]*:" $LOGFILE)
    ERRORLIST=$(echo $ERRORLIST)
    if [ -n "$ERRORLIST" ]
    then
        SUBJECT="$(date +%y)/$(date +%m)/$(date +%d) $(date +%H).$(date +%M).$(date +%S) - `hostname` - Backup Report ${OUTPUT_SID} - ERROR"
    else
        SUBJECT="$(date +%y)/$(date +%m)/$(date +%d) $(date +%H).$(date +%M).$(date +%S) - `hostname` - Backup Report ${OUTPUT_SID}"
    fi
    cat -v $LOGFILE | mail -s "$SUBJECT" "$DEST_EMAIL"
}

if [ $1 = 'ALL' ]; then
    for database in `ps -ef | grep pmon | egrep -v 'ASM|grep' | awk '{print $8}' | cut -d_ -f3`
    do
        backup_database $database
    done
else
    for database in $(echo $1 | sed "s/,/ /g")
    do
        backup_database $database
    done
fi