Oracle数据文件位置重建迁移

操作系统：Windows Xp Professional

Oracle9i Enterprise Edition Release 9.2.0.1.0

 

下为非归档模式的迁移重建过程：

 

一、准备工作：

（1）  做原有数据库的冷备份（数据文件、重做日志文件、控制文件）文件，

原安装路径位于：E:/oracle/oradata/yborcl/

（2）   安装好目标电脑的操作系统，ORACLE数据库（版本与原数据库一样），创建一个新的数据库，安装路径到：D:/oracle/oradata/yborcl/

 

二、操作步骤： 

（1） 查看数据文件、控制文件的状态

D:>sqlplus "/as sysdba"

 

SQL> select name from v$datafile;

 

NAME

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

E:/ORACLE/ORADATA/YBORCL/SYSTEM01.DBF

E:/ORACLE/ORADATA/YBORCL/UNDOTBS01.DBF

E:/ORACLE/ORADATA/YBORCL/DRSYS01.DBF

E:/ORACLE/ORADATA/YBORCL/EXAMPLE01.DBF

E:/ORACLE/ORADATA/YBORCL/INDX01.DBF

E:/ORACLE/ORADATA/YBORCL/TOOLS01.DBF

E:/ORACLE/ORADATA/YBORCL/USERS01.DBF

E:/ORACLE/ORADATA/YBORCL/XDB01.DBF

E:/ORACLE/ORADATA/YBORCL/APACCT.DBF

E:/ORACLE/ORADATA/YBORCL/APBLDBANK.DBF

E:/ORACLE/ORADATA/YBORCL/APCOMM.DBF

E:/ORACLE/ORADATA/YBORCL/APECONSTAT.DBF

E:/ORACLE/ORADATA/YBORCL/APEQUIPMENT.DBF

E:/ORACLE/ORADATA/YBORCL/APEXAM.DBF

E:/ORACLE/ORADATA/YBORCL/APINFORQY.DBF

E:/ORACLE/ORADATA/YBORCL/APINPADM.DBF

E:/ORACLE/ORADATA/YBORCL/APINPBILL.DBF

E:/ORACLE/ORADATA/YBORCL/APINSURANCE.DBF

E:/ORACLE/ORADATA/YBORCL/APLAB.DBF

E:/ORACLE/ORADATA/YBORCL/APMEDADM.DBF

E:/ORACLE/ORADATA/YBORCL/MHIS.DBF

E:/ORACLE/ORADATA/YBORCL/APMEDREC.DBF

E:/ORACLE/ORADATA/YBORCL/APORDADM.DBF

E:/ORACLE/ORADATA/YBORCL/APOUTPADM.DBF

E:/ORACLE/ORADATA/YBORCL/APOUTPBILL.DBF

E:/ORACLE/ORADATA/YBORCL/APPHARMACY.DBF

E:/ORACLE/ORADATA/YBORCL/APSURGERY.DBF

E:/ORACLE/ORADATA/YBORCL/APTEMP.DBF

E:/ORACLE/ORADATA/YBORCL/APBACK.DBF

E:/ORACLE/ORADATA/YBORCL/APINTERFACE.DBF

E:/ORACLE/ORADATA/YBORCL/PERFSTAT.DBF

 

31 rows selected

 

SQL> select name from v$controlfile;

 

NAME

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

D:/ORACLE/ORADATA/YBORCL/CONTROL01.CTL

D:/ORACLE/ORADATA/YBORCL/CONTROL02.CTL

D:/ORACLE/ORADATA/YBORCL/CONTROL03.CTL

 

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER

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

         1  STALE   ONLINE  E:/ORACLE/ORADATA/YBORCL/REDO01.LOG

         2          ONLINE  E:/ORACLE/ORADATA/YBORCL/REDO02.LOG

         3          ONLINE  E:/ORACLE/ORADATA/YBORCL/REDO03.LOG

 

（2） 生成路径更改语句

SQL>

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/SYSTEM01.DBF' to 'D:/ORACLE/ORADATA/YBORCL/SYSTEM01.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/UNDOTBS01.DBF' to 'D:/ORACLE/ORADATA/YBORCL/UNDOTBS01.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/DRSYS01.DBF' to 'D:/ORACLE/ORADATA/YBORCL/DRSYS01.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/EXAMPLE01.DBF' to 'D:/ORACLE/ORADATA/YBORCL/EXAMPLE01.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/INDX01.DBF' to 'D:/ORACLE/ORADATA/YBORCL/INDX01.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/TOOLS01.DBF' to 'D:/ORACLE/ORADATA/YBORCL/TOOLS01.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/USERS01.DBF' to 'D:/ORACLE/ORADATA/YBORCL/USERS01.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/XDB01.DBF' to 'D:/ORACLE/ORADATA/YBORCL/XDB01.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APACCT.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APACCT.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APBLDBANK.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APBLDBANK.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APCOMM.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APCOMM.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APECONSTAT.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APECONSTAT.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APEQUIPMENT.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APEQUIPMENT.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APEXAM.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APEXAM.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APINFORQY.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APINFORQY.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APINPADM.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APINPADM.DBF' ;

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APINPBILL.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APINPBILL.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APINSURANCE.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APINSURANCE.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APLAB.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APLAB.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APMEDADM.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APMEDADM.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/MHIS.DBF' to 'D:/ORACLE/ORADATA/YBORCL/MHIS.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APMEDREC.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APMEDREC.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APORDADM.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APORDADM.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APOUTPADM.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APOUTPADM.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APOUTPBILL.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APOUTPBILL.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APPHARMACY.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APPHARMACY.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APSURGERY.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APSURGERY.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APBACK.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APBACK.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/APINTERFACE.DBF' to 'D:/ORACLE/ORADATA/YBORCL/APINTERFACE.DBF';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/PERFSTAT.DBF' to 'D:/ORACLE/ORADATA/YBORCL/PERFSTAT.DBF' ;

 

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/REDO01.LOG' to 'D:/ORACLE/ORADATA/YBORCL/REDO01.LOG';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/REDO02.LOG' to 'D:/ORACLE/ORADATA/YBORCL/REDO02.LOG';

alter database rename file 'E:/ORACLE/ORADATA/YBORCL/REDO03.LOG' to 'D:/ORACLE/ORADATA/YBORCL/REDO03.LOG';

 

（3）临时表空间的处理

SQL> select name from v$tempfile;

NAME

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

E:/ORACLE/ORADATA/YBORCL/TEMP01.DBF

 

SQL> alter database tempfile 'E:/ORACLE/ORADATA/YBORCL/TEMP01.DBF' drop;

Database altered

SQL> alter tablespace temp add tempfile 'D:/ORACLE/ORADATA/YBORCL/TEMP01.DBF' SIZE 100m reuse;

Tablespace altered

SQL> select name from v$tempfile;

NAME

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

D:/ORACLE/ORADATA/YBORCL/TEMP01.DBF

 

（4）SYS口令文件的修改

请删掉或改名初始的口令文件C:/oracle/ora92/database/PWDyborcl.ora

生成新的口令文件

C:/oracle/ora92/database>orapwd file=pwdyborcl.ora password=oracle entries=2

 

启动数据库startup