欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

expdp/impdp 的测试， 以及使用remap 参数

程序员文章站 2024-03-13 17:22:39
...

RDBMS 11.2.0.4

测试案例

[email protected]>create table bb_t1 tablespace bb as select * from sys.bb_t1;

Table created.

[email protected]>select count(*) from bb.bb_t1 ;

  COUNT(*)
----------
   5581504

[email protected]>

expdp 导出 ，导出bb schema下的bb_t1表。

[[email protected] dump]$ expdp \"/ as sysdba\" tables=bb.bb_t1 dumpfile=dump:bb_t1.dmp logfile=dump:bbt1_log  

Export: Release 11.2.0.4.0 - Production on Mon Mar 12 13:56:18 2018

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, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=bb.bb_t1 dumpfile=dump:bb_t1.dmp logfile=dump:bbt1_log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 632 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BB"."BB_T1"                                540.6 MB 5581504 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/dump/bb_t1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Mar 12 13:56:32 2018 elapsed 0 00:00:13

查看alert log 日志，DW00进程会出现 。这是数据泵的主进程。DM00这个进程，是worker的进程。

[[email protected] trace]$ tail 200 alert_test.log 
tail: cannot open `200' for reading: No such file or directory
==> alert_test.log <==
DW00 started with pid=48, OS id=5239, wid=1, job SYS.SYS_EXPORT_TABLE_01
Mon Mar 12 13:56:18 2018
Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_5257.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 2
Mon Mar 12 13:56:19 2018
DM00 started with pid=44, OS id=5259, job SYS.SYS_EXPORT_TABLE_01
Mon Mar 12 13:56:20 2018
DW00 started with pid=46, OS id=5261, wid=1, job SYS.SYS_EXPORT_TABLE_01
[[email protected] trace]$

将dump文件，传输到另一台数据库服务器上，导入。在导入前，要做一些准备工作，建立用户以及权限等等

[email protected]>create directory dump as '/u01/dump';

Directory created.

[email protected]>grant read,write on directory dump to public;

Grant succeeded.

[email protected]>

导入及验证 

impdp bb/oracle dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1.log 

[[email protected] ~]$ impdp bb/oracle dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1.log

Import: Release 11.2.0.4.0 - Production on Mon Mar 12 14:29:18 2018

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, OLAP, Data Mining and Real Application Testing options
Master table "BB"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "BB"."SYS_IMPORT_FULL_01":  bb/******** dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1.log 
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BB"."BB_T1"                                540.6 MB 5581504 rows
Job "BB"."SYS_IMPORT_FULL_01" successfully completed at Mon Mar 12 14:30:44 2018 elapsed 0 00:01:23
[email protected]>conn bb/oracle
Connected.
[email protected]>select count(*) from bb.bb_t1;

  COUNT(*)
----------
   5581504

[email protected]>

下面测试remap_tablespace,remap_table,remap_schema  这些参数 

表bb.bb_t1在原库上是在bb表空间的。这里计划导入到cc表空间。首先要创建cc表空间

[email protected]>create tablespace cc datafile '/u01/app/oracle/oradata/orcl/cc.dbf' size 10M autoextend on ;

Tablespace created.

[email protected]>create user cc identified by oracle default tablespace cc;

User created.

[email protected]>
[[email protected] ~]$ impdp bb/oracle dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1_imp.log remap_tablespace=BB:CC

Import: Release 11.2.0.4.0 - Production on Mon Mar 12 15:54:45 2018

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, OLAP, Data Mining and Real Application Testing options
Master table "BB"."SYS_IMPORT_FULL_05" successfully loaded/unloaded
Starting "BB"."SYS_IMPORT_FULL_05":  bb/******** dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1_imp.log remap_tablespace=BB:CC 
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BB"."BB_T1"                                540.6 MB 5581504 rows
Job "BB"."SYS_IMPORT_FULL_05" successfully completed at Mon Mar 12 15:56:07 2018 elapsed 0 00:01:22

remap_table

impdp bb/oracle dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1_imp1.log REMAP_TABLE=bb_t1:bb_t3  exclude=PROCACT_INSTANCE
[[email protected] ~]$ impdp bb/oracle dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1_imp1.log REMAP_TABLE=bb_t1:bb_t3  exclude=PROCACT_INSTANCE

Import: Release 11.2.0.4.0 - Production on Mon Mar 12 15:36:52 2018

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, OLAP, Data Mining and Real Application Testing options
Master table "BB"."SYS_IMPORT_FULL_06" successfully loaded/unloaded
Starting "BB"."SYS_IMPORT_FULL_06":  bb/******** dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1_imp1.log REMAP_TABLE=bb_t1:bb_t3 exclude=PROCACT_INSTANCE 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BB"."BB_T3"                                540.6 MB 5581504 rows
Job "BB"."SYS_IMPORT_FULL_06" successfully completed at Mon Mar 12 15:38:14 2018 elapsed 0 00:01:21

remap_schema

impdp \"/ as sysdba\"  dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1_imp3.log remap_schema=BB:CC

[[email protected] ~]$ impdp \"/ as sysdba\"  dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1_imp3.log remap_schema=BB:CC

Import: Release 11.2.0.4.0 - Production on Mon Mar 12 16:08:04 2018

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, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_06" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_06":  "/******** AS SYSDBA" dumpfile=dump:bb_t1.dmp logfile=dump:bb_t1_imp3.log remap_schema=BB:CC 
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CC"."BB_T1"                                540.6 MB 5581504 rows
Job "SYS"."SYS_IMPORT_FULL_06" successfully completed at Mon Mar 12 16:09:04 2018 elapsed 0 00:00:59

END 






上一篇： mysql免密登录出现2003错误

下一篇： Spring：Bean的自动装配AutoWiring

推荐阅读