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

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 :

I encountered following bugs :