ORACLE 11.2.0.4 TO 12.1.0.2 UPGRADE STEPS

ORACLE 11.2.0.4 TO 12.1.0.2 UPGRADE STEPS

SQL> purge DBA_RECYCLEBIN;

DBA Recyclebin purged.

There should be no invalid  objects in Oracle supplied user schemas – especially one owned by SYS or SYSTEM

SQL> select unique OBJECT_NAME, OBJECT_TYPE, OWNER from DBA_OBJECTS where STATUS='INVALID' order by OWNER;

no rows selected

Always check for DUPLICATE objects in SYS & SYSTEM

SQL> 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');

no rows selected

RUN THE PREUPGRD.SQL

In order to run the preupgrd.sql file, we first need to install the new binaries into an Oracle home for 12c. Once the binaries are in place, we need to setup our environment to connect to the database we want to upgrade.

In my test environment, my Oracle Database 11g settings are:

ORACLE_SID=DPA

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME/u01/app/oracle/product/11.2.0.4/dbhome_1/

Next we need to connect to the 11g database with SQL*Plus and run the preupgrd.sql file:

[oracle@xxxxxxxx ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 24 08:32:15 2016

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, OLAP, Data Mining and Real Application Testing options

SQL> @/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/preupgrd.sql

Loading Pre-Upgrade Package...

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

Executing Pre-Upgrade Checks in DPA...

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

                   ====>> ERRORS FOUND for DPA <<====

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.

          You MUST resolve the above errors prior to upgrade

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

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

               ====>> PRE-UPGRADE RESULTS for DPA <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

/u01/app/oracle/cfgtoollogs/DPA/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

/u01/app/oracle/cfgtoollogs/DPA/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

/u01/app/oracle/cfgtoollogs/DPA/preupgrade/postupgrade_fixups.sql

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

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

Pre-Upgrade Checks in DPA Completed.

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

When the preupgrd.sql script is done, we will be given the locations of the files that we need to reference for verifying and correcting any issues with our environment. Results of the checks are located at:

/u01/app/oracle/cfgtoollogs/DPA/preupgrade/preupgrade.log

Few changes that will be required

SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile;

SQL> alter system set processes=300 scope=spfile;

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

WARNING: --> Enterprise Manager Database Control repository found in the database

go to your Oracle_home/bin directory.

[oracle@xxxxxxxx bin]$ ./emctl stop dbconsole

SET ECHO ON;

SET SERVEROUTPUT ON;

SQL> @/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/emremove.sql

old  69:     IF (upper('&LOGGING') = 'VERBOSE')

new  69:     IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

INFORMATION: --> OLAP Catalog(AMD) exists in database

----> Remove OLAP Catalog

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/olap/admin/catnoamd.sql

----> Remove OLAP API

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/olap/admin/olapidrp.plb

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/olap/admin/catnoxoq.sql

----> Deinstall APS - OLAP AW component

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/olap/admin/catnoaps.sql

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/olap/admin/cwm2drop.sql

----> Recompile invalids

SQL> @?/rdbms/admin/utlrp.sql

SQL> purge DBA_RECYCLEBIN;

DBA Recyclebin purged.

Pre-Upgrade Fixup Script (run in source database environment):

/u01/app/oracle/cfgtoollogs/DPA/preupgrade/preupgrade_fixups.sql

Post-Upgrade Fixup Script (run shortly after upgrade):

/u01/app/oracle/cfgtoollogs/DPA/preupgrade/postupgrade_fixups.sql

Review these scripts and correct anything that needs to be fixes. Once these corrections are made, running the DBUA will be simpler. If there are any errors listed in the preupgrade.log, these need to be corrected before proceeding.

RUNNING DBUA

Once everything has been corrected after reviewing the preupgrade.log, we can start the Database Upgrade Assistant (DBUA). To start the DBUA, we need to go to the Oracle Database 12c home and run dbua:

#> cd /u01/app/oracle/product/12.1.0.2/dbhome_1/bin

#>./dbua &

This will start the GUI to begin the upgrade. You will notice that I did not change anything in my environment. I’m still pointing to the 11g environment.

ORACLE_SID=DPA

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

Once the DBUA starts, you will notice that we are at step 1 of 11.  The number of steps will change depending on the options that are selected. Step 1, we have two options:

• Upgrade an Oracle Database
• Move an existing 12c database to a new 12c oracle home

For the purpose of the update, we can just click next and move on.

Part of the upgrade process we need to identify which Oracle home we want to upgrade. Since we started the DBUA with the Oracle home set to the 11g home, the DBUA will give us all databases associated with that Oracle home.  Select the database to be upgraded, then click next.

In step 3 we see results that look similar to what we looked at in the preupgrade.log. As you can tell, the DBUA is actually running the same preupgrd.sql script and returning the results back to the GUI. Additionally, Oracle has made the DBUA more intelligent by allowing us to select whether we can fix, ignore or revalidate the issues found by the preupgrd.sql. Make the selections you want and continue.

Step 4 is one of the most interesting screens in the DBUA. Oracle has made a fundamental change to how upgrades are handled. Upgrades can now be done in parallel! This is accomplished by using a new perl script,catctl.pl. The number of parallelism is calculated based on the number of CPUs in the server. Additionally, we can recompile object now in parallel and have DBUA perform the upgrade of time zones, gather statistics and make tablespaces read only during the upgrade. Click next to continue.

Step 5 allows us to select how we want to manage our Oracle Database 12c environment. We can either select to use EM Express, the new web interface for Oracle Database 12c that replaces the database console in previous versions or we can register that database with Oracle Enterprise Manager 12c (OEM).

Note: If the OEM12c agents are already installed on the server where the upgrade is being done, the DBUA will pick up the need information automatically.

Step 6 allows us to specify where we want to move our data files and setup our Fast Recovery Area (FRA). We can also configure if we want to use Oracle Managed Files (OMF) at this point.

Step 7 gives us the option to migrate the listener for 11g over to 12c (if not already up). In the image below the 12c binaries are already installed and have a listener running from it. What’s important on this screen is the ‘Migrate’ column. This column will tell you whether or not the listener is going to be migrated.

In step 8 we have the option to create a new backup of our database before upgrading. If we are confident in our backup strategies, we can tell the DBUA not to make a backup by selecting the radio button: ‘I have my own backup and restore strategy’.

Finally, we reach the summary screen (Step 9). This screen will show us what the DBUA thinks it will be doing. One should always review this screen and make sure everything appears to be in order before clicking ‘Finish’.  Once we click finish, the upgrade will begin and we can monitor it via the progress screen.

Once the upgrade is complete, the ‘Stop’ button will change it’s wording and say ‘Upgrade Results’. When you click this button, the interface will change and provide you with the results of the upgrade. At this point, the upgrade is done and the DBUA can be closed. Click the ‘Close’ button to exit the GUI.

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

                    [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;

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

SOME OTHER CHANGES I HAD TO MAKE AFTER THE UPGRADE

• IF YOU HAVE STATSPACK CONFIGURED FOR THIS DATABASE UPGRADING THE STATSPACK SCHEMA FROM 11.2.0.4 TO 12.1.0.2

Follow the general instructions in section 'Upgrading an existing Statspack schema to a newer release' above.
To upgrade:

• ensure you have sufficient free space in the tablespace
• disable any programs which use Statspack
• backup the Statspack schema (e.g. using export)
• run the upgrade by connecting as a user with SYSDBA privilege:

connect perfstat
create table STATS$IOSTAT_FUNCTION_DETAIL
(snap_id number not null
,dbid number not null
,instance_number number not null
,func_id number
,func_name varchar2(20)
,filetyp_id number
,filetyp_name varchar2(30)
,smallrd_MB number
,smallwt_MB number
,largerd_MB number
,largewt_MB number
,num_waits number
,wait_time number
,constraint STATS$IOSTAT_FUNC_PK primary key (snap_id, dbid, instance_number, func_id, filetyp_id) using index tablespace SYSAUX storage (initial 1m next 1m pctincrease 0));

cd $ORACLE12_HOME/rdbms/admin
sqlplus / as sysdba
grant select on v_$iostat_function_detail to perfstat;
@spup112.sql
connect / as sysdba
@spup12102.sql

  

Once the upgrade script completes, check the log files (spup112a.lis and spup112b.lis) for errors.  
If errors are evident, determine and rectify the cause.  
If no errors are evident, re-enable any Statspack data collection or reporting scripts which were previously disabled.

• IF YOU HAVE RMAN CATALOG ON 11.2.0.4 OR LOWER

Upgrading Virtual Private Catalogs to Oracle Database 12c Release 1 (12.1.0.2)

Starting with Oracle Database 12c Release 1 (12.1.0.2), RMAN uses the Virtual Private Database (VPD) functionality to implement virtual private catalogs. The only privilege that you need to grant to the database user who owns the virtual private catalog is the CREATE SESSION privilege. If you created a recovery catalog and virtual private catalogs using a version lower than Oracle Database 12c Release 1 (12.1.0.2), then you must upgrade these virtual private catalogs. RMAN provides scripts, located in the $ORACLE_HOME/rdbms/admin directory, to upgrade virtual private catalogs.

To upgrade existing virtual private catalogs to Oracle Database 12c Release 1 (12.1.0.2):

1. Use SQL*Plus to connect to the recovery catalog database as the SYS user with SYSDBA privilege.

2. Run the dbmsrmansys.sql script to grant additional privileges that are required for the RECOVERY_CATALOG_OWNER role.

1. SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql

3. Connect RMAN to the base recovery catalog, upgrade the base recovery catalog, and then exit RMAN.
4. Assume that the database user who owns the base recovery catalog is rco. The following command upgrades the base recovery catalog. The UPGRADE CATALOG command must be entered twice to confirm the upgrade.
$ rman CATALOG rco@catdb
recovery catalog database Password:
RMAN> UPGRADE CATALOG;
RMAN> UPGRADE CATALOG;
RMAN> EXIT;
5. Use SQL*Plus to connect to the recovery catalog database as the SYS user with SYSDBA privilege.
6. Run the dbmsmanvpc.sql script to upgrade virtual private catalog schemas to the VPD model.
The base recovery catalog schema name must be provided as an input parameter to this script. You can specify a maximum of 10 schema names. Alternately, you can use the -all option to automatically detect base catalog schemas and upgrade all associated virtual private catalog schemas.
7. The following command upgrades the virtual private catalog schemas of the base recovery catalog owned by rco:
SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmanvpc.sql rco