Oracle数据库有3中标准的备份方法：

  • 导出／导入（EXP/IMP）

  • 冷备份

  • 热备份（RMAN）

Here only share the EXP/IMP way.

利用Export可将数据从数据库中提取出来，利用Import则可将提取出来的数据送回到Oracle数据库中去。

$ORACLE_HOME/bin/expdp help=y
$ORACLE_HOME/bin/impdp help=y

Before export steps:

-  备份和还原路径
SQL> SELECT * FROM Dba_Directories; --检查路径是否存在

mkdir /scratch/tabledmp1
SQL> create or replace directory dump_dir as '/scratch/tabledmp1';

- 检查是否拥有EXPORT FULL DATABASE 和IMPORT FULL DATABASE权限
SQL> SELECT * FROM Dba_Sys_Privs a WHERE a.Privilege LIKE '%EXP%' OR a.Privilege LIKE '%IMP%';    
SQL> SELECT * FROM Dba_Role_Privs a WHERE a.Granted_Role LIKE '%IMP%' OR a.Granted_Role LIKE '%EXP%';

- 授以某个用户的备份和还原的权限：
SQL> GRANT EXPORT FULL DATABASE,IMPORT FULL DATABASE TO $user;

- 增加tablespace （if the backup/restore data amount is large）
SQL> alter tablespace FUSION_TS_TX_DATA add datafile '/scratch/work/fusion/oradata/fusion/fusion_ts_tx_data2.dbf' size 20000m;

1. 简单数据的EXP/IMP

- By tables
$ORACLE_HOME/bin/expdp $user/$pwd@$ORACLE_SID \
TABLES=tab1,tab2 dumpfile=expdp.dmp directory=dump_dir LOGFILE=expdp.LOG;

$ORACLE_HOME/bin/impdp $user/$pwd@$ORACLE_SID \
TABLES=tab1,tab2 dumpfile=expdp.dmp directory=dump_dir LOGFILE=impdp.LOG;

- By users
$ORACLE_HOME/bin/expdp $user/$pwd@$ORACLE_SID \
schemas=scott dumpfile=expdp.dmp directory=dump_dir;

$ORACLE_HOME/bin/impdp $user/$pwd@$ORACLE_SID \
schemas=scott dumpfile=expdp.dmp directory=dump_dir;

-By Query condition
$ORACLE_HOME/bin/expdp $user/$pwd@$ORACLE_SID \
Tables=emp query='WHERE deptno=20'directory=dump_dir dumpfile=expdp.dmp;

$ORACLE_HOME/bin/impdp $user/$pwd@$ORACLE_SID \
TABLES=emp dumpfile=expdp.dmp directory=dump_dir;

-By tablesapce
$ORACLE_HOME/bin/expdp $user/$pwd@$ORACLE_SID \
TABLESPACES=tbs1,tbs2 dumpfile=expdp.dmp directory=dump_dir;

$ORACLE_HOME/bin/impdp $user/$pwd@$ORACLE_SID \
TABLESPACES=tbs1 dumpfile=expdp.dmp directory=dump_dir;

-By full db
$ORACLE_HOME/bin/expdp  $user/$pwd@$ORACLE_SID \
FULL=y dumpfile=expdp.dmp directory=dump_dir;

$ORACLE_HOME/bin/impdp $user/$pwd@$ORACLE_SID \
FULL=y dumpfile=expdp.dmp directory=dump_dir;

2. 增量数据的IMP

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}, default value is SKIP

$ORACLE_HOME/bin/impdp $user/$pwd@$ORACLE_SID \
TABLE_EXISTS_ACTION=append dumpfile=expdp.dmp directory=dump_dir;

3. Tips

-3.1 如果系统空间不足，可以用COMPRESSION参数，但会增加导出时间

– COMPRESSION

Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

expdp ... COMPRESSION=all

– COMPRESSION_ALGORITHM

Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH

expdp ... COMPRESSION_ALGORITHM=MEDIUM

-3.2 如果系统性能高，可以使用并行备份 PARALLEL，减少导出导入时间

– To Export, the PARALLEL parameter value should be less than or equal to the number of dump files.

expdp ... parallel=8 DUMPFILE=expdata%U.dmp

– To Import, the PARALLEL parameter value should not be much larger than the numbers of dump files.

impdp ... parallel=8 DUMPFILE=expdata%U.dmp