oracle数据跨平台迁移,一次oracle数据跨平台迁移过程

源库环境：

操作系统 suse linux

数据库 oracle 10g 10.2.0.1

存储 IBM DS4500

目标库环境：

操作系统 AIX 5.3.009

数据库 oracle 10.2.0.1

存储 IBM DS4500

主机 IBM P550/P520

10.150.64.30

方案原理：

在目标库建立好物理环境，然后使用oracle 10g的DATAPUMP工具对数据的逻辑结构导出，再导入到目标库。

oracle工程师： luda

实施具体过程：

在suse linux上 切换到oracle用户

按顺序执行以下命令：

/*登陆数据库*/

Sqlplus '/as sysdba'

/*创建expdp的存放目录，并给予system用户存放目录的读写权限*/

//cd /opt/oradata

mkdir -p dump

Chmod -R 755 dump//

SQL > CREATE DIRECTORY DIR_DUMP AS '/opt/oradata/dump';

SQL > GRANT READ,WRITE ON DIRECTORY DIR_DUMP TO SYSTEM;

/*在oracle用户下 执行导出数据库的命令*/

expdp sys/oracle directory=dir_dump dumpfile=suseora.dmp full=y logfile=EXPDPSUSE.log

/*然后通过FTP的方式将数据传送到AIX主机上*/

------------------至此在suseliunx的操作完成----------------

在AIX主机上进行数据库前期部署工作。

/*在oracle用户下创建用于存放数据的dump目录*/

mkdir -p dump

/*在sqlplus下建立dir_dump目录，并赋予system用户读写权限*/

sqlplus '/as sysdba'

SQL > CREATE DIRECTORY DIR_DUMP AS '/oracle/dump';

SQL > GRANT READ,WRITE ON DIRECTORY DIR_DUMP TO SYSTEM;

//建立迁移数据所需要的物理环境，这里就是创建表空间，MIS系统的表空间创建语句我集成了脚本模式//

/*在sqlplus sys下执行*/

SQL > @xsdb_create.sql

脚本xsdb_create.sql内容为:

/*------------------------------------------------------------------------------------------------------------------*/

CREATE TABLESPACE "XSPGIMS_DEVICE" DATAFILE

'/oracle/XSPGIMS_DEVICE.dbf' SIZE 100m

autoextend on next 50m

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE "XSPGIMS_DEVICE_TEMP" TEMPFILE

'/oracle/XSPGIMS_DEVICE_TEMP.dbf' SIZE 200m

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

CREATE TABLESPACE "XSPGIMS_FILE" DATAFILE

'/oracle/XSPGIMS_FILE.dbf' SIZE 100m

autoextend on next 50m

NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE "XSPGIMS_FILE_TEMP" TEMPFILE

'/oracle/XSPGIMS_FILE_TEMP.dbf' SIZE 100m

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

CREATE TABLESPACE "XSPGIMS_FUEL" DATAFILE

'/oracle/XSPGIMS_FUEL.dbf' SIZE 52428800

autoextend on next 50m

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE "XSPGIMS_FUEL_TEMP" TEMPFILE

'/oracle/XSPGIMS_FUEL_TEMP.dbf' SIZE 100m

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

CREATE TABLESPACE "XSPGIMS_MATERIAL" DATAFILE

'/oracle/XSPGIMS_MATERIAL.dbf' SIZE 100m

autoextend on next 50m

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE "XSPGIMS_MATERIAL_TEMP" TEMPFILE

'/oracle/XSPGIMS_MATERIAL_TEMP.dbf' SIZE 104857600

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

CREATE TABLESPACE "XSPGIMS_PLAN" DATAFILE

'/oracle/XSPGIMS_PLAN.dbf' SIZE 100m

autoextend on next 50m

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE "XSPGIMS_PLAN_TEMP" TEMPFILE

'/oracle/XSPGIMS_PLAN_TEMP.dbf' SIZE 104857600

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

CREATE TABLESPACE "XSPGIMS_PRODUCE" DATAFILE

'/oracle/XSPGIMS_PRODUCE.dbf' SIZE 100m

autoextend on next 50m

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

CREATE TEMPORARY TABLESPACE "XSPGIMS_PRODUCE_TEMP" TEMPFILE

'/oracle/XSPGIMS_PRODUCE_TEMP.dbf' SIZE 1073741824

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

CREATE TABLESPACE "XSPGIMS_SEQURITY" DATAFILE

'/oracle/XSPGIMS_SEQURITY.dbf' SIZE 104857600

autoextend on next 50m

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

CREATE TEMPORARY TABLESPACE "XSPGIMS_SEQURITY_TEMP" TEMPFILE

'/oracle/XSPGIMS_SEQURITY_TEMP.dbf' SIZE 104857600

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

CREATE TABLESPACE "XSPGIMS_SYSTEM" DATAFILE

'/oracle/XSPGIMS_SYSTEM.dbf' SIZE 268435456

autoextend on next 50m

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

CREATE TEMPORARY TABLESPACE "XSPGIMS_SYSTEM_TEMP" TEMPFILE

'/oracle/XSPGIMS_SYSTEM_TEMP.dbf' SIZE 367001600

AUTOEXTEND ON NEXT 1048576 MAXSIZE 32767M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

CREATE TABLESPACE "XSPGIMS_OTHER" DATAFILE

'/oracle/XSPGIMS_OTHER.dbf' SIZE 5242880

AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

CREATE TEMPORARY TABLESPACE "XSPGIMS_OTHER_TEMP" TEMPFILE

'/oracle/XSPGIMS_OTHER_TEMP.dbf' SIZE 5242880

AUTOEXTEND ON NEXT 516096 MAXSIZE 32767M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

CREATE TABLESPACE "RIMS_DATA" DATAFILE

'/oracle/RIMS_DATA.dbf' SIZE 5242880

AUTOEXTEND ON NEXT 106496 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

CREATE TABLESPACE "XSPGIMS_MONITOR" DATAFILE

'/oracle/XSPGIMS_MONITOR.dbf' SIZE 5242880

AUTOEXTEND ON NEXT 106496 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

CREATE TABLESPACE "XSPGIMS_HUMANRESOURCE" DATAFILE

'/oracle/XSPGIMS_HUMANRESOURCE.dbf' SIZE 5242880

AUTOEXTEND ON NEXT 106496 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

/*----------------------------------------------------------------------------------------------------------------------*/

====================================================================

/*执行脚本在oracle用户下执行导入命令*/

Impdp system/oracle directory=dir_dump dumpfile=suseora.dmp full=y logfile=10.30.01.log

----------------------------------------------------------------------------------------------------------------------

HA的oracle环境实施具体过程