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;
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
(相应的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;
查看错误:
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';
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
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 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
/
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博客 ” ,链接:http://blog.itpub.net/30935525/viewspace-2122716/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30935525/viewspace-2122716/