One of the problems I have seen when deploying Data Guard for systems such as RAC One Node and policy managed databases was the static listener configuration you needed in 11.2. This has changed with 12c for the better if you are using Grid Infrastructure.
http://docs.oracle.com/database/121/DGBKR/install.htm
In the section about static listener registration a little addendum can be found (thanks to Patrick Hurley/@phurley for pointing this out to me!):
“A static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used.”
This is good news, let’s put it to the test; I’m a great fan of Oracle Restart. If I ever find the time I’d like to repeat this test with clustered Grid Infrastructure. I think the quote mentioned earlier still stands true but I would like to see it with my own eyes.
The Setup
To start with I used a dbca-created database named “NCDB” on my server named “server1″. It uses the DATA and RECO disk groups and is a non-CDB (although I’m quite sure that doesn’t matter). The patch level is current at the time of writing, I’m on 12.1.0.2.4 (which is the OJVM and PSU bundle patch for July 2015):
The future standby database will be called STDBY. This is for demonstration purposes only, I’m not a fan of using db_unique_name implying roles. Imagine you switch over to STDBY and run it as primary database for extended periods of time-that would be potentially confusing.
With that in mind, I created/updated a common tnsnames.ora on server1 and server2:
The duplicate command was a simple “duplicate target database for standby” after creating a simple pfile and auxiliary structures (audit_dump_dest, large pages etc) were in place. For this to work you need a copy of the controlfile for the standby in place as well as a backup of the primary database).
That’s a working standby database. I will have to register it with Grid Infrastructure next.
Now all I need to do is set standby_file_management to auto, add standby redo logs to both databases and enable the broker. I’ll not show these here.
Broker Configuration
The next step in the deployment of my standby database is the creation of a Data Guard Broker configuration. I always create it using dgmgrl.
That’s looking good so far! I can see archived redo logs being exchanged, and I can see MRP0 applying data in real time on the standby.
Recap
This is a reference for the settings in the Data Guard Broker. I have not modified any listener.ora file on any host, all I did was to ensure that the tnsnames.ora file has identical contents on both sides. I have listed the database configuration for later reference here:
Now the big question is: will this allow me to switch over? For the record I am still using my SSH connection (in screen!) on server1, but am now connected to the standby database in dgmgrl.
Well that seems to have worked!
It seems straight forward though: the SSH connection was made to the future standby host, which could have helped with Oracle Restart restarting the database. What if I try to switch back, but this time connect to server1 (currently in the standby role) and issue the switchover command? Here is the output:
Ready to switch over:
OK, that worked too-very nice. It’s safe to say that in my lab conditions restarting of databases works pretty well.
