DB Upgrade Utility

Copyright © 1999 Martin Wilkinson
Designed and developed by Simon Maxen

Introduction

These scripts operate on a reference schema and a target schema to generate a script that if run against the target schema will bring it in line with the reference schema.

This utility is useful when wanting to upgrade a database to a known state from an unknown state. A database may be in a unknown state because:

• It is not known what version the database is at.
• It is not known what patches have been applied to the database.
• DBA's on the installed site have made modifications to the database.
• Indexes on the target schema have been altered to improve performance.

Overview

The target schema is in line with the reference schema when:

• Named indexes and constraints in the target schema that match a name in the reference schema have the same definition
• All reference tables and table columns are present in the target schema and have the same column attributes.
• The target schema may contain other columns and tables not in the reference schema.
• The column definitions for the target schema may have length, precision or scale greater than the reference schema.
• All unique indexes and constraints present in the reference schema are present in the target schema.
• The target schema does not have any extra unique indexes or constraints that are not present in the reference schema.
• The target schema may contain additional named table check constraints.
• The target schema may contain unique indexes or constraints on non-reference tables or columns.

Requirements

• Access to the web.
• An Oracle user with an empty schema who has been granted the CONNECT, RESOURCE and SELECT ANY TABLE permissions.
• Oracle SQL*Plus
• An reference schema on the same instance as the target schema.

Instructions

1. Download the upgrade scripts from the web site. The scripts are available either as a ZIP file (DBupgrade.ZIP, 12K) or, for Unix users, as a compressed tar file (dbupgrade.tar.Z , 15K). Both compressed archive files contain exactly the same thing.
2. Unzip or uncompress and untar the compressed archive file in the <upgrade dir>.
3. Important: Make a backup copy of the target schema.
4. Edit the upgrade_db.sql script making the following changes:
• Set the TRIGGER_SCRIPT to the location of the reference trigger script in the local file system.
• Set the TAB_STORAGE and IDX_STORAGE to have the table and index storage options used when creating new DB objects. The following options may be specified: INITRANS, MAXTRANS,TABLESPACE,STORAGE,PCTFREE,PCTUSED. The storage options may be configured on an object by object based during inspection of the target schema upgrade script later in the upgrade procedure.
5. As the upgrade Oracle user generate the target upgrade script by running the command below redirecting the output to a target schema upgrade script.
   sqlplus -s @ upgrade_db.sql <reference schema> <target schema> <upgrade dir>
   
6. Inspect the target schema upgrade script investigating any unexpected operations. The following sections will be present in the file:
• A summary of constraints to be dropped with reason.
• A summary of indexes to be dropped with reason.
• A warning that error messages may be generated when dropping indexes. If you are uncomfortable with having error messages generated see section below on avoiding error messages.
• A summary of non-unique indexes that although not present in the target schema will not be created.
7. If any tables were missing from the target schema ensure that the target schema owner is granted permission to select from the missing tables in the reference schema.
8. As the target schema owner run the target schema upgrade script investigating any problems.
9. As the upgrade Oracle user re-generate the target upgrade script.
10. Inspect the script to ensure it does not contain any DDL commands.
11. The upgrade is now complete.

Avoiding error messages

One benefit of the output script is that it generates a series of DDL commands that can be inspected by hand prior to running on the database. This visibility helps re-assure DBA's that the script is not going to do anything unwanted to the database. One consequence of this is that if an index is dropped automatically by Oracle as a consequence of dropping another object an error will result when the index is dropped by the script. To get around this problem run the upgrade in two passes. On the first pass delete all DDL commands in the target schema upgrade script from the start of the index deletion onwards. On the second pass run as normal, no errors should be generated.

A note on performance

This utility is primarily concerned with upgrading the integrity of the target schema. DBA's can fine-tune the indexes by making modifications to the target schema upgrade script. Note that currently there is no guarantee that the order of columns in a created index will match that of the reference schema. This work is left as a future enhancement.

Last updated 20May 1999

Download  dbupgrade.tar.Z  approx. 16K: (for Unix)
Download  dbupgrade.zip       approx. 13K: ( for NT)