数据库跨平台迁移

客户为省钱，要把AIX server“干掉”！ 原来跑在上面的应用/数据库统统搬到WinNT2000上。

若数据量不大，推荐用exp/imp方式，如下。

方法一 expdp/impdp

导出准备：

导出语法：

ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-00942: table or view does not exist
ORA-39097: Data Pump job encountered unexpected error -942

解决办法：（仅供参考）

ACTION PLAN
===========
1. Catdph.sql will Re-Install DataPump types and views:
SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql
Note: If XDB is installed the it is required to run "catmetx.sql" script. also.

2. prvtdtde.plb will Re-Install tde_library packages:
SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb

3. Catdpb.sql will Re-Install DataPump packages:
SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql

4.Dbmspump.sql will Re-Install DBMS DataPump objects:
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql

5. To recompile invalid objects, if any:
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

导入准备：

建好表空间/数据文件，和源数据库一样。

参考脚本（运行在源数据库）：

select 'CREATE TABLESPACE "'||tablespace_name|| '" LOGGING DATAFILE +' ||FILE_NAME||
'+ SIZE 0M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;'
from dba_data_files
order by tablespace_name
-- ALTER TABLESPACE "NAME" ADD DATAFILE 'NAME' SIZE 0M;

导入语法(同样要先建好directory)：

本次运行碰到的错误和解决办法：（仅供参考）

ORA-31684: Object type %s:"%s" already exists
ORA-39151: Table "%s"."%s" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39111: Dependent object type %s:"%s" skipped, base object type %s:"%s"."%s" already exists

Of the other errors that occurred:

1. the errors on creating the UNDOTBS tablespace can be ignored as the path specification
is invalid, and the current database must be using a different undo tablespace name.

2. The failure to create the PROCOBJ object due to the PLS-201 error on
'BMS_SCHEDULER.DISABLE' is due to bug:4328909 fixed in 11.1 and 10.2.0.4. A
generic one-off patch is available for 10.2.0.3 via patch number 4328909.

3. The "ORA-27486: insufficient privileges" errors on importing the MGMT_CONFIG_JOB
and MGMT_STATS_CONFIG_JOB scheduler jobs are due to bug:5668604 fixed in 11.1.
There is no fix available for that on top of 10.2.0.3, however the issue is documented in
Note:881601.1 which details how to workaround the errors.

方法二 传统exp/imp

导出语法：

exp parfile=.par (若是全库导出，输入用户名/密码时推荐用system/<>)

例

$more .par

log=<>.log
file=<>.dmp
buffer=1048576
full=y
rows=y
grants=y
indexes=y
compress=y
constraints=y
consistent=y
direct=y
statistics=none

本次运行碰到的错误：（仅供参考）

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
EXP-00105: parameter CONSISTENT is not supported for this user
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 19:
PLS-00905: object SYS.DBMS_JVM_EXP_PERMS is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling SYS.DBMS_JVM_EXP_PERMS.gra
nt_sysprivs_exp
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
EXP-00056: ORACLE error 600 encountered
[], [], [], [], []
EXP-00056: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [],
[], [], [], [], []
EXP-00000: Export terminated unsuccessfully

解决办法： （一般只需做step 2.)

Solution


Please follow step by step this instructions to fix the unable to load the XDB
library error:


1. Stop the database and stop the listener.

2. Set LIBPATH so the first directory referenced is $ORACLE_HOME/lib
Example (replace $ORACLE_HOME with the full path of the Oracle home directory):

csh:

setenv LIBPATH $ORACLE_HOME/lib:$ORACLE_HOME/lib32:

ksh:

export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:

Make sure $ORACLE_HOME/lib is first and $ORACLE_HOME/lib32 is second.

3. run /usr/sbin/slibclean as root

4. Re-start the database and the listener.

导入语法：

imp parfile=<>.par

不罗嗦了， 请读者自己试着写parfile内容～哈哈！

总结：感觉是data pump方式（即方法一）要快一些，就像Oracle官方说的那样。

但exp/imp方式比较稳妥，可能不会碰到很多bug。
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/716233/viewspace-615578/，如需转载，请注明出处，否则将追究法律责任。