PGTS PGTS Pty. Ltd.   ACN: 007 008 568               Mobile Version Coming Soon

point Site Navigation







Valid HTML 4.01!






   Download Kubuntu Today

   Ubuntu

   The Power Of KDE + Ubuntu





Upgrading Oracle Database on Windows NT/2000

By Gerry Patterson

This paper discusses the issues surrounding upgrading an Oracle Database on a Win NT or Win 2K platform.

Much of this is an adaption of techniques that have been employed on Unix Systems, and relies on some tools from the Cygwin toolkit.


Upgrading an Oracle RDBMS.

This essay is based on a recent project which involved the upgrade of an Oracle database from 7.3.4 on Windows NT to 8.1.7 on Windows 2000.

Upgrading an Oracle NT/2000 database represents an excellant opportunity to upgrade the Operating System to Linux. This will not only save you a packet on operating costs and licence fees but will give you a robust high performance Open Source Server with a swag of powerful Open Source tools to help administer you database.

When you consider upgrading your RDBMS, you should consider upgrading your operating system. Linux is being actively promoted by Oracle and many businesses are choosing this platform. It is stable, reliable and makes good business sense.

However, if you decide not to go down the open source route (or it is not your decision), it is still possible to get some powerful Unix tools to work in Windows using the Cygwin utilities. In this particular project I used some of these tools. ActivePerl for Windows is another invaluable tool for automating administrative tasks, and should part of a DBA's toolkit.

Generally it is preferable to create a new database instance for the upgrade and import the objects from the old instance rather than upgrading the old instance to the new version. Otherwise you might be bringing along a lot of unecessary baggage from the existing installation, not to mention potential problems with the upgrade process. By creating a new 8.1.7 instance and then porting only the application data from the old version 7 database you start with a clean instance.

The First thing to do is identify the objects which need to imported into the new instance. If your application has been well designed, the application objects will have distinct owners (i.e. not 'SYS' or 'SYSTEM'). If this is not the case, you are in for a lot of pain. This article probably won't help you.

Each application is unique and will have its' own peculiarities. Generally speaking however, an application will be set up so that application objects are owned by a small number of application owners, and there will be a sensible naming system for schemas and tablespaces. This should be the case if the system has been created by professional developers

This will vary according to the application. Just run something like the following query:

select owner,object_type, count(*)
from dba_objects
where owner not in ('SYS','SYSTEM')
group by owner,object_type;

Depending on how the database has been setup, you may wish to exclude other owners like DBSNMP or OUTLN.

Usually it is only necessary to review the various packages, sequences, triggers and dbms_jobs. Every application will have unique quirks, and it is advisable to discuss the various processes carefully with the application owner. Be on the look out for unusual "features" in the application. (e.g. "Advanced Queueing").

Creating the new instances is best done in the GUI. Usually on a Unix system this would be done from the command line. On a Windows System however, it is easiest to use the GUI "Assistant" to create the scripts and save them for further modification. Although it is possible to create the scripts from scratch, there are several entries in the Windows Registry which need to be created, and these are dependant on the version of the Operating System.

You will need to review file sizes and locations. You will probably want to keep the same tablespace names.

Start the Windows Database Utility (Oracle Configuration Assistant), and follow these steps:

  1. Choose create a new database.
  2. Choose "Custom"
  3. Select the appropriate model. Beware! Some of these set the default block size to 4096 (e.g. OLTP appears to do this). The default block size of 8192 seems to have been the preferred option in the past.
  4. Answer the questions about numbers of concurrent users, options, Global Database Name, SID, file locations etc. Generally you should avoid spaces in any filenames. Even though Win 2K now handles them (sort of!).
  5. After you have gone through through the various options, do not create the database! Chosse the option that saves the scripts in known location. If you are sticking to the standard pathnames then a pathname like %ORACLE_HOME%/admin/%ORACLE_SID%/scripts would be logical.
NOTE: unless you have a very good reason for not doing so, you should use the standard pathnames -- it makes life easier for other DBAs and maintenance staff and it makes thinkgs easier yourself if you might be absent from the site for any length of time.

Now start an export on the existing database. The export should be done with out column rows For example if you are using Oracle 7.3, a command like the following will suffice:

exp73 sys/passwd full=y rows=n file=g:\path\SID_full.dmp log=g:\path\SID_full.log

Where:
	exp73	(or equivalent) is the export utility (may need full file spec)
	passwd	is the sys password
	SID	is the oracle SID of the source database
	g:\path is the drive and path of the destination export and logfile.
The export file will have all the necessary SQL code that can be used to create a target database. In order to extract it use the strings utility (from the Cygwin distribution):
strings -a g:/path/SID_full.log > temp.sql
Now examine the file temp.sql with vi. With your knowledge of the application you should be able to decide which SQL statements to keep. Most of the necessary system statements will be between the first CONNECT SYSTEM statement and the end of the CREATE DATABASE LINK statements (if there are any). You do not want to keep the code that creates SEQUENCES, TABLES and INDEXES (all of those will be contained in the data exports later).

Having decided which statements to keep, you will then need to alter them. Alterations could be new pathnames for datafiles (unless you decide to keep the old ones). Most alterations can be achieved with global substitution commands from within vi. You may decide to merge some datafiles, and in most cases you will be making the tablespaces larger, so there will be several changes that need to be made to the CREATE TABLESPACE statements. Remember that more recent versions of Oracle require more system tablespace than older versions.

When you have alterered the SQL accordingly, merge it with the create scripts that were saved in step 5 above (with the Oracle Configuration Assistant). One of these scripts will be an SQL script for creating the instance.

NOTE: Don't forget to put the missing semicolons on the end of each line. (use :%s/$/;/).

Finally test out your create script and clean out the bugs. Running this script should result in a new empty database.

You are now ready to import the data and do your acceptance testing.

Start your data export, using a similar export to the previous one. Something like the following:

exp73 sys/passwd owner={owners} compress=y consistent=y file=g:\path\SID_data.dmp log=g:\path\SID_data.log

Where:
	exp73	(or equivalent) is the export utility (may need full file spec)
	passwd	is the sys password
	owners	is a list of the owners of the data (identified earlier).
	SID	is the oracle SID of the source database
	g:\path is the drive and path of the destination export and logfile.
This data can be imported into the new instance.

No doubt there will be other problems to solve. Many of them will be idiosyncratic for the application.