Minimal downtime rolling database upgrade to 12c Release 1This note describes the procedure used to perform a rolling database upgrade from 126.96.36.199 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 188.8.131.52 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 184.108.40.206
The transient logical standby process uses SQL Apply to take redo generated by a database running a lower Oracle version (220.127.116.11) , and apply the redo to a standby database running on a higher Oracle version (18.104.22.168)
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.
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
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 22.214.171.124.0 software has already been installed on both the primary as well as standby database servers.