Install

This post is in continuation to Grid Infrastructure upgrade post written earlier. Ensure that you have downloaded the software and  have upgraded the infrastructure database.

Following files contain database software for 11.2.0.2.

p10098816_112020_Linux-x86-64_1of7.zip

p10098816_112020_Linux-x86-64_2of7.zip

Please note that this is full release , so if you are installing new software, you can directly install 11.2.0.2 without need of first installing 11.2.0.1 database.Unzip the software and start the runInstaller from the database directory.

cd $SW_HOME/database

./runInstaller

We will be presented with OUI screen asking for MOS credentials. Please note that we would be installing software into new ORACLE_HOME, which is a new feature called out of place upgrade.

Next screen ask’s again for MOS credentials. This can be used to check if there are any patches which need’s to be applied before software can be upgraded. Since we have already applied the PSU2, we will choose skip Software updates

We are now presented with 3 options

a)Create and Configure database – installs software and creates a database using dbca

b)Install database software only – Only installs 11.2.0.2 software

c) Upgrade exisiting database – Installs software and launches dbua to upgrade database

We chose option b) i.e Install database software only

Next screen presents you with 3 options

a)Single Instance database installation

b)Oracle RAC Installation

c)Oracle RAC One Node database Installation

I will be discussing RAC one node in a future post. For our installation we are upgrading RAC database and require RAC software

Next screen gives option to choose between Enterprise edition and Standard Edition

Next screen asks for database software installation directory. Unlike Grid infrastructure, where it is mandatory to install in new ORACLE_HOME, RAC database software can be installed in existing home. But we will be choosing Out of place upgrade i.e install in new home.

Choose the OSDBA and OSOPER group

Next screen checks the pre-requisites. You can ask oracle to create fixup script. Refer to my earlier post for detail

Finally we are prompted to run root.sh from both nodes

After you have run root.sh from both nodes, you can then use dbua to upgrade the database. Note that we have not yet brought down the database. This is great benefit of using out of place upgrade . Second benefit is that you are not touching the existing binaries,so you are not required to take backup of binaries and can can easily rollback the changes (if required). You can also continue using the old binaries for databases which you cannot get downtime and can upgrade them later.

Set the ORACLE_HOME and PATH to include 11.2.0.2 software location and start dbua. Take backup of database before you start upgrade process using dbua

$dbua

We are presented with welcome screen. Press next

DBUA presents you with list of databases currently registered in /etc/oratab. We select db11g which we need to upgrade

Next screen asks for following options

a) Option to recompile invalid objects and degree of parallelism for running utlrp.sql. We keep default value of 3

b) Option to disable archiving during upgrade

c)Upgrade the timezone file. We have not selected it now and will do upgrade manually

Next screen presents with option to select Fast Recovery area and size

Next we will be presented with summary screen and asked to take backup of database. If you have not taken it till now, its good time to take backup. Also now database will be stopped and will be started from new oracle home.Please ensure that you have appropriate setting for JAVA_POOL_SIZE and SHARED_POOL_SIZE during upgrade or can use SGA_TARGET/MEMORY_TARGET to avoid ora-4031

Following screen shows database upgrade progress screen.

Once upgrade completes, it gives summary of upgrade process. As you can see it has warned for DST upgrade.

Oracle 11.2.0.2 contains version 14 file. Starting 11g you can have multiple database running out of single oracle home to have different timezone version files.

You can refer to Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]

Giving summary of steps taken by us to upgrade from version 11 to version 14

---Check the current version of file ---
SQL> select version from V$TIMEZONE_FILE;

   VERSION
----------
	11

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4  

PROPERTY_NAME		       VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION	       11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE	       NONE

---Confirm if $ORACLE_HOME/oracore/zoneinfo contains timezlrg_14.dat i.e version 14 file

--Prepare for upgrade
SQL> exec DBMS_DST.BEGIN_PREPARE(14);

PL/SQL procedure successfully completed.

--Confirm status

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4  

PROPERTY_NAME		       VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION	       11
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE	       PREPARE

---Re-running prepare statement will give below error.
SQL> exec DBMS_DST.BEGIN_PREPARE(14);
BEGIN DBMS_DST.BEGIN_PREPARE(14); END;

*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in
an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1340
ORA-06512: at line 1

-- truncate logging tables if they exist.

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

-- log affected data

BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/
---Query table to check if any affected table
SQL> SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL>
SQL> SELECT * FROM sys.dst$error_table;

no rows selected

-- End the prepare phase
SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

--Startup the database in upgrade mode. Ensure you have set cluster_database=false

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

-- To confirm whether it has been upgraded to version 14
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4  

PROPERTY_NAME		       VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION	       14
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE	       UPGRADE

--Restart the database after removing cluster_database

This completes the database upgrade process. I would recommend dbua to upgrade the database as  it takes care of copying the init.ora files,password file and also modified the OCR to point to new oracle home and upgraded srvctl version. Oracle Upgrade guide does not list any method documenting steps to be taken for srvctl. I referred following link i.e Oracle Database Upgrade documentation for the upgrade purpose

http://download.oracle.com/docs/cd/E11882_01/server.112/e17222/upgrade.htm#i1011981

In case you use manual method and use srvctl modify database to point to new oracle home, you will get errors like below

You can use following command to upgrade the srvctl version

srvctl upgrade database -d test11g -o /oracle/product/app/11.2.0.2/dbhome_1

Happy Upgrading.