Backup Konzept Oracle Datenbank DMMAP

Martin Zahn, Akadia AG, 25.09.2007, martin dot zahn at akadia dot ch, 079 410 27 87

Übersicht

ORACLE_HOME	D:\oracle\product\10.2.0\db_1
ORACLE_SID	DMMAP
Alert.log	D:\oracle\product\10.2.0\admin\DMMAP\bdump\alert_dmmap.log
Admin Directory	D:\oracle\product\10.2.0\admin\DMMAP
Database Create Scripts	D:\oracle\product\10.2.0\admin\DMMAP\create
Database Files	D:\oracle\oradata\DMMAP
PFILE	D:\oracle\product\10.2.0\db_1\database\initDMMAP.ora
SPFILE	D:\oracle\product\10.2.0\db_1\database\SPFILEDMMAP.ORA
LISTENER.ORA	D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
TNSNAMES.ORA	D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
Archived Redolog Files	D:\oracle\product\10.2.0\db_1\database\archive
Backup Directory	D:\oracle\product\10.2.0\db_1\database\backup
Archive Mode	ARCHIVELOG (Online Backup)

Backup Konzept

Übersicht

Es erfolgt sowohl ein physischer (RMAN) wie auch ein logischer (DataPump) Backup wie auf der folgenden Skizze dargestellt. Die Backup Files werden unter D:\oracle\product\10.2.0\db_1\database\backup vom RMAN bzw DataPump Prozess abgelegt.

    264167424 Sep 24 23:01 DMMAP_DATAPUMP_FULL_DMMAP_2007.09.24.EXP
        40075 Sep 24 23:01 DMMAP_DATAPUMP_FULL_DMMAP_2007.09.24.LOG
     45961216 Sep 24 23:01 DMMAP_RMAN_BACKUP_FULL_18ISPD31_1_1
   1231462400 Sep 24 23:03 DMMAP_RMAN_BACKUP_FULL_19ISPD34_1_1
        29696 Sep 24 23:03 DMMAP_RMAN_BACKUP_FULL_1AISPD5H_1_1
      7208960 Sep 24 23:03 DMMAP_RMAN_CTRLFILE_C-230837723-20070924-08
        50432 Sep 24 23:03 backup_database.log

Von hier erfolgt das Wegkopieren mittels OS-Utilities.

Backup Scripts

Der Backup erfolgt vollautomatisch über ein Script, das vom Windows Scheduler um 23:00 gestartet wird.

C:\Batch\backup_database.bat

Dieses Script wird vom Windows Scheduler gestartet. Es startet das eigentlich Backup Script.

C:\Cygwin\bin\bash.exe --login -i C:\Batch\backup_database.bash 1>D:\oracle\product\10.2.0\db_1\database\backup\backup_database.log 2>&1

C:\Batch\backup_database.bash

Dieses Script führt den gesamten RMAN und DataPump Backup durch. Im Fehlerfall sendet es ein EMail an den/die Administrator/en.

#/bin/bash

# Akadia AG, Fichtenweg 10, CH-3672 Oberdiessbach
# --------------------------------------------------------------------------
# File:        backup_database.bash
#
# Autor:       Martin Zahn, Akadia AG, 20.09.2007
#
# Purpose:     Backup Oracle Database using RMAN (Physical Backup)
#              Backup Oracle Database using DataPump (Logical Backup)
#
# Location:    C:\Batch
#
# Certified:   Windows 2003 Server / Oracle 10.2.0.3 /
#              CYGWIN_NT-5.2 1.5.24(0.156/4/2) i686
#
# Note:        Create Oracle Directory for DataPump as follows:
#
#              CREATE OR REPLACE DIRECTORY dpDMMAP
#              AS 'D:\oracle\product\10.2.0\db_1\database\backup\';
#
#              Configuration of Email see /etc/email.conf
#
# Usage:       backup_database.bash 1>backup_database.log 2>&1
# --------------------------------------------------------------------------

# DEBUG
set -x

SENDTO="martin dot zahn at akadia dot ch bae@novasys.ch sven.sacker@direct-mail-company.com"
export SENDTO

USR_PWD="system/oracledmg";
export USR_PWD

CHECK_DATE=`date +%Y.%m.%d`
export CHECK_DATE

BACKUP_DIR="/cygdrive/d/oracle/product/10.2.0/db_1/database/backup"
export BACKUP_DIR

BACKUP_LOG=${BACKUP_DIR}/backup_database.log
export BACKUP_LOG

# Setup ORACLE 10 Environment
# ---------------------------
ORACLE_SID=DMMAP; export ORACLE_SID
ORACLE_BASE=D:\\oracle; export ORACLE_BASE
ORACLE_HOME=${ORACLE_BASE}\\product\\10.2.0\\db_1; export ORACLE_HOME
TNS_ADMIN=${ORACLE_HOME}\\network\admin; export TNS_ADMIN
ORA_NLS10=${ORACLE_HOME}\\nls\\data; export ORA_NLS10
CLASSPATH=${ORACLE_HOME}\\JRE:${ORACLE_HOME}\\jlib:${ORACLE_HOME}\\rdbms\\jlib; export CLASSPATH
ORACLE_TERM=xterm; export ORACLE_TERM
ORACLE_OWNER=oracle; export ORACLE_OWNER
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1; export NLS_LANG
LD_LIBRARY_PATH=${ORACLE_HOME}\\lib:\\lib:\\usr\\lib; export LD_LIBRARY_PATH
PATH=${ORACLE_HOME}/bin:${PATH}:.; export PATH

DATAPUMP_FILE=DMMAP_DATAPUMP_FULL_${ORACLE_SID}_${CHECK_DATE}.EXP
export DATAPUMP_FILE

DATAPUMP_LOG=DMMAP_DATAPUMP_FULL_${ORACLE_SID}_${CHECK_DATE}.LOG
export DATAPUMP_LOG

# Delete Backup(s) older than -mtime X Days
# -----------------------------------------
cd ${BACKUP_DIR}
if [ "`pwd`" = "${BACKUP_DIR}" ]
then
   find . -type f -name "DMMAP*" -mtime 1 -exec rm -rf {} \;
fi

# Logical Backup of Oracle Database (Data Pump)
# ---------------------------------------------
let status1=0
cd ${BACKUP_DIR}
${ORACLE_HOME}/bin/expdp userid=${USR_PWD} directory=dpDMMAP parallel=1 full=y dumpfile=${DATAPUMP_FILE} logfile=${DATAPUMP_LOG}
if [ "$?" != 0 ]
then
   cat ${BACKUP_LOG} | /usr/bin/email -s "`uname -n`: Datapump ${DATAPUMP_FILE} for ${ORACLE_SID} failed." $SENDTO
   let status1=1
fi

# Physical Backup of Oracle Database (RMAN)
# The Backupfiles are synchronized withe the Content of the Controlfile
# ---------------------------------------------------------------------
let status2=0
rman nocatalog <<-EOF
   CONNECT TARGET sys/oracledmg
   CROSSCHECK BACKUP;
   DELETE NOPROMPT FORCE EXPIRED BACKUP;
   CONFIGURE CONTROLFILE AUTOBACKUP ON;
   CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
   'D:\oracle\product\10.2.0\db_1\database\backup\DMMAP_RMAN_CTRLFILE_%F';
   CONFIGURE DEFAULT DEVICE TYPE TO DISK;
   CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
   CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
   'D:\oracle\product\10.2.0\db_1\database\backup\DMMAP_RMAN_BACKUP_FULL_%U';
   CONFIGURE MAXSETSIZE TO 10000M;
   SHOW ALL;
   BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
   EXIT;
EOF
if [ "$?" != 0 ]
then
   cat ${BACKUP_LOG} | /usr/bin/email -s "`uname -n`: Oracle RMAN Backup for ${ORACLE_SID} failed." $SENDTO
   let status2=1
fi

# Send Success Message
# --------------------
if [ "$status1" = 0 ] && [ "$status2" = 0 ]
then
   cat ${BACKUP_LOG} | /usr/bin/email -s "`uname -n`: Oracle Backup (RMAN & DataPump) for ${ORACLE_SID} successfully completed" $SENDTO
fi