DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, December 31, 2013

Minimal downtime rolling database upgrade to 12c Release 1

Minimal downtime rolling database upgrade to 12c Release 1

This note describes the procedure used to perform a rolling database upgrade from 11.2.0.3 to Oracle 12c Release 1 using a Data Guard physical standby database and transient logical standby database.

The average time to perform a database upgrade would be in the region of one to two hours and for many organizations even that amount of downtime would in some cases not be possible or would lead to a significant financial implication because of a database outage.

The rolling upgrade procedure greatly reduces the downtime for an upgrade from hours to a few minutes which is the duration in which a database switchover can be performed.

At a high level these are the steps involved in the  rolling upgrade process

1) Start with the 11.2.0.3 Data Guard physical standby database and convert that to a transient logical standby database. Users are still connected to  primary database.

2) Upgrade the transient logical standby database to 12.1.0.1
The transient logical standby process uses SQL Apply to take redo generated by a database running a lower Oracle version (11.2.0.3) , and apply the redo to a standby database running on a higher Oracle version (12.1.0.1)

3) Perform a switchover so that the original primary database now becomes a physical standby database

4) Use Redo Apply to synchronize (and upgrade) the original primary database with the new upgraded primary database

5) Perform another switchover to revert the databases to their former roles.


Oracle provides a Bourne shell script (physru) which really does automate a lot of the rolling upgrade process and is available for download from MOS via the note – Database Rolling Upgrade Shell Script (Doc ID 949322.1).

The DBA only has a few tasks to perform as the physru script handles the rolling upgrade process.

i. Upgrade the standby database using DBUA or manual upgrade.
ii. Start the upgraded standby database in the new Oracle 12c home
iii. Start the original primary database in the new Oracle 12c home

The physru script accepts six parameters as shown below.

$./physru

We need to provide the SYSDBA password  and can run this from either the primary database server or from the node hosting the standby database as long as SQL*Net connectivity is available from that node to both the databases involved in the rolling upgrade.

We need to execute the script 3 times and let us see what happens at each stage.

1. First execution

Create control file backups for both the primary and the target physical standby database

Creates Guaranteed Restore Points (GRP) on both the primary database and the physical standby database that can be used to flashback to beginning of the process or any other  intermediate steps along the way.

Converts a physical standby into a transient logical standby database.

2. Second execution

Use SQL apply to synchronize the transient logical standby database and make it current with the primary

Performs a switchover to the upgraded 12c transient logical standby and  the standby database becomes the primary

Performs a flashback on the original primary database to the initial Guaranteed Restore Point  and converts the original primary into a physical standby

3. Third execution

Starts Redo Apply on the new physical standby database (the original primary database) to apply all redo that has been generated during the rolling upgrade process, including any SQL statements that have been executed on the transient logical standby as part of the upgrade.

When synchronized, the script offers the option of performing a final switchover to return the databases to their original roles of primary and standby, but now on the new 12c database software version.

Removes all Guaranteed Restore Points

Prerequisites

A) Data Guard primary and physical standby database environment exists

B) Flashback database is enabled on both Primary and Standby database

C) If Data Guard Broker is managing the configuration, then it has to be disabled for the duration of the upgrade process (by setting the initialization parameter DG_BROKER_START=FALSE)

D) Ensure that the log transport (initialization parameter LOG_ARCHIVE_DEST_n) is correctly configured to perform a switchover from the primary database to the target physical standby database and back.

E) Static entries defined in the listener.ora file on both Primary as well as Standby database nodes for the databases directly involved the rolling upgrade process.

F) Oracle 12.1.0.1.0 software has already been installed on both the primary as well as standby database servers.