环境简介
迁移前环境: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_job或kill_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的一个bug,Bug 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相关的bug(Bug 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;