oracle数据库迁移操作步骤及说明文档

一.操作步骤

1．导出数据

1)切换到oracle用户；

2)输入命令exp；

3)按提示依次要到处数据库的用户名，密码，导出的文件名，缓冲区大小，是否赋予

导出权限，是否导出表数据，是否压缩，具体参数列表及输入的值如下图所示，没

有输入则按默认值，直接回车：

bash-2.03$ exp

Export: Release 9.2.0.5.0 - Production on Thu Feb 15 11:36:22 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: omp9801

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

Enter array fetch buffer size: 4096 >

-- 缓冲区大小，可以按默认值

Export file: expdat.dmp >

-- 导出的文件名，可以修改

(2)U(sers), or (3)T(ables): (2)U >

-- 导出用户还是导出表，此处选择导出用户

Export grants (yes/no): yes >

-- 是否赋予grant权限

Export table data (yes/no): yes >

-- 是否导出表数据

Compress extents (yes/no): yes >

-- 是否压缩

4)导出结果

导出过程中系统会列出导出的状态列表，最后系统会提示导出成功信息，如下所示：Export terminated successfully without warnings.

2．ftp得到导出的源文件

1)在导出的目标服务器上，切换到oracle用户

2)ftp到源服务器，得到导出的源文件；

3．导入数据

1)切换到oracle用户；

2)创建导入的用户，赋予相应的权限，具体做法见附件A，如果要导入的用户已经存

在，必须删除该用户以及该用户拥有的表空间再创建；

3)输入命令exp；

4)按提示依次要到处数据库的用户名，密码，导入的文件名，缓冲区大小，是否只列

出导出的文件，是否忽略导出错误，是否赋予导出权限，是否导出表数据，是否导出整个export文件，具体参数列表及输入的值如下图所示，没有输入则按默认值，直接回车：

bash-3.00$ imp

Import: Release 9.2.0.6.0 - Production on Thu Feb 15 12:29:47 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: omp9801

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

Import file: expdat.dmp >

-- 导入的文件名，根据实际情况修改

Enter insert buffer size (minimum is 8192) 30720>

-- 缓冲区大小，可以按默认值

Export file created by EXPORT:V09.02.00 via conventional path

import done in UTF8 character set and UTF8 NCHAR character set

List contents of import file only (yes/no): no >

-- 是否列出导入文件

Ignore create error due to object existence (yes/no): no >

-- 是否忽略创建的错误

Import grants (yes/no): yes >

-- 是否赋予了grant权限

Import table data (yes/no): yes >

-- 是否导入表数据

Import entire export file (yes/no): no > yes

-- 是否导入整个文件

5)导入结果

导入过程中系统会列出导入的状态列表，但中间会出现警告信息，最后系统会提示导入成功信息，如下所示：

IMP-00041: Warning: object created with compilation warnings

"CREATE FORCE VIEW "OMP9801"."PREP_PACKAGE_VIEW" ("P"

"ACKAGE_ID","PACKAGE_REF_ID","CHANNEL_ID_LIST") AS "

"SELECT distinct( pkg.PKG_RECID ) AS PACKAGE_ID,

pkg.PKG_IMP"

"ORT_REFNUM AS PACKAGE_REF_ID, preparer_pub.getChannels("

" pkg.PKG_RECID ) as CHANNEL_ID_LIST FROM OV A_PACKAGE_VIEW pkg"

About to enable constraints...

Import terminated successfully with warnings.

二.操作中可能出现的问题

1.导入过程中会出现警告信息，如上所列出的；

2.导入后的数据库下有些编译错误信息，通过PL/SQL的Compile Invalid Objects按

钮重新编译一遍；

3.对于JA V A SOURCE的编译错误，可以通过Oracle自带的Enterprise Manager

Console平台drop掉错误的Java Source，再重新创建编译一次(drop之前要保存Java

Source源程序)；

三.实验室操作环境信息

导出数据库环境：

主机：

SunOS https://www.360docs.net/doc/7d7876050.html, 5.8 Generic_117350-02 sun4u sparc SUNW,Sun-Fire-V240

数据库：

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

PL/SQL Release 9.2.0.5.0 - Production

CORE 9.2.0.6.0 Production

TNS for Solaris: Version 9.2.0.5.0 - Production

NLSRTL Version 9.2.0.5.0 - Production

导入数据库主机环境：

主机：

SunOS fjgi 5.10 Generic_118833-17 sun4us sparc FJSV,GPUZC-M

数据库：

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

PL/SQL Release 9.2.0.6.0 - Production

CORE 9.2.0.6.0 Production

TNS for Solaris: Version 9.2.0.6.0 - Production

NLSRTL Version 9.2.0.6.0 – Production

附件A 创建用户的SQL

1.新建表空间

>sqlplus ‘/as sysdba’

sql>CREATE TABLESPACE OV A_SPACE9801 DATAFILE '/opt/oracle/OraHome1/ovaspace9801.dbf' SIZE 250M AUTOEXTEND ON NEXT 50M ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

sql>CREATE TABLESPACE OV A_SPACE9801_IDX DATAFILE '/opt/oracle/OraHome1/ovaspace9801_idx.dbf' SIZE 150M AUTOEXTEND ON NEXT 50M ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

2.新建用户

sql>-- Create the user

create user OMP9801

identified by "OMP9801"

default tablespace OV A_SPACE9801

temporary tablespace TEMP

profile DEFAULT

quota unlimited on ova_space9801

quota unlimited on ova_space9801_idx;

-- Grant/Revoke object privileges

grant select on SYS.DBA_DATA_FILES to OMP9801;

grant select on SYS.DBA_FREE_SPACE to OMP9801;

grant select on SYS.DBA_TABLESPACES to OMP9801;

grant execute on SYS.DBMS_ALERT to OMP9801;

grant execute on SYS.DBMS_LDAP to OMP9801;

grant execute on SYS.DBMS_LDAP_UTL to OMP9801;

grant execute on SYS.DBMS_LOCK to OMP9801;

grant execute on SYS.DBMS_PIPE to OMP9801;

grant execute on SYS.DBMS_SQL to OMP9801;

grant select on SYS.V_$PARAMETER to OMP9801;

grant select on SYS.V_$SESSION to OMP9801;

grant select on SYS.V_$SESSTA T to OMP9801;

grant select on SYS.V_$SQLAREA to OMP9801;

grant select on SYS.V_$STA TNAME to OMP9801;

-- Grant/Revoke role privileges

grant javasyspriv to OMP9801;

grant javauserpriv to OMP9801;

grant select_catalog_role to OMP9801;

-- Grant/Revoke system privileges

grant administer database trigger to OMP9801;

grant alter session to OMP9801;

grant create any trigger to OMP9801;

grant create cluster to OMP9801;

grant create database link to OMP9801;

grant create procedure to OMP9801;

grant create sequence to OMP9801;

grant create session to OMP9801;

grant create synonym to OMP9801;

grant create table to OMP9801;

grant create type to OMP9801;

grant create view to OMP9801;

grant select any dictionary to OMP9801;