Friday, April 24, 2015

Upgrading RAC from 11.2.0.4 to 12.1.0.2 - Database

After upgrading grid infrastructure from 11.2.0.4 to 12.1.0.2 the next step is the upgrade of RAC software and the database itself. There are earlier post which upgraded 12.1.0.1 to 12.1.0.2 and also upgraded in data guard configuration and with PDBs. These posts have useful metalink notes that could be benefitial in RAC environment as well. The database software upgrade is an out of place upgrade with role separation. Use orachk or cluvfy to check the pre upgrade status.
cluvfy stage  -pre dbinst -upgrade -src_dbhome /opt/app/oracle/product/11.2.0/dbhome_1 -dest_dbhome /opt/app/oracle/product/12.1.0/dbhome_2 -dest_version 12.1.0.2.0
Create additional user groups (backupdba,kmdba,dgdba) and install the 12.1.0.2 using the software only installation option out of place to current 11.2.0.4 oracle home. One difference that was noted is that with 12.1.0.2 there's no need to manually correct the oracle binary's ownership as before. After the database installation the oracle binaries will have ownership as oracel:oinstall even though the correct ownership should be (for role separated installation) oracle:asmadmin. However this get auto rectified when the DBUA is run.
Before running the DBUA check oracle user has the write permission on the audit folder under $ORACLE_BASE if not grant write permission for oinstall ggroup.
chmod 770 $ORACLE_BASE/audit
The default listener name has been changed on this cluster setup. This missing default listener causes a problem similar to earlier upgrade (from 11.2.0.3 to 12.1.0.1).
To fix this add default listener and start it before DBUA is run.
$ srvctl add listener -listener listener -p 1521
$ srvctl start listener -l listener
To avoid the bug 19518079 apply the patch to 12c oracle home before DBUA is run. However this is not a must and could use other workarounds as well. For more information refere MOS 19518079.8 and 1941383.1
Run preupgrade script to identify any pre-reqs tasks that must be done on the database before the upgrade.
cd /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin
SQL> @preupgrd.sql


Run the DBUA to start the database upgrade.
Timezone is also be upgraded at the same time the database.
Summary
Upgrade tasks completion
Upgrade results
Check time timezone information is same on both timezone file and database registry as there was a mismatch between these values in 12.1.0.1 after upgrading from 11.2.0.3
SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat              18          0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        18
If ASM configuration is referring the default listener modify it before removing the default listener entry added during the upgrade.
[grid@rhel6m2 ~]$ srvctl config asm
ASM home: <CRS home>
Password file:
ASM listener: LISTENER

$ srvctl modify asm -l MYLISTENER
$ srvctl config asm
ASM home: <CRS home>
Password file:
ASM listener: MYLISTENER

$ srvctl stop listener -l listener
$ srvctl remove listener -l listener
If database patches are being applied make sure sqlpatch directory has write permission for oracle user.
chmod 775 /opt/app/oracle/cfgtoollogs/sqlpatch
If the database upgrade is satisfactory upgrade the compatible parameter to new version.
alter system set compatible='12.1.0.2.0' scope=spfile sid='*';
ASM diskgroup compatible parameters could also be upgrade at this time. There are dependencies between the DB compatibility value and ASM's rdbms compatibility value as such consider in which order to update these values.
alter diskgroup data set attribute 'compatible.asm'='12.1.0.2.0';
alter diskgroup flash set attribute 'compatible.asm'='12.1.0.2.0';
alter diskgroup flash set attribute 'compatible.rdbms'='12.1.0.2.0';
alter diskgroup data set attribute 'compatible.rdbms'='12.1.0.2.0';
alter diskgroup cluster_dg set attribute 'compatible.rdbms'='12.1.0.2.0';
alter diskgroup cluster_dg set attribute 'compatible.asm'='12.1.0.2.0';
alter diskgroup flash set attribute 'compatible.advm'='12.1.0.2.0';
Verify that upgrade entry is added to patch history.
ACTION_TIME                    ACTION           VERSION            COMMENTS
------------------------------ ---------------- ------------------ --------------------------
11-JUN-12 05.07.59.601706 PM   APPLY            11.2.0.3           Patchset 11.2.0.2.0
12-JUN-12 02.24.59.156482 PM   APPLY            11.2.0.3           PSU 11.2.0.3.2
19-JUL-12 05.31.51.092475 PM   APPLY            11.2.0.3           PSU 11.2.0.3.3
15-NOV-12 02.59.09.956573 PM   APPLY            11.2.0.3           PSU 11.2.0.3.4
22-JAN-13 01.02.11.445936 PM   APPLY            11.2.0.3           PSU 11.2.0.3.5
24-APR-13 11.59.40.161626 AM   APPLY            11.2.0.3           PSU 11.2.0.3.6
31-JUL-13 02.01.12.758084 PM   APPLY            11.2.0.3           PSU 11.2.0.3.7
09-SEP-13 04.24.23.479935 PM   VIEW INVALIDATE                     view invalidation
09-SEP-13 04.24.23.586103 PM   UPGRADE          11.2.0.4.0         Upgraded from 11.2.0.3.0
09-SEP-13 04.25.02.867598 PM   APPLY            11.2.0.4           Patchset 11.2.0.2.0
08-MAY-14 01.51.36.029968 PM   APPLY            11.2.0.4           PSU 11.2.0.4.2
25-JUL-14 04.16.58.140512 PM   APPLY            11.2.0.4           PSU 11.2.0.4.3
17-NOV-14 04.01.03.481360 PM   APPLY            11.2.0.4           PSU 11.2.0.4.4
22-JAN-15 05.19.08.323577 PM   jvmpsu.sql       11.2.0.4.2OJVMBP   RAN jvmpsu.sql
22-JAN-15 05.19.15.000000 PM   APPLY                               Patch 19877440 applied
22-JAN-15 05.19.15.796773 PM   APPLY            11.2.0.4.2OJVMBP   OJVM PSU post-install
22-JAN-15 05.21.55.225164 PM   APPLY            11.2.0.4           PSU 11.2.0.4.5
22-APR-15 01.05.40.917488 PM   VIEW INVALIDATE                     view invalidation
22-APR-15 01.07.04.188992 PM   UPGRADE          12.1.0.2.0         Upgraded from 11.2.0.4.0
22-APR-15 03.51.53.094015 PM   jvmpsu.sql       12.1.0.2.2OJVMBP   RAN jvmpsu.sql
This conclude the upgrade of RAC DB from 11.2.0.4 to 12.1.0.2.

Useful metalink notes
Oracle Database Upgrade Path Reference List [ID 730365.1]
Oracle 12cR1 Upgrade Companion [ID 1462240.1]
Memory Guard Offlines Database Service Due to Low Free Memory [ID 1929994.1]
DBA_REGISTRY_HISTORY Does Not Get Updated on Database Upgraded to 12.1.0.2 [ID 1941383.1]
Bug 19518079 - DBA_REGISTRY_HISTORY is not updated during upgrade to 12.1.0.2 [ID 19518079.8]
ORA-600 [kwqitnmphe:ltbagi] or ORA-600 [kwqdlprochstentry:ltbagi] on the SYS$SERVICE_METRICS_TAB, Orphan Subscribers [ID 1986078.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 12.1.0.2 Patchset [ID 1665676.1]
Things to Consider Before Upgrading to 12.1.0.2 to Avoid Poor Performance or Wrong Results [ID 2034610.1]

Related Posts
Upgrading 11.2.0.3 (11gR2) Database to 12.1.0.1 (12c) Using DBUA
Upgrading from 11.2.0.3 to 12.1.0.1 RAC
Upgrade Oracle Database 12c1 from 12.1.0.1 to 12.1.0.2
Upgrading 12c CDB and PDB from 12.1.0.1 to 12.1.0.2