Thursday, September 21, 2017

Upgrading RAC from 11.2.0.4 to 12.2.0.1 - Database

After upgrading grid infrastructure from 11.2.0.4 to 12.2.0.1 the next step is the upgrade of RAC software and the database itself. There are earlier post which upgraded 11.2.0.4 to 12.1.0.1. The database is a non-CDB database. There are earlier posts upgraded in data guard configuration and with PDBs. These posts have useful metalink notes that could be beneficial 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_4 -dest_dbhome /opt/app/oracle/product/12.2.0/dbhome_1 -dest_version 12.2.0.1.0
Create additional user groups that were introduced as part of 12c
# groupadd backupdba
# groupadd dgdba
# groupadd kmdba
# groupadd racdba
# usermod -g oinstall -G dba,oper,asmoper,asmdba,asmadmin,backupdba,dgdba,kmdba,racdba oracle
Install the 12.2.0.1 binaries by selecting the software only installation option. This installation is done out of place to current 11.2.0.4 oracle home.
Once the 12.2 database software is installed the next step is to upgrade the database. This is done using DBUA. Before running DBUA check if oracle user has the write permission on the audit folder under $ORACLE_BASE if not grant write permission for oinstall group.
chmod 770 $ORACLE_BASE/audit
Also a default listener is needed for DBUA upgrade to complete (this setup has a non-default listener name and port). DBUA will complain if the default listener is missing.




12.2 has a new preupgrade check tool, a jar file which is available in 12.2 $ORACLE_HOME/rdbms/admin. This could be run as a standalone jar. Running this check the upgrade readiness of the database
cp /opt/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar ~

[oracle@rhel6m1 ~]$ $ORACLE_HOME/jdk/bin/java -jar preupgrade.jar TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  STD11G2
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
         Compatible:  11.2.0.4.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  14
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  R1

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID


==============
BEFORE UPGRADE
==============

  Run /preupgrade_fixups.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             600 MB  DISABLED      500 MB  None
     SYSTEM                             700 MB  ENABLED       914 MB  None
     TEMP                               100 MB  ENABLED       150 MB  None
     UNDOTBS1                           375 MB  ENABLED       400 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.

  RECOMMENDED ACTIONS
  ===================
   + Backup the existing ACLs and their assignments for reference. Use the
     new DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views to administer
     network privileges after upgrade.

     The database contains network ACLs with privileges that will be migrated
     to a new format in 12c.

     Network access control list (ACL) privileges in 11g will be migrated to
     a new format in 12c. As part of the migration, new
     DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views are provided, and
     privileges in the existing ACLs will be converted to the new format with
     new ACL names. The old ACL names, DBMS_NETWORK_ACL_ADMIN interfaces and
     dictionary views may continue to be used but are deprecated and their
     use is discouraged. For further information, refer to My Oracle Support
     note number 2078710.1.

   + Review and remove any unnecessary EVENTS.

     The database contains events.

     There are events set that should be removed before upgrade, unless your
     application vendors and/or Oracle Support state differently.  Changes
     will need to be made in the spfile.

=============
AFTER UPGRADE
=============

  Run /postupgrade_fixups.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 14 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.

     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.

   + Gather statistics on fixed objects two weeks after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

     This recommendation is given for all preupgrade runs.

     Fixed object statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans.  Those
     statistics are specific to the Oracle Database release that generates
     them, and can be stale upon database upgrade.

Preupgrade generated files:
    /opt/app/oracle/cfgtoollogs/std11g2/preupgrade/preupgrade_fixups.sql
    /opt/app/oracle/cfgtoollogs/std11g2/preupgrade/postupgrade_fixups.sql

 
SQL> @/opt/app/oracle/cfgtoollogs/std11g2/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2017-05-04 13:24:32

For Source Database:     STD11G2
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
network_acl_priv          Failed  Manual fixup recommended.
underscore_events         Failed  Manual fixup recommended.
dictionary_stats          Passed  None

PL/SQL procedure successfully completed.
Run the pre-upgrade fix script followed by the DBUA.
SQL> @/opt/app/oracle/cfgtoollogs/std11g2/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2017-05-04 13:24:32

For Source Database:     STD11G2
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
network_acl_priv          Failed  Manual fixup recommended.
underscore_events         Failed  Manual fixup recommended.
dictionary_stats          Passed  None

PL/SQL procedure successfully completed.
Database registry component versions and status before the upgrade are shown below.
COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- ----------
CATJAVA    Oracle Database Java Packages            11.2.0.3.0      REMOVED
XML        Oracle XDK                               11.2.0.3.0      REMOVED
JAVAVM     JServer JAVA Virtual Machine             11.2.0.3.0      REMOVED
RAC        Oracle Real Application Clusters         11.2.0.4.0      VALID
CATALOG    Oracle Database Catalog Views            11.2.0.4.0      VALID
OWM        Oracle Workspace Manager                 11.2.0.4.0      VALID
XDB        Oracle XML Database                      11.2.0.4.0      VALID
CATPROC    Oracle Database Packages and Types       11.2.0.4.0      VALID
Run the DBUA to begin the database upgrade.
Run the post upgrade fix script
SQL> @/opt/app/oracle/cfgtoollogs/std11g2/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2017-05-04 13:27:35

For Source Database:     STD11G2
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
old_time_zones_exist      Passed  None
post_dictionary           Passed  None
fixed_objects             Passed  None

PL/SQL procedure successfully completed.
Session altered.
The registry component status
COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- ----------
XML        Oracle XDK                               11.2.0.3.0      REMOVED
CATJAVA    Oracle Database Java Packages            11.2.0.3.0      REMOVED
JAVAVM     JServer JAVA Virtual Machine             11.2.0.3.0      REMOVED
XDB        Oracle XML Database                      12.2.0.1.0      VALID
OWM        Oracle Workspace Manager                 12.2.0.1.0      VALID
RAC        Oracle Real Application Clusters         12.2.0.1.0      VALID
CATPROC    Oracle Database Packages and Types       12.2.0.1.0      VALID
CATALOG    Oracle Database Catalog Views            12.2.0.1.0      VALID
Registry history view shows upgrade to 12.2
ACTION_TIME                    ACTION               NAMESPAC VERSION            ID COMMENTS
------------------------------ -------------------- -------- ---------- ---------- -------------------------
10-JUL-17 03.10.52.412402 PM   APPLY                SERVER   11.2.0.4       161018 PSU 11.2.0.4.161018
14-JUL-17 01.49.21.603390 PM   VIEW INVALIDATE                             8289601 view invalidation
14-JUL-17 02.03.18.931179 PM   UPGRADE              SERVER   12.2.0.1.0            Upgraded from 11.2.0.4.0
Check the timezone file has been upgrade
SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat              26          0
This is a SE database which had statspack installed. Upgrade the statspack to 12.2 (refer 394937.1).
SQL>@?/rdbms/admin/spup112
SQL>@?/rdbms/admin/spup12200.sql
If satisfied with the database upgrade set the compatibility parameter to 12.2. At the same time the compatibility values in disk group could also be set to 12.2.
SQL> alter system set compatible='12.2.0.1.0' scope=spfile sid='*';

srvctl stop database -d std11g2

alter diskgroup flash SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup DATA  SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup flash set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup data set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup CLUSTER_DG SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup CLUSTER_DG3  SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup CLUSTER_DG set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup CLUSTER_DG3 set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup GIMR  SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup GIMR set attribute 'compatible.rdbms'='12.2.0.1.0';

select group_number,name,value from v$asm_attribute where name like 'compat%';

GROUP_NUMBER NAME                                     VALUE
------------ ---------------------------------------- ------------------------------
           1 compatible.asm                           12.2.0.1.0
           1 compatible.rdbms                         12.2.0.1.0
           2 compatible.asm                           12.2.0.1.0
           2 compatible.rdbms                         12.2.0.1.0
           3 compatible.asm                           12.2.0.1.0
           3 compatible.rdbms                         12.2.0.1.0
           4 compatible.asm                           12.2.0.1.0
           4 compatible.rdbms                         12.2.0.1.0

srvctl start database -d std11g2
Upgrade also adds new oracle user accounts that are with default passwords to the database. Change the default passwords of these accounts
GSMCATUSER
REMOTE_SCHEDULER_AGENT
DBSFWUSER
SYSBACKUP
GGSYS
SYSRAC
AUDSYS
GSMADMIN_INTERNAL
SYSKM
SYS$UMF
SYSDG
GSMUSER
Finally run
orachk -u -o post
to check post upgrade status.

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
Upgrading RAC from 11.2.0.4 to 12.1.0.2 - Database
Upgrading RAC from 12.1.0.1 to 12.1.0.2 - Database
Upgrading RAC from 11.2.0.3 to 11.2.0.4 - Database