Standby wallets

As you may have seen from earlier posts we are setting up to stuff in azure. As this is public cloud we want to ensure we encrypt the data at rest (i.e. in the datafiles on disk) so not anyone can read them or dump out data from them.

Azure seem to have recently introduced a native way of doing this without the need for any extra oracle licences/configuration but for now we are not using that and have stuck with TDE (transparent data encryption) which is part of the advanced security option.

I get the impression that oracle will be selling a whole lot more of these licences to people in the coming years with the huge shift in the direction of cloud.

I'm not going to go through all the details of the wallet/TDE setup as you can find that elsewhere - what i am going to cover is how the use of a wallet specifically works with dataguard as we've had an interesting couple of days with this.

Our first task was just to get a switchover working with the wallet in place - this resulted in a number of issues - many of which were caused by the fact that the wallet was attempted to be created on both the standby and the primary which strangely enough does seem to be allowed although it just ends in tears. You have to make sure the wallet is copied from the primary to the standby rather than trying to create on the standby also  - this makes sense as the data is encrypted based on keys in the wallet - different wallet different keys. 12c does seem to support the copying of keys between wallets but i ignored that and just copied the entire wallet as is.

The next step was to try fast start failovers this did not start well - it just wouldn't work at all to start with complaining about missing TDE keys where previously for switchover it worked fine. In the end i found the issue - the listener had not been restarted after making the changes to the wallet - so remote connections were somehow initializing the wrong wallet and data could not be decrypted - so make sure if you make changes to wallet locations that the listener is restarted. So somehow a failover is different in terms of how things connect to a switchover......

Next was something that sounded pretty horrendous at first look but i think was somehow triggered by earlier failover issues:

Terminal Recovery timestamp is '11/03/2016 14:46:05'
Cannot recover datafiles with current logs containing different
Terminal Recovery Stamps (TRSTAMP).  The datafiles must be
restored from backups to clear the TRSTAMP and then recovered
up to the current log.
Log file TRSTAMP:  Count: 926952365l
Datafile #1:  Count: 926933518l
Terminal recovery failed with error 16131

This was fixed by setting this undercore (thanks metalink - known issues fixed in 12.2 apparently)

_skip_trstamp_check

So at this point i had fast start failover working (which incidentally is very neat if you never saw it - you can have a select statement running on the primary, kill the pmon and then you just wait a few seconds the session reconnects and carries on from the standby - seamlessly - who needs RAC!)

Now my biggest question when i first set up this setup was what happens with the wallet on failover  - we have wallets that have to be opened with a password - it kind of defeats the object if you just have an auto login wallet - how does the wallet get opened so the data is readable for the application?

I don't want to have an auto login wallet as mentioned, i don't want a startup trigger as that's no better - does the observer/dg config somehow allow this to happen? Well no is the short answer to that - but actually it's not an issue.

For fast start failover you can actually have the wallet pre-opened on the standby - in fact you need this to decrypt the redo anyway and apply to the datafiles.

You basically just have to have the command

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "secretpassword";

run on both sides - then the wallet is pre-opened in the event of failover and its all seamless. The only extra step you need to be aware of is in the event of reinstating the old primary - the following steps should be done:

startup nomount
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "secretpassword";
alter database mount;

i.e. open the wallet before the mount - fast start failover/observer will then kick in do an auto flashback and reinstate the database into a new standby - all very clever.

So in summary i'm happy to report that fast start failover works with 12cR1 with TDE pretty much seamlessly.