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 2018. 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
Shivananda Rao 
Leave a Reply