Category: Database

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:

$ORACLE_HOME/rdbms/admin/utlu112i.sql

**********************************************************************
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