How do you test an upgrade?

[newbie5]

I am upgrading 2-8.0.3 identical databases with advanced replication to 9.2. As per oracle docs, i first upgraded to 8.1.7 successfully. My question is how to test whether the upgrade is successful? Describe few tables and run some queries or Is there any standard procedure to follow other than checking the logs?

Also before upgrading to 9.2, I want to change 8.1.7 tablesspaces to Locally Managed and convert LONG columnss to LOBS? What is the best way to do this? Drop tablespaces completely, recreate them as extent management local and re-import?

Thanks for your help.

[slimdave]

There are really two questions on whether the upgrade has worked -- has the upgrade been a technical success, and is the application still going to work (with respect to performance etc)

The best method is to run the application against the db to test whether everything is working.

With respect to conversion of ts types and lob's, it could be that there are better tools available for this in 9.2. Check the documentation, particularly for the TS conversion.

[newbie5]

Starting 8.1.6 there seems to be a package:

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL (tablespace, unitsize, rfno);

Anyone used this? Will this package automatically resize initial extents of all tables and indexes. I have a table with an initial extent of 1G which I want to change.

[davey23uk]

no it wont change all your initial extent sizes. All it will do is convert your DMT to an LMT, however dont personally recommed it as you lose some of the major benefits of an LMT, one of which is uniform extents. All you extent sizes will still be the same as they were before.

What you should really do is create new locally managed tablespaces with unform extent sizes of whatever sizes you need and move your existing objects to the new tablespaces.

Wouldn't go for the convert myself, heard it isnt worth it.

[newbie5]

When you upgrade the database, say 803 to 817, all the database files are in the old oracle_home directories such as the control files are in c:\orant\database while the new oracle home is c:\0ra817\database.

To bring the database to OFA standards, do we have to rename all the files? This could get cumbersome if there are lot of files, for example 202 datafiles.

[stecal]

You could create a new database in 817 using OFA, and import the data from 803.

[newbie5]

Yes this is an option. But importing a full database into the new database (with only system tablespace created) is recreating the tablespaces(datafiles) using the old directory paths.

[stecal]

But importing a full database into the new database (with only system tablespace created) is recreating the tablespaces(datafiles) using the old directory paths.

Say what? You don't really mean that, do you?

[newbie5]

I mean it sincerely. Where will it create the datafiles when tablespaces are newly created: In the same directory strucure as full import!!

If it is an old database on the same machine, it will try to create the same datafile or bomb with 'already exists' error!!

[marist89]

Create the before you do the import!