总结一下10g到11g利用数据泵迁移的过程

适用于数据量较小并且停业时间较长的系统。

一.导出之前

1.1.找出所有的用户并排除系统用户--因为我们是按照用户来导出导入的

set line 200 pages 9999

select username from dba_users where username not in (

'SYS','SYSTEM','OUTLN','MGMT_VIEW','MDSYS','ORDSYS','CTXSYS','ANONYMOUS','EXFSYS','DMSYS','DBSNMP','WMSYS','SYSMAN','XDB','ORDPLUGINS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','DIP','SCOTT','ORACLE_OCM','TSMSYS'

);

1.2.检查对象数量

set line 200

select object_type,count(*) from dba_objects where owner in('a','b','c')group by object_type;

1.3.查询所有失效对象

col owner for a20

col object_name for a20

col object_type for a20

col status for a20

select owner,object_name,object_type,status from dba_objects where status= 'INVALID';

1.4.测试dblink的使用情况，并把dblink记录下来

测试dblink 的方法：

select * from dual@dblink_name;

或者通过plsql测试。

set line 200

col owner for a20

col db_link for a30

col username for a20

select owner,db_link,username from dba_db_links;

或者

set line 200

col object_name for a20

col owner for a20

select object_name,owner from dba_objects where object_type=’DTABASE LINK’;

二.开始导出

expdp "'/ as sysdba'" directory=DP_DIR dumpfile=dblink.dmp include=db_link full=y

expdp \'\/ as sysdba\'  directory=DP_DIR  SCHEMAS=a dumpfile=a.dmp logfile=expdp_a.log EXCLUDE=statistics,dblink

expdp \'\/ as sysdba\'  directory=DP_DIR  SCHEMAS=b dumpfile=b.dmp logfile=expdp_b.log EXCLUDE=statistics,dblink

expdp \'\/ as sysdba\'  directory=DP_DIR  SCHEMAS=c dumpfile=c.dmp logfile=expdp_c.log EXCLUDE=statistics,dblink

因为按照用户导出的话默认不会导出public的dblink，所以我们在导出用户的时候排除dblink，然后单独导出dblink。

三.传dmp并进行导入

impdp "'/ as sysdba'" directory=DP_DIR dumpfile=dblink.dmp include=db_link full=y

impdp \'\/ as sysdba\'  directory=DP_DIR  SCHEMAS=a dumpfile=a.dmp logfile=impdp_a.log EXCLUDE=statistics,dblink

impdp \'\/ as sysdba\'  directory=DP_DIR  SCHEMAS=b dumpfile=b.dmp logfile=impdp_b.log EXCLUDE=statistics,dblink

impdp \'\/ as sysdba\'  directory=DP_DIR  SCHEMAS=c dumpfile=c.dmp logfile=impdp_c.log EXCLUDE=statistics,dblink

四.解决报错并验证

记录导入过程中的报错并进行处理。

验证对象数量和失效对象的数量。

编译失效对象：

@$ORACLE_HOME/rdbms/admin/utlrp.sql    

或者个别的可以手动编译

select 'ALTER' ||OBJECT_TYPE||''||OWNER||'.'|| OBJECT_NAME||'COMPILE;' from all_objects where status='INVALID'
AND object_type in ('PACKAGE','FUNCTION','PROCEDURE','TABLE','VIEW','SEQUENCE','TRIGGER');