Oracle-数据迁移四种方式

场景

为什么做数据迁移

• 原非核心业务与核心业务数据库使用同一TData实例，现将非核心业务拆分到其他实例，方便管理以及安全
• 受数据库存储空间限制，拆分数据库达到节省数据库空间的目的

软件及硬件环境

• 硬件环境

  腾讯云虚拟机 4C 8G

• 软件版本

  Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

数据迁移的方法

exp/imp逻辑备份与恢复(常规备份)

方法简介

它是oracle最常用最简单的方法，一般是基于应用的owner级做导出导入

优点是可以跨平台使用;
缺点是停机时间长，停机时间为从exp到网络传输到新库，再加上imp的时间。

实现流程

暂停业务，数据备份

• 为了保证数据的一致性，需对迁移库有操作的业务进行停机处理，根据业务的流量和迁移的时长一般在夜间处理

检查数据库是否还有连接的会话

SELECT S.USERNAME,
       S.SID,
       S.SERIAL#,
       S.INST_ID,
       S.EVENT,
       S.WAIT_CLASS,
       S.SQL_EXEC_START,
       S.LOGON_TIME,
       S.ACTION,
       SQ.SQL_TEXT
  FROM GV$SESSION S, GV$SQLAREA SQ
 WHERE S.STATUS = 'ACTIVE'
   AND S.USERNAME IS NOT NULL
   AND S.SQL_ID = SQ.SQL_ID;

如果长时间有会话未断开，可手动杀死会话

alter system kill session '27,33353';

27为sid 33353为SERIAL

对数据库用户加锁，防止迁移期间数据发生变化

对用户加锁
alter user SCOTT account lock;
查看用户状态
select * from dba_users;

对数据进行备份

• 在一台内网机器上进行数据备份和导入操作最为合适

exp/imp方式

• exp/imp 为客户端工具，即无需在数据库服务端进行操作

  导出操作
  exp uname/password@SName dumpfile=/path/uname.dmp logfile=/path/uname.log users=(uname) 
  导入操作
  imp uname/password@SName file=/path/uname.dmp full=y ignore=y

expdp/impdp方式

在备份目的路径建立备份文件夹 
例如：/DBbak

用sys用户在oracle中创建逻辑目录 
credit  directory oracleBak_dir as ‘/DBbak’;

查看数据库中的逻辑目录 
select * from dba_directories;

授权用户有对逻辑目录的读写权限 
grant read,write on directory oracleBak_dir to uname;

导出操作（导出用户）
expdp uname/passwd@sname  schemas=uname dumpfile=expdp.dmp directory=oracleBak_dir ;
导入操作（导入用户）
impdp uname/passwd@sname directory=oracleBak_dir dumpfile=expdp.dmp schemas=uname;

下面两张截图是使用了DBlink做的导出操作

创建新库，导入数据

新的数据库的准备和恢复

在新数据库上创建用户

• 创建用户需先知道数据文件的存放位置，可通过sql进行查看已有数据文件位置
• 知道了数据文件位置，创建表空间，配置自动扩展
• 创建用户 分配默认表空间和临时表空间
• 授权用户对表空间的权限，对表空间的使用量限制

  查询已有表空间数据位置
  SELECT  *  FROM  dba_data_files;
  创建表空间
  CREATE TABLESPACE CREDIT_DATA
           LOGGING
           DATAFILE '/app/oracle/oradata/bak/credit_data.bdf'
           SIZE 32M
           AUTOEXTEND ON
           NEXT 32M MAXSIZE UNLIMITED
           EXTENT MANAGEMENT LOCAL;
  
  
  创建用户 
  CREATE USER credit IDENTIFIED BY "credit"
  DEFAULT TABLESPACE CREDIT_DATA
  TEMPORARY TABLESPACE temp PROFILE DEFAULT;
  
  授权用户连接使用
  GRANT resource,connect  TO credit;
  
  授权用户对表空间使用量不限制
  ALTER USER  credit  QUOTA UNLIMITED  ON  CREDIT_DATA;

导入操作

impdp uname/passwd@sname directory=oracleBak_dir dumpfile=expdp.dmp schemas=uname;

导入过程

导入前没有表

导入后

删除原库

删除表空间，执行
drop tablespace CREDIT_DATA including contents and datafiles;

storage存储迁移

方法简介

这种情况下，数据文件、控制文件、日志文件、spfile都在存储上(一般情况下是裸设备)，我们可以直接把存储挂到新机器上，然后在新机器上启动数据库。

操作方法：将老库的pfile(因为里面有指向裸设备的spfile链接)，tnsnames.ora，listener.ora，密码文件传到新库的对应位置。将存储切至新机，或者用文件拷贝或dd的方式复制数据文件，启动数据库。

优缺点：优点是该迁移方式非常简单，主要的工作是主机工程师的工作，dba只需配合即可，停机时间为停库、切存储、起库的时间；缺点是要求新老库都是同一平台，是相同的数据库版本。

用rman做迁移

方法描述

rman比较适合于跨文件系统的迁移，如同平台下的不同文件系统。

利用dataguard迁移

方法描述

用dg我们不仅可以用来做容灾，物理的dg我们还可以作为迁移的方式。

优缺点：优点是停机时间短，停机时间为switch over的时间；缺点是主机必须双份、存储必须双份。

问题整理

如何将备份文件传输到别的机器？

常用的传输方式即可：scp rsync ftp等

导入的数据库对象已经存在

一般情况, 导入数据前应该彻底删除目标数据下的表, 序列, 函数/过程,触发器等; 数据库对象已经存在, 按缺省的imp参数, 则会导入失败
如果用了参数ignore=y, 会把exp文件内的数据内容导入 此时，
如果（目标）表【即被导入的表】有唯一关键字的约束条件, 不合条件将不被导入
如果（目标）表【即被导入的表】没有唯一关键字的约束条件, 将引起记录重复

数据库对象有主外键约束

不符合主外键约束时, 数据会导入失败
解决办法: 先导入主表, 再导入依存表
(或)disable目标导入对象的主外键约束, 导入数据后, 再enable它们

权限不够

如果要把A用户的数据导入B用户下, A用户需要有imp_full_database权限

导入大表( 大于80M ) 时, 存储分配失败

默认的EXP时, compress = Y, 也就是把所有的数据压缩在一个数据块上. 导入时, 如果不存在连续一个大数据块, 则会导入失败. 导出80M以上的大表时, (e x p )记得compress=N, 则不会引起这种错误.

imp和exp使用的字符集不同

如果字符集不同, 导入会失败, 可以改变unix环境变量或者NT注册表里NLS_LANG相关信息. 导入完成后再改回来.

imp和exp版本不能往上兼容

imp可以成功导入低版本exp生成的文件, 不能导入高版本exp生成的文件根据情况我们可以用$ imp username/password@connect_string说明: connect_string 是在/ORACLE_HOME/network/admin/tnsnames.ora 定义的本地或者远端数据库的名称注意事项:UNIX:/etc/hosts 要定义本地或者远端数据库服务器的主机名win98: windows\hosts 和IP地址的对应关系

EXP可以导出成多个DUMP文件

　早在Oracle 8i开始，FILE参数就可以容纳用逗号分开的多个文件路径。FILESIZE参数让你指定在转移到下一个文件之前，多少数据可以写到单个文件中。如果Export用光了FILE列表中的名字，它会提示额外的文件名。　　比如，让我们假设全部的数据库导出需要6G的空间，并且操作系统中单个文件大小的限制是2G。你要将导出文件放在/exp文件系统下的prod目录中。这个参数文件应该包含下面的内容：
FILE=/exp/prod/exp01.dmp,/exp/prod/exp02.dmp,/exp/prod/exp03.dmp FILESIZE=2G

job已经存在

ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

查询出未运行的任务后，drop即可

select 'drop table ' || owner_name || '.' || job_name || ';'
from dba_datapump_jobs
where state = 'NOT RUNNING'

资料相关连接

数据库迁移的几种方式(Oracle版)
oracle官方文档

oracle的imp/exp导出导入