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>

finally do the backup…

Thanks

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