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 184.108.40.206.
Please note that this is full release , so if you are installing new software, you can directly install 220.127.116.11 without need of first installing 18.104.22.168 database.Unzip the software and start the runInstaller from the database directory.
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 22.214.171.124 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 126.96.36.199 software location and start dbua. Take backup of database before you start upgrade process using 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 188.8.131.52 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 (184.108.40.206 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
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/220.127.116.11/dbhome_1