In my previous article I have demonstrated how to Install and Upgrade Oracle Grid Infrastructure to 12.1.0.2 on Exadata Database Machine. Now it is time to Upgrade Database to 12.1.0.2.
Take a look at the link below for the steps required to Install and Upgrade Oracle GI to 12.1.0.2:
http://netsoftmate.blogspot.in/2017/03/exadata-upgrade-oracle-grid.html
This article covers the following steps:
- Install Database 12.1.0.2 Software
- Upgrade Database to 12.1.0.2 using DBCA &
- Post Upgrade Steps
Install Oracle Database 12.1.0.2 Software
Oracle Database 12.1.0.2 software will be installed in a new Oracle Home without any downtime. The databases currently running will continue run without any issues.
- Unzip the 12.1.0.2 database software if not already done earlier.
dm01db01-orcldb1 {/u01/app/oracle/software}: unzip -q /u01/app/oracle/software/p21419221_121020_Linux-x86-64_1of10.zip -d /u01/app/oracle/software
dm01db01-orcldb1 {/u01/app/oracle/software}: unzip -q /u01/app/oracle/software/p21419221_121020_Linux-x86-64_2of10.zip -d /u01/app/oracle/software
- Create the new Oracle Home directory on all Compute nodes
dm01db01-orcldb1 {/u01/app/oracle/software}:dcli -g ~/dbs_group -l oracle mkdir -p /u01/app/oracle/product/12.1.0.2/dbhome
- Install Oracle Database 12.1.0.2
dm01db01-orcldb1 {/home/oracle}:unset ORACLE_HOME ORACLE_BASE ORACLE_SID
dm01db01- {/home/oracle}:echo $ORACLE_HOME
dm01db01- {/home/oracle}:echo $ORACLE_SID
dm01db01- {/home/oracle}:echo $ORACLE_BASE
dm01db01- {/home/oracle}:export DISPLAY=10.30.20.1:0.0
dm01db01- {/home/oracle}:cd /u01/app/oracle/software/database/
dm01db01- {/u01/app/oracle/software/database}:./runInstaller
Starting Oracle Universal Installer…
Checking Temp space: must be greater than 500 MB. Actual 7567 MB Passed
Checking swap space: must be greater than 150 MB. Actual 16378 MB Passed
Checking monitor: must be configured to display at least 256 colors
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-01-11_04-55-53AM. Please wait …
dm01db01- {/u01/app/oracle/software/database}:
Uncheck Security Update and click Next
Click Yes to ignore the warning and continue with installation
Select “Install database software only” and click Next
Select “Oracle RAC database installation” and click Next
Select all nodes and click Next
Select Language and click Next
Select “Enterprise Edition” and click Next
Specify the Oracle Base and Oracle Home location and click Next
Select OS group for OS authentication and click Next
Prerequisite checks start
Review the summary page and Install button to begin installation
Oracle Software Installation started
Software Installation in progress
Execute Configuration script page appears. Open a new terminal and execute the configuration script on all the compute nodes as root user.
First execute the configuration script on node 1 and then it can be executed on all the remaining nodes parallel.
[root@dm01db01 ~]# /u01/app/oracle/product/12.1.0.2/dbhome/root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of “dbhome” have not changed. No need to overwrite.
The contents of “oraenv” have not changed. No need to overwrite.
The contents of “coraenv” have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
[root@dm01db01 ~]# dcli -g dbs_group -l root ‘/u01/app/oracle/product/12.1.0.2/dbhome/root.sh’
dm01db01: Performing root user operation.
dm01db01:
dm01db01: The following environment variables are set as:
dm01db01: ORACLE_OWNER= oracle
dm01db01: ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
dm01db01:
dm01db01: Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of “dbhome” have
not changed. No need to overwrite.
dm01db01: The contents of “oraenv” have not changed. No need to overwrite.
dm01db01: The contents of “coraenv” have not changed. No need to overwrite.
dm01db01:
dm01db01: Entries will be added to the /etc/oratab file as needed by
dm01db01: Database Configuration Assistant when a database is created
dm01db01: Finished running generic part of root script.
dm01db01: Now product-specific root actions will be performed.
dm01db02: Performing root user operation.
dm01db02:
dm01db02: The following environment variables are set as:
dm01db02: ORACLE_OWNER= oracle
dm01db02: ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
dm01db02:
dm01db02: Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of “dbhome” have
not changed. No need to overwrite.
dm01db02: The contents of “oraenv” have not changed. No need to overwrite.
dm01db02: The contents of “coraenv” have not changed. No need to overwrite.
dm01db02:
dm01db02: Entries will be added to the /etc/oratab file as needed by
dm01db02: Database Configuration Assistant when a database is created
dm01db02: Finished running generic part of root script.
dm01db02: Now product-specific root actions will be performed.
dm01db03: Performing root user operation.
dm01db03:
dm01db03: The following environment variables are set as:
dm01db03: ORACLE_OWNER= oracle
dm01db03: ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
dm01db03:
dm01db03: Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of “dbhome” have
not changed. No need to overwrite.
dm01db03: The contents of “oraenv” have not changed. No need to overwrite.
dm01db03: The contents of “coraenv” have not changed. No need to overwrite.
dm01db03:
dm01db03: Entries will be added to the /etc/oratab file as needed by
dm01db03: Database Configuration Assistant when a database is created
dm01db03: Finished running generic part of root script.
dm01db03: Now product-specific root actions will be performed.
dm01db04: Performing root user operation.
dm01db04:
dm01db04: The following environment variables are set as:
dm01db04: ORACLE_OWNER= oracle
dm01db04: ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
dm01db04:
dm01db04: Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of “dbhome” have
not changed. No need to overwrite.
dm01db04: The contents of “oraenv” have not changed. No need to overwrite.
dm01db04: The contents of “coraenv” have not changed. No need to overwrite.
dm01db04:
dm01db04: Entries will be added to the /etc/oratab file as needed by
dm01db04: Database Configuration Assistant when a database is created
dm01db04: Finished running generic part of root script.
dm01db04: Now product-specific root actions will be performed.
dm01db05: Performing root user operation.
dm01db05:
dm01db05: The following environment variables are set as:
dm01db05: ORACLE_OWNER= oracle
dm01db05: ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
dm01db05:
dm01db05: Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of “dbhome” have
not changed. No need to overwrite.
dm01db05: The contents of “oraenv” have not changed. No need to overwrite.
dm01db05: The contents of “coraenv” have not changed. No need to overwrite.
dm01db05:
dm01db05: Entries will be added to the /etc/oratab file as needed by
dm01db05: Database Configuration Assistant when a database is created
dm01db05: Finished running generic part of root script.
dm01db05: Now product-specific root actions will be performed.
dm01db06: Performing root user operation.
dm01db06:
dm01db06: The following environment variables are set as:
dm01db06: ORACLE_OWNER= oracle
dm01db06: ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
dm01db06:
dm01db06: Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of “dbhome” have
not changed. No need to overwrite.
dm01db06: The contents of “oraenv” have not changed. No need to overwrite.
dm01db06: The contents of “coraenv” have not changed. No need to overwrite.
dm01db06:
dm01db06: Entries will be added to the /etc/oratab file as needed by
dm01db06: Database Configuration Assistant when a database is created
dm01db06: Finished running generic part of root script.
dm01db06: Now product-specific root actions will be performed.
dm01db07: Performing root user operation.
dm01db07:
dm01db07: The following environment variables are set as:
dm01db07: ORACLE_OWNER= oracle
dm01db07: ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
dm01db07:
dm01db07: Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of “dbhome” have
not changed. No need to overwrite.
dm01db07: The contents of “oraenv” have not changed. No need to overwrite.
dm01db07: The contents of “coraenv” have not changed. No need to overwrite.
dm01db07:
dm01db07: Entries will be added to the /etc/oratab file as needed by
dm01db07: Database Configuration Assistant when a database is created
dm01db07: Finished running generic part of root script.
dm01db07: Now product-specific root actions will be performed.
dm01db08: Performing root user operation.
dm01db08:
dm01db08: The following environment variables are set as:
dm01db08: ORACLE_OWNER= oracle
dm01db08: ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome
dm01db08:
dm01db08: Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of “dbhome” have
not changed. No need to overwrite.
dm01db08: The contents of “oraenv” have not changed. No need to overwrite.
dm01db08: The contents of “coraenv” have not changed. No need to overwrite.
dm01db08:
dm01db08: Entries will be added to the /etc/oratab file as needed by
dm01db08: Database Configuration Assistant when a database is created
dm01db08: Finished running generic part of root script.
dm01db08: Now product-specific root actions will be performed.
Go back to the Installation screen and click ok
Click Close to complete installation.
Post Oracle Database 12.1.0.2 Installation
- Re-link Oracle Executable in new Oracle Home with RDS if required
dm01db01-orcldb1 {/home/oracle}:dcli -g ~/dbs_group -l oracle ‘/u01/app/oracle/product/12.1.0.2/dbhome/bin/skgxpinfo’
dm01db01: rds
dm01db02: rds
dm01db03: rds
dm01db04: rds
dm01db05: rds
dm01db06: rds
dm01db07: rds
dm01db08: rds
If the command does not return rds, relink as follows:
dm01db01-orcldb1 {/home/oracle}:dcli oracle -g -l oracle ~/dbs_group ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome make -C /u01/app/oracle/product/12.1.0.2/dbhome/rdbms/lib -f ins_rdbms.mk ipc_rds ioracle
- Install Latest OPatch in the new Oracle Home on all compute nodes
dm01db01-orcldb1 {/u01/app/oracle/software}:dcli -g ~/dbs_group -l oracle /u01/app/oracle/product/12.1.0.2/dbhome/OPatch/opatch version
dm01db01: OPatch Version: 11.2.0.3.15
dm01db01:
dm01db01: OPatch succeeded.
dm01db02: OPatch Version: 11.2.0.3.15
dm01db02:
dm01db02: OPatch succeeded.
dm01db03: OPatch Version: 11.2.0.3.15
dm01db03:
dm01db03: OPatch succeeded.
dm01db04: OPatch Version: 11.2.0.3.15
dm01db04:
dm01db04: OPatch succeeded.
dm01db05: OPatch Version: 11.2.0.3.15
dm01db05:
dm01db05: OPatch succeeded.
dm01db06: OPatch Version: 11.2.0.3.15
dm01db06:
dm01db06: OPatch succeeded.
dm01db07: OPatch Version: 11.2.0.3.15
dm01db07:
dm01db07: OPatch succeeded.
dm01db08: OPatch Version: 11.2.0.3.15
dm01db08:
dm01db08: OPatch succeeded.
Upgrade Database to 12.1.0.2
You can upgrade the Database using DBUA or manually. Here I am using DBUA to upgrade my database orcldb
Prerequisites
- Backing up the database
RMAN> backup database plus archivelog;
- Create a Guaranteed Restore Point
SQL> CREATE RESTORE POINT before_upgrade_12102 GUARANTEE FLASHBACK DATABASE;
Restore point created.
- Analyze the Database to Upgrade with the Pre-Upgrade Information Tool (if not done earlier)
SQL> @/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package…
***************************************************************************
Executing Pre-Upgrade Checks in ORCLDB…
***************************************************************************
************************************************************
====>> ERRORS FOUND for ORCLDB <<====
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.
1) Check Tag: PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fixup Summary:
“The recycle bin will be purged.”
You MUST resolve the above error prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for ORCLDB <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in ORCLDB Completed.
***************************************************************************
***************************************************************************
***************************************************************************
- Review the preupgrade.log file for errors/issues:
dm01db01-orcldb1 {/u01/app/oracle/software}:vi /u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade.log
- Run the preupgrade_fixups.sql produced by pre-upgrade utility above:
dm01db01-orcldb1 {/u01/app/oracle/software}:sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 9 06:29:50 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> @/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2017-01-09 06:26:48 Version: 12.1.0.2 Build: 014
Beginning Pre-Upgrade Fixups…
Executing in container ORCLDB
**********************************************************************
Check Tag: DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary: Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
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.
**********************************************************************
**********************************************************************
Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
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.
**********************************************************************
**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
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.
**********************************************************************
**********************************************************************
Check Tag: APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary: Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
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 ^^^
**************************************************
************* Fixup Summary ************
4 fixup routines generated INFORMATIONAL messages that should be reviewed.
**************** Pre-Upgrade Fixup Script Complete *********************
PL/SQL procedure successfully completed.
- Take care of pre-upgrade Recommendation stated above:
- Increase processes parameter to 300
SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
System altered.
- Drop OLAP Catalog
SQL> @$ORACLE_HOME/olap/admin/catnoamd.sql
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
…
Type dropped.
Type dropped.
PL/SQL procedure successfully completed.
Role dropped.
PL/SQL procedure successfully completed.
1 row deleted.
- Gather dictionary statistics
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
- Before starting the Database Upgrade Assistant it is required change the preference for ‘concurrent statisticsgathering’ on the current release if the current setting is not set to ‘FALSE’.
First, while still on the 11.2. release, obtain the current setting:
SQL> SELECT dbms_stats.get_prefs(‘CONCURRENT’) from dual;
DBMS_STATS.GET_PREFS(‘CONCURRENT’)
——————————————————————————–
FALSE
When ‘concurrent statistics gathering’ is not not set to ‘FALSE’, change the value to ‘FALSE before the upgrade.
SQL> BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘CONCURRENT’,’FALSE’);
END;
/
SQL> SELECT dbms_stats.get_prefs(‘CONCURRENT’) from dual;
- For each database being upgraded use the srvctl command to determine if a ‘TAF policy specification’ with ‘PRECONNECT’ is defined.
dm01db01-orcldb1 {/home/oracle}:srvctl config service -d orcldb | grep -i preconnect | wc -l
0
- Oracle recommends removing the value for the init.ora parameter ‘listener_networks’ before starting DBUA.
SQL> col name for a30
SQL> col value for a30
SQL> select name, value from v$parameter where name=’listener_networks’;
NAME VALUE
—————————— ——————————
listener_networks
dm01db01-orcldb1 {/home/oracle}:export DISPLAY=10.30.204.35:0.0
dm01db01-orcldb1 {/home/oracle}:/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/dbua
Select Upgrade Oracle Database and click Next
Select the source Database Oracle Home, Verify the database details and click Next.
Prerequisite checks begins
Click Next
Increase the Upgrade and recompilation parallelism based on the number of CPUs you have.
Here as part of Upgrade I am upgrading Timezone Data, Gather Stats, Set user tbs to read only.
Click Next
Check if you want to configure EM express for this database. Click Next
Optionally, take RMAN backup if not already done so. Click Next
Review the summary page and click Next
Database Upgrade process begins
Upgrade process continues
While the upgrade process is running, you can monitor the database log as follows:
dm01db01-orcldb1 {/u01/app/oracle/diag/rdbms/orcldb/orcldb1/trace}:tail -f alert_orcldb1.log
Upgrade process continues
Upgrade process continues
Finally the upgrade results appear. Take a note of the result
Scroll down to review the Upgrade result. Once complete, Click close to complete the upgrade process
Post upgrade steps
- Validate databases
dm01db01-dbm011 {/u01/app/11.2.0.4/grid/crs/script}:srvctl status database -d orcldb
Instance orcldb1 is running on node dm01db01
Instance orcldb2 is running on node dm01db02
Instance orcldb3 is running on node dm01db03
Instance orcldb4 is running on node dm01db04
Instance orcldb5 is running on node dm01db05
Instance orcldb6 is running on node dm01db06
Instance orcldb7 is running on node dm01db07
Instance orcldb8 is running on node dm01db08
SQL> select * from v$version;
BANNER CON_ID
——————————————————————————– ———-
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production 0
PL/SQL Release 12.1.0.2.0 – Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 – Production 0
NLSRTL Version 12.1.0.2.0 – Production
- Remove restore point
SQL> DROP RESTORE POINT before_upgrade_12102;
Restore point dropped.
- Run Exachk report
See MOS note 1070954.1
- Deinstall Old Oracle Home (11.2.0.4)
See the article: http://netsoftmate.blogspot.in/2016/12/deinstall-oracle-homes-on-exadata.html
- Perform a database backup
RMAN> backup database plus archivelog;
- Modify ASM compatibility attribute
SQL> ALTER DISKGROUP RECO SET ATTRIBUTE ‘compatible.asm’ = ‘12.1.0.2.0’;
Diskgroup altered.
SQL> ALTER DISKGROUP DATA SET ATTRIBUTE ‘compatible.asm’ = ‘12.1.0.2.0’;
Diskgroup altered.
SQL> alter diskgroup SYSTEMDG set attribute ‘compatible.asm’=’12.1.0.2.0’;
Diskgroup altered.
SQL> select a.group_number,b.name dgname,a.name,a.value from v$asm_attribute a, v$asm_diskgroup b where a.name in (‘au_size’,’disk_repair_time’,’compatible.rdbms’,’compatible.asm’) and a.group_number=b.group_number order by b.name,a.name;
GROUP_NUMBER DGNAME NAME VALUE
———— —————————— —————————— ——————————
1 DATA au_size 4194304
1 DATA compatible.asm 12.1.0.2.0
1 DATA compatible.rdbms 11.2.0.4.0
1 DATA disk_repair_time 3.6h
2 RECO au_size 4194304
2 RECO compatible.asm 12.1.0.2.0
2 RECO compatible.rdbms 11.2.0.4.0
2 RECO disk_repair_time 3.6h
3 SYSTEMDG au_size 4194304
3 SYSTEMDG compatible.asm 12.1.0.2.0
3 SYSTEMDG compatible.rdbms 11.2.0.4.0
3 SYSTEMDG disk_repair_time 3.6h
12 rows selected.
Conclusion
In this article we have Upgrade Oracle Database from 11.2.0.4 to 12.1.0.2. First we have installed 12.1.0.2 in Oracle Home and then upgraded the database to 12.1.0.2. Using DBCA to upgrade an Oracle Database is straight forward and easy to use.