DB-hub Technology Oracle Lab – Data Pump

Oracle Lab – Data Pump

1.创建测试环境

#!/bin/bash

if [ -f "./create_tables.sql" ] 
then
    rm -f ./create_tables.sql
fi

for i in {1..150};
do
    # create a test table
    RANDOM_NAME=`date +%s%N | md5sum | head -c 10`
    TABLE_NAME=STAGE_#{RANDOM_NAME}
    echo "create table SALES.#{TABLE_NAME} (ID char(32), CLIENT_NAME varchar(30));" >> create_tables.sql

    # insert one row
    SALES_ID=`cat /proc/sys/kernel/random/uuid | sed 's/-//g'`
    RANDOM_NAME=`date +%s%N | md5sum | head -c 10`
    echo "insert into SALES.#{TABLE_NAME} values('#SALES_ID','#RANDOM_NAME');" >> create_tables.sql

    # insert another row
    SALES_ID=`cat /proc/sys/kernel/random/uuid | sed 's/-//g'`
    RANDOM_NAME=`date +%s%N | md5sum | head -c 10`
    echo "insert into SALES.#{TABLE_NAME} values('#SALES_ID','#RANDOM_NAME');" >> create_tables.sql
done

sqlplus / as sysdba <<EOF
-- create a schema
declare
    userexist integer;
begin
    select count(*) into userexist from dba_users where username='SALES';
    if (userexist = 0) then
        execute immediate 'create user SALES identified by sales';
        execute immediate 'alter user SALES quota unlimited on USERS';
    else
        execute immediate 'drop user SALES cascade';
        execute immediate 'create user SALES identified by sales';
        execute immediate 'alter user SALES quota unlimited on USERS';
    end if;
end;
/

--create test tables
@create_tables.sql

exit
EOF

2.创建DUMP目录

SQL> col DIRECTORY_NAME for a30
SQL> col DIRECTORY_PATH for a70
SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ----------------------------------------------------------------------
IMP_TBS                        /bck/import
XMLDIR                         /oracle/app/oracle/product/12.2.0.1/db/rdbms/xml
XSDDIR                         /oracle/app/oracle/product/12.2.0.1/db/rdbms/xml/schema
ORA_DBMS_FCP_LOGDIR            /oracle/app/oracle/product/12.2.0.1/db/cfgtoollogs
ORA_DBMS_FCP_ADMINDIR          /oracle/app/oracle/product/12.2.0.1/db/rdbms/admin
OPATCH_INST_DIR                /oracle/app/oracle/product/12.2.0.1/db/OPatch
ORACLE_OCM_CONFIG_DIR          /oracle/app/oracle/product/12.2.0.1/db/ccr/state
DATA_PUMP_DIR                  /oracle/app/oracle/admin/SLOTH/dpdump/
ORACLE_OCM_CONFIG_DIR2         /oracle/app/oracle/product/12.2.0.1/db/ccr/state
OPATCH_SCRIPT_DIR              /oracle/app/oracle/product/12.2.0.1/db/QOpatch
OPATCH_LOG_DIR                 /oracle/app/oracle/product/12.2.0.1/db/rdbms/log
ORACLE_BASE                    /oracle/app/oracle
ORACLE_HOME                    /oracle/app/oracle/product/12.2.0.1/db

CREATE DIRECTORY DUMP_DIR AS '/home/oracle';
grant read,write on directory DUMP_DIR to dba_6579;

3.创建parameter file

select table_name from dba_tables where owner='SALES';

如果导入和导出数据库版本不一致，请使用version参数。参数值是导入数据库的版本号。
expdp导出的时候，导出的表之间并不能保证一致性，每张表都有自己的scn。如果用户有一定要保证导出表一致性的需求，除了建议在停止限制数据库读写访问再做导出外，oracle官方也提供了以下两个参数保证expdp导出的一致性：
- 使用参数 flashback_scn或者flashbackup_time来指定数据导出的时间点或scn点。
- 使用consistent=y参数。这个参数其实是转换成了FLASHBACK_TIME，导出时间为SYSTIMESTAMP时间，即当时数据库时间。
  Oracle检测到参数 consistent=y 会转换为 flashback_time=TO_TIMESTAMP(‘2016-03-22 08:22:06’, ‘YYYY-MM-DD HH24:MI:SS’)
  使用的是 flashback query 技术，如果 undo_retention 设置过小或者没有 retention guarantee ，会报ORA-01555错误。

exp_tables.par

DIRECTORY = DUMP_DIR
DUMPFILE = trans_tables.dmp
LOGFILE =  trans_tables.log
CONSISTENT=Y
VERSION = 12.2.0.1.0
schemas=SALES
include=table:"IN (
'STAGE_5527531260',
'STAGE_500BEDD6DB',
'STAGE_F41863B607',
'STAGE_C1ADEC21D3',
'STAGE_7BF38E3D97',
'STAGE_3D1E931F68',
'STAGE_15689E75AA',
'STAGE_F6CC6B1F11',
'STAGE_AF8CE0E06B',
'STAGE_68C09CE90E',
'STAGE_4DE9865AC9',
'STAGE_EE102AD472',
'STAGE_F04C530AF1',
'STAGE_C3EE12D002',
'STAGE_66D0AC4B8A',
'STAGE_9C750AEC7D',
'STAGE_AF7794D014',
'STAGE_AB374939EB',
'STAGE_D4D4CE690C',
'STAGE_A68B985193',
'STAGE_4F4E829D16',
'STAGE_76DC110AAB',
'STAGE_6B40F90EF0',
'STAGE_55509F16ED',
'STAGE_610E5CD70B',
'STAGE_8E76F24F32',
'STAGE_FE8C0F66A8',
'STAGE_CB21251B4F',
'STAGE_FBD6C8E441',
'STAGE_4575DB9BD0',
'STAGE_F5B29DADAC',
'STAGE_64B37A0D61',
'STAGE_91AB8D7DDF',
'STAGE_B3451080E3',
'STAGE_587A6E1143',
'STAGE_76AF34E001',
'STAGE_97D6B97ED8',
'STAGE_059EAB9A88',
'STAGE_279A7E8AE6',
'STAGE_D8AAD4D7D4',
'STAGE_E9F4DBB61C',
'STAGE_1EB5646BE0',
'STAGE_6EAF71A51D',
'STAGE_7C5C742325',
'STAGE_80876B46AB',
'STAGE_E5677CB805',
'STAGE_7B3B557C76',
'STAGE_3C90DB183C',
'STAGE_D134613B6C',
'STAGE_B1F75BD3A9',
'STAGE_DBC0C3800A',
'STAGE_4C3477A1FD',
'STAGE_A13E44E855',
'STAGE_55EB835F82',
'STAGE_477A10080E',
'STAGE_B90377C914',
'STAGE_D9BBAE1CE9',
'STAGE_A77123BA62',
'STAGE_AAEBB28910',
'STAGE_82884A005D',
'STAGE_098400271F',
'STAGE_8DEC8915BD',
'STAGE_7D8895CAC5',
'STAGE_8AA81E41DB',
'STAGE_199383178B',
'STAGE_1DBEF84A80',
'STAGE_A31BAA636D',
'STAGE_DCEC0FFA1E',
'STAGE_448F053D2A',
'STAGE_0626AD292B',
'STAGE_B64E761890',
'STAGE_123C157071',
'STAGE_F071FD5692',
'STAGE_372C638C3F',
'STAGE_3BF38A7466',
'STAGE_F2E0430548',
'STAGE_6B0E28095D',
'STAGE_9DC6739BD1',
'STAGE_A07B9AD439',
'STAGE_461BAAC3FB',
'STAGE_E57C7C36DD',
'STAGE_EAB3B868EA',
'STAGE_9DA9E5BF6D',
'STAGE_CBDD1FE8A9',
'STAGE_2C6FA55E26',
'STAGE_0929E140C5',
'STAGE_38B8C374CD',
'STAGE_7A2FD88AFD',
'STAGE_B1BF45D027',
'STAGE_4D53C29D38',
'STAGE_DA3DECF137',
'STAGE_125831EC22',
'STAGE_10E3BDEDEB',
'STAGE_5E65066F18',
'STAGE_6840914AE1',
'STAGE_662BB13BDB',
'STAGE_EB98266FDA',
'STAGE_8490E10137',
'STAGE_A44E2B6145',
'STAGE_64D2C7F1B4',
'STAGE_79319F2B11',
'STAGE_57C11C785C',
'STAGE_2A98589C63',
'STAGE_6B70F6B07E',
'STAGE_79F7CFA09A',
'STAGE_C9407A78B7',
'STAGE_F4D9964E3E',
'STAGE_216118F547',
'STAGE_6299D586C3',
'STAGE_9121B971A8',
'STAGE_07068A972B',
'STAGE_3E25E4E8DD',
'STAGE_952799C155',
'STAGE_42FCEF699A',
'STAGE_2067D67B50',
'STAGE_687DF65942',
'STAGE_7C1E382781',
'STAGE_5CEEE67741',
'STAGE_90A37842E4',
'STAGE_AA5FD4C433',
'STAGE_8EB5B0288B',
'STAGE_CF045D3077',
'STAGE_19415B6E37',
'STAGE_AD53E5F538',
'STAGE_B2D1ED8AD2',
'STAGE_99678F841B',
'STAGE_0CA6520274',
'STAGE_F71A006B5C',
'STAGE_1281F2CF8F',
'STAGE_2BE541FBE1',
'STAGE_818A7D7B09',
'STAGE_71CE24A26A',
'STAGE_3CD38824A8',
'STAGE_4E44E9C3DA',
'STAGE_1C455B05CB',
'STAGE_58FC20C496',
'STAGE_F22E088BA4',
'STAGE_807317BB42',
'STAGE_734F8D236A',
'STAGE_326A061EB1',
'STAGE_B290248E4C',
'STAGE_6ED09CC2D6',
'STAGE_808EB49F8C',
'STAGE_7947925661',
'STAGE_0FB8933659',
'STAGE_0ED1AB5968',
'STAGE_BAEF5B9088',
'STAGE_7B9BEC54CE',
'STAGE_35A2AD32D2',
'STAGE_F3232E95CC')"

4.导出

nohup expdp  dba_6579/dba_6579  PARFILE=exp_tables.par > expdp.log &

expdp导出的时候，假如对一组有着主外键的表的父表做了删除操作，那oracle是怎么保证导入到其它环境的时候，主表及子表的主外键关系正常的呢？
Oracle默认在导出数据的时候，按照顺序先导出父表，然后再导出子表，这样无论对父表做任何操作，父表的数据都是早于子表的。