Oracle数据文件迁移知多少？

本人以前整理的数据库文件迁移过程，希望能够对大家有所帮助

 

 

1、sqlplus "sys/sys@服务名 as sysdba"
 
2、修改控制文件:
 
    alter system set control_files='E:/oracle/oradata/myOracle_1/control01.ctl',
 'E:/oracle/oradata/myOracle_1/control02.ctl','E:/oracle/oradata/myOracle_1/control03.ctl'
  scope=spfile;
3、备份控制文件
 alter database backup controlfile to 'E:/oracle/oradata/testdb.ctl' reuse;
4、备份到跟踪文件, 方便重建控制文件
 alter database backup controlfile to trace;
6、查看存放路径
 show parameter user_dump_dest
 
6、数据文件拷贝到对应的目录:
  
7、到对应的ORACLE的数据目录 user_dump_des,找到对应的最新的TRACE文件，拷贝对应的数据出来：
 内容如下：
 1、重做日志文件可用的情况
 STARTUP NOMOUNT;
 
 CREATE CONTROLFILE REUSE DATABASE "MYORACLE" NORESETLOGS  NOARCHIVELOG
 --  SET STANDBY TO MAXIMIZE PERFORMANCE
     MAXLOGFILES 3
     MAXLOGMEMBERS 3
     MAXDATAFILES 20
     MAXINSTANCES 1
     MAXLOGHISTORY 226
 LOGFILE
   GROUP 1 'E:/ORACLE/ORADATA/MYORACLE_1/REDO01.LOG'  SIZE 30M,
   GROUP 2 'E:/ORACLE/ORADATA/MYORACLE_1/REDO02.LOG'  SIZE 30M,
   GROUP 3 'E:/ORACLE/ORADATA/MYORACLE_1/REDO03.LOG'  SIZE 30M
 -- STANDBY LOGFILE
 DATAFILE
   'E:/ORACLE/ORADATA/MYORACLE_1/SYSTEM01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/UNDOTBS01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/DRSYS01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/INDX01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/TOOLS01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/USERS01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/XDB01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/PERFSTAT.ORA'
 CHARACTER SET ZHS16GBK;
 
 alter database rename file 'E:/ORACLE/ORADATA/MYORACLE/system01.dbf' to 'E:/ORACLE/ORADATA/MYORACLE_1/system01.dbf';
 
 recover database until cancel using backup controlfile;
 
 
 ALTER DATABASE OPEN;
 
 2、重做日志文件不可用的情况
 
 STARTUP NOMOUNT;
 
 CREATE CONTROLFILE REUSE DATABASE "MYORACLE" RESETLOGS  NOARCHIVELOG
 --  SET STANDBY TO MAXIMIZE PERFORMANCE
     MAXLOGFILES 3
     MAXLOGMEMBERS 3
     MAXDATAFILES 20
     MAXINSTANCES 1
     MAXLOGHISTORY 226
 LOGFILE
   GROUP 1 'E:/ORACLE/ORADATA/MYORACLE_1/REDO01.LOG'  SIZE 30M,
   GROUP 2 'E:/ORACLE/ORADATA/MYORACLE_1/REDO02.LOG'  SIZE 30M,
   GROUP 3 'E:/ORACLE/ORADATA/MYORACLE_1/REDO03.LOG'  SIZE 30M
 -- STANDBY LOGFILE
 DATAFILE
   'E:/ORACLE/ORADATA/MYORACLE_1/SYSTEM01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/UNDOTBS01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/DRSYS01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/INDX01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/TOOLS01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/USERS01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/XDB01.DBF',
   'E:/ORACLE/ORADATA/MYORACLE_1/PERFSTAT.ORA'
 CHARACTER SET ZHS16GBK;
 
 
 recover database until cancel using backup controlfile;
 
 ALTER DATABASE OPEN RESETLOGS