oracle exp(expdp)数据迁移（生产环境，进行数据对以及统计信息

前言：客户需要迁移XX 库 ZJJJ用户（迁移到其他数据库），由于业务复杂，客户都弄不清楚里面有哪些业务系统，为保持数据一致性，需要停止业务软件，中间件，杀掉oracle进程。

?温馨提示：很多网上资料只是简单的导入，导出（其实大家都会），并没有进行数据对比，以及统计信息的收集，就会业务反馈特别慢，原因是导入的数据还是原先的统计信息。

一、迁移数据倒出部分=============================================================1、前期准备

停止业务软件，中间件，杀掉oracle进程

ps -ef | grep LOCAL=NO | awk '{print $2}' | xargs kill -9

2、检查无效对象--统计失效的对象：select owner,object_type,status,count(*) from dba_objects where status='INVALID' group by owner,status order by owner,object_type;

结果如下：OWNER OBJECT_TYPE STATUS COUNT(*)------------------------------ ------------------- ------- ----------ZJJJ PACKAGE BODY INVALID 1

--查看具体失效对象col owner for a20;col object_name for a32;col object_type for a16col status for a8 select owner,object_name,status from dba_objectswhere status='INVALID'order by 1,2,3;

OWNER OBJECT_NAME OBJECT_TYPE STATUS-------------------- -------------------------------- ---------------- -------ZJJJ PKG_XXFW_SMS PACKAGE BODY INVALID

--执行脚本编译数据库失效对象。@$ORACLE_HOME/rdbms/admin/utlrp.sql

编译无效，需要业务人员手动编译。

3、EXP 按用户导出

用户 表空间ZJJJ TBS_YW_DATA

select username,account_status,default_tablespace,temporary_tablespace from dba_users;

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE------------------------------ -------------------------------- ------------------------------ ---------------------WEIXIN OPEN WEIXIN TEMPZJJJ OPEN TBS_YW_DATA TEMPKETTLE OPEN USERS TEMPSYS OPEN SYSTEM TEMPSYSTEM OPEN SYSTEM TEMP

已选择24行。

select * from dba_sys_privs where grantee in ('ZJJJ') order by 1;

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---ZJJJ CREATE TYPE NOZJJJ UNLIMITED TABLESPACE NOZJJJ CREATE TRIGGER NOZJJJ CREATE SEQUENCE NOZJJJ DEBUG CONNECT SESSION NOZJJJ CREATE PROCEDURE NOZJJJ CREATE TABLE NOZJJJ CREATE VIEW NO

已选择8行。

select * from dba_role_privs where grantee in('ZJJJ') order by 1;

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---ZJJJ EXP_FULL_DATABASE NO YESZJJJ RESOURCE NO YESZJJJ IMP_FULL_DATABASE NO YESZJJJ CONNECT NO YES

设置字符集（expdp不用设置）

查看字符集：

SQL>select userenv('language') from dual;

AMERICAN _ AMERICA. ZHS16GBK

set nls_lang=AMERICAN_AMERICA.ZHS16GBK

exp system/oracle@CCDB direct=y recordlength=65535 buffer=104857600 file=d:/temp-2017-02-23/exp_zjjj.dmp log=d:/temp-2017-02-23/exp_zjjj.log feedback=10000 owner=zjjj

注释：如果不开并行，exp和expdp速度差距不大，我主张用expdp,尴尬的是领导要我用exp这种方式。4、检查对象下表的具体行数

set serveroutput on size 1000000 set pages 50000spool d:/temp-2017-02-23/laoku-zjjj.txt DECLARE v_cnt number; BEGIN FOR rec in (select 'ZJJJ.' || TABLE_NAME AS tanme from dba_tables where owner='ZJJJ' order by 1) LOOP execute immediate 'select count(*) from '||rec.tanme into v_cnt;dbms_output.put_line(rpad(rec.tanme,40,'-')||v_cnt); END LOOP; END; /=============================================================

*********************************

二、迁移倒入部分=============================================================修改数据库默认参数

1、创建表空间&用户SQL> select name from v$datafile;

NAME------------------------------------------------------

+CCDG/dcpdb/datafile/system.260.933443685+CCDG/dcpdb/datafile/sysaux.261.933443687+CCDG/dcpdb/datafile/undotbs1.262.933443689+CCDG/dcpdb/datafile/undotbs2.264.933443695+CCDG/dcpdb/datafile/users.265.933443697

SQL>

create tablespace TBS_YW_DATA datafile '+CCDG' size 2G autoextend on next 500m;

create user ZJJJ identified by zjjj default tablespace TBS_YW_DATA;

grant EXP_FULL_DATABASE,RESOURCE,IMP_FULL_DATABASE,CONNECT to ZJJJ;

grant CREATE TYPE,UNLIMITED TABLESPACE,CREATE TRIGGER,CREATE SEQUENCE,DEBUG CONNECT SESSION,CREATE PROCEDURE,CREATE TABLE,CREATE VIEW to ZJJJ;

2、IMP按用户导入

设置字符集（impdp不用设置）

查看字符集：

select userenv('language') from dual;

imp system/oracle@ccdb fromuser=zjjj touser=zjjj file=d:/temp-2017-02-23/exp_zjjj.dmp log=d:/temp-2017-02-23/imp_zjjj.log feedback=100000 buffer=524288000

3、检查对象下表的具体行数

set serveroutput on size 1000000 set pages 50000spool d:/temp-2017-02-23/xinku-zjjj.txt DECLARE v_cnt number; BEGIN FOR rec in (select 'ZJJJ.' || TABLE_NAME AS tanme from dba_tables where owner='ZJJJ' order by 1) LOOP execute immediate 'select count(*) from '||rec.tanme into v_cnt;dbms_output.put_line(rpad(rec.tanme,'-')||v_cnt); END LOOP; END; /

三、迁移数据进行对比部分：

进行导出文件d:/temp-2017-02-23/xinku-zjjj.txt 文件和导入文件d:/temp-2017-02-23/xinku-zjjj.txt? 所有表行数的对比，确保无误。

注意：为确保数据一致性，一定要对比导入和导出数据行数是否一样，因为客户公司都是证券，基金等，每一条数据都很重要。

4、检查无效对象--统计失效的对象：select owner,object_type

--查看具体失效对象col owner for a20;col object_name for a32;col object_type for a16col status for a8 select owner,3;

--执行脚本编译数据库失效对象。

@$ORACLE_HOME/rdbms/admin/utlrp.sql

5、收集对象统计信息

--查看表统计信息是否过期：exec dbms_stats.flush_database_monitoring_info;

select owner,table_name,num_rows,sample_size,trunc(sample_size / num_rows * 100) estimate_percent,stale_stats,last_analyzed from dba_tab_statistics where --table_name in upper('t1') and owner = upper('ZJJJ') and (stale_stats = 'YES' or last_analyzed is null);

SELECT Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_AnalyzedFROM Dba_Tables WHERE owner = upper('ZJJJ');

--查看表的直方图select a.column_name, b.num_rows, a.num_distinct Cardinality, round(a.num_distinct / b.num_rows * 100,2) selectivity, a.histogram, a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = upper('ZJJJ'); --and a.table_name = upper('t1');

--对某一个schma收集统计信息

BEGINdbms_stats.gather_schema_stats(ownname=> 'ZJJJ', estimate_percent => 100, method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree => 8, cascade => TRUE);END;/

=============================================================

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!