Friday, September 28, 2012

Logical standby how to use for upgrading (2)


In my previous article i blogged about the use of logical standby  for upgrading purposes.

As said the client wants to have a physical standby per site at all times during the upgrade process and to have a limited downtime. 
So that the production database will nourish the logical standby which we can upgrade to 11.2.0.3 and which in it's turn will feed the redo it generates to other physical standby …..


I will not go through the documentation here but describe what we did.


i would like to refer to an excellent paper  which I followed but adapted to our situation


Database Rolling Upgrade Using Transient Logical Standby
Oracle Database 10g Release 2
Oracle Maximum Availability Architecture White Paper August, 2010

which can be found here  





It was decided not to do the upgrade in a Rolling fashion but to put a parallel environment in place, that way there is always a fallback. we didn't have to buy new hardware to do that but made use of containers in solaris ... 
Since we are not doing a rolling upgrade this means that we didn't use the KEEP identity feature of logical standby but that the logical standby received a new database namen with its own db id


so briefly explained you start of with a physical database
and then you convert it to a logical standby.


the DB_UNIQUE_NAME of the logical standby will be PRODSITEB



First step after you checked the physical standby is following correctly, all datatypes are supported ...  is to stop the application of redo on the physical standby



    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  CANCEL;

on the primary db you have to build a dictionary in the redologs and also enable supplemental loging this is done by executing following



    SQL> begin
           dbms_logstdby.build();
         end;
         /



==> this makes use of flashback query make sure the undo_retention is at least 3600
==> this can have some nasty consequences so best to do it in off peak periods Alter Database Add Supplemental Log Data Hangs [ID 406498.1] I scheduled a maintenance window to do this to avoid all risk.

A logical standby will generate redo from its  own so make sure you create a directory, since we use ASM i created a directory with asmcmd

ASMCMD>cd +ARCHDG1/PRDSITEB
ASMCMD>mkdir standbyARCHIVE


on the future logical standby I also put following :

SQL> alter system set log_archive_Dest_2='LOCATION=+ARCHDG1/PRDSITEB/standbyarchive VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PRODSITEB';


This way the redo logs generated by the primary and source redo logfiles for mining  are put here.


the redo log files that are generated by reconstructing the sql statements are put in a different place as you can see here


 SQL>alter system set log_archive_dest_1='LOCATION=+ARCHDG1 VALID_FOR=(ONLINE_LOGFILE,STANDBY_ROLE)';


this took me a while to get, because it sounds a bit contradictory, ONLINE_LOGFILE in a STANDBY_ROLE but if you think about it, it makes sense ...


once this was set we actually converted the physical standby to logical standby



SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY DB11G

SQL> BEGIN
DBMS_LOGSTDBY.APPLY_SET(„LOG_AUTO_DELETE‟,‟FALSE‟);
END;



Now is time to really open the logical standby

SQL> ALTER DATABASE OPEN RESETLOGS; 

start mining ….

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

check if that works
Now you do whatever you want with the logical standby, e.g. upgrade it but first stop logical apply


SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY ;



After the upgrade finished I duplicated the standby via rman …. from active database … see here for more info about that great feature…


to ship the redo that the logical standby generated following log_Archive_dest needs to be defined;


SQL> ALTER system set log_archive_dest_3='SERVICE=PHYSTDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,STANDBY_ROLE)  DB_UNIQUE_NAME=PHYSTDB'




done …

cave eat and known issues :
  • FAL and correct settings for the log_Archive_dests are two different things, sometimes FAL works but not regular sending for redo, in fact it manifested itself after putting SQL apply on, FAL kicked in but afterwards the primary didn't send the redo ….thanks to @GeertDepaep my buddy for pointing this out
  • our primary database version 10203 was pretty bug prone regarding to logical standby

I encountered following bugs :

  • 3313487 : A failed Create Table As Select (CTAS) causes SQL Apply to shutdown on logical standby with ORA-95
  • 5685296: Apply process stops with PLS-0103
  • also other one unfortunately no reference to that were indexes were being created twice,at least that is what was tried but which failed miserably … solution was to skip the transaction altogether..

         





No comments: