1.源端导出

（相应的directory要创建的）

expdp \' / as sysdba \' directory=backup dumpfile=expdp_full_20160729.dmp logfile=expdp_full_20160729.log full=y COMPRESSION=ALL

2.
目标端创建相应表空间

create tablespace MONIETLSHIBORSPACE datafile '/oradata/basedb/monietlshibospace01.dbf' size 100M autoextend on;
create tablespace MONIETLBNCHMRKSPACE datafile '/oradata/basedb/monietlbnchmrkspace01.dbf ' size 100M autoextend on;
create tablespace FSOTSPACE datafile '/oradata/basedb/fsotspace01.dbf' size 100M autoextend on;
create tablespace MONIETLTRADINGSPACE datafile '/oradata/basedb/monietltradingspace01.dbf' size 100M autoextend on;
create tablespace MONITORSPACE datafile '/oradata/basedb/monitorspace01.dbf' size 100M autoextend on;
create tablespace BASEETLTRADINGSPACE datafile '/oradata/basedb/baseetltradingspace01.dbf' size 100M autoextend on;
create tablespace BASEETLSHIBORSPACE datafile '/oradata/basedb/baseetlshiborsapce01.dbf' size 100M autoextend on;
create tablespace BASESPACE datafile '/oradata/basedb/basespace01.dbf' size 100M autoextend on;
create tablespace BASEINDX datafile '/oradata/basedb/baseindex01.dbf' size 100M autoextend on;
create tablespace BASEHISSPACE datafile '/oradata/basedb/basehisspace01.dbf' size 100M autoextend on;
alter tablespace BASESPACE add datafile '/oradata/basedb/basespace02.dbf' size 100M autoextend on;

3.目标端导入
（相应的directory要创建的）
impdp \' / as sysdba \' directory=db_backup dumpfile=expdp_full_20160729.dmp logfile=impdp_20160729_full.log schemas=ETLUSER2,MBROKER,BASE,DWUSER,YML,ETLUSER,MONIUSER,FSOTUSER,DATAWDBUSER,MIDCURVE,TADDM,TEST,MONITOR,SUPPORT,TIVOLI

4.检查无效对象
@?/rdbms/admin/utlrp.sql

set linesize 1000 pagesize 1000
select owner , object_type, object_name from dba_objects where status = 'INVALID';

compile无效对象

例如：alter package MONIUSER.DATA_TRANSFER compile body;
查看错误：
show error;

根据错误信息进行相关操作，比如权限问题之类的，授予相应权限

获取无效对象的DDL语句
set pagesize 1000 linesize 10000 long 10000
select dbms_metadata.get_ddl('PACKAGE BODY','DATA_TRANSFER','MONIUSER') from dual;

再次compile，检查无效对象
exec dbms_utility.compile_schema('MONIUSER',false);
select owner , object_type, object_name from dba_objects where status = 'INVALID';

5.检查db_link
对比源端的目标端的查询结果
select * from dba_db_links；

获取相应db link的创建语句，public的dblink

SQL> select dbms_metadata.get_ddl('DB_LINK','BKP_TO_BASE25','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('DB_LINK','BKP_TO_BASE25','PUBLIC')
--------------------------------------------------------------------------------

CREATE PUBLIC DATABASE LINK "BKP_TO_BASE25"
   CONNECT TO "ETLUSER" IDENTIFIED BY VALUES '0630A8FC656BA51ED433196420B5438E9D
11E34F9A6853209281EC46328C99CC934584F4AFBEA092E1594550755B8F189E79BF3175799258CA
0388FC8ECBE3E4255CF4AFAC548C70E6D8A199EE7610E2582577B4B1A94346ED9A28F44D8C4A04A5
1DA406062A67A86D82515D9F2664763037CC3BBFB85DE7FE462FF396148777'
   USING '
(DESCRIPTION =
    (SDU=32767)
     (ADDRESS = (PROTOCOL = TCP)(HOST = ******)(PORT = 1521))
    (CONNECT_DATA =
       (SERVER=dedicated)
       (SERVICE_NAME=**DB)
    )
)'

CREATE PUBLIC DATABASE LINK "BKP_TO_BASE25"
   CONNECT TO "ETLUSER" IDENTIFIED BY etluser
   USING '
(DESCRIPTION =
    (SDU=32767)
     (ADDRESS = (PROTOCOL = TCP)(HOST = ******)(PORT = 1521))
    (CONNECT_DATA =
       (SERVER=dedicated)
       (SERVICE_NAME=**DB)
    )
)'

6.检查job

select job, SCHEMA_USER,WHAT,NEXT_DATE,NEXT_SEC,INTERVAL,broken,FAILURES from dba_jobs；
select OWNER,JOB_NAME,ENABLED,STATE from dba_scheduler_jobs；

备注：

查看进度语句：

select sid,
serial#,
context,
sofar,
totalwork,
(time_remaining/60) minutes,
round(sofar/totalwork * 100 ,2) "complete"
from v$session_longops
where 1=1
and totalwork != 0
and sofar <> totalwork
/

ITPub博客

expdp/impdp 数据迁移一例

最新文章