• Arun Kumar

Oracle 11g to 12c Rolling Upgrade

There are multiple ways to perform rolling upgrade. But you must identify which one will work for your environment. Below are the two common methods used


Method 1: Transient logical standby database

  • Convert existing physical standby to logical standby using KEEP IDENTITY clause

  • Upgrade Logical standby first

  • Return the Logical standby back to physical standby

Method 2: DBMS_ROLLING_UPGRADE package

  • Only available from 12c

  • Its automation of transient logical standby process

Assumptions


We assume that you already have a physical standby setup for a production database and you want to perform a rolling upgrade so that users are not impacted.

CURRENT CONFIGURATION:
======================

PRIMARY
=======
SID: prod
Role: Primary Database
Version: 11.2.0.4

STANDBY
=======
SID: prod_st
Role: Physical Standby Database
Version: 11.2.0.4

Convert physical to logical stb


Create a Guaranteed Restore Point on both primary and physical standby before upgrade activity

On primary (prod):
==================
SQL> create restore point pre_upgrade_pri guarantee flashback database;

On standby (prod_st):
=====================
SQL> alter database recover managed standby database cancel;
SQL> create restore point pre_upgrade_stb guarantee flashback database;
SQL> alter database recover managed standby database disconnect;

Build Log Miner directory on primary

begin
dbms_logstdby.build;
end;
/

Convert physical standby into logical standby

On standby (prod_st):
=====================
SQL> alter database recover managed standby database cancel;
SQL> shut immediate;

SQL> startup mount;
SQL> alter database recover to logical standby keep identity;
SQL> alter database open;
SQL> alter database start logical standby apply immediate;

SQL> select state from v$logstdby_state;        >> Must see IDEL state

CURRENT CONFIGURATION:
======================

PRIMARY
=======
SID: prod
Role: Primary Database
Version: 11.2.0.4

STANDBY
=======
SID: prod_st
Role: Logical Standby Database
Version: 11.2.0.4

Stop SQL apply process on logical standby and create another restore point before starting upgrade

On primary (prod):
==================
SQL> alter system set log_archive_dest_state_2=DEFER scope=memory;

On Logical Standby (prod_st):
=============================
SQL> alter database stop logical standby apply;
SQL> create restore point before_upgrade_lstb guarantee flashback database;
SQL> shutdown immediate;

Perform logical standby upgrade using DBUA or manual method. Note, users are still connected to prod, the original primary database. Once upgrade is completed, we will start the LSP from upgrade logical standby database

On primary (prod):
==================
SQL> alter system set log_archive_dest_state_2=enable scope=memory;

On Upgraded Logical Standby (prod_st):
======================================
SQL> alter database start logical standby apply immediate;

CURRENT CONFIGURATION:
======================

PRIMARY
=======
SID: prod
Role: Primary Database
Version: 11.2.0.4

STANDBY
=======
SID: prod_st
Role: Logical Standby Database
Version: 12.1.0.1                  >> upgraded

Switch to logical standby


Perform switchover to upgraded logical standby

On primary (prod):
==================
SQL> select switchover_status from v$database;	>> must see TO STANDBY 
SQL> alter database commit to switchover to logical standby;

On Upgraded Logical Standby (prod_st):
======================================
SQL> select switchover_status from v$database;	>> must see TO PRIMARY
SQL> alter database commit to switchover to logical primary;

CURRENT CONFIGURATION:
======================

PRIMARY
=======
SID: prod
Role: Logical Standby Database
Version: 11.2.0.4

STANDBY
=======
SID: prod_st
Role: Primary Database
Version: 12.1.0.1                  >> upgraded

Convert logical to physical stb


As the users are now connected to new primary (earlier upgraded logical standby), we will flashback the original primary to the restore point pre_upgrade_pri

On new primary – Earlier upgraded logical standby (prod_st):
============================================================
SQL> alter system set log_archive_dest_state_2=defer scope=memory;


On new logical standby database – Earlier primary database (prod):
==================================================================
SQL> select database_role from v$database;	>> must see LOGICAL STANDBY
SQL> shutdown immediate;
SQL> startup mount
SQL> flashback database to restore point pre_upgrade_pri;
SQL> shutdown immediate;

Start new logical standby database (prod) via new 12c ORACLE HOME and convert it into physical standby.

• Copy parameter file, password file to new ORACLE HOME

• Copy tnsnames.ora to new ORACLE HOME

On new logical standby database – Earlier primary database (prod):
==================================================================

SQL> startup mount
SQL> alter database convert to physical standby;

SQL> shutdown immediate;
SQL> startup mount

CURRENT CONFIGURATION:
======================

PRIMARY
=======
SID: prod
Role: Physical Standby Database
Version: 12.1.0.1                 >> upgraded

STANDBY
=======
SID: prod_st
Role: Primary Database
Version: 12.1.0.1                  >> upgraded

Start MRP on new physical standby (prod)

On new primary – Earlier upgraded logical standby (prod_st):
============================================================
SQL> alter system set log_archive_dest_state_2='ENABLE' scope=memory;

On new physical standby database – Earlier primary database (prod):
===================================================================
SQL> alter database recover managed standby database disconnect;

Monitor the alert log and see redo apply should upgrade the physical standby. Once upgrade is done, issue below command on physical standby to check the component versions

SQL> @?/rdbms/admin/utlu112s.sql

You can choose to perform a switchover at this stage to revert back to original configuration.

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Oracle Data Guard Protection Modes

A Data Guard configuration always runs in one of three data protection modes (also called as redo transport rules): Maximum Protection Maximum Availability Maximum Performance (default mode of operati

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback