微软云azure 数据迁移expdp报错ORA-01110: data file 201: '/home/oradata/powerdes/temp01.dbf'

Azure云Oracle expdp报错解决方法

最新推荐文章于 2024-09-28 20:36:28 发布

原创最新推荐文章于 2024-09-28 20:36:28 发布 · 1w 阅读

0 ·

CC 4.0 BY-SA版权

linux 同时被 2 个专栏收录

71 篇文章

订阅专栏

oracle problem

61 篇文章

订阅专栏

1，expdp的时候报错：

在idc的oracle服务器上正常导入导出都ok的，但是到了azure云上就出故障了：

[oracle@pldb1 oracle]$ expdp  \'powerdesk/testcrmfile\' directory=DIR_DUMP tables=bis_floor,bis_shop,bis_shop_conn,bis_store,bis_cont dumpfile=zhengyin.qu_bak_$(date +%Y%m%d)_02.dmp


Export: Release 11.2.0.1.0 - Production on Sat Feb 6 20:00:00 2016


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-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/home/oradata/powerdes/temp01.dbf'
ORA-06512: at "SYS.DBMS_LOB", line 664
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3901
ORA-06512: at line 1


[oracle@pldb1 oracle]$

查看temp01.dbf表空间，是online的，貌似没有坏：

SQL> select name,bytes/1024/1024,status from v$tempfile;


NAME
--------------------------------------------------------------------------------
BYTES/1024/1024 STATUS
--------------- -------
/home/oradata/powerdes/temp01.dbf
	     68 ONLINE




SQL>

2，google说起是drop一个temp01.dbf

SQL> alter tablespace tempfile "/home/oradata/powerdes/temp01.dbf" drop;
alter tablespace tempfile "/home/oradata/powerdes/temp01.dbf" drop
                          *
ERROR at line 1:
ORA-00972: identifier is too long




SQL> 


SQL> ALTER TABLESPACE tempfile '/home/oradata/powerdes/temp01.dbf' drop;
ALTER TABLESPACE tempfile '/home/oradata/powerdes/temp01.dbf' drop
                          *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option




SQL>

删除报错了，无效，google不出来比较有效的解决方案

3，尝试添加新的temp02.dbf，这样试试看。

SQL> 
SQL> 
SQL> alter tablespace temp add tempfile '/home/oradata/powerdes/temp02.dbf' size 60M reuse;


Tablespace altered.


SQL> 


[oracle@pldb1 oracle]$ expdp  \'powerdesk/testcrmfile\' directory=DIR_DUMP tables=bis_floor,bis_shop,bis_shop_conn,bis_store,bis_cont dumpfile=zhengyin.qu_bak_$(date +%Y%m%d)_02.dmp


Export: Release 11.2.0.1.0 - Production on Sat Feb 6 20:00:00 2016


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-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/home/oradata/powerdes/temp01.dbf'
ORA-06512: at "SYS.DBMS_LOB", line 664
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3901
ORA-06512: at line 1
[oracle@pldb1 oracle]$

添加之后还是报错，所以这种方案没有生效。

4，尝试offline离线temp01.dbf，让expdp走temp02.dbf试试，结果成功了

设置离线状态：

SQL> alter database tempfile '/home/oradata/powerdes/temp01.dbf' offline;


Database altered.


SQL> 
SQL> select name,bytes/1024/1024,status from v$tempfile;


NAME
--------------------------------------------------------------------------------
BYTES/1024/1024 STATUS
--------------- -------
/home/oradata/powerdes/temp01.dbf
	      0 OFFLINE


/home/oradata/powerdes/temp02.dbf
	     60 ONLINE




SQL>

再执行expdp成功导出备份结果集：

[oracle@pldb1 oracle]$ expdp  \'powerdesk/testcrmfile\' directory=DIR_DUMP tables=bis_floor,bis_shop,bis_shop_conn,bis_store,bis_cont dumpfile=zhengyin.qu_bak_$(date +%Y%m%d)_02.dmp


Export: Release 11.2.0.1.0 - Production on Sat Feb 6 20:13:00 2016


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
Starting "POWERDESK"."SYS_EXPORT_TABLE_01":  "powerdesk/********" directory=DIR_DUMP tables=bis_floor,bis_shop,bis_shop_conn,bis_store,bis_cont dumpfile=zhengyin.qu_bak_20160206_02.dmp 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 76.25 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "POWERDESK"."BIS_SHOP"                      25.62 MB   49633 rows
. . exported "POWERDESK"."BIS_CONT"                      17.53 MB   19836 rows
. . exported "POWERDESK"."BIS_STORE"                     9.833 MB   25941 rows
. . exported "POWERDESK"."BIS_SHOP_CONN"                 5.708 MB   30758 rows
. . exported "POWERDESK"."BIS_FLOOR"                     186.6 KB    1082 rows
Master table "POWERDESK"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for POWERDESK.SYS_EXPORT_TABLE_01 is:
  /oracle/dump/zhengyin.qu_bak_20160206_02.dmp
Job "POWERDESK"."SYS_EXPORT_TABLE_01" successfully completed at 20:13:14


[oracle@pldb1 oracle]$

OK，最终导入成功了。