oracledba.help

Upgrading Oracle 11g to 12c

<- Create

Overview

What follows are the manual steps to perform an upgrade of an Oracle database from version 11.2.0.3 to 12.1.0.2 in a Windows environment. Oracle support feels the manual method is more reliable than using the Database Upgrade Assistant (DBUA). MYSID used for database service name in the examples. Change as required for your environment.

This process requires switching between the old and new ORACLE_HOME environment variable. After each change open a new console Window to ensure the steps run correctly.

ORACLE_HOME Environment Variable Examples

11g: C:\app\oracle\product\11.2.0.3\dbhome_1
12c: C:\app\oracle\product\12.1.0.2\dbhome_1

Upgrades to a multitenant container database (CDB) are outside the scope of this document.

Assumptions

  • You have a full RMAN backup of your database.
  • You have a full cold backup of your existing 11g ORACLE_HOME.
  • Currently running Oracle database 11.2.0.2 and later.
  • Oracle 12c database product installed as detailed here?.
  • You have Oracle's latest preupgrade scripts as indicated via Doc ID 884522.1.
    Example file from Oracle: preupgrade_12.1.0.2.0_11_crlf.zip
  • You can log into your Oracle database on the system to be upgraded using this mode of a SQLPlus connection using this exact syntax: sqlplus / as sysdba. This is used by some of the internal processes during the upgrade.
    • For Windows ensure sqlnet.ora SQLNET.AUTHENTICATION_SERVICES = (NTS).
  • You must open all console windows via Run as Administrator even if logged in as the local administrator.

Preupgrade Prep

preupgrd11.sql script is here.

1. Init work dir.
   a. Create dir C:\temp\upg.
   b. Copy Oracle's preupgrade scripts and preupgrd11.sql to C:\temp\upg.
2. Run Oracle 11 environment scripts.
   a. Ensure ORACLE_HOME environment variable set to 11g path.
   b. SQLPLUS> @C:\temp\upg\preupgrd11.sql
   c. Exit SQLPlus.
3. Run Oracle 12 environment scripts.
   a. Change the ORACLE_HOME environment variable to 12c path.
   b. From the 12c path to SQLPlus run preupgrd.sql script.
   c. OS> sqlplus / as sysdba
   d. SQLPLUS> @C:\temp\upg\preupgrd.sql
   e. Exit SQLPlus.
4. Run Preupgrade Fixups Script
   a. OS> sqlplus / as sysdba
   b. @C:\app\oracle\cfgtoollogs\MYSID\preupgrade\preupgrade_fixups.sql
   c. Exit SQLPlus.
5. Remove the Database Control (AKA OEM)
   a. OS> sqlplus / as sysdba
   b. SQLPlus> @C:\temp\upg\emremove.sql
   c. Exit SQLPlus.
   d. OS> sc delete OracleDBConsoleMYSID
6. Make the C:\TEMP\upg\initMYSID.ora usable.
   - Remove the MYSID.__<param> entries at the top.
   - You are left with just *.<param> entries.
  • cd C:\TEMP\upg after each ORACLE_HOME change unless otherwise indicated.

Recreate Services

1. Change your systems ORACLE_HOME environment variable to 11g path.
2. Shutdown all database services.
3. Delete 11g services.
   a. Database:      ORADIM -DELETE -SID MYSID
   b. Listener via: 11g Net Configuration Assistant
4. Change your systems ORACLE_HOME environment variable to 12c path.
   Example: C:\app\oracle\product\12.1.0.2\dbhome_1
5. Create new 12c services.
   a. ORADIM -NEW -SID MYSID -SYSPWD YourSYSPassword -STARTMODE MANUAL 
             -PFILE %ORACLE_HOME%\DATABASE\spfileMYSID.ORA
      File created: %ORACLE_HOME%\DATABASE\PWDMYSID.ORA 
   b. Create Oracle Listener (LISTENER) using the 12c Net Configuration Assistant.
  • cd C:\TEMP\upg after each ORACLE_HOME change unless otherwise indicated.

Upgrade Process

See catctl.pl output example here.

0. These steps assume your ORACLE_HOME is still set to your 12c path.

1. Start Sqlplus for Upgrade
   a. OS> cd  %ORACLE_HOME%\rdbms\admin

   b. OS> sqlplus "/ as sysdba" OR "sys/MyPassword as sysdba"
          Displayed: Connected to an idle instance.

   c. SQLPLUS> spool C:\temp\upg\upgrade.log

   d. SQLPLUS> startup upgrade pfile=C:\TEMP\upg\initMYSID.ora
               Displayed: 
               ORACLE instance started.
               ...
               Database mounted.
               Database opened.
   e. Init SPFILE
      SQLPLUS> CREATE SPFILE FROM pfile='C:\TEMP\upg\initMYSID.ora';
      SQLPLUS> shutdown immediate
      SQLPLUS> startup upgrade
      SQLPLUS> show parameter spfile

   f. Exit sqlplus.

2. Run catctl.pl from the new ORACLE_HOME
   a. cd %ORACLE_HOME%\rdbms\admin

   b. %ORACLE_HOME%\perl\bin\perl catctl.pl -n 3 
         -l %ORACLE_HOME%\diagnostics -d %ORACLE_HOME%\rdbms\admin catupgrd.sql

      - Takes 30+ minutes in most environments: Phase #:0 ... Phase #:73.
        Can be 1.5 hours on slow systems.
      - If seems slow or stuck tail:
        C:\app\oracle\diag\rdbms\<SID>\<SID>\trace\<SID>_ora_<nnnn>.trc
      - The above process performs a shutdown immediate.  

3. Restart the database in normal mode and perform final actions.

   a. Start database normally.
      OS> cd  %ORACLE_HOME%\rdbms\admin
      OS> sqlplus "/ as sysdba" OR  "sys/MyPassword as sysdba"
          Displayed: Connected to an idle instance.

      SQLPLUS> startup

      OR if need be: SQLPLUS> startup pfile='c:\temp\upg\initMYSID.ora'

   b. Recompile any INVALID application objects.
      @%ORACLE_HOME%\rdbms\admin\utlrp.sql     

   c. Run post upgrade fixes script (if previously created).
      spool C:\temp\postupgrade_fixups.spool
      @%ORACLE_BASE%\cfgtoollogs\MYSID\preupgrade\postupgrade_fixups.sql

   d. Verify all issues fixed.
      @%ORACLE_HOME%\rdbms\admin\utlu121s.sql

      Review log created here: %ORACLE_HOME%\cfgtoollogs\MYSID\upgrade

   e. Verify that all packages are valid.
      Generally nothing should be listed.
      @%ORACLE_HOME%\rdbms\admin\utluiobj.sql

   f.  Exit sqlplus before proceeding to the next step.
       Your database is now upgraded to 12c.           

Post Upgrade Process Actions

  • Check the dictionary state.
    Should show all are now using version 12 and VALID.
SELECT substr(comp_id,1,15) comp_id,
       substr(comp_name,1,30) comp_name,
       substr(version,1,10) version,
       status
FROM dba_registry
ORDER BY modified;
Output Example:
Oracle Database 12.1 Post-Upgrade Status Tool   06-15-2015 14:52:51

Component                     Current         Version   Elapsed
Name                           Status          Number   HH:MM:SS

Oracle Server                 UPGRADED      12.1.0.2.0  00:33:15
JServer JAVA Virtual Machine     VALID      12.1.0.2.0  00:16:13
Oracle Workspace Manager         VALID      12.1.0.2.0  00:01:51
Oracle XDK                       VALID      12.1.0.2.0  00:01:25
Oracle Text                      VALID      12.1.0.2.0  00:01:54
Oracle XML Database              VALID      12.1.0.2.0  00:03:25
Oracle Database Java Packages    VALID      12.1.0.2.0  00:00:34
Final Actions                                           00:06:06
Post Upgrade                                            00:08:51

Total Upgrade Time: 01:14:45
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.43
Grand Total Upgrade Time: [0d:1h:21m:1s]
  • 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';
  • Ensure there are no pointers going to previous version file paths. To list all parameters run:
SQL> show parameter
  • Validate that your inventory is OK using OPatch.
 %ORACLE_HOME%\OPatch\opatch lsinventory
  • Two Weeks After Upgrade Update Fixed Stats
    - This is said to improve database performance.
    - Run during busy time of production day.
EXEC dbms_stats.gather_fixed_objects_stats;

Windows Specific

  • Confirm the service file has been created in %ORACLE_HOME%\database. If not run:
    SQL> CREATE SPFILE FROM PFILE='c:\pfileDir\init<SID>.ora';
CREATE SPFILE FROM PFILE='c:\temp\upg\initMYSID.ora';
SPfile should now exist in: %ORACLE_HOME%\database
  • Set the database to use the SPFILE.
OS> %ORACLE_HOME%\bin\oradim -edit -sid MYSID -SPFILE -startmode auto
  • Ensure the SPFILE is being used.
    • Bounce the database services.
    • Check the SPFILE parameter. If the value column is blank then its started using pfile otherwise shows the path to the SPFILE.
SQLPlus> show parameter spfile;
  • Review the registry entries to ensure they look OK.
    HKEY_LOCAL_MACHINE -> SOFTWARE -> Oracle -> KEY_OraDB12Home1
    • Ensure the ORA_<SID>_AUTOSTART is set to TRUE in the Windows registry.
    • Remove the key ORA_<OracleSID>_PFILE if it exists.

Components To Check (If Used)

  • DBMS_STATS Package, Upgrade
    ->EXEC dbms_stats.upgrade_stat_table('green', 'stat_table');
  • SQLNET.ALLOWED_LOGON_VERSION Deprecated
    You must configure new parameters to enable this.
  • Oracle Text
  • Configure ACLS if UTL_* packages.

Check\Upgrade the TimeZone

The default time zone file shipped with the Oracle 12c Release 1 is version 18.

SELECT version FROM v$timezone_file;

If it needs to be upgraded the instructions are here.

Oracle Text Change

In previous releases of Oracle Text, CTXSYS had DBA privileges, and only CTXSYS could perform certain functions, such as modifying system-defined preferences or setting system parameters. In some cases calls to ctx_ouput() that previously worked in 11g now fail.

To fix this:

  1. Grant DBA to CTXSYS.
  2. Run any of your jobs that make a call to ctx_ouput().
  3. Revoke DBA from CTXSYS.

The jobs with ctx_ouput() calls should now continue to work.

APPENDIX: preupgrd11.sql

-- c:\temp\upg\preupgrd11.sql
set verify off
define v_ORA12_HOME=C:\app\oracle\product\12.1.0.2\dbhome_1

PROMPT
PROMPT +----------------+
PROMPT | preupgrd11.sql |
PROMPT +----------------+
PROMPT
ACCEPT v_confirm PROMPT 'If environment is set to Oracle 11 press Enter to Continue'

-- Process
spool c:\temp\upg\Preupgrade_Prep.spool

PROMPT - Updating Stats...
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PROMPT - Purging Recylclebin...
PURGE DBA_RECYCLEBIN;

PROMPT - Invalid Objects
@%ORACLE_HOME%\rdbms\admin\utluiobj.sql

PROMPT - Pending Trans
SELECT * FROM v$recover_file;
SELECT * FROM dba_2pc_pending;

PROMPT - Creating Pfile
CREATE pfile='C:\TEMP\upg\initMYSID.ora' FROM spfile;

PROMPT - Copying Files
--host copy &v_ORA12_HOME\rdbms\admin\preupgrd.sql c:\temp
--host copy &v_ORA12_HOME\rdbms\admin\utluppkg.sql c:\temp
host copy &v_ORA12_HOME\rdbms\admin\emremove.sql c:\temp

-- End
spool off
PROMPT
PROMPT *** Process Ended ***
PROMPT

APPENDIX - catctl.pl Output

The end of the process looks something like this:

   Serial   Phase #:70 Files: 1     Time: 539s
   Serial   Phase #:71 Files: 1     Time: 2s
   Serial   Phase #:72 Files: 1     Time: 0s
   Serial   Phase #:73 Files: 1     Time: 23s

   Grand Total Time: 4861s

   LOG FILES: (catupgrd*.log)

   Upgrade Summary Report Located in:
   C:\app\oracle\product\12.1.0.2\dbhome_1\cfgtoollogs\MYSID
   \upgrade\upg_summary.log

References

  • Check list for manual upgrades (Doc ID 1503653.1).
  • Check list For Oracle On Windows. (Doc ID 443813.1).
  • Common reasons for OUI failures on the MS Windows platforms (Doc ID 436299.1).
  • FAQ: Oracle Database 12.1 Oracle Home User On Windows (Doc ID 1529702.1).
  • Install and Configure Database Server/Client Installations (Doc ID 1351051.2).
  • Master Note For Oracle Database 12c Release 1 (12.1): Doc ID 1520299.1
  • Master Note For Oracle Database Upgrades and Migrations: Doc ID 1152016.1
  • UpgradeTimeZones

<- Create