您的位置: 首页  >  IT文章  >  数据库迁移（分享十一续集）

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

分类: IT文章 • 2022-05-11 20:04:17

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

迁移技术：

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

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

下面一一介绍迁移步骤：

逻辑备份：

1.exp/impexpdp/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分钟

相关推荐