ZT 升级和移植Oracle数据库方法若干-优快云博客

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 --&gt CREATE CONTROLFILE set DATABASE RESETLOGS
b.ALTER DATABASE OPEN; --&gt 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--&gt10g的没有测试过。

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)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/toc.htm

"migration db to 10g" from metalink.oracle.com
Note:263809.1

感谢我的同事老万在第二方式给于的建议和帮助。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/82387/viewspace-1016632/，如需转载，请注明出处，否则将追究法律责任。