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.
1. Perform Pre – Upgrade Check
SQL> @/opt/app/oracle/product/12.1.0.1/dbhome_1/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
/opt/app/oracle/cfgtoollogs/BPMPROD/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/opt/app/oracle/cfgtoollogs/BPMPROD/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/opt/app/oracle/cfgtoollogs/BPMPROD/preupgrade/postupgrade_fixups.sql
************************************************************
Fixup scripts must be reviewed prior to being executed.
************************************************************
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
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
************************************************************
SQL>
dbf10ykf: BPMPROD>cat /opt/app/oracle/cfgtoollogs/BPMPROD/preupgrade/preupgrade.log
Oracle Database Pre-Upgrade Information Tool 05-14-2014 02:10:03
Script Version: 12.1.0.1.0 Build: 006
**********************************************************************
Database Name: BPMPROD
Version: 11.2.0.2.0
Compatible: 11.2.0.0.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V14
**********************************************************************
[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
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [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: 2834 MB
--> SYSAUX tablespace is adequate for the upgrade.
minimum required size: 1841 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
[No adjustments recommended]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
WARNING: --> Existing DBMS_LDAP dependent objects
Database contains schemas with objects dependent on DBMS_LDAP package.
Refer to the Upgrade Guide for instructions to configure Network ACLs.
USER APEX_030200 has dependent objects.
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/oraolap/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.2.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.
ERROR: --> RECYCLE_BIN not empty.
Your recycle bin contains 9 object(s).
It is REQUIRED that the recycle bin is empty prior to upgrading.
Immediately before performing the upgrade, execute the following
command:
EXECUTE dbms_preup.purge_recyclebin_fixup;
**********************************************************************
[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 ************
1 ERROR exist that must be addressed prior to performing your upgrade.
1 WARNING that Oracle suggests are addressed to improve database performance.
2 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 977512.1
***********************************
dbf10ykf: BPMPROD>
SQL> EXECUTE dbms_preup.purge_recyclebin_fixup;
PL/SQL procedure successfully completed.
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
SQL>
2.Create Flashback Restore point just in case.
SQL> alter system set db_recovery_file_dest_size=100G scope=both; System altered. SQL> alter system set db_recovery_file_dest='/dbBPMPROD/db05/oradata/BPMPROD/flashback' scope=both; System altered. SQL> create restore point before_upgrade01 guarantee flashback database; Restore point created. SQL>
3.Perform upgrade using DBUA using silent option.
Set Environment variables.
export ORACLE_BASE=/opt/app/oracle export ORACLE_HOME=/opt/app/oracle/product/12.1.0.1/dbhome_1 export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH export LD_LIBRARY_PATH=/usr/local/lib:$ORACLE_HOME/lib:/usr/lib:$ORACLE_HOME/oracm/lib export PATH=$PATH:$ORACLE_BASE/script:$ORACLE_HOME/OPatch export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
Now let run the upgrade.
dbf10ykf: BPMPROD>dbua -silent > -sid BPMPROD > -oracleHome /opt/app/oracle/product/11.2.0.2/db_1 > -diagnosticDest /opt/app/oracle > -autoextendFiles > -recompile_invalid_objects true > -degree_of_parallelism 2 > -upgradeTimezone > -emConfiguration NONE > -keepHiddenParams > -gatheringStatistics > -createGRP true > -upgrade_parallelism 2 Log files for the upgrade operation are located at: /opt/app/oracle/cfgtoollogs/dbua/BPMPROD/upgrade1 Performing Database Backup 16% complete Listener configuration 16% complete 18% complete Performing Pre Upgrade 21% complete 21% complete 21% complete 21% complete 34% complete Performing RDBMS Upgrade 34% complete 34% complete 35% complete 100% complete Check the log file "/opt/app/oracle/cfgtoollogs/dbua/logs/silent.log_1400048822183" for upgrade details. you have mail in /var/spool/mail/oracle dbf10ykf: BPMPROD>
upgrade completed very fast upon checking the logs found some issue related to flashback.
PL/SQL: ORA-01219: database not open: queries allowed on fixed tables/views only ORA-06550: line 24, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 27, column 15: PLS-00364: loop index variable 'C' use is invalid ORA-06550: line 27, column 5: PL/SQL: Statement ignored UPGRADE_PROGRESS : 34% Performing RDBMS Upgrade UPGRADE_PROGRESS : 34% UPGRADE_PROGRESS : 34% ORA-38760: This database instance failed to turn on flashback database UPGRADE_PROGRESS : 35% Upgrade operation has failed. Check the log files for the upgrade operation located at: /opt/app/oracle/cfgtoollogs/dbua/BPMPROD/upgrade1 UPGRADE_PROGRESS : 100% dbf10ykf: BPMPROD>
To correct it restarted database and restore my restore point created earlier.
dbf10ykf: BPMPROD>sqlplus '/ as sysdba' SQL*Plus: Release 11.2.0.2.0 Production on Wed May 14 02:36:48 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 3140026368 bytes Fixed Size 2230600 bytes Variable Size 3070232248 bytes Database Buffers 50331648 bytes Redo Buffers 17231872 bytes Database mounted. SQL> flashback database to restore point before_upgrade01; Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------------------------------------------ RESTORE POINT ONLY SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------------------------------------------ YES SQL>
now, let’s do the upgrade again.
dbf10ykf: BPMPROD>dbua -silent > > -oracleHome /opt/app/oracle/product/11.2.0.2/db_1 > -diagnosticDest /opt/app/oracle > > -recompile_invalid_objects true > -degree_of_parallelism 2 > -upgradeTimezone > -emConfiguration NONE > -keepHiddenParams > -gatheringStatistics > -createGRP true > -upgrade_parallelism 2 Log files for the upgrade operation are located at: /opt/app/oracle/cfgtoollogs/dbua/BPMPROD/upgrade1 Performing Database Backup 16% complete Performing Pre Upgrade 20% complete 33% complete Performing RDBMS Upgrade 33% complete 34% complete 34% complete 34% complete 35% complete 35% complete 35% complete 36% complete 36% complete 36% complete 37% complete 37% complete 37% complete 38% complete 38% complete 38% complete 39% complete 39% complete 39% complete 40% complete 40% complete 40% complete 41% complete 41% complete 41% complete 42% complete 42% complete 42% complete 43% complete 43% complete 43% complete 44% complete 44% complete 44% complete 45% complete 45% complete 45% complete 46% complete 46% complete 46% complete 47% complete 47% complete 47% complete 48% complete 48% complete 48% complete 49% complete 49% complete 49% complete 50% complete Performing Post Upgrade 51% complete 53% complete 66% complete Upgrading Timezone 83% complete Generating Summary Database upgrade has been completed successfully, and the database is ready to use. 100% complete Check the log file "/opt/app/oracle/cfgtoollogs/dbua/logs/silent.log_1400049729031" for upgrade details. you have mail in /var/spool/mail/oracle dbf10ykf: BPMPROD>
4.Upgrade Complete..
SQL> @/opt/app/oracle/product/12.1.0.1/dbhome_1/rdbms/admin/utlu121s.sql . Oracle Database 12.1 Post-Upgrade Status Tool 05-14-2014 03:56:25 . Component Current Version Elapsed Time Name Status Number HH:MM:SS . Oracle Server . VALID 12.1.0.1.0 00:12:39 JServer JAVA Virtual Machine . VALID 12.1.0.1.0 00:04:23 Oracle Workspace Manager . VALID 12.1.0.1.0 00:01:20 OLAP Analytic Workspace . VALID 12.1.0.1.0 00:00:23 Oracle OLAP API . VALID 12.1.0.1.0 00:00:27 Oracle XDK . VALID 12.1.0.1.0 00:00:40 Oracle Text . VALID 12.1.0.1.0 00:01:26 Oracle XML Database . VALID 12.1.0.1.0 00:03:54 Oracle Database Java Packages . VALID 12.1.0.1.0 00:00:15 Oracle Multimedia . VALID 12.1.0.1.0 00:02:48 Spatial . VALID 12.1.0.1.0 00:05:57 Oracle Application Express . VALID 4.2.0.00.27 00:18:59 Final Actions . 00:02:06 Total Upgrade Time: 00:55:47 PL/SQL procedure successfully completed. SQL>
5. Execute post upgrade script.
SQL> @/opt/app/oracle/cfgtoollogs/BPMPROD/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2014-05-14 04:05:34 Version: 12.1.0.1 Build: 006
Beginning Post-Upgrade Fixups...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
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 12.1.0.1.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.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
PL/SQL procedure successfully completed.
*****************************************
******** 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 ^^^
PL/SQL procedure successfully completed.
**************************************************
************* Fixup Summary ************
1 fixup routine generated an INFORMATIONAL message that should be reviewed.
PL/SQL procedure successfully completed.
*************** Post Upgrade Fixup Script Complete ********************
PL/SQL procedure successfully completed.
SQL>