DB2 database restoration from a snapshot

[SERGEY_DB2]

Hi all.

We have a SAP system with DB2 database on a Virtual Machine on Windows. VM has 2 disks on one LUN on NetApp - a system disk and a disk with SAP and DB2 (we know this is not the best practice but this machine is created only for tests).

We made a snapshot of the LUN with SnapVault and SnapCreator (agent is installed on VM). I don't know all details because the Virtual Machine and NetApp are in a Public Cloud and backup is a cloud service. We can only request to backup or restore some LUN.

After the restoration of  the LUN with the whole VM we're unable to rollforward log files.

db2start

db2inidb TWN as mirror

DBT1008N  Database "TWN" is not a split mirror image.

What is wrong with DB ?

Best regards,

Sergey

[oommen]

Sergey,

sorry to hear that you are having issues.

when you do a db2 list db directory - you will see a db path. Under which LUN you have that.  If that path is not part of Snapshot then you might get into this issue. Also are you using SDW - which is a requirement when you have DB2 on windows.

regards,

Bobby.

[SERGEY_DB2]

Hi Bobby,

thanks for your responce.

As I said above we have one LUN that contains two disks - system disk C and disk D with SAP and DB2 that is one LUN contains the whole virtual machine including db path. It was a part of the snapshot.

D:\db2\db2twn\db2_software\BIN>db2 list db directory

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias                       = TWN

Database name                        = TWN

Local database directory             = D:\db2\TWN

Database release level               = d.00

Comment                              = SAP database TWN

Directory entry type                 = Indirect

Catalog database partition number    = 0

Alternate server hostname            =

Alternate server port number         =

What is SDW ? Is it related to DB2 or NetApp ?

Best regards,

Sergey

[ktenzer]

Hi Sergey,

I am no DB2 expert, bobby is so you getting the right help here!

However I figured I would chime in on SDW. This is SnapDrive Windows and you need to use this to take snapshot in windows as it handles the windows file system / vss layer.

Snap Creator can either take snapshots directly on ONTAP or it can call SnapDrive Windows or Unix. To enable snapdrive you need to install it on DB2 host and then configure in SC config the following parameter:

NTAP_SNAPSHOT_CREATE_CMD01="C:\Program Files\Netapp\SnapDrive\SDCLI.exe" snap create -s %SNAME-%SNAP_TYPE_%SNAP_TIME -D F G

F and G would be drive letters for example.

You also need on SC Agent under etc/allowed_commands.config you need to allow snapdrive command as follows:

command: SDCLI.exe

Also I have a question for you! Can you provide more details of the public cloud service you are using and built? Also provide any info on how Snap Creator is integrated into this service? We are trying to understand cloud use cases and this is very interesting so if you can help that would be huge.

I am sure booby will add more detail but hopefully this helps

Keith

[oommen]

thanks Keith.

You can have SAP/DB2 (data and logs) under the same LUN and do a snap restore - it should work. I am under the assumption you used DB2 plug-in while configuring Snap Creator. If you think doing a webex sometime early next week to take a look at your setup, please do let me know.

Regards,

Bobby.

[SERGEY_DB2]

Hi, Bobby and Keith,

It seems that the database is in wrong status.

It has to be in WRITE SUSPEND status but it isn't.

I started the instance, tried to initialize the restored DB with db2inidb and wasn't able to do it, connected to the DB and the DB started crash recovery and then came to a normal status.

The cloud provider's engineers sent me the logs from SnapCreator and I see that they quiesced the DB, took a snapshot, then unquiesced the DB - all is correct but the DB is not in "frozen" state after the restoration.

I don't know how to attach a file to my message so I send the log to your mails. May be you can say what was wrong with the snapshot.

Is the SnapDrive a requirement only for Windows ? We have virtual machines on Linux as well and 2 systems on AIX. All systems have DB2 database and we're going to backup them with SnapCreator.

Keith, I'm sending you some info about our cloud solution by e-mail also.

Best regards,

Sergey

[SERGEY_DB2]

Hi, Bobby.

Please find attached the log from SnapCreator I wrote you about in the NetApp forum.

Best regards,

Sergey

[SERGEY_DB2]

Hi, Keith.

Please find attached the log from SnapCreator I wrote you about in the NetApp forum.

Now about the cloud.

Our company is a brewing company which was founded 2 years ago after the merging of two brewing companies.

We had two similar infrastructures – some infrastructure Windows servers (Exchange Server, Active Directory, FTP servers and other) and SAP infrastructure (about 30 SAP systems each) – AIX/DB2 of the first company and Solaris/Oracle of the second. The first infrastructure was located on 2 sites in 300 meters between them – on a plant and warehouses. The second infrastructure was located in the headquarter and a rented datacenter for DRP. And we had the task to design and implement the safe and secure architecture of united infrastructures that wouldn’t depend on location of plants and offices.

So we’ve developed the infrastructure and now we are in process of migration of our business-critical systems to the cloud. The next step is moving non-critical (development, quality assurance, test and other) systems to the same datacenters on collocation to save money. Then instead of upgrading of this hardware we’ll move these systems to the cloud as well.

The scheme of our cloud solution is in the attachment.

Our cloud provider have 2 datacenters – DС1 and DС2. The distance between them is about 30 km.

The infrastructure is mirrored in both datacenters. We use VMWare as a virtual platform (Windows and Linux RedHat VM’s) and geocluster of two synchronized NetApp 6210 as storage. This isn’t a dedicated for us infrastructure we share it with other cloud clients.

We rent on each side:

~250 CPU, >900 GB RAM, ~20 TB SAS discs for systems, >40 TB SATA discs for backups.

I can tell little about the SnapCreator. Our cloud provider has an infrastructure for SnapVault backups. We just installed SnapCreator agent 4.1 on our servers and gave them the info for backups – host name and address, instance and database name, path to db2 command. At the moment we are in the process of backup/restore/recovery tests.

We requested to make a snapshots on Windows and Linux VM’s, made some changes in the databases, requested to restore the snapshots and got this problem I wrote in my message in the NetApp forum on both servers.

Of course when we finish our tests we’ll send the schedule and parameters of backups to the provider and backups will be performed automatically.

Best regards,

Sergey

[ktenzer]

Hi Sergey,

A huge thanks for the details this is exactly the info we are trying to gather and this helps us improve the product and focus on what customers need.

As for your problem I looked through attached log but dont see an error, Snap Creator is putting database into write suspend mode etc. The issue is you aren't using SnapDrive Windows and you need this for DB2 on windows. We have also observed this behavior with DB2 on windows it has to do with the windows file system not being in-sync or consistent at time of snapshot.

As I mentioned in my earlier post Snap Creator can use SnapDrive for Windows to take snapshot, this will guarantee the state of file system as SnapDrive Windows handles the windows file system and ensure file system consistency.

The steps to resolve this issue are:

1) install SnapDrive for Windows (you can download this from now.netapp.com)

2) update Snap Creator configuration to use SnapDrive for Windows

NTAP_SNAPSHOT_CREATE_CMD01="C:\Program Files\Netapp\SnapDrive\SDCLI.exe" snap create -s %SNAME-%SNAP_TYPE_recent -D F G

SNAP_TIMESTAMP_ONLY=N

OR

NTAP_SNAPSHOT_CREATE_CMD01="C:\Program Files\Netapp\SnapDrive\SDCLI.exe" snap create -s %SNAME-%SNAP_TYPE_%SNAP_TIME -D F G

SNAP_TIMESTAMP_ONLY=Y

The -D option specifies the drives so this should be the dive letters of the DB2 database, in this example G:\ and F:\

You can also and should test snapdrive first and ensure you can create snapshots before configuring Snap Creator.

Let me know if this resolved your issue?

Regards,

Keith

[ktenzer]

Also you need to allow SnapDrive commands on agent so you need to edit the etc/allow_commands.config file under scAgent4.1 and add following:

command: "C:\Program Files\Netapp\SnapDrive\SDCLI.exe"

I think you can also do it without quotes

command: C:\Program Files\Netapp\SnapDrive\SDCLI.exe

Regards,

Keith

[SERGEY_DB2]

Hi Keith,

Do we need SnapDrive for Linux also ?

We restored 2 virtual machines - one on Windows and another on Linux RedHat.

And both have the same problem - I don't see the messages related to WRITE SUSPEND in the database log of the restored server.

It seems like we took the snapshot earlier on unfrozen DB or WRITE SUSPEND didn't finished when the snapshot was taken.

Best regards,

Sergey

[ktenzer]

Hi Sergey,

For Linux you shouldnt need SnapDrive for Unix. I have never heard of this problem on Unix systems. Bobby would have to comment on that in regards to DB2.

Certainly adding SnapDrive for Unix wont hurt anything and could help if you are having problems with the file system consistency.

Keith

[SERGEY_DB2]

Hi Keith,

Ok, we’ll try SnapDrive.

I’ll let you know the results.

Thanks for your help.

Best regards,

Sergey

[ktenzer]

Ok great let us know

Bobby is on EST so he should be on in a few hours and can comment as well

Keith

[oommen]

Good Team work - Sergey and Keith -  This is what I would like to hear every  morning.

Yep - You are facing the exact issue when you don't have SDW (again only Windows). For Unix you don't necessarily need Snap Drive as DB2 doesn't use any File system buffering, how ever Snap Drive handles some of the steps when you do the recovery. But my recommendation is - you can easily do those with host commands ex: re-mounting File systems, re-scanning new devices etc...

Thanks again Sergey for giving the details about your environment. The feedback from our customers is key to our success.

Regards,

Bobby.

[SERGEY_DB2]

Hi, Bobby, Hi, Keith,

We recreated test virtual machines, set up snapshot backups again and this time everything worked fine. Both snapshots (Windows and Linux) were correct and we were able to initialize the databases as mirror with db2inidb and rollforwarded them to the point in time we needed. We did it without SnapDrive. And the cloud provider's engineers said they changed nothing in backups.

I don't know why the snapshots were incorrect the previous time.

Thanks for your time and help.

Best regards,

Sergey

[ktenzer]

Hi Sergey,

Thanks for getting back to us

Glad things work. It must have been something with the original setup.

Keith