数据库迁移（分享十一续集）

          上两个 章节主要讲的是如何使用工具去迁移数据。本章节重点介绍oracle特有的技术。

迁移技术：

                逻辑迁移：exp/imp   expdp/impdp   impdp+dblink

                物理迁移：rman、传递表空间、Dataguard、Goldengate、冷备迁移

下面一一介绍迁移步骤：

逻辑备份：

1.exp/imp\expdp/impdp(重点讲解expdp/impdp，这个导出导入效率高，适合异构平台，适合1T数据量以下迁移）

分为如下五个部分:

1. 源库环境确认

2. 目标端操作

3. 源库端导出数据

4. 目标端导入数据

5. 事后检查

1.源库环境确认

• 库的创建时间  select dbid,name,to_char(created,’yyyy-mm-dd hh24:mi:22’),log_mode from v$database;

• 查看数据库用户  select * from dba_users;

• 查看字符集  select userenv('language') from dual;

• 查看数据库角色  select * from dba_roles;

• 查看数据库表空间  select * from  dba_tablespaces;

• 查看物化视图  select * from dba_mviews

• 查看job及schedulerselect * from dba_jobs;select * from dba_scheduler_jobs;

• 查看dblink  select * from dba_db_links;

• 查看数据库大小

• 查看其他数据库有无关联    这里查看环境中其他数据库的dblink是否和该数据库有关联

2. 目标端操作(oracle 数据库已经安装）

1. 建立表空间 create tablespace test datafile’/u01/app/oracle/oradata/test/test.dbf’size 10G autoextend.             

   或者批量（在源端）select 'create tablespace '||a.tablespace_name||' datafile ''+DATA/capdb/datafile/'||b.tablespace_name||'01.dbf''  size 5g autoextend on;' from
   (select tablespace_name from  dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1') and contents not in ('TEMPORARY')) a,                 
   (select tablespace_name from  dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1') and contents not in ('TEMPORARY') ) b                  
   where a.tablespace_name=b.tablespace_name                                                                                                                  
   order by 1 ;

2. 建立用户和权限 create user test identified by test default tablespace test ;grant connect,resource to test;grant select on v_$session to test;grant unlimited tablespace to test;或者grant dba to test;

3. 建立dblink(只需建立public用户的) 

   create public database link MESASSY

   connect to username IDENTIFIED BY password

   using '(description=(address=(protocol=TCP)

   (host=192.168.0.12)(port=1521))(connect_data=(SERVICE_NAME = MESASSY)))';

检查字符集与原库一样即可

3. 源库端导出数据

• 查看数据库目录信息  select * from dba_directories;

• 建立新的目录用于存放导出文件  CREATE OR REPLACE DIRECTORY dump_dir AS '/oradata/dump';grant read , write on directory dump_dir  to system; 

• 导出用户数据  alter user test acccount lock;  expdp system/system directory=dump_dir dumpfile='||a.username||'01.dmp logfile='||b.username||'_exp_01.log schemas='||c.username|| ' cluster=N parallel=4                                                                                                                                                                                                                                                             批量脚本(根据环境修改)：

  select 'expdp system/system directory=dump dumpfile='||a.username||'01.dmp logfile='||b.username||'_exp_01.log schemas='||c.username|| ' cluster=N parallel=4'from
  (select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) a,
  (select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) b,
  (select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) c
  where  a.username=b.username and b.username=c.username ;

4. 目标端导入数据

• 查看数据库目录信息   select * from dba_directories

• 建立新的目录用于存放导出文件  CREATE OR REPLACE DIRECTORY dump_dir AS '/oradata/dump/';

• 拷贝expdp文件至该目录  scp expdp_emp_0* oracle@10.65.202.201:/oradata/dump/      

• 导入test/test用户数据

  select 'impdp system/system directory=dump dumpfile='||a.username||'01.dmp logfile='||b.username||'_imp_01.log schemas='||c.username|| ' cluster=N parallel=4'from
  (select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) a,
  (select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) b,
  (select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) c
  where  a.username=b.username and b.username=c.username;

5. 事后检查

• 检查对象是否迁移完成，检查对象

select 'SELECT count(*) FROM ALL_TABLES WHERE OWNER='''||username||''';' from dba_users
where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX');

select 'SELECT count(*) FROM ALL_views WHERE OWNER='''||username||''';' from dba_users
where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX');

select 'SELECT count(*) FROM ALL_indexs WHERE OWNER='''||username||''';' from dba_users
where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX');                                                                                       编译

 

• 检查无效的数据库对象：

SELECT owner, object_name, object_type,status 

FROM dba_objects 

WHERE status = 'INVALID';

@?/rdbms/admin/utlrp.sql

• 检查dblink  select * from dba_db_links;
• 检查job  select*  from dba_jobs;

  检查如下参数是否和源库一致或设置合理

sga 

pga 

PROCESSES 

SESSIONS 

案例：expdp 大小：约50G
expdp 数据时间为：1小时
scp dmp文件时间为：20分钟
impdp 数据时间为：1小时30分钟