Oracle数据库的转移与升级

答案：
公司最近要上shop floor系统,需将数据库从东莞转移过来,以下就是转移的经过…

东莞系统环境:

OS:Windows 2000 Advanced Server+SP4

Oracle 9i standard Edition Version: 9.0.1.1.1

吴江系统环境:

  HP ML570 CPU:Intel Xeon MP CPU 3.0G RAM:2GB

  OS:Windows 2000 Advanced Server+SP4

  Oracle 9i Standard Editon Version: 9.2.0.1.0.

步骤:

1.        安装Oracle 9i,选择只安装Software,不创建数据库,安装过程略.

2.        创建相关目录:d:\oracle\admin\wjsfms\bdump

d:\oracle\admin\wjsfms\cdump

                           d:\oracle\admin\wjsfms\udump

d:\oracle\admin\wjsfms\create

d:\oracle\admin\wjsfms\pfile

3.        Copy数据文件到E:\Oradata\WJSFMS目录下,临时文件可不COPY,文件清单如下:

            SYSTEM01.DBF NDOTBS01.DBF CWMLITE01.DBF   DRSYS01.DBF EXAMPLE01.DBF INDX01.DBF TOOLS01.DBF 
           USERS01.DBF    CSFIS01.ORA  SFIS01.ORA   RSFIS02.ORA  HSFIS01.ORACINDX01.ORA  RINDX01.ORA  
          RINDX02.ORA  HINDX01.ORA   UNDOTBS2.ORA

4.        COPY控制文件G:\ControlFile\WJSFMS,H:\ControlFile\WJSFMS,I:\ControlFile\WJSFMS

5.        COPY Redo文件到G:\RedoLog\WJSFMS,H:\ RedoLog \WJSFMS,I:\ RedoLog \WJSFMS

6.        新建实例,此SID必须跟原来的SID一样…

C:\Oradim –NEW –SID WJSFMS –STARTMODE m

7.        创建密码文件

C:\orapwd file=d:\oracle\ora92\database\pwdwjsfms.ora password=password entries=5

8.        修改初始参数文件INITwjsfms.ora,如没有参数文件,则可用oracle自带的参数文件进行修改,红色部分为修改部分:

…….

control_files=("G:\ControlFile\WJSFMS\CONTROL01.CTL", "H:\ControlFile\WJSFMS\CONTROL02.CTL", "I:\ControlFile\WJSFMS\CONTROL03.CTL")

……………………..

background_dump_dest=d:\oracle\admin\WJSFMS\bdump

core_dump_dest=d:\oracle\admin\WJSFMS\cdump

timed_statistics=TRUE

user_dump_dest=d:\oracle\admin\WJSFMS\udump

……………………….

log_archive_dest=f:\oracle\wjsfms\archivelog

 

如果是根据oracle自带的参数文件进行修改的,则还需修改db_name,instance_name

9.        激活oracle

c: qlplus /nolog

sql>conn / as sysdba

sql>startup pfile=d:\oracle\ora92\database\initwjsfms.ora

此时提示如下信息(由于升级时没有将相关信息spool出来,故只能将alert文件中的信息show出来作为参考):

ORA-00218: block size 4096 of controlfile does not match DB_BLOCK_SIZE (8192)

ORA-00202: controlfile: 'G:\ControlFile\WJSFMS\CONTROL01.CTL'

出现此错误的原因是原来的database的db_block_size跟现在的初始化参数设的不一样,将初始化参数中的db_block_size=8192改成4096即可

sql>shutdown immediate

sql> startup pfile=d:\oracle\ora92\database\initwjsfms.ora 

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'D:\ORACLE\ORADATA\WJSFMS YSTEM01.DBF'

ORA-27041: unable to open file

Sql> select a.name from v$datafile a,v$recover_file b where a.file#=b.file#;

NAME

--------------------------------------------------------------------------------

D:\ORACLE\ORADATA\WJSFMS YSTEM01.DBF 

D:\ORACLE\ORADATA\WJSFMS\UNDOTBS01.DBF                                      

D:\ORACLE\ORADATA\WJSFMS\CWMLITE01.DBF                                         

D:\ORACLE\ORADATA\WJSFMS\DRSYS01.DBF                                             

D:\ORACLE\ORADATA\WJSFMS\EXAMPLE01.DBF                                          

D:\ORACLE\ORADATA\WJSFMS\INDX01.DBF                                             

D:\ORACLE\ORADATA\WJSFMS\TOOLS01.DBF                                            

D:\ORACLE\ORADATA\WJSFMS\USERS01.DBF                                             

D:\ORACLE\ORADATA\WJSFMS\CSFIS01.ORA                                             

D:\ORACLE\ORADATA\WJSFMS\RSFIS01.ORA                                            

D:\ORACLE\ORADATA\WJSFMS\RSFIS02.ORA                                           

D:\ORACLE\ORADATA\WJSFMS\HSFIS01.ORA                                            

D:\ORACLE\ORADATA\WJSFMS\CINDX01.ORA                                            

D:\ORACLE\ORADATA\WJSFMS\RINDX01.ORA                                            

D:\ORACLE\ORADATA\WJSFMS\RINDX02.ORA              &nbs

上一个：Install oracle client on Solaris
下一个：在Oracle中如何利用Rowid查找和删除表中的重复记录