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

ORACLE逻辑备份

news 2026/8/13 3:36:06

生产上对于Oracle的备份通常有两种手段
1.物理备份通过数据库全备+归档+当前redo的方式可以实现数据库无丢失恢复
2.逻辑备份通过导入导出工具(exp/imp,expdp/impdp)实现数据库中数据对象的导出操作导出的方式是将对象的创建语句和行信息导出到一个二进制的转储文件中这样的文件不可以人为读取不能够使用归档进行修复

对于Oracle来讲生产上能够选择的备份方式只有RMAN(物理备份)

DBA 经常会需要在多个数据库之间迁移数据
测试数据库中的数据发布到产品库
OLTP数据库中的数据加载到数据仓库中
等等

逻辑备份不能当作是常规的备份手段但是可以当作是RMAN备份的一种补充手段

MySQL
mysqldump
mydumper/myloader

Oracle
exp/imp oracle 10G之前版本研发支持操作的对象有表/用户/表空间/数据库不能够支持并发操作
expdp/impdp 10G版本研发最主流的逻辑备份工具支持操作的对象有表/用户/表空间/数据库支持并发支持直接通过网络转储数据

expdp 工作原理

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
导出时 expdp支持中断操作可以在导出时为本次导出附加一个job名在到处过程中crtl+c 可以中断本次操作需要继续时
使用对应JOB名可以重新连回中断的导出操作

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
可以对转储文件进行压缩操作

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
指定导出的内容all:有结构语句有行数据
data_only:只要行数据
METADATA_ONLY:只要结构语句

生产上通常导出数据时不会要导出全部数据

expdp hr/oracle directory=d1 dumpfile=sales tables=hr.sales content=data_only query=sales:"WHERE create_date between xxx and xxxx ".

DIRECTORY
Directory object to be used for dump and log files.
转储文件的存放目录不能直接写DIRECTORY=/home/oracle/bak DIRECTORY是数据库中定义的某个目录的别名 directory=d1
SQL> select * from dba_directories; 查询数据库中所有目录的权限

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

-------------------------------------------
对导出的文件进行加密
ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

--------------------------------------------------
在导出时预估导出的内容大小
ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.
仅预估不导出
ESTIMATE_ONLY
Calculate job estimates without performing the export.

----------------------------------------
当导出用户的时候 exclude 排除某些对象或者使用include 仅包含某些对象

SCHEMAS
List of schemas to export [login schema].

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

-------------------------------------------------
可以设置导出的dumpfile的最大值
FILESIZE
Specify the size of each dump file in units of bytes.
----------------------------------
实现导出数据一致性
FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
-------------------------------------
导出全库是不包含sys用户的对象
FULL
Export entire database [N].

HELP
Display Help messages [N].

JOB_NAME
Name of export job to create.
---------------------------------------------
导出时将导出内容记录到某个日志文件中在生产上如果导出的内容比较多将导出内容记录在一个指定的日志文件中
LOGFILE
Specify log file name [export.log].
-----------------------------------------
提供2种操作 1.不产生转储文件直接将一个数据库中的对象导入到另一个数据库中
2.将产生的转储文件直接生成在客户端
NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [N].
并发
*PARALLEL
Change the number of active workers for current job.
----------------------------------------------
参数文件
可以将要操作的内容写入到某个文件中通过导出读取文件的方式获得要操作的内容
根据where条件只导出相关行：
vi par3
userid=system/oracle
directory=d2
dumpfile=emp.dmp
tables=hr.employees
QUERY=hr.employees:"WHERE department_id > 10"

expdp parfile=par3

使用参数文件导出数据非常常用因为在写导出语句时经常会写的非常长

*PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

------
导出时修改对象的定义信息
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

取样
SAMPLE
Percentage of data to be exported.

SCHEMAS
List of schemas to export [login schema].

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
------------------------------------------------------
导出语句中可以实现传输表空间
传输表空间可以实现一个数据库的表空间复制一份到另一个数据库中工作
TABLESPACES
Identifies a list of tablespaces to export.

TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

VERSION
Version 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_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly 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 directory=d1 dumpfile=hr.dmp tables=employees,departments

Export: Release 11.2.0.1.0 - Production on Thu Apr 18 01:39:47 2019

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=d1 dumpfile=hr.dmp tables=employees,departments
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.81 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/home/oracle/hr.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 01:40:02