Upgrade Oracle 7.3.4 database to Oracle 8.1 
using the MIG utility

This tip is a compilation of steps from Metalink (Doc ID:  Note:47290.1 : Checklist for Migrating from Oracle7 to Oracle8.0 on UNIX ) and Oracle 8.1 Migration manual. This may be considered as a general checklist, check your OS specific documentation for details, I cannot take responsibility for any error or loss of data. The steps described here are for an UNIX environment.

Setup (One time for each server)

 Oracle 7.3.4 installed under /home/app/oracle/product/7.3.4 (referred as ORAHOME7).

 Oracle 8.1.5 installed under /home/app/oracle/product/8.1.5 (referred as ORAHOME8).

 The sqlnet directory is $ORACLE_HOME/network/admin.  Make sure the tnsnames.ora and sqlnet.ora files are copied to ORAHOME8/network/admin (all the oracle7 alias names exist in oracle8 alias list). This step may not be necessary if TNS_ADMIN variable is set.

Install MIG utility

For converting Oracle7 database to Oracle8, the migration utility needs to be installed under Oracle7 home.  Issue this command in the Oracle8 environment:

Syntax: migprep ORAHOME8 ORAHOME7

Example: migprep /home/app/oracle/product/8.1.5 /home/app/oracle/product/7.3.4

After this, check that these files exist in the following directories, ORACLE_HOME is the version 7 home.

mig             -> $ORACLE_HOME/bin

migus.msb       -> $ORACLE_HOME/rdbms/mesg

migrate.bsq     -> $ORACLE_HOME/dbs

NLS files       -> $ORACLE_HOME/migrate/nls/admin/data

Migration steps for each database

Follow these steps for each database you need to convert on this server.

Things to check on the Version7 Database

The script verify73to81.sql may be used to verify the following - output is written to /tmp/verify73to81.lst. Print this file. Verify against each of the following.

Make sure you have the procedural option (PL/SQL) installed. 

Make sure you do not have a user or role named migrate

Make sure you do not have a user or role named outln

Make sure all tablespaces are online or offline normal.

Check the status of of the datafiles, they should all be online or offline. Files with a recover status cannot be opened after migration.

Check SYSTEM rollback segment. Make sure that OPTIMAL is not set for the SYSTEM rollback segment. Also, check to make sure that the SYSTEM rollback segment is not too small , that it is not reaching MAXEXTENTS (set to 505 or 249) and that NEXT_EXTENT is a reasonable size (set to 512k)

Make sure AUD$ is in the SYSTEM tablespace if database auditing is enabled.

Make sure the default tablespace of user SYS is SYSTEM.

Make sure SYS owned dictionary objects are in the SYSTEM tablespace, if not run catproc.sql and catalog.sql to create them on the SYSTEM tablespace. The SYS default tablespace should be SYSTEM. Look for indexes also, if on different tablespace REBUILD them to SYSTEM tablespace.

Increase the size of the SYSTEM tablespace. Add a datafile or resize an existing datafile. Oracle8 has a larger data dictionary. System should be about 1.5 times that needed in Oracle7. Allow a minimum of 100Mb free space in system, or twice the current space usage, whichever is larger.

Shrink all rollback segments

Default storage of SYSTEM tablespace is adequate (INITIAL 256K NEXT 256K MAXEXTENTS 505 or 249)

Make sure there are no uncommitted transactions outstanding:

Make sure there is no pending distributed transactions: 

Make sure at least 10-15MB is free on the file systems where the control files reside.

Optional - Make Read Only all tablespaces except SYSTEM, TEMP and RBS, it is a very good thing to do. That way, if migration fails, only the SYSTEM, temp and rollback datafiles need to be restored.

Unset TWO_TASK environment variable.

Set ORA_NLS33 to $ORACLE_HOME/migrate/nls/admin/data, where the home is Oracle7.

Shutdown normal the Oracle7 database.

Backup the database

Take a cold backup of the database. This step is very important.

Migrate

Make sure the database shutdown is normal, if not sure, just startup the database and shutdown normal.

No users or DBAs should connect to the database during migration.

 Run the mig command in command-line:

This is command line, mig does a startup of the database so you must shutdown the database before you run this. Set up the environment to Oracle7. Make sure these variables are set to Oracle7.

ORACLE_HOME

PATH

LD_LIBRARY_PATH

ORA_NLS

ORACLE_PATH (if used)

 mig dbname=SID new_dbname=SID spool=\"/tmp/migrate.log\"

The \" is needed to preserve the case of the file name. Here SID is the ORACLE_SID you need to migrate. I prefer to have the same database name for the upgraded version, so dbname and new_dbname are the same SID values.

This step drops all the obsoleted views and tables, creates new O8 tables, views, clusters etc, and updates base tables. It will create a user called migrate. It will also create a convSID.dbf (the convert file) file in ORAHOME7/dbs directory. The convSID.dbf file contains a list of tablespaces, datafiles and redo logs. It also contains the close SCN generated when the database is shutdown by the migration utility.

We now have completed the migration. The next step is to convert the DB. DO NOT open the database which the migration utility has just shutdown. If you do, you will have to run the migration again but you will not have to restore from backup first. The reason is that starting the database will increase the SCN, which has already been recorded in the convSID.dbf file.

 If you have not yet backed up the database this is the last chance to do so.    

 It is not too late to go back to Oracle7 without a restore at this point. You would drop the migrate user and run catalog.sql and catproc.sql. There is no need to run downgrade scripts.

Convert

Setup new environment:

 Change the following environmental variables to Oracle8 (but keep the ORACLE_SID pointing to Oracle7).

ORACLE_HOME

 PATH

LD_LIBRARY_PATH / SHLIB_PATH / LIBPATH

ORA_NLS

ORACLE_PATH (if used)

Tip: Change the /etc/oratab file to point the oracle home of the database to covert, to ORAHOME8. Then use the sid script to set up the environment.

Change ORA_NLS33 to point to Oracle8 ORACLE_HOME/ocommon/nls/admin/data

Rename the current control files in case you want to go back to Oracle 7. The control files cannot exist when doing the database CONVERT, otherwise convert will fail. If you do not want to rename, delete them.

 Make a copy of  the initSID.ora file. Modify the initSID.ora for Oracle8:

Set compatible = 8.1.X (X is your version, should be 5 or 6)

Delete parameters that have been obsolete in Oracle8, if you have them. The exact list of obsolete parameters depends on the releases you are migrating from and to. For migration purposes it is simple to comment out all parameters you are unsure of and re-introduce those which are still valid after the migration is complete. Some of the more common parameters that are obsolete include:

async_read

async_write

ccf_io_size

 checkpoint_process

db_file_standby_name_convert

 db_writers

gc_db_locks and other parallel server GC parameters

log_file_standby_name_convert

sequence_cache_entries

sequence_cache_hash_buckets

 unlimited_rollback_segments

use_readv

v733_plans_enabled

 Modify parameters that have been renamed in Oracle8 if you have them (Comment out while converting):

snapshot_refresh_interval << job_queue_interval

snapshot_refresh_process << job_queue_processes

Copy files to the Oracle8 ORACLE_HOME (ORAHOME8):

Make a link under ORAHOME8/dbs to initSID.ora (If your initSID.ora is in ORAHOME7/dbs directory, just copy the file to ORAHOME8/dbs)

 Copy the convSID.dbf file to the Oracle8 $ORACLE_HOME/dbs from Oracle7 $ORACLE_HOME/dbs directory. Do not modify this file in any way. If the Oracle8 sid is different from the Oracle7 sid, rename the file so that it has the Oracle8 sid.

 If you have a password file you need to move the password file to the Oracle8 $ORACLE_HOME/dbs directory. Otherwise, 'alter database convert;' will fail with ora-600 [KZSRSDN:1][32]. Alternatively, you can set remote_login_passwordfile = NONE in the init.ora so that convert won't attempt to update the password file. However, if you do this, the password file might be lost, you will need to recreate it.

Convert the database:

If you are using a different ORACLE_SID for Oracle7 and Oracle8 then change the ORACLE_SID in your environment to the new value now.

Start Server manager and connect internal. Startup nomount the database. Important, very important, do not mount or open the database.

SVRMGR>  CONNECT INTERNAL
SVRMGR>  STARTUP NOMOUNT

Convert the database. This is the point of NO RETURN to Oracle7. This command will create a controlfile(s) in the new Oracle8 format and alter the datafile headers of all online files by reading the convSID.dbf file. It will also change the ownership of objects created during migration from the migrate to the sys schema.

SVRMGR> ALTER DATABASE CONVERT;

Now open the database. All online rollback segments get converted to the Oracle8 format and the online redo logs will be recreated.

SVRMGR> ALTER DATABASE OPEN RESETLOGS;

Run Scripts

Run the new catalog:

Run in svrmgrl after connecting as internal.

@$ORALCE_HOME/rdbms/admin/u0703040.sql

Recompile PL/SQL:

All packages, procedures, functions, triggers, and views will need to be recompiled because of changes in the P-Code.

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Shutdown normal:

Shutdown the database normal. This will flush all caches, clear buffers, and perform other housekeeping.

Startup the database. 

Startup the database, make the tablespaces which are made READ ONLY to READ WRITE.

Update the listener.ora file to piont to the new Oracle Home. If you create a new listener, and chage the port, update the tnsnames.ora file.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.