If you want upgrade your oracle 8i database to 10g, you need make sure upgrade oracle 8i to 8.1.7 frist.
COMPATIBILITY MATRIX
===============================================================
+ Minimum Version of the database that can be directly upgraded to Oracle 10g
Release 2
8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X
9.2.0.4 or higher -> 10.2.X.X.X
10.1.0.2 or higher -> 10.2.X.X.X
+ The following database version will require an indirect upgrade path.
7.3.3 (or lower) -> 7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.0.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.1.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
這裡介紹一個工作中碰到比較複雜的情況作為例子。
老資料庫ORACLE817從HK傳過來的
目標資料庫是ODC的10.2.0.2,
兩者都是在solaris上的，這個過程中不僅需要從命名資料庫，而且檔案路徑不能再現。
其實這是一個遷移的任務。資料庫檔案有16G，全庫EXP檔案5G,因此我們選擇所以升級資料庫，而不是exp/imp.
方法一 先修改控制檔案中的檔案路徑，然後升級，再重新命名資料庫。
1. parpare 10.2.0.2 oracle software envriment
2. backup this 8i database and shutdown it and it's listner
3. transfer all datafile controlfile redolog to 10g
4. make a new init.ora, specially make sure blow point
a. compatible initalization parameter
b. SGA_AGGREGATE_TARGET >= 150m
C. PGA_AGGREGATE_TARGET >= 120m
D. add "*.undo_tablespace='UNDOTBS1'"
5. modify FULL path of all datafiles and redofiles, it run under 10g
run output sql create by blow sql which is run under in 8i database.
In 8i
SQL>spool adf.sql
select
'alter database rename file '''||df.file_name||''' to '||' ''/oradata/ssz0012dww/noracle/oradata/hkjpcd69/'||substr(df.file_name,instr(df.file_name,'/',-1)+1,100)||''';'
from dba_data_files df;
select
'alter database rename file '''||lf.MEMBER||''' to '||' ''/oradata/ssz0012dww/noracle/oradata/hkjpcd69/'||substr(lf.MEMBER,instr(lf.MEMBER,'/',-1)+1,100)||''';'
from v$logfile lf
spool off;
In 10g
SQL>@adf.sql
6. startup this database in upgrade mode using 10g software
SQL>alter database open upgrade
create SYSAUX and undotbs1 tablespace
CREATE TABLESPACE SYSAUX DATAFILE
'/oradata/ssz0012dww/noracle/oradata/hkjpcd69/_sysaux_4ng5bpdy_.dbf' SIZE 20M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SQL>CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE
'/oradata/ssz0012dww/noracle/oradata/hkjpcd69/_undo01.dbf' SIZE 200M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;
SQL>spool catelogupgrade.log
@catupgrd.sql
@utlrp.sql
shutdown immediate;
升級過程可能碰到一些錯誤，可以用 grep "^ORA-" catelogupgrade.log |sore|uniq 來歸類察看，我在升級過程中碰到的兩個問題，是注意temp和tools表空間是否充足
7. backup this database
8. create a sql script for recreate controlfile
SQL> alter database backup controlfile to trace;
shutdown immediate
9. modify the sql created by step 8 and save it as cc.sql
a.CREATE CONTROLFILE reuse DATABASE NORESETLOGS --> CREATE CONTROLFILE set DATABASE RESETLOGS
b.ALTER DATABASE OPEN; --> ALTER DATABASE OPEN resetlogs;
10. modify databasename in init.ora
11. rename this database by recreate controlfile
SQL>startup nomount
@cc.sql
shutdown immediate
startup
方法二 在熱備的方式下直接clone資料，從命名資料庫，直接升級。
1. hot backup old database
2. alter database backup controlfile to trace in old database
3. host cp all files to target folder
4. modify file from step 2 and save is as cc.sql, it process renameing database name and alter datafile path.
4. startup nomount newdatabase
5. @cc.sql
6. recover database
7. alter database open resetlogs upgrade
8. @catupgrd.sql and @ utlrp.sql
此種方法主要需要關注的在於 6 步 recover database，9i 和 10g 的archive log 結構上是相同的，所以可以跨版本apply, 8i-->10g的沒有測試過。
exp/imp 方式升級8i資料庫到10g的例子可以參考 itpub.net上的一篇文章
http://www.itpub.net/thread-1078953-1-1.html
Ref List
Oracle® Database Upgrade Guide 10g Release 2 (10.2)
"migration db to 10g" from metalink.oracle.com
感謝我的同事老萬在第二方式給於的建議和幫助。