Advertisements
Skip to content
September 6, 2017 / Shivananda Rao P

Manual Upgrade of RAC Database from 11.2.0.3 to 12.1.0.2

In the previous post, we have seen on upgrading the Grid Infrastructure from 11.2.0.3 to 12.1.0.2. In this, we shall see the steps involved in the database upgrade from 11.2.0.3 to 12.1.0.2

 
 

Environment:

 

RAC nodes: drnode1, drnode2
DB Name: srprim
DB Instances: srprim1, srprim2
Current DB version: 11.2.0.3.0
DB to be upgraded to version: 12.1.0.2.0
Cluster Storage used: ASM
Platform: OEL 6
Current DB HOME: /u01/app/oracle/product/11.2.0.3/db_1
New 12c DB HOME: /u01/app/oracle/product/12.1.0.2/db_1

 

Since this is an out-of-place upgrade, firstly install Oracle 12.1.0.2 database software. In this environment, the software here has been unzipped to location “/u03” and then the database software is installed using a response file in silent mode.

 

Here is the response file used to install the 12.1.0.2 database software.

 

[oracle@drnode1 ~]$ cd /u03/database
[oracle@drnode1 database]$ ./runInstaller -silent -responseFile /u02/db_install.rsp -ignoreSysPrereqs
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 32711 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 10229 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-02-21_07-55-11PM. Please wait ...[oracle@drnode1 database]$ You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2017-02-21_07-55-11PM.log
The installation of Oracle Database 12c was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2017-02-21_07-55-11PM.log' for more details.

As a root user, execute the following script(s):
        1. /u01/app/oracle/product/12.1.0.2/db_1/root.sh

Execute /u01/app/oracle/product/12.1.0.2/db_1/root.sh on the following nodes:
[drnode1, drnode2]


Successfully Setup Software.

 

Once the 12.1.0.2 database software has been installed, it’s time for us to upgrade our database.

 

Run the “preupgrd.sql” script available from the newly installed oracle 12c home on the 11.2 database. This script performs pre-requisite checks on the database to be upgraded and generates 2 scripts:

 

1. preupgrade_fixups.sql which needs to be run on the database to fix any issues reported while performing the pre-checks.
2. postupgrade_fixups.sql which needs to be run on the database once upgraded to 12.1.0.2 version.

 

Let’s run the “preupgrd.sql” script from the newly installed oracle 12.1.0.2 home (/u01/app/oracle/product/12.1.0.2/db_1) on the database.

 

[oracle@drnode1 ~]$ ls -lrt /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/preupgrd.sql
-rw-r--r--. 1 oracle oinstall 14083 May 15  2014 /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/preupgrd.sql

 

[oracle@drnode1 ~]$
[oracle@drnode1 ~]$ sqlplus sys/oracle@srprim as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 21 21:22:35 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/preupgrd.sql



Loading Pre-Upgrade Package...


***************************************************************************
Executing Pre-Upgrade Checks in SRPRIM...
***************************************************************************


      ************************************************************

                  ====>> ERRORS FOUND for SRPRIM <<====

 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.

           You MUST resolve the above errors prior to upgrade

      ************************************************************

      ************************************************************

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

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
 /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/srprim/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
 /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/srprim/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
 /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/srprim/preupgrade/postupgrade_fixups.sql

      ************************************************************

***************************************************************************
Pre-Upgrade Checks in SRPRIM Completed.
***************************************************************************

***************************************************************************
***************************************************************************

 

 

We see above that the preupgrd.sql script generated “preupgrade_fixups.sql” ( to fix any issues reported during pre-check phase) and “postupgrade_fixups.sql” (to be run post the upgrade) scripts that needs to be run on the database. Review the “preupgrade.log” and take necessary actions on the recommendations made.

 

================================================================================================

 

Preupgrade warning log:

[oracle@drnode1 ~]$ cat /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/srprim/preupgrade/preupgrade.log
Oracle Database Pre-Upgrade Information Tool 02-21-2017 21:24:39
Script Version: 12.1.0.2.0 Build: 006
**********************************************************************
   Database Name:  SRPRIM
  Container Name:  Not Applicable in Pre-12.1 database
    Container ID:  Not Applicable in Pre-12.1 database
         Version:  11.2.0.3.0
      Compatible:  11.2.0.0.0
       Blocksize:  8192
        Platform:  Linux x86 64-bit
   Timezone file:  V14
**********************************************************************
                           [Update parameters]
         [Update Oracle Database 11.2.0.3.0 init.ora or spfile]

--> If Target Oracle is 32-bit, refer here for Update Parameters:
WARNING: --> "processes" needs to be increased to at least 300

--> If Target Oracle is 64-bit, refer here for Update Parameters:
WARNING: --> "processes" needs to be increased to at least 300
**********************************************************************
**********************************************************************
                          [Renamed Parameters]
                     [No Renamed Parameters in use]
**********************************************************************
**********************************************************************
                    [Obsolete/Deprecated Parameters]
             [No Obsolete or Desupported Parameters in use]
**********************************************************************
                            [Component List]
**********************************************************************
--> Oracle Catalog Views                   [upgrade]  VALID
--> Oracle Packages and Types              [upgrade]  VALID
--> JServer JAVA Virtual Machine           [upgrade]  VALID
--> Oracle XDK for Java                    [upgrade]  VALID
--> Real Application Clusters              [upgrade]  VALID
--> Oracle Workspace Manager               [upgrade]  VALID
--> OLAP Analytic Workspace                [upgrade]  VALID
--> Oracle Enterprise Manager Repository   [upgrade]  VALID
--> Oracle Text                            [upgrade]  VALID
--> Oracle XML Database                    [upgrade]  VALID
--> Oracle Java Packages                   [upgrade]  VALID
--> Oracle Multimedia                      [upgrade]  VALID
--> Oracle Spatial                         [upgrade]  VALID
--> Expression Filter                      [upgrade]  VALID
--> Rule Manager                           [upgrade]  VALID
--> Oracle Application Express             [upgrade]  VALID
--> Oracle OLAP API                        [upgrade]  VALID
**********************************************************************
                              [Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
     minimum required size: 1225 MB
--> SYSAUX tablespace is adequate for the upgrade.
     minimum required size: 1509 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
     minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
     minimum required size: 60 MB
--> EXAMPLE tablespace is adequate for the upgrade.
     minimum required size: 310 MB

                      [No adjustments recommended]

**********************************************************************
**********************************************************************
                          [Pre-Upgrade Checks]
**********************************************************************
WARNING: --> Process Count may be too low

     Database has a maximum process count of 150 which is lower than the
     default value of 300 for this release.
     You should update your processes value prior to the upgrade
     to a value of at least 300.
     For example:
        ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
     or update your init.ora file.

WARNING: --> Enterprise Manager Database Control repository found in the database

     In Oracle Database 12c, Database Control is removed during
     the upgrade. To save time during the Upgrade, this action
     can be done prior to upgrading using the following steps after
     copying rdbms/admin/emremove.sql from the new Oracle home
   - Stop EM Database Control:
    $> emctl stop dbconsole

   - Connect to the Database using the SYS account AS SYSDBA:

   SET ECHO ON;
   SET SERVEROUTPUT ON;
   @emremove.sql
     Without the set echo and serveroutput commands you will not
     be able to follow the progress of the script.

INFORMATION: --> OLAP Catalog(AMD) exists in database

     Starting with Oracle Database 12c, OLAP Catalog component is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the
     ORACLE_HOME/olap/admin/catnoamd.sql script before or
     after the upgrade.

INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 11.2.0.3.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.

INFORMATION: --> There are existing Oracle components that will NOT be
     upgraded by the database upgrade script.  Typically, such components
     have their own upgrade scripts, are deprecated, or obsolete.
     Those components are:  OLAP Catalog,OWB

INFORMATION: --> Oracle Application Express (APEX) can be
     manually upgraded prior to database upgrade

     APEX is currently at version 3.2.1.00.12 and will need to be
     upgraded to APEX version 4.2.5 in the new release.
     Note 1: To reduce database upgrade time, APEX can be manually
             upgraded outside of and prior to database upgrade.
     Note 2: See MOS Note 1088970.1 for information on APEX
             installation upgrades.


**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^


**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^

**********************************************************************
                   ************  Summary  ************

 0 ERRORS exist in your database.
 2 WARNINGS that Oracle suggests are addressed to improve database performance.
 4 INFORMATIONAL messages that should be reviewed prior to your upgrade.

 After your database is upgraded and open in normal mode you must run
 rdbms/admin/catuppst.sql which executes several required tasks and completes
 the upgrade process.

 You should follow that with the execution of rdbms/admin/utlrp.sql, and a
 comparison of invalid objects before and after the upgrade using
 rdbms/admin/utluiobj.sql

 If needed you may want to upgrade your timezone data using the process
 described in My Oracle Support note 1509653.1
                   ***********************************

 

 

Oracle suggested a few recommendations as seen above that needs to be carried out before performing the upgrade. Let’s try running “fixup.sql” script that got generated previously.

 

Fixup sql:

 

[oracle@drnode1 ~]$ sqlplus sys/oracle@srprim as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 21 21:50:00 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @/u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/srprim/preupgrade/preupgrade_fixups.sql

 

If the “fixup.sql” script is unable to fix the recommendations made, then fix them manually.

 

Upgrade:

 

Now that the recommendations have been fixed, let’s move with the upgrade.

 

Create a pfile from the spfile on first instance of 11.2.0.3 database to a temporary location and comment out the following parameters.

 

1. instance_number
2. thread
3. 2nd instance's undo tablespace.
4. cluster_database

 


[oracle@drnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 21 22:10:00 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>
SQL> create pfile='/u02/srprim.ora' from spfile;

File created.

 

[oracle@drnode1 u02]$ cat /u02/srprim.ora | grep ^#
#*.cluster_database=true
#srprim1.instance_number=1
#srprim2.instance_number=2
#srprim2.thread=2
#srprim1.thread=1
#srprim2.undo_tablespace='UNDOTBS2'

 

Stop the database on 11.2.0.3 home

 

[oracle@drnode1 u02]$ srvctl stop database -d srprim

 

Now on the first node, set the environment variables pointing to the newly oracle 12c Home

 

[oracle@drnode1 ~]$ export ORACLE_SID=srprim1
[oracle@drnode1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
[oracle@drnode1 ~]$ export PATH=$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
[oracle@drnode1 ~]$ which sqlplus
/u01/app/oracle/product/12.1.0.2/db_1/bin/sqlplus

 

Start the first instance in upgrade mode from oracle 12c home using the pfile that was created previously in the temporary location.

 

[oracle@drnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 22:18:19 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade pfile='/u02/srprim.ora';
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             729809000 bytes
Database Buffers          335544320 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

 

Now it’s time to run the “catupgrd.sql” script available at 12c ORACLE HOME. Here, I’m running this using the parallel utility “catctl.pl” with 5 number of parallel processes.

 

[oracle@drnode1 ~]$ cd /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/
[oracle@drnode1 admin]$
[oracle@drnode1 admin]$ nohup /u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl catctl.pl -n 5 catupgrd.sql > /u02/upgrade.log &

 

Here is the sample of the upgrade process log. Review the log that was spooled for the execution of “catupgrd.sql” and once the execution is completed successfully, copy the pfile and password file of the instances from 11.2.0.3 home to 12.1.0.2 oracle home.
Make sure that the pfile consists of the path to spfile and nothing else.

 

On first node:

 

[oracle@drnode1 admin]$ cat /u01/app/oracle/product/11.2.0.3/db_1/dbs/initsrprim1.ora
SPFILE='+DATA/srprim/spfilesrprim.ora'          # line added by Agent

 

[oracle@drnode1 admin]$ cp /u01/app/oracle/product/11.2.0.3/db_1/dbs/orapwsrprim1 /u01/app/oracle/product/12.1.0.2/db_1/dbs/
[oracle@drnode1 admin]$
[oracle@drnode1 admin]$ cp /u01/app/oracle/product/11.2.0.3/db_1/dbs/initsrprim1.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/
[oracle@drnode1 admin]$

 

On second node:

 

[oracle@drnode2 ~]$ cat /u01/app/oracle/product/11.2.0.3/db_1/dbs/initsrprim2.ora
SPFILE='+DATA/srprim/spfilesrprim.ora'          # line added by Agent
[oracle@drnode2 ~]$

 

[oracle@drnode2 ~]$ cp /u01/app/oracle/product/11.2.0.3/db_1/dbs/orapwsrprim2 /u01/app/oracle/product/12.1.0.2/db_1/dbs/
[oracle@drnode2 ~]$ cp /u01/app/oracle/product/11.2.0.3/db_1/dbs/initsrprim2.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/
[oracle@drnode2 ~]$

 

Now, start the first instance from new 12c Home and review the parameters. Post that, run the postupgrade_fixups.sql script that was generated earlier.

 

[oracle@drnode1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 22 01:43:55 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             775946344 bytes
Database Buffers          289406976 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL>

 

Execution of postupgrade_fixups.sql:

 

SQL> @/u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/srprim/preupgrade/postupgrade_fixups.sql


SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;


SQL> select count(*) from dba_objects where status = 'INVALID';

  COUNT(*)
----------
      6376

SQL> @?/rdbms/admin/utlrp.sql


SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0


SQL> @?/rdbms/admin/catuppst.sql		 


SQL> @?/rdbms/admin/utlu121s.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.



CATCTL REPORT = /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/srprim/upgrade/upg_summary.log

PL/SQL procedure successfully completed.



Oracle Database 12.1 Post-Upgrade Status Tool           02-22-2017 02:17:23

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                             VALID      12.1.0.2.0  00:45:29
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:13:46
Oracle Real Application Clusters          VALID      12.1.0.2.0  00:00:06
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:02:58
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:01:12
OLAP Catalog                         OPTION OFF      11.2.0.3.0  00:00:00
Oracle OLAP API                           VALID      12.1.0.2.0  00:02:09
Oracle XDK                                VALID      12.1.0.2.0  00:02:17
Oracle Text                               VALID      12.1.0.2.0  00:03:09
Oracle XML Database                       VALID      12.1.0.2.0  00:06:44
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:54
Oracle Multimedia                         VALID      12.1.0.2.0  00:07:37
Spatial                                   VALID      12.1.0.2.0  00:19:07
Oracle Application Express                VALID     4.2.5.00.08  01:02:58
Final Actions                                                    00:05:38
Post Upgrade                                                     00:00:11

Total Upgrade Time: 02:55:35

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Update Summary Table with con_name and endtime.
SQL> --
SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,
  2                                  con_name = SYS_CONTEXT('USERENV','CON_NAME'),
  3                                  endtime  = SYSDATE
  4         WHERE con_id = -1;

1 row updated.

SQL> commit;

Commit complete.

SQL>

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

From 11.2 home, crosscheck if the database is down and then remove its 11.2 configuration from the clusterware managing. (In this post, I’m removing the 11.2 database configuration from clusterware management and adding it back with 12c database configuration settings. You can avoid removing the old configuration and adding the new one by just running “srvctl upgrade database -db -oraclehome ” from the 12c ORACLE HOME.

 

[oracle@drnode1 ~]$ cd /u01/app/oracle/product/11.2.0.3/db_1/bin/
[oracle@drnode1 bin]$ ./srvctl status database -d srprim
Instance srprim1 is not running on node drnode1
Instance srprim2 is not running on node drnode2

 

[oracle@drnode1 bin]$ ./srvctl remove database -d srprim
Remove the database srprim? (y/[n]) y
[oracle@drnode1 bin]$

 

Now, add this database “srprim” with 12c home configuration to the clusterware managing.

 

[oracle@drnode1 bin]$ cd $ORACLE_HOME/bin
[oracle@drnode1 bin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin
[oracle@drnode1 bin]$
[oracle@drnode1 bin]$ ./srvctl add database -d srprim -o /u01/app/oracle/product/12.1.0.2/db_1
[oracle@drnode1 bin]$
[oracle@drnode1 bin]$
[oracle@drnode1 bin]$ ./srvctl add instance -i srprim1 -d srprim -n drnode1
[oracle@drnode1 bin]$
[oracle@drnode1 bin]$
[oracle@drnode1 bin]$ ./srvctl add instance -i srprim2 -d srprim -n drnode2

 

[oracle@drnode1 bin]$
[oracle@drnode1 bin]$ ./srvctl status database -d srprim -v -f
Instance srprim1 is not running on node drnode1
Instance srprim2 is not running on node drnode2

 

If there were any services configured previously, then create them as such from 12c ORACLE HOME.
Start the database now using srvctl and also it’s services.

 

 

[oracle@drnode1 bin]$ ./srvctl add service -s srprim_any -d srprim -r srprim1,srprim2
[oracle@drnode1 bin]$ ./srvctl start database -d srprim
[oracle@drnode1 bin]$ ./srvctl start service -s srprim_any -d srprim
[oracle@drnode1 bin]$ ./srvctl status database -d srprim -v -f
Instance srprim1 is running on node drnode1 with online services srprim_any. Instance status: Open.
Instance srprim2 is running on node drnode2 with online services srprim_any. Instance status: Open.

 

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2017. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

 

 

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organisation. The views expressed by visitors on this blog are theirs solely and may not reflect mine

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: