Upgrade database from 11g to 12c using DBUA

DBUA in oracle 12c simplifies the upgrade process a lot . 

SEE: Manual upgrade from 11g to 12c

STEPS:

PRE-REQUSITES:

1. Install Oracle 12c RDMBS homeHow to install oracle 12c binary 

2.Enable flashback database:

This is  to ensure that, if upgrade fails , we can restore it using guarantee restore point.

 

SQL> alter system set db_recovery_file_dest_size=10G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/dumparea/FRA/B2BRBMT3' scope=both;

System altered.

SQL> alter database flashback on;

Database altered.

SQL> startup force

3. Check whether all components are valid or not:

 

set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

COMP_NAME                                STATUS      VERSION
---------------------------------------- ----------- ----------
JServer JAVA Virtual Machine             VALID       11.2.0.4.0
Oracle Application Express               VALID       3.2.1.00.1
Oracle Database Catalog Views            VALID       11.2.0.4.0
Oracle Database Java Packages            VALID       11.2.0.4.0
Oracle Database Packages and Types       VALID       11.2.0.4.0
Oracle Expression Filter                 VALID       11.2.0.4.0
Oracle Multimedia                        VALID       11.2.0.4.0
Oracle Rules Manager                     VALID       11.2.0.4.0
Oracle Text                              VALID       11.2.0.4.0
Oracle Workspace Manager                 VALID       11.2.0.4.0
Oracle XDK                               VALID       11.2.0.4.0
Oracle XML Database                      VALID       11.2.0.4.0

12 rows selected.

 

4. Make sure no invalid objects under sys or system schema:

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

5. Run pre-upgrade tool:

Copy the preupgrd.sql and utluppkg.sql script from Oracle 12c binary to any temporary location .

cp /oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/preupgrd.sql .
cp /oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/utluppkg.sql .

SQL> @preupgrd.sql
Loading Pre-Upgrade Package...

***************************************************************************
Executing Pre-Upgrade Checks in B2BRBMT3...
***************************************************************************

************************************************************

====>> ERRORS FOUND for B2BRBMT3 <<==== 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 B2BRBMT3 <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
/oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
/oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
/oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/postupgrade_fixups.sql

************************************************************

***************************************************************************
Pre-Upgrade Checks in B2BRBMT3 Completed.
***************************************************************************

***************************************************************************

In action required, it is providing preupgrade_fixup.sql scrip to run before upgrade. Run the same in 11g database

@/oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/preupgrade_fixups.sql

Pre-Upgrade Fixup Script Generated on 2016-11-08 01:42:41  Version: 12.1.0.2 Build: 008
Beginning Pre-Upgrade Fixups...
Executing in container B2BRBMT3

**********************************************************************
Check Tag:     PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fix Summary:   The recycle bin will be purged.
**********************************************************************
Fixup Succeeded
**********************************************************************


**********************************************************************
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 ************

 1 fixup routine was successful.
 1 fixup routine returned INFORMATIONAL text that should be reviewed.

**************** Pre-Upgrade Fixup Script Complete *********************


Now review the output of this script. And take action manually, if those were not fixed by the script.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

6. Purge aud$ table ( take backup if required)

SQL> truncate table sys.aud$;

Table truncated.

SQL> truncate table sys.fga_log$;

Table truncated.

7 . Remove enterprise manager if installed:

From 12c , enterprise manager has been decommissioned from 12c. Either we can use oracle 12c/13c cloud control or enterprise database express for monitoring database.

Run below script to remove the EM component:

sql> connect / as sysdba

sql> spool emremoval.log

sql>@emremove.sql

sql> spool off

8. Check the ACLS present:

SQL> select aclid, start_date, end_date from xds_ace where start_date is not null;

no rows selected

If this query returns any row- Please make sure 20369415 is applied to the ORACLE 12C rdbms binary , before upgrade.

UPGRADE:

At this stage, we are still connected to 11g database which is up and running.

Now run the dbua utility from 12c oracle_home location.

cd  oracle/app/oracle/product/12.1.0.2/dbhome_1/bin

./dbua

upgrade_dbua1

upgrade_dbua2 upgrade_dbua3

Select parallelism to required degree and select upgrade timezone , which will upgrade the timezone, after end of db upgrade.

upgrade_dbua4 upgrade_dbua5 upgrade_dbua6 upgrade_dbua7

Make sure to select USE FLASHBACK OPTION: ( it will create a restore point automatically)

upgrade_dbua8 upgrade_dbua9 upgrade_dbua10 upgrade_dbua11 upgrade_dbua12

 

POST-UPGRADE:

1. Run the post upgrade script.

SQL> @/oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2016-11-08 01:42:41  Version: 12.1.0.2 Build: 008
Beginning Post-Upgrade Fixups...

**********************************************************************
Check Tag:     INVALID_OBJECTS_EXIST
Check Summary: Check for invalid objects
Fix Summary:   Invalid objects are displayed and must be reviewed.
**********************************************************************
Fixup Returned Information:
WARNING: --> Database contains INVALID objects prior to upgrade

     The list of invalid SYS/SYSTEM objects was written to
     registry$sys_inv_objs.
     The list of non-SYS/SYSTEM objects was written to
     registry$nonsys_inv_objs unless there were over 5000.
     Use utluiobj.sql after the upgrade to identify any new invalid
     objects due to the upgrade.
**********************************************************************


**********************************************************************
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.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.
**********************************************************************


**********************************************************************
                     [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 ^^^


           **************************************************
                ************* Fixup Summary ************

 2 fixup routines generated INFORMATIONAL messages that should be reviewed.

*************** Post Upgrade Fixup Script Complete ********************

PL/SQL procedure successfully completed.


2. Check the timezone version:

SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat              18          0

3. Check invalid sys or system objects:

SQL> select * from registry$sys_inv_objs;

no rows selected

4. Run utlrp.sql

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

5. Check the status of registry component:

Make sure all are in valid state:

SQL> SQL> SQL> select comp_id,status from dba_registry;

COMP_ID                        STATUS
------------------------------ -----------
APEX                           VALID
ORDIM                          VALID
XDB                            VALID
CONTEXT                        VALID
OWM                            VALID
CATALOG                        VALID
CATPROC                        VALID
JAVAVM                         VALID
XML                            VALID
CATJAVA                        VALID

10 rows selected.

6. Disable flashback and drop restore point:

Once confirmed that upgrade is successfully, then disable the flashback mode and drop the restore point;

Alter database flashback off;


-- get the restore point from v$restore_point:

Drop restore point GRP_1478559837445; 

3 thoughts on “Upgrade database from 11g to 12c using DBUA”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.