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
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.
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)
Perform a switchover so that the original primary database now becomes a physical standby database
Use Redo Apply to synchronize (and upgrade) the original primary database with the new upgraded primary database
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.
Upgrade the standby database using DBUA or manual upgrade.
Start the upgraded standby database in the new Oracle 12c home
Start the original primary database in the new Oracle 12c home
The physru script accepts six parameters as shown below.
$./physru <sysdba user> <primary TNS alias> <physical standby TNS alias> <primary db unique name> <physical standby db unique name> <target version>
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.
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.
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
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
Data Guard primary and physical standby database environment exists
Flashback database is enabled on both Primary and Standby database
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)
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.
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.
Oracle 12.1.0.1.0 software has already been installed on both the primary as well as standby database servers
Let us now see an example.
In this case the primary database is TESTDB and the physical standby database is TESTDBS,
The DB_UNIQUE_NAME of the primary and standby is also TESTDB and TESTDBS
The original version is 11.2.0,3 and we are upgrading to 12.1.0.1.
We have enabled the Flashback database on both Primary as well as Standby database
Added static entries in the listener.ora on both sites and then reloaded the listener.
For example on the Primary site:
(SID_DESC=
(SID_NAME=testdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_2)
(GLOBAL_DBNAME=testdb)
)
And on the Standby site:
(SID_DESC=
(SID_NAME=testdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_2)
(GLOBAL_DBNAME=testdbs)
)
The tnsnames.ora on both Primary as well as Standby sites have entries for TESTDB and TESTDBS.
Important – before starting the operation, do a tnsping from both sites and ensure that the TNS aliases are being resolved
Stop managed recovery and shutdown the Standby database.
Mount the standby database
Now run physru script – Execution One
Note – we can run the script from either the Primary or Standby site – but in this example we are running it from the Primary site for all the three executions of the script
If we connect to the standby database we can now see that the role has been changed from PHYSICAL STANDBY to LOGICAL STANDBY
Now start the 12c database upgrade on the standby database. Have a look a this post which will discuss the upgrade to 12.1.01.0 using DBUA .
Note that users are still connected to the primary database and it is business as usual
Make some changes to the Primary database while the standby database upgrade is in progress.
SQL> update customers set cust_city=’Dubai’ where rownum < 10001;
10000 rows updated.
SQL> commit;
Commit complete.
SQL> create table mycustomers as select * from customers;
Table created.
After the 12c upgrade is completed, we need to update the static entry we made in the listener.ora providing the location of the 12c database Oracle Home and then reload the listener.
For example this is the change we made in the listener.ora
SID_LIST_LISTENER12C =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = testdbs)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = testdb)
)
)
After the upgrade we now connect to the transient Logical Standby database which is now running in 12c and run the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE command. Ensure the database is running in READ WRITE mode.
Now run the physru script again - Execution Two
The transient logical standby database has now been converted to a data guard Primary database
We now prepare the Original Primary database for the upgrade to 12c. Application is now running from the Standby site.
Change static listener.ora entry to point to 12c Oracle Home ( or we can create a new 12c listener in addition to the 11g one) and then reload the listener
(SID_DESC=
(SID_NAME=testdb)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(GLOBAL_DBNAME=testdb)
)
Copy spfile, init.ora , password file for TESTDB from 11g Oracle Home to 12c Oracle Home.
Copy the tnsnames.ora file from 11g $ORACLE_HOME/network/admin to 12c $ORACLE_HOME/network/admin
Change /etc/oratab entry for TESTDB to point to new Oracle 12c home
Mount the TESTDB database (now standby database) from the new Oracle 12c
Connect to both databases TESTDB and TESTDBS and ensure that the values for the parameters ‘log_archive_dest_state_1′ and ‘log_archive_dest_state_2′ are both set to ENABLE
Now the third and final execution of the physru script!
Application still connected to TESTDBS and database changes are being performed
SQL> update mycustomers set cust_city=’Timbuktu’;
55500 rows updated.
SQL> commit;
Commit complete.
Now connect to the original primary database and check if the database role is what it originally was
Check last change made has been applied
Lastly, shutdown standby and start managed recovery
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2293496 bytes
Variable Size 314573064 bytes
Database Buffers 478150656 bytes
Redo Buffers 6684672 bytes
Database mounted.
Database opened.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.