Thursday, August 14, 2014

Upgrade Oracle Database 12c1 from to

This post list the steps of upgrading from to for single instance database in a data guard configuration (physical standby). The single instance databases are non-CDB. When upgrading databases in a data guard configuration the upgrade process is initiated on the standby site by upgrading the standby database software first. Upgrade of database software to is done as an out of place upgrade (oppose to in-place upgrade). As such the database software could be installed in a different oracle home while the redo apply is taking place. Installing database software is identical to that of and there no new steps to be carried out.
Once the database software is installed copy the spfile , initfile, oracle password file, tnsnames.ora and listener.ora file from the oracle home into the oracle home. In this case the oracle home is /opt/app/oracle/product/12.1.0/dbhome_1 and oracle home is /opt/app/oracle/product/12.1.0/dbhome_2
/opt/app/oracle/product/12.1.0/dbhome_1  ## ORALCE HOME 
$ cp spfileent12c1s.ora initent12c1s.ora orapwent12c1s ../../dbhome_2/dbs/
$ cd ../network/admin/
$ cp tnsnames.ora listener.ora ../../../dbhome_2/network/admin/
Open the listener.ora file that is in the new ORACLE_HOME/network/admin and edit the static listener entries to reflect the new oracle home path. These static listener entries were created as part of the data guard configuration.
        (SID_DESC =
                (GLOBAL_DBNAME = ent12c1)
                (SID_NAME = ent12c1)
                (ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2)
Defer the redo transport on the primary until the standby is mounted using the new oracle home.
SQL> alter system set log_archive_dest_state_2='defer';
and cancel the redo apply on the standby
SQL> alter database recover managed standby database cancel;
Modify the /etc/oratab to reflect the new oracle association with the standby instance
cat /etc/oratab
Stop the listener started out of the home.
Modify the environment variables so that they are pointed to new oracle home (ORACLE_HOME,PATH and etc). Start the listener using the oracle home. Verify that listener is started from the new home
$ lsnrctl start

Starting /opt/app/oracle/product/12.1.0/dbhome_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version - Production
System parameter file is /opt/app/oracle/product/12.1.0/dbhome_2/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/hpc5/listener/alert/log.xml
Services Summary...
Service "ent12c1s" has 1 instance(s).
  Instance "ent12c1s", status UNKNOWN, has 1 handler(s) for this service..
Mount the standby database using the oracle home and start the redo apply
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;
Verify from the alert log that database is started using oracle home parameter file.
ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2
System name:    Linux
Node name:
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine:        x86_64
Using parameter settings in server-side spfile /opt/app/oracle/product/12.1.0/dbhome_2/dbs/spfileent12c1s.ora
Enable the redo transport on the primary
alter system set log_archive_dest_state_2='enable'
This conclude the upgrade activity on the standby. The standby database instance will be upgrade once the redo generated during the primary database upgrade is transported and applied on to the standby.

Although the setup used on this post consists of a data guard configuration the primary site steps are valid for single instances without data guard configuration as well. The software upgrade on the primary is done as an out of place upgrade. Once the software is installed run the preupgrade script from the new home.
cd /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin
SQL> @preupgrd.sql

Loading Pre-Upgrade Package...

Executing Pre-Upgrade Checks in ENT12C1...


                 ====>> ERRORS FOUND for ENT12C1 <<====

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.

 1) Check Tag:    PURGE_RECYCLEBIN
    Check Summary: Check that recycle bin is empty prior to upgrade
    Fixup Summary:
     "The recycle bin will be purged."

            You MUST resolve the above error prior to upgrade



              ====>> PRE-UPGRADE RESULTS for ENT12C1 <<====


1. Review results of the pre-upgrade checks:

2. Execute in the SOURCE environment BEFORE upgrade:

3. Execute in the NEW environment AFTER upgrade:


Pre-Upgrade Checks in ENT12C1 Completed.

As instructed on the output run the fixup sql on the primary database
Run pre upgrade sql again and check of errors and warnings. If there are any errors of warnings fix them before continuing with DBUA. To being the upgrade run DBUA from the home.
Select the database upgrade option.

Select the source oracle home and the instance to upgrade.

The DBUA detects the data guard configuration is in place and prompt to sync the standby database. This is not an error but an information provided by the DBUA. There should not be any archive gaps prior to the primary upgrade.
The network configuration page did not detect the listener running out of the home. Because of this when the upgrade finished the redo transport failed as there was no tnsnames.ora file in the home and standby was unable to fetch the archive logs as the listener was running out of the old home. If during the upgrade no listener is detected similar to below, manually copy the listener.ora and tnsnames.ora files to the home and edit the oracle home entry for static listener registration.
Take a backup before the upgrade or allow DBUA to take a backup as part of the upgrade process.
Summary and upgrade
Upgrade results

Once the upgrade is completed set the environment variables to reflect the new oracle home.
Also run the post upgrade script as suggested by the pre upgrade sql
Verify listener is running out of the new home. If as mentioned earlier listener.ora and tnsnames.ora files are not moved to the new oracle home, move them manually to the home. Once moved stop the listener and start it to run out of the new oracle home.
To validate the data guard configuration is working carry out few log file switches and verify they are received at the standby and applied.
Once tested and satisfied with the upgrade, update the compatible initialization parameter to It is not possible to downgrade the database once this has been set. In this case the standby was the first to be set compatible = followed by the primary. Read data guard admin guide for exact steps.
This conclude the upgrade from to This is not an extensive "how to" guide but only the highlights. For complete information refer the oracle upgrade guide and the following metalink notes.

Useful metalink notes
Oracle 12cR1 Upgrade Companion [ID 1462240.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]
Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) [ID 1520299.1]
Actions For DST Updates When Upgrading To Or Applying The Patchset [ID 1665676.1]

Related Posts
Upgrading RAC from to - Grid Infrastructure
Upgrading RAC from to - Grid Infrastructure
Upgrade from to (Clusterware, ASM & RAC)
Upgrading from to (Clusterware, RAC, ASM)
Upgrade from to (Clusterware, RAC, ASM)
Upgrade from to (Clusterware, ASM & RAC)
Upgrading from to with Transient Logical Standby
Upgrading from to with in-place upgrade for RAC
In-place upgrade from to
Upgrading from to with Physical Standby - 1
Upgrading from to with Physical Standby - 2
Upgrading from 11gR2 ( to 12c ( Grid Infrastructure