datapump导出数据库准备以及注意事项

--------------------------------------------前期准备工作

–1.在目标库创建 用户的PROFILE，如果有不知道为DEFAULT的PROFILE，需单独创建
select ‘create profile ‘||profile ||’ limit ‘||resource_name ||’ ‘|| limit||’;’ from dba_profiles where limit <>‘DEFAULT’ and profile<>‘DEFAULT’ order by profile,resource_name
create profile DOURECPROFILE limit PASSWORD_LIFE_TIME UNLIMITED;
create profile GAPROFILE limit PASSWORD_LIFE_TIME UNLIMITED;
create profile ICMSPROFILE limit PASSWORD_LIFE_TIME UNLIMITED;
create profile LISPROFILE limit PASSWORD_LIFE_TIME UNLIMITED;
create profile LISUATPROFILE limit PASSWORD_LIFE_TIME UNLIMITED;
create profile MONITORING_PROFILE limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
create profile RISPROFILE limit PASSWORD_LIFE_TIME UNLIMITED;

–在目标库 创建表空间，建议配置db_create_file_dest参数
alter system set db_create_file_dest=’/datafile’ scope=both;
select ‘create tablespace ‘||tablespace_name ||’ datafile size 500m autoextend on;’ from dba_data_files order by tablespace_name;

–在目标库创建临时表空间，如果源库不只有一个临时表空间
select ‘create temporary tablespace ‘||tablespace_name ||’ tempfile size 500m autoextend on;’ from dba_temp_files order by tablespace_name;

–确认是否所有的表空间为自动拓展，以免导入数据的中途导致无法拓展，IMPDP无法HANG住
SELECT ‘ALTER DATABASE DATAFILE ‘’’||FILE_NAME||’’’ AUTOEXTENSIBLE ON;’ FROM DBA_DATA_FILES WHERE AUTOEXTENSIBLE=‘NO’ UNION ALL
SELECT ‘ALTER DATABASE TEMPFILE ‘’’||FILE_NAME||’’’ AUTOEXTENSIBLE ON;’ FROM DBA_TEMP_FILES WHERE AUTOEXTENSIBLE=‘NO’

–创建存储dump文件的目录并授权给用户
create or replace directory TUNE_SCRIPTS as ‘/datatmp’;
grant read,write on directory TUNE_SCRIPTS to b2b;

–查看logfile的大小，并适当的加大，防止导入时候，频繁切换log，导致HANG住
–查看状态，文件大小
select group#,status,bytes/1024/1024 m from v$log order by 1;

–添加logfile group
alter database add logfile group 4(’/data/POCSIT02/datafile/redo04a’,’/data/POCSIT02/datafile/redo04b’) size 1024m ;
或者
alter database add logfile group 4 size 1024m ;

–删除掉老的日志文件，需手动删除OS文件
alter database drop logfile group 1 ;

–连接要导出数据的用户，或者FULL=Y整库导出
select wm_concat(username) from dba_users where account_status=‘OPEN’
AND USERNAME NOT IN (‘SYS’,‘SYSTEM’,‘GGS’,‘VMSDB2’,‘TANHF’,‘CISPRO’,‘ZABBIX’,‘LINKFOREGL’,‘AUDIT5801’,‘LISTOYBTUSER’,‘GAPRO’) ORDER BY 1

–这个特殊需求，查看没有对象的用户
select wm_concat(a.username) from (
select username from dba_users where account_status=‘OPEN’ ) a left join
(select owner,count(object_name) num from dba_objects group by owner ) b on a.username=b.owner where b.num is null

–按照用户导出,添加参数 ’ACCESS_METHOD=DIRECT_PATH‘，直接导出，不写日志
expdp directory=TUNE_SCRIPTS dumpfile=full47200.dmp logfile=full47200.log schemas=XXX,XXX,XXX ACCESS_METHOD=DIRECT_PATH

–SCP到目标端
scp full47200.dmp oracle@目标端ip:目标端路径

–导入dump到目标端，建议排除index和统计信息
impdp directory=TUNE_SCRIPTS dumpfile=full47200.dmp logfile=full47200.log exclude=comment,index ACCESS_METHOD=DIRECT_PATH

–或者可以单独导出导入package,package body,index,statistics,procedure等等
expdp ‘/ as sysdba’ directory=TUNE_SCRIPTS dumpfile=full47200.dmp include=PROCEDURE,INDEX,FUNCTION,VIEW,SYNONYM,PACKAGE BODY,SEQUENCE,PACKAGE ACCESS_METHOD=DIRECT_PATH logfile=LISUAT_STAT.log schemas=XXX,XXX

–报以下错误时候，添加exclude=comment 排除comment
impdp directory=TUNE_SCRIPTS dumpfile=full47200.dmp logfile=full47200.log exclude=comment,index ACCESS_METHOD=DIRECT_PATH

–如果不想导出、传输、导入的这种方式，数据也不是很大，网速也比较好，可以直接用NETWORK_LINK导入到数据库
impdp network_link=poc47200 schemas=XXX,XXX,XXX exclude=comment,index

–导入时候是否有锁表，导致导入进程hang住
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locke{d}_{o}bjectlo,db{a}_{o}bjectsao,v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid

–杀掉进程
alter system kill ‘&sid,&serial’;

–备注：单个对象的导出导入

–建立一个远程数据的DATABASE LINK,检查源端和目标端的差异，包括
select a.OWNER,a.type,WM_CONCAT(’’’’||a.name||’’’’),LENGTH(WM_CONCAT(a.name)) from
(select a.owner,name,type,count(1) num from dba_source a where a.owner not like ‘%SYS%’ group by a.owner,name,type)a left join
(select a.owner,name,type,count(1) num from dba_source@poc47200 a where a.owner not like ‘%SYS%’ group by a.owner,name,type)b
on a.owner=b.owner and a.name=b.name and a.type=b.type where b.owner is null and a.owner not like ‘%SYS%’ and A.owner not in (‘XDB’,‘APEX_030200’,‘DBSNMP’)
and a.owner not like ‘ORD%’ --and a.name not like ‘BIN$%’
group by a.OWNER,a.type --HAVING LENGTH(WM_CONCAT(a.object_name))>0
ORDER BY A.OWNER

–检查源端和目标端的差异，包括
select wm_concat(’’’’||a.object_name||’’’’),a.OWNER ,a.OBJECT_TYPE
from
(select a.owner,a.object_name,OBJECT_TYPE, count(1) num from dba_objects a where a.object_type=‘VIEW’ group by a.owner,a.object_name,OBJECT_TYPE)a left join
(select a.owner,a.object_name,OBJECT_TYPE,count(1) num from dba_objects@poc47200 a where a.object_type=‘VIEW’ group by a.owner,a.object_name,OBJECT_TYPE)b
on a.owner=b.owner and a.object_name=b.object_name where b.owner is null and a.owner not like ‘%SYS%’ and A.owner not in (‘XDB’)
group by a.owner,a.OBJECT_TYPE
ORDER BY A.OWNER–,A.OBJECT_NAME

impdp ‘/ as sysdba’ directory=TUNE_SCRIPTS dumpfile=dumpTABLES.DMP logfile=dumpTABLES.log TABLES=ORDDATA.ORDDCM_PREFS_DEF_VALUES_TAB,ORDDATA.ORDDCM_PREFS_LOOKUP,ORDDATA.ORDDCM_PREFS_VALID_VALUES_TAB

–批量导出导入视图
expdp ‘/ as sysdba’
directory=TUNE_SCRIPTS dumpfile=dumpviews.dmp logfile=dumpviews.log schemas=LISUAT
include=view:“in(‘V_PEECORE’,‘VIEW_BEFORE_2017_06_30’,‘VIEW_ALL_DATA_RMP842’,‘V_PRELIMINARYEARNINGSESTIMATE2’,‘V_PRELIMINARYEARNINGSESTIMATE’,‘VIEW_RMP842_N’,‘VIEW_AFTER_2017_06_30’,‘VIEW_RMP842_P’,‘V_PEECORE233’,‘VM_CFG_TL_PRODUCTRATE’,‘V_LETTERS_DETAIL’,‘XQ_LCCONT_INFO’)”

–利用impdp可以导出的视图创建语句，这个方法还可以用于查看创建表的原始SQL。
impdp ‘/ as sysdba’ directory=TUNE_SCRIPTS dumpfile=dumpviews.dmp schemas=APEX_030200 exclude=comment SQLFILE=pss2.0_apptst1_view.sql

备注：导出SQL文件后，用SHELL的vi打开，把"CREATE FORCE" 变成"create or replace"，重新执行下脚本

–对于同义词的备份我们有多种方式来实现，如直接通过脚本生成同义词的创建脚本，或者使用dbms_metadata.get_ddl来提取同义词的定义脚本。然而在使用传统的exp或是datapump expdp实现schema级别数据迁移时，不能导出公共同义词。尽管如此，我们依旧可以使用导出导入的方式来实现。所不同的是，我们使用FULL=Y的方式来单独导出同义词，然后再将其导入的目标数据库。下文是对此进行的描述，末尾也给出了手动创建同义词的脚本。

–使用下面的方式导出公共同义词，对于可导出的对象我们可以查询数据字典 DATABASE_EXPORT_OBJECTS
expdp ‘/ as sysdba’
directory=TUNE_SCRIPTS dumpfile=dumpsynonym.dmp logfile=dumpsynonym.log full=y
include=SYNONYM:“IN (SELECT synonym_name FROM dba_synonyms A where exists (select 1 from dba_users B where account_status=‘OPEN’ AND USERNAME NOT IN (‘SYS’,‘SYSTEM’) AND A.TABLE_OWNER=B.USERNAME))”

impdp ‘/ as sysdba’ directory=TUNE_SCRIPTS dumpfile=dumpsynonym.dmp logfile=dumpsynonym.log full=y

directory=TUNE_SCRIPTS dumpfile=dumpsynonym.dmp logfile=dumpsynonym.log full=y
include=PUBLIC_SYNONYM/SYNONYM:“IN (select synonym_name from dba_synonyms where owner=‘B2Bxx’ )”

directory=TUNE_SCRIPTS dumpfile=dumpsynonym.dmp logfile=dumpsynonym.log full=y
include=SYNONYM:“IN (select synonym_name from dba_synonyms where owner=‘ICMSxx’ )”

–批量导出trigger
userid=’/ as sysdba’
directory=TUNE_SCRIPTS dumpfile=dumptrigger.dmp logfile=dumptrigger.log schemas=VCSIT
include=TRIGGER:“in(‘TIG_VOUCHER_INFOxx’,‘TIG_CODE_MAPPINGxx’)”

impdp ‘/ as sysdba’ directory=TUNE_SCRIPTS dumpfile=dumptrigger.dmp logfile=dumptrigger.log schemas=FLOWS_FILES

–批量导出索引
–去除storage等多余参数
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘STORAGE’,FALSE);
–输出信息采用缩排或换行格式化
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘PRETTY’,true);
–确保每个语句都带分号
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘SQLTERMINATOR’,true);
–去掉pctfree等的参数
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘SEGMENT_ATTRIBUTES’,false);
–增加表空间参数，但SEGMENT_ATTRIBUTES设置成false，这个参数被忽略
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘TABLESPACE’,true);

–导出导入lob表
expdp ‘/ as sysdba’ directory=TUNE_SCRIPTS dumpfile=dumptrigger.dmp logfile=dumptrigger.log tables=HCIMAGE.ES_MARKLAYER,HCIMAGE.UPDATE_PACK_INFO
impdp ‘/ as sysdba’ directory=TUNE_SCRIPTS dumpfile=dumptrigger.dmp logfile=dumptrigger.log tables=HCIMAGE.ES_MARKLAYER,HCIMAGE.UPDATE_PACK_INFO
–验证目标和源端的差异LOB表
select a., b. from
(select owner,table_name from dba_lobs a where segment_name in (‘SYS_LOB0000099026C00010$$x{x}^{\prime }{,}^{\prime }SY{S}_{L}OB0000099044C00006$$xx’)) a
left join (select owner,table_name from dba_lobs@poc47200 b) b
on a.owner=b.owner and a.table_name=b.table_name
where b.owner is null

----导出导入物化视图,切换到该用户下
conn xxx/xxx
SELECT DBMS_METADATA.GET_DDL(‘MATERIALIZED_VIEW’,‘T37_PARTY_RESULT_CURRxx’,‘AMLMVxx’) FROM DUAL;

–导出导入job
expdp lisuat/lisuat directory=TUNE_SCRIPTS dumpfile=dumptrigger.dmp logfile=dumptrigger.log schemas=LISUAT11 include=procobj:“in(‘JDT_PROCESSxxx’)”
impdp lisuat/lisuat directory=TUNE_SCRIPTS dumpfile=dumptrigger.dmp logfile=dumptrigger.log

–导出导入package body
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
expdp ‘/ as sysdba’
directory=TUNE_SCRIPTS dumpfile=dumptrigger.dmp logfile=dumptrigger.log schemas=gapro
include=package_body:“in(‘PKG_ICMS_RENxxxx’,‘xxxxx’)”

impdp gapro/gapro directory=TUNE_SCRIPTS dumpfile=dumptrigger.dmp logfile=dumptrigger.log SQLFILE=gapro1.sql

–更新dblink 连接用户的密码
update sys.link$ set passwordx=‘9CFEEE1ED5B56C86760257DAEE33BA4’
where name=‘POMSTEST11’