For a customer I had the privilege to investigate and implement a Logical standby Database. Primary side was a 6 Node RAC and the Standby side was scheduled to become a two Node RAC. The standby side was not meant as a Fail over solution but had to serve the purpose of an almost on-line Reporting environment to the customers ( in such a way also separating them from the production database). The requirements became quite a challenge in more than one aspect giving the facts that the project wanted to go live with it in the very next 2 weeks, that this was unknown territory for the complete team hmm and given the fact that the decision to change from a physical standby database to a logical one was one of the very last minute. I forgot who said it, but with respect I would like to quote the unknown creator of this following thought: .. but if things would always be easy you and I would be out of a job..
Both the implementation and even more important keeping the environment alive became a huge challenge (and if I put it that way I also mean huge effort) since we really had a guy called Murphy in the team. Hardware, network, and even shipment of the archives of a 6 Node primary Rac Database to a two node Standby Rac database became topics that has cost us a lot of time and resources.
Should you have to implement a logical standby database here are a number of heads up you really need to cover before even starting.
- Communication: The Source Database(s) (aka Primary side) NEEDS to be able to talk to the Target Side (Standby side) and visa versa. That means be aware of firewall and issues with them. If you start losing archives in shipment or resending , you are already out of the game.
- Storage and behavior: Make sure both the primary – and standby side have PLENTY of space in the disk groups in ASM (or on your file systems). If your standby database is falling behind it will NOT allow regular RMAN backups to delete the archives on the primary database until it is catching up again. And once you have a logical database, making changes to or adding tablespaces to the primary side means you will have to implement that manually on the standby side as well !
- Transition to Logical Standby Database: All logical standby databases are born as a Physical standby database. The transition to a logical standby is performed in a number of steps for that. One BIG issue we had is that on the primary side you have to run a package execute dbms_logstdby.build; on a quiet moment in the database .BUT this package will wait for all ACTIVE transactions to finish… And in our case with the application being set up as it was.. we had very long running transactions and plenty of them too so that was never the case. That means in plain text you might Have to claim a Maintenance window , restart the database in restricted mode to run this package… And that each time again when you rebuild your physical – logical standby
- Test, test , test, test and Test the concept first not only on a test environment, but preferably also on a preproduction ( close to production – like) situations.
To my opinion the concept of a logical standby is interesting and implement and operating it can be done , but only if you have good control of quite a number of areas. If you do not have that control, I would reconsider the options first and see if a physical standby (active dataguard) can do the Job. Or look for alternatives like Oracle Goldengate first.
As always, Happy reading and till we meet again.