cancel
Showing results for 
Search instead for 
Did you mean: 

Recovery backup prior the last and all following log until last minute

former_member239632
Participant
0 Kudos

Hi Guru,

The scenario is the following:

I must recover the db from a full backup prior the last full backup. The last backup was damaged. In order to recover until the last second I must restore the prior backup and the log between this backup and the last, and all log following the last bakup. The restore is without initialization, so the log area is also restored into the database. During the procedure I don't find this procedure. I'm able to recovert until last second only with initialization, or restore previous full backup and following log until the next full backup.

Example:

Backup full A Log1 Log2 Backup full B Log2 Log3

|__________|____|__________|__________|___| Log Area: 30% not saved

| |

Sucessfull Damaged

Restore chain:

Backup full A + Log1 + Log2 + Log3 + Log4 + Log Area 30% ---> System Consistent

How can I implement this scenario? I try but I have no success.

Please help me.

Thaks in advance.

Fabio Sambugaro

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Fabio,

I think I know what is this about

you were restoring database (without initialization) and your plan was to restore

Backup full A + Log1 + Log2 + Log3 + Log4 + Log Area 30% ---> System Consistent

assuming that last 30% of log entries are still in log area so you do not need to recover them from backup

however what happed was:

Backup full A + Log1 + Log2 + End of recovery

and at this point recovery wizard did not ask for log3 and log4 , right ?

the reason for this is that information from log3 and log 4 is still in log area , even thou the backup were created , this does not mean we have deleted the log area. the log area will be rewritten only once it is necessary. So if you are using autolog backup with staging area = 1/3 log area , it might be possible that you do not need last 2-3 log backup at all because the information is still in logs.

Regards

Ivan

former_member239632
Participant
0 Kudos

Hi Ivan,

I think what you say is not completly correct. The log area hasn't yet the information on log3 and log4 . After various test, analisys and watching training documentation the true behaviour is the following:

Case restore without initialization, also the log area is recovered on database.

- Select Recovery - Recovery... - Restore a specified backup from history - Select the previous backup from history list.

- It is important to specify also the Restore database until a specific time option. Specify the date and time at whitch point to recover the database: in our case the actual date and time.

- Note that in the window that display the steps required to perform a recovery we can see only the database medium and not all the logs mediums to recover until the point in time option specified.

- Start the restoration. At the end the system ask to start the database instance. Is during this operation that the database takes into account the point in time option and check the existance of logs to recover the database. If so the recovery is performed, but we don't see whitch log were restored. For me this is a lack. Also the log area is restored in this case.

If I perform the same steps with the initialization option and the database recovery display me the database medium we select and all the logs that must be restored until the point in time option specified.

This difference is not justified for me, if the restore include also the recovery of logs, those logs must be shown in both cases.

What do you think about this? Please give me suggestion, confirmations and corrections if necessary. Or give explanation about your experiences to implements this scenario.

Thaks.

Best regards,

Fabio Sambugaro.

lbreddemann
Active Contributor
0 Kudos

Hi Fabio,

nope - you did not get it right yet, sorry.

> The log area hasn't yet the information on log3 and log4

How do you know that?`

Show us the output from "db_restartinfo" and your backup history.

> Case restore without initialization, also the log area is recovered on database.

As I've written: we never restore the log area. We just don't do it.

To recover we need log entries. These log entries are either read from the log area or from log backups.

But we never put log entries from a log backup to the log area - it's simply not necessary.

> - Select Recovery - Recovery... - Restore a specified backup from history - Select the previous backup from history list.

> - It is important to specify also the Restore database until a specific time option. Specify the date and time at whitch point to recover the database: in our case the actual date and time.

If you want to recover to the latest state of the database, then you don't need to specify a 'restart until' timestamp. This is only necessary when you want to stop the recovery to perform a point-in-time recovery.

> - Note that in the window that display the steps required to perform a recovery we can see only the database medium and not all the logs mediums to recover until the point in time option specified.

If you've specified "Restore a specified backup from history" then you'll see all necessary log backups in the preview list.

If you don't get this, something must be wrong.

Again the backup history should provide some insight here.

> - Start the restoration. At the end the system ask to start the database instance. Is during this operation that the database takes into account the point in time option and check the existance of logs to recover the database. If so the recovery is performed, but we don't see whitch log were restored. For me this is a lack. Also the log area is restored in this case.

Wrong again - one of the thing the recovery wizard does before doing the actual recovery is to check, what needs to be recovered at all.

It uses the 'db_restart' command for this and figures out:

1. what is the first logpage available in the log area

2. what is the current/highes log page number available in the log area.

Now all it takes to determine what backups need to be recovered is to check what log page was current, when the data backup, that should be used for the recovery, was created.

If this log page is >= the first log page on the log area, we'll find all log entries in the log area, so no log backup needs to be recovered.

> If I perform the same steps with the initialization option and the database recovery display me the database medium we select and all the logs that must be restored until the point in time option specified.

Sure - if you delete all log entries before the recovery, we need to read all of them from backups.

> This difference is not justified for me, if the restore include also the recovery of logs, those logs must be shown in both cases.

No, again. As explained, we read as much log information from the log area as possible.

If we find all necessary data in the log area, we don't need to read the log backups.

Quite simple...

Maybe one mental obsticle for you is, that the log data is still present in the log area after you performed log backups, although you see the "log filling bar" getting smaller in DBMGUI when you perform a log backup.

The reason for that is simple: the 'log filling bar' does not show the filling of the log volume, but the amount of not-backed up log data with respect to the whole log area.

When we write log data, we mark it as "not backed up". This means: don't overwrite it!

When we perform a log backup we mark the saved log data as "backed up". This means: if you need this space, you can overwrite it.

It does however not mean that we overwrite or delete this data.

Hope this helps a bit.

regards,

Lars

former_member239632
Participant
0 Kudos

Hi Lars,

Finally I had good answers, thanks for your patience.

>

> Hi Fabio,

>

> nope - you did not get it right yet, sorry.

> > The log area hasn't yet the information on log3 and log4

>

> How do you know that?`

> Show us the output from "db_restartinfo" and your backup history.

I had no clear the meaning of log area bar. Now it's all ok.

>

> > Case restore without initialization, also the log area is recovered on database.

>

> As I've written: we never restore the log area. We just don't do it.

> To recover we need log entries. These log entries are either read from the log area or from log backups.

> But we never put log entries from a log backup to the log area - it's simply not necessary.

>

It's correct, I verify it.

> > - Select Recovery - Recovery... - Restore a specified backup from history - Select the previous backup from history list.

> > - It is important to specify also the Restore database until a specific time option. Specify the date and time at whitch point to recover the database: in our case the actual date and time.

>

> If you want to recover to the latest state of the database, then you don't need to specify a 'restart until' timestamp. This is only necessary when you want to stop the recovery to perform a point-in-time recovery.

It's correct I test it after this reply. If no point in time is specified the database recovered at latest state.

>

> > - Note that in the window that display the steps required to perform a recovery we can see only the database medium and not all the logs mediums to recover until the point in time option specified.

>

> If you've specified "Restore a specified backup from history" then you'll see all necessary log backups in the preview list.

> If you don't get this, something must be wrong.

> Again the backup history should provide some insight here.

I test the recovery until latest state, and I think that in the log area I no longer have any information for recovery. But I don't see the log needed for restoration. I should clear the log area already saved and try the recovery in order to check if all logs shows.

How can I force a log area clean?

>

> > - Start the restoration. At the end the system ask to start the database instance. Is during this operation that the database takes into account the point in time option and check the existance of logs to recover the database. If so the recovery is performed, but we don't see whitch log were restored. For me this is a lack. Also the log area is restored in this case.

>

> Wrong again - one of the thing the recovery wizard does before doing the actual recovery is to check, what needs to be recovered at all.

> It uses the 'db_restart' command for this and figures out:

> 1. what is the first logpage available in the log area

> 2. what is the current/highes log page number available in the log area.

>

> Now all it takes to determine what backups need to be recovered is to check what log page was current, when the data backup, that should be used for the recovery, was created.

> If this log page is >= the first log page on the log area, we'll find all log entries in the log area, so no log backup needs to be recovered.

>

> > If I perform the same steps with the initialization option and the database recovery display me the database medium we select and all the logs that must be restored until the point in time option specified.

>

> Sure - if you delete all log entries before the recovery, we need to read all of them from backups.

>

> > This difference is not justified for me, if the restore include also the recovery of logs, those logs must be shown in both cases.

>

> No, again. As explained, we read as much log information from the log area as possible.

> If we find all necessary data in the log area, we don't need to read the log backups.

> Quite simple...

>

> Maybe one mental obsticle for you is, that the log data is still present in the log area after you performed log backups, although you see the "log filling bar" getting smaller in DBMGUI when you perform a log backup.

> The reason for that is simple: the 'log filling bar' does not show the filling of the log volume, but the amount of not-backed up log data with respect to the whole log area.

> When we write log data, we mark it as "not backed up". This means: don't overwrite it!

> When we perform a log backup we mark the saved log data as "backed up". This means: if you need this space, you can overwrite it.

> It does however not mean that we overwrite or delete this data.

>

> Hope this helps a bit.

>

> regards,

> Lars

Thanks

Regards,

Fabio Sambugaro

lbreddemann
Active Contributor
0 Kudos

Hi Antonio,

> Finally I had good answers, thanks for your patience.

You're welcome.

> I had no clear the meaning of log area bar. Now it's all ok.

I also find the "filling bar" counter intuitive - but I'm not the designer of this thing...

> I test the recovery until latest state, and I think that in the log area I no longer have any information for recovery. But I don't see the log needed for restoration. I should clear the log area already saved and try the recovery in order to check if all logs shows.

> How can I force a log area clean?

To figure out, what information is available in the log area, use the "db_restartinfo" command.

To clear the log area before performing a recovery, just use "recovery with initialization" - it just does this.

best regards,

Lars

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Hi Fabio,

(have the user accounts become expensive or is Frighetto Antonio your alias ? )

Please use the "Restore backup from Backup-History" option in the recovery wizard to choose the second last backup.

Be aware that there is no way to restore the log area at all!

It doesn't matter what kind of restore you choose.

When you use "recovery with initialization" the log area is simply cleared and its contents ignored.

When you use just "recovery" the log area is used for the recovery - thus all necessary log entries that can be found in there are taken instead of reading them (slowly) from the log backups.

So it might well be (depending on the amount of log generated and the size of the log area) that no log backup need to be used for a complete recovery if all log entries are still present in the log area.

When you perfom the recovery of log backups the data is not written back to the log area, but directly read by the kernel to build up the internal redo-files that are used to 'replay' the uncommitted transactions.

In any case: before you do anything further, perform an additional log backup right away to be sure that you don't loose unsaved log information from the log area due to some recovery 'accident' !

regards,

Lars

former_member239632
Participant
0 Kudos

Hi Lars,

you're telling me that if I choose the backup to restore from the backup history, the log area isn't restored?

Can I restore all the logs after this backup until the last minute? As previous example

Backup history:

backupA log1 log2 log3 backupB log4 log5 log6

Restore chain:

backupA log1 log2 log3 log4 log5 log6

Is this scenario (Restore chain) possible? And how? Why the log area is lost?

Thaks.

Fabio (no alias )