A very weird question on HADR synch mode?

Harishkumar .Pathangay

A very weird question on HADR synch mode?

Hi,
This is a question for learning. I have a HADR setup with SUPERASYNC mode setup with a little twist in log file locations. In Primary I setup MIRRORLOGPATH as a shared location. This location is configured in Stand by as OVERFLOWLOGPATH. Also both Primary and Standby share same archive log location, a shared folder again. In this twisted setup, even if take over is done by force on stand by it will be lossless since both the active and transaction log files are shared folders or devices. Will such a setup be benefit from saving lot of data transfer over http network and also provide me the ability of lossless transactions considering all the log files are there. Obviously I am excluding the scenario of log file or log disk failures or any such log file deleted accidentally kind of scenarios. but they are any case an issue, irrespective of synch mode.
Do any of you have seen such a kind of HADR setup?
thanks, harish pathangay

Philip Gunning

A very weird question on HADR synch mode?
(in response to Harishkumar .Pathangay)
In SUPERASYNC mode you could lose transactions as the standby is somewhat behind the primary. SUPERASYNC IS usually used for reporting off the standby. PG


> IBM Champion for Analytics
>
> Certified Information Systems Security Professional(CISSP)
>
> Certification Number 539059
>
> Certified Advanced DB2 DBA v10.5
>
> Certified Database Adminstrator, DB2 11.1
>
> IBM DB2 LUW Support Page -- https://www.ibm.com/analytics/us/en/technology/db2/db2-linux-unix-windows.html
>
> Skype: DB2LUW
>
> Twitter: DB2LUW
>
> Direct +1.610.451.5801
>
> IDUG DB2-L Hall of Fame
>
> www.philipkgunning.com
>
> IBM Business Partner
>
>
>
>
Sent from my iPhone

> On Feb 14, 2018, at 3:51 AM, Harishkumar .Pathangay <[login to unmask email]> wrote:
>
> Hi,
> This is a question for learning. I have a HADR setup with SUPERASYNC mode setup with a little twist in log file locations. In Primary I setup MIRRORLOGPATH as a shared location. This location is configured in Stand by as OVERFLOWLOGPATH. Also both Primary and Standby share same archive log location, a shared folder again. In this twisted setup, even if take over is done by force on stand by it will be lossless since both the active and transaction log files are shared folders or devices. Will such a setup be benefit from saving lot of data transfer over http network and also provide me the ability of lossless transactions considering all the log files are there. Obviously I am excluding the scenario of log file or log disk failures or any such log file deleted accidentally kind of scenarios. but they are any case an issue, irrespective of synch mode.
> Do any of you have seen such a kind of HADR setup?
> thanks, harish pathangay
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
> ---> Bengaluru, India, March 27, 2018 <---
> http://ibm.biz/IDUGBengaluru2018
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Mark Barinstein

RE: A very weird question on HADR synch mode?
(in response to Harishkumar .Pathangay)

Hi,

It would look good if you shared storage was backed by some clustered file system like GPFS.
But even in this case it's hard to say how db2 logger on primary behaves, if it encounters unexpectedly some log file locks placed by standby trying to read this file at the same time.


If you implement it as some NFS mounted directory on primary for MIRRORLOGPATH, it's a performance killer for your primary database.

Harishkumar .Pathangay

A very weird question on HADR synch mode?
(in response to Mark Barinstein)
Thanks so much Mark for your valuable inputs.
It is a question out of curiosity and for learning purpose only. I do not have any setup that way.

Sent from Mail for Windows 10

From: Mark Barinstein
Sent: 14 February 2018 20:09
To: [login to unmask email]
Subject: [DB2-L] - RE: A very weird question on HADR synch mode?

Hi,

It would look good if you shared storage was backed by some clustered file system like GPFS.
But even in this case it's hard to say how db2 logger on primary behaves, if it encounters unexpectedly some log file locks placed by standby trying to read this file at the same time.

If you implement it as some NFS mounted directory on primary for MIRRORLOGPATH, it's a performance killer for your primary database.


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
---> Bengaluru, India, March 27, 2018 <---
http://ibm.biz/IDUGBengaluru2018

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • 8DDFBAC906444C3C9EE28AAC1C9EEF2B.png (<1k)

Harishkumar .Pathangay

RE: A very weird question on HADR synch mode?
(in response to Harishkumar .Pathangay)

hi mark,

I did some more testing that implicates during take over archive log files are not looked upon for transactions, where as if i initialize stand by from a backup image and issue "start as stand by" command, that will search for additional log files in shared archive log location. that is just my initial conclusion. the question is can "take over command" be changed to refer the archive log files and replay as many transactions as possible from archive location? [assuming it is not that way currently]. what would be the impact of such a change? Please share your inputs.

If take over command is designed not to refer archive log files, the only reason I could think of is a take over should not jeopardize the consistent state of database after take over completes. But what I am not able to understand is what could be in transactional log files that may put a db in inconsistent state. transactions are going to be only table/index/system catalog updates/inserts and deletes. Any case you are rolling back all uncommitted transactions.


Please do not ask me why i am asking for such a change. I do not have any business case for that. I do not belong to any organization and i am only just asking here for learning purposes only. It will not directly benefit to any client or customer value addition. thanks, harish p

Mark Barinstein

RE: A very weird question on HADR synch mode?
(in response to Harishkumar .Pathangay)

Hi,
It would be good, if you provided exact steps / commands you used and at which HADR state exactly.

The set of log paths accessed after 'takeover hadr' command depends on HADR state.

DB2 high availability disaster recovery (HADR) database states

If the standby is able to connect to the primary (entering 'Remote catchup pending'), it will try to retrieve logs from the archive location itself.

If not, then 'takeover hadr' without 'by force' option fails. If your primary is dead at this point, but you do have archived/active logs from it, you can place them to the 'overflow log path' directory of the standby (LOGSTREAM0000 subdirectory of the active log path if it's not configured) or to the active log path itself (it's better to use overflow log path though). These files are processed during 'local catchup' state when you activate your standby without primary. You must reactivate your standby after such a files copy, if the standby was active before it. When you see that the standby came to the 'DISCONNECTED' state, you can run 'takeover hadr' command with 'by force' option. Former primary must be reinitialized after this, unless you are very lucky.

Harishkumar .Pathangay

RE: A very weird question on HADR synch mode?
(in response to Mark Barinstein)

Hi Mark,

Exactly. I did the test case. Here is a video.

https://youtu.be/Qt-x6ng4yHU

thanks, harish p

Harishkumar .Pathangay

RE: A very weird question on HADR synch mode?
(in response to Harishkumar .Pathangay)

i have even made sure that db2inst1 and db2fenc1 user id and group id are same between P and S machines.

still observe the same issue.

Harishkumar .Pathangay

RE: A very weird question on HADR synch mode?
(in response to Harishkumar .Pathangay)

any helpful inputs.

thanks harish p

Mark Barinstein

RE: A very weird question on HADR synch mode?
(in response to Harishkumar .Pathangay)

Hi,

The problem may be in the same log file names -  standby may catch log files in the active log path instead of processing copied from the primary in the overflow log path.
So, the solution is:
- deactivate standby
- copy with replace all the logs (with numbers higher than the standby's current log number at least) found in the primary's active/archive log paths to the standby's active log path; actually, if some copied files don't exist in the standby's active log path, you can place them to the overflow log path if you wish or have some file system size limitations

Then you have 2 options:
- activate the standby & wait for the REMOTE_CATCHUP_PENDING HADR state & takeover hadr by force

or
- stop hadr & rollforward the database to end of logs

Harishkumar .Pathangay

RE: A very weird question on HADR synch mode?
(in response to Mark Barinstein)

thanks mark for your valuable inputs. it is only for learning purpose. will do the same and revert back to you.

Harishkumar .Pathangay

RE: A very weird question on HADR synch mode?
(in response to Harishkumar .Pathangay)

hi mark,

it works when you copy it into active log path. I do not even need to do take over. just deactivate stand by, copy the log files from archive location into active log path. activate the database [in stand by mode only]. it catches up all missing transactions as it is replaying from active log file path.

but I would have been more happier if it works without putting things into active log location. I am not sure whether my happiness counts or means any thing to IBM Db2. Just for Fun !!!!

 

Thanks for your immense help and patient replies.

thanks, Harish P