Oracle 数据泵导数据(expdp,impdp)

friendlessstar

　　目录：
　　一、冷备份导数注意事项
　　二、数据泵导数注意事项
　　一、冷备份导数注意事项
　　1、冷备恢复步骤：
　　a、关闭源库
　　b、从源库传输数据文件、控制文件、pfile、日志文件到目标库，并赋予oracle用户权限
　　c、从目标库打开传送过来的pfile，修改实例名、控制文件路径、dump（adump、bdump...）类文件夹路径
　　d、启动数据库到mount，rename数据文件和日志文件路径
　　e、启动数据库到open状态
　　f、配置监听，工作完成
　　2、冷备恢复常见命令备忘：
　　--目标端有数据库的情况下，需要删除目标端数据库的步骤
　　ps -ef | grep LOCAL=NO  --查看有无客户端连接到数据库
　　lsnrctl stop 监听名     --停止监听
　　ps -ef | grep LOCAL=NO | awk '{print ($2)}' | xargs kill -9  --kill客户端连接
　　shutdown immediate      --关闭数据库
　　startup exclusive restrict mount;   --启动数据库到专有限制模式
　　drop database;     --删除数据库
　　--rname数据文件、日志文件的命令

　　>
　　>　　二、数据泵导数注意事项
　　--使用parfile方式写导入导出语句，因为有的时候只要导出或者只要导入，用pfile写方便其他同事查看你的导数参数。
　　--导入全库数据而不导入表结构时，要在目标库建立和源库对应的表空间，并分配好大小。
　　1、导出、导入数据
　　导出：
　　vi expdp_full_日期.par
　　userid='/ as sysdba'
　　directory=导数目录
　　##该路径可以通过查询dba_directoires表得到；
　　##建立路径的语法为：create directory 目录名 as '目录路径';
　　##对目录路径要授予相应的权限：chown -R oracle:dba /目录路径
　　dumpfile=expdp_full_日期.dmp
　　logfile=expdp_full_日期.log
　　parallel=2  --分配两条通道
　　##通道分配数量的大小要参考逻辑CPU的数量来确定，用nmon命令查看逻辑CPU个数，parallel不能超过逻辑CPU的一半，常见的设置有2、4、8、16。有时候parallel会与filesize这个参数冲突，如果发现语句书写无误，但是有不明原因报错，可以尝试去掉filesize参数再测试下。
　　filesize=5G   --每个dump文件大小
　　--导出全库结构
　　加参数：full=y
　　content=metadata_only
　　--导出全库数据不导结构
　　加参数：full=y
　　content=data_only
　　--导出schemas
　　加参数：schemas=xxx
　　--导出表
　　加参数：tables=xxx,xxx,xxx
　　导入：
　　vi impdp_full_日期.par
　　userid='/ as sysdba'
　　directory=导数目录
　　dumpfile=expdp_full_日期.dmp
　　logfile=expdp_full_日期.log
　　parallel=2    --导出的pfile是多少parallel，导入就是多少parallel
　　--导入全库结构
　　加参数：content=metadata_only
　　--导出全库数据不导结构
　　加参数：content=data_only
　　2、注意事项
　　（以FCR为例，FCR库相对来说比较有针对性）
　　1、impdp异常终止时的处理步骤：
　　第一步：查看数据库中的导数任务（sqlplus下运行）
　　set line 200
　　col owner_name for a20
　　col operation for a30
　　col state for a30
　　select owner_name,job_name,operation,state,degree from dba_datapump_jobs;
　　第二步：查看impdp导入详情（操作系统命令模式下运行）
　　impdp \'/ as sysdba\' ATTACH='SYS_IMPORT_SCHEMA_01'
　　第三步：停止JOB
　　impdp \'/ as sysdba\' ATTACH='SYS_EXPORT_SCHEMA_01'
　　impdp> stop_job
　　第四步：杀掉JOB
　　impdp \'/as sysdba\' ATTACH='SYS_EXPORT_SCHEMA_01'
　　impdp> kill_job
　　2、当不导入结构只导入数据时，需要禁用涉及对象的约束和触发器，语句如下：
　　--先关闭触发器再关闭约束，防止触发器插入约束外的数据。
　　--关闭trigger
　　cat >start_dis_tirger.sql
　　spool /home/oracle/dis_tirger.sql
　　select 'alter trigger ' || owner || '.' || trigger_name||' ENABLE;'from dba_triggers
　　where status='DISABLED' and owner in ('FCRBRNDATA','FCRHSTDATA');
　　spool off;
　　--关闭约束
　　cat >start_dis_const.sql
　　spool /home/oracle/dis_const.sql
　　SELECT 'ALTER TABLE '||T.owner||'.'||T.table_name||' enable constraint '||t.constraint_name||';'
　　from dba_constraints s, dba_constraints t
　　where s.owner = t.r_owner and s.constraint_name = t.r_constraint_name and t.owner in ('FCRBRNDATA','FCRHSTDATA')
　　and t.status='DISABLED';
　　spool off;
　　--导数工作完成后替换上述语句的关键字，先开约束再开触发器，防止触发器插入约束外的数据。
　　3、导某些表的时候可能存在资源占用，查询语句如下：
　　set pages 130;
　　set lines 1300;
　　col object_name for a20;
　　col machine for a20;
　　col program for a20;
　　col killid for a30;
　　col os_pid format a20;
　　select object_name ,machine ,s.program ,s.sid||','||s.serial# as killid,
　　p.spid as os_pid ,s.sql_address,l.locked_mode,s.username,s.process,s.sql_id
　　from v$locked_object l,dba_objects o ,v$session s ,v$process p
　　where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr
　　and object_name=upper('&tablename') and o.owner=upper('&owner') order by 1;
　　4、导入全库数据而不导入结构时，会有很多无效对象，无效对象编译过程如下：
　　附录二：快速删除索引语句
　　附录三：无效对象编译相关脚本
　　附录四：禁用约束和触发器的处理
　　附录五：从全库结构dmp文件中取建索引语句
　　附录一：导数情况查询
　　--获取对象定义
　　set lin 200 pagesize 500
　　set long 99999
　　set longc 99999
　　select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&username')) from dual;
　　附录二：快速删除索引语句
　　--删除全局索引
　　select distinct 'drop index '||i.OWNER||'.'||i.INDEX_NAME||';' from dba_indexes i
　　where i.TABLE_OWNER='FCRHSTDATA' and i.TABLE_NAME in (
　　'CH_ACCT_LEDG',
　　'REC_TXN_LOG',
　　'TD_DEP_PRORATED_TAX_DTLS',
　　'TD_DEP_PRORATED_TAX_DTLS',
　　'XFACE_AUDIT_TRAIL_LOG',
　　'XF_OL_ST_TXNLOG_MMDD'
　　);
　　--删除分区索引
　　select distinct 'drop index '||p.INDEX_OWNER||'.'||p.INDEX_NAME||';' from dba_ind_partitions p,dba_indexes i
　　WHERE  p.INDEX_OWNER=i.OWNER and i.INDEX_NAME=p.INDEX_NAME and i.TABLE_OWNER='FCRHSTDATA' and
　　i.STATUS='N/A' and i.TABLE_NAME in (
　　'XF_STCAP_GL_TXNS_MMDD','XF_ST_CAP_INPUT_TXN_TEMP','CH_ACCT_INT_BREAKUP','XFACE_AUDIT_TRAIL_LOG',
　　'ET_ALERTS_LOG_1','XF_OL_ST_SCLOG_MMDD','CH_ARREARS_TABLE','ET_ALERT_PUBLISH','TD_NOBOOK',
　　'CI_CUSTMAST','CH_ACCT_INT_BREAKUP_DELETED ','TD_AUDIT_TRAIL','XFACE_FCR_LOGGER',
　　'CH_ACCT_MAST','BA_CHANGE_RECORD_LOG','REC_TXN_LOG_PURGE','CH_ACCT_BALANCE_DTLS_MMDD','XF_OL_ST_TXNLOG_STL_MMDD ',
　　'CH_TMP_RCH405','CH_ACCT_CAPITALIZATION_HISTORY','XF_STCAP_GL_TXNS'
　　);
　　附录三：无效对象编译相关脚本
　　1、系统用户无效对象编译： @?/rdbms/admin/utlrp.sql
　　2、一般用户无效对象编译
　　--源库无效对象采集脚本start_privs.sql，生成授权脚本privs.sql
　　cat >start_privs.sql
　　#####
　　--start$$
　　spool /home/oracle/privs.sql
　　--create role$$
　　select 'create role '|| role||';' from dba_roles;
　　select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to '||ROLE||';' from role_tab_privs
　　union all
　　select 'grant '||PRIVILEGE|| ' to '||ROLE||';' from role_sys_privs
　　union all
　　select 'grant '||GRANTED_ROLE|| ' to '||ROLE||';' from role_role_privs;
　　--grant privs$$
　　select 'grant '||PRIVILEGE||' to '||GRANTEE||';'
　　from dba_sys_privs
　　where GRANTEE in(select username from dba_users where username not in('APPQOSSYS','DBSNMP','DIP','EXFSYS','WMSYS','SYSTEM','SYS','SDBXTS','OUTLN','ORACLE_OCM'))
　　union all
　　select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';'
　　from DBA_ROLE_PRIVS
　　where GRANTEE in(select username from dba_users where username not in('APPQOSSYS','DBSNMP','DIP','EXFSYS','WMSYS','SYSTEM','SYS','SDBXTS','OUTLN','ORACLE_OCM'))
　　union all
　　select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to '||GRANTEE||';'
　　from dba_tab_privs
　　where GRANTEE in(select username from dba_users where username not in('APPQOSSYS','DBSNMP','DIP','EXFSYS','WMSYS','SYSTEM','SYS','SDBXTS','OUTLN','ORACLE_OCM'))
　　;
　　--compile objects$$
　　select 'alter VIEW '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='VIEW' and status='INVALID' and owner  'IMPUSER'
　　union all
　　select 'alter PROCEDURE '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='PROCEDURE' and status='INVALID' and owner  'IMPUSER'
　　union all
　　select 'alter FUNCTION '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='FUNCTION' and status='INVALID' and owner  'IMPUSER'
　　union all
　　select 'alter TRIGGER '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='TRIGGER' and status='INVALID' and owner  'IMPUSER'
　　union all
　　select 'alter PACKAGE '||OWNER||'.'||OBJECT_NAME||' compile BODY;' from dba_objects where OBJECT_TYPE='PACKAGE BODY' and status='INVALID' and owner  'IMPUSER'
　　union all
　　select 'alter PACKAGE '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='PACKAGE' and status='INVALID' and owner  'IMPUSER'
　　union all
　　SELECT 'CREATE OR REPLACE PUBLIC SYNONYM '||A.OBJECT_NAME||' FOR '||B.TABLE_OWNER||'.'||B.TABLE_NAME||';'
　　FROM DBA_OBJECTS A,DBA_SYNONYMS B
　　WHERE B.SYNONYM_NAME=A.OBJECT_NAME AND B.OWNER='PUBLIC' AND A.OBJECT_TYPE='SYNONYM' AND A.STATUS='INVALID'  and owner  'IMPUSER'
　　union all
　　SELECT 'CREATE OR REPLACE SYNONYM '||A.OWNER||'.'||A.OBJECT_NAME||' FOR '||B.TABLE_OWNER||'.'||B.TABLE_NAME||';'
　　FROM DBA_OBJECTS A,DBA_SYNONYMS B
　　WHERE B.SYNONYM_NAME=A.OBJECT_NAME AND A.OWNER=B.OWNER AND B.OWNER'PUBLIC' AND A.OBJECT_TYPE='SYNONYM' AND A.STATUS='INVALID'  and A.owner  'IMPUSER'
　　;
　　--end$$
　　spool off;
　　####
　　--目标库运行无效对象编译采集脚本start_compile.sql，生成无效对象编译脚本compile.sql
　　cat >start_compile.sql
　　#####
　　set line 1000
　　set pages 20000
　　spool /home/oracle/compile.sql
　　--compile objects$$
　　select 'alter VIEW '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='VIEW' and status='INVALID' and owner'IMPUSER'
　　union all
　　select 'alter PROCEDURE '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='PROCEDURE' and status='INVALID' and owner'IMPUSER'
　　union all
　　select 'alter FUNCTION '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='FUNCTION' and status='INVALID' and owner'IMPUSER'
　　union all
　　select 'alter TRIGGER '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='TRIGGER' and status='INVALID' and owner'IMPUSER'
　　union all
　　select 'alter PACKAGE '||OWNER||'.'||OBJECT_NAME||' compile BODY;' from dba_objects where OBJECT_TYPE='PACKAGE BODY' and status='INVALID' and owner'IMPUSER'
　　union all
　　select 'alter PACKAGE '||OWNER||'.'||OBJECT_NAME||' compile;' from dba_objects where OBJECT_TYPE='PACKAGE' and status='INVALID' and owner'IMPUSER'
　　union all
　　select 'alter type '||OWNER||'.'||OBJECT_NAME||' compile body;' from dba_objects where OBJECT_TYPE='TYPE BODY' and status='INVALID' and owner'IMPUSER'
　　union all
　　SELECT 'CREATE OR REPLACE PUBLIC SYNONYM '||A.OBJECT_NAME||' FOR '||B.TABLE_OWNER||'.'||B.TABLE_NAME||';'
　　FROM DBA_OBJECTS A,DBA_SYNONYMS B
　　WHERE B.SYNONYM_NAME=A.OBJECT_NAME AND B.OWNER='PUBLIC' AND A.OBJECT_TYPE='SYNONYM' AND A.STATUS='INVALID' and A.owner'IMPUSER'
　　union all
　　SELECT 'CREATE OR REPLACE SYNONYM '||A.OWNER||'.'||A.OBJECT_NAME||' FOR '||B.TABLE_OWNER||'.'||B.TABLE_NAME||';'
　　FROM DBA_OBJECTS A,DBA_SYNONYMS B
　　WHERE B.SYNONYM_NAME=A.OBJECT_NAME AND A.OWNER=B.OWNER AND B.OWNER'PUBLIC' AND A.OBJECT_TYPE='SYNONYM' AND A.STATUS='INVALID' and B.owner'IMPUSER';
　　--end$$
　　spool off;
　　#####
　　附录四：禁用约束和触发器的处理
　　1、在导入数据库时关闭触发器和约束
　　2、在导出数据库时开启约束和触发器
　　--开启约束
　　cat >start_en_const.sql
　　set line 1800
　　spool /home/oracle/en_const.sql
　　SELECT 'ALTER TABLE '||T.owner||'.'||T.table_name||' enable constraint '||t.constraint_name||';'
　　from dba_constraints s, dba_constraints t
　　where s.owner = t.r_owner and s.constraint_name = t.r_constraint_name AND t.status='DISABLED' and t.VALIDATED='VALIDATED'
　　union all
　　SELECT 'ALTER TABLE '||T.owner||'.'||T.table_name||' enable novalidate constraint '||t.constraint_name||';'
　　from dba_constraints s, dba_constraints t
　　where s.owner = t.r_owner and s.constraint_name = t.r_constraint_name AND t.status='DISABLED' and t.VALIDATED='NOT VALIDATED';
　　spool off;
　　--开启trigger
　　cat >start_en_tirger.sql
　　set line 1800
　　spool /home/oracle/en_tirger.sql
　　select 'alter trigger ' || owner || '.' || trigger_name||' ENABLE;'from dba_triggers
　　where status='DISABLED';
　　spool off;
　　附录五：从全库结构dmp文件中取建索引语句
　　下面语句是取指定表的索引语句，取全库同样也可以使用，生成语句后，可以在生成的脚本内修改并发数，使索引创建更快。
　　USERID='/ AS SYSDBA'
　　DIRECTORY=EXPDP_FULLDATA
　　DUMPFILE=FULLDATABASE_0604.dmp
　　logfile=index_import_full.log
　　tables=(
　　FCRHSTDATA.ACTB_HISTORY,
　　)
　　include=index
　　sqlfile=index.sql