February 06, 2012

Switchover to logical standby

This morning we did a switchover between our primary database and one of the two logical standby databases.

The switchover went smoonthly. When the data began to be replicated from primary, however, the standby database suddenly did successive log switch at very high frequency. This is a bit puzzling since the primary database was not too busy.

Loads of waiting events from “SYS” user:
LogMiner builder: DDL                    oracle@si-sbr-db (MS01)  
LogMiner reader: buffer                  oracle@si-sbr-db (MS00) 
Streams apply: waiting for dependency    oracle@si-sbr-db (AS05)                     
Streams apply: waiting for dependency    oracle@si-sbr-db (AS04)   

The frequent updating SQL was:

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 
where obj#=:1

We started to replicate a big log table a couple of days ago. That table was previous skipped in log apply. The old logical standby coped the change very well. Did this change cause problem on the new standby?
I increased the cache size of hibernate sequence to 10,000 from default value 20.

alter sequence hitlog.hibernate_sequence cache 10000

The log switch immediately stopped. I was surprised how effective the SQL was.

At developer’s request, I did switchover again. Went smoothly. Then I was asked to switchover again ( for some reason of course), I encountered an error on the current standby database:

ERROR at line 1:
ORA-16282: operation not permitted during rolling upgrade

After I faffed around on google, then I recalled I forgot to startup the log apply on the current standby.
Well, the “PREPARE TO SWITCHOVER TO PRIMARY” succeeded after I started the log apply.

- No comments Not publicly viewable

Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

Search this blog

Search Warwick Blogs



Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder