Oracle Data Pump Backup (EXPDP command) [go]

Oracle backup method mainly divided into data pump export backup, hot backup and cold backup three kinds, today first to practice the data pump backup and restore. Data pump export/import is a logical backup, both hot and cold backups are physical backups. ORACLE10G started to roll out the data Pump (EXPDP/IMPDP) and can use the parallel parameter options, so the execution is more efficient than the traditional exp command.

　　I. Knowledge of the EXPDP order

C:\>expdp -help Export: Release 11.1.0.7.0 - Production on星期六, 28 9月, 2013 10:21:52 Copyright (c) 2003, 2007, Oracle.  Allrights reserved. 数据泵导出实用程序提供了一种用于在 Oracle 数据库之间传输数据对象的机制。该实用程序可以使用以下命令进行调用: 示例: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp 您可以控制导出的运行方式。具体方法是: 在 ‘expdp‘命令后输入各种参数。要指定各参数, 请使用关键字:    格式: expdp KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)    示例: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scot         或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表 USERID 必须是命令行中的第一个参数。 关键字               说明 (默认) --------------------------------------------------------------------------- ATTACH                连接到现有作业, 例如 ATTACH [=作业名]。 COMPRESSION           减小转储文件内容的大小, 其中有效关键字  值为: ALL, (METADATA_ONLY), DATA_ONLY 和 NONE。 CONTENT              <SPAN style="COLOR: #ff0000"> 指定要卸载的数据, 其中有效关键字  值为: (ALL), DATA_ONLY 和 METADATA_ONLY。</SPAN>  --默认值为ALL                     DATA_OPTIONS          数据层标记, 其中唯一有效的值为: 使用CLOB格式的 XML_CLOBS-write XML 数据类型 DIRECTORY             <SPAN style="COLOR: #ff0000">供转储文件和日志文件使用的目录对象</SPAN>。 DUMPFILE              <SPAN style="COLOR: #ff0000">目标转储文件 (expdat.dmp) 的列表,例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.d </SPAN>ENCRYPTION            <SPAN style="COLOR: #ff0000">加密部分或全部转储文件, 其中有效关键字值为: ALL, DATA_ONLY, METADATA_ONLY,ENCRYPTED_COLUMNS_ONLY 或 NONE。</SPAN> ENCRYPTION_ALGORITHM  指定应如何完成加密, 其中有效关键字值为: (AES128), AES192 和 AES256。 ENCRYPTION_MODE       生成加密密钥的方法, 其中有效关键字值为: DUAL, PASSWORD和 (TRANSPARENT)。 ENCRYPTION_PASSWORD   用于创建加密列数据的口令关键字。 ESTIMATE              计算作业估计值, 其中有效关键字值为: (BLOCKS) 和 STATISTICS。 ESTIMATE_ONLY         在不执行导出的情况下计算作业估计值。 EXCLUDE               <SPAN style="COLOR: #ff0000">排除特定的对象类型, 例如 EXCLUDE=TABLE:EMP。--EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]<BR></SPAN>FILESIZE              以字节为单位指定每个转储文件的大小。 FLASHBACK_SCN         用于将会话快照设置回以前状态的 SCN。 --指定导出特定SCN时刻的表数据<BR>FLASHBACK_TIME        用于获取最接近指定时间的 SCN 的时间。--指定导出特定时间点的表数据，注意FLASHBACK_SCN和FLASHBACK_TIME不能同时使用<BR>FULL                  <SPAN style="COLOR: #ff0000">导出整个数据库 (N)。</SPAN> HELP                  显示帮助消息 (N)。 INCLUDE               包括特定的对象类型, 例如 INCLUDE=TABLE_DATA。 JOB_NAME              要创建的导出作业的名称。 LOGFILE               <SPAN style="COLOR: #ff0000">日志文件名 (export.log)。 </SPAN>NETWORK_LINK          链接到源系统的远程数据库的名称。 NOLOGFILE             不写入日志文件 (N)。 PARALLEL              <SPAN style="COLOR: #ff0000">更改当前作业的活动 worker 的数目。</SPAN> PARFILE               <SPAN style="COLOR: #ff0000">指定参数文件</SPAN>。 QUERY                 <SPAN style="COLOR: #ff0000">用于导出表的子集的谓词子句。--</SPAN>QUERY = [schema.][table_name:] query_clause<BR>REMAP_DATA            <SPAN style="COLOR: #ff0000">指定数据转换函数,例如 REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。 </SPAN>REUSE_DUMPFILES       覆盖目标转储文件 (如果文件存在) (N)。 SAMPLE               <SPAN style="COLOR: #ff0000"> 要导出的数据的百分比; </SPAN>SCHEMAS               <SPAN style="COLOR: #ff0000">要导出的方案的列表 (登录方案)。 </SPAN>STATUS                在默认值 (0) 将显示可用时的新状态的情况下,要监视的频率 (以秒计) 作业状态。 TABLES                <SPAN style="COLOR: #ff0000">标识要导出的表的列表 - 只有一个方案。--[schema_name.]table_name[:partition_name][,…]<BR></SPAN>TABLESPACES          <SPAN style="COLOR: #ff0000"> 标识要导出的表空间的列表。 </SPAN>TRANSPORTABLE         指定是否可以使用可传输方法, 其中有效关键字值为: ALWAYS, (NEVER)。 TRANSPORT_FULL_CHECK  验证所有表的存储段 (N)。 TRANSPORT_TABLESPACES 要从中卸载元数据的表空间的列表。 VERSION               <SPAN style="COLOR: #ff0000">要导出的对象的版本, 其中有效关键字为:(COMPATIBLE), LATEST 或任何有效的数据库版本。 </SPAN><BR>下列命令在交互模式下有效。 注: 允许使用缩写 命令               说明 --------------------------------------------------------------------------- ADD_FILE              向转储文件集中添加转储文件。 CONTINUE_CLIENT       返回到记录模式。如果处于空闲状态, 将重新启动作业。 EXIT_CLIENT           退出客户机会话并使作业处于运行状态。 FILESIZE              后续 ADD_FILE 命令的默认文件大小 (字节)。 HELP                  总结交互命令。 KILL_JOB              分离和删除作业。 PARALLEL              更改当前作业的活动 worker 的数目。PARALLEL=<worker 的数目>。                     <BR>REUSE_DUMPFILES       覆盖目标转储文件 (如果文件存在) (N)。 START_JOB             启动/恢复当前作业。 STATUS                在默认值 (0) 将显示可用时的新状态的情况下,要监视的频率 (以秒计) 作业状态。STATUS[=interval] STOP_JOB              顺序关闭执行的作业并退出客户机。STOP_JOB=IMMEDIATE 将立即关闭数据泵作业。 <BR><SPAN style="COLOR: #ff0000">备注：红颜色标记的字体，表示是expdp命令常用的命令选项，这个需要大家掌握理解并能灵活运用。()括号括起来的代表是expdp命令的默认选项</SPAN>

Ii. preparatory work

　　1. Query path information

To view the path information that has been created:

SELECT * from Dba_directories;

　　2. Create a path

The Create path requires the SYS permission and requires the Create any directory permission before the path can be created.

Option: Directory=directory_object

The directory_object is used to specify the directory object name. It is important to note that directory objects are objects created using the Create DIRECTORY statement, not the OS directory.

Eg:create OR REPLACE directory backup_path as ' D:\APP\ORADATA\db_backup '; --Create a path named Dackup_path and point to the specified location of the hard disk

To authorize the newly created path:

Eg:grant read,write on the directory backup_path to Orcldev; --Assign the Read and write permissions of the path to each Orcldev user.

　Third, operation example

The Execute EXPDP and IMPDP commands require exp_full_database and Imp_full_database permissions, and the authorization statements are as follows:

Eg:grant exp_full_database,imp_full_database to Orcldev;

　　　1. Export Orcldev objects used in this schema [schemas or full]

EG:EXPDP orcldev/[email protected] Directory=backup_path dumpfile=orcldev_schema.dmp logfile=orcldev_ Schema_2013.log Schemas=orcldev

　　2. Export Orcldev Some tables under this user [tables]

EG:C:\>EXPDP orcldev/oracle Directory=dackup_path dumpfile=orcldev_table.dmp logfile=orcldev_table_2013.log tables= (' tab_test ', ' tab_a ')

　　　3. Export only Orcldev metadata for this user [content]

EG:C:\>EXPDP orcldev/oracle Directory=dackup_path dumpfile=orcldev_meta.dmp logfile=orcldev_meta_2013.log

Schemas=orcldev content=metadata_only

4, only export Orcldev This user 50% sample data [sample]

EG:C:\>EXPDP orcldev/oracle Directory=dackup_path dumpfile=orcldev_samp.dmp logfile=orcldev_samp_2013.log Schemas=orcldev sample=50

5. Backup the whole library in parallel mode [parallel]

The parallel parameter is valid only after the oracle10g version (contains 10g).

Oracle_online:you can use the DumpFile parameter during export operations to specify multiple dump files, by using a subst Itution variable (%u) in the filename. This is called a dump file template. The new dump files are created as they is needed, beginning with-for-%u, then using-02,03,and so on.

EG:C:\>EXPDP orcldev/oracle Directory=dackup_path dumpfile=orcldev_parallel_%u.dmp logfile=orcldev_parallel_ 2013.log parallel=4

"%u" means that an incremented sequence number is automatically generated.

　　6, export Orcldev This schema object, but does not include the index [exclude]

Eg:--objects that can be excluded are: view,package,function,index,constraints,table,schema,user, etc.

1) C:\>EXPDP orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log Schemas=orcldev Exclude=index

2) C:\>EXPDP orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log Schemas=orcldev exclude=index: "Like ' test% '"--Export the Orcldev scheme, rejecting the index starting with TEST

3) C:\>EXPDP orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log Exclude=schema: "= ' SCOTT '"

C:\>EXPDP orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log EXCLUDE= USER: "= ' SCOTT '"

--Backs up the entire library but rejects the Scott user's object.

Note: Include and exclude cannot be used at the same time.

7. Parfile option

The EXPDP command can call the Parfile file, and the backup script can be written in Parfile, using the query option.

Oracle highly recommends QUERY that is specifications in a parameter file; otherwise, you might has to use Operati Ng System-specific escape characters on the command line before each quotation mark.

such as expdp.txt content as follows:

Userid=orcldev/oracle directory=dackup_path dumpfile=orcldev_parfile.dmp logfile=orcldev_parfile.log TABLES= ' TAB_ TEST ' query= ' WHERE tran_date=to_date (' 2013-08-31 ', ' yyyy-mm-dd ') "

Execution method: EXPDP Parfile=expdp.txt can perform backup

Using the Parfile advantage is that using the query option does not use escape characters, and if you put the query parameter outside, you need to escape "".

eg

Unix notation:

EXPDP orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log schemas=orcldev INCLUDE=TABLE:\ "in \ (\ ') Test_a\ ', \ ' test_b\ ' \) \ "--the UNIX system performs the need to escape single quotes, otherwise it will be an error.

Windows notation:

EXPDP orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log schemas=orcldev INCLUDE=TABLE: "in \ (' Test_a ', ' test_b ') "

　　8. tablespace Export Table Space

EG:EXPDP orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log Tablespaces=user,orcldev

9. Version Options

The default value for the version option is compatible, which is compatibility mode. When we back up, we can specify the version number.

EG:EXPDP orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log full=y VERSION=10.2.0.4

　　　10. Flashback_time option

Specify the table data to export at a specific point in time, and you can contact the Flashback feature.

EG:C:\>EXPDP orcldev/oracle directory=dackup_path dumpfile=orcldev_flash.dmp logfile=orcldev_flash.log SCHEMAS= Orcldev flashback_time= "To_timestamp (' 2013-09-28 14:30:00 ', ' dd-mm-yyyy HH24:MI:SS ')"

　Iv. references

http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm#i1007829

Oracle Data Pump Backup (EXPDP command) [go]