Upgrading Oracle 11g to 12c
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:
- Grant DBA to CTXSYS.
- Run any of your jobs that make a call to ctx_ouput().
- 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