目录
1、数据泵
单独使用数据泵(expdp、impdp)做表的全量迁移,通过指定表名,即可将表的定义、主键、索引、外键、CHECK约束和全量数据导出到一个文件,在另一个数据库导入文件,这是一种逻辑层面的导出导入,可以跨Oracle版本,适合12c到19c的迁移。
导出:
expdp paastest/Paas1015 dumpfile=mk_iclul_mnt_sn_hs.dmp tables=MK_ICLUL_MNT_SN_HS directory=workdir
directory选项是在sqlplus中创建的目录对象,例如:
create directory workdir as '/mnt/disk01/u01/app/oracle/workdir/';
如果不指定directory选项,默认导出文件在$ORACLE_BASE/admin/$ORACLE_SID/dpdump/mk_iclul_mnt_sn_hs.dmp
tables选项可以指定多个表,例如:
tables=“MK_ICLUL_MNT_SN_HS,JOB_TRIGGER_LOG”
导入:
impdp paastest/Paas1015 dumpfile=job_trigger_log.dmp directory=workdir
注意,最好使用table模式,指定具体表名列表,而不是整个用户的schema,这样更容易控制,出错也更容易解决,尽管这样有时命令中会有上百个表,下面impdp+dblink也是一样。
2、impdp+dblink
在目标数据库执行impdp命令时,指定选项network_link=<database link名称>(事先配置好dblink),则可将表从源数据库直接导入目标数据库。这样跳过了导出到文件,再从文件导入的步骤。
而且,数据泵+dblink可以支持CLOB、BLOB类型的导入。(公司的数据库中许多表都定义了CLOB和BLOB类型的列,经测试支持导出导入且源端和目标端CLOB和BLOB类型的数据内容一致。)
在目标数据库,创建到源数据库的TNS连接项:
vim network/admin/tnsnames.ora
PAAS_TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.32.150.13)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nlpass01)
)
)
sqlplus paastest/Paas1015@paas_test
在目的数据库中,创建到源数据库的database link:
create public database link paas_test_link connect to paastest identified by Paas1015 using 'PAAS_TEST';
在目的数据库中,执行impdp+dblink导入:
impdp paastest/Paas1015 network_link=paas_test_link tables="t1,t2,t3" cluster=no exclude=statistics,grant parallel=2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
注意(对单独使用数据泵和数据泵+dblink都适用):
a) 源数据库与目标数据库字符集需要相同,字符集是创建数据库时指定的,创建以后不能改,例如公司生产系统的字符集为ZHS16GBK:
b) 目标数据库中,应创建与源数据库相同的:用户名、权限、表空间。源数据库中,需要迁移的所有表所在的表空间,索引所在表空间,表空间的大小,数据文件的个数和类型(smallfile或bigfile)。这些需要手动在目标数据库创建,原则上一一对应,表空间可以有所扩展。
查看表空间和数据文件:
select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME like 'PAASTEST';
select TABLESPACE_NAME from user_tablespaces;
select file_name, tablespace_name, BYTES/(1000*1000*1000) GB from dba_data_files;
select * from dba_sys_privs where grantee='PAASTEST';
创建表空间
create tablespace tab_paastest datafile '/u01/app/oracle/oradata/NLPASS01/paastest01.dbf' size 31G;
alter tablespace tab_paastest add datafile '/u01/app/oracle/oradata/NLPASS01/paastest02.dbf' size 31G;
create tablespace paas2 datafile '/u01/app/oracle/oradata/NLPASS01/paas2_01.dbf' size 100m autoextend on next 100m;
创建用户
create user paastest identified by Paas1015 default tablespace tab_paastest;
alter user paastest quota unlimited on tab_paastest;
alter user paastest quota unlimited on paas2;
grant connect,resource to paastest;
grant dba to paastest;
3、公司迁移项目注意事项
不迁移统计数据:
数据泵默认会迁移表的统计数据,但是在测试中发现,迁移统计数据会出错(可能是权限原因),由于统计数据不需要完全与源数据库相同,迁移时可以不迁移统计数据,而是在迁移后,在目标数据库重新收集统计数据,使用impdp选项exclude=statistics排除导出统计数据。
在目标数据库导入完成后,重新生成统计数据:
-- 收集paastest用户所有表的统计信息
exec dbms_stats.gather_schema_stats('paastest');
-- 收集一个表的统计信息
dbms_stats.gather_table_stats(‘table_name’);
查看表的统计信息:
select TABLE_NAME,NUM_ROWS,BLOCKS,SAMPLE_SIZE,AVG_ROW_LEN from user_tab_statistics where table_name='QRTZ_TRIGGERS_PK';
不迁移grant:
迁移时,会在目标数据库中,尝试将表的操作权限赋给某些用户,如果目标数据库没有这些用户,会迁移失败。确实如果需要用到这些用户,就在目标数据库创建,如果不需要,可以使用impdp选项exclude=grant排除。
4、监控数据泵状态
对于运行时间很长的expdp和impdp可以监控其运行过程(对单独使用数据泵和数据泵+dblink都适用):
col JOB_NAME for a50
col STATE for a30
select JOB_NAME, STATE from dba_datapump_jobs;
impdp paastest/Paas1015 attach=SYS_IMPORT_FULL_01
>status
stop_job=immediate
kill_job
5、遇到的问题和解决
impdp时会出现ORA-01555: snapshot too old: 参考
expdp error ORA-01555: snapshot too old — oracle-tech
增大源端Oracle的undo_retention,增大到90000:
ALTER SYSTEM SET UNDO_RETENTION=90000 scope=both;
(参数UNDO_RETENTION不用重启Oracle就可以生效)
这可能是因为源数据库导出表时,同时有数据更新,此时被导出的表的数据会放到undo表空间里,如果导出时间过长,undo空间的数据得不到释放,undo空间不够用,就会有这个错误。如果没有数据更新,估计这种情况不会发生。解决方法是:每次执行impdp时,tables参数中的表不要太多,不超过50个,如果表很大,就指定一个表。
下面是执行impdp的shell脚本,接受一个文件作为输入,一个表名一行:
table_list=''
list_size=0
for line in $(cat $1)
do
if test -n "$table_list";then
table_list+=','
table_list+=$line
else
table_list+=$line
filist_size=$(expr $list_size + 1)
if test $list_size -eq 50;then
set -x
impdp paastest/Paas1015 network_link=paas_test_link tables="$table_list" cluster=no exclude=statistics,grant parallel=2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
set +x
list_size=0
table_list=''
fi
doneif test -n "$table_list";then
set -x
impdp paastest/Paas1015 network_link=paas_test_link tables="$table_list" cluster=no exclude=statistics,grant parallel=2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
set +xfi
被外键引用的主表和引用它的子表,要一起在tablse参数中,否则会导出失败。实际上,先导入所有被外键引用的主表,再导入引用它们的子表,这样也可以。