RE: Logical Standby
- From: "Taft, David" <TaftD@xxxxxxxxxxx>
- To: "'Smith.Steven@xxxxxxx'" <Smith.Steven@xxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 8 May 2007 12:55:09 -0400
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@xxxxxxx]
Sent: Monday, May 07, 2007 7:29 PM
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
