标签 导入 下的文章

impdp导入还原命令详解

Oracle数据库还原IMPDP命令是相对于EXPDP命令的,方向是反向的。即对于数据库备份进行还原操作。

[oracle@ehrtest108 ~]$ impdp -help

Import: Release 11.2.0.4.0 - Production on чǚ̄ 1Ղ 17 13:48:33 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

参数说明:

---阅读剩余部分---

impdp导入报错ORA-39001: invalid argument value

oracle导入dmp文件报错:

impdp 'username/password' directory=DIR_DUMP_T1 table_exists_action=replace dumpfile=b30.dmp
Import: Release 11.2.0.1.0 - Production on Wed Jan 10 16:20:14 2018

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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39088: file name cannot contain a path specification

低版本的导入数据库在读高版本数据库由expdp产生的文件的时候很可能会产生上述错误,
比如对于上述问题而言,只需要在源数据库执行expdp的时候加上version=10.2.0.2,
然后在目标数据库执行impdp的时候也加上version=10.2.0.2的就OK了。
解决方法就是本着一个"就低不就高"的原则,
源数据库的版本是 11g 11.2.0.4.0,导入的目标库是11.2.0.1.0,版本有点差异,带上版本号Version=11.2.0.1.0重新导出:

[oracle@pldb2 expdir_tmp]$ expdp 'username/password'@PD1 DIRECTORY=DIR_DUMP_01 DUMPFILE=b30.dmp tables=BIS_TRAFFIC_INTSUMMARY_HOUR Version=11.2.0.1.0                                      
Export: Release 11.2.0.4.0 - Production on Wed Jan 10 16:18:24 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
Starting "POWERDESK"."SYS_EXPORT_TABLE_01":  powerdesk/********@PD1 DIRECTORY=DIR_DUMP_01 DUMPFILE=b30.dmp tables=BIS_TRAFFIC_INTSUMMARY_HOUR Version=11.2.0.1.0 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80 MB
>>> DBMS_AW_EXP: BIN$TAsGFYCiGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCkGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCmGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCoGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCqGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCsGqfgUwwD+woJNg==$0 not AW$
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: BIN$TAsGFYCiGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCkGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCmGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCoGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCqGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCsGqfgUwwD+woJNg==$0 not AW$
. . exported "POWERDESK"."BIS_TRAFFIC_INTSUMMARY_HOUR"   63.76 MB  715644 rows
Master table "POWERDESK"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for POWERDESK.SYS_EXPORT_TABLE_01 is:
  /oracle/expdir_tmp/b30.dmp
Job "POWERDESK"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 10 16:18:28 2018 elapsed 0 00:00:03

重新导入ok

impdp 'username/password' directory=DIR_DUMP_T1 table_exists_action=replace dumpfile=b30.dmp Version=11.2.0.1.0 ;

MySQL 清空数据库中的所有表及导出/导入表、结构

清空所有表SQL
mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='TABLE_NAME'" | mysql -f TABLE_NAME

例如:
mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='langold_enrolment_db'" | mysql -f langold_enrolment_db

加-f参数是为了清理掉外键,要么会报错



---阅读剩余部分---

Oracle导入数据库 imdbp命令参数详解

Oracle数据导入导出impdp/expdp就相当于oracle数据还原与备份,expdp命令可以把数据从远程数据库服务器导出到本地的dmp文件,impdp命令可以把dmp文件从本地导入到远处的数据库服务器中,利用这个功能可以构建两个相同的数据库,一个用来测试,一个用来正式使用。

---阅读剩余部分---

oracle使用impdp导入dmp数据文件时若表存在时的解决方法

当使用IMPDP完成数据库导入时,如遇到表已经存在时,Oracle提供给我们如下四种处理方式:
1.忽略(SKIP,默认行为);
2.在原有数据基础上继续增加(APPEND);
3.先DROP表,然后创建表,最后完成数据插入(REPLACE);
4.先TRUNCATE,再完成数据插入(TRUNCATE)。





---阅读剩余部分---

最新

分类

归档

评论

  • 安安: 都是af
  • Liang: 嗯,有点不通顺·
  • 王庭威: “MySQL互为主从...
  • Liang: 贴下编译参数和步骤,...
  • shao3911: 您好,为什么我在编译...
  • aliang: 先看是yum安装还是...
  • aliang: 将原来的nginx安...
  • yen: 3、如果要回滚的话,...
  • yen: 刚好需要升级ngin...
  • 文雨: 一些新的method...

其它