Pythian Group

If you have an extremely busy OLTP system with a physical standby ready for a manual role transition, and you want to run very heavy reporting queries without affecting the system, consider using a separate report database with downstream capture configuration. Yes, it is very easy to configure, and it will have no performance impact on the OLTP system whatsoever.

In this blog, I will show how to do this, and how to maintain the archivelog transportation during a manual switchover.

Below is the configuration of the three databases I have for testing:
Read the rest of this entry . . .

It’s probably worth some explanation to understand where I want to drive you: when you run an update in Oracle, the changes are made at the point that is consistent during the whole execution of the update. This allows you to run a command like the one below, even if ID is the primary key of T:

update t
  set id=(case id
             when 1 then 2
             when 2 then 1
             end)
  where id in (1,2);

If that was not the case, you would have to DEFER the constraint validation to the commit time every time you ran a command like the one above, or you would get an error like:

ORA-00001: unique constraint T_PK violated

To read more about how updates work in Oracle, look at Tom Kyte’s Write Consistency Part I, Part II, Seeing a Restart, and Part III, Why is a Restart Important to Us. But that’s a tangent to the subject of this post. What I’ll show here is that you can run a DELETE and an INSERT as parts of the same statement.

Read the rest of this entry . . .