Oracle資料庫檔案遷移步驟

（1）首先確認需要遷移的檔案

SQL> select name from v$controlfile;

NAME

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

D:\ORACLE\ORADATA\XSFREE\CONTROL01.CTL

D:\ORACLE\ORADATA\XSFREE\CONTROL02.CTL

SQL> select name from v$datafile;

NAME

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

D:\ORACLE\ORADATA\XSFREE\SYSTEM01.DBF

D:\ORACLE\ORADATA\XSFREE\SYSAUX01.DBF

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

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

SQL> select member from v$logfile;

MEMBER

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

D:\ORACLE\ORADATA\XSFREE\REDO01.LOG

D:\ORACLE\ORADATA\XSFREE\REDO02.LOG

D:\ORACLE\ORADATA\XSFREE\REDO03.LOG

SQL> select name from v$tempfile;

NAME

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

D:\ORACLE\ORADATA\XSFREE\TEMP01.DBF

（2）建立pfile，spfile

SQL> create pfile from spfile;

move SPFILEXSFREE.ORA SPFILEXSFREE.ORA_bak

（3）關閉資料庫

SQL> shutdown immediate;

（4）遷移資料庫檔案

將D:\oracle\oradata下的檔案複製到D:\oracle\oradata_bak下（本測試是將oradata目錄下的資料庫檔案遷移至oradata_bak下）

（5）修改pfile中控制檔案路徑

*.control_files='D:\oracle\oradata_bak\xsfree\control01.ctl','D:\oracle\oradata_bak\xsfree\control02.ctl'

（6）rename檔案

SQL> startup mount pfile='D:\oracle\product\11.2.0\dbhome_1\database\INITxsfree.ORA';

SQL> alter database rename file 'D:\ORACLE\ORADATA\XSFREE\SYSTEM01.DBF' to 'D:\ORACLE\ORADATA_BAK\XSFREE\SYSTEM01.DBF';

SQL> alter database rename file 'D:\ORACLE\ORADATA\XSFREE\SYSAUX01.DBF' to 'D:\ORACLE\ORADATA_BAK\XSFREE\SYSAUX01.DBF';

SQL> alter database rename file 'D:\ORACLE\ORADATA\XSFREE\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA_BAK\XSFREE\UNDOTBS01.DBF';

SQL> alter database rename file 'D:\ORACLE\ORADATA\XSFREE\USERS01.DBF' to 'D:\ORACLE\ORADATA_BAK\XSFREE\USERS01.DBF';

SQL> alter database rename file 'D:\ORACLE\ORADATA\XSFREE\REDO01.LOG' to 'D:\ORACLE\ORADATA_BAK\XSFREE\REDO01.LOG';

SQL> alter database rename file 'D:\ORACLE\ORADATA\XSFREE\REDO02.LOG' to 'D:\ORACLE\ORADATA_BAK\XSFREE\REDO02.LOG';

SQL> alter database rename file 'D:\ORACLE\ORADATA\XSFREE\REDO03.LOG' to 'D:\ORACLE\ORADATA_BAK\XSFREE\REDO03.LOG';

SQL> alter database rename file 'D:\ORACLE\ORADATA\XSFREE\TEMP01.DBF' to 'D:\ORACLE\ORADATA_BAK\XSFREE\TEMP01.DBF';

（7）開啟資料庫

SQL> alter database open;

SQL> create spfile from pfile='D:\oracle\product\11.2.0\dbhome_1\database\INITxsfree.ORA';