你的位置:首页 > 数据库

[数据库]数据库迁移记录(数据泵方式)


1.平台:windows迁移至linux(需要停止业务)

2.源库:
 导出用户下的对象(源库有两个业务schema)

expdp system/xxxx@sid schemas=xxxx dumpfile=xxxx_pic.dmp content=metadata_only logfile=exp.log (此模式仅导出空表和其他对象,因为表过大且表不是很重要,所以采用后续同步的方式)。expdp system/xxxx@sid schemas=xxxx dumpfile=xxxx_tb.dmp logfile=exp.log

3.新库:
   新建表空间:

CREATE TABLESPACE xxxx DATAFILE '/u01/app/oracle/oradata/xxx/datafile/xxx01.DBF' SIZE 20480M,'/u01/app/oracle/oradata/xxx/datafile/xxx02.DBF' SIZE 20480M,'/u01/app/oracle/oradata/xxx/datafile/xxx03.DBF' SIZE 20480MAUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED ;

4.新建用户并赋予权限(user1:xxx,user2:xxx_PIC)

-- Create the user create user xxx_PIC identified by xxxx default tablespace TSP_WEBAGENTtemporary tablespace TEMP  profile DEFAULT;  -- Grant/Revoke role privileges grant connect to xxx_PIC;  grant resource to Wxxx_PIC;grant create synonym to xxx_PIC;grant create view to xxx_PIC;grant unlimited tablespace to xxx_PIC;

5.导入数据库

impdp system/xxx@sid dumpfile=xxx_tb.DMP logfile=imp.log
impdp system/xxx@sid dumpfile=xxx_pic.DMP logfile=imp_pic.log

6.验证对象数量

SELECT owner, object_type, COUNT(*) count# FROM all_objects where owner='XXX'GROUP BY owner, object_type, statusorder by 2;

SELECT owner, object_type, COUNT(*) count# FROM all_objects where owner='XXX_OIC'GROUP BY owner, object_type, statusorder by 2;

5.验证表的行数(使用业务账户登录)

select 'analyze table '|| table_name||' compute statistics;' from user_tables;(执行结果语句)select table_name,num_rows from dba_tables where owner='XXX' order by 1;(XXX是业务账户)

6.更新那张特殊表(通过与源库建立DBLINK)(按照时间[月份]分批同步)

   a.先查看行数

select count(*) from XXX_check@piclink where statedate < to_date('2015-12-09 00:00:00','yyyy-MM-dd HH24:mi:ss')and statedate >= to_date('2015-11-01 00:00:00','yyyy-MM-dd HH24:mi:ss');

  b.输入数据

insert into shop_check 
select * from XXX_check@piclink

where statedate < to_date('2015-12-09 00:00:00','yyyy-MM-dd HH24:mi:ss')
and statedate >= to_date('2015-11-01 00:00:00','yyyy-MM-dd HH24:mi:ss');

 c.如果步骤a和步骤b提示的行数一致,则说明没有问题

commit;

   d.重复以上步骤,知道所有数据同步