ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11g expdp增强

11g expdp增强

原创 Linux操作系统 作者:安佰胜 时间:2011-12-12 16:32:34 0 删除 编辑

11g expdp增强
 
----------------------------
1、压缩选项
 
11g中增强了expdp压缩功能
10g中expdp其实已经有了compression关键字用来对备份进行压缩
但可选的选项只有metadata或者none
也就是说只能对metadata压缩
通常这部分数据在备份文件中占的比重都是比较小的
11g中compression允许三个值
all  --所有导出信息全部压缩
data_only  --只压缩导出中的数据部分
metadata_only  --只压缩导出中定义部分
 
 
--测试
--授予directory读写权限
SQL>  grant read,write on directory data_pump_dir to an;
Grant succeeded.
 
--对导出进行压缩
--选择全部压缩
--expdp预估导出文件将有4m
--实际生成的文件只有360k
--测试数据是连续的数字,所以压缩比相当的高
 
[oracle@localhost dpdump]$ expdp "' / as sysdba'" dumpfile=an_comp.dmp schemas=an compression=all
Export: Release 11.2.0.1.0 - Production on Mon Dec 12 11:48:20 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
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 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" dumpfile=an_comp.dmp schemas=an compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 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/TABLE/TABLE
. . exported "AN"."T_ATEST"                              301.7 KB  320000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /opt/app/oracle/admin/db11/dpdump/an_comp.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:49:17
[oracle@localhost dpdump]$
[oracle@localhost dpdump]$ ls -ltr
total 368
-rw-r--r-- 1 oracle dba   1266 Dec 12 11:49 export.log
-rw-r----- 1 oracle dba 360448 Dec 12 11:49 an_comp.dmp
 
------------------------
 
--不使用压缩选项对同样的数据进行导出操作
--导出文件问2.7m,同样低于程序预估的4m
--对导出的没有进行压缩的文件进行gzip压缩
--压缩后文603k
--在这个测试中expdp自带的压缩功能明显优于gzip压缩
--压缩比可能和数据有关,所以其他数据压缩比和这个测试结果未必相同
 
[oracle@localhost dpdump]$ expdp "' / as sysdba'" dumpfile=an_nocomp.dmp schemas=an             
Export: Release 11.2.0.1.0 - Production on Mon Dec 12 11:49:34 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
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 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" dumpfile=an_nocomp.dmp schemas=an
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 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/TABLE/TABLE
. . exported "AN"."T_ATEST"                              2.440 MB  320000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /opt/app/oracle/admin/db11/dpdump/an_nocomp.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:50:28
[oracle@localhost dpdump]$ ls -ltr
total 3036
-rw-r----- 1 oracle dba  360448 Dec 12 11:49 an_comp.dmp
-rw-r--r-- 1 oracle dba    1254 Dec 12 11:50 export.log
-rw-r----- 1 oracle dba 2723840 Dec 12 11:50 an_nocomp.dmp
[oracle@localhost dpdump]$ gzip an_nocomp.dmp
[oracle@localhost dpdump]$ ls -ltr
total 968
-rw-r----- 1 oracle dba 360448 Dec 12 11:49 an_comp.dmp
-rw-r--r-- 1 oracle dba   1254 Dec 12 11:50 export.log
-rw-r----- 1 oracle dba 603936 Dec 12 11:50 an_nocomp.dmp.gz

-----------------------------------
 
2、加密选项
 
2.1、加密对象
expdp命令可以指定关键字encryption关键字
关键字可选值如下:
all   --加密全部数据
data_only   --只加密数据部分
metadata_only   --值加密元数据部分
encrypted_column_only   --只有被加密的列以加密格式写出,使用时还需要使用参数encryption_alogorithm指定加密方式,如aes128、aes192、aes256
 
2.2加密方式
encryption_mode可选住有三个
dual
password
transparent
transparent方式:
如果oracle wallet是打开的,使用oracle wallet密码进行加密和解密
无需人工干预
password方式:
手工指定加密密码
dual方式:
手工方式

--使用系统认证的/ as sysdba代替登录密码
--指定备份文件为an_nocomp.dmp
--导出schema  an
--对所有导出数据进行加密
--加密方式为dual
--加密密码为Anbaisheng1
--加密标准aes256
--一旦备份文件存在就将文件重用
 
[oracle@localhost dpdump]$ expdp "' / as sysdba'" dumpfile=an_nocomp.dmp schemas=an encryption=all encryption_mode=dual encryption_password=Anbaihseng1 encryption_algorithm=aes256 reuse_dumpfiles=y
Export: Release 11.2.0.1.0 - Production on Mon Dec 12 14:46:23 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
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 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" dumpfile=an_nocomp.dmp schemas=an encryption=all encryption_mode=dual encryption_password=******** encryption_algorithm=aes256 reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 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/TABLE/TABLE
. . exported "AN"."T_ATEST"                              2.440 MB  320000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /opt/app/oracle/admin/db11/dpdump/an_nocomp.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:47:14
 
--删除表之后重新导入
--导入语句必须指定encryption_password
--并且必须保证加密密码的正确性,确认之后才能导入数据

[oracle@localhost dpdump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 14:50:16 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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
 
SQL> drop table an.t_atest;
Table dropped.
 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
[oracle@localhost dpdump]$ impdp "'/ as sysdba'" dumpfile=an_nocomp.dmp encryption_password=Anbaisheng1
 
 
Import: Release 11.2.0.1.0 - Production on Mon Dec 12 14:50:33 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
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
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" dumpfile=an_nocomp.dmp encryption_password=********
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"AN" already exists
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "AN"."T_ATEST"                              2.440 MB  320000 rows
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:50:42
 
[oracle@localhost dpdump]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 14:50:55 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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
 
SQL> select count(*) from an.t_atest;
  COUNT(*)
----------
    320000
-----------------------------
 
3、重映射数据
 
impdp时可以根据定义的规则重新映射输出到数据到指定问位置
通过remap_data在expdp的同事就已经替换了敏感数据
能够更好的保护敏感数据
对于银行或者其他类似于要保护登录密码的应用来说是很有意义的
 
--创建测试数据
--两个用户an和ab
--假定an用户aa表中id字段数据位敏感数据
--将其替换导出
conn an/Anbaisheng1
create table aa(id int);
insert into aa select 1 from dual;
insert into aa select 2 from dual;
commit;
conn ab/Anbaisheng1
create table bb as select * from an.aa where 1=2;
 
--创建替换的包和包体
create or replace package rep_an
is
function f_rep (p_in in number) return number;
end;
/
create or replace package body rep_an
as
function f_rep (p_in in number) return number
is
out_name number;
begin
 out_name:=16910;
 return out_name;
end;
end;
/
 
--导出和导入数据
--导出是实用关键字remap_data
expdp an/Anbaisheng1 dumpfile=an1.dmp  tables=aa remap_data=an.aa.id:rep_an.f_rep directory=DATA_PUMP_DIR
impdp ab/Anbaisheng1 dumpfile=an1.dmp  remap_schema=an:ab  directory=DATA_PUMP_DIR TABLE_EXISTS_ACTION=append
 
--确认导入的数据已经被替换
SQL> select * from ab.aa;
        ID
----------
     16910
     16910
------------------------
 
4、重映射表
 
11g提供remap_table功能将一个表导入到另一个表中
可以使用这个功能来更改表名
不过这个功能再使用时还是有些限制的:
以可移植方式导出分区表,每一个分区和子分区都可以对应到表
表已经存在的话不能被移植,只能移植到目前不存在的表
导出必须是以一种费可移植的方式进行
 
--导出an.aa
expdp an/Anbaisheng1 dumpfile=an1.dmp  tables=aa directory=DATA_PUMP_DIR
--将导出数据重新定向输出到新表an.abc中
impdp an/Anbaisheng1 dumpfile=an1.dmp remap_table=an.aa:an.abc directory=DATA_PUMP_DIR
-----------------------
 
 

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

请登录后发表评论 登录
全部评论

注册时间:2009-08-26

  • 博文量
    215
  • 访问量
    607567