Oracle database backup performance

[Victor Balenton]

We do database backups to a remote server using SAP application backup tools,
which uses HPUX cpio / dd tools.
The database server config is:
N Class 11.0,2 CPU, lan1: FDDI network, lan2:100MB ethernet
The backup remote server config:
K Class 11.0,4 CPU, lan1 :FDDI network, lan2:10 MB ethernet. It also has 4
DLT8000 HP Sure store tape library,each of the DLT drive are connected to its
own FW SCSI host channel which is provided by a 4 port HSC bus card on the K
class server.
The backup is done in cpio format,where the remote server is connected via
remsh command, dd command is used to send the cpio stream of data via pipe from
the database server to the remote server.
On the backend database FDDI network,for a size of 150GB we were achieving
online backups in 5 1/2 hours.
To reduce the load on the FDDI network, we added a 100Mbit HP-PB Lan card on
the remote server,configured a new LAN3 network.We used a spare LAN0 interface
on N class database server and configured it for LAN3 network.Both the servers
were then connected via 100MBit Switch for this new network.
The time for backups on the new 100Mbit Ethernet lan3 network with the same
backup parameters as earlier now takes 9 hours for 150GB! which almost double
the time, any ideas how to improve backup peformance?
I was planning to have separate 100Mb ethernet network for backup so as to
reduce the network load on the FDDI network,also thought that it would decrease
backup time to some extent but instead it takes double the time!

Thanks much in advance for any suggestions / help / inputs on this.

Mohan

[Scott Van Kalken]

Check that the cards are actually running at 100 MB or not.

This can be achieved through lanadmin command.

Also check the MTU size, because for ethernet it's 1500 (well, just under, but 1500 will do).

If the switch isn't actually negotiating at 100MB your cards wont either.

Scott.

[Alexander M. Ermes]

Hi there.
Perhaps you can use this little script.
You can do a setup for multiple drives, if you want to.
Start :
-------------------------------------------

# set -x
today_=`date +'%y-%m-%d'`; export today_
#
LOG1=/usr/tmp/cpio_AT01_${today_}.1st; export LOG1
#
PATH=$PATH:/usr/local/bin
export PATH
exec > /usr/tmp/cpio_AT01_${today_}.log 2>&1
echo "Start time is: `/bin/date`"
echo "Start time is: `/bin/date`" >> ${LOG1}
#
#
find /u0*/ORACLE/xx/dbs/*' /u0*/ORACLE/xx/log/*' /u0*/ORACLE/xx/cntrl/*' -xdev -fsonly hfs -fsonly vxfs -depth -print | cpio -ocBv > /dev/rmt/0m
2>> ${LOG1} &
first_cpio=$!; export first_cpio
#
wait $first_cpio

if test $? -ne 0; then
echo "AT01 Offline Backup did not complete successfully!"
fi
echo "Time is: `/bin/date`"
echo "Time is: `/bin/date`" >> ${LOG1}
---------------------------------------------
end

By the way, if you use all four drives, your backup should be faster. That is not clear from your posting. And 5 1/2 hrs for a 150GB backup is ok, not double the time.
We backup two servers with one library on a separate server net connection ( 100 MBit ethernet ). No problems.
Rgds
Alexander M. Ermes

[Volker Borowski]

Hi,

brbackup supports (and from Release 4.x on recommends) the use of dd to write to the tape. Do either this or increase the cpio-blocksize to a reasonable value for a DLT drive. The initSID.sap default -ovB is too small. initSID.sap contains parameters to set the blocksize for dd as well. Watch out, you'll gonna have two dd-commands! One writing to the pipe and on writing to the tape.

In addition brbackup supports the use of several tapes in parallel, which might increase your throughput.

If your machine has a very good CPU-power, consider to use SW-compression, because this will decrease network load.

Hope this helps
Volker

[Victor Balenton]

Hi all,

Thanks for providing responses. Well I checked the cards on both database / backup servers are working in 100Mb FDX mode. I checked this with lanadmin -x # (card ID).

I do use SAP's brbackup command with tape devices ( 4 drives ) in parallel.In cpio mode with -ovB option,the max. blocking factor is 5K bytes on HPUX, I understand I can beef up the block size by using dd command instead.
But my question was in regards to increase in backup time when we moved to a separate 100Mb ethernet network.
Before on FDDI network we could achieve online backups of 150GB in 5 1/2 hours. Now keeping the same backup parameters, backup tools and drives , the same 150GB database backup takes 9 hours on a 100Mb Ethernet network !

Thanks,

[Krishna Prasad]

How are you connecting to the network?

Are you using VPN connection?

I would check the port settings in the switch if you are using a VPN. It needs to match what you have your network cards set to.

[Dennis J Robinson]

Check routing from the host to the backup server.

Do traceroute and determine the number of hops.
You want one to two hops if at all possible to backup server.

Do the following two times in succession:

150 Opening BINARY mode data connection for vmunix (21822672 bytes).
226 Transfer complete.
21822672 bytes received in 3.66 seconds (5824.78 Kbytes/s)
ftp> cd /stand
ftp> get vmunix /dev/null
200 PORT command successful.
150 Opening BINARY mode data connection for vmunix (21822672 bytes).
226 Transfer complete.
21822672 bytes received in 0.37 seconds (57031.08 Kbytes/s)
ftp>

The first time you do the transfer vmunix is buffered, so drive speeds can slow you down. The second time the file is buffered and if you have optimal performance btw your host and backup server, on 100BT FULL you should see somewhere around 10000-11000kbytes/s.

Email me @ djrobins@flash.net if you have any other questions. We will get your performance.

[Patrick Preuss]

hi,

as i'm going right you want to backup the datasotred in the oracle rdbms. you have two ways to get this faster:

a) if you have "Mirror Disk" installed make a Mirror Copy of u01 and take this offline. and backup the binary files of the database.

b) you can export the data stored in the rdbms with the command "exp". this would produce a dump file of the data stored in the rdmbs size is aporx. 10% of the size from the rdbms. than you could compress this with gizip and copy it every where. should a bit slower. and you could do a diff to the file from the day before, and save only the diff file, so you could save additional bandwidth and disc space.

best regards

patrick

[Victor Balenton]

Hi all,

Sorry for the delay on replying.Well as I said earlier there is no route established for this connection it is on its own network, for e.g the ifconfig info of this interface :
on database server :
lan0: flags=843
inet 192.168.16.9 netmask ffffff00 broadcast 192.168.16.255

on backup server :
lan4: flags=843
inet 192.168.16.10 netmask ffffff00 broadcast 192.168.16.255

I have even bypass the Ethernet switch connection,instead connected the two servers with a cross-over cable directly. I checked with netperf tool and gives the following results :
for 100Mb ethernet network:

TCP STREAM TEST to 192.168.16.10
Recv Send Send
Socket Socket Message Elapsed
Size Size Size Time Throughput
bytes bytes bytes secs. 10^6bits/sec

32768 32768 32768 10.01 53.69

for 100Mb FDDI network :
TCP STREAM TEST to 10.21.0.6
Recv Send Send
Socket Socket Message Elapsed
Size Size Size Time Throughput
bytes bytes bytes secs. 10^6bits/sec

32768 32768 32768 10.01 79.01

If you see above the transfer rate for FDDI is better than 100Mb ethernet. Further thing to note that the FDDI ring has 6 servers on it in production environment, while 100Mb ethernet does not have any load since it is connected direct back to back over a cross over cable.
Any more ideas / suggestions ?

Thanks,