20126月给客户做的oracle RAC数据库迁移,当时使用的方法是停应用后、数据泵分用户导出导入,导出和导入过程中遇到的问题记录如下,其他的省略(本文中表空间名称、用户、表名称等一些地方做了一些处理):

环境简介

迁移前环境:P570+AIX 5.3+Oracle 10.2 RAC

迁移后环境:P750+AIX 6.1+Oracle 11.2 RAC

数据导出

导出报错一

报错信息摘录:

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 14 June, 2012 11:22:47

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31626: job does not exist

ORA-31633: unable to create master table "XX"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 871

ORA-00955: name is already used by an existing object

XX.dmp: A file or directory in the path name does not exist.

解决方法:

Job名称和一个已经停止(失败)的job名一样,出现了冲突;

使用ATTACH参数,进入交换模式,stop_jobkill_job清除老的job并且为新的导出操作指定一个新的job名称;

导出报错二

报错信息摘录:

ORA-31693: Table data object "XX"."GL_DETAIL" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-00600: internal error code, arguments: [kpotcgah-9], [0], [ORA-00600: internal error co

Processing object type

解决方法:

该报错为oracle的一个bugBug 8544008  OERI [kpotcgah-9] using datapump export

建议的处理方法为,不使用并行参数,或者指定dumpfile时添加%U参数。

因为数据量较大,当时在导出时使用了参数parallel=4,后来指定dumpfile时添加%U参数解决了问题

导出报错三

第三个脚本执行报错:

报错信息摘录:

ORA-31626: job does not exist

ORA-31633: unable to create master table "XX"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 871

ORA-01950: no privileges on tablespace 'DATA'

XX.dmp: A file or directory in the path name does not exist.

处理方法:

赋予unlimited权限在相应的表空间上;

alter user XX quota unlimited on DATA;

数据导入

数据传输:

scp

创建数据泵目录并赋权:

create directory expdata as '/backup';

grant read,write on directory expdata to system;

修改后的导入脚本(添加红色标记参数,解决11g的一个Bug 8415620):

impdp system/XX directory=expdata dumpfile=zj2_%U.dmp logfile=zj2.log parallel=4 schemas=ZJ job_name=zj2 cluster=n

导入报错一

导入过程中碰到了一个11g相关的bugBug 8415620

报错信息摘录如下:

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

ORA-31693: Table data object "XX"."XX" failed to load/unload and is being skipped due to error:

ORA-31640: unable to open dump file "XX.dmp" for read

ORA-19505: failed to identify file "XX.dmp"

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

解决方法:

Cannot Run DataPump With PARALLEL > 1 On 11.2 RAC [ID 1071373.1]

Cause

From 11.2, DataPump new parameter CLUSTER is introduced.
 

CLUSTER : Default=Y

Purpose :
Determines whether Data Pump can use Oracle Real Application Clusters (RAC)
resources and start workers on other Oracle RAC instances.

Syntax and Description : CLUSTER=[Y | N]

Solution

To force DataPump to use only the instance where the job is started and to replicate pre-Oracle Database 11g release 2 (11.2) behavior, specify CLUSTER=N.

导入报错二

表空间不存在:

报错信息摘录:

Import: Release 11.2.0.3.0 - Production on Thu Jun 14 17:52:13 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "SYSTEM"."ZJ2" successfully loaded/unloaded

Starting "SYSTEM"."ZJ2":  system/******** directory=expdata dumpfile=zj2_%U.dmp logfile=zj2.log parallel=4 schemas=ZJ job_name=zj2

Processing object type SCHEMA_EXPORT/USER

ORA-39083: Object type USER failed to create with error:

ORA-00959: tablespace 'DATA' does not exist

Failing sql is:

XX

解决方法:

创建一个表空间:

create tablespace DATA datafile '+DATA' size 10g autoextend on next 512m maxsize unlimited;

导入报错三

数据文件达到最大大小,无法自动扩展,导入作业挂起:

报错信息摘录:

ORA-39171: Job is experiencing a resumable wait.

ORA-01653: unable to extend table XX by 8192 in tablespace XX

解决方法:

为表空间XX添加一个数据文件,初始大小20G,设置为自动扩展:

SQL> alter tablespace XX add datafile '+DATA' size 20g autoextend on maxsize unlimited;        

10-09 20:45