ERP系统版本更新原因需要在测试系统上通过IMP/EXP方式进行了备份恢复数据,在imp方式导出是报如下错误:
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
导入时发现如下错误提示:
Column 17
Column 18
Column 19
Column 20 CanEmpty{}|CheckLenX{FEditLen}|CheckLenMax{30}
Column 21 2
Column 22 com.kingdee.eas.base.form.ide.model.property.DefVa...
Column 23
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GDERPTEST.PK_T_BAS_ELEPROP) violated
Column 1 C8t8LfYGBE2XpayN4Ztr160AB873
Column 2 22
Column 3 Default Val
Column 4 ???
Column 5 ???
Column 6 Default Val
另外一个异常就是,在导入时出现将数据导入到原来用的表空间中(不是新建用户默认的表空间),因此按expdp/impdp方式进行备份恢复的测试。具体过程如下:[@more@]
1.创建转储文件(存放导出数据的文件)存放的目录
[oracle@gderp1 data]$ sqlplus / as sysdba
SQL> create directory dump_dir as '/data/dump_dir'
SQL> grant read,write on directory dump_dirto erp;
--执行导出:
expdp erp/erp dumpfile=dump_dir:erp0711.dmp logfile=dump_dir:erp0711.log
导出完毕后在执行如下命令进行了备份的导入:
impdp erpnew/erpnewschemas="erp" remap_schema="erp:erpnew" dumpfile=dump_dir:erp0711.dmp logfile=dump_dir:erpnew_imp.log
导入成功了,但是发现将数据导入到原来表空间而不是新建用户默认表空间。
因此采用如下方法,查看了导出DMP文件中的有关DDL语句:
impdp system/systemschemas="erp" dumpfile=dump_dir:erp0711.dmpnologfile=y sqlfile=dump_dir:erp_full.sql
部分结果如下:
...
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 13:08:48
执行完毕后,查看gderp_full.sql文件内容,部分内容如下所示:
[oracle@erp1 dump_dir]$ more erp_full.sql
-- CONNECT SYSTEM
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT ERP
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'EAS', inst_scn=>'108184772');
COMMIT;
END;
/
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYSTEM
CREATE TABLE "ERP"."DW_BD_PUMPRECORD"
("FTABLE" VARCHAR2(80),
"FSYSTEM" NUMBER(10,0),
"FCURRENTPERIODID" VARCHAR2(44),
"FBEGINDATE" TIMESTAMP (6),
"FENDDATE" TIMESTAMP (6),
"FISCLOSEDACCOUNT" NUMBER(10,0),
"FPUMPTIME" TIMESTAMP (6)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EASDATA" ;
…
原来是将创建表和索引等对象的TABLESPACE "EASDATA"属性已经写死的,知道原因,则解决起来就简单了。
解决办法1:
impdp erpnew/erpnewschemas="erp" remap_schema="erp:erpnew"remap_tablespace=easdata:erpnewdumpfile=dump_dir:erp0711.dmp logfile=dump_dir:erpnew_imp.log
另外一种在不知道remap_tablespace参数之前想出来的解决办法如下:
首先用ultraedit打开gderp_full.sql并将TABLESPACE "EASDATA"属性批量替换为想要的表空间,然后在SQL Plus里调用该sql文件并创建这些对象的定义,最后根据content=DATA_ONLY选项只导入数据;
impdp erpnew/erpnewschemas="erp" remap_schema="erp:erpnew"content=DATA_ONLYdumpfile=dump_dir:erp0711.dmp logfile=dump_dir:erpnew_imp.log
注:
content=content_optionSpecifies whether data, metadata, or both are imported.
Valid values are: DATA_ONLY (data only), METADATA_ONLY
(metadata only), and the default ALL (both).