Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Multiple Oracle DB/Instances on single machine, how to include in one script.

Created: 03 Sep 2013 • Updated: 03 Sep 2013 | 9 comments

Hello All,

I am in desperate need of some assitance configuring Oracle Database backups and i would really value your suggestions and input.

Problem:

We have two Oracle Database servers running on HP-UX with multiple database instances running on them, we shall call them node6 and node7.

Each servers houses approximately 60-70 small databases ranging from 2GB to about 80GB in size.

The problem is not the size but rather the amount of database instances, this involves a backup script for each database instance, which is becoming a nightmare to setup firstly and then to get backed up. We have written a script that copies and edits the Netbackup RMAN template into individual scripts that get added into Netbackup.

We have configured a policy per server and then added all of the scripts into the policies but in my eyes this is rather inefficient and cumbersome, as the backup will backup each DB one by one which takes over 24 hours, the other option we tried but was even worse was a policy per script, HORRIBLE idea.

So my question is, (and yes i have googled to the point of raw fingers) to find a solution similar to the Microsoft SQL Agent backups where you can have one script and just specify $ALL to backup any added or removed database from the server, is there any similar way to do this with Oracle?

I have also looked through the Netbackup for Oracle guide but cannot find anything unless i am missing it.

I have come across a post detailing the use of a file called db_channels which allows you to specify RMAN to use a channel per Database in the script but was wondering if anyone has tried this or knows of a way.

You assistance in this matter will be highly appreicated.

Operating Systems:

Comments 9 CommentsJump to latest comment

Gavrilov's picture

NetRestore, I you can easily  create another script which will copy template policy (as you know policy is just a folder), rename it and add correct script location.

Cheers,

Mike

NetRestore's picture

Hi,

Gavrilov, does this mean I will be able to include multiple Database's into one policy, will doing your suggested method enable us to backup all the DB's in one go?

Gavrilov's picture

Unfortunately, no. I don't think it's a good idea to add multiple scripts into one policy cause if one DB backup fails you need to restart whole policy. I just wanted to say that if you have alredy created all necessary backup scripts for all DBs you can create a template policy and write a script which will copy the policy, rename it ( for example add SID name) and after that add script location into backup selection. I think it's quite simple. I do understand that you'll have a lot of policies but it will give you flexability and you'll be able to to run individual instancse backup.

Cheers,

Mike

NetRestore's picture

Yep, I believe you confirmed my fears relating to this, i know i am going to have to do a policy per DB instance.

Thanks Gavrilov.

Yasuhisa Ishikawa's picture

You can do it by determining running instance inside script like below. However, as already noticed here, you should not configure single big backup. When backup fails, all DB will backuped up again.

#! /bin/sh

#
DBA_USER=oracle
DEFAULT_ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
TARGET_CONNECT_STR=/
LOGFILE=${0}.`date +%Y%m%d`.out
#


SYSNAME=`uname -s`
if [ "${SYSNAME}" = "HP-UX" ];then UNIX_STD=2003; export UNIX_STD; fi
if [ "${SYSNAME}" = "SunOS" ];then
	ORATAB=/var/opt/oracle/oratab
else
	ORATAB=/etc/oratab
fi
if [ "${NB_ORA_POLICY}" = "" ];then
	echo "This script must be kicked by NetBackup Oracle Agent. Do not run manually."
	exit 1
fi
if [ "${NB_ORA_FULL}" = "1" ];then
	BACKUP_TYPE="INCREMENTAL LEVEL=0"
elif [ "${NB_ORA_CINC}" = "1" ];then
	BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"
elif [ "${NB_ORA_INCR}" = "1" ];then
	BACKUP_TYPE="INCREMENTAL LEVEL=1"
else
	BACKUP_TYPE="INCREMENTAL LEVEL=0"
fi

SIDS_SUCCEED=
SIDS_FAIL=
SIDS=`ps -eo args | grep "^ora_dbw0_" | cut -c 10- | grep -v "^+"`
if [ -z "${SIDS}" ];then
	echo "No instance running on this host."
	exit 0
fi
echo "### `date` Backup session started" >> ${LOGFILE}
for sid in ${SIDS}; do

	ORACLE_SID=${sid}

	# deterimine ORACLE_HOME from oratab
	ORACLE_HOME=`grep "^${sid}:" ${ORATAB} | head -n 1 | tr : " " 2>/dev/null`
	set -- ${ORACLE_HOME}
	ORACLE_HOME=$2
	if [ "${ORACLE_HOME}" = "" ];then ORACLE_HOME=${DEFAULT_ORACLE_HOME}; fi

	RMAN=${ORACLE_HOME}/bin/rman
	CMD_STR="
env ORACLE_SID=${ORACLE_SID} ORACLE_HOME=${ORACLE_HOME} ${RMAN} target ${TARGET_CONNECT_STR} nocatalog <<EOM
RUN {
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t2 TYPE 'SBT_TAPE';
BACKUP
	${BACKUP_TYPE}
	SKIP INACCESSIBLE
	FILESPERSET 5
	FORMAT 'bk_${ORACLE_SID}_%s_%p_%t'
	DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t2 TYPE 'SBT_TAPE';
BACKUP
	FILESPERSET 20
	FORMAT 'arch_${ORACLE_SID}_%s_%p_%t'
	ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';
BACKUP
	FORMAT 'ctrl_${ORACLE_SID}_%s_%p_%t'
	CURRENT CONTROLFILE;
RELEASE CHANNEL t1;
}
EOM
"
	echo "==== ${ORACLE_SID} backup started on `date` ====" >> ${LOGFILE}
	su - ${DBA_USER} -c "${CMD_STR}" >> ${LOGFILE} 2>&1
	RC=$?
	echo "==== ${ORACLE_SID} backup ended on `date` : rc=${RC} ====" >> ${LOGFILE}
	if [ ${RC} -ne 0 ];then
		SIDS_FAIL="${SIDS_FAIL} ${ORACLE_SID}"
	else
		SIDS_SUCCEED="${SIDS_SUCCEED} ${ORACLE_SID}"
	fi
done

RC=`echo "${SIDS_FAIL}" | wc -w`
echo "SIDs SUCCEEDED: ${SIDS_SUCCEED}" >>${LOGFILE}
echo "SIDs FAILED: ${SIDS_FAIL}" >>${LOGFILE}
echo "### `date` Backup session ended rc=${RC}" >>${LOGFILE}

exit ${RC}
AttachmentSize
sample.sh_.txt 2.46 KB

Authorized Symantec Consultant(ASC) Data Protection in Tokyo, Japan

Gavrilov's picture

 Yasuhisa, I think NetRestore was looking for some option to backup all DBs togather cause he didn't want to backup DBs one-by-one. In your script you just do the same thing but in more fancy way but the script is nice and we can avoid using one script per DB.

P.S. Your script expects that  ORACLE_USER is the same for all SIDs -- it might be not true.

Cheers,

Mike

NetRestore's picture

Yes Gavrilov you are right, but for now we are resorting to creating a policy per script and per DB instance, we then only allocate RMAN one channel and for netbackup to not exceed 2 streams per policy, lets see how this works.

NetRestore's picture

Well that did not work out so well, so we resorted to builiding a ZFS storage pool and sharing a mount point via NFS to the Oracle host, we then backup to the NFS share, and then snapshot the NFS mount point on the ZFS host which we then backup the snapshot. In this case far more efficient than Netbackup for Oracle.

Will Restore's picture

Quicker backup may mean longer restore time though.  Have you tried to restore and recover yet?

Will Restore -- where there is a Will there is a way