Restore DB2 from Snapshot

[ANDREAS_JANKOWIAK]

Hello,

happy new year to all of you.

We backup our DB2 (SAP) databases via SnapCreator DB2 Plugin.

I have a question regarding the restore:

The only way to restore the database is to restore the snapshot and then do a rollforward via "db2 rollforward..."  , right?   Is it possible to use db2 recover (via db2 history file) also?  I guess not because

db2 recover assume a classical db2 backup image as a restore source. Is there another way?   Db2 rollforward is not very "friendly" in my opinion, especially if you have to do some more PIT recoverys.

Thanks a lot!

BR,

Andreas

[oommen]

Andreas - Happy New Year

The backups done thru Snap Creator doesn't update the DB2 history files, which means you cannot use history file to recover a DB2 database from storage snapshot. The process to recover the database would be,

For Recoverable database (Archive Logging)

1. Do snap restore (only for data volumes)

2. as db2 instance user - db2inidb dbname as mirror

3. db2 rollforward database dbname to <PIT>

For Non-recoverable database (Circular Logging)

1. Do snap restore (ALL volumes)

2. db2 restart db dbname write resume

If you have any questions please do let me know. Also DB2 is my good friend, and I should be able help you out )

Regards,

Bobby

[ANDREAS_JANKOWIAK]

Hi Bobby,

thanks for your reply.

In my opinion the db2 rollforward command is very uncomfortable if you have to do more PIT recoverys to find the correct time you need.

Rollforward to end of logs is no problem

Rollforward to a PIT is ok

But if you have to do some more PIT rollforwards you will have different log chains containing log files with the same name but of course containing different LSNs.

Then it will be difficult to reach the correct point in time because db2 rollforward is always using the log from the latest chain (the newest log file with the same number).

So you have to rename the log chain(s) you don't need or you have to manually copy the needed log archives to log_dir directory... not very user friendly.

DB2 recover can handle different log chains without any problem because it uses the db2 history file to choose the latest backup image and the correct log files. So it is very easy to reach the correct time.

If there is no other option to do a recovery in combination with snapshots I think this is a big disadvantage compared to the traditional db2 backup.

Maybe there is a better option when using rollforward, do you have some tips?

Thanks again!

BR,

Andreas

[ANDREAS_JANKOWIAK]

Anyone?

[ktenzer]

I think you will need to wait for Bobby (he should be back next week) he is the DB2 expert at NetApp and this is above my skill level unfortunately

Keith

[oommen]

Andreas,

Sorry I was pretty swamped.

To me the RF operation in DB2 is no different than other databases. You should know your PIT to do the recovery, I understand there could be scenarios where you have to find the data corruption point and you will end up doing more than one RF operation. In those cases I would clone the database rather than trying the recovery on the parent volume.

Not sure why would need to copy the LOGS to get around different LOG chains. You do the snap restore only on the Data volumes right? Sorry, I didn't quite get you on the 'different log chain' scenario.

Bobby.

[ANDREAS_JANKOWIAK]

Hi Bobby,

thanks again for your response.

Here is an example where rollforward in my opinion has some limitations:

1. You have created a user at 14:05 in the database.

2. You do a restore (Data Volume + DB Instance FS (Log Header File)

3. You do a PIT to 14:03,  with log files 0-10 from Chain 0    (Chain 0 contains Log 0 - 15) 

4. The db is started and it creates a new log 10 in Chain 1  (transactional work is done and Logs 10 - 13 are created)

Now you decide to go back to 14:05 when the user was created. This will not work because rollforward will use the log 10 from chain 1 and not from chain 0. So you will not be able to reach this point.

If you rename / move the chain 1 to something else (so the db is not able to find the latest log 10) , the database will colllect log 10 from chain 0 (and maybe further neeeded logs) , you will be able to reach the point the user was created.

Another option would be to use rollforward with noretrieve and copy the needed log files manually from chain 0.

DB2 recover which is using the history file can handle this without any problem, no user intervention is required.

I know maybe this example is a bit strange... but in may opinion this should be possible.  Rollforward is much more comblicated in my opinion.

Best Regards,

Andreas

[ANDREAS_JANKOWIAK]

Hi Bobby,

db2 really behaves strange when restoring from the snapshot and doing some rollforwards.

I do:

1. Restore db from snapshot  (data Volume with db directory)

2. Rollforward to PIT 14:00   --> new Chain 01 is created

3. Restore db from snapshot

4. Rollfoward to PIT 13:00 --> NO new chain is created... instead db overwrites log files in Chain 01. 

This is not the normal behaviour for db2.   When using db2 backup images there will be a new Chain with every PIT recovery and this makes sense.

Have you seen this behaviour before?

Do you know if there is a counter for the log chains?   Is the chain information somewhere saved in a file?  How is the chain number exactly calculated?

Thanks again.

BR,

Andreas

Nachricht wurde geändert durch: Andreas Jankowiak  I have tested this scenario again in a second system.  Ever time I do a second PIT scenario there is no second chain created and original archive logs are overwritten....  Is this scenario tested by Netapp?   BR, Andreas

Nachricht wurde geändert durch: Andreas Jankowiak I guess I know what the problem is.. The log chain is stored in the LFH file. As we overwrite the LFH file with every restore there is no new chain. So in my opinion this solution is not working correctly.  Was this ever tested by NetApp? Does anyone have any tip?

[oommen]

Andreas,

I have seen this in the past, if I try to do the RF more than one time. What I believe is, as snapshot process doesn't update the DB2 history file, DB2 doesn't have a clue whether a recovery is been done. If you are planning to do a one time RF process you are good. But if you are trying to find a good PIT to recover the database, I would use the FlexClone process.

We are working with IBM on an API so that the history files get updated during a snapshot event. This will ensure applications like SAP can get the BR reports in the GUI.

Bobby

[ANDREAS_JANKOWIAK]

Hi Bobby,

maybe this restriction should be documented in the NetApp documentation regarding db2 on Netapp and SnapCreator.

Do you think that the log header file is updated with information from the db2 history file?  I don't think so.

The API to get the history file updated would be wonderful. but I don't think that it will solved the "problem" with the log chains, right?

The problem is that we overwrite the log header file with every snap restore.

Do you know when this API is planed for delievery?  That would be great.

Best Regards,

Andreas

[oommen]

Andreas,

Currently the API documentation is with IBM legal. I have an onsite meeting with IBM team this week and will be able to get an update soon.

I will do more investigation on the LOG header chain. Sorry, its been really hectic for the past couple of weeks.

Regards,

Bobby

[ANDREAS_JANKOWIAK]

Hi Bobby,

thank you very much.

It would be great if we could stay in touch.

This is very important for my customers.

BR,

Andreas