1 person found this helpful
Is it possible this is being made more complex than needed? Is reporting so critical that is requires zero downtime?
If yes I would might consider getting rid of the logical standby and just creating a physical standby to replace it. You could use snapshot database with this to do your reporting and have a few "brownouts" off peak to sync the physical standby then return to snapshot database.
I would not do 1 -3 of the options you listed. They are too complex and I would keep this as simple as possible. I might consider 4 if the snapshot won't work for some reason.
My DR for Standby is RMAN. Either to recover, roll forward or duplicate for standby.
1 person found this helpful
I have designed many DR solutions for many clients so here are my opinions that I feel deliver solid DR solution.
1) Create a Physical Standby of the Logical Standby? If so, how do we keep the standby production database and standby reporting database in sync in a DR situation?
Your logical standby does not have all tables so no do not create physical standby from the logical standby this would not be the DR protection you are looking for. Create a physical standby from the primary database as a second standby database so you will have 2 standbys 1 physical and 1 logical. Only time I would not follow this is in cases where network issues would prevent this. Manage the logical standby and physical standby separately so one does not depend on the other. There are inherent dangers with having one standby depend on another.
2) Run the standby production database with Active Data Guard and create a Logical Standby from it? Would that even work?
Yes that should be technically possible, but typically when network is not an issue have the standby databases come off the primary directly if network is an issue from primary to both standbys then you can consider using the physical standby as a source for the logical standby but location and network at standby becomes your potential next issue, though I have never had a case where I needed to do this I have always been able to create the multiple standbys directly from the primary without issue.
3) Run two Logical Standby databases from the production database? How would we keep non-guarded schema changes in sync?
I never suggest using a logical standby for true DR protection, it is subject to differences from the primary and therefore could be an issue if you ever have to use it without realizing a difference existed and now you are stuck with an RMAN recovery and any potential SLA has been violated esp. on a large database restore and that is if you RMAN backups are available at the standby location.
4) Do we resort to storage level replication for one/both of the databases?
Typically storage level replication consumes more network bandwidth then a standby database does, but this has been used in the past and depending on storage vender be careful I have had folks do this only to find out that the database on the other end was not usable because they did not do this properly.
Does anyone have any real world experience of a situation like this?
Yes, if you are questioning your DR solution and protection I would suggest contacting a consultant that has done many of these that could help your architect and plan this and potentially assist with the implementation. Better to be safe and spend a little on help up front then to find out you are between a rock and a hard place with a solution that has gaps and issues.
Are there any other options?
Yes there are other options depending on your needs, Golden Gate could be an option that could help with the reporting and DR and do both in the same database, but typically a physical standby is used for true DR as it is a reliable exact copy of the source and managed as such.