环境：源端Windows2008，目标端Centos7.9
1、两端创建备份恢复目录
CREATE DIRECTORY directory_name AS 'path_to_directory';
grant read,write on DIRECTORY directory_name to public ;
2、源端数据泵导出数据
expdp system/Passwd@servername directory=directory_name dumpfile=ALL_meta.dmp logfile=exp_all_meta.log content=metadata_only compression=all full=y
#content，matadata_only 只导出元数据，默认全部导出
expdp system/Passwd@servername directory=directory_name dumpfile=schema.dmp logfile=schema.log schemas=schema1,schema2 compression=all
3、使用winscp等工具上传数据包，plsql查询表空间生成创建表空间脚本
-- 统计用户表与表空间的映射关系
select t.OWNER,t.TABLESPACE_NAME,count(*) from dba_tables t
group by t.OWNER,t.TABLESPACE_NAME ;
-- all 全部用户
select 'create TABLESPACE '|| tablespace_name ||' DATAFILE ' || '''' ||
'/path/oradata/'||tablespace_name|| '.DBF' || '''' ||' SIZE 521M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED ;'
from dba_data_files where tablespace_name!='USERS' ;
select 'create TEMPORARY TABLESPACE ' || tablespace_name || ' TEMPFILE ' ||
''''|| '/path/oradata/'||tablespace_name|| '.DBF' ||'''' || ' SIZE 521M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED ;'
from dba_temp_files where tablespace_name!='TEMP' ;
select 'create user ' || username || ' identified by ''passwd'' default tablespace '||
DEFAULT_TABLESPACE || ' TEMPORARY_TABLESPACE '|| TEMPORARY_TABLESPACE || ';'
from dba_users ;
4、选择需要恢复的用户对应的表空间在目标端创建表空间，并在目标端恢复数据
create TABLESPACE TB_DAT DATAFILE '/path/oradata/TB.DBF' SIZE 521M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED ;
create TEMPORARY TABLESPACE TB_TMP TEMPFILE '/path/ora_data/TB_TMP.DBF' SIZE 521M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED ;
create user tb identified by 'passwd' default tablespace TB_DAT TEMPORARY_TABLESPACE TB_TMP;
v_dump=XXX.DMP
v_d=`date '+%Y%m%d-%H%M'`
impdp system/passwd directory=directory_name dumpfile=${v_dump} logfile=imp_${v_dump}_${v_d}.log schemas=schema1,schema2 remap_tablespace=OLD_DAT:NEW_DAT,OLD_TMP:NEW_TMP table_exists_action=replace
5、验证，两端对比用户表、存储过程、函数、job等数据是否一致。
select t.OWNER,count(*),sum(t.NUM_ROWS)
from dba_tables t
where t.OWNER in
('SCHEMA1',''SCHEMA2)
group by t.OWNER
order by 1
;
select s.owner,s.type,count(*) from dba_source s
where s.OWNER in
('SCHEMA1',''SCHEMA2)
group by s.OWNER,s.type
order by 1,2,3
;
select j.SCHEMA_USER,count(*) from dba_jobs j
where j.SCHEMA_USER in
('SCHEMA1',''SCHEMA2)
group by j.SCHEMA_USER
order by 1,2
;
select l.owner,count(*) from dba_db_links l
where l.OWNER in
('SCHEMA1',''SCHEMA2)
group by l.OWNER
order by 1
;
select s.owner,s.table_owner,count(*) from dba_synonyms s
where s.OWNER in
('WFQLC','WFQLC_FORM','WFQLCB','WFQLC_SSJ','WFQLC_ZCGL','WFQLC_JSKH','WFQLC_DJDW','WFMAIL','WFXXPT','WFDZDA','WFDZDAB','HLWXX','XMFBGL')
group by s.OWNER,s.table_owner
order by 1,2,3
;
