Whole Database Online + redo log backup: How Long?

[Former Member]

Hi gurus,

There's something I want to ask about online backup with redo log.

If i'm correct, redo log file is created everytime there's operation that change datafile right?

If i schedule this type of backup everyday, it means it will backup all datafiles, along with redo log file when transaction take places, at my pre-defined hours, right?

Then here's what i'm confused about. When will this backup process finished? Is it when all the datafiles have been backed up? Then how about the redo log files? If there are users that keep making transaction (therefore make change to datafiles), new redo log files will be created on disk, and that will prevent the backup process to finish. Then when will this cycle come to an end?

Please confirm if my grasp about oracle online backup is correct or not, and provide explanation to satisfy my curiousity.

Thanks gurus,

Edited by: Bobby Gunawan on Jan 6, 2009 10:16 AM

[stefan_koehler · Accepted solution]

Hello Bobby,

> If i'm correct, redo log file is created everytime there's operation that change datafile right?

No that's wrong. You have a defined number of redolog files (=Online redolog files) in redolog groups. If you make changes in the database the changes are tracked in them. If a redolog file is "full", oracle switches to the next one (in the next redolog group) and archives the already filled one (= Archived/offline redolog files).

> If i schedule this type of backup everyday, it means it will backup all datafiles, along with redo log file when transaction take places, at my pre-defined hours, right?

It depends on the type of backup you will execute, but generally you can say that a consistent online backup needs datafiles, a controlfile and archivelogs (between and one after the online backup).

> When will this backup process finished?

The backup will finish when all blocks of the datafiles (with copy or with RMAN) are copied to your backup device.

> Then how about the redo log files?

The offline redolog files needs also to be backuped in an extra archivelog backup. If you are using RMAN this happens automatically.

If you are responsible for the backup in your environment, please get some training. You are working on some critical points for your company.

Here are some additional information about backup and recovery:

http://www.oracle.com/technology/deploy/availability/htdocs/BR_Overview.htm

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm

Regards

Stefan

[Former Member]

Thanks gurus..

I think it's clear enough.

Solved.

[Former Member]

Thanks for the answers gurus,

Just want to confirm,

Let's say we schedule an whole online backup + redo log..

When the backup process running online, a user make INSERT into table, therefore changing datafile.

But that datafile has been backed up already some minutes ago. It is when redo log comes in handy if i'm not mistaken? CMIIW.

So let's say i schedule an online backup at 19.00 and finished at 02.00 (backup 1)

At 08.00 some users start to make transactions. But unexpected failure occurs at 14.00.

Therefore we lost works between 08.00 - 14.00, and we should recover from backup 1.

Is there something we can do for that lost works?

And what happen when failure occurs when the backup process is in progress?

Do we discard them and recover the system from the previously backed up files?

best regards,

[mohammed_anish]

Hello Bobby,

If we are having only one Online backup + redo log scheduled for a day and that is the only backup activity for that day, then there is a greater risk of loosing all transactions done from 02:00 till 14:00.

To minimise the loss, schedule archive log backup every 15 min / 30 min/ 1 hour (more frequent => lesser loss). Then if we get failure, then we can restore the database from "backup 1" and then recover to the time just before the crash by applying the redo logs that we have been backing up every 15 min / 30 min/ 1 hour.

Regards,

Anish

[hemanth2]

Hi bobby,

The number of online redo log files in an SAP server are fixed. there are normally 4 of them.....they can have max. 50MB size (you can specify this during installation). They are written during the normal working hours of the SAP system using the LGWR (logwriter) process.

You can take the backup of the online redo log files with the SAPDATA that you are backing up (plus the control file). The online redo log files are archived to offline (or archive redo log files) by a process called ARCH.

When will this backup process finish -> depends on the backup technique being used and whether speedy processed like Backint etc. are being employed.

I hope it makes sense. I recommend you to unbergo SAP courses like ADM 505/506 for more on this..

regards,

hemanth

[hemanth2]

Hi bobby,

The number of online redo log files in an SAP server are fixed. there are normally 4 of them.....they can have max. 50MB size (you can specify this during installation). They are written during the normal working hours of the SAP system using the LGWR (logwriter) process.

You can take the backup of the online redo log files with the SAPDATA that you are backing up (plus the control file). The online redo log files are archived to offline (or archive redo log files) by a process called ARCH.

When will this backup process finish -> depends on the backup technique being used and whether speedy processed like Backint etc. are being employed.

I hope it makes sense. I recommend you to unbergo SAP courses like ADM 505/506 for more on this..

regards,

hemanth

[fidel_vales]

Hi hemanth,

I think you are confused (or you have not explained yourself properly)

> The number of online redo log files in an SAP server are fixed.

Are you trying to say that the number of redo log files cannot be changed?

If so, that is not correct.

> there are normally 4 of them.....they can have max. 50MB size

By default, an SAP nstallation will have 4 online redo log (mirrored) with a size of 50 Mb.

This does not means that you cannot change it (the number or the size)

Depending on your system load it is possible that you have to change it. For example:

• SAP does not recommend to have more than one redo log switch per minute (during peak times) The only way to avoid it is to increase the size of it

• If you are getting "checkpoint not complete" in your alert log, then you will have to increase the number of redo log groups.

Depending on the backup type you are doing it could be wise to back up the online redo log or not. The best option would be to use BRBACKUP for the backups and BRRECOVER if you need to perform any kind of recovery.

Regarding the OP, I'll recommend you to take a look at the answer from Stefan Koehler as it is the best one.

And, of course, check the documentation and the training mentioned.

Keep in mind that the only thing where a DBA cannot make mistakes is restore/recovery and this depends on your backups

[stefan_koehler]

Hello Fidel,

i am feeling honored

> Keep in mind that the only thing where a DBA cannot make mistakes is restore/recovery and this depends on your backups

In general i would say you are right, but i have seen already one case where this statement is not true.

Some time ago i got a call from a colleague where his database crashed, the online redolog files were corrupted and the recovery was not working.

If you don't look really close at this point you are in trouble and can not continue the recovery. Let explain this on a example.

This demonstration is done on an oracle 10.2.0.4, but it work on every other version too.

Let's simulate a crash

SQL> shutdown abort;

Corrupt/delete a specifc redolog file

SQL> startup
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/TST/oradata/redolog/redo03.log'
ORA-27037: unable to obtain file status

Ok - so far so good, lets check the groups and files

SQL> select GROUP#, STATUS, MEMBER from v$logfile where TYPE = 'ONLINE';
    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------
         1         /oracle/TST/oradata/redolog/redo01.log
         2         /oracle/TST/oradata/redolog/redo02.log
         3         /oracle/TST/oradata/redolog/redo03.log


SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         95   52428800          1 NO  CURRENT                5117817 06-JAN-09
         3          1         94   52428800          1 YES ACTIVE                 5117814 06-JAN-09
         2          1         93   52428800          1 YES ACTIVE                 5112855 15-DEC-08

What's the situation?

The online redolog file of group 3 is lost/corrupted and this group is needed to perform a complete recovery (see status ACTIVE).

But you are lucky, because of this group 3 is already archived - so you can perform a complete recovery.

Now let's perform a complete recovery but with UNTIL clause (because we need to jump between the online and archived redologfiles)

SQL> recover database until cancel;
ORA-00279: change 5116194 generated at 01/06/2009 21:06:48 needed for thread 1
ORA-00289: suggestion : /oracle/TST/oraarch/TST_1_93_6b8c0516_666969185.arc
ORA-00280: change 5116194 for thread 1 is in sequence #93
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 5117814 generated at 01/06/2009 21:08:47 needed for thread 1
ORA-00289: suggestion : /oracle/TST/oraarch/TST_1_94_6b8c0516_666969185.arc
ORA-00280: change 5117814 for thread 1 is in sequence #94
ORA-00278: log file '/oracle/TST/oraarch/TST_1_93_6b8c0516_666969185.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 5117817 generated at 01/06/2009 21:08:51 needed for thread 1
ORA-00289: suggestion : /oracle/TST/oraarch/TST_1_95_6b8c0516_666969185.arc
ORA-00280: change 5117817 for thread 1 is in sequence #95
ORA-00278: log file '/oracle/TST/oraarch/TST_1_94_6b8c0516_666969185.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/TST/oradata/redolog/redo01.log
Log applied.
Media recovery complete.

Now execute an OPEN NORESETLOGS accidentally (maybe the dba think it is not necessary because of the complete recovery) and try an OPEN RESETLOGS after:

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/TST/oradata/redolog/redo03.log'
ORA-27037: unable to obtain file status

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

So now you are lost.. you can't execute an UNTIL CANCEL anymore which would be needed to perform a successful OPEN RESETLOGS.

In this special case you can do mistakes and you have to restore the whole database and perform the same recovery again and end with OPEN RESETLOGS.

Just for information

Regards

Stefan

[Former Member]

Hi ,

redo log file is created everytime there's operation that change datafile right?

No.

If i schedule this type of backup everyday, it means it will backup all datafiles, along with redo log file when transaction take places, at my pre-defined hours, right?

Yes. Online backup only contains the current one but not the latest redo log files.

When will this backup process finished

?

It will complete once all datafiles and redo log files backed up.

when will this cycle come to an end?

There is a backup cycle defined at sap level:

SAP recommends a backup cycle of four weeks. The backup cycle must be the

same for database backups and offline redo log backups:

• Perform a complete online backup each work day.

• Perform a complete offline backup at least once in the cycle.

• Back up the offline redo log files on each work day, as well as after every

online and offline backup. Ensure that you back up every offline redo log file

twice, on separate tapes, before the file is deleted in the archive directory.

You can also perform additional backups after database structure modification,

but this is not necessarily required. BRRECOVER handles such modifications

automatically during recovery.

Hope it clears your doubt.

Thanks

Kishore

[Former Member]

Hi ,

redo log file is created everytime there's operation that change datafile right?

Yes.

If i schedule this type of backup everyday, it means it will backup all datafiles, along with redo log file when transaction take places, at my pre-defined hours, right?

Yes. Online backup only contains the current one but not the latest redo log files.

When will this backup process finished

?

It will complete once all datafiles and redo log files backed up.

when will this cycle come to an end?

There is a backup cycle defined at sap level:

SAP recommends a backup cycle of four weeks. The backup cycle must be the

same for database backups and offline redo log backups:

• Perform a complete online backup each work day.

• Perform a complete offline backup at least once in the cycle.

• Back up the offline redo log files on each work day, as well as after every

online and offline backup. Ensure that you back up every offline redo log file

twice, on separate tapes, before the file is deleted in the archive directory.

You can also perform additional backups after database structure modification,

but this is not necessarily required. BRRECOVER handles such modifications

automatically during recovery.

Hope it clears your doubt.

Thanks

Kishore