Oracle 单实例 迁移到 RAC 实例 -- 使用导出导入方法

将Oracle单实例迁移到RAC实例上有两种方法：

1.使用RMAN复制

2.使用逻辑导出导入（exp/imp）或者数据泵（expdp/impdp）

这篇演示用数据泵（expdp/impdp）将单实例迁移到RAC环境。导出导入schema方式来进行。逻辑导出导入相对数据泵而言，更简单一点。

这里假设RAC环境已经搭建好了。如果没有搭建好，可以参考我的Blog：

Redhat 5.4 + ASM + RAW+ Oracle 10g RAC安装文档

http://blog.csdn.net/xujinyang/article/details/6837265

导出导入参考：

Oracle 10g EXPDP和IMPDP使用说明

http://blog.csdn.net/xujinyang/article/details/6830446

ORACLE数据库逻辑备份简单EXP/IMP

http://blog.csdn.net/xujinyang/article/details/6830199

测试过程如下：

1.现在本地库上创建用户Dave,与值对应的表空间。

2.用Dave登陆，创建相关的表。

3.将Dave用户的表空间导出

4.将dump文件导入到RAC实例

一．在本地库上创建表空间，用户

SQL> select file_name from dba_data_files;

FILE_NAME

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

D:/APP/ADMINISTRATOR/ORADATA/ORCL/USERS01.DBF

D:/APP/ADMINISTRATOR/ORADATA/ORCL/UNDOTBS01.DBF

D:/APP/ADMINISTRATOR/ORADATA/ORCL/SYSAUX01.DBF

D:/APP/ADMINISTRATOR/ORADATA/ORCL/SYSTEM01.DBF

D:/APP/ADMINISTRATOR/ORADATA/ORCL/DAVE0.DBF

D:/APP/ADMINISTRATOR/ORADATA/ORCL/DBA1.DBF

D:/APP/ADMINISTRATOR/ORADATA/ORCL/CATALOG1.DBF

D:/APP/ADMINISTRATOR/ORADATA/ORCL/CATALOG_TS1.DBF

已选择8行。

SQL> create tablespace tianlesoftware datafile

'D:/APP/ADMINISTRATOR/ORADATA/ORCL/tianlesoftware.dbf' size 50m;

表空间已创建。

SQL> create user dave identified by dave default tablespace tianlesoftware temporary tablespace temp;

用户已创建。

SQL> grant dba to dave;

授权成功。

SQL> grant connect to dave;

授权成功。

SQL> grant resource to dave;

授权成功。

SQL>

二．用dave登陆，创建相关表

SQL> create table userinfo(name varchar2(10),hometown varchar2(20));

表已创建。

SQL> insert into userinfo values('dave','安徽省安庆市怀宁县');

已创建1行。

SQL> commit;

提交完成。

SQL> insert into userinfo values('Tianle','安徽省安庆市怀宁县');

已创建1行。

SQL> commit;

提交完成。

SQL> select * from userinfo;

NAMEHOMETOWN

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

dave安徽省安庆市怀宁县

Tianle安徽省安庆市怀宁县

SQL>

三．用数据泵导出Dave用户的表空间

3.1创建directory并赋权

SQL> conn / as sysdba;

已连接。

SQL> create directory backup as 'e:/tmp';

目录已创建。

SQL> grant read,write on directory backup to dave;

授权成功。

SQL> select * from dba_directories;

OWNERDIRECTORY_NAMEDIRECTORY_PATH

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

SYSBACKUPe:/tmp

3.2导出Dave schema - - - SCHEMA与用户对应

[oracle@localhost ~]$ expdp dave/dave DIRECTORY=backup DUMPFILE=tianlesoftware.dmp SCHEMAS=Dave logfile=exp.log;

Export: Release 10.2.0.1.0 - Production on Tuesday, 28 September, 2010 15:12:36

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "DAVE"."SYS_EXPORT_SCHEMA_01":dave/******** DIRECTORY=backup DUMPFILE=tianlesoftware.dmp SCHEMAS=Dave logfile=exp.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

. . exported "DAVE"."USERINFO"5.25 KB2 rows

Master table "DAVE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DAVE.SYS_EXPORT_SCHEMA_01 is:

/u01/tianlesoftware.dmp

Job "DAVE"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:13:32

四．将dump文件导入RAC

RAC状态：

[oracle@rac2 bin]$ crs_stat -t

NameTypeTargetStateHost

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

ora.orcl.dbapplicationONLINEONLINErac1

ora....oltp.cs applicationONLINEONLINErac2

ora....cl1.srv applicationONLINEONLINErac1

ora....cl2.srv applicationONLINEONLINErac2

ora....l1.inst applicationONLINEONLINErac1

ora....l2.inst applicationONLINEONLINErac2

ora....SM1.asm applicationONLINEONLINErac1

ora....C1.lsnr applicationONLINEONLINErac1

ora.rac1.gsdapplicationONLINEONLINErac1

ora.rac1.onsapplicationONLINEONLINErac1

ora.rac1.vipapplicationONLINEONLINErac1

ora....SM2.asm applicationONLINEONLINErac2

ora....C2.lsnr applicationONLINEONLINErac2

ora.rac2.gsdapplicationONLINEONLINErac2

ora.rac2.onsapplicationONLINEONLINErac2

ora.rac2.vipapplicationONLINEONLINErac2

4.1创建相关用户和表空间

SQL>select file_name from dba_data_files;

FILE_NAME

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

+DATA/orcl/datafile/users.279.730181053

+DATA/orcl/datafile/sysaux.277.730181053

+DATA/orcl/datafile/undotbs1.278.730181053

+DATA/orcl/datafile/system.276.730181051

+DATA/orcl/datafile/undotbs2.284.730181347

+DATA/orcl/datafile/anhuianqing.dbf

6 rows selected.

SQL>create tablespace tianlesoftware datafile '+DATA/orcl/datafile/tianlesoftware.dbf' size 50m;

Tablespace created.

SQL>create user dave identified by dave default tablespace tianlesoftware temporary tablespace temp;

User created.

SQL> grant dba to dave;

Grant succeeded.

SQL> grant connect to dave;

Grant succeeded.

SQL> grant resource to dave;

Grant succeeded.

SQL>

4.2创建导入directory

SQL> create directory backup as '/u01';

Directory created.

SQL> grant read,write on directory backup to dave;

Grant succeeded.

SQL> set wrap off

SQL> select * from dba_directories;

OWNERDIRECTORY_NAMEDIRECTORY_PATH

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

SYSADMIN_DIR/ade/aime_10.2_lnx

SYSDATA_PUMP_DIR/u01/app/oracle/pr

SYSWORK_DIR/ade/aime_10.2_lnx

SYSBACKUP/u01

4.3开始导入

导入之前需要将导出的dump文件copy到backup directory目录下，即/u01。Windows到linux的话，mount过去，linux到linux用SCP命令就可以了。

导入数据：

[oracle@rac1 u01]$export ORACLE_SID=orcl1

[oracle@rac1 u01]$impdp dave/dave DIRECTORY=BACKUP DUMPFILE=tianlesoftware.dmp SCHEMAS=DAVE logfile=impdp.log;

Import: Release 10.2.0.1.0 - Production on Tuesday, 28 September, 2010 6:32:42

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

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

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

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":system/******** DIRECTORY=BACKUP DUMPFILE=tianlesoftware.dmp SCHEMAS=DAVE logfile=impdp.log

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"DAVE" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "DAVE"."USERINFO"5.25 KB2 rows

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 06:33:51

这里要注意，要指定ORACLE_SID，不然会报如下错误：

UDI-00008: operation generated ORACLE error 1034

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

UDI-00003: all allowable logon attempts failed

验证：

SQL> conn dave/dave;

Connected.

SQL> select * from userinfo;

NAMEHOMETOWN

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

dave安徽省安庆市怀宁县

Tianle安徽省安庆市怀宁县

[oracle@rac2 bin]$export ORACLE_SID=orcl2

[oracle@rac2 bin]$sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 28 06:54:45 2010

Copyright (c) 1982, 2005, Oracle.All rights reserved.

SQL> conn dave/dave;

Connected.

SQL> select * from userinfo;

NAMEHOMETOWN

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

dave安徽省安庆市怀宁县

Tianle安徽省安庆市怀宁县

如果在导入之前，禁用了相关的触发器，那么在导入之后要启用它们。并且还要检查是否有无效的procedure, function, packages, job等。如果有，重新编译一下。

方法参考：

Oracle Compile编译无效对象

http://blog.csdn.net/xujinyang/article/details/6830032

一些注意事项：

（1）导出导入之前检查每个table之间是否有严格的关联，比如PK, FK ,使用如下SQL查找，如果有，DISABLE先。

select * from all_constraints where constraint_type='R' and OWNER='DAVE' ;

（2）自动归档模式下，导入数据的时候注意手工清理归档。

（3）如果在导出时，如果只导结构，如exp使用了rows=n，或者expdp使用了content=metadata_only都会导致导入后再次分析表的时候报ora-20005这个错误。我们需要在exprows=n的时候加入statistics=none。或者在expdpcontent=metadata_only的时候使用exclude=(table_statistics,index_statistics)来避免这个错误的发生。

也就是说导出没有数据的结构的时候不需要导出统计信息。如果导出后，在导入时，统计信息会出于锁定状态，必须使用execute DBMS_STATS.UNLOCK_TABLE_STATS('<user name>','<table name>');来帮所有table解掉统计信息上的锁，否则不能再次统计。

如果是先导入结构，注意先Disable Trigger及相关可能触发的Job,导入完成后再开启。

在只导结构的情况下，Table, Index统计信息的一个说明：

因为导入的时候没有包含统计信息，所以需要重新收集统计。

exec dbms_stats.gather_schema_stats(ownname => 'dave',estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'for all columns sizeauto',

cascade=>TRUE,

degree => 8 ) ;

如果在expdp的时候没有加入exclude=(table_statistics,index_statistics)，这时就会碰到类似下面的错误：

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 13437

ORA-06512: at "SYS.DBMS_STATS", line 13457

ORA-06512: at line 1

解决方法：解锁：

找到这些table,index

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='DAVE' ;

批量修改为unlock .

select'execdbms_stats.unlock_table_stats(''DFMS'','''||table_name||'''); 'fromsys.dba_tab_statistics where stattype_locked is not null andwner='DFMS' ;

上面只是简单的测试，对于生产环境的搬迁，还是那句话：具体情况具体对待。在搬迁之前最好在测试环境上测试一下。

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