首页 > 代码库 > 使用数据泵+dblink迁移数据库,适用于本地空间不足的情况

使用数据泵+dblink迁移数据库,适用于本地空间不足的情况

col name for a40select name,locks,pinsfrom v$db_object_cachewhere locks > 0 and pins > 0 and type=‘PROCEDURE‘;10.21.1.20 和10.24.51.11两个数据库的dblibk已经建好了,主要就impdp就可以了,一共需要导出drop user ILEARN_TRA       cascade;drop user ILA_SAP          cascade;drop user ILEARN_JOB       cascade;drop user ILEARN           cascade;drop user ILEARN_BEIYAN    cascade;drop user APEX_PUBLIC_USER cascade;srvctl stop database -d ilndbsrvctl start database -d ilndbSELECT SID,JOB FROM DBA_JOBS_RUNNING;MACHINE     PORT       TERMINAL   PROGRAM  select username,sid,SERIAL# from gv$session where username =‘ILEARN_TRA‘;select username,sid,SERIAL# from v$session where username =‘ILEARN_TRA‘;USERNAME                              SID    SERIAL#------------------------------ ---------- ----------ILEARN_TRA                           2545          5ALTER SYSTEM KILL SESSION ‘2545,5‘;SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=‘ILEARN_TRA‘;这几个用户下数据,正式迁移的时候,10.21.1.20上这几个用户数据要删除,整个过程耗时10:15:38 这是上次测试的日志 SYS@ilndb1>  select name from v$database@dblink_datapump; NAME --------- ILNDB dblink导库过程: 1、在客户端数据库创建 dblink 和要保存数据文件的目录: create public database link dblink_datapump connect to system identified by Clic1234 using ‘10.24.51.12:1521/ilndb‘; select name from v$database@dblink_datapump; create or replace directory expdp_dir as ‘/oracle/expdp‘; grant read, write on directory expdp_dir to public; 2、在客户端数据库创建相关表空间和用户 create tablespace ILEARN_DATA DATAFILE ‘+DATADG‘ SIZE 32767M AUTOEXTEND off;        //对比生产端,加足表空间容量 create user ILEARN_TRA identified by "Qwer4321" default tablespace ILEARN_DATA; grant resource,connect to ILEARN_TRA; 3、在客户端数据库使用IMPDP导入 nohup impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn1.log directory=expdp_dir schemas=ILEARN_TRA EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ILEARN_TRA parallel=20 &select sum(bytes/1024/1024) from dba_segments where owner=‘ILEARN_TRA‘;impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn3.log directory=expdp_dir schemas=ILEARN EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ILEARN  parallel=20select sum(bytes/1024/1024) from dba_segments where owner=‘ILEARN‘;impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn2.log directory=expdp_dir schemas=ILA_SAP EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ILA_SAP parallel=14--wanchengimpdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn4.log directory=expdp_dir schemas=ilearn_beiyan EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ilearn_beiyan parallel=14--wancheng impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn4.log directory=expdp_dir schemas=ilearn_job EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ilearn_job parallel=14--wanchengimpdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn5.log directory=expdp_dir schemas=APEX_PUBLIC_USER EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ilearn_apex parallel=14wancheng4、在客户端数据库删除 dblink: drop public database link dblink_datapump;   祝工作顺利,万事如意~~       nohup impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn_1210.log directory=expdp_dir schemas=ILEARN_TRA,ILA_SAP,ILEARN_JOB,ILEARN,ILEARN_BEIYAN,APEX_PUBLIC_USER  EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_1210 parallel=14 &ILEARN_TRA,ILA_SAP,ILEARN_JOB,ILEARN,ILEARN_BEIYAN,APEX_PUBLIC_USERcreate user VIEW_LMS_WORKBOOKS identified by Clic1234 default tablespace ILEARN_DATA account unlock;CREATE_AND_VIEW_LMS_WORKBOOKS

使用数据泵+dblink迁移数据库,适用于本地空间不足的情况