1716.生产环境中如何对大对象进行分片expdp\impdp
本人医疗行业DBA，精通oracle底层；会文件、触发器加密的勒索恢复、asm磁盘组不能mount及各种灾难恢复，后面将利用空闲时间将自己工作中遇到的问题及整理的资料分享给更多愿意学习和提升自己的dba。作者微信号：Oray_123
1、介绍
在做迁移、搭建测试库以及其他需要导出、导入数据的需求下，我们常用到数据泵来进行数据的转移操作，但往往很多事后我们要操作的库数据量都非常大，且数据库中大对象字段非常多，就给我们导出带来了一些问题，如报错ora-01555，导出慢或者卡在那里很久不动等问题；
有小伙伴可能会说你开并行不就行了吗？事实上当你数据库中大对象数据量占用较大时候，你会发现开并行没有任何用，那么怎么解决这个问题呢？我的思路是先排除大对象的表；然后再通过rowid切片多个进程导出导入。
下面是一个示例：
–查看大对象，先排除导出，然后再单独导出
SELECT owner,tablespace_name,case SEGMENT_type
when ‘LOBSEGMENT’ then
(select table_name || ‘.’ || column_name
from dba_lobs t
where t.segment_name = s.segment_name)
else
SEGMENT_NAME
end as SEGMENT_NAME,
BYTES / 1024 / 1024 MB,
BYTES / 1024 / 1024 / 1024 GB
FROM DBA_SEGMENTS s
–WHERE TABLESPACE_NAME = upper(‘ZL_ZLEMR’)
Order By 4 Desc,3 asc;
–通过查询发现 【 检验报告图像.图形 】这个字段占用了400g的空间，我需要先进行排除
2、第一步导出主体数据：
–排除系统用户
select username,account_status,lock_date,default_tablespace,created from dba_users order by created;
–创建数据泵目录
create directory dump as ‘E:\dump’;
grant read,write on directory dump to system;
exit;
–生成符合expdp的排除用户的脚本
declare
s1 varchar2(2000);
cursor c is select username from dba_users where trunc(created) =to_date(‘2013/10/9’,‘yyyy/mm/dd’);
begin
for j in c loop
s1 :=s1||’’||’’’’||j.username||’’||’’’’||’,’;
–s1 :=s1||’’’’||j.username||’’’’||’,’;
end loop;
dbms_output.put_line(s1);
end;
–将上面的脚本复制进来
expdp "/ as sysdba " dumpfile=his.dmp logfile=his.log directory=dump full=y EXCLUDE=audit,TABLE:“IN(‘检验报告图像’)” EXCLUDE=SCHEMA:“in(‘SYSTEM’,‘SYS’,‘OLAPSYS’,‘SI_INFORMTN_SCHEMA’,‘MGMT_VIEW’,‘ORDPLUGINS’,‘ORACLE_OCM’,‘XDB’,‘SYSMAN’,‘WMSYS’,‘SCOTT’,‘DBSNMP’,‘DMSYS’,‘DIP’,‘OUTLN’,‘EXFSYS’,‘ANONYMOUS’,‘CTXSYS’,‘ORDSYS’,‘MDSYS’,‘MDDATA’)”
3、rowid切片导出数据的原理是什么？
我们先来看rowid是怎么切片的，才知道具体我们应该如何来控制并行；
SQL> select distinct mod(dbms_rowid.rowid_block_number(rowid),10) from zlhis.检验报告图像 a order by 1;
MOD(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),10)
0
1
2
3
4
5
6
7
8
9
已选择 10 行。
SQL> select mod(dbms_rowid.rowid_block_number(rowid),3),count(*) from zlhis.检验报告图像 a group by mod(dbms_rowid.rowid_block_number(rowid),3);
MOD(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),3) COUNT(*)
1 2983
2 2936
0 2920
–通过上面结果我们可以看到，rowid分配就是通过mod(v1,v2)来进行的，其中v2参数就是控制并行的参数值，可以理解为数据泵你parallel=v2
4、正式导出
在本步骤中我改写了网上的一些发的脚本，因为在实际实验过程中和生产中我发现他们都存在一些问题，比如数据泵还没开始产生进程下一个就开始了，导致一些报错和数据没导出的情况；
1）linux
#!/bin/bash
chunk=9
for ((i=0;i<=8;i++));
do
expdp zlhis/9d22bbb TABLES=检验图像结果 QUERY=检验图像结果:"where mod(dbms_rowid.rowid_block_number(rowid), ${chunk}) = {i}\" directory=DMP dumpfile=TX_{i}.DMP logfile=TX_${i}.log &
echo $i
sleep 10
done
2）windows
@echo off
set connStr=zlhis/9d22bbb
set chunk=10
set oracle_sid=orcl
set oracle_home_path=d:\oracle\product\10.2.0\db_1\bin\
rem 格式：FOR /L %variable IN (start,step,end) DO command [command-parameters]
for /l %%a in (0,1,9) do (
start /b %oracle_home_path%expdp %connStr% TABLES=检验图像结果 QUERY=检验图像结果:“where mod(dbms_rowid.rowid_block_number(rowid),%chunk%) = %%a” directory=expdpdump dumpfile=TX_%%a.DMP logfile=TX_%%a.log
timeout 10 >nul 2>nul
)
3）手工方式
—或者手动导出
–如下，打开三个窗口分别粘贴执行，如果要开更多的进程，需要更改3后后面的0~2
SQL> create or replace directory dmp as ‘D:\dump’;
SQL> grant read,write on directory dmp to public;
expdp zldoc/zlsoft tables=BZ_DOC_LOG QUERY=BZ_DOC_LOG:“where mod(dbms_rowid.rowid_block_number(rowid),3) = 0” directory=DMP dumpfile=bz_doc_log_0.dmp logfile=bz_doc_log_0.log
expdp zldoc/zlsoft tables=BZ_DOC_LOG QUERY=BZ_DOC_LOG:“where mod(dbms_rowid.rowid_block_number(rowid),3) = 1” directory=DMP dumpfile=bz_doc_log_1.dmp logfile=bz_doc_log_1.log
expdp zldoc/zlsoft tables=BZ_DOC_LOG QUERY=BZ_DOC_LOG:“where mod(dbms_rowid.rowid_block_number(rowid),3) = 2” directory=DMP dumpfile=bz_doc_log_2.dmp logfile=bz_doc_log_2.log
5、正式导入
1）linux
–脚本导入
#!/bin/bash
for ((i=0;i<=8;i++));
do
#echo dumpfile=TX_{i}.DMP
impdp \"/ as sysdba\" directory=impdpdump dumpfile=TX_{i}.DMP logfile=impdp_TX_${i}.log DATA_OPTIONS=DISABLE_APPEND_HINT TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y CONTENT=DATA_ONLY &
sleep 10
done
2）windows
@echo off
set connStr=zlhis/9d22bbb
set oracle_sid=orcl
set oracle_home_path=d:\oracle\product\10.2.0\db_1\bin\
rem 格式：FOR /L %variable IN (start,step,end) DO command [command-parameters]
for /l %%a in (0,1,9) do (
rem 11g upper >start /b %oracle_home_path%impdp %connStr% directory=impdpdump dumpfile=TX_%%a.DMP logfile=imp_TX_%%a.log DATA_OPTIONS=DISABLE_APPEND_HINT TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y CONTENT=DATA_ONLY
start /b %oracle_home_path%impdp %connStr% directory=impdpdump dumpfile=TX_%%a.DMP logfile=imp_TX_%%a.log CONTENT=DATA_ONLY
timeout 10 >nul 2>nul
)
3）手工方式
—或者手动导入，更安全
truncate table zldoc.bz_doc_log;
impdp “/ as sysdba” directory=dmp dumpfile=bz_doc_log_0.dmp DATA_OPTIONS=DISABLE_APPEND_HINT CONTENT=DATA_ONLY
impdp “/ as sysdba” directory=dmp dumpfile=bz_doc_log_1.dmp DATA_OPTIONS=DISABLE_APPEND_HINT CONTENT=DATA_ONLY
impdp “/ as sysdba” directory=dmp dumpfile=bz_doc_log_2.dmp DATA_OPTIONS=DISABLE_APPEND_HINT CONTENT=DATA_ONLY
6、那么我们导出过程中如果遇到了业务卡顿或者我想停止怎么办呢？
select s.inst_id,to_char(sysdate,‘YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid, ‘orakill orcl2 ‘||spid,‘kill -9 ‘||spid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid ,‘ALTER SYSTEM KILL SESSION ‘||’’’’||s.SID||’,’||s.SERIAL#||’,@’||s.inst_id||’’’’||’ IMMEDIATE;’ killse
from gvprocess p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr and s.inst_id=p.inst_id and s.inst_id=d.inst_id;
select ‘orakill orcl ‘||spid ,‘ALTER SYSTEM KILL SESSION ‘||’’’’||t1.SID||’,’||t1.SERIAL#||’’’’||’ IMMEDIATE;’ killse
–,'kill -9 '||spid
from sys.gV_session t1,dba_datapump_sessions d
where t.addr=t1.paddr and t1.saddr=d.saddr
and t1.Type=‘USER’ and t1.username is not null;
select ‘drop table ’ || owner_name || ‘.’ || job_name || ’ purge;’ from dba_datapump_jobs where state = ‘NOT RUNNING’ ;
