oracle DB cold backup with networker

[dradhzn]

Hi all ,
i want to do a oracle DB cold backup with networker , my question is :
1. i can schedule the networker to backup the oracle filesystem , say 2am everyday .
2. i can schedule a OS cronjob to shutdown DB at 1:30am and start up at 6am .
but how can i ensure that while networker start , the DB is down? or the networker job is done before 6am and cronjob start the db at 6am ? any configuration or scripting can be done from OS or networker ? my network version is 7.4

 

[Till]

Hi dradhzn

the best solution for this is savepnpc that executes pre- and post commands for a specific backup job, i.e. stopping services before the backup and starting them right after.
This way you can be sure that the database services are down during the backup process.
Please find more details about savepnpc in the admin guide.

Hope it helps

cheers Till

 

[vatok]

dradhzn,

I suggest you let rman do all job.

Bellow find a sample script

connect target ***
connect rcvcat ***
shutdown immediate; #shutdown db
startup mount; #mount db
RUN {
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
BACKUP DATABASE; #here is the cold backup
RELEASE CHANNEL ch01;
}
alter database open; #open db

Use this script as a save set for the networker client.

 

[goony]

Here's what we do at our shop that works 100% for us (a Unix shop)... nothing magic here, it is well documented in the NetWorker docs; but you need to have someone that is well versed in the DB stop/start scripts and rolling your own scripts:

First, to have this scripting run on the client for each backup session, the field Backup Command on the client's server profile must contain the text savepnpc.

Contents of the /nsr/res/<groupname>.res file on the client:

[tt]type: savepnpc;
precmd: "/usr/bin/ksh /usr/local/bin/nw_pre_backup.ksh", "/usr/local/bin/nw_dbstatus.pl";
pstcmd: "/usr/bin/ksh /usr/local/bin/nw_post_backup.ksh", "/usr/bin/sleep 1";
timeout: "06:30am";[/tt]

On the "precmd" line above, BOTH scripts must finish before the backup proceeds.

The script nw_pre_backup.ksh invokes the Oracle DB shutdown script, and the nw_dbstatus.pl Perl script actually looks through the running processes to see if the DB is really shutdown... it does this task by counting the occurrences of specific process names. When the count of these processes reaches zero, then the script outputs a "The DB is down" message and exits.

However... if for some reason the DB shutdown gets hung, this 2nd script will actually timeout after 15 minutes or so with a message "DB shutdown WAS NOT OKAY - proceeding anyway".

In either case, the text spit out by the DB status checking script becomes part of the email notification that is sent when the backup job is completed. It is up to someone (or some script) to take note that an error in the DB shutdown occurred and thus a bogus backup may have happened.

The pstcmd part of the file above invokes a script that runs the Oracle DB startup when the backup has finished.

The timeout is your "safety net"... i.e. if the backup runs longer than this wall-clock time, then the backup will abort and it will run the pstcmd script(s) to bring things back up.

Final note: When you turn on savepnpc in the client profile on the server, and force any backup (even just a small directory), you can log onto the client and look in the /nsr/res/ directory for a fresh new file <groupname>.res which is a template file. Take a look at it.
<groupname> = The group on the server that your client runs in... so if the backup group on your server is called Oracle_DB the you will find a file created /nsr/res/Oracle_DB.res that you will need to modify to your needs.

 

[goony]

I will post examples of my DB shutdown and process checking scripts...

 

[goony]

Here is the nw_pre_backup.ksh that does the actual DB shutdown on our Sun unix (Solaris) client.

Note that we log processes before and after DB shutdown to a logfile so that we have a trail if something goes haywire. We also change the system login message to tell any interactive user that a backup is underway.

[tt]

#!/usr/bin/ksh
#
# Name: nw_pre_backup.ksh
# Author: goony
# Description: This script runs prior to backup by Networker Server
# Functions: 1) Put message in the /etc/motd file that backups are underway
# and that the DB is shutdown
#
# 2) Run the script to shut down the oracle database
#
# Change history:
# Created goony 18-Sep-2002
# Modified goony 23-Jun-2007 Rework log file naming, show processes (if needed)

dtname=`date "+%Y_%m"` #this creates one-month sized logfiles
LOGFILE=/var/log/nw_backup_$dtname.log

#add timestamp to begin of run
now=`date`

echo "" >> $LOGFILE
echo "" >> $LOGFILE
echo "" >> $LOGFILE
echo "Entering /usr/local/bin/nw_pre_backup.ksh" >> $LOGFILE
echo "-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- Backup Start -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-" >> $LOGFILE
echo "---- BEGIN NetWorker BACKUP RUN AT $now ----" >> $LOGFILE

# alter the /etc/motd message in case someone logs in

echo "Note: Begin database application shutdown at `date` for backup via network" >> /etc/motd

echo "Starting backup via network at `date`" >> $LOGFILE

now=`date`
echo "" >> $LOGFILE
echo "+++ SHOW PROCESSES BEFORE DB APPS SHUTDOWN `date`" >> $LOGFILE
ps -ef >> $LOGFILE
echo "" >> $LOGFILE

# shutdown the DB
now=`date`
print "Stopping DB Apps for NetWorker backup at `date`" >> $LOGFILE

/etc/init.d/dbappcontrol stop >> $LOGFILE 2>&1

print "+++ Sleeping for 5 minutes..." >>$LOGFILE
/usr/bin/sleep 300

now=`date`
echo "" >> $LOGFILE
echo "+++ SHOW PROCESSES AFTER DB APPS SHUTDOWN `date`" >> $LOGFILE
ps -ef >> $LOGFILE
echo "" >> $LOGFILE

echo "Note: Completed database application shutdown at `date` for backup via network" >> /etc/motd
[/tt]

 

[goony]

I grabbed a bad example in the above file and the board software here doesn't permit edits.

It should be using [tt]$now[/tt] in a lot of spots where it outputs the current date/time to the logfile instead of [tt]`date`[/tt]. It works, but isn't very clean.

Replace and test as you see fit.

 

[goony]

Here is the Perl script that counts/waits for all database processes to be completed, but will give up after 15 minutes even if not totally shutdown.

You'll have to seriously modify this to meet your specific needs!

[tt]
#!/usr/bin/perl
#
# nw_dbstatus.pl
#
# Created: 18-Sep-03
# Authoer: goony
# Purpose: Make sure all Oracle processes have died before proceeding
# with NetWorker backup processing.
#
# Written in Perl 'cause I am too lazy to figure
# it out as a shell program.
#
# **** IMPORTANT NOTE ****
# This program is likely unique to the host that it is running
# on - see the lines below the 'find candidate processes'
# You must tailor the program for the Oracle processes
# found on this server.
#
# MODIFICATION HISTORY
#
# 09-Oct-03 00:12 goony - Change 'sleep 15' to 'sleep 60' at end (just trying to find
# if there is a problem... don't think so, but try it anyway)
# 08-Nov-04 15:32 goony - Modify to timeout after 15 minutes of waiting
# 06-Dec-04 22:53 goony - Fix output message

$now = `date`;
chomp($now);

$prog = "/usr/local/bin/nw_dbstatus.pl";

print "Starting $prog at $now - check/wait for DB to be stopped.\n";

$cnt = 1;

while($cnt) { #lather, rinse, repeat until processes are gone
@temp = `ps -ef | grep oracle`;
$cnt = 0;
foreach (@temp) {
chomp();
#find candidate processes (using regex) - tune as needed for your DB processes
$cnt++ if (/ora_[A-z0-9]{4}_(tpx|rtk|fin)1/); #count dem boogers
}
# print "Count $cnt\n";
sleep 30;

$loopcnt++; #how many times have we slept?
last if ($loopcnt > 30); #exit out if longer than 15 minutes
}

sleep 60; # wait "just to be sure" all is settled down
$now = `date`;
chomp($now);

if ($loopcnt > 30) { #DB did not shutdown within 15 minutes
$msg = "\nDB still running(?) after 15 minutes of waiting... proceeding with backup.";
} else { #DB shutdown was normal
$msg = "\nDB appears to be down... proceeding with backup.";
}

print "Exiting $prog at $now - $msg\n";

exit (0);

[/tt]

 

[goony]

One last item:

Here is the email that is generated at the completion of the backup - note the text that is generated due to the nw_dbstatus.pl script:

[tt]
Date: Sat, 14 Feb 2009 23:15:11 -0500 (EST)
From: Super-User <root-n-toot@nwserver77.domainx.com>
Subject: NetWorker savegroup: 2100_Offsite completed, Total 1 client(s), 1 Succeeded. Please see group completion details for more information.
To: undisclosed-recipients:;

NetWorker savegroup: (notice) 2100_Offsite completed, Total 1 client(s), 1 Succeeded. Please see group completion details for more information.

Succeeded: oracledb5

Start time: Sat Feb 14 21:00:00 2009
Clone Start: Sat Feb 14 21:55:23 2009
End time: Sat Feb 14 23:15:11 2009

Automatic cloning of save sets to pool Offsite Clone succeeded.

--- Successful Save Sets ---

* oracledb5:All savefs oracledb5: succeeded.
oracledb5: /oracleexport level=incr, 0 KB 00:00:22 0 files
* oracledb5:/u09 Starting /usr/local/bin/nw_dbstatus.pl at Sat Feb 14 21:04:10 EST 2009 - check/wait for DB to be stopped.
* oracledb5:/u09 Exiting /usr/local/bin/nw_dbstatus.pl at Sat Feb 14 21:05:40 EST 2009 -
* oracledb5:/u09 DB appears to be down... proceeding with backup.
oracledb5: /u09 level=incr, 0 KB 00:00:01 0 files
oracledb5: /u08 level=incr, 99 MB 00:00:05 6 files
oracledb5: /u07 level=incr, 24 GB 00:49:23 295 files
oracledb5: /u06 level=incr, 12 GB 00:31:05 89 files
oracledb5: /u05 level=incr, 5720 MB 00:10:57 19 files
oracledb5: /u04 level=incr, 8463 MB 00:19:39 22 files
oracledb5: /u03 level=incr, 12 GB 00:33:17 22 files
oracledb5: /u02 level=incr, 10 GB 00:24:35 25 files
oracledb5: /u01 level=incr, 82 MB 00:03:26 913 files
oracledb5: /usr/local level=incr, 146 MB 00:00:31 33 files
oracledb5: /opt level=incr, 0 KB 00:00:01 0 files
oracledb5: /export/home level=incr, 0 KB 00:00:05 0 files
oracledb5: /var level=incr, 88 MB 00:00:41 2201 files
oracledb5: /usr level=incr, 0 KB 00:00:10 0 files
* oracledb5:/ 66135:savepnpc: NSR directive file (/nsr/run/.nsr) parsed
* oracledb5:/ 66135:savepnpc: NSR directive file (/nsr/cores/nsrexecd/.nsr) parsed
* oracledb5:/ 66135:savepnpc: NSR directive file (/nsr_old1/cores/nsrexecd/.nsr) parsed
oracledb5: / level=incr, 7008 KB 00:00:03 33 files
nwserver: indexracledb5 level=9, 26 MB 00:00:04 78 files

--- Cloned Save Sets ---

Automatic cloning of save sets to pool Offsite Clone succeeded.
[/tt]