Manual Migration of Oracle 8.1.6 to 8.1.7

	Manual Migration of Oracle 8.1.x to 8.1.7 (x < 7)
This is a short Guide to Migrate Oracle 8.1.x (x < 7) to 8.1.7 using Oracle Migration Scripts. We have successfully migrated several databases using this method. Content Special Migration Steps are needed (not documented here) for Prepare to migrate Migration Steps Postmigration Additional Checks after the Migration Special Migration Steps are needed (not documented here) for Upgrading interMedia Text From a Previous 8.1 Release Upgrading JServer Upgrading Oracle Parallel Server Upgrading Snapshots Upgrading the Advanced Queuing Option Prepare to migrate Complete the following steps to begin the upgrade process: Before you migrate or upgrade to release 8.1, make sure the DB_DOMAIN initialization parameter in your initialization parameter file is set to one of the following: .WORLD A valid domain setting for your environment Make sure the _SYSTEM_TRIG_ENABLED initialization parameter is set to FALSE in the initialization parameter file. If this initialization parameter is not currently set, then explicitly set it to FALSE: _SYSTEM_TRIG_ENABLED = FALSE Do NOT drop the OUTLN User If you are upgrading from an 8.1 release, then you do not need to perform this check because the OUTLN user should have been created when you installed the previous 8.1 release. Do not drop the OUTLN user if you are upgrading from a previous 8.1 release. Check free Space in SYSTEM and Rollback Segment Tablepace Upgrading to a new release requires more space in your SYSTEM tablespace and in the tablespaces where you store rollback segments. If you have enough space on your system, then consider adding more space to these tablespaces. In general, you need at least 20 MB of free space in your SYSTEM tablespace to upgrade. Check the free space with the following SQL statement clear buffer clear columns clear breaks set linesize 500 set pagesize 5000 column a1 heading 'Tablespace' format a15 column a2 heading 'Data File' format a45 column a3 heading 'Total\|Space [MB]' format 99999.99 column a4 heading 'Free\|Space [MB]' format 99999.99 column a5 heading 'Free\|%' format 9999.99 break on a1 on report compute sum of a3 on a1 compute sum of a4 on a1 compute sum of a3 on report compute sum of a4 on report SELECT a.tablespace_name a1, a.file_name a2, a.avail a3, NVL(b.free,0) a4, NVL(ROUND(((free/avail)100),2),0) a5 FROM (SELECT tablespace_name, SUBSTR(file_name,1,45) file_name, file_id, ROUND(SUM(bytes/(10241024)),3) avail FROM sys.dba_data_files GROUP BY tablespace_name, SUBSTR(file_name,1,45), file_id) a, (SELECT tablespace_name, file_id, ROUND(SUM(bytes/(10241024)),3) free FROM sys.dba_free_space GROUP BY tablespace_name, file_id) b WHERE a.file_id = b.file_id (+) ORDER BY 1, 2 / Save your 8.1.6 Database Determine the files that you will back up by issuing the following SQL statements: SPOOL v8files.log; SELECT member FROM v$logfile; SELECT name FROM v$datafile; SELECT value FROM v$parameter WHERE name = 'control_files'; SPOOL OFF; Migration Steps Shutdown your 8.1.x Database and Listener (Use shutdown immediate not shutdown abort) SVRMGR> SHUTDOWN IMMEDIATE* LSNRCTL> STOP <Listener-Name> Adjust the initialization parameter file for use with the new release. db_domain = .WORLD optimizer_mode = choose job_queue_processes = 0 aq_tm_processes = 0 Switch to the Oracle 8.1.7 Environment Make sure that the following environment variables point to the new release 8.1 directories: ORACLE_HOME PATH ORA_NLS LD_LIBRARY_PATH Start the Migration (this is the Main Task) cd $ORACLE_HOME/rdbms/admin As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get an error messge: svrmgrl connect internal STARTUP RESTRICT SPOOL revoke_restricted_session; SELECT 'REVOKE restricted session FROM ' \|\| username \|\| ';' FROM dba_users WHERE username NOT IN ('SYS','SYSTEM'); SPOOL OFF; @revoke_restricted_session.log; ALTER SYSTEM ENABLE RESTRICTED SESSION; SPOOL catoutu.log SET ECHO ON @u0801060.sql # Script for 8.1.6 -> 8.1.7 SET ECHO OFF SPOOL OFF ALTER SYSTEM DISABLE RESTRICTED SESSION; SHUTDOWN IMMEDIATE The script you run creates and alters certain dictionary tables. It also runs the catalog.sql and catproc.sql scripts that come with the release to which you are upgrading, which create the system catalog views and all the necessary packages for using PL/SQL. Important Executing this clean shutdown flushes all caches, clears buffers, and performs other DBMS housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle8i database If you retain the old Oracle software, then never start the upgraded database with the old software. Only start the database with the executables in the new release 8.1 installation directory. Postmigration You may execute additional scripts, e.g. # Run all sql scripts for replication option @$ORACLE_HOME/rdbms/admin/catrep.sql # Collect I/O per table (actually object) statistics by # statistical sampling @$ORACLE_HOME/rdbms/admin/catio.sql # This package creates a table into which references to # the chained rows for an IOT (Index-Only-Table) can be # placed using the ANALYZE command. @$ORACLE_HOME/rdbms/admin/dbmsiotc.sql # Wrap Package which creates IOTs (Index-Only-Table) @$ORACLE_HOME/rdbms/admin/prvtiotc.plb # This package allows you to display the sizes of objects # in the shared pool, and mark them for keeping or # unkeeping in order to reduce memory fragmentation. @$ORACLE_HOME/rdbms/admin/dbmspool.sql # Creates the default table for storing the output # of the ANALYZE LIST CHAINED ROWS command @$ORACLE_HOME/rdbms/admin/utlchain.sql # Creates the EXCEPTION table @$ORACLE_HOME/rdbms/admin/utlexcpt.sql # Grant public access to all views used by TKPROF # with verbose=y option @$ORACLE_HOME/rdbms/admin/utltkprf.sql # Create table PLAN_TABLE that is used by the EXPLAIN PLAN # statement. The explain statement requires the presence # of this table in order to store the descriptions of # the row sources. @$ORACLE_HOME/rdbms/admin/utlxplan.sql # Create performance tuning views @$ORACLE_HOME/rdbms/admin/catperf.sql # Create v7 style export/import views against the v8 RDBMS # so that EXP/IMP v7 can be used to read out data in a # v8 RDBMS. These views are necessary if you want to export # from Oracle8 and import in an Oracle7 database. @$ORACLE_HOME/rdbms/admin/catexp7.sql # Create views of oracle locks @$ORACLE_HOME/rdbms/admin/catblock.sql # Print out the lock wait-for graph in a tree # structured fashion @$ORACLE_HOME/rdbms/admin/utllockt.sql # Creates the default table for storing the output of the # analyze validate command on a partitioned table @$ORACLE_HOME/rdbms/admin/utlvalid.sql # PL/SQL Package of utility routines for raw datatypes @$ORACLE_HOME/rdbms/admin/utlraw.sql @$ORACLE_HOME/rdbms/admin/prvtrawb.plb # Contains the PL/SQL interface to the cryptographic toolkit @$ORACLE_HOME/rdbms/admin/dbmsoctk.sql @$ORACLE_HOME/rdbms/admin/prvtoctk.plb # This package provides a built-in random number # generator. It is faster than generators written in PL/SQL # because it calls Oracle's internal random number # generator. @$ORACLE_HOME/rdbms/admin/dbmsrand.sql # DBMS package specification for Oracle8 Large Object # This package provides routines for operations on BLOB # and CLOB datatypes. @$ORACLE_HOME/rdbms/admin/dbmslob.sql # Procedures for instrumenting database applications # DBMS_APPLICATION_INFO package spec. @$ORACLE_HOME/rdbms/admin/dbmsapin.sql # Run obfuscation toolkit script. @$ORACLE_HOME/rdbms/admin/catobtk.sql # Create Heterogeneous Services data dictionary objects. @$ORACLE_HOME/rdbms/admin/caths.sql # Stored procedures for Oracle Trace server @$ORACLE_HOME/rdbms/admin/otrcsvr.sql # Oracle8i Profiler for PL/SQL # Profilers are helpful tools to investigate programs and # identify slow program parts and bottle necks. Furthermore # you can determine which procedure, function or any other # code part is executed how many times. To be able to use # the DBMS_PROFILER package you have to install once for # your database the following packages. Do this as user SYS @$ORACLE_HOME/rdbms/admin/profload.sql @$ORACLE_HOME/rdbms/admin/proftab.sql @$ORACLE_HOME/rdbms/admin/dbmspbp.sql @$ORACLE_HOME/rdbms/admin/prvtpbp.plb Recompiling Invalid PL/SQL Modules The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, etc. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather than in the future @$ORACLE_HOME/rdbms/admin/utlrp.sql Additional Checks after the Migration Check for Bad Date Constraints A bad date constraint involves invalid date manipulation, which is a date manipulation that implicitly assumes the century in the date, causing problems at the year 2000. The utlconst.sql script runs through all of the check constraints in the database and marks constraints as bad if they include any invalid date manipulation. This script selects all the bad constraints at the end. Oracle7 let you create constraints with a two-digit year date constant. However, version 8 returns an error if the check constraint date constant does not include a four-digit year. To run the utlconst.sql script, complete the following steps: cd $ORACLE_HOME/rdbms/admin svrmgrl SVRMGR> CONNECT INTERNAL SVRMGR> SPOOL utlresult.log SVRMGR> @utlconst.sql SVRMGR> SPOOL OFF Server Output ON Statement processed. Statement processed. Checking for bad date constraints Finished checking -- All constraints OK! After you run the script, the utlresult.log log file includes all the constraints that have invalid date constraints. The utlconst.sql script does not correct bad constraints, but instead it disables them. You should either drop the bad constraints or recreate them after you make the necessary changes. Rebuild Unusable Bitmap Indexes During migration, some bitmap indexes may become unusable. To find these indexes, issue the following SQL statement: SELECT index_name, index_type, table_owner, status FROM dba_indexes WHERE index_type = 'BITMAP' AND status = 'UNUSABLE'; Rebuild Unusable Function-Based Indexes During upgrade, some function-based indexes may become unusable. To find these indexes, issue the following SQL statement: SELECT owner, index_name, funcidx_status FROM dba_indexes WHERE funcidx_status = 'DISABLED'; Change the Password for the OUTLN User The OUTLN user is created automatically during installation of Oracle8i. This user has DBA privileges. Use the ALTER USER statement to change the password for this user. Oracle8i adds the OUTLN user schema to support Plan Stability. The OUTLN user acts as a place to centrally manage metadata associated with stored outlines.

Manual Migration of Oracle 8.1.x to 8.1.7 (x < 7)

This is a short Guide to Migrate Oracle 8.1.x (x < 7) to 8.1.7 using Oracle Migration Scripts. We have successfully migrated several databases using this method.

Content

Special Migration Steps are needed (not documented here) for
Prepare to migrate
Migration Steps
Postmigration
Additional Checks after the Migration

Special Migration Steps are needed (not documented here) for

Upgrading interMedia Text From a Previous 8.1 Release
Upgrading JServer
Upgrading Oracle Parallel Server
Upgrading Snapshots
Upgrading the Advanced Queuing Option

Prepare to migrate

Complete the following steps to begin the upgrade process:

Before you migrate or upgrade to release 8.1, make sure the DB_DOMAIN initialization parameter in your initialization parameter file is set to one of the following:

.WORLD

A valid domain setting for your environment

Make sure the _SYSTEM_TRIG_ENABLED initialization parameter is set to FALSE in the initialization parameter file. If this initialization parameter is not currently set, then explicitly set it to FALSE:

_SYSTEM_TRIG_ENABLED = FALSE

Do NOT drop the OUTLN User

If you are upgrading from an 8.1 release, then you do not need to perform this check because the OUTLN user should have been created when you installed the previous 8.1 release. Do not drop the OUTLN user if you are upgrading from a previous 8.1 release.

Check free Space in SYSTEM and Rollback Segment Tablepace

Upgrading to a new release requires more space in your SYSTEM tablespace and in the tablespaces where you store rollback segments. If you have enough space on your system, then consider adding more space to these tablespaces. In general, you need at least 20 MB of free space in your SYSTEM tablespace to upgrade. Check the free space with the following SQL statement

clear buffer
clear columns
clear breaks
set linesize 500
set pagesize 5000
column a1 heading 'Tablespace' format a15
column a2 heading 'Data File' format a45
column a3 heading 'Total|Space [MB]' format 99999.99
column a4 heading 'Free|Space [MB]' format 99999.99
column a5 heading 'Free|%' format 9999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
SELECT a.tablespace_name a1,
       a.file_name a2,
       a.avail a3,
       NVL(b.free,0) a4,
       NVL(ROUND(((free/avail)*100),2),0) a5
FROM (SELECT tablespace_name,
               SUBSTR(file_name,1,45) file_name,
               file_id,
               ROUND(SUM(bytes/(1024*1024)),3) avail
          FROM sys.dba_data_files
      GROUP BY tablespace_name,
               SUBSTR(file_name,1,45),
               file_id) a,
       (SELECT tablespace_name,
               file_id,
               ROUND(SUM(bytes/(1024*1024)),3) free
          FROM sys.dba_free_space
      GROUP BY tablespace_name, file_id) b
WHERE a.file_id = b.file_id (+)
ORDER BY 1, 2
/

Save your 8.1.6 Database

Determine the files that you will back up by issuing the following SQL statements:

SPOOL v8files.log;
SELECT member FROM v$logfile;
SELECT name FROM v$datafile;
SELECT value FROM v$parameter WHERE name = 'control_files';
SPOOL OFF;

Migration Steps

Shutdown your 8.1.x Database and Listener
(Use shutdown immediate not shutdown abort)

SVRMGR> SHUTDOWN IMMEDIATE
LSNRCTL> STOP <Listener-Name>

Adjust the initialization parameter file for use with the new release.

db_domain = .WORLD
optimizer_mode = choose
job_queue_processes = 0
aq_tm_processes = 0

Switch to the Oracle 8.1.7 Environment

Make sure that the following environment variables point to the new release 8.1 directories:

ORACLE_HOME

PATH

ORA_NLS

LD_LIBRARY_PATH

Start the Migration (this is the Main Task)

cd $ORACLE_HOME/rdbms/admin

As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get an error messge:

svrmgrl
connect internal
STARTUP RESTRICT

SPOOL revoke_restricted_session;
SELECT 'REVOKE restricted session FROM '
        || username || ';' FROM dba_users
        WHERE username NOT IN ('SYS','SYSTEM');
SPOOL OFF;
@revoke_restricted_session.log;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

SPOOL catoutu.log
SET ECHO ON
@u0801060.sql     # Script for 8.1.6 -> 8.1.7
SET ECHO OFF
SPOOL OFF

ALTER SYSTEM DISABLE RESTRICTED SESSION;
SHUTDOWN IMMEDIATE

The script you run creates and alters certain dictionary tables. It also runs the catalog.sql and catproc.sql scripts that come with the release to which you are upgrading, which create the system catalog views and all the necessary packages for using PL/SQL.

Important

Executing this clean shutdown flushes all caches, clears buffers, and performs other DBMS housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle8i database

If you retain the old Oracle software, then never start the upgraded database with the old software. Only start the database with the executables in the new release 8.1 installation directory.

Postmigration

You may execute additional scripts, e.g.

# Run all sql scripts for replication option
@$ORACLE_HOME/rdbms/admin/catrep.sql

# Collect I/O per table (actually object) statistics by
# statistical sampling
@$ORACLE_HOME/rdbms/admin/catio.sql

# This package creates a table into which references to
# the chained rows for an IOT (Index-Only-Table) can be
# placed using the ANALYZE command.
@$ORACLE_HOME/rdbms/admin/dbmsiotc.sql

# Wrap Package which creates IOTs (Index-Only-Table)
@$ORACLE_HOME/rdbms/admin/prvtiotc.plb

# This package allows you to display the sizes of objects
# in the shared pool, and mark them for keeping or
# unkeeping in order to reduce memory fragmentation.
@$ORACLE_HOME/rdbms/admin/dbmspool.sql

# Creates the default table for storing the output
# of the ANALYZE LIST CHAINED ROWS command
@$ORACLE_HOME/rdbms/admin/utlchain.sql

# Creates the EXCEPTION table
@$ORACLE_HOME/rdbms/admin/utlexcpt.sql

# Grant public access to all views used by TKPROF
# with verbose=y option
@$ORACLE_HOME/rdbms/admin/utltkprf.sql

# Create table PLAN_TABLE that is used by the EXPLAIN PLAN
# statement. The explain statement requires the presence
# of this table in order to store the descriptions of
# the row sources.
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

# Create performance tuning views
@$ORACLE_HOME/rdbms/admin/catperf.sql

# Create v7 style export/import views against the v8 RDBMS
# so that EXP/IMP v7 can be used to read out data in a
# v8 RDBMS. These views are necessary if you want to export
# from Oracle8 and import in an Oracle7 database.
@$ORACLE_HOME/rdbms/admin/catexp7.sql

# Create views of oracle locks
@$ORACLE_HOME/rdbms/admin/catblock.sql

# Print out the lock wait-for graph in a tree
# structured fashion
@$ORACLE_HOME/rdbms/admin/utllockt.sql

# Creates the default table for storing the output of the
# analyze validate command on a partitioned table
@$ORACLE_HOME/rdbms/admin/utlvalid.sql

# PL/SQL Package of utility routines for raw datatypes
@$ORACLE_HOME/rdbms/admin/utlraw.sql
@$ORACLE_HOME/rdbms/admin/prvtrawb.plb

# Contains the PL/SQL interface to the cryptographic toolkit
@$ORACLE_HOME/rdbms/admin/dbmsoctk.sql
@$ORACLE_HOME/rdbms/admin/prvtoctk.plb

# This package provides a built-in random number
# generator. It is faster than generators written in PL/SQL
# because it calls Oracle's internal random number
# generator.
@$ORACLE_HOME/rdbms/admin/dbmsrand.sql

# DBMS package specification for Oracle8 Large Object
# This package provides routines for operations on BLOB
# and CLOB datatypes.
@$ORACLE_HOME/rdbms/admin/dbmslob.sql

# Procedures for instrumenting database applications
# DBMS_APPLICATION_INFO package spec.
@$ORACLE_HOME/rdbms/admin/dbmsapin.sql

# Run obfuscation toolkit script.
@$ORACLE_HOME/rdbms/admin/catobtk.sql

# Create Heterogeneous Services data dictionary objects.
@$ORACLE_HOME/rdbms/admin/caths.sql

# Stored procedures for Oracle Trace server
@$ORACLE_HOME/rdbms/admin/otrcsvr.sql

# Oracle8i Profiler for PL/SQL
# Profilers are helpful tools to investigate programs and
# identify slow program parts and bottle necks. Furthermore
# you can determine which procedure, function or any other
# code part is executed how many times. To be able to use
# the DBMS_PROFILER package you have to install once for
# your database the following packages. Do this as user SYS
@$ORACLE_HOME/rdbms/admin/profload.sql
@$ORACLE_HOME/rdbms/admin/proftab.sql
@$ORACLE_HOME/rdbms/admin/dbmspbp.sql
@$ORACLE_HOME/rdbms/admin/prvtpbp.plb

The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, etc. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather than in the future

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Additional Checks after the Migration

A bad date constraint involves invalid date manipulation, which is a date manipulation that implicitly assumes the century in the date, causing problems at the year 2000. The utlconst.sql script runs through all of the check constraints in the database and marks constraints as bad if they include any invalid date manipulation. This script selects all the bad constraints at the end. Oracle7 let you create constraints with a two-digit year date constant. However, version 8 returns an error if the check constraint date constant does not include a four-digit year.

To run the utlconst.sql script, complete the following steps:

cd $ORACLE_HOME/rdbms/admin
svrmgrl

SVRMGR> CONNECT INTERNAL
SVRMGR> SPOOL utlresult.log
SVRMGR> @utlconst.sql
SVRMGR> SPOOL OFF

Server Output ON
Statement processed.
Statement processed.
Checking for bad date constraints
Finished checking -- All constraints OK!

After you run the script, the utlresult.log log file includes all the constraints that have invalid date constraints. The utlconst.sql script does not correct bad constraints, but instead it disables them. You should either drop the bad constraints or recreate them after you make the necessary changes.

During migration, some bitmap indexes may become unusable. To find these indexes, issue the following SQL statement:

SELECT index_name, index_type, table_owner, status
FROM dba_indexes
WHERE index_type = 'BITMAP'
AND status = 'UNUSABLE';

During upgrade, some function-based indexes may become unusable. To find these indexes, issue the following SQL statement:

SELECT owner, index_name, funcidx_status
FROM dba_indexes
WHERE funcidx_status = 'DISABLED';

The OUTLN user is created automatically during installation of Oracle8i. This user has DBA privileges. Use the ALTER USER statement to change the password for this user. Oracle8i adds the OUTLN user schema to support Plan Stability. The OUTLN user acts as a place to centrally manage metadata associated with stored outlines.