Oracle 11g to 12c Rolling Upgrade
Upgrade Oracle 11g to 12c with minimal downtime using rolling upgrades.
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 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.4Create 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 IDELNEW CONFIGURATION:
==================
PRIMARY
=======
SID: prod
Role: Primary Database
Version: 11.2.0.4
STANDBY
=======
SID: prod_st
Role: Logical Standby Database
Version: 11.2.0.4Stop 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 >> upgradedSwitch 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 >> upgradedConvert 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 mountCURRENT 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 >> upgradedStart 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.sqlYou can choose to perform a switchover at this stage to revert back to original configuration.
