实战:expdp/impdp的参数灵活应用，达事半功倍效果

需求：Oracle生产环境12.2.0.1，测试环境11.2.0.4，操作系统均为centos6.x

由于开发需要在测试环境搭测试库，库名和生产相同，但由于条件限制，只能在11.2.0.4上建库。
考虑到数据量太大问题，需要库表结构一致，对于数据：大部分表需有1000条记录即可，个别表需要全量数据。

处理思路:

1.建测试库，调整表空间等
2.expdp导数据，scp/sftp到测试机
3.测试机impdp导入对象和数据(先导全库对象，然后再导全量表replace方式），编译失效对象

操作步骤:

1.测试环境建库，一条命令搞定

测试库存放路径空间检查足够，例如：/oracle/oradata
注意字符集–源端提前检查,任选一种SQL查询即可

select userenv('language') from dual;
select * from props$;
select * from database_properties;

例如
characterSet ZHS16GBK
nationalCharacterSet AL16UTF16
memory_target根据物理内存实际大小分配：比如32G，分配3G，可设置memoryPercentage 10即可，后期可调整

su - oracle
cd $ORACLE_HOME/assistants/dbca/templates
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oradb -sid oradb -sysPassword xxxxxx -systemPassword xxxxxx -responseFile NO_VALUE -datafileDestination /oracle/oradata -redoLogFileSize 200 -recoveryAreaDestination NO_VALUE -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema false -memoryPercentage 10 -databaseType OLTP -emConfiguration NONE
防止密码过期锁定：
alter profile default limit failed_login_attempts unlimited;
alter profile default limit password_life_time unlimited;

源端检查表空间，根据表空间名称和大小，在测试库提前建好

set line 132
set wrap off
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;

2.生产库导出

测试和生产都可提前准备dmp目录，例如

create directory dmp as '/oracle/dmp';
grant read,write on directory dmp to public;

生成需要导出的用户脚本，方便expdp调用。

select LISTAGG(username,',') within group (order by username) from dba_users where account_status='OPEN' and username not in('SYSTEM','SYS','OGGADM','WKSYS','OLAPSYS','DBSNMP','MGMT_VIEW','SYSMAN');
例如：AAA,BBB,CCC
编辑t.par文件(无需转义字符)：注意并行度parallel的选择，建议1/4的cpu数，确保cpu idle大于70%（sar 1 10可参考），对系统影响较小，排除多个内容，并列写exclude即可,对于高版本到低版本需要注意加version=11.2，否则无法导入。
version=11.2
schemas=AAA,BBB,CCC
directory=dmp
dumpfile=oradb_%U.dmp
logfile=oradb_20220119.log
parallel=4
cluster=n
exclude=statistics
query="where rownum<=1000"
exclude=TABLE:"LIKE'%201%'" 
exclude=TABLE:"in ('XXX_LOG','XXX_LOG_20090901','TMP0901_1','XXX_LOG161206','DE_WHOLE_ROUTE_LOG161010')"
导出直接调用（用sysdba好处是无需密码，同时可导出public的同义词,dblink等对象
第1次导出不超过1000条的符合要求的库表对象：
expdp \'/ as sysdba\' parfile=t.par
如果不使用parfile，那么需要添加转义字符\，例如：
exclude=TABLE:\"LIKE\'%201%\'\"  exclude=TABLE:\"in \(\'XXX_LOG\',\'XXX_LOG_20090901\',\'TMP0901_1\',\'XXX_LOG161206\',\'DE_WHOLE_ROUTE_LOG161010\'\)\"
注意字母要大写，同时不要加用户名.形式，只需要大写表名。对于windows不要用powershell窗口操作（命令会报错），而应用cmd的dos窗口。
第2次导出指定全量数据的表：(换一种形式写)
expdp \'/ as sysdba\' tables=AAA.T1,BBB.T2,CCC.T3 dumpfile=oradb_tab.dmp logfile=oradb_tab_20220119.log directory=dmp version=11.2 exclude=statistics

生成的dmp赋权，并压缩，目的是减少网络传输时间
chmod 777 /oracle/dmp/oradb*.dmp
tar zcvf oradb_20220119.tar.gz oradb*.dmp
传输到测试机：
scp oracle@生产库IP:/oracle/dmp/oradb_20220119.tar.gz /oradata/dmp/

3.测试库导入

解压tar zxvf oradb_20220119.tar.gz
对于涉及dblink的库，建议导入之前先排除掉（加快速度的关键），避免导入过程中如果有视图，存储过程，包体调用到的话，可能卡半天（每个编译涉及link连接需要等待超时以后才会继续导入）

导入的几种方式参考：
第1次导入所有对象:
最简单的：
impdp \'/ as sysdba\' directory=dmp full=y dumpfile=oradb_%U.dmp parallel=4 cluster=n logfile=zydb_20220118-ok.log exclude=db_link
其次是参考expdp导出时的参数：
impdp \'/ as sysdba\' directory=dmp schemas=AAA,BBB,CCC dumpfile=oradb_%U.dmp parallel=4 cluster=n logfile=oradb_20220118-1.log exclude=db_link

第2次导入指定全量表：注意 TABLE_EXISTS_ACTION=REPLACE或TRUNCATE使用，对于有外键情况可能需要考虑先禁用外键，根据实际情况处理。
impdp \'/ as sysdba\' directory=dmp full=y cluster=n dumpfile=oradb_tab.dmp parallel=1 logfile=oradb_tab_20220119-imp.log TABLE_EXISTS_ACTION=REPLACE
同样也可以指定表写法:
impdp \'/ as sysdba\' tables=AAA.T1,BBB.T2,CCC.T3 dumpfile=oradb_tab.dmp logfile=oradb_tab_20220119-imp.log directory=dmp TABLE_EXISTS_ACTION=REPLACE
最后可考虑是否导入dblink，注意parallel=1 content=metadata_only include=db_link参数

impdp \'/ as sysdba\' directory=dmp full=y cluster=n dumpfile=oradb_tab.dmp cluster=n logfile=oradb_20220118-1.log  parallel=1 content=metadata_only include=db_link

排除系统默认用户的命令参考：
expdp \'/ as sysdba\' directory=DPUMP_DIR1 dumpfile=full_meta.dmp LOGFILE=full_meta.log full=y content=metadata_only PARALLEL=1 CLUSTER=N EXCLUDE=TABLE,INDEX,SEQUENCE,STATISTICS EXCLUDE=SCHEMA:\"in \(\'SYS\',\'SYSTEM\',\'OWBSYS_AUDIT\',\'OWBSYS\',\'APEX_PUBLIC_USER\',\'APEX_030200\',\'FLOWS_FILES\',\'SPATIAL_CSW_ADMIN_USR\',\'SPATIAL_WFS_ADMIN_USR\',\'MDDATA\',\'OLAPSYS\',\'ORDDATA\',\'ORDPLUGINS\',\'SI_INFORMTN_SCHEMA\',\'MDSYS\',\'ORDSYS\',\'XDB\',\'ANONYMOUS\',\'CTXSYS\',\'EXFSYS\',\'WMSYS\',\'APPQOSSYS\',\'DBSNMP\',\'ORACLE_OCM\',\'DIP\',\'OUTLN\',\'SYSMAN\'\)\"

编译失效对象

conn /as sysdba
@?/rdbms/admin/utlrp.sql

小结：

上述参数方法在日常迁移中比较常用，不限于此，如果熟悉了expdp/impdp的灵活用法，对于迁移库来说便可事半功倍！