Oracle incremental backups taking as long as normal backups ?!?

[symon_braunbaer]

Dear Experts,

we are running our SAP systems on oracle 11g, on Solaris zones.

Backups are being done through RMAN and stored on an EMC DataDomain.

Dynamic block change tracking is enabled in each DB.

We have recently switched our backup strategy for non-production systems

from Full backups each day to a level 0 backup once per week and incremental

backups each day. The DB size is about 500 GB. But there is something VERY

frustrating:

On the ERP DEV system, the incremental backups are about 14 GB in size,

and they take about 15-20 mins, which is OK.

On the QAS system and on another systems though, incremental backups

take absolutely the same time, as level 0 backups (6-7 hours), which is not

acceptable at all!!

Can you please advise, if there is something that can be done ot SAP/DB

level, in order to speed up those incremental backups, or we really have a

serious performance bottleneck in our storage subsystem ?

Many thanks in advance!

[Brindavan_M]

Hello,

check any memory and CPU resource for the same.

check the channel  allocation and fileperset etc values to be set to speed the process.. in file "init<SID>.sap".

check SGA and PGA ,large pool size... and tune if required. 

check the network area with storage team  as well.

cross check the QUA DB size which might be copy fo PRD recently  and size might be increased.

Thanks,

Brindavan

[stefan_koehler]

Hi Symon,

> Can you please advise, if there is something that can be done ot SAP/DB level, in order to speed up those incremental backups, or we really have a serious performance bottleneck in our storage subsystem?

At first you need to identify the root cause / bottleneck. There are a lot of layers between Oracle and the storage subsystem. Is there any chance that you are using ZFS as you have mentioned Solaris zones? If yes, you might want to check out my blog post which describes one possible issue ().

Where do you store your backups? B2D or to tape? There are 3 possible areas (ready, copy and write) which can slow down the backup. By the way 14 GB in 15 minutes are only 16 MB/s, which is also not very fast (depending on the storage and backup type) assuming that you are using block change tracking feature of RMAN and the bitmap limit is not reached yet.

Regards

Stefan

[symon_braunbaer]

Hello Stefan,

many thanks for your answer! We are using ZFS for the oracle sapdata filesystems, you are right!

I have no idea what the filesystem on the backup device (the EMC Data Domain) is.

I will check your blog post.

As I said, we are storing the backups on an EMC DataDomain device. But it is in the scope of

the OS team, we do not play much with it. I know that the speed is low, we have been complaining

for 1 year now and OS admins are only telling - we know about your problem, but we have currently

no time to check it, so this is really too much of procrastination...

May I please kindly ask you to let me know what the bitmap limit is and how to check if it is reached

or not ?

Many thanks!

[symon_braunbaer]

Dear Stefan,

I went quickly through your blog post. We have asynchronous I/O. I can just tell you, that in the past, we had problems that the DB was crashing during backup. In the alert log there were errors, like asynchronous I/O takes too much time. I have decided to try to disable it and I have enabled those I/O slaves that you are talking about. The performance was nearly the same, but this was not a solution, the DB was still crashing, so I think I reverted everything back and I found another solution.

So, I am afraid, that we have to check another things.

Again, my question is, is there anything I can try to do in Oracle or SAP, or I can completely sit back and state, that this is a problem of Solaris / storage / architecture and I cannot do anything about it ?

Thank you very much!

[stefan_koehler]

Hi Symon,

> May I please kindly ask you to let me know what the bitmap limit is and how to check if it is reached or not ?

Please check this blog post by Jonathan Lewis: Change Tracking | Oracle Scratchpad

> But it is in the scope of the OS team, we do not play much with it. I know that the speed is low, we have been complaining for 1 year now and OS admins are only telling - we know about your problem, but we have currently no time to check it, so this is really too much of procrastination...

I/O performance problems are not necessarily an OS team issue. As i said it also depends on how the application works (kind of I/O like async I/O) on specific file systems / devices. In case of ZFS you have several layers (e.g. zfs_read, zfs_write, zio_wait, zio_vdev_io_done, vdev_disk_io_done, io:::start, scsi, etc.), which can be measured by DTrace. Maybe you are also running into the ZFS copy-on-write performance issue.

You can measure all of this on your own (from a database perspective as well) without the OS team and help them or fix it by yourself.

Regards

Stefan

[stefan_koehler]

Hi Symon,

> Again, my question is, is there anything I can try to do in Oracle or SAP, or I can completely sit back and state, that this is a problem of Solaris / storage / architecture and I cannot do anything about it ?

Yes, measure each RMAN phase (read,copy and write) and layer to find the issue. This is plain Oracle in first place. For example if you find the root cause in the RMAN write phase - measure the write performance with DTrace to verify if this is a ZFS layer (or maybe even lower / higher level) issue and so on. You can do all of this on your own as i mentioned previously - no reason / time to lean back and chill

Regards

Stefan