How To : Upgrade 11.2.0.2.0 to 12.1.0.1.0

it’s a little bit long time since my last post. though i have some notes on my activity i don’t have to update here.

Assumption :
SID : BPMPROD
old ORACLE_HOME = /opt/app/oracle/product/11.2.0.2/db_1
New ORACLE_HOME = /opt/app/oracle/product/12.1.0.1/dbhome_1

so today, i’m doing upgrade of oracle 11g to oracle 12c. Continue reading

Sanity Check Before Upgrade/Migration

Check for Invalid Objects

select unique OBJECT_NAME, OBJECT_TYPE,
OWNER from DBA_OBJECTS where
STATUS='INVALID';

–> Fix all INVALID objects BEFORE the upgrade or migration
if invalid objects found on SYS/SYSTEM need to fix it.

@?/rdbms/admin/utlrp.sql

Check for Duplicate Objects

Check for duplicate objects on SYS/SYSTEM

select OBJECT_NAME, OBJECT_TYPE from
DBA_OBJECTS where OBJECT_NAME||OBJECT_TYPE
in (select OBJECT_NAME||OBJECT_TYPE from
DBA_OBJECTS where OWNER='SYS') and
OWNER='SYSTEM' and OBJECT_NAME not in
('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES',
'DBMS_REPCAT_AUTH');

–> Fix all DUPLICATE objects BEFORE the upgrade or migration
Note:1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema

Check for Invalid Components

select substr(COMP_ID, 1,10) compid,
substr(COMP_NAME,1,24) compname, STATUS,
VERSION from DBA_REGISTRY where
STATUS'VALID';

–>Try to fix all NON VALID components BEFORE the upgrade
compile using @?/rdbms/admin/utlrp.sql but if it dows not fix it. see notes

Note:472937.1 Information On Installed Database Components
Note:753041.1 How to diagnose Components with NON VALID status

Purge Recycle Bin (10g/11g)

purge DBA_RECYCLEBIN;

Remove Old Parameters

.
.
_always_semi_join=off
_unnest_subquery=false

optimizer_features_enable=9.2.0.8

event = "10061 trace name context forever, level 10"

.
.

.
.

upgrade 10.2.0.1 to 10.2.0.4 on linux with physical standby

PRIMARY : TEST01
STANDBY : TEST01DR

1.Unzip Oracle Installer

oracle@lnxdg01 installer]$ ls -lrt
total 176
drwxr-xr-x 5 oracle oinstall   4096 Mar 12  2008 Disk1/
-rwxr-xr-x 1 oracle oinstall 171882 Mar 17  2008 README.html*
[oracle@lnxdg01 installer]$

2. Stop all services

-stop database and listener  [primay and stadby]

3. execute the installer in silent mode in both primary and standby server.

runInstaller -silent                                 \
      -responseFile /u01/installer/Disk1/response/patchset.rsp   \
       ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1 \
       ORACLE_HOME_NAME=ORADB102_Home10

[oracle@lnxdg02 Disk1]$ ./runInstaller -silent -responseFile /u01/installer/Disk1/response/patchset.rsp ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1 ORACLE_HOME_NAME=ORADB102_Home10
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3
                                      Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-10-31_05-06-37AM. Please wait ...[oracle@lnxdg02 Disk1]$ Oracle Universal Installer, Version 10.2.0.4.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.

You can find a log of this install session at:
 /opt/app/oracle/oraInventory/logs/installActions2011-10-31_05-06-37AM.log
.................................................................................................... 100% Done.

Loading Product Information
.......................................................................................................... 100% Done.

Starting execution of Prerequisites...
Total No of checks: 2

Performing check for checkToInstallCCR
Checking the Components installed in Oracle Home
Check complete. The overall result of this check is: Passed

Check complete: Passed
=======================================================================
Performing check for CompatibilityChecks
Checking for Oracle Home incompatibilities ...
Check complete. The overall result of this check is: Passed

Check complete: Passed
=======================================================================
PrereqChecks complete

Analyzing dependencies 
............................................................................................................. 100% Done.

-----------------------------------------------------------------------------
Summary
Global Settings
    Source: /u01/installer/Disk1/stage/products.xml
    Oracle Home: /opt/app/oracle/product/10.2.0/db_1 (ORADB102_Home10)
Product Languages
   English
Space Requirements
   / Required 1.42GB (includes 55MB temporary) : Available 11.32GB
New Installations (81 products)
   Oracle Notification Service Patch 10.2.0.4.0 
   Database Configuration and Upgrade Assistants Patch 10.2.0.4.0 
   Character Set Migration Utility Patch 10.2.0.4.0 
   Oracle Database 10g Patch 10.2.0.4.0 
   Oracle JDBC/OCI Instant Client Patch 10.2.0.4.0 
   Oracle Database 10g Patch 10.2.0.4.0 
   Enterprise Manager plugin Common Files Patch 10.2.0.4.0 
   SQL*Plus 10.2.0.4.0 
   HAS Common Files Patch 10.2.0.4.0 
   HAS Files for DB Patch 10.2.0.4.0 
   Oracle Java Client Patch 10.2.0.4.0 
   Oracle JVM Patch 10.2.0.4.0 
   Oracle Internet Directory Client Patch 10.2.0.4.0 
   iSQL*Plus 10.2.0.4.0 
   Oracle Advanced Security Patch 10.2.0.4.0 
   Oracle Net Patch 10.2.0.4.0 
   Oracle Net Listener Patch 10.2.0.4.0 
   Oracle Wallet Manager Patch 10.2.0.4.0 
   Precompiler Common Files Patch 10.2.0.4.0 
   Secure Socket Layer Patch 10.2.0.4.0 
   Oracle ODBC Driver Patch 10.2.0.4.0 
   Oracle OLAP Patch 10.2.0.4.0 
   Oracle OLAP API Patch 10.2.0.4.0 
   OLAP SQL Scripts Patch 10.2.0.4.0 
   Oracle interMedia Client Option Patch 10.2.0.4.0 
   Oracle Database 10g interMedia Files Patch 10.2.0.4.0 
   Oracle interMedia Patch 10.2.0.4.0 
   PL/SQL Embedded Gateway Patch 10.2.0.4.0 
   Oracle XML Development Kit Patch 10.2.0.4.0 
   Oracle Text Patch 10.2.0.4.0 
   Oracle Clusterware RDBMS Files Patch 10.2.0.4.0 
   Database SQL Scripts Patch 10.2.0.4.0 
   Oracle Data Mining RDBMS Files Patch 10.2.0.4.0 
   Generic Connectivity Common Files Patch 10.2.0.4.0 
   Installation Common Files Patch 10.2.0.4.0 
   Oracle Starter Database Patch 10.2.0.4.0 
   Sample Schema Data Patch 10.2.0.4.0 
   Oracle interMedia Locator RDBMS Files Patch 10.2.0.4.0 
   Oracle Call Interface (OCI) Patch 10.2.0.4.0 
   Oracle OLAP RDBMS Files Patch 10.2.0.4.0 
   Oracle Partitioning Patch 10.2.0.4.0 
   PL/SQL 10.2.0.4.0 
   Oracle Recovery Manager Patch 10.2.0.4.0 
   Oracle Database Utilities Patch 10.2.0.4.0 
   Oracle interMedia Locator Patch 10.2.0.4.0 
   XML Parser for Java Patch 10.2.0.4.0 
   Assistant Common Files Patch 10.2.0.4.0 
   Oracle JDBC Thin Driver for JDK 1.2 Patch 10.2.0.4.0 
   Oracle JDBC Thin Driver for JDK 1.4 Patch 10.2.0.4.0 
   Oracle interMedia Java Advanced Imaging Patch 10.2.0.4.0 
   SQLJ Runtime Patch 10.2.0.4.0 
   XML Parser for Oracle JVM Patch 10.2.0.4.0 
   Enterprise Manager Agent DB 10.2.0.4.0 
   Enterprise Manager Baseline 10.2.0.4.0 
   Oracle Enterprise Manager Console DB 10.2.0.4.0 
   XDK Required Support Files Patch 10.2.0.4.0 
   Agent Required Support Files Patch 10.2.0.4.0 
   DBJAVA Required Support Files Patch 10.2.0.4.0 
   LDAP Required Support Files Patch 10.2.0.4.0 
   Precompiler Required Support Files Patch 10.2.0.4.0 
   Oracle RAC Required Support Files-HAS Patch 10.2.0.4.0 
   RDBMS Required Support Files for Instant Client Patch 10.2.0.4.0 
   RDBMS Required Support Files Patch 10.2.0.4.0 
   SQL*Plus Required Support Files Patch 10.2.0.4.0 
   SSL Required Support Files for InstantClient Patch 10.2.0.4.0 
   Oracle Net Required Support Files Patch 10.2.0.4.0 
   Oracle Globalization Support Patch 10.2.0.4.0 
   Oracle Core Required Support Files Patch 10.2.0.4.0 
   Platform Required Support Files 10.2.0.4.0 
   Oracle Message Gateway Common Files Patch 10.2.0.4.0 
   Enterprise Manager Agent Core Patch 10.2.0.4.0a 
   Enterprise Manager Common Core Patch 10.2.0.4.0a 
   Enterprise Manager Repository Core patch 10.2.0.4.0a 
   Oracle Containers for Java 10.2.0.4.0 
   Enterprise Manager Repository DB 10.2.0.4.0 
   Oracle LDAP administration patch 10.2.0.4.0 
   Oracle Required Support Files 32 bit Patch 10.2.0.4.0 
   JDBC Common Files 10.2.0.4.0 
   Database Workspace Manager 10.2.0.4.0 
   Oracle Configuration Manager 10.2.7.1.0 
   Oracle Real Application Testing 10.2.0.4.0 
Upgrades (5 products)
   Oracle Universal Installer 10.2.0.4.0 
   Oracle One-Off Patch Installer 10.2.0.4.0 
   Installer SDK Component 10.2.0.4.0 
   Java Runtime Environment 1.4.2.14.0 
   Sun JDK 1.4.2.14.0 
Already Installed (79 products)
   Oracle Notification Service 10.1.0.3.0 
   Database Configuration and Upgrade Assistants 10.2.0.1.0 
   Character Set Migration Utility 10.2.0.1.0 
   Oracle Database 10g 10.2.0.1.0 
   Oracle JDBC/OCI Instant Client 10.2.0.1.0 
   Oracle Database 10g 10.2.0.1.0 
   Enterprise Manager plugin Common Files 10.2.0.1.0 
   SQL*Plus 10.2.0.1.0 
   HAS Common Files 10.2.0.1.0 
   HAS Files for DB 10.2.0.1.0 
   Oracle Java Client 10.2.0.1.0 
   Oracle JVM 10.2.0.1.0 
   Oracle Internet Directory Client 10.2.0.1.0 
   iSQL*Plus 10.2.0.1.0 
   Oracle Advanced Security 10.2.0.1.0 
   Oracle Net 10.2.0.1.0 
   Oracle Net Listener 10.2.0.1.0 
   Oracle Wallet Manager 10.2.0.1.0 
   Precompiler Common Files 10.2.0.1.0 
   Secure Socket Layer 10.2.0.1.0 
   Oracle ODBC Driver 10.2.0.1.0 
   Oracle OLAP 10.2.0.1.0 
   Oracle OLAP API 10.2.0.1.0 
   OLAP SQL Scripts 10.2.0.1.0 
   Oracle interMedia Client Option 10.2.0.1.0 
   Oracle Database 10g interMedia Files 10.2.0.1.0 
   Oracle interMedia 10.2.0.1.0 
   PL/SQL Embedded Gateway 10.2.0.1.0 
   Oracle XML Development Kit 10.2.0.1.0 
   Oracle Text 10.2.0.1.0 
   Oracle Clusterware RDBMS Files 10.2.0.1.0 
   Database SQL Scripts 10.2.0.1.0 
   Oracle Data Mining RDBMS Files 10.2.0.1.0 
   Generic Connectivity Common Files 10.2.0.1.0 
   Installation Common Files 10.2.0.1.0 
   Oracle Starter Database 10.2.0.1.0 
   Sample Schema Data 10.2.0.1.0 
   Oracle interMedia Locator RDBMS Files 10.2.0.1.0 
   Oracle Call Interface (OCI) 10.2.0.1.0 
   Oracle OLAP RDBMS Files 10.2.0.1.0 
   Oracle Partitioning 10.2.0.1.0 
   PL/SQL 10.2.0.1.0 
   Oracle Recovery Manager 10.2.0.1.0 
   Oracle Database Utilities 10.2.0.1.0 
   Oracle interMedia Locator 10.2.0.1.0 
   XML Parser for Java 10.2.0.1.0 
   Assistant Common Files 10.2.0.1.0 
   Oracle JDBC Thin Driver for JDK 1.2 10.2.0.1.0 
   Oracle JDBC Thin Driver for JDK 1.4 10.2.0.1.0 
   Oracle interMedia Java Advanced Imaging 10.2.0.1.0 
   SQLJ Runtime 10.2.0.1.0 
   XML Parser for Oracle JVM 10.2.0.1.0 
   Enterprise Manager Agent DB 10.2.0.1.0 
   Enterprise Manager Baseline 10.2.0.1.0 
   Oracle Enterprise Manager Console DB 10.2.0.1.0 
   XDK Required Support Files 10.2.0.1.0 
   Agent Required Support Files 10.2.0.1.0 
   DBJAVA Required Support Files 10.2.0.1.0 
   LDAP Required Support Files 10.2.0.1.0 
   Precompiler Required Support Files 10.2.0.1.0 
   Oracle RAC Required Support Files-HAS 10.2.0.1.0 
   RDBMS Required Support Files for Instant Client 10.2.0.1.0 
   RDBMS Required Support Files 10.2.0.1.0 
   SQL*Plus Required Support Files 10.2.0.1.0 
   SSL Required Support Files for InstantClient 10.2.0.1.0 
   Oracle Net Required Support Files 10.2.0.1.0 
   Oracle Globalization Support 10.2.0.1.0 
   Oracle Core Required Support Files 10.2.0.1.0 
   Platform Required Support Files 10.2.0.1.0 
   Oracle Message Gateway Common Files 10.2.0.1.0 
   Enterprise Manager Agent Core 10.2.0.1.0 
   Enterprise Manager Common Files 10.2.0.1.0 
   Enterprise Manager Repository Core 10.2.0.1.0 
   Oracle Containers for Java 10.2.0.1.0 
   Enterprise Manager Repository DB 10.2.0.1.0 
   Oracle LDAP administration 10.2.0.1.0 
   Oracle Required Support Files 32 bit 10.2.0.0.0 
   JDBC Common Files 10.2.0.1.0 
   Database Workspace Manager 10.2.0.1.0 
-----------------------------------------------------------------------------

Deinstall in progress (Monday, October 31, 2011 5:13:11 AM SGT)
...............................................................   0% Done.
...............................................................  14% Done.
...............................................................  29% Done.
...............................................................  44% Done.
...............................................................  59% Done.
...............................................................  74% Done.
..............................................                  100% Done.

Deinstall successful

Installation in progress (Monday, October 31, 2011 5:13:12 AM SGT)
...............................................................  14% Done.
...............................................................  29% Done.
...............................................................  44% Done.
...............................................................  59% Done.
...............................................................  74% Done.
......................................................           87% Done.
Install successful

Linking in progress (Monday, October 31, 2011 5:18:35 AM SGT)
...                                                              87% Done.
Link successful

Setup in progress (Monday, October 31, 2011 5:26:05 AM SGT)
.........                                                       100% Done.
Setup successful

End of install phases.(Monday, October 31, 2011 5:26:27 AM SGT)
WARNING:
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
/opt/app/oracle/product/10.2.0/db_1/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts

The installation of Oracle Database 10g Release 2 Patch Set 3 was successful.
Please check '/opt/app/oracle/oraInventory/logs/silentInstall2011-10-31_05-06-37AM.log' for more details.

[oracle@lnxdg02 Disk1]$ su -
Password: 
[root@lnxdg02 ~]# /opt/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/app/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying coraenv to /usr/local/bin ...

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.sh script.
Now product-specific root actions will be performed.
[root@lnxdg02 ~]#

4. Start Database and upgrade.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size		    2019320 bytes
Variable Size		   92274696 bytes
Database Buffers	   71303168 bytes
Redo Buffers		    2174976 bytes
Database mounted.
Database opened.

SQL> spool upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
.
.
.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END  2011-10-31 07:36:19
.
Oracle Database 10.2 Upgrade Status Utility           10-31-2011 07:36:19
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.4.0  00:18:01
JServer JAVA Virtual Machine              VALID      10.2.0.4.0  00:05:55
Oracle XDK                                VALID      10.2.0.4.0  00:00:31
Oracle Database Java Packages             VALID      10.2.0.4.0  00:00:55
Oracle Text                               VALID      10.2.0.4.0  00:00:25
Oracle XML Database                       VALID      10.2.0.4.0  00:02:17
Oracle Workspace Manager                  VALID      10.2.0.4.3  00:01:09
Oracle Data Mining                        VALID      10.2.0.4.0  00:00:32
OLAP Analytic Workspace                   VALID      10.2.0.4.0  00:00:32
OLAP Catalog                              VALID      10.2.0.4.0  00:00:58
Oracle OLAP API                           VALID      10.2.0.4.0  00:01:20
Oracle interMedia                         VALID      10.2.0.4.0  00:22:32
Spatial                                   VALID      10.2.0.4.0  00:03:11
Oracle Expression Filter                  VALID      10.2.0.4.0  00:00:31
Oracle Enterprise Manager                 VALID      10.2.0.4.0  00:02:17
Oracle Rule Manager                       VALID      10.2.0.4.0  00:00:11
.
Total Upgrade Time: 01:01:29
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

5. Verify again that all components are valid.

SQL> col COMP_NAME format a50
set linesize 200
set pagesize 200
 select comp_name,version, status from dba_registry;SQL> SQL> SQL> 

COMP_NAME					   VERSION			  STATUS
-------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views			   10.2.0.4.0			  VALID
Oracle Database Packages and Types		   10.2.0.4.0			  VALID
Oracle Workspace Manager			   10.2.0.4.3			  VALID
JServer JAVA Virtual Machine			   10.2.0.4.0			  VALID
Oracle XDK					   10.2.0.4.0			  VALID
Oracle Database Java Packages			   10.2.0.4.0			  VALID
Oracle Expression Filter			   10.2.0.4.0			  VALID
Oracle Data Mining				   10.2.0.4.0			  VALID
Oracle Text					   10.2.0.4.0			  VALID
Oracle XML Database				   10.2.0.4.0			  VALID
Oracle Rule Manager				   10.2.0.4.0			  VALID
Oracle interMedia				   10.2.0.4.0			  VALID
OLAP Analytic Workspace 			   10.2.0.4.0			  VALID
Oracle OLAP API 				   10.2.0.4.0			  VALID
OLAP Catalog					   10.2.0.4.0			  VALID
Spatial 					   10.2.0.4.0			  VALID
Oracle Enterprise Manager			   10.2.0.4.0			  VALID

17 rows selected.

6. Start the Physical standby

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size		    2083304 bytes
Variable Size		   88081944 bytes
Database Buffers	  171966464 bytes
Redo Buffers		    6303744 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

Check alert log for any error. all changes in primary will be applied once standby is in sync.