当前位置：首页 > article >正文

oracle expdp/impdp 迁移数据库

article 2024/11/16 6:59:08

1.创建和源库相同tablespace

select 'alter ' || 'tablespace ' || b.tablespace_name || ' add datafile ''' ||
       --修改为目标库datafile目录
        b.file_name|| '''' || ' size 128m autoextend on next 16384 maxsize ' ||
        to_char(b.MAXBYTES) ||  ';'
  from dba_tablespaces a, dba_data_files b
 where a.TABLESPACE_NAME = b.TABLESPACE_NAME
   and b.tablespace_name not in ('SYSTEM', 'SYSAUX', 'USERS')
   and a.CONTENTS not in ('TEMPORARY', 'UNDO')
   and a.bigfile = 'NO'
union
select 'create ' || case a.BIGFILE
          when 'NO' then
           ' '
          when 'YES' then
           ' bigfile '
        end || 'tablespace ' || a.tablespace_name || ' datafile ''' ||
       --修改为目标库datafile目录
        '/datafilename/' || a.tablespace_name || '_001.dbf' || '''' ||
        ' size 128m autoextend on next 16384 ' ||
        case a.BIGFILE
          when 'NO' then
           ''
          when 'YES' then
           'maxszie ' || to_char(b.MAXBYTES) 
        end || ' blocksize ' || a.block_size || ';'
  from dba_tablespaces a, dba_data_files b
 where a.TABLESPACE_NAME = b.TABLESPACE_NAME
   and a.tablespace_name not in ('SYSTEM', 'SYSAUX', 'USERS')
   and a.CONTENTS not in ('TEMPORARY', 'UNDO')
 order by 1 desc

1.查找正在执行的dump任务

select * from dba_datapump_jobs

通过以下命令进入执行的任务 impdp \"/ as sysdba\" attach=SYS_IMPORT_SCHEMA_01

Kill任务命令：kill_job

TABLE_EXISTS_ACTION=replace

cluster=n

2.新建dump目录

create directory dumpdir as '/home/oracle/dumpdir'

3.全库导出

expdp \'/as sysdba\' DIRECTORY=dumpdir dumpfile=misdev20230627_%U.dmp logfile=misdev20230627.log full=y exclude=statistics compression=all PARALLEL=8

4.指定用户导出

expdp \'/as sysdba\' directory=DATA_PUMP_DIR dumpfile=fwqc_%U.dmp logfile=fwqc.log SCHEMAS=fwqc exclude=statistics compression=all PARALLEL=8

5.指定表导出

expdp \'/as sysdba\' directory=dumpdir dumpfile=CP_DIE_%U.dmp logfile=CP_DIE.log tables=tysapp.CP_DIE exclude=statistics PARALLEL=3

7.按照用户导出

expdp \'/as sysdba\' directory=DATA_PUMP_DIR dumpfile=mes12db_20230713_%U.dmp SCHEMAS=MESSA,MISREAD,FWRUN,FLOWDATALDR,GGER,FWPURGE,MES8TRAN,TIVY,MESQ,REPUSER,YMSFAB,SHMESQ,FWSPC,EAPQUERY,WORKMANAGER,HELPDESKQ,ORACLE,FDCUSER,BJBS,PMSUSER,SQLTXPLAIN,MHAQUERY,MESSHIP,MESMODULE,VCSTEST,RTDUSER,MESY,KPIUSER,MESQUERY,ECOMM,DAVID logfile=mes12db_20230713.log exclude=statistics PARALLEL=8

8.只导出元数据

expdp \'/as sysdba\' directory=DATA_PUMP_DIR dumpfile=mes19db_20230928.dmp logfile=mes19db_20230928.log SCHEMAS=MESSA,fwprod,MISREAD,FWRUN,FLOWDATALDR,GGER,FWPURGE,MES8TRAN,TIVY,MESQ,REPUSER,YMSFAB,SHMESQ,FWSPC,EAPQUERY,WORKMANAGER,HELPDESKQ,ORACLE,FDCUSER,BJBS,PMSUSER,MHAQUERY,MESSHIP,MESMODULE,VCSTEST,RTDUSER,MESY,KPIUSER,MESQUERY,ECOMM,DAVID content=metadata_only PARALLEL=8

1.全库导入

Impdp \'/as sysdba\' DIRECTORY=dumpdir dumpfile=apc12db_exp _%U.dmp logfile=apc12db_imp.log full=y transform=oid:n PARALLEL=32 cluster=n EXCLUDE=SCHEMA:\"IN $\'SYS\',\'SYSTEM\',\'XDB\',\'WMSYS\',\'PERFSTAT\',\'TOAD\',\'APPQOSSYS\',\'SPA\',\'GSMUSER\',\'MDDATA\',\'SI_INFORMTN_SCHEMA\',\'OUTLN\',\'GGSYS\',\'OLAPSYS\',\'ORDDATA\',\'LBACSYS\',\'GSMROOTUSER\',\'SYSDG\',\'SYSKM\',\'ORACLE_OCM\',\'GSMCATUSER\',\'REMOTE_SCHEDULER_AGENT\',\'ORDSYS\',\'SYS$UMF\',\'DVF\',\'DBSNMP\',\'SYSBACKUP\',\'GSMADMIN_INTERNAL\',\'MDSYS\',\'SYSRAC\',\'XDB\',\'WMSYS\',\'DBSFWUSER\',\'DVSYS\',\'AUDSYS\',\'ORDPLUGINS\',\'OJVMSYS\'$\"

查看全文

http://www.kler.cn/a/288261.html