RE: Logical Standby

Steve,  

We recently set up an Oracle 9.2.0.7 Logical Standby in our shop. Just yesterday, I was asking the DBA responsible for this system what issues he has encountered. The main problem is that any errors on the primary appear to cause the apply SQL process on the logical to stop, i.e. a foreign-key constraint error during an insert on the primary, fails on the logical and then all further log mining stops. At that point he must determine why it stopped and if it is OK to just skip the SQL with the error and continue applying SQL from that point forward. He has had to put customized monitoring in place to alert him whenever these errors happen. Depending on the number of such errors per day, it can time consuming. The impression I get is that the ability to automatically skip these kinds of errors is not very robust in 9.2. He indicated to me that he has read that 10gR2 is more robust in this area. Sorry, but this is all second hand knowledge on my part.  

In my own environments that I am responsible for, we only run physical standbys. One of those environments is open for read-only during business hours and used for reporting purposes. This takes a load off of the primary by not having to handle ad-hoc reporting. A simple cron job does the switch-over from managed recovery mode to read-only and back. It is very low maintenance. The only problem I have is that access to the primary is supposed to be limited to using the web application interface and ad-hoc users need direct SQL*Plus access. This means that in order to give a user access to the standby for ad-hoc reporting, they must first be added to the primary. That night their UserIDs get applied to the standby. At that point they are on their honor not to run ad-hocs against the primary. Also, if using password aging, the user must log into the primary to alter their passwords.  

Cheers,  

David Taft

-----Original Message-----

From: Smith, Steven K - MSHA [mailto:Smith.Steven_at_DOL.GOV] Sent: Monday, May 07, 2007 7:29 PM
To: oracle-l_at_freelists.org
Subject: Logical Standby

I'm researching data guard with physical and logical standby databases.

We currently are running standby database in 9.2 and are investigating options that data guard will give us after upgrading to 10.2.

Question I have is - how reliable and what issues have people with experience using logical standby? I see that there advantages would be availability to report and view data with possibly additional reporting indexes on the 'standby' server.. I understand that having that available in physical mode is an option, but updates stop while the database is open in 'read only' mode.

What is the performance hit? Assuming maximum performance mode. I don't trust the network to the standby site to recommend Max Protection or Max availability.

Ongoing maintenance? I know with our current standby database, the maintenance on the standby site is minimal. The setup is just pretty reliable as long as the network is available and not saturated.

Logical - large(r) bandwidth requirements?

I am currently reading the manuals so anything that I'll 'get to' please don't tell me to RTM. I'm looking for more actual experience and lessons learned.

Thanks

Steve Smith

Desk: 303-231-5499      

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 08 2007 - 11:55:09 CDT