生产上 对于Oracle的备份 通常有两种手段1.物理备份 通过数据库全备归档当前redo的方式 可以实现数据库无丢失恢复2.逻辑备份 通过导入导出工具(exp/imp,expdp/impdp)实现数据库中数据对象的导出操作 导出的方式 是将对象的创建语句和行信息 导出到一个二进制的转储文件中 这样的文件 不可以人为读取 不能够使用归档进行修复对于Oracle来讲 生产上 能够选择的备份方式 只有RMAN(物理备份)DBA 经常会需要在多个数据库之间迁移数据测试数据库中的数据发布到产品库OLTP数据库中的数据加载到数据仓库中等等逻辑备份 不能当作是常规的备份手段 但是可以当作是RMAN备份的一种补充手段MySQLmysqldumpmydumper/myloaderOracleexp/imp oracle 10G之前版本研发 支持操作的对象有 表/用户/表空间/数据库 不能够支持并发操作expdp/impdp 10G版本研发 最主流的逻辑备份工具 支持操作的对象有 表/用户/表空间/数据库 支持并发 支持直接通过网络转储数据expdp 工作原理ATTACHAttach to an existing job.For example, ATTACHjob_name.导出时 expdp支持中断操作 可以在导出时 为本次导出附加一个job名 在到处过程中crtlc 可以中断本次操作 需要继续时使用对应JOB名可以重新连回中断的导出操作COMPRESSIONReduce the size of a dump file.Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.可以对转储文件 进行压缩操作CONTENTSpecifies data to unload.Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.指定导出的内容all:有结构语句 有行数据data_only:只要行数据METADATA_ONLY:只要结构语句生产上 通常导出数据时 不会要导出全部数据expdp hr/oracle directoryd1 dumpfilesales tableshr.sales contentdata_only querysales:WHERE create_date between xxx and xxxx .DIRECTORYDirectory object to be used for dump and log files.转储文件的存放目录 不能直接写DIRECTORY/home/oracle/bak DIRECTORY是数据库中定义的某个目录的别名 directoryd1SQL select * from dba_directories; 查询数据库中所有目录的权限DUMPFILESpecify list of destination dump file names [expdat.dmp].For example, DUMPFILEscott1.dmp, scott2.dmp, dmpdir:scott3.dmp.-------------------------------------------对导出的文件进行加密ENCRYPTIONEncrypt part or all of a dump file.Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.ENCRYPTION_ALGORITHMSpecify how encryption should be done.Valid keyword values are: [AES128], AES192 and AES256.ENCRYPTION_MODEMethod of generating encryption key.Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].ENCRYPTION_PASSWORDPassword key for creating encrypted data within a dump file.--------------------------------------------------在导出时 预估导出的内容大小ESTIMATECalculate job estimates.Valid keyword values are: [BLOCKS] and STATISTICS.仅预估 不导出ESTIMATE_ONLYCalculate job estimates without performing the export.----------------------------------------当导出用户的时候 exclude 排除某些对象 或者 使用include 仅包含某些对象SCHEMASList of schemas to export [login schema].EXCLUDEExclude specific object types.For example, EXCLUDESCHEMA:HR.INCLUDEInclude specific object types.For example, INCLUDETABLE_DATA.-------------------------------------------------可以设置导出的dumpfile的最大值FILESIZESpecify the size of each dump file in units of bytes.----------------------------------实现导出数据一致性FLASHBACK_SCNSCN used to reset session snapshot.FLASHBACK_TIMETime used to find the closest corresponding SCN value.-------------------------------------导出全库 是不包含sys用户的对象FULLExport entire database [N].HELPDisplay Help messages [N].JOB_NAMEName of export job to create.---------------------------------------------导出时将导出内容记录到某个日志文件中 在生产上 如果导出的内容比较多 将导出内容记录在一个指定的日志文件中LOGFILESpecify log file name [export.log].-----------------------------------------提供2种操作 1.不产生转储文件 直接将一个数据库中的对象 导入到另一个数据库中2.将产生的转储文件 直接生成在客户端NETWORK_LINKName of remote database link to the source system.NOLOGFILEDo not write log file [N].并发*PARALLELChange the number of active workers for current job.----------------------------------------------参数文件可以将要操作的内容写入到某个文件中 通过导出读取文件的方式 获得要操作的内容根据where条件只导出相关行vi par3useridsystem/oracledirectoryd2dumpfileemp.dmptableshr.employeesQUERYhr.employees:WHERE department_id 10expdp parfilepar3使用参数文件导出数据 非常常用 因为在写导出语句时 经常会写的非常长*PARFILESpecify parameter file name.QUERYPredicate clause used to export a subset of a table.For example, QUERYemployees:WHERE department_id 10.------导出时修改对象的定义信息REMAP_DATASpecify a data conversion function.For example, REMAP_DATAEMP.EMPNO:REMAPPKG.EMPNO.REUSE_DUMPFILESOverwrite destination dump file if it exists [N].取样SAMPLEPercentage of data to be exported.SCHEMASList of schemas to export [login schema].TABLESIdentifies a list of tables to export.For example, TABLESHR.EMPLOYEES,SH.SALES:SALES_1995.------------------------------------------------------导出语句中可以实现 传输表空间传输表空间 可以实现 一个数据库的表空间 复制一份到另一个数据库中工作TABLESPACESIdentifies a list of tablespaces to export.TRANSPORTABLESpecify whether transportable method can be used.Valid keyword values are: ALWAYS and [NEVER].TRANSPORT_FULL_CHECKVerify storage segments of all tables [N].TRANSPORT_TABLESPACESList of tablespaces from which metadata will be unloaded.VERSIONVersion of objects to export.Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.------------------------------------------------------------------------------中断导出操作后 重新连回操作 可以用以下命令 继续执行The following commands are valid while in interactive mode.Note: abbreviations are allowed.ADD_FILEAdd dumpfile to dumpfile set.CONTINUE_CLIENTReturn to logging mode. Job will be restarted if idle.EXIT_CLIENTQuit client session and leave job running.FILESIZEDefault filesize (bytes) for subsequent ADD_FILE commands.HELPSummarize interactive commands.KILL_JOBDetach and delete job.PARALLELChange the number of active workers for current job.REUSE_DUMPFILESOverwrite destination dump file if it exists [N].START_JOBStart or resume current job.Valid keyword values are: SKIP_CURRENT.STATUSFrequency (secs) job status is to be monitored wherethe default [0] will show new status when available.STOP_JOBOrderly shutdown of job execution and exits the client.Valid keyword values are: IMMEDIATE.先添加一下临时表空间SQL alter tablespace temp add tempfile /u01/app/oracle/oradata/PROD4/PROD4/temp01.dbf size 100m;expdp hr/oracle directoryd1 dumpfilehr.dmp tablesemployees,departmentsExport: Release 11.2.0.1.0 - Production on Thu Apr 18 01:39:47 2019Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting HR.SYS_EXPORT_TABLE_01: hr/******** directoryd1 dumpfilehr.dmp tablesemployees,departmentsEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 128 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/COMMENTProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/TRIGGERProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported HR.DEPARTMENTS 7.007 KB 27 rows. . exported HR.EMPLOYEES 16.81 KB 107 rowsMaster table HR.SYS_EXPORT_TABLE_01 successfully loaded/unloaded******************************************************************************Dump file set for HR.SYS_EXPORT_TABLE_01 is:/home/oracle/hr.dmpJob HR.SYS_EXPORT_TABLE_01 successfully completed at 01:40:02导出导入一定要注意用户的权限问题expdp sh/oracle directoryd1 dumpfilehr.dmp tableshr.employees,hr.departments导出操作通常使用system用户