The document comes from Mei Yuan. Thanks Mei, for sending this to me. Questions and comments, please contact the author.

Migration: From 7.3.3  To  8.1.6

I.               Check UNIX OS

1.        Disk space for the migration.

For executables: 3 times of the current space

For data dictionary: 2 times of the current space

2.        RAM is sufficient.

Whatever the current buffer required size should be double,

 II.             Contact User for application information

1.        If there is any packages, triggers, watch out for ROWID (might need to run DBMS package to convert ROW ID)

2.        Code change due to data dictionary changes (Migration Manual, Appendix B)

 III.           Copy the existing init<sid>.ora file to new_init<sid>.ora and make modification in the new file.

a.       Make sure the following parameters are in the new_init<sid>.ora:

·         Db_block_size (Keep the same setting).

·         Job_queue_process = 0

·         Log_archive_start = TRUE if the database is running on ARCHIVEMODE.  Be sure, there is enough space in log_archive_dest directory.

·         User_dump_dest, backgroud_dump_dest must be explicit directories

b.       Check the obsolete/changed  parameters

c.       Set compatible = 8.1.6

d.       You should increase the value of SORT_AREA_SIZE in release 8.1 if either of the following conditions were true in a past release:

·         SORT_DIRECT_WRITES was set to FALSE.

·         SORT_DIRECT_WRITES was set to AUTO, and SORT_AREA_SIZE was set to 640 KB or less

e.       The default value for Log_checkpoint_timeout changed from 0 to 1800 sec.

f.        db_domain = teldta.com

 IV.           Take a backup

 V.             Prepare Database for migration

1.        Disable replication (if you use symmetric replication).

2.        Take all the tablespaces off line except SYSTEM and ROLLBACK.

Run tsp_offline.sql

3.        Check if everything is clean

Run check.sql

4.        Check the output check.lst from check.sql

5.        If the SYSTEM rollback segment is not too small

        alter rollback segment system storage (optimal null maxextents 121)

6.        The safest option is to ensure the SYSTEM tablespace has 2/3rds free space, with a minimum of 50Mb free space. Ensure there is a reasonable PCTINCREASE default storage for the SYSTEM tablespace.

7.        Shutdown normal if you have parallel server, otherwise, shutdown immediate

8.        Make sure ORACLE_HOME, ORACLE_SID are included in /etc/oratab file

 VI.           Migration Process (Run mig from command line)

Phase A (Migrate Data Dictionary)

1.        Run .8_1_6profile

2.        cd $ORACLE_HOME/bin

3.        ./migprep /home/oracle/product/8.1.6 /home/oracle/product/7.3.3

4.        Make sure the database is down

5.        . sid <sid name>

6.        cd $ORACLE_HOME/bin

7.    ./mig multiplier=15 spool=\"/tmp/8_1_6mig1.log\"

 Note: You can go back to 7.3.3 by re-run catproc.sql, catparr.sql in the Oracle 7.3.3 /rdbms/admin

 Phase B (Migrate the Data)

1.        Do a full database backup (control files, online datafiles, online redo log files, parameter files, convert file) -- optional

2.        . sid <sid name>

3.        . 8_1_6profile

4.        vi /etc/oratab; change $ORALCE_HOME for the migration sid to the new $ORALCE_HOME

5.        Move the new_init<sid>.ora file to 8i home. Copy IFILE to 8i home

6.        Rename the control files in case you want to go back to Oracle 7

7.        Copy convSID.dbf from Oralce 7 $ORACLE_HOME/dbs to Oracle 8 $ORACLE_HOME/dbs

8.        If you have a password file, copy the password file to Oracle 8 $ORALCE_HOME/dbs

9.        Export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data (ORACLE_HOME in 8i environment).

10.     cd $ORALCE_HOME/rdbms/admin

11.     Start Server Manager (svrmgrl) and connect as internal

12.     Startup nomount

13.     Alter database convert

14.     Alter database open resetlogs

 VII.         Post Migration

1.        Put the tablespace online: run tsp_online.sql

2.        Run the following scripts in Oracle 8i environment:

If this step fails, Correct then rerun any of the scripts described

          as many times as necessary.

 a) If the Oracle system has Oracle replication installed:

       -> SVRMGR> SPOOL catrep.log

       -> run catrep.sql:

          SVRMGR> @catrep.sql

       -> run u0703040.sql:

          SVRMGR> @r0703040.sql

       -> SVRMGR> SPOOL OFF

This r0703040.sql script performs a post-catrep.sql Oracle replication related upgrade.

 b) Run the Oracle8i database conversion script u0703040.sql

       -> SVRMGR> SPOOL catoutm.log

       -> SVRMGR> SET ECHO ON  (optional: tail the log file for viewing)

       -> SVRMGR> @u0703040.sql

       -> SVRMGR> SPOOL OFF

        !! The u0703040.sql script is the database conversion script for all 7.1, 7.2,  and 7.3 releases supported by the Migration utility on your operating system.

          The u0703040.sql script creates and alters certain system tables and drops

 c) If the Oracle system has Oracle Parallel Server installed:

       -> SVRMGR> SPOOL catrepm.log

       -> run catparr.sql:

          SVRMGR> @catparr.sql

       -> SVRMGR> SPOOL OFF

 d) Run utlrp.sql (optional):

       -> SVRMGR> SPOOL utlrpm.log

       -> SVRMGR> @utlrp.sql

       -> SVRMGR> SPOOL OFF

        The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, and so on.

       These actions are optional; however, they ensure that the cost of recompilation

        Oracle Corporation highly recommends performing this optional step.

 e) Check for invalid date constraints:

·         After migration, constraints which have ambiguous date comparisons will be invalid associated tables.

          -> run utlconst.sql

          SVRMGR> SPOOL utlresult.log

          SVRMGR> @utlconst.sql

          SVRMGR> SPOOL OFF

           The utlconst.sql script does not correct bad constraints, but insteadit disables them.

   You should either drop the bad constraints or recreate them after you make the necessary changes.

3.        Shutdown normal or immediate

4.        Startup restrict

5.        Exp system/password full=y file=/dev/null 

Note, there is no file will be generated by this export.  This is just for convert ROWID format in each objects.

6.        Check database links

7.        Change user OUTLN’s password

Alter user outln identified by <password>

8.        Enable replications if there is any

9.        Put the optimal size back to Roll back segments

10.     Do a full database backup.

 Issues:

1.        dbstart is not written for 8.1.6: bug:1264052: change "PL/SQL" for "JServer".

2.        Invalid objects: there are two invalid procedures, one is sys. find_sort_segment_owners, one belongs to ops$oracle. 

The procedure sys. find_sort_segment_owners has a bug in its code.  Please use the new code to recompile it.

Note:  Run utlbstat.sql, utlestat.sql before and after the migration to see the performance gain. Since we do not have distributed database in house, there is no step to take care distribution.