Tuesday, June 24, 2014

Couple of weeks ago there was a Twitter discussion started by Martin Bach (@MartinDBA) about cases for logical standby implementation. A rolling upgrade was mentioned by Tim Gorman (@timothyjgormanas) as one of potential recommendations for using this rare use product. I have been involved in such project in the past and I prepared an instruction and did quite large number of rolling upgrades from 11.1 into 11.2.

There are couple of my “gotchas”

Support for data types – make sure that all data type in your application are supported by logical standby

Support for Oracle features like reference partitioning or compression

Logging all apply related errors during a logical standby phase

Keep DML operations running on big dataset to minimum – keep in mind that update tab1 set col1=2 will be translated into separated update for every row in table and you really want to avoid it.

Compatible parameter – if you are using flashback to rollback changes you can change compatible parameter with restore points

If you checked that all your types and features are supported this is a list of advantage you can get from rolling upgrade:

Keep your application downtime low – in reality we have an average downtime around 3 min (including additional restart of instance to remove a restore points and change compatible parameter)

If you have a problems with upgrading you can rollback it quite easy and revert logical standby into physical

Your upgrade script can work longer as your primary database is still running

After upgrade you can have a read only access to your production database for tests if needed