oracle 遷移,ORACLE 数据迁移

步骤：

备份数据库

backup database

1、use rman backup database

rman target /

rman>backup as compressed backupset format 'f:\backup\full_%d_U' database;

迁移用户存放的数据文件

1、tablespace offline

sql>alter tablespace USERS offline normal;

sql>alter tablespace BSCHIS_DATA offline normal;

sql>alter tablespace MPI offline normal;

sql>alter tablespace BSHIS offline normal;

sql>alter tablespace PHIS offline normal;

sql>alter tablespace BSLIS offline normal;

sql>alter tablespace PFLIS offline normal;

sql>alter tablespace PHIS23 offline normal;

sql>alter tablespace HISTEST offline normal;

sql>alter tablespace LIS offline normal;

2、CP datafile to new destination

3、alter tablespace rename datafile

sql>alter tablespace USERS rename datafile 'N:\ORADATA\ORCL\USERS01.DBF' to 'E:\oradata\USERS01.DBF' ;

sql>alter tablespace BSCHIS_DATA rename datafile 'N:\ORADATA\ORCL\BSCHIS_DATA.DBF' to 'E:\oradata\BSCHIS_DATA.DBF' ;

sql>alter tablespace MPI rename datafile 'N:\ORADATA\ORCL\MPI.DBF' to 'E:\oradata\MPI.DBF' ;

sql>alter tablespace BSHIS rename datafile 'N:\ORADATA\ORCL\BSHIS.DBF' to 'E:\oradata\BSHIS.DBF' ;

sql>alter tablespace PHIS rename datafile 'N:\ORADATA\ORCL\PHIS.DBF' to 'E:\oradata\PHIS.DBF' ;

sql>alter tablespace BSLIS rename datafile 'N:\ORADATA\ORCL\BSLIS.DBF' to 'E:\oradata\BSLIS.DBF' ;

sql>alter tablespace PFLIS rename datafile 'N:\ORADATA\ORCL\PFLIS.DBF' to 'E:\oradata\PFLIS.DBF' ;

sql>alter tablespace PHIS23 rename datafile 'N:\ORADATA\ORCL\PHIS23.DBF' to 'E:\oradata\PHIS23.DBF' ;

sql>alter tablespace HISTEST rename datafile 'N:\ORADATA\ORCL\HISTEST.DBF' to 'E:\oradata\HISTEST.DBF' ;

sql>alter tablespace LIS rename datafile 'N:\ORADATA\ORCL\LIS.DBF' to 'E:\oradata\LIS.DBF' ;

4、alter tablespace online

sql>alter tablespace USERS online;

sql>alter tablespace BSCHIS_DATA online;

sql>alter tablespace MPI online;

sql>alter tablespace BSHIS online;

sql>alter tablespace PHIS online;

sql>alter tablespace BSLIS online;

sql>alter tablespace PFLIS online;

sql>alter tablespace PHIS23 online;

sql>alter tablespace HISTEST online;

sql>alter tablespace LIS online;

迁移数据库redolog 文件

1、add loggroup

sql>alter database add logfile group 4('E:\oradata\redo04.log') size 50M;

sql>alter database add logfile member 'E:\oradata\redo004.log' to group 4;

sql>alter database add logfile group 5('E:\oradata\redo05.log') size 50M;

sql>alter database add logfile member 'E:\oradata\redo005.log' to group 5;

sql>alter database add logfile group 6('E:\oradata\redo06.log') size 50M;

sql>alter database add logfile member 'E:\oradata\redo006.log' to group 6;

2、drop old loggroup file

sql>alter database drop logfile group 1;

sql>alter database drop logfile group 2;

sql>alter database drop logfile group 3;

3、sql>alter system switch logfile;

注意删除旧的loggroup 需要确定 loggroup 为inactive 状态。

select status ,group# from v$log 确定。

迁移temp tablespace 临时表空间文件

1、create a new temp tablespace

sql>create temporary tablespace temp02 tempfile 'e:\oradata\temp02.dbf' size 512M autoextend on next 500m MAXSIZE UNLIMITED;

2、set default temp tablespace

sql>alter database default temporary tablespace temp02;

3、drop old temp tablespace

sql>drop tablespace temp including contents and datafiles;

数据库undotablespace 文件

sql> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'e:\oradata\undotbs02.dbf' SIZE 5120M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ONLINE RETENTION NOGUARANTEE BLOCKSIZE 8K FLASHBACK ON;

sql>alter system set undo_tablespace ='UNDOTBS2';

sql>ALTER SYSTEM SWITCH LOGFILE;

sql>drop tablespace undotbs1 including contents;

迁移数据库的control 文件

change control file location

1、sql>create pfile from spfile

2、shutdown database

sql>shutdown immediate

3、edit pfile change location

cp old control file to new destination

4、startup database

sql>startup pfile='xxx.ora';

5、create new spfile

sql>create spfile from pfile='xxx.ora'

6、shutdown database

sql>shutdown immediate

7、startup database

sql>startup

备份数据库

backup database

1、use rman backup database

rman target /

rman>backup as compressed backupset format 'f:\backup\20140729full2221_%d_U' database;

结束

在原来数据文件目录(N:\oradata\orcl\) 下有system.dbf 和sysaux.dbf 文件，为数据库本身系统文件 是数据库存放数据字典的文件，不建议改动，未做迁移。