The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

How do I change the mirror mode from asynchronous to synchronous in a old-school 12.0.1.3797 HA setup (no CREATE MIRROR usage) without an immediate outage on the primary?

Is it possible to stop the secondary (mirror) partner, change the -xf mirror mode, and restart it? At that point the restarted partner (secondary, mirror) will be using -xp ... mode=sync whereas the primary partner will still be using -xp ... mode=async.

Will everything still work?

If "yes" then presumably the mirror mode specification could be changed in the primary partner's startup configuration, and it would take effect on the next failover, scheduled or otherwise :)

asked 19 Feb '14, 11:52

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

edited 19 Feb '14, 15:16

Ian%20McHardy's gravatar image

Ian McHardy
2.6k22945


First of all, mirroring was never designed for one partner to be async and one to be sync. So the safest thing to do would be to stop the mirror, then stop the primary, then restart the primary with sync, then restart the mirror with sync.

I did try taking two 12.0.1 partners, both with -xp mode=async;autofailover=yes and stopping the mirror, and restarting it with -xp mode=sync;autofailover=yes. At this point all seemed well (the servers are probably giving a mixture of sync and async behaviour). But when I stopped the primary the mirror would not take over - I think because the arbiter thought the state was synchronizing (normal for async), but the primary required the state to be synchronized. So I don't think you can force them to failover. ALTER DATABASE SET PARTNER FAILOVER also failed to failover. So I'm pretty sure you can not get this to work, and I wouldn't recommend trying on a production system.

permanent link

answered 19 Feb '14, 15:15

Ian%20McHardy's gravatar image

Ian McHardy
2.6k22945
accept rate: 39%

edited 19 Feb '14, 20:00

Graeme%20Perrow's gravatar image

Graeme Perrow
8.3k369106

You could set the synchronize_mirror_on_commit option for connections to the database. This will make all transactions synchronous, even though the servers all still running in async. mode. This will likely not be good for performance, but it might be good enough as a temporary solution.

permanent link

answered 20 Feb '14, 10:19

Jason%20Hinsperger's gravatar image

Jason Hinspe...
2.7k63447
accept rate: 35%

edited 20 Feb '14, 10:19

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×101
×37

question asked: 19 Feb '14, 11:52

question was seen: 486 times

last updated: 20 Feb '14, 10:19