Backup a MAXDB standby database

[rayko_aben]

Hi experts

we are going to implement a MAXDB standby database for our SAP solution.

May be by ourself ( scripting ) or with third party product "Libelle".

Because of a 24x7 availability of the SAP system it would be a big advantage to backup ONLY the standby database.

Now my question...

Is this possible with a MAXDB !!! ( because of the recovery mode of the standby database ) and has anybody experience with this?

Are we able o recover our original production system with this backup?

I think, we have to interrupt the log shipping, than restart the standby db and than backup, but we need a solution without interrupting the chain. This would be ideal.

Thanks in advance for any ideas or alternatives.

Kind regards

Rayko

[lbreddemann · Accepted solution]

> we are going to implement a MAXDB standby database for our SAP solution.

> May be by ourself ( scripting ) or with third party product "Libelle".

Good choice!

> Because of a 24x7 availability of the SAP system it would be a big advantage to backup ONLY the standby database.

> Now my question...

> Is this possible with a MAXDB !!! ( because of the recovery mode of the standby database ) and has anybody experience with this?

Hmm... usually you would use a question mark (?) at the end of questions and not a triple exlamation mark

And: yes, it is of course possible to just backup the standby database.

The database is not in recovery mode - but in ADMIN mode and you can take backups in ADMIN mode.

Therefore: sure you can do this!

However, usually there is no problem with just taking the backup from the primary instance directly.

> Are we able o recover our original production system with this backup?

Well, technically it's possible, but if you're able to do it...

> I think, we have to interrupt the log shipping, than restart the standby db and than backup, but we need a solution without interrupting the chain. This would be ideal.

Interrupt log shipping? NO - what for?

Interrupt log applying? Yes, of course!

Restart the database? NO NO NO - please - before desigining the super clever definitive backup scenario, make sure that you fully understand how backup and recovery works with MaxDB!

There are tons of documentation on this. Read them!

Try things out - yourself.

Make some experience.

Then you know what works and how it works.

Then design your backup scenario.

regards,

Lars

[markus_doehr2 · Accepted solution]

> However, usually there is no problem with just taking the backup from the primary instance directly.

I wouldn't sign that.

A backup, especially if done on high-speed disks with parallel backup devices, adds quite a huge load on the system and trashes the SAN caches which will lead to quite some performance impact (single I/O times going up from 4 -6 ms to > 20 ms). So I'd consider too taking the backup of the standby - but this only if it's on a physically different I/O subsystem.

Markus

[lbreddemann · Accepted solution]

> > However, usually there is no problem with just taking the backup from the primary instance directly.

>

> I wouldn't sign that.

>

> A backup, especially if done on high-speed disks with parallel backup devices, adds quite a huge load on the system and trashes the SAN caches which will lead to quite some performance impact (single I/O times going up from 4 -6 ms to > 20 ms). So I'd consider too taking the backup of the standby - but this only if it's on a physically different I/O subsystem.

Sure there are situations possible, where you have negative impacts that actually end up with decreased end-user performance experience.

And loading lots of data of course adds to the bandwith utilization of the SAN.

However, by disabling filesystem caches (via DIRECT_IO and/or mount options) you should be able to avoid SAN cache invalidations. (and it is of course questionable how much benefit you can get from a SAN cache if your I/O is largely random).

Beyond that - many SANs today support actions like flash copy that can be used for split mirror backups.

But as I wrote: usually this is not what most customers experience.

Most customers actually take their backups from the live production system without effects that let "the business" complain.

And that's what's all about in the end.

Don't get me wrong - I'm a huge fan of having a standby db.

But it's not like you cannot use the normal backup for your primary instances.

regards,

Lars

[markus_doehr2 · Accepted solution]

> However, by disabling filesystem caches (via DIRECT_IO and/or mount options) you should be able to avoid SAN cache invalidations. (and it is of course questionable how much benefit you can get from a SAN cache if your I/O is largely random).

As far as I know "Direct I/O" bypasses the filesystem cache, not the SAN cache, the DB doesn't know anything about SAN caches. Since at least our databases run on raw devices that doesn't show any effect because RAW devices are anyway opened with O_DIRECT.

> Most customers actually take their backups from the live production system without effects that let "the business" complain.

We do that too but we are just in the same process to switch that to a standby database on a different storage subsystem. If you have an almost 3 TB database server to fully backup each night and business running in different time zones it's difficult

We would love to use page backups but applying roughly 100 - 120 GB pages changes a day takes HOURS, basically more than a full restore takes (we tested that).

> Don't get me wrong - I'm a huge fan of having a standby db.

> But it's not like you cannot use the normal backup for your primary instances.

You're totally right. For "most" customers your mentioned scenario applies perfectly.

Markus

[rayko_aben]

Thanks guys for answer,

scripting is no option because of missing IT know how ( for 7x24 ) at the installation location. We need a simple "one button" high availability solution and it seems, Libelle provides most of our requirements.

I have to test, if it is possible to backup the standby db in restore mode ( may be by canceling restore ) and reactivate the log applying in the most easiest way.

Of course, the main focus is to take care about the standby availability of the production system because it controls the warehouse ( LVS ) and the production is running 24 hours.

Probably there are further restrictions within the Libelle implementation.

If you are interested in I will provide the results of our test.

Kind regards and a nice weekend

Rayko

[Former Member]

Hi Rayko

We have a standby MaxDB for one of our Production databases.

I have had to do offline backups of the standby database. While it is possible to backup the database in ADMIN mode, the change counter was reset to 0 after the backup completed. This meant that I couldn't continue the recovery of the standby database and I had to re-synchronise the standby database again.

For the offline backups, we stop the recovery process and offline the database. I then run the following command to list the data and log files for the database. The script then extracts the filenames and then backs them up using a third party tool (Networker or CommVault). After the backup is complete, the database goes back to ADMIN mode and we do a recover_start to start the recovery process.

DR> dbmcli -d SID -u control,******** param_getvolsall

OK

LOG_MIRRORED NO

MAXLOGVOLUMES 2

MAXDATAVOLUMES 21

LOG_VOLUME_NAME_001 256000 F G:\sapdb\SID\saplog\DISKL001

DATA_VOLUME_NAME_0001 1587200 F J:\sapdb\SID\sapdata\DISKD0001

.......

Regards

Doug

[lbreddemann]

Hi Doug,

sorry, but what version are you using?

And which "change counter" do you refer to?

What error message did you get when you tried to continue the recovery of the log backups after taking the ADMIN mode backup?

regards,

Lars

[rayko_aben]

Hi Doug,

... interesting kind of backup. I never did it in this way with Maxdb. SQL Server and Oracle - no problem, but did not know, that Maxdb is able to restore with flatfiles.

To set Maxdb offline during the "recovery process" is no problem?

After resetting to Admin mode, I am able to restore further logs?

Kind regards

Rayko

[lbreddemann]

> ... interesting kind of backup. I never did it in this way with Maxdb. SQL Server and Oracle - no problem, but did not know, that Maxdb is able to restore with flatfiles.

It does not work as it does with Oracle/MS SQL.

There are way more dependencies between all the files.

So you cannot e.g. recover just one of them.

Besides this: it's also not supported.

So if this method fails for some reason, you can't count to get a fix for that.

> To set Maxdb offline during the "recovery process" is no problem?

No, that's really not a problem. In fact if you interrupt the recovery of the log backups by recover_cancel the instance is set to offline automatically.

> After resetting to Admin mode, I am able to restore further logs?

Sure - no problem with that.

UPDATE:

Sorry, but I have to take back what I've written before.

In this special situation (MaxDB in recovery mode, was never 'open' before, so this is a HISTLOST situation for this instance) the full backup in ADMIN mode generate a new DBIdent to start a new backup history line.

Unfortunately this makes the old primary site log backups unusable for the standby db that now has it's own line of backup history.

That means that the log backups of the primary db cannot be recovered any further.

Right now, you either need to follow the unsupported approach and copy all data volume files (the log volume files are really unnecessary) including the parameter files when the database is offline, or you just take the full/incremental backup from the primary database instance.

regards,

Lars

[rayko_aben]

Hi Lars,

thanks for very fast reply.

The explamation mark was only because of the special case of maxdb, not for the common case of backing up a stand by databases.

The database is not in recovery mode - but in ADMIN mode and you can take backups in ADMIN mode.

Are you sure? I did a lot of restores with with MAXDB -believe me - but never tried to make a backup same time or at the end of restore ( before finish the hole restore )

As i understand, if the restore of a dump has finished, i have 2 possibilities

1.) restore further logs or incr. backups ( that would be the situation on standby db )

2.) finish the restore and restart the database to operational mode.

I would be very lucky, if its working in this way.

What do you think about Libelle? Are there alternative products?

Kind regards

Rayko

[markus_doehr2]

> Are you sure? I did a lot of restores with with MAXDB -believe me - but never tried to make a backup same time or at the end of restore ( before finish the hole restore )

Why not? Nothing changes

> What do you think about Libelle? Are there alternative products?

You can adapt the scripts out of

Markus

[lbreddemann]

> Are you sure? I did a lot of restores with with MAXDB -believe me - but never tried to make a backup same time or at the end of restore ( before finish the hole restore )

Yes I am sure about this.

Because I just tried it

> As i understand, if the restore of a dump has finished, i have 2 possibilities

1) it's called a "backup" not a "dump" - we're not mySQL or PostgrSQL

> 1.) restore further logs or incr. backups ( that would be the situation on standby db )

Correct.

But your database is in ADMIN mode now.

So you can take a full data backup.

What should be the problem with it?

> 2.) finish the restore and restart the database to operational mode.

Hmm.... ok, maybe I now got what you refer to.

Well, when you are just within a recovery session (! not state), then you've to end this recovery session (via recover_cancel) to be able to take a backup.

Afterwards the database is in OFFLINE mode, but the state of the log recovery has been saved (e.g. the internal transaction files are saved within the data area).

So you can bring the database back to ADMIN mode and either take a backup or start a new recovery session to apply more redo log backups.

> I would be very lucky, if its working in this way.

Then you now have a reason to be very lucky!

> What do you think about Libelle? Are there alternative products?

I personally never worked with Libelle - so all I can say is: it's there.

And I am not aware of any other products that provide this feature for MaxDB.

BTW: have you checked the MaxDB WIki here in SDN? There are some sample scripts for log backup shipping/applying ....

regards,

Lars

[markus_doehr2]

..or better use this link:

https://wiki.sdn.sap.com/wiki/display/MaxDB/HowTo-StandbyDBlog+shipping

Markus

[lbreddemann]

Ok - I see, Markus really can substitute me in every aspect here.