揭秘ORACLE备份之－－逻辑备份（EXPDP）

最新推荐文章于 2024-07-25 08:41:17 发布
最新推荐文章于 2024-07-25 08:41:17 发布
阅读量193 收藏
点赞数
文章标签： 数据库

EXPDP（数据泵）是逻辑备份的一种，是通过PL/SQL语句操作数据库，必须在服务端执行。
从ORACLE 10g开始提供。

[root@dg ~(23:29:45)]# su - oracle
[oracle@dg ~(23:30:59)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 23:31:06 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 查看目录
23:31:06 SYS@wailon>select * from all_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
------------------------------------------------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

SYS                            DATA_PUMP_DIR
/u01/app/oracle/admin/wailon/dpdump/

SYS                            XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS                            GGS_DDL_TRACE
/u01/app/oracle/diag/rdbms/wailon/wailon/trace

-- 创建备份目录
23:31:15 SYS@wailon>create directory backup_dir as '/home/oracle/dbbackup';

Directory created.

-- 授权目录读写权限给SCOTT
23:32:11 SYS@wailon>grant read,write on directory backup_dir to scott;

Grant succeeded.

23:32:32 SYS@wailon>select privilege,table_name from dba_tab_privs where grantee='SCOTT';

PRIVILEGE                                TABLE_NAME
---------------------------------------- ------------------------------
WRITE                                    BACKUP_DIR
READ                                     BACKUP_DIR

2 rows selected.

23:33:04 SYS@wailon>host
[oracle@dg ~(23:34:11)]$ cd dbbackup/

-- 备份表结构等，不包括数据
[oracle@dg dbbackup(23:36:24)]$ more expdp-metadata.par
userid=scott/tiger
directory=backup_dir
tables=emp
dumpfile=emp-metadata.dmp
logfile=emp-metadata.log
content=metadata_only
job_name=expdp-metadata

[oracle@dg dbbackup(23:36:27)]$ expdp parfile=expdp-metadata.par
Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:36:42 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."expdp-metadata":  scott/******** parfile=expdp-metadata.par
Processing object type TABLE_EXPORT/TABLE/TABLE
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SCOTT"."expdp-metadata" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.expdp-metadata is:
  /home/oracle/dbbackup/emp-metadata.dmp
Job "SCOTT"."expdp-metadata" successfully completed at 23:37:24

-- 只备份表数据，不备份结构
[oracle@dg dbbackup(23:37:53)]$more expdp-data.par
userid=scott/tiger
directory=backup_dir
tables=emp
dumpfile=emp-data.dmp
logfile=emp-data.log
content=data_only
job_name=expdp-data

[oracle@dg dbbackup(23:38:53)]$ expdp parfile=expdp-data.par
Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:38:59 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."expdp-data":  scott/******** parfile=expdp-data.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
Master table "SCOTT"."expdp-data" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.expdp-data is:
  /home/oracle/dbbackup/emp-data.dmp
Job "SCOTT"."expdp-data" successfully completed at 23:39:08

-- 备份表结构及数据
[oracle@dg dbbackup(23:39:41)]$ more expdp-all.par
userid=scott/tiger
directory=backup_dir
tables=emp
dumpfile=emp-all.dmp
logfile=emp-all.log
content=all
job_name=expdp-all

[oracle@dg dbbackup(23:39:41)]$ expdp parfile=expdp-all.par
Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:40:15 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."expdp-all":  scott/******** parfile=expdp-all.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
Master table "SCOTT"."expdp-all" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.expdp-all is:
  /home/oracle/dbbackup/emp-all.dmp
Job "SCOTT"."expdp-all" successfully completed at 23:40:31

-- 备份当前连接用户所有数据
[oracle@dg dbbackup(23:40:37)]$more expdp-user.par
userid=scott/tiger
directory=backup_dir
dumpfile=expdp-scott.dmp
logfile=expdp-scott.log
content=all
job_name=expdp-scott

[oracle@dg dbbackup(23:43:02)]$ expdp parfile=expdp-user.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:43:11 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."expdp-scott":  scott/******** parfile=expdp-user.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 49.12 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TAB_DR"                            38.00 MB 1194928 rows
. . exported "SCOTT"."GGS_DDL_HIST"                      909.7 KB     462 rows
. . exported "SCOTT"."GGS_MARKER"                        352.6 KB     359 rows
. . exported "SCOTT"."CHECKPOINT"                        10.26 KB       2 rows
. . exported "SCOTT"."DEPT"                              5.984 KB       6 rows
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
. . exported "SCOTT"."EMPLOYEE"                          5.460 KB       1 rows
. . exported "SCOTT"."GGS_DDL_HIST_ALT"                  6.648 KB      25 rows
. . exported "SCOTT"."GGS_SETUP"                         5.546 KB       6 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."SQLLDR"                            7.710 KB      31 rows
. . exported "SCOTT"."TAB_OGG_DLL"                       5.445 KB       2 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."CHECKPOINT_LOX"                        0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_COLUMNS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_LOG_GROUPS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_OBJECTS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PARTITIONS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PRIMARY_KEYS"                  0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES"                         0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES_LOG"                     0 KB       0 rows
Master table "SCOTT"."expdp-scott" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.expdp-scott is:
  /home/oracle/dbbackup/expdp-scott.dmp
Job "SCOTT"."expdp-scott" successfully completed at 23:44:28

-- 并行备份指定用户的所有数据
[oracle@dg dbbackup(23:46:23)]$ more expdp-scott.par
userid=scott/tiger
directory=backup_dir
dumpfile=expdp-scott1.dmp
logfile=expdp-scott1.log
content=all
parallel=2
schemas=scott
job_name=expdp-scott1

[oracle@dg dbbackup(23:46:32)]$ expdp parfile=expdp-scott.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:46:51 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."expdp-scott1":  scott/******** parfile=expdp-scott.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 50.18 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TAB_DR"                            38.00 MB 1194928 rows
. . exported "SCOTT"."GGS_DDL_HIST"                      1.111 MB     576 rows
. . exported "SCOTT"."GGS_MARKER"                        395.6 KB     387 rows
. . exported "SCOTT"."CHECKPOINT"                        10.26 KB       2 rows
. . exported "SCOTT"."DEPT"                              5.984 KB       6 rows
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
. . exported "SCOTT"."EMPLOYEE"                          5.460 KB       1 rows
. . exported "SCOTT"."GGS_DDL_HIST_ALT"                  6.742 KB      28 rows
. . exported "SCOTT"."GGS_SETUP"                         5.546 KB       6 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."SQLLDR"                            7.710 KB      31 rows
. . exported "SCOTT"."TAB_OGG_DLL"                       5.445 KB       2 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."CHECKPOINT_LOX"                        0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_COLUMNS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_LOG_GROUPS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_OBJECTS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PARTITIONS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PRIMARY_KEYS"                  0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES"                         0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES_LOG"                     0 KB       0 rows
Master table "SCOTT"."expdp-scott1" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.expdp-scott1 is:
  /home/oracle/dbbackup/expdp-scott1.dmp
Job "SCOTT"."expdp-scott1" successfully completed at 23:47:55

-- 测试EXPDP的INCLUDE及EXCLUDE
[oracle@dg dbbackup(23:55:19)]$ more expdp2.par
cuserid=scott/tiger
directory=backup_dir
dumpfile=expdp-scott2.dmp
logfile=expdp-scott2.log
content=all
include=table:"in('DEPT','EMP')"
include=table:"like  'SAL%'"
exclude=function,procedure,table:"='BONUS'"
job_name=expdp-scott2



[oracle@dg dbbackup(23:56:19)]$ expdp parfile=expdp2.par
Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:55:23 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00011: parameter include is incompatible with parameter exclude
-- INCLUDE和EXCLUDE不能同时使用

[oracle@dg dbbackup(23:57:19)]$ more expdp2.par
cuserid=scott/tiger
directory=backup_dir
dumpfile=expdp-scott2.dmp
logfile=expdp-scott2.log
content=all
include=table:"in('DEPT','EMP')"
include=table:"like 'SAL%'"
#exclude=function,procedure,table:"='BONUS'"
job_name=expdp-scott2



[oracle@dg dbbackup(23:58:19)]$ expdp parfile=expdp2.par
Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:57:27 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."expdp-scott2":  scott/******** parfile=expdp2.par
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SCOTT"."expdp-scott2" completed with 2 error(s) at 23:57:37

-- 只能使用一个INCLUDE参数

[oracle@dg dbbackup(23:58:54)]$ more expdp2.par
userid=scott/tiger
directory=backup_dir
dumpfile=expdp-scott2.dmp
logfile=expdp-scott2.log
content=all
include=table:"in('DEPT','EMP')"
#exclude=function,procedure,table:"='BONUS'"
job_name=expdp-scott2

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:58:59 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."expdp-scott2":  scott/******** parfile=expdp2.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.984 KB       6 rows
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
Master table "SCOTT"."expdp-scott2" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.expdp-scott2 is:
  /home/oracle/dbbackup/expdp-scott2.dmp
Job "SCOTT"."expdp-scott2" successfully completed at 23:59:26


-- EXCLUDE操作,以下参数不备份FUNCTION,PROCEDURE以及BONUS表

[oracle@dg dbbackup(00:00:11)]$more expdp3.par
userid=scott/tiger
directory=backup_dir
dumpfile=expdp-scott3.dmp
logfile=expdp-scott3.log
content=all
exclude=function,procedure,table:"='BONUS'"
job_name=expdp-scott3

[oracle@dg dbbackup(00:00:11)]$ expdp parfile=expdp3.par

Export: Release 11.2.0.3.0 - Production on Sun Sep 29 00:00:41 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."expdp-scott3":  scott/******** parfile=expdp3.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 54.56 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TAB_DR"                            38.00 MB 1194928 rows
. . exported "SCOTT"."GGS_DDL_HIST"                      2.900 MB    1488 rows
. . exported "SCOTT"."GGS_MARKER"                        766.8 KB     615 rows
. . exported "SCOTT"."CHECKPOINT"                        10.26 KB       2 rows
. . exported "SCOTT"."DEPT"                              5.984 KB       6 rows
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
. . exported "SCOTT"."EMPLOYEE"                          5.460 KB       1 rows
. . exported "SCOTT"."GGS_DDL_HIST_ALT"                  7.539 KB      52 rows
. . exported "SCOTT"."GGS_SETUP"                         5.546 KB       6 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."SQLLDR"                            7.710 KB      31 rows
. . exported "SCOTT"."TAB_OGG_DLL"                       5.445 KB       2 rows
. . exported "SCOTT"."CHECKPOINT_LOX"                        0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_COLUMNS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_LOG_GROUPS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_OBJECTS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PARTITIONS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PRIMARY_KEYS"                  0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES"                         0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES_LOG"                     0 KB       0 rows
Master table "SCOTT"."expdp-scott3" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.expdp-scott3 is:
  /home/oracle/dbbackup/expdp-scott3.dmp
Job "SCOTT"."expdp-scott3" successfully completed at 00:01:55

-- EXCLUDE操作,以下参数不备份FUNCTION,PROCEDURE和以TAB开头的表
[oracle@dg dbbackup(00:03:19)]$ more expdp4.par
cuserid=scott/tiger
directory=backup_dir
dumpfile=expdp-scott4.dmp
logfile=expdp-scott4.log
content=all
exclude=function,procedure,table:"like 'TAB%'"
job_name=expdp-scott4


Export: Release 11.2.0.3.0 - Production on Sun Sep 29 00:03:32 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."expdp-scott4":  scott/******** parfile=expdp4.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9.5 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."GGS_DDL_HIST"                      3.123 MB    1602 rows
. . exported "SCOTT"."GGS_MARKER"                        809.8 KB     643 rows
. . exported "SCOTT"."CHECKPOINT"                        10.26 KB       2 rows
. . exported "SCOTT"."DEPT"                              5.984 KB       6 rows
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
. . exported "SCOTT"."EMPLOYEE"                          5.460 KB       1 rows
. . exported "SCOTT"."GGS_DDL_HIST_ALT"                  7.640 KB      55 rows
. . exported "SCOTT"."GGS_SETUP"                         5.546 KB       6 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."SQLLDR"                            7.710 KB      31 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."CHECKPOINT_LOX"                        0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_COLUMNS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_LOG_GROUPS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_OBJECTS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PARTITIONS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PRIMARY_KEYS"                  0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES"                         0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES_LOG"                     0 KB       0 rows
Master table "SCOTT"."expdp-scott4" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.expdp-scott4 is:
  /home/oracle/dbbackup/expdp-scott4.dmp
Job "SCOTT"."expdp-scott4" successfully completed at 00:04:29

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/429786/viewspace-776536/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/429786/viewspace-776536/

cuiwu7783
关注
备份数据库的expdp语句_oracle数据泵备份（Expdp命令）
weixin_39793553的博客
12-22 1114
Oracle备份方式主要分为数据泵导出备份、热备份与冷备份三种，今天首先来实践一下数据泵备份与还原。数据泵导出/导入属于逻辑备份，热备份与冷备份都属于物理备份。oracle10g开始推出了数据泵(expdp/impdp)，可以使用并行参数选项，因此，相对于传统的exp命令来说，执行效率更高。一、知晓expdp命令C:\>expdp -helpExport: Release 11.1.0.7....
oracle数据泵导入提示00972,oracle数据库使用expdp指定FLASHBACK_TIME遇到ORA-39150错误 | 信春哥，系统稳，闭眼上线不回滚！...
weixin_39713805的博客
04-14 574
目前正在做一个数据库合并的项目，有一套系统使用分库分表并且读写分离的架构，订单系统和用户系统都是6个写库和6个读库，目前订单系统已经由6个写库合并为3个写库，马上要做用户系统的数据库合并工作，用户系统使用到了OGG，在搭建测试环境时，也要搭建OGG环境。再搭建用户系统的测试数据库时，使用了数据泵的方式，因为要尽量保证所有数据库的数据尽量接近同一时间，指定了FLASHBACK_TIME选项，再导出数...
Oracle数据备份（服务器端）——expdp
宸睿的博客
01-04 6031
expdp属于逻辑备份。逻辑备份是指利用Oracle提供的导出工具，将数据库中选定的记录集或数据字典的逻辑副本以二进制文件的形式储存到操作系统中。逻辑备份的二进制文件称为转储文件，以dmp格式存储。 在Oracle 11g数据库中提供了Data Pump Export(EXPDP)实用程序，实现数据的逻辑备份与恢复。 EXPDP是服务器端程序，其转储文件只能存放在由DIRECTORY对...
oracle数据库备份 expdp和exp详解
最新发布
舒婷(Zoe)
07-25 604
这里可以通过查询参数，select * from dba_directories 查询具体备份文件路径。可以通过cmd操作备份。注意：可以通过cmd操作。
Oracle数据库数据expdp备份
wyz_New的博客
12-17 1179
连接数据库所在服务器，并切换成Oracle用户：su - username 导出当前数据库数据： expdp (用户名)/(密码) directory=(路径) dumpfile="(备份后的文件名)" schemas=(用户名); 例如： expdp username/password directory=CBY dumpfile="filename.dmp" schemas=username...
oracle数据库备份2（expdp）
每天学习一点点
11-28 3513
expdp 数据库用户名称/数据库用户密码@服务器地址/数据库实例名称 schemas=数据库名称 dumpfile=生成的dmp文件名称 directory=生成文件存放路径 logfile=生成log日志文件的名称。下面记录下使用更加高效的expdp命令和impdp，这两个命令同样是用来做数据库备份和还原的，但速度更快，效率更高，缺点是只能用在服务器端进行操作，exp可以用在客户端进行操作。上面命令的含义是创建名称为BACKUP_DIR的文件夹，文件地址为'C:\db_back'
揭秘Oracle数据库备份策略：全方位保障数据安全
[揭秘Oracle数据库备份策略：全方位保障数据安全](https://media.geeksforgeeks.org/wp-content/uploads/20231108161540/JavaScript-Libraries-and-Frameworks.webp) # 1. Oracle数据库备份概述** Oracle数据库备份...
揭秘Oracle数据库备份秘籍：从新手到大师的完整指南
[揭秘Oracle数据库备份秘籍：从新手到大师的完整指南](https://img-blog.csdnimg.cn/img_convert/ca05ff063206e8b3385cd85547523e36.png) # 1. Oracle数据库备份基础 Oracle数据库备份是确保数据完整性和业务连续性...
Oracle数据库备份与恢复大揭秘：掌握备份与恢复的最佳实践
[Oracle数据库备份与恢复大揭秘：掌握备份与恢复的最佳实践](https://res-static.hc-cdn.cn/cloudbu-site/china/zh-cn/zaibei-521/0603-3/1-02.png) # 1. Oracle数据库备份基础 **1.1 备份的重要性** 数据库备份是...
Oracle数据库备份还原原理大揭秘：深入理解备份与还原机制
[Oracle数据库备份还原原理大揭秘：深入理解备份与还原机制](https://img-blog.csdnimg.cn/540a6904ffb8496a8e5cb0728c8d9a94.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1...
揭秘Oracle数据导出秘诀：10个实用技巧助你轻松迁移数据
[揭秘Oracle数据导出秘诀：10个实用技巧助你轻松迁移数据](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/9f3c5592923948598a145f1fd4b32fb5~tplv-k3u1fbpfcp-zoom-in-crop-mark:1512:0:0:0.awebp) ...
IMPDP
u010037629的博客
12-05 740
IMPDP 只能把低版本中的数据导入高版本中 如果高版本导入低版本的话 需要加一个参数 version=11.2.0.4.0 hdsoaware_test create directory BACKUP_DIR as ‘/oracle/oradata/hdsoaware/backup_dir’ 导入impdp [oracle@localhost ~]$ impdp ‘sys/sys as sysd...
expdp/impdp 参数大全
MythColor的专栏
12-17 2659
<br /><br />在expdp help=y中给的例子是有错误的.下面是该参数的详细用法，找不到原出处<br />The examples below are based on:<br />- the demo schema SCOTT that is created with script: $ORACLE_HOME/rdbms/admin/scott.sql<br />- the directory object my_dir that refers to an existing director
expdp impdp中 exclude/include 的使用
anjichan4261的博客
12-02 1909
exclude和include参数能够在使用expdp或impdp是对特定的对象或对象类型进行筛选或过滤。比如因工作的需要导出特定的表或不导出特定 的表、视图以及存储过程、索引、约束、授权统计信息等等。下面将给出expdp或impdp使用exclude和include参数的方法和示例。 一、exclude/include参数用法： EXCLUDE=[obj...
数据泵联合dblink 使用场景
huangliang0703的专栏
08-24 2036
一 、 expdp可以通过dblink来实现把导出文件直接放在客户端 1、在客户端数据库创建db link和要保存数据文件的目录（在system 用户下操作） create database link fbfj connect to system identified BY ora1Q23$AZ using '(DESCRIPTION = (ADDRESS_LIST =
impdp导入时ORA-39125
yangchen8497的专栏
04-18 1534
背景： 最rp
Oracle数据库数据泵(expdp/impdp)进行数据库的备份与恢复
孑木文学的博客
11-26 3108
在实际项目开发过程中，团队协同工作会对开发环境的数据库进行频繁的新增表、修改表结构等操作，时经常会遇到开发环境的数据库与每一位开发者本地数据库不一致的情形，可能连接本地数据库跑项目出现错误的问题，或者防止团队人员对数据库进行严重错误的操作，开发环境的数据库也需要定时的数据备份。本文以Oracle数据库为例使用数据泵进行数据库的备份(导出)与恢复(导入)。
记录一次impdp的过程
hayata的专栏
01-23 3797
记录一次impdp的过程 前几天有一个测试用的库数据不完全，于是从其他地方恢复了一个测试库，使用效率比较高的impdp导入数据的方式，从AIX导入到一台Linux系统，但晚上头晕眼花的，操作比较急，准备工作没做好，碰到了一堆错误，记录一下。 首先删除原来的schema： SQL> drop user erp; drop user erp * ERROR at line 1:
Oracle EXPDP-IMPDP 使用详解
"Oracle的expdp和impdp工具是数据库管理员进行数据导出和导入的常用命令，主要用于数据迁移、备份和恢复等操作。本文主要介绍这两个工具的使用方法及关键选项。” Oracle的expdp（Export Data Pump）和impdp...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值