Implement a Logical Standby Database

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.

Mathijs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s