Category Archives: RMAN

This category will contains articles or script in relation with Oracle Recovery Manager

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

RMAN table recovery (12c new feature)

Before oracle 12c, recover a table from loss or corruption was a difficult affair.
Most of the time you notice that the table is lost or corrupted too late to use the flashback technology and then the only way to go is to duplicate your database to a time the table was still valid, export this table and reimport it in your production database. That was a long drawn out affair.
Oracle 12c still procede the same way, it creates an auxiliary database with just the needed tablespace(s) containing the table to restore, export the table using datapump and reimport it to the database. All these steps are now automated. This feature is only available in Enterprise Edition as for the Tablespace Point in time recovery.

Recover a table with RMAN

As for any RMAN recovery you can restore your table by specifying :

  • UNTIL SCN
  • UNTIL SEQUENCE
  • UNTIL TIME

Continue reading RMAN table recovery (12c new feature)

Moving datafiles

Move a datafile online in Oracle 12c

One of the new feature of Oracle 12c is relocating datafile online using the move command.

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/EXAMPLE.dbf' 
      TO '/u01/app/oracle/oradata2/ORCL/EXAMPLE.dbf';0

if you want to do a copy instead of a move, use the keep keyword and the old datafile will be retained.

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/EXAMPLE.dbf' 
    TO '/u01/app/oracle/oradata2/ORCL/EXAMPLE.dbf' KEEP;

Continue reading Moving datafiles