Oracle Online backup limitations

[Former Member]

Hi,

We are using oracle 10g for SAP.

1. In which situation oracle Online consistent backup is not useful.

2. In a situation all online relog groups are corrupted. Is it possible recovery with online consistent backup ?

3. In which recovery situation offline backup is good than online backup?

Regards,

Nani

[Former Member · Accepted solution]

Hi,

1. In which situation oracle Online consistent backup is not useful.

We can't say it is not useful. But the disadvatages are,

i. It will take more time for the backup.

ii. During the backup the system performance is slow and the tablespace backing up at that point of time has only read-only access.

iii. It will take more time to restore than the offlinebackup, since we need to restore all offline redolog files created during the online backup.

2. In a situation all online relog groups are corrupted. Is it possible recovery with online consistent backup ?

We can recover the database with the online consistent backup, but we can recover the database till the last offline redolog file. We can't recover transactions which are in online redolog files.

3. In which recovery situation offline backup is good than online backup?

i.If the data load to the system is less.

ii.It will take less time compare to onlinebackup.

iii. Offline backup can be restored and the database can be opened without to do recovery.

Rgds,

JP.

[stefan_koehler · Accepted solution]

Hello Jayaprakasan,

ii. During the backup the system performance is slow and the tablespace backing up at that point of time has only read-only access.

Completely wrong.

Only the SCN in the header is frozen, but in my opinion the preferred way is to use RMAN for backup - so you also avoid that.

For further information: http://www.speakeasy.org/~jwilton/oracle/hot-backup.html

The performance point is also a myth.

Regards

Stefan

[former_member204746 · Accepted solution]

Stefan,

we see performance hits when an online backup is running.

There are obviously more disk I/O during a backup, so why is this translating in no possible performance impact?

[stefan_koehler · Accepted solution]

Hello Eric,

There are obviously more disk I/O during a backup

Of course there is much more I/O activity during a backup. No doubt.

so why is this translating in no possible performance impact?

Well - let's see (some statistic of our production database running since round about 4 days):

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads                                                    344372559
db block gets                                                    2175619495
consistent gets                                                  7.0066E+10

Check the proportion between consistent gets and phyiscal reads for round about 4 days. The phyiscal I/O is not so dramatic in relation.

Our online backup (directly to tape) takes round about 3 to 4 hours for round about 3.5 TB via RMAN and we don't see any I/O performance impact in that time frame, which is not really suprising by the ratio between physical and logical I/O.

The mirrored online redo log files and archive log files are located on a different LUN - so also no impact here.

If you really see any performance problems while performing an online backup - maybe your I/O subsystem is already reaching its limit.

Regards

Stefan

[former_member204746 · Accepted solution]

Thanks Stefan for these explanations.

So, from what I understand, there is probably a perfomance impact, but in most cases, impact is minimal

[former_member524429]

Hi,

2. In a situation all online relog groups are corrupted. 
Is it possible recovery with online consistent backup ?

Online Consistent backup only contains the offline redo log files created during a backup run. One can not get any online redo log group members from such online consistent backup. If all online redo logs are corrupted/loss then in such situation instance recovery is not possible and only incomplete partial /point-in-time recovery is possible. If the mirrored online redo log files is available then its possible to perform instance recovery.

Please refer "Recovering from Current Online Redo Log Missing" && "Recovering from One Inactive Online Redo Log Missing" topic of this useful document [Oracle Restore and Recovery|http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/97a4f439-0d01-0010-d488-d29b45e2f5ab?quicklink=index&overridelayout=true].

I would refer this useful document [Oracle Restore and Recovery|http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/97a4f439-0d01-0010-d488-d29b45e2f5ab?quicklink=index&overridelayout=true] (specially its last part) to get more information about different scenario of Restore &recovery in Oracle.

Regards,

Bhavik G. Shroff

[former_member204746]

1. In which situation oracle Online consistent backup is not useful.

-> online consisten backup is always useful

2. In a situation all online relog groups are corrupted. Is it possible recovery with online consistent backup ?

-> yes

3. In which recovery situation offline backup is good than online backup?

-> online consistent is as good as offline backup... unless you are unlucky and one backed up redolog is corrupted.

[Former Member]

Read documentation and if possible go to DBA course. I suggest you must follow:

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/6a/081d7ce5c8fa44af33bd1258547020/content.htm