Database Upgrade Assistant (DBUA)

Posted on , updated on by Yannick Jaquier
Oracle ,

Table of contents

Preamble

I have always used the manual upgrade method to keep up to date my Oracle databases. So using, for 11gR2 target release of this post, Oracle reference note: Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]. This time I wanted to use the suggested Oracle method i.e. using Database Upgrade Assistant (DBUA). So using reference note Complete checklist to upgrade the database to 11g R2 using DBUA [ID 870814.1].

DBUA pre-upgrade

When using DBUA you need to have both Oracle binaries in parallel, which is an issue for me as my standards for ORACLE_HOME does not contains the release number (i.e. /ora_<sid>/software). So copy old software to software_102.

Rename all the symbolic links with:

find . -type l -exec ls -l {} ; | grep "/ora_10g/software" | awk '{printf "%s %sn",$9,$11}' | while read l1 l2
do
  rm -f $l1
  L2=`echo $l2 | sed 's!/ora_10g/software!/ora_10g/software_102!'`
  ln -s $L2 $l1
done

Change the environment variables of your Unix account to point to new Oracle home. Relink the software and change the ORACLE_HOME in $TNS_ADMIN/listener.ora file:

[ora10g@server1 dbs]$ relink all
[ora10g@server1 dbs]$ vi $TNS_ADMIN/listener.ora

Then it’s time to install 11gR2 binaries in correct ORACLE_HOME( just to make sure your oraInventory will be usable for further upgrade) and associated latest PSU available on Oracle web site.

Then issue utlu112i.sql script from newly created Oracle home:

SQL> @/ora_10g/software/rdbms/admin/utlu112i.SQL
Oracle DATABASE 11.2 Pre-Upgrade Information Tool    09-28-2010 12:35:18
.
**********************************************************************
DATABASE:
**********************************************************************
--> name:          10G
--> version:       10.2.0.5.0
--> compatible:    10.2.0.5.0
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments IN the CURRENT environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required SIZE: 300 MB
.... AUTOEXTEND additional SPACE required: 90 MB
WARNING: --> UNDOTBS1 tablespace is not large enough for the upgrade.
.... currently allocated SIZE: 200 MB
.... minimum required SIZE: 390 MB
.... increase CURRENT SIZE BY: 190 MB
.... TABLESPACE IS NOT AUTOEXTEND ENABLED.
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required SIZE: 180 MB
.... AUTOEXTEND additional SPACE required: 20 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required SIZE: 61 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
UPDATE Parameters: [UPDATE Oracle DATABASE 11.2 init.ora OR spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
.
**********************************************************************
Renamed Parameters: [UPDATE Oracle DATABASE 11.2 init.ora OR spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [UPDATE Oracle DATABASE 11.2 init.ora OR spfile]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by
"diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by
"diagnostic_dest"
--> core_dump_dest               11.1       DEPRECATED   replaced by
"diagnostic_dest"
.
**********************************************************************
Components: [The following DATABASE components will be upgraded OR installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... 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: --> Database contains schemas with stale optimizer statistics.
.... Refer TO the Upgrade Guide FOR instructions TO UPDATE
.... SCHEMA STATISTICS PRIOR TO upgrading the DATABASE.
.... Component Schemas WITH stale STATISTICS:
....   SYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list OF invalid SYS/SYSTEM objects was written TO
.... registry$sys_inv_objs.
.... The list OF non-SYS/SYSTEM objects was written TO
.... registry$nonsys_inv_objs.
.... USE utluiobj.SQL after the upgrade TO identify ANY NEW invalid
.... objects due TO the upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer TO the Upgrade Guide FOR instructions TO configure Network ACLs.
.... USER ORACLE_OCM has dependent objects.
WARNING:--> recycle bin in use.
.... Your recycle bin turned ON.
.... 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.
.
 
PL/SQL PROCEDURE successfully completed.

We need to solve few issues before going further:

  • Increased Undotbs to 400MB.
  • Issued, as SYS:
    SQL> PURGE DBA_RECYCLEBIN;
 
DBA Recyclebin purged.
  • Computed dictionary statistics:
    SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
 
PL/SQL PROCEDURE successfully completed.
  • Finally issued utlrp.sql script to recompile all invalid objects

Re-issued utlu112i.sql script but still complaining for time zone, SYS stale statistics, dependencies on network packages and recycle bin:

WARNING: --> Database is using a timezone file older than version 11.
.... 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: --> Database contains schemas with stale optimizer statistics.
.... Refer TO the Upgrade Guide FOR instructions TO UPDATE
.... SCHEMA STATISTICS PRIOR TO upgrading the DATABASE.
.... Component Schemas WITH stale STATISTICS:
....   SYS
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer TO the Upgrade Guide FOR instructions TO configure Network ACLs.
.... USER ORACLE_OCM has dependent objects.
WARNING:--> recycle bin in use.
.... Your recycle bin turned ON.
.... 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.

As suggested followed Oracle note Script to Check Schemas with Stale Statistics [ID 560336.1] and issue what has been generated by script:

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
 
PL/SQL PROCEDURE successfully completed.

But still complains about SYS stale statistics…

For time zone issue followed Actions For DST Updates When Upgrading To 11.2.0.1 Base Release [ID 815679.1] and for release 10.2.0.4 warning can be discarded. Actions can be taken after 11gR2 upgrade with:

  • update to DSTv11 by following Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST.
  • update to a higher DST than 11 version if this is needed. The latest DST patch is found in Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches.

For the warning on Database contains schemas with objects dependent on network packages it is explained in “WARNING: –> Database contains schemas with objects dependent on network packages” When Running utlu111i.sql [ID 797750.1]. So can be safely ignored.

DBUA upgrade

/etc/oratab must contains an entry with the database you plan to upgrade, unless you don’t see anything in available database to upgrade screen.

Issue 11gR2 DBUA tool. Nothing particular to mention for the few additional questions asked by DBUA, except maybe the additional 31MB for SYSTEM tablespace asked to perform the upgrade.

DBUA throw PLS-00201: identifier SYS.DBMS_JAVA must be declared error message and this is explained in Oracle support note 11GR2 DBUA ORA-06550 PLS-00201 IDENTIFIER SYS.DBMS_JAVA MUST BE DECLARED [ID 1066828.1]. So I just ignored it…

DBUA post-upgrade

I remastered the init.ora and removed the spfile that has been created as not using it. Also copied files from old $TNS_ADMIN directory.

DST stands for Daylight Saving Time. DST files are under $ORACLE_HOME/oracore/zoneinfo directory and with their name you are able to see which DST releases are currently support:

[ora10g@server1 zoneinfo]$ cd $ORACLE_HOME/oracore/zoneinfo
[ora10g@server1 zoneinfo]$ ls
big           timezlrg_10.dat  timezlrg_3.dat  timezlrg_7.dat   timezone_11.dat  timezone_4.dat  timezone_8.dat
little        timezlrg_11.dat  timezlrg_4.dat  timezlrg_8.dat   timezone_1.dat   timezone_5.dat  timezone_9.dat
readme.txt    timezlrg_1.dat   timezlrg_5.dat  timezlrg_9.dat   timezone_2.dat   timezone_6.dat
timezdif.csv  timezlrg_2.dat   timezlrg_6.dat  timezone_10.dat  timezone_3.dat   timezone_7.dat

With 11gR2 base release (11.2.0.1.0) DST up to 11 are supported, with 11.2.0.2.0 DST release up to 14 are supported. You can anyway install latest DST file on a base release directory. I choose the simplest method and will try to upgrade the database to DST 11.

To see which DST file you database is currently using you can issue:

SQL> SELECT version FROM v$timezone_file;
 
   VERSION
----------
         4

So followed Oracle note 977512.1:

SQL> SET lines 130
SQL> col VALUE FOR a60
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> SELECT version FROM v$timezone_file;
 
   VERSION
----------
         4
 
SQL> EXEC DBMS_DST.BEGIN_PREPARE(11);
 
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       11
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> SELECT * FROM sys.dst$affected_tables;
 
no ROWS selected
 
SQL> EXEC DBMS_DST.END_PREPARE;
 
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       0
DST_UPGRADE_STATE              NONE

Preparation was quite positive as no error. Choose DST 11 as latest available and launched the DST upgrade.

SQL> shutdown IMMEDIATE
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
 
Total SYSTEM Global Area  368263168 bytes
Fixed SIZE                  1336596 bytes
Variable SIZE             121637612 bytes
DATABASE Buffers          239075328 bytes
Redo Buffers                6213632 bytes
DATABASE mounted.
DATABASE opened.
SQL> SET lines 130
SQL> col VALUE FOR a60
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> purge dba_recyclebin;
 
DBA Recyclebin purged.
 
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> EXEC DBMS_DST.BEGIN_UPGRADE(11);
 
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         11
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              UPGRADE
 
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES WHERE UPGRADE_IN_PROGRESS='YES';
 
no ROWS selected
 
SQL> shutdown IMMEDIATE
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total SYSTEM Global Area  368263168 bytes
Fixed SIZE                  1336596 bytes
Variable SIZE             121637612 bytes
DATABASE Buffers          239075328 bytes
Redo Buffers                6213632 bytes
DATABASE mounted.
DATABASE opened.
SQL> SET serveroutput ON
VAR numfail NUMBER
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
Failures:0
 
PL/SQL PROCEDURE successfully completed.
 
SQL> VAR fail NUMBER
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
An upgrade window has been successfully ended.
Failures:0
 
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         11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE
 
SQL> SELECT * FROM v$timezone_file;
 
FILENAME                VERSION
-------------------- ----------
timezlrg_11.dat              11

References

  • Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
  • Complete checklist to upgrade the database to 11g R2 using DBUA [ID 870814.1]
  • Script to Check Schemas with Stale Statistics [ID 560336.1]
  • Actions For DST Updates When Upgrading To 11.2.0.1 Base Release [ID 815679.1]
  • WARNING: –> Database contains schemas with objects dependent on network packages” When Running utlu111i.sql [ID 797750.1]
  • 11GR2 DBUA ORA-06550 PLS-00201 IDENTIFIER SYS.DBMS_JAVA MUST BE DECLARED [ID 1066828.1]
  • Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]
  • Updated DST transitions and new Time Zones in Oracle Time Zone File patches [ID 412160.1]

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

This entry was posted in Oracle and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>