All entries for Monday 06 February 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.

ORA-16282
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:

SYS@standby SQL>ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; 
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY
*
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.

Hongfeng Sun : 06 Feb 2012 12:36 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem