Oracle 11g to 12c Rolling Upgrade

A rolling upgrade allows you to perform database upgrade without having any noticeable downtime to the end users. There are multiple ways to perform rolling upgrade. But you must identify which one will work for your environment.

Types of Rolling Upgrade
Convert Physical Standby to Logical Standby
Switch to Logical Standby
Convert Logical Standby to Physical Standby

Types of Rolling Upgrades

Below are the two common methods used for performing Oracle rolling upgrades:

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

Convert Physical Standby to Logical Standby

Assumption: 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

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

NEW 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;
SQL> alter database commit to switchover to logical standby;

On Upgraded Logical Standby (prod_st):
======================================
SQL> select switchover_status from v$database;
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 Standby to Physical Standby

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.

Heading 2