Oracle database Upgrade from 11.2.0.1 to 11.2.0.3 with Dataguard Broker in Place

INTRODUCTION

Purpose of this document is to guide the reader trough the steps of upgrading an Oracle Database to a higher release when a Physical Standby database is in place.  In this example we use an Oracle RDBMS 11.2.0.1 release that is upgraded to 11.2.0.3.

Starting point is a configured Oracle Data Guard environment, managed by Data Guard broker release 11.2.0.1.

STEP 1: DATABASE Software

We are installing the new Oracle software release in its own $ORACLE_HOME for this upgrade, we use the out placement principle. This step can be done in advance before we start the upgrade. Follow the Oracle installation manual to install the software and select the software-only installation.

STEP 2: DISABLE CONFIGURATION BROKER

We start with turning off the Data Guard broker configuration. During the migration, we do not use the Data Guard broker.

DGMGRL> connect /
Connected.

DGMGRL> disable configuration
Disabled.

DGMGRL> exit

In addition, we disable the background process DMON on both primary and standby instance.

On the primary:
SQL> alter system set dg_broker_start=false scope=Both;
altered system.

On standby:
SQL> alter system set dg_broker_start=false scope=Both;
altered system.

STEP 3: COPY FILES TO THE NEW $ORACLE_HOME

When the broker is disabled we can copy the required files from the old to the new $ORACLE_HOME , think of the SPFILE file, orapwd, tnsnames.ora and listener.ora.
Of course perform this operation on both nodes, first the files from $ORACLE_HOME/dbs and $ORACLE_HOME/network/admin/. Beside the copy of the files modify also the path mentioned in the SID_LIST_LISTENER  string in the listener.ora file.

Primary host:
$ cp -p $ORACLE_HOME/dbs/orapwproda /u01/app/oracle/product/11.2.0/dbhome_2/dbs/
$ cp -p $ORACLE_HOME/dbs/dr*.dat /u01/app/oracle/product/11.2.0/dbhome_2/dbs/
$ cp -p $ORACLE_HOME/dbs/spfileproda.ora /u01/app/oracle/product/11.2.0/dbhome_2/dbs/
$ cp -p $ORACLE_HOME/dbs/tnsnames.ora /u01/app/oracle/product/11.2.0/dbhome_2/network/admin//
$ cp -p $ORACLE_HOME/dbs/listener.ora /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/
$ vi listener.ora

Standby host:
$ cp -p $ORACLE_HOME/dbs/orapwproda /u01/app/oracle/product/11.2.0/dbhome_2/dbs/
$ cp -p $ORACLE_HOME/dbs/dr*.dat /u01/app/oracle/product/11.2.0/dbhome_2/dbs/
$ cp -p $ORACLE_HOME/dbs/spfileproda.ora /u01/app/oracle/product/11.2.0/dbhome_2/dbs/
$ cp -p $ORACLE_HOME/dbs/tnsnames.ora /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/
$ cp -p $ORACLE_HOME/dbs/listener.ora /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/
$ vi listener.ora

STEP 4: START Listener FROM THE NEW $ORACLE_HOME

We want to start the listener from the new $ORACLE_HOME. Stop de current listener and restart the listener from the new $ORACLE_HOME. When you perform this action a small interruption is possible for new sessions connecting to the primary database.

Perform this action for both environments.

On the primary host:$ lsnrctl stop
$ / u01/app/oracle/product/11.2.0/dbhome_2/bin/lsnrctl start

On standby host:$ lsnrctl stop
$ / u01/app/oracle/product/11.2.0/dbhome_2/bin/lsnrctl start

When correct, the listener is actively running using version 11.2.0.3.

STEP 5: START PHYSICAL STANDBY FROM THE NEW $ORACLE_HOME

Now the files are copied and the listener is running from the 11.2.0.3 version, stop the physical standby database and start it from the new ORACLE_HOME.

Warning: The physical standby database must not running with  Active Data Guard option, only allowed mode is mounted with Redo Apply active.

Stop the physical standby:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 20 11:06:09 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Modify the oratab to be able to set the new SID to the new $ORACLE_HOME:

$ vi /etc/oratab

proda:/u01/app/oracle/product/11.2.0/dbhome_2:N
agent:/u01/app/oracle/agent11g:N

Use oraenv for the new ORACLE_HOME, and then start the physical standby database in mount mode and activate Redo Apply service. When started the database instance is running using 11.2.0.3 software.

$ . oraenv
ORACLE_SID = [proda] ? proda
The Oracle base remains unchanged with value /u01/app/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 20 11:11:20 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  414298112 bytes
Fixed Size                  1345324 bytes
Variable Size             352323796 bytes
Database Buffers           54525952 bytes
Redo Buffers                6103040 bytes
Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> exit

STEP 6: UPGRADE THE PRIMARY DATABASE

Now the physical standby database is running in the new ORACLE_HOME we can start to upgrade the primary database. The modifications on the primary database are automatically send and applied on the standby database.

To upgrade the primary database you must follow the Oracle Upgrade Guide. Upgrading the database does not differ with respect to a database in a Data Guard environment.

Start the dbua from the new $ORACLE_HOME and follow the wizard to perform the upgrade.
$ / u01/app/oracle/product/11.2.0/dbhome_2/bin/dbua

Remark: Do not turn off  archive logmode during the upgrade.

After the upgrade modify the compatibility parameter if required.

Validate whether the parameters are correct, it may happen that this is not correct, I notice during the upgrade the log_archive_dest_# was invalid after disable the broker configuration.  The usage of the Database upgrade assistant (DBUA) is not part of this blog. 

STEP 7: ENABLE BROKER CONFIGURATION.

When the primary is upgraded, the physical standby is  automatically upgraded as well. We can (re)enable the broker configuration again. We start the broker process DMON on both the primary and physical standby database.

On the primary host:
SQL> alter system set dg_broker_start=true scope=both;

System altered.

On the standby host:
SQL>  alter system set dg_broker_start=true scope=both;

System altered.

Now we can enable the broker configuration again.

DGMGRL> connect /
Connected.

DGMGRL> enable configuration
Enabled.

DGMGRL> exit

Note: If you copy the broker files after you have started DMON, you need to restart both primary and standby database to pickup the broker file. Recreating a new configuration is also possible.

Validate the configuration including the properties with special attention to the static connect properties, execute a switchover to validate this is still working as designed.

STEP 8: Remove the old ORACLE_HOME.

Remove the old software by running the deinstall script.

$ cd /u01/app/oracle/product/11.2.0/dbhome_1/deinstall
$ ./deinstall
Checking for required files and bootstrapping …
Please wait …