oracleexp(expdp)数据迁移（生产环境，进行数据对以及统计信息的收集）

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

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

一、迁移数据倒出部分
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
1、前期准备

停止业务软件&＃xff0c;中间件&＃xff0c;杀掉oracle进程

ps -ef | grep LOCAL&＃61;NO | awk &＃39;{print $2}&＃39; | xargs kill -9

2、检查无效对象
--统计失效的对象&＃xff1a;
select owner, object_type,status, count(*)
from dba_objects
where status&＃61;&＃39;INVALID&＃39;
group by owner, object_type, status
order by owner, object_type;

结果如下&＃xff1a;
OWNER OBJECT_TYPE STATUS COUNT(*)
------------------------------ ------------------- ------- ----------
ZJJJ PACKAGE BODY INVALID 1

--查看具体失效对象
col owner for a20;
col object_name for a32;
col object_type for a16
col status for a8
select owner, object_name, object_type, status
from dba_objects
where status&＃61;&＃39;INVALID&＃39;
order by 1, 2,3;

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

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

编译无效&＃xff0c;需要业务人员手动编译。

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 TEMP
ZJJJ OPEN TBS_YW_DATA TEMP
KETTLE OPEN USERS TEMP
SYS OPEN SYSTEM TEMP
SYSTEM OPEN SYSTEM TEMP

已选择24行。

select * from dba_sys_privs where grantee in (&＃39;ZJJJ&＃39;) order by 1;

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

已选择8行。

select * from dba_role_privs where grantee in(&＃39;ZJJJ&＃39;) order by 1;

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

设置字符集&＃xff08;expdp不用设置&＃xff09;

查看字符集&＃xff1a;

SQL>select userenv(&＃39;language&＃39;) from dual;

AMERICAN _ AMERICA. ZHS16GBK

set nls_lang&＃61;AMERICAN_AMERICA.ZHS16GBK

exp system/oracle&＃64;CCDB direct&＃61;y recordlength&＃61;65535 buffer&＃61;104857600 file&＃61;d:/temp-2017-02-23/exp_zjjj.dmp log&＃61;d:/temp-2017-02-23/exp_zjjj.log feedback&＃61;10000 owner&＃61;zjjj

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

set serveroutput on size 1000000
set pages 50000
spool d:/temp-2017-02-23/laoku-zjjj.txt

DECLARE
v_cnt number;
BEGIN
FOR rec in (select &＃39;ZJJJ.&＃39; || TABLE_NAME AS tanme from dba_tables where owner&＃61;&＃39;ZJJJ&＃39; order by 1)
LOOP
execute immediate &＃39;select count(*) from &＃39;||rec.tanme into v_cnt;
dbms_output.put_line(rpad(rec.tanme,40,&＃39;-&＃39;)||v_cnt);
END LOOP;
END;
/
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

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

二、迁移倒入部分
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
修改数据库默认参数

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

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

&＃43;CCDG/dcpdb/datafile/system.260.933443685
&＃43;CCDG/dcpdb/datafile/sysaux.261.933443687
&＃43;CCDG/dcpdb/datafile/undotbs1.262.933443689
&＃43;CCDG/dcpdb/datafile/undotbs2.264.933443695
&＃43;CCDG/dcpdb/datafile/users.265.933443697

SQL>

create tablespace TBS_YW_DATA datafile &＃39;&＃43;CCDG&＃39; 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按用户导入

设置字符集&＃xff08;impdp不用设置&＃xff09;

查看字符集&＃xff1a;

SQL>select userenv(&＃39;language&＃39;) from dual;

AMERICAN _ AMERICA. ZHS16GBK

set nls_lang&＃61;AMERICAN_AMERICA.ZHS16GBK

imp system/oracle&＃64;ccdb fromuser&＃61;zjjj touser&＃61;zjjj file&＃61;d:/temp-2017-02-23/exp_zjjj.dmp log&＃61;d:/temp-2017-02-23/imp_zjjj.log feedback&＃61;100000 buffer&＃61;524288000

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

set serveroutput on size 1000000
set pages 50000
spool d:/temp-2017-02-23/xinku-zjjj.txt

DECLARE
v_cnt number;
BEGIN
FOR rec in (select &＃39;ZJJJ.&＃39; || TABLE_NAME AS tanme from dba_tables where owner&＃61;&＃39;ZJJJ&＃39; order by 1)
LOOP
execute immediate &＃39;select count(*) from &＃39;||rec.tanme into v_cnt;
dbms_output.put_line(rpad(rec.tanme,40,&＃39;-&＃39;)||v_cnt);
END LOOP;
END;
/

三、迁移数据进行对比部分&＃xff1a;

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

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

4、检查无效对象
--统计失效的对象&＃xff1a;
select owner, object_type,status, count(*)
from dba_objects
where status&＃61;&＃39;INVALID&＃39;
group by owner, object_type, status
order by owner, object_type

--查看具体失效对象
col owner for a20;
col object_name for a32;
col object_type for a16
col status for a8
select owner, object_name, object_type, status
from dba_objects
where status&＃61;&＃39;INVALID&＃39;
order by 1, 2,3;

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

&＃64;$ORACLE_HOME/rdbms/admin/utlrp.sql

5、收集对象统计信息

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

select owner, table_name,object_type,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(&＃39;t1&＃39;) and
owner &＃61; upper(&＃39;ZJJJ&＃39;)
and (stale_stats &＃61; &＃39;YES&＃39; or last_analyzed is null);

SELECT Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_Analyzed
FROM Dba_Tables WHERE owner &＃61; upper(&＃39;ZJJJ&＃39;);

--查看表的直方图
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 &＃61; b.owner
and a.table_name &＃61; b.table_name
and a.owner &＃61; upper(&＃39;ZJJJ&＃39;);
--and a.table_name &＃61; upper(&＃39;t1&＃39;);

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

BEGIN
dbms_stats.gather_schema_stats(ownname&＃61;> &＃39;ZJJJ&＃39;,
estimate_percent &＃61;> 100,
method_opt &＃61;> &＃39;for all columns size repeat&＃39;,
no_invalidate &＃61;> FALSE,
degree &＃61;> 8,
cascade &＃61;> TRUE);
END;
/

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;