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

Oracle DB performance hit during Daily Full Backups

Created: 13 May 2013 • Updated: 07 Jul 2013 | 40 comments
This issue has been solved. See solution.

Hi All,

My Oracle admin says there was huge performance issue on one of my ORACLE DB LINUX server while the daily full backup happening.

Can anyone suggest how i will be able to fine tune this so that the backups will run smooth.

Thanks,

Nayab

Operating Systems:

Comments 40 CommentsJump to latest comment

Marianne's picture

Online RMAN backups give us exactly that - online backups. 
How performance is affected on the Oracle server is determined by physical resources on the server, like memory, cpu and disk layout.

Some good links that should make sense to an Oracle dba:

http://www.nyoug.org/Presentations/2010/December/Chien_RMAN.pdf

http://www.oracle.com/technetwork/database/features/availability/s316928-2-175929.pdf

http://levipereira.files.wordpress.com/2011/01/tuning_rman_buffer.pdf

http://www.oracle.com/technetwork/database/focus-areas/availability/rman-perf-tuning-bp-452204.pdf

The secret will be to ensure sufficient resources for 'normal' Oracle processing as well as rman performance.

I cannot see that much can be done from NBU point of view.
Maybe reduce 'filesperset' parameter in the script... (actually rman setting, not NBU)
Or schedule backups during quiet time.

*** EDIT ***

Another link that may be useful:

http://www.freelists.org/post/oracle-l/RMAN-backup-slows-database-performance-to-a-crawl,2

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

SOLUTION
Nayab.Rasool's picture

Hi Maria,

These links seems to be some interesting stuff :) i will go through and will get back to you.

Thanks,

Nayab

Nayab.Rasool's picture

Hi Maria,

I have gone through these docs will ask my ORACLE_DB to check all these parameters , Are these the best recommendations to get a better throughput from ORACLE  ??

Thanks,

Nayab

huanglao2002's picture

1.You can ask the DBA, backup windows avoid the business peak time.
2.Please reference the rman manual,you can try to control backup speed. such as  "configure channel device type disk rate 20480k"

Marianne's picture

You need to hand this over to the Oracle team... 

I have posted the links so that we all (Oracle dba's included) can understand that they need to troubleshoot and tune from within Oracle. Nothing we can do from NBU side.
As a backup admin, I cannot make Oracle recommendations.

Best if dba's ask the same questions on an Oracle forum.

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

Nayab.Rasool's picture

It seems he tried these already but not much performance , Is something do i need to change from the Policy or change some values or parameters in Netbackup so that it will help the backup to boost as Archive Log backup is fine with the server , When Full backup triggers it will be a huge performance hit.

Thanks,

Nayab

Nayab.Rasool's picture

Will you people able to say something by seeing logs ?? any chance ???

Thanks,

Nayab

Nayab.Rasool's picture

As my DB admin says the performance of the Oracle server is very slow during the DAILY FULL BACKUPS :( . Please help me if i can change any parameters that will help.

Thanks,

Nayab

huanglao2002's picture

2.Please reference the rman manual,you can try to control backup speed. such as "configure channel device type disk rate 20480k"

Nayabsk's picture

Hi Huanglao,

Thank you verymuch for your reply , I have checked the rman backup and recovery guide and i have found the option what u have mentioned , I would like to know what will be the maximum value to get better performance.

CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='ENV=(NSR_SERVER=bksvr1)';
CONFIGURE CHANNEL DEVICE TYPE DISK RATE 5M FORMAT="?/oradata/%U";
 
Because you do not specify channel numbers for these channels, the channel settings 
are generic to all automatic channels of the specified type. The configuration acts as a 
template. For example, if you set PARALLELISM for DISK to 10, and the default device 
type is DISK, then RMAN allocates ten disk channels using the settings in the 
CONFIGURE CHANNEL DEVICE TYPE DISK command
 
 
Thanks,
Nayab
 
 
 
 
Nayab.Rasool's picture

My DBA said  " It will not help cos we are doing tape and not disk backups" ??. 

Please help if we have any other option for this??

Marianne's picture

 I would like to know what will be the maximum value to get better performance.

This type of question should best be asked in an Oracle forum.

PS:
You are now logging onto Connect with a different user id?

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

Nayab.Rasool's picture

Yupp that is my partner ID as my current company is Platinum Partner of Symantec.

Nayab.Rasool's picture

I have found this parameter to tune up "NET_BUFFER_SZ"  but later I came to know that changing the parameter 

/usr/openv/netbackup/NET_BUFFER_SZ will cause restoration to fail ???

"After changing the number of buffer and the size of the media server and the client, test backup and restoration was successful. However restoration from old backup image failed, this was resolved after changing the number of buffer and the size back to the original setting."

If the above case is true i am sure there should be some alternative that symantec would have found.

Please help me with your comments.

Thanks,

Nayab

huanglao2002's picture

We have two method to handle the issue.

1 try to control the backup speed.

2 imporve the server performance.

If 1 can not resolve the problem, Can you let DBA to do some thing?

Nayab.Rasool's picture

Hi huanlao,

But how i will be able to control the backup speed and server performance ?????

Thanks,

Nayab

Marianne's picture

I have said the following in my very first reply:

I cannot see that much can be done from NBU point of view.
Maybe reduce 'filesperset' parameter in the script... (actually rman setting, not NBU)
Or schedule backups during quiet time.

As far as Oracle performance is concerned - the DBA and the server /storage admin need to find out where the bottleneck is and address accordingly.

No matter how many time you ask - this is not going to be solved on a NetBackup forum or by a backup administrator.

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

Nayab.Rasool's picture

Hmmm Ok sorry better i will not drag this thread anymore.

Thanks,

Nayab

Nayab.Rasool's picture

Hi Maria,

A server can be a MEDIA SERVER itself on which ORACLE DB is running ?? Is this recommended to use will this also be the reason for SLOW full backups but i can see archive log backups are happening fine.

Thanks,

Nayab

Marianne's picture

It seems this server already has a performance problem.

Loading media server software with the additional bptm and bpbrm processes will just make things worse on this particular server.

Until such time as dba and system owner have found reason for poor performance on this server, there is nothing you can do from NBU point of view. (I think I have said this before? )

On a server that performs well, i.e. local read speed is good and not exhausting cpu and memory resources and only the network is the bottleneck, NBU media server software will help to improve backup performance.

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

Nayab.Rasool's picture

What i meant to ask is they are already using this as MEDIA server so is this is the reason for SLOWNESS ??

Marianne's picture

So.. You forgot to tell us all along?
Has anyone thought of monitoring processes for cpu and memory usage during backups?
And disk I/O?

Change the Storage Unit to another media server as a test.
Just confirm network comms using hostnames and SERVER entries like you would do with any network client.
 

Be sure to monitor processes and disk I/O when backup is running as media server, then do the same when backup is running as network client.

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

vjuhola's picture

1. Ask from the DBA when the database is NOT used that much -> schedule full backup during that time

2. Is the backup taken through SAN or LAN? whats the speed of LAN or SAN? is it utilized to the max?

3. Is it going on to disk or tape?

4. What are media servers values for SIZE_DATA_BUFFERS, NUMBER_DATA_BUFFERS, NUMBER_DATA_BUFFERS_DISK and SIZE_DATA_BUFFERS_DISK ?

5. How many channels are you allocating for the backup? Whats the filesperset? Are you using RMAN compression? Are you using client side deduplication?

6. RMAN uses Oracle's SGA (shared global area), ask your DBA to increase SGA if possible.

Please post your RMAN-script used for taking the backup. Also post "show all;" from RMAN

Nayab.Rasool's picture

Hi Vjuhola,

These many days the DB itself acting as Media server for Daily DB full Backups, Is this best practice ???

I suspect this might be the reason for the slowness in accessing DB while DB full backups going on so coming weekday we are going to change the MEDIA SERVER to another NON-DB server and start the backup and see how it goes. I will surely provide all the above details you have asked as i have no access to these servers i wil be able to give the details on Monday/Tuesday only.

Thanks a lot for replying :)

Nayab

vjuhola's picture

"DB itself acting as Media server for Daily DB full Backups" so there are tape drives shown to the database server directly, is that right? If that is the case, then yes the database server can be considered to be a media server. And yes that may lead to performance issues.

Nayab.Rasool's picture

Hi  Vjuhola,

Thank you very much , So if i change the media server to a regular media server ( LINUX MEDIA SERVER USED TO BACKUP OS DAILY FULL FROM OTHER CLIENTS ) will help me to see some improvement in perfromance of my DB DAILY FULL BACKUPS ???

Thanks,

Nayab

Marianne's picture

I have suggested that you backup to another media server over here (5 days ago)....

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

Nayab.Rasool's picture

Hi Maria,

Yes you do I am going to try it tomorrow and post the results here.

Thanks,

Nayab

Nayab.Rasool's picture

Hi Vjuhola,

1. Ask from the DBA when the database is NOT used that much -> schedule full backup during that time

Yes the backup were configured during that time only.

2. Is the backup taken through SAN or LAN? whats the speed of LAN or SAN? is it utilized to the max?

It is running through SAN FC

3. Is it going on to disk or tape?

It is going to TAPE

4. What are media servers values for

SIZE_DATA_BUFFERS --> 262144

NUMBER_DATA_BUFFERS --> 16

NUMBER_DATA_BUFFERS_DISK --> Not Found

SIZE_DATA_BUFFERS_DISK --> Not Found

Please post your RMAN-script used for taking the backup.

Please find the RMAN scripts attached.

For rest of the question i will answer asap

AttachmentSize
Oracle Arch Script.txt 6.66 KB
Oracle Full Script.txt 10.02 KB
Nayab.Rasool's picture

How many channels are you allocating for the backup?

I can see two channels aloocated for the backup CH00 and CH01

Whats the filesperset

I was able to see two values 10 and 20 

You will be able to see these values from the script attached.

Nayab.Rasool's picture

Hi Maria/Vjuhola,

Even after changing the MEDIA SERVER still the backup is very slow casuing the performance on the server.

I have attached the TOP command and IOSTAT and NETSTAT. During backup and Non-Backup Hrs please help me if you ppl able to find any bottleneck ??

Thanks,

Nayab

AttachmentSize
IOSTAT Non-Backup Hrs.txt 10.66 MB
IOSTAT Archive Backup Hrs.txt 2.44 MB
Netstat Archive Backup Hrs.txt 3.66 MB
NETSTAT Non-Backup Hrs.txt 2.14 MB
TOP Arch bkup 9.30AM.txt 7.07 KB
TOP Archive Backup Hrs.txt 178.21 KB
TOP Non-Backup Hrs.txt 403.48 KB
Marianne's picture

We have on more than occasion suggested that filesperset be lowered.

A value of 10 for database backups is very high - NBU sample script has this value set to 5.
Yours is exactly double. This means 10 databases are backed up in full simultaneously.
Performance hit should be expected.

Start by reducing this value to 5.

If this does not help, reduce to 1.

For archive logs, all should be fine with higher filesperset value.

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

Nayab.Rasool's picture

Hi Maria,

Ok will try to change that and see if that helps but other DB of same size is running fine with the same values above.

Vjuhola, 

Please let me know if you have any findings from the information provided above.

Thanks,

Nayab

Nayab.Rasool's picture

Hi Maria,

Have tried changing the fileperset values but still the backup are slow :( . 

Thanks,

Nayab

Marianne's picture

Your initial issue is with 

Oracle DB performance hit during Daily Full Backups

not with backup performance. Filesperset WILL cause backups to be slow, but should in theory have less performance impact on the Oracle DB performance.

The issue is clearly not with NetBackup.

I think we have provided enough advice and have said in so many ways that your server admins and dba need to do investigation from their side.

I will no longer respond to this discussion.

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

Nayab.Rasool's picture

Hi Maria,

Sorry for asking too many questions :( I am not familiar with this kind of issue thats why was asking so many doubts and also i have just replied to your solution about fileperset didnt helped much thats it.

Thanks for your participation and suggestiions all the way. Hope will not ask you any more questions.

Regards,

Nayab