Hi clerks,

 

Offloading the backup operation to another host using disk cloning could really improve the performance on highly busy databases ( 24x7, zero downtime and all this stuff ...)

There are well know white papers on this subject, ASM included, but today Im showing you a nice way to automate the procedure using shell scripting

with EMC TimeFinder technologies:

 

Assumptions:

***********

ASM diskgroups name:

 

+data_${db_name} : asm data diskgroup

+fra_${db_name} :  asm fra  diskgroup

 

EMC Time Finder sync groups name:

 

rac_${DB_NAME}_data_tf : data group

rac_${DB_NAME}_fra_tf:   fra group

 

 

There are two scripts, one located on the production box ( bck_database.sh ) and the other one on the backup server node ( bck_database_mirror.sh )

The second one is remotly executed from the production host

There are a bunch of variables along the code with selfexplanatory names I guess, anyway let me know if you want some help

 

 

#!/bin/ksh

###

###  Copyright (c) 1988, 2010, Oracle Corporation.  All Rights Reserved.

###

###    NAME

###     bck_database.sh

###

###    DESCRIPTION

###     Database backup on third mirror

###

###    RETURNS

###

###    NOTES

###

###    MODIFIED                                 (DD/MM/YY)

###    Oracle            28/01/10             - Creacion

###

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

V_FICH_LOG=`dirname $0`/trace_dir_location/`basename $0`.${V_DATE}.log

exec 4>&1

tee ${V_FICH_LOG} >&4 |&

exec 1>&p 2>&1

 

 

ADMIN_DIR=`dirname $0`

. ${ADMIN_DIR}/setenv_instance.sh -- This script should set the instance vars like Oracle Home, Sid, db_name ...

if [ $? -ne 0 ]

then

  echo "Error when setting the environment."

  exit 1

fi

 

echo "${V_DATE} ####################################################"

echo "Executing database backup: ${DB_NAME}"

echo "####################################################################"

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Sync asm data diskgroups ..."

echo "####################################################################"

sudo symmir -g rac_${DB_NAME}_data_tf establish -noprompt

if [ $? -ne 0 ]

then

  echo "Error when sync asm data diskgroups"

  exit 2

fi

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Verifying asm data disks ..."

echo "####################################################################"

sudo symmir -g rac_${DB_NAME}_data_tf -i 30 verify

if [ $? -ne 0 ]

then

  echo "Error when verifying asm data diskgroups"

  exit 3

fi

 

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Sync asm fra diskgroups ..."

echo "####################################################################"

sudo symmir -g rac_${DB_NAME}_fra_tf establish -noprompt

if [ $? -ne 0 ]

then

  echo "Error when sync asm fra diskgroups"

  exit 4

fi

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Verifying asm fra disks ..."

echo "####################################################################"

sudo symmir -g rac_${DB_NAME}_fra_tf -i 30 verify

if [ $? -ne 0 ]

then

  echo "Error when verifying asm fra diskgroups"

  exit 5

fi

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "ASM sync sucessfully completed!"

echo "####################################################################"

 

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Updating status ${DB_NAME} to BEGIN BACKUP ..."

echo "####################################################################"

sqlplus -s /nolog <<-!

  whenever sqlerror exit 1

  connect / as sysdba

  whenever sqlerror exit

  alter system archive log current;

  alter database ${DB_NAME} begin backup;

!

if [ $? -ne 0 ]

then

  echo "Error when updating database status to BEGIN backup"

  exit 6

fi

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Splitting asm data disks....."

echo "####################################################################"

sudo symmir -g rac_${DB_NAME}_data_tf split -noprompt

if [ $? -ne 0 ]

then

  echo "Error when splitting asm data disks"

  exit 7

fi

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Updating status ${DB_NAME} to END BACKUP ..."

echo "####################################################################"

sqlplus -s /nolog <<-!

  whenever sqlerror exit 1

  connect / as sysdba

  whenever sqlerror exit

  alter database ${DB_NAME} end backup;

  alter system archive log current;

!

if [ $? -ne 0 ]

then

  echo "Error when updating database status to END backup"

  exit 8

fi

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Generating controlfile copies...."

echo "####################################################################"

rman<<-!

connect target /

run

{

allocate channel ch1 type DISK;

copy current controlfile to

'+FRA_${DB_NAME}/${DB_NAME}/CONTROLFILE/control_mount.ctl';

copy current controlfile to

'+FRA_${DB_NAME}/${DB_NAME}/CONTROLFILE/control_backup.ctl';

}

!

if [ $? -ne 0 ]

then

  echo "Error generating controlfile copies"

  exit 9

fi

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Resync RMAN catalog ....."

echo "####################################################################"

rman<<-!

connect target /

connect catalog ${V_RMAN_USR}/${V_RMAN_PWD}@${V_DB_CATALOG}

resync catalog;

!

if [ $? -ne 0 ]

then

  echo "Error when resyncing RMAN catalog"

  exit 10

fi

 

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Splitting asm fra disks....."

echo "####################################################################"

sudo symmir -g rac_${DB_NAME}_fra_tf split -noprompt

if [ $? -ne 0 ]

then

  echo "Error when splitting asm fra disks"

  exit 11

fi

 

 

echo "WARNING!: Calling bck_database_mirror.sh host ${NODE_BCK_SERVER}..."

ssh ${NODO_BCK_SERVER} ${ADMIN_DIR_BCK}/bck_database_mirror.sh

if [ $? -ne 0 ]

then

  echo "Error, when remote executing the backup "

  exit 12

fi

V_DATE=`/bin/date +%Y%m%d_%H%M%S`

echo "${V_DATE} ####################################################"

echo "Cleaning the archived redo logs already copied to tape ..."

echo "####################################################################"

rman<<-!

connect target /

connect catalog ${V_RMAN_USR}/${V_RMAN_PWD}@${V_DB_CATALOG}

run

{

resync catalog;

delete noprompt archivelog all backed up 1 times to device type sbt;

}

!

if [ $? -ne 0 ]

then

  echo "Error when cleaning the archived redo logs"

  exit 13

fi

echo "${V_DATE} ####################################################"

echo "Backup sucessfully executed!!"

echo "####################################################################"

exit 0

 

------------------------------------------------------------------------------

------------------------** BACKUP SERVER NODE ** -----------------------------

------------------------------------------------------------------------------

 

#!/bin/ksh

###

###  Copyright (c) 1988, 2010, Oracle Corporation.  All Rights Reserved.

###

###   

###    NAME

###     bck_database_mirror.sh

###

###    DESCRIPTION

###      Backup @ backup server

###

###    RETURNS

###

###    NOTES

###

###    MODIFIED                                 (DD/MM/YY)

###      Oracle                    28/01/10     - Creacion

 

 

 

  V_DATE=`/bin/date +%Y%m%d_%H%M%S`

  echo "${V_DATE} ####################################################"

  echo "Starting ASM instance ..."

  echo "####################################################################"

  ${V_ADMIN_DIR}/start_asm.sh -- This script is supposed to start the ASM instance in the backup server

  if [ $? -ne 0 ]

  then

    echo "Error when tying to start ASM instance."

    exit 1

  fi

 

 

  . ${V_ADMIN_DIR}/setenv_asm.sh -- This script is supposed to set the env. variables of the ASM instance

  if [ $? -ne 0 ]

  then

    echo "Error when setting the ASM environment"

    exit 1

  fi

 

    V_DATE=`/bin/date +%Y%m%d_%H%M%S`

  echo "${V_DATE} ####################################################"

  echo "The asm diskgroups/disks dettected are the following ..."

  echo "####################################################################"

 

  sqlplus /nolog <<-!

    whenever sqlerror exit 1

    connect / as sysdba

    whenever sqlerror exit

    SET LINES 200

    COL PATH FORMAT A25

    SELECT DISK.MOUNT_STATUS, DISK.PATH, DISK.NAME, DISK_GROUP.NAME, DISK_GROUP.TOTAL_MB FROM V\$ASM_DISK DISK, V\$ASM_DISKGROUP DISK_GROUP WHERE DISK.GROUP_NUMBER=DISK_GROUP.GROUP_NUMBER;

!

 

 

  V_ADMIN_DIR=`dirname $0`

  . ${V_ADMIN_DIR}/setenv_instance.sh -- This script is supposed to set the env. variables of the database instance

  if [ $? -ne 0 ]

  then

    echo "Error when setting the database instance environment"

    exit 1

  fi

 

  V_DATE=`/bin/date +%Y%m%d_%H%M%S`

  echo "${V_DATE} ####################################################"

  echo "Starting ${DB_NAME} in MOUNT mode..."

  echo "####################################################################"

  ${V_ADMIN_DIR}/start_instance_mount.sh -- This script is supposed to do a startup mount

  if [ $? -ne 0 ]

  then

    echo "Error starting  ${DB_NAME} in MOUNT mode"

    exit 1

  fi

  V_DATE=`/bin/date +%Y%m%d_%H%M%S`

  echo "${V_DATE} ####################################################"

  echo "Executing RMAN backup..."

  echo "####################################################################"

  rman<<-!

  connect target /

  connect catalog ${V_RMAN_USR}/${V_RMAN_PWD}@${V_DB_CATALOG}

  run {

  allocate channel ch1 type 'SBT_TAPE' parms'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; -- TDPO Media Library

  crosscheck archivelog all;

  backup tag BCK_CONTROLFILE_ST_${DB_NAME}

  format 'ctl_%d_%s__%p_%t'

  controlfilecopy '+FRA_${DB_NAME}/${DB_NAME}/CONTROLFILE/control_backup.ctl';

  backup tag BCK_DATAFILE_ST_${DB_NAME} full

  format 'db_%d_%s_%p_%t'database;

  backup tag BCK_ARCHLOG_ST_${DB_NAME} format 'al_%d_%s_%p_%t' archivelog all;

  release channel ch1;

  }

!

  if [ $? -ne 0 ]

  then

    echo "Error executing the RMAN backup"

    exit 1

  fi

 

  ${V_ADMIN_DIR}/stop_instance_immediate.sh -- This script is supposed to do a shutdown immediate of the database instance

  ${ADMIN_DIR}/stop_asm_immediate.sh -- This script is supposed to do a shutdown immediate of the ASM instance

  exit 0

 

 

fi

 

Hope it helps someone!

--L