投递文章投递文章 投稿指南 RSS订阅RSS订阅

Oracle数据逻辑迁移综合实战篇

来源:IT堂 堂友 发布时间:2015-06-17 收藏 投稿 字体:【

 如果你的需求只是简单的迁移少量表,可直接参考这两篇文章即可完成需求:

Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)

Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(下)

本次需求:
指定用户表结构迁移,所有表需要数据(因为此用户下的数据规模是10T的级别,所以想完全迁移不现实,最终确定为大表迁移部分数据,小表迁移全部数据)。
至于大表和小表的界定,研发侧不能提供,需要DBA自行评估划分。

最终确定迁移方案如下:

  1. 首先导出所有表结构
  2. 导出所有小表(单表预估占用空间小于等于1000M)的数据
  3. 导出大表(单表预估占用空间大于1000M)部分数据
  4. 逻辑迁移前的检查脚本

1. 首先导出所有表结构:

如果expdp 导出元数据长时间导不出来的话,可以考虑使用exp导出(rows=n)

导出示例:

数据泵导出元数据:

expdp system/oracle DIRECTORY=jy SCHEMAS=scott CONTENT=metadata_only EXCLUDE=statistics DUMPFILE=scott_metadata_only.dmp LOGFILE=scott_metadata_only.log VERSION=10.2.0.2

exp导出元数据:

nohup exp scott/tiger OWNER=scott ROWS=n BUFFER=10240000 STATISTICS=none RESUMABLE=y FILE=scott_metadata_exp.dmp LOG=scott_metadata_exp.log &

注意:
低版本->高版本迁移,不需要指定VERSION选项,
高版本->低版本迁移,需要指定VERSION选项。

导入示例:

数据泵导入元数据:

impdp system/oracle DIRECTORY=jy SCHEMAS=scott REMAP_SCHEMA=scott:scott_new REMAP_TABLESPACE=users:dbs_d_xxx TABLE_EXISTS_ACTION=replace DUMPFILE=scott_metadata_only.dmp LOGFILE=scott_metadata_only.log

exp导入元数据:

nohup imp scott/tiger BUFFER=1024000 RESUMABLE=y FILE=scott_metadata_exp.dmp LOG=imp_scott_metadata_exp.log IGNORE=y FULL=y &

2. 导出所有小表(单表预估占用空间小于等于1000M)的数据:

使用expdp并行导出, CONTENT=data_only

导出示例:

expdp scott/tiger PARFILE=scott_small_onlydata
配置文件scott_small_onlydata示例:

DIRECTORY=jy TABLES=EMP, DEPT CONTENT=data_only PARALLEL=16 DUMPFILE=scott_small_onlydata%U.dmp  LOGFILE=scott_small_onlydata.log

导入示例:

impdp scott/tiger DIRECTORY=jy CONTENT=data_only PARALLEL=16 DUMPFILE=scott_small_onlydata%U.dmp LOGFILE=impdp_scott_small_onlydata.log

3. 导出大表(单表预估占用空间大于1000M)部分数据:

3.1 统一导出大表当前月份的一个分区数据。

导出示例:

expdp scott/tiger PARFILE=scott_big_onlydata1

配置文件scott_big_onlydata1示例:

DIRECTORY=jy TABLES=EMP:P20150606, DEPT:P201507 CONTENT=data_only PARALLEL=8 DUMPFILE=scott_big_onlydata1_%U.dmp  LOGFILE=scott_big_onlydata1.log

导入示例:

impdp scott/tiger DIRECTORY=jy CONTENT=data_only PARALLEL=8 DUMPFILE=scott_big_onlydata1_%U.dmp LOGFILE=impdp_scott_big_onlydata1.log

3.2 对于未分区的大表,视表的具体情况选择性导出全部或部分数据。

导出示例:

expdp scott/tiger PARFILE=scott_big_onlydata2

配置文件scott_big_onlydata2示例:

DIRECTORY=jy TABLES=EMP, DEPT CONTENT=data_only QUERY=EMP:"where start_time>=to_date('2015-06-01','yyyy-mm-dd') and start_time<to_date('2015-06-08','yyyy-mm-dd')", DEPT:"where dept_no > 20" PARALLEL=8 DUMPFILE=scott_big_onlydata2_%U.dmp  LOGFILE=scott_big_onlydata2.log

导入示例:

impdp scott/tiger DIRECTORY=jy CONTENT=data_only PARALLEL=8 DUMPFILE=scott_big_onlydata2_%U.dmp LOGFILE=impdp_scott_big_onlydata2.log

4. 逻辑迁移前的检查脚本:

#!/bin/bash #Usage: check information before migrate data. #ex: sh checkinfo.sh username password #Author: AlfredZhao #Version: 1.0.0  sqlplus -s $1/$2 <<EOF > ./checkinfo_$1.log  prompt 0.数据库版本 prompt 0.DB Version select * from v$version;  prompt 1.确定当前连接用户下的表空间: # prompt 1.To examine all tablespace_name that the current user owned: # select tablespace_name from user_tables union select tablespace_name from user_tab_partitions union select tablespace_name from user_indexes union select tablespace_name from user_ind_partitions;  prompt 2.预估当前连接用户的数据量 # prompt 2.To estimate the sum(bytes/1024/1024/1024) "GB" of current user. select sum(bytes/1024/1024/1024) "GB" from user_segments;  --确认:用户下的预估数据量小于10G的用户,可以将表结构和数据一起导出,其他用户则先导出表结构,然后对数据量进行进一步的分析确定导出方案。  prompt 3.查询每个用户的每个对象占用空间情况 # prompt 3.To examine the space usage of every objects; set linesize 140 pagesize 100 col SEGMENT_NAME for a55  select /*+ rule */ segment_name, sum(bytes/1024/1024)"Size (MB)" from user_segments where segment_type in('TABLE','TABLE PARTITION') group by segment_name order by 2 desc;  prompt 查询大于1000M的表 prompt more 1000M table_name select /*+ rule */ segment_name, sum(bytes/1024/1024)"Size (MB)" from user_segments where segment_type in('TABLE','TABLE PARTITION') group by segment_name having sum(bytes/1024/1024) > 1000;  prompt 创建2个视图:v_more1000M, v_1000M prompt create view v_more1000M,v_1000M --create view create view v_more1000M as select /*+ rule */ segment_name, sum(bytes/1024/1024)"Size (MB)" from user_segments where segment_type in('TABLE','TABLE PARTITION') group by segment_name having sum(bytes/1024/1024) > 1000;  create view v_1000M as select /*+ rule */ segment_name, sum(bytes/1024/1024)"Size (MB)" from user_segments where segment_type in('TABLE','TABLE PARTITION') group by segment_name having sum(bytes/1024/1024) <= 1000;  prompt 查询大于1000M的表和小于1000M的表分别的预估总大小 prompt select total_size select sum("Size (MB)") from v_more1000M; select sum("Size (MB)") from v_1000M;  prompt 查询大于1000M和小于1000M的表的数量 prompt select count(1)  prompt 大于1000M的表数量 prompt more1000M select count(1) FROM v_more1000M;  prompt 小于1000M的表数量 prompt less or equal 1000M select count(1) FROM v_1000M;   prompt 4.查询大于1000M的表是否分区 # prompt 4.To judge whether more than 1000M table partition.  set linesize 140 pagesize 100 col SEGMENT_NAME for a55  prompt 查询大于1000M的未分区的表: prompt select the name of normal table which more than 1000M select /*+ rule */ segment_name, sum(bytes/1024/1024)"Size (MB)" from user_segments where segment_type in('TABLE','TABLE PARTITION') and partition_name is null group by segment_name having sum(bytes/1024/1024) > 1000;  --prompt 查询大于1000M分区的表分区个数: --select TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT from user_part_tables where table_name in (select segment_name from v_more1000M) order by 4;  prompt 查询分区表的分区类型DAY(P20150606),Hour(P2015060612),MONTH(P201507) prompt 此部分只适合分区名规范的情况 set pagesize 100 select a.table_name || ',' ||        (case length(a.partition_name)          when 9 then           'Day'          when 11 then           'Hour'          when 7 then           'MONTH'        end) tab_part_desc   from user_tab_partitions a,        (select table_name, max(partition_position) max_a           from user_tab_partitions b          group by b.table_name) c  where a.table_name = c.table_name    and a.partition_position = c.max_a    and a.table_name in (select segment_name from v_more1000M)    and a.table_name not like 'BIN%';  prompt 5.查询小表清单 prompt user_small_onlydata set pagesize 1500 select segment_name from v_1000M where segment_name not like 'BIN$%';  EOF
AlfredZhao©版权所有「从Oracle起航,领略精彩的IT技术。」
顶一下
(1)
100%
踩一下
(0)
0%
本文Tags:
  • 表情:
  •    
  • 评价:
用户名: 密码: 匿名 注册
最新评论 查看所有评论
About iTtang - 联系我们  - 专题列表 - 友情链接  -  高级搜索  -  帮助中心  -  您的意见