Upgrade 11g to 12c



Install 12c Binaries

Install 12c Software, apply patches but do not create a Database

Please be aware that after your install the new 12c Software, a new Directory will be added to your PATH variable: C:\oracle\product\12.2.0\dbhome_1\bin

 

Source Server Preparation Upgrade Checklist

 

Use this checklist to prepare your server with the earlier Oracle Database release before you start the upgrade.

TAKS

DESCRIPTION

Clean up the database

    • As SYS User, Empty the recycle bin
      • PURGE DBA_RECYCLEBIN;
    • Check for INVALID objects in SYS and SYSTEM
      • col owner format a15
      • col object_name format a45
      • col object_type format a15
      • select owner, object_name, object_type from dba_objects where owner in ('SYS','SYSTEM') and status='INVALID' order by owner,object_type;
    • Check for duplicate objects in SYS and SYSTEM
      • SELECT object_name, object_type, subobject_name, object_id
        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','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH') ;

 

Check materialized views

    • Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
      • SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 

Check Time Zone setting

    • Use the following query to detect the timezone of your source database:
      • select version from v$timezone_file;
    • Default time zone version for Oracle Database 12c release 2 (12.2) is V26
    • If the source database Time Zone version is higher than the target database, then apply the Time Zone patch on the target database to match the source database version.
      Time Zone defaults are in located in the path $ORACLE_HOME/oracore/zoneinfo.

Removing EM Repository (if exists)

    • Execute emremove.sql script. The script will be located in new target  ORACLE_HOME/rdbms/admin
      • SET ECHO ON
        SET SERVEROUTPUT ON
        @emremove.sql
      • After emremove.sql completes, you must manually remove ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directories from your file system.

Check accounts using ONLY 10g password version

    • As SYS, enter the following SQL query
      • select USERNAME from DBA_USERS where ( PASSWORD_VERSIONS = '10G ' or PASSWORD_VERSIONS = '10G HTTP ') and USERNAME <> 'ANONYMOUS';
    • If there is any account, then:
      • 1- Configure the system so that it is not running in Exclusive Mode by editing the setting of the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a level appropriate for affected accounts. For example: SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
      • 2- After you make this change, proceed with the upgrade
      • 3- After the upgrade completes, use the following command syntax to expire the accounts you found in step 1, where username is the name of a user returned from the query in step 1:
        ALTER USER username PASSWORD EXPIRE;
      • 4- Ask the users for whom you have expired the passwords to log in.
      • 5- When these users log in, they are prompted to reset their passwords. The system internally generates the missing 11G and 12C password versions for their account, in addition to the 10G password version. The 10G password version is still present, because the system is running in the permissive mode.

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE

    • As SYS execute:  show parameter SEC_CASE_SENSITIVE_LOGON. If is TRUE or FALSE, remove it from the init.ora File and restart the DB

Gather dictionary statistics to decrease Oracle Database downtime

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Back up the database (recommended)

    • Back up the database, create a guaranteed flashback restore point, or both.
    • Test your fallback strategies at least once before your upgrade window.
    • Ensure that you have fallback strategies for issues both during upgrade, and after upgrade.

 

Perform Upgrade

Now you can invoke DBUA from Command Prompt

Machine generated alternative text: XUsers NDMS)dbua

 

DBUA (Step 1 of 9)

We need to choose the database SID for upgrade to 12.2.0.1. In this case, it is orcl database.

 

DBUA (step 2of 9)

In below screen, DBUA will perform various pre-checks. It will execute the preupgrade script (preupgrade.jar) and return warnings / errors.

Machine generated alternative text: Prerequisite
              Checks Select Database Prerequisite Checks Select LIDQrade
              Options Select Recover,' Options Configure Configure
              Management Summary Progress Database Upgrade Assistant
              step 2 of 9 ORACLE Check Again Checks to be fixed x g
              Check Again ShowWarnings and Errors 12 DATABASE Show All
              Containers Va Zation Severity Warning Warning Error xab e
              Manual Manual Cancel Starting wth Oracle Database 12c, the
              local Enterprise Manager Dat Starting wth Oracle Database
              12c, the OLAP Catalog (OLAP AMD) The recycle bin must be
              completel empt before database upgrade Pre Upgrade
              Recommendations The creation of database triggers must be
              done by users granted w Post Upgrade Recommendations
              Dictionary statistics prov de essential information to the
              Orac e opt„ xed object statistics prov de essential
              information to the Orac eo Se ect a prerequisite check
              from the above tab e to v evvthe details here Back Next

 

DBUA (Step 3 of 9)

Once the pre-upgrade warnings has been adressed, next screen will show various options like Enable Parallel Upgrade, Recompile invalid objects in post upgrade phase, upgrading timezone, gather statistics before the upgrade, setting the user tablespaces to read only during upgrade. also, you can provide any SQL scripts before or after the upgrade. I unchecked the "Gather Statistics Before Upgrade" Option

Machine generated alternative text: Select Upgrade
              Options Select Database Prereauisite Checks Select Upgrade
              Options Select Recoverv Options Configure Net'Aork
              Configure Management Summary Progress Database Upgrade
              Assistant - Enable Parallel Upgrade step 3 of 9 ORACLE
              DATABASE Next 12 Browse Browse Cancel Recompile Invalid
              Objects During Post Upgrade Upgrade Timezone Data Cather
              Statistics Before Upgrade Set User Tablespaces to Read
              Only During the Upgrade Specify the Custom SQL Scripts to
              be executed Before Upgrade After Upgrade Back

 

 

DBUA (Step 4 of 9)

After selecting option, The next screen will show various Recovery options where it will create Guaranteed Restore Point, or Take an RMAN backup or the backup is already taken outside DBUA then "I have my own backup and restore stratege " option can be chosen.

Machine generated alternative text: Select Recovery
              Options Select Database Prereauisite Checks Select
              LIDQrade Options Select Recovery Options ConfiQure Netvwrk
              Configure Management Summary Progress Database Upgrade
              Assistant - Step 4 of 9 ORACLE DATABASE Browse 12 Select
              an option to recover the database in case of upgrade
              failure Use Flashback and Cuaranteed Restore Point Create
              a New Cuaranteed Restore Point Use Aval ab e Cuaranteed
              Restore Point Use RMAN Backup Create a O ffline RMAN
              Backup Backup Location Use Latest Aval able RMAN Backup
              Latest RMAN Backup Timestamp N/A have ml own backup and
              restore strategy Back V ew/Edit Restore Script Cancel Next

 

 

DBUA (Step 5 of 9)

in this screen, we can create a new listener or upgrade the existing listener to 12.2.0.1. I used the existing listener.

Machine generated alternative text: Database Upgrade
              Assistant step 5 of 9 Oracle home ORACLE DATABASE 12
              Migrate No Cancel Configure Network Listener Selection
              Select Database Prereauisite Checks Select LIDQrade
              Options Select Recoverv Options Configure Network
              ConfiQure Manaaement Summary Progress Name LISTENER_11204
              LISTENER_122 Port 1523 1526 Status Dovvn
              /uOI/app/oracIe/product IL20 4/dbhome_I
              /uOI/app/oracIe/product/12 20 1/dbhome_l Create a new
              listene Listener name: Listener 12011: Oracle homæ
              /uÜI/app/oracIe/procIuct/122YII/dbhome_I Back Next

 

 

DBUA (Step 6 of 9)

This screen is for configure EM express or register the upgraded database with EM Cloud control. Uncheck all the options.

Machine generated alternative text: Configure
              Management Select Database Prereauisite Checks Select
              LIDQrade Options Select Recoverv Options ConfiQure Netvwrk
              Configure Management Sum marv Progress Database Upgrade
              Assistant step 6 of 9 ORACLE DATABASE 12 Specify the
              management options for the database Configure Enterprise
              Manager (EM) database express ELI database express poll:
              Register w th Enterprise Manager (EM) cloud control OMS
              host OMS pom ELI admin usernamæ ELI admin DBSNLIP user
              ASLISNLIP user

 

 

DBUA (step 7 of 9)

This is the summary screen before the actual upgrade starts. Click on Finish to proceed with the upgrade.

Machine generated alternative text: Summary Select
              Database Prereauisite Checks Select LIDQrade Options
              Select Recoverv Options ConfiQure Netvwrk ConfiQure
              Manaaement Summary Progress Database Upgrade Assistant -
              Daabase Upgrade Summary Source Database Name orcl Re ease
              112 0 4f_' step 7 of 9 ORACLE DATABASE 12 Oracle Home:
              /uOI/app/oracIe/product IL2 0 4/dbhome_I Target Daabase
              Name orcl Re 1220 LO Oracle Home:
              /uOI/app/oracIe/product/12 20 1/dbhome_l Pre- Upgrade
              WARNINC WARNINC RECOMM RECOMM Checks Starting With Orac e
              Database 12c, the local Enterprise Manager Database Contr
              Starting With Oracle Database 12c, the OLAP Catalog (OLAP
              AMD) desupporte ND Dictionary statistics prov de essential
              information to the Orac e optimizer to END ed object
              statistics proWde essential information to the Orac e
              optimizer t RECOMMEND The creation of database triggers
              must be done by users granted With ADM INFO The Orac e
              database upgrade script upgrades most but nota Orac e
              Database com INFO The recyc e bin must be completely empty
              before database upgrade ORC INFO To reduce database
              upgrade time, you can upgrade APEX manuall before the
              databa Cancel Initializaion Parameter Changes Parameters
              requring an update processe% 300 sga_target: 981467136
              Back Finish

 

DBUA (Step 8 of 9)

The DBUA will start the upgrade process of orcl database. It will perform pre-upgrade, database upgrade and post upgrade steps. It takes about 1 hour.

DBUA will perform the upgrade of database components like Oracle server, XML etc.

Machine generated alternative text: Progress Select
              Database Prerequisite Checks Select Llpgrade Options
              Select Recover/ Options Configure Configure Management Sum
              marq Progress Database Upgrade Assistant - Progress Oracle
              Database upgrade in progress step 8 of 9 ORACLE DATABASE
              12 Upgrading database orcl from Oracle Database release
              IL2 0 411 (/uOI/app/oracIe/product/IL20 4/dbhome_I) to
              Oracle Database release 12 2 0 1 J)
              (/uOI/app/oracIe/product/12 20 1/dbhome_l Steps Pre
              Upgrade Steps Cathering Dictionary Statistics Pre Upgrade
              Step Database Upgrade Steps Configure Database in 1220
              Oracle Home Database Components Upgrade Orac e Server Time
              00011 Status In Progress Finished In Progress DELIA
              /uOI/app/oracIe/cfgtooI 21_10-25-59-PM Alert
              /uol/app/oracle/dlag/rdbms/orcl/orcl/trace/alert orcLIog
              Pause Errors O Cancel

 

 

DBUA Step 9 of 9)

The final screen will show the result of upgrade.

 

Machine generated alternative text: Results Select
              Database Prerequisite Checks Select Llpgrade Options
              Select Recover,' Options Configure Configure Management
              Summary Progress Results Database upgrade Assistant - step
              9 of 9 ORACLE DATABASE 12 Upgrade Results Database upgrade
              has been completed successfully, and the database is
              readvto use Name: Release: Orade Home: Source Daabase
              /u01/app/orac e/product/ll 2 04/dbhome_I Target Daabase
              2201 /u01/app/orac e/product/12201/dbhome_I files for all
              the steps, as Upgrade Details The following summary lists
              the steps performed during the upgrade process as this
              summary, are aval ab eat /u01/app/orac e/cfgtoo
              ogs/dbua/upgrade2016 p am Gathering Dictionary Statisti
              Pre Upgrade 0-25-59-PM/orcl" GatherDOStatIog
              PreUpgradæ og Successful Successful 00011 00018 C ose

 

Machine generated alternative text: Timezone upgrade
              uwsded from WEI c n Post upgrade AMP W TH T IME ZONE dBt8
              cf y A g«s.stent file (spfile) Bt foll Initialization
              Parameter changes fol s Updated: tyget 524288000 10024388%

 

 

It will show the log file location and the time taken by various steps of upgrade.

Machine generated alternative text: Results Select
              Database Prerequisite Checks Select upgrade Options Select
              Recover,' Options Configure Configure Management Summary
              Progress Results Database Upgrade Assistant as this
              summary, are aval ab eat step 9 of 9 12 08 20 40 00 54 00
              C ose ORACLE DATABASE 1_10-25-59-PM/orcl"
              GatherDOStatIog PreUpgradæ og Migrate_Sd og Orac
              e_ServerIog Utlprll og UpgradeTimezonæIog PostUpgradæ og
              generateSummary log 0:00 000 0:00 07 009 0:00 000 000
              Successful Successful Successful Successful Successful
              Successful Successful Successful /u01/app/orac e/cfgtoo
              ogs/dbua/upgrade2016 p am Gathering Dictionary Statisti
              Pre Upgrade Configure Database in I 220 Database
              Components Upgrade Recompile Inva Objects Timezone Upgrade
              Post Upgrade Generate Summary Total Upgrade Time Oracle
              Home Step E,-ecution Infonnation: Post Upgrade

 

 

 

Target Server Post-Upgrade Checklist

Complete these checks on the upgraded Oracle Database environment.

 

TASK

DESCRIPTION

Query for any INVALID Objects

SELECT substr(owner,1,12) owner, substr(object_name,1,12) object, substr(object_type,1,30) object, status from dba_objects WHERE status <> 'VALID';

Run postinstallation SQL scripts as SYS

    • @C:\oracle\product\12.2.0\dbhome_1\rdbms\admin\utlrp.sql

Review upgrade logs and trace files

    • $ORACLE_BASE/cfgtoollogs/DBUA/upgradeTimestamp
      (Look under $ORACLE_HOME if $ORACLE_BASE is not set). Also, folders with the system identifier (SID) of individual database are in this timestamp folder. The SID folders contain files for individual databases for the preupgrade and upgrade process.
      alert_SID.log

Verify upgrade status for CATALOG and CATPROC components

    • select substr(comp_name, 1, 45) as comp_name, substr(version,1,8) as version , substr(status,1,8) as status from dba_registry;
      Look for version and status changes.

Run Oracle Database 12.2 Post-Upgrade Status Tool (utlu122s.sql)

    • @C:\oracle\product\12.2.0\dbhome_1\rdbms\admin\utlu122s.sql

Update time zone settings

    • If Time Zone version at source database is lower than the target database, then run Time Zone conversion after the upgrade.
      Time Zone defaults are in the path $ORACLE_HOME/oracore/zoneinfo

Create Oracle Database system files

    • Create an SPFILE from the PFILE
    • Gather new Oracle Cost-Based Optimizer (CBO) statistics
    • Back up the database