The following document is the best one you can start documenting yourself from:
11gr2-upgrade-bp-apr2012-1610082.pdf
From the above document, I followed:
- Note 251.1: Database Upgrades from 10.2 to 11.2
On that note, on the PLAN tab, I followed:
Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)
In this document you’ll find a bunch of steps to be followed. The most important one is the pre-upgrade one, which you have to run it on the SOURCE database. This script is meant to display back to you a lot of useful information in respect to what needs to be changed/adapted on the source (prior upgrade) or on the target (after the upgrade). The script can be found here:
$ORACLE_HOME/rdbms/admin/utlu112i.sql
If you run the upgrade on the same server as the source database is on, it’s better if you copy this file in a different place like /tmp or HOME folder.
In my case, between other advices, I got the following warnings:
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin contains 4 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database. The command:
PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
WARNING: --> JOB_QUEUE_PROCESS value must be updated
.... Your current setting of "20" is too low.
.... Starting with Oracle Database 11g Release 2 (11.2), setting
.... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and
.... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES
.... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were
.... unaffected and would still run. This parameter must be updated to
.... a value greater than 48 (default value is 1000) prior to upgrade.
.... Not doing so will affect the running of utlrp.sql after the upgrade
.
**********************************************************************
As you can see, the first advice should be executed on the TARGET database whereas the last 2 on the SOURCE database.
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
SQL> alter system set job_queue_processes=100 scope=both;
System altered.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
Now I just run the usual scripts in order to upgrade to 11204 version:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
conn / as sysdba
STARTUP UPGRADE
SPOOL upgrade11204_DB.log
@catupgrd.sql
-- perform upgrade actions that do not require the database to be in UPGRADE mode
@catuppst.sql
@$ORACLE_HOME/ctx/admin/ctxf102.sql
@utlrp.sql
-- Check for invalid entries:
col comp_name format a80
set linesize 200
set pagesize 2000
SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;
-- Check for invalid objects:
SELECT count(*) FROM dba_invalid_objects;
select owner, object_type, object_name from dba_invalid_objects;
### CPU Patch (if that's the case)
@catbundle cpu apply
@utlrp
In the end the, upgrade was successful. However, I still need to fix the timezone issue which appeared in the upgrade check script in the very first step:
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE
SQL> !ls -ltr $ORACLE_HOME/oracore/zoneinfo/time*14*
-rw-r--r-- 1 oracle oinstall 344448 Jul 16 2013 /u01/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezone_14.dat
-rw-r--r-- 1 oracle oinstall 791430 Jul 16 2013 /u01/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezlrg_14.dat
SQL> exec DBMS_DST.BEGIN_PREPARE(14);
A prepare window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION
14
DST_UPGRADE_STATE
PREPARE
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/
PL/SQL procedure successfully completed.
SQL> SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected
SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.
PL/SQL procedure successfully completed.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1090519944 bytes
Database Buffers 3170893824 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
SQL> spool DST_upgrade.log
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14
DST_SECONDARY_TT_VERSION
4
DST_UPGRADE_STATE
UPGRADE
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1090519944 bytes
Database Buffers 3170893824 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
14
Like this:
Like Loading...
