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.
Y Oracle
7.3.4 installed under /home/app/oracle/product/7.3.4 (referred as ORAHOME7).
Y Oracle
8.1.5 installed under /home/app/oracle/product/8.1.5 (referred as ORAHOME8).
Y 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.
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
Follow
these steps for each database you need to convert on this server.
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.
Take a cold backup of
the database. This step is very important.
ü
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.
v
ORACLE_HOME
v
PATH
v
LD_LIBRARY_PATH
v
ORA_NLS
v
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.
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):
v
snapshot_refresh_interval << job_queue_interval
v
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 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.