代码改变世界

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

2015-06-17 20:17  AlfredZhao  阅读(...)  评论(...编辑  收藏

本文适合迁移大量表和数据的复杂需求。

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

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

imp导入元数据:

nohup imp scott/tiger BUFFER=10240000 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 < ./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$%';

prompt 删除过程中创建的2个视图:v_more1000M, v_1000M
prompt drop view v_more1000M,v_1000M
--drop view
drop view v_more1000M;

drop view v_1000M;

EOF