ITPub博客

首页 > 数据库 > Oracle > ORACLE 数据迁移

ORACLE 数据迁移

Oracle 作者:dbasdk 时间:2014-07-30 16:31:22 0 删除 编辑
步骤:
备份数据库
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 文件,为数据库本身系统文件 是数据库存放数据字典的文件,不建议改动,未做迁移。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29734436/viewspace-1242572/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-07-02

  • 博文量
    490
  • 访问量
    1193545