Answered Ai Sequence Restart Following Aioff

[BigSlick]

Hello,

I was wondering whether someone could possibly help.

We are wanting to add an index to our database so it would be nice to have after-imaging switched off whilst this goes ahead. When we use aioff then probkup enableai, the AI sequence starts again at 1.

Is there a way to stop AI restarting at 1?
or
Is there a way to put the DR database in sync without doing a restore?

Hopefully i'm missing something.

Thanks in advance.

 

[Cringer]

Why do you want ai off on index add? Doesn't strike me as particularly safe. Not that I'm an expert in this. Just seems odd.
Add the index online and disabled and then idxbuild to activate it.

 

[BigSlick]

Mainly due to the size of the index that could be applied and the associated delays in processing this would cause.

Is there no way to re-align the production and dr databases?

 

[Cringer]

So you're running replication?

As far as I know your best bet is to add the index disabled as I said earlier, then run idxbuild. Idxbuild is faster (usually) than adding the index active.

 

[TomBascom]

Idxbuild is offline only and requires ai to be turned off. You would use idxactivate or idxfix to activate the index online.

If you disable ai you will need to re-seed the replication target. You will have "done stuff" to the source -- that stuff has to get to the target. It can get there either via replication (which is effectively disabled when you turn off ai) or by restoring and rolling forward.

You might be able to reduce the burden somewhat by fiddling with incremental backups. But IMHO that isn't generally worth the PITA aspect.

You have to decide if that is faster than adding and activating the index online. The answer depends on the size of your database, the quality of the network between source and target and the nature of your maintenance window.

The only way to be sure is to test the alternatives. Of course that requires a test setup that mirrors production.

 

[BigSlick]

Thank you for the responses.

I was in the middle of testing all available avenues.

The last time this index was added, the databases fell out of sync (as they would do) but the solution was to backup and restore the database and re-apply ai and roll forward recovery. The database in question is around 1TB and takes 2-3 hours to back up.

We were trying to avoid this, but as Cringer suggested, we could add the index online.

Either way i have an answer.

Thanks

 

[cj_brandt]

Maybe I haven't added an index to a large enough table, but for our TB+ database it is a lot faster to add the index inactive and then activate while AI remains active than to go through the process of taking the extra backup and shipping it to DR.
I see you can take a backup of a TB database in 2 or 3 hours ?? WOW - and I thought we had good disk...