1.创建备份目录
[root@rac1 ~]# cd /u01
[root@rac1 u01]# mkdir backup
[root@rac1 u01]# ls
app backup
[root@rac1 u01]# chown oracle:oinstall backup
2.准备数据
SQL>select * from dba_data_files;
SQL> create tablespace leo datafile'+DATA/leo/datafile/cndba01.dbf' size 500m;
Tablespace created.
SQL> create user leo identified by leo ;
User created.
SQL> grant connect,resource to leo;
Grant succeeded.
SQL> conn leo/leo
Connected.
SQL> create table leo tablespace leo as select * from all_objects;
Table created.
SQL> select count(*) from leo;
COUNT(*)
----------
68339
SQL> create directory backup as '/u01/backup';
Directory created.
SQL> grant read,write on directory backup to leo;
Grant succeeded.
3数据泵基本操作
3.1整库库导出与导入
expdp /'/as sysdba/' directory=backup dumpfile=leofull.dmp logfile=leofull.log full=y
--删除表空间进行测试
SQL> drop tablespace leo including contents and datafiles;
Tablespace dropped.
impdp /'/as sysdba/' directory=backup dumpfile=leo.dmp logfile=leoimp.log full=y
--数据恢复成功
SQL> select count(*) from leo;
COUNT(*)
----------
68340
3.2用户级别导出与导入
--导出用户
两种方式
expdp /'/as sysdba/' schemas=leo directory=backup dumpfile=leo2.dmp logfile=leo2.log
--用户leo 要赋操作目录的权限
expdp leo/leo schemas=leo directory=backup dumpfile=leo.dmp logfile=leoexp.log
--导入用户
--同库测试先
SQL> drop user leo cascade;
User dropped.
SQL> conn /as sysdba
Connected.
--导入用户之前要先建用户
SQL> create user leo identified by leo ;
User created.
SQL> grant read,write on directory backup to leo;
Grant succeeded.
impdp /'/as sysdba/' schemas=leo directory=backup dumpfile=leo.dmp logfile=leoimp.log
--用户leo 要赋操作目录的权限
impdp leo/leo schemas=leo directory=backup dumpfile=leo.dmp logfile=leoimp.log
SQL> select count(*) from leo;
COUNT(*)
----------
68339
3.3表级导出与导入
导出表
expdp /'/as sysdba/' directory=backup dumpfile=leotable.dmp logfile=leotable.log tables=leo.leo
expdp leo/leo directory=backup dumpfile=leotable2.dmp logfile=leotable2.log tables=leo
导入表
本库测试
drop table leo;
impdp /'/as sysdba/' directory=backup dumpfile=leotable.dmp logfile=leotable.log tables=leo.leo
impdp leo/leo directory=backup dumpfile=leotable2.dmp logfile=leotable2.log tables=leo
SQL> select count(*) from leo;
COUNT(*)
----------
68339
4.4表空间导出与导入
expdp /'/as sysdba/' directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo
[oracle@rac1 ~]$ expdp /'/as sysdba/' directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo
Export: Release 11.2.0.4.0 - Production on Wed May 3 11:00:27 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "LEO"."LEO" 6.706 MB 68340 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/u01/backup/tbspace1.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed May 3 11:00:34 2017 elapsed 0 00:00:05
SQL> drop tablespace leo including contents and datafiles;
Tablespace dropped.
--在导入表空间之前要先建表空间，在导入表空间回复表空间数据
SQL> create tablespace leo datafile'+DATA/cndba/datafile/cndba01.dbf' size 100m;
Tablespace created.
impdp /'/as sysdba/' directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo
[oracle@rac1 backup]$ impdp /'/as sysdba/' directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo
Import: Release 11.2.0.4.0 - Production on Wed May 3 11:06:44 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LEO"."LEO" 6.706 MB 68340 rows
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at Wed May 3 11:06:48 2017 elapsed 0 00:00:02
SQL> select count(*) from leo;
COUNT(*)
----------
68339
5.5导入过程中指定remap user 和 remap tablespace 以及忽略remap功能的TRANSFORM=segment_attributes:n 使用。
--remap user
SQL> create user leo1 identified by leo1;
User created.
SQL> grant read,write on directory backup to leo1;
Grant succeeded.
SQL> grant connect,resource to leo1;
Grant succeeded.
SQL> exit
[oracle@rac1 backup]$ impdp leo1/leo1 remap_schema=leo:leo1 directory=backup dumpfile=leo.dmp logfile=leoexp.log
Import: Release 11.2.0.4.0 - Production on Wed May 3 11:44:10 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "LEO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "LEO1"."SYS_IMPORT_FULL_01": leo1/******** remap_schema=leo:leo1 directory=backup dumpfile=leo.dmp logfile=leoexp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LEO1"."LEO" 6.706 MB 68340 rows
Job "LEO1"."SYS_IMPORT_FULL_01" successfully completed at Wed May 3 11:44:15 2017 elapsed 0 00:00:03
[oracle@rac1 backup]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 3 11:44:26 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn leo1/leo1
Connected.
--remap tablespace
SQL> set lines 200
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='LEO';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
LEO USERS TEMP
SQL> select count(*) from leo;
COUNT(*)
----------
68340
expdp /'/as sysdba/' directory=backup dumpfile=leo_user.dmp schemas=leo1
SQL> drop user leo1 cascade;
User dropped.
SQL> SET LINES 200
impdp /'/as sysdba/' directory=backup dumpfile=leo_user.dmp logfile=leoimp.log remap_tablespace=users:leo
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='LEO1';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
LEO1 LEO TEMP
--忽略remap功能的TRANSFORM=segment_attributes:n 使用 参数说明参考链接：
http://www.cndba.cn/Expect-le/article/1890
SQL> set long 9999;
SQL> SELECT dbms_metadata.get_ddl('TABLE','LEO','LEO') FROM dual;
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO')
--------------------------------------------------------------------------------
CREATE TABLE "LEO"."LEO"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER NOT NULL ENABLE,
"EDITION_NAME" VARCHAR2(30)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "LEO"
expdp /'/as sysdba/' dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo
SQL> drop tablespace leo including contents and datafiles;
Tablespace dropped.
不加TRANSFORM=SEGMENT_ATTRIBUTES:N:table：
看到了报错：提示leo表空间不存在，无法导入
[oracle@rac1 ~]$ impdp /'/as sysdba/' dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo
Import: Release 11.2.0.4.0 - Production on Wed May 3 10:45:30 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"LEO"."LEO" failed to create with error:
ORA-00959: tablespace 'LEO' does not exist
Failing sql is:
CREATE TABLE "LEO"."LEO" ("OWNER" VARCHAR2(30 BYTE) NOT NULL ENABLE, "OBJECT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPOR
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed May 3 10:45:34 2017 elapsed 0 00:00:02
加TRANSFORM=SEGMENT_ATTRIBUTES:N:table： 使用默认表空间users
[oracle@rac1 ~]$ impdp /'/as sysdba/' dumpfile=leo.dmp logfile=imp_leo.log directory=backup tables=leo.leo TRANSFORM=SEGMENT_ATTRIBUTES:N:table
Import: Release 11.2.0.4.0 - Production on Wed May 3 10:46:18 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=leo.dmp logfile=imp_leo.log directory=backup tables=leo.leo TRANSFORM=SEGMENT_ATTRIBUTES:N:table
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LEO"."LEO" 6.706 MB 68340 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed May 3 10:46:22 2017 elapsed 0 00:00:03
SQL> SELECT dbms_metadata.get_ddl('TABLE','LEO','LEO') FROM dual;
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO')
--------------------------------------------------------------------------------
CREATE TABLE "LEO"."LEO"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO')
--------------------------------------------------------------------------------
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER NOT NULL ENABLE,
"EDITION_NAME" VARCHAR2(30)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO')
--------------------------------------------------------------------------------
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"