Oracle单实例数据库迁移到OracleRAC环境之--数据导出导入

Oracle单实例数据库迁移到OracleRAC环境之--数据导出导入系统环境：操作系统：RedHatEL55Oracle：Oracle11.2.0.1.0集群软件：OracleGI11.2.0.1.0本案例采用的是

Oracle单实例数据库迁移到Oracle RAC环境之--数据导出导入

系统环境：

操作系统：RedHat EL55

Oracle ： Oracle 11.2.0.1.0

集群软件：Oracle GI 11.2.0.1.0

本案例采用的是通过数据的导入导出（EXPDP/IMPDP)的迁移方式

从单实例的库迁移数据到RAC环境，可以有多种方式，通过数据的导出和导入也可以，但前提是数据库采用相同的字符集。

1、数据库环境

单实例：

17:35:59 SYS@ test1>SELECT * FROM V$VERSION;

BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production Elapsed: 00:00:00.02

17:35:36 SYS@ test1>select userenv('LANGUAGE') FROM DUAL;

USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.ZHS16GBK

RAC 环境：

17:46:03 SYS@ prod1>SELECT * FROM V$VERSION;

BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production Elapsed: 00:00:00.16

17:46:29 SYS@ prod1>select instance_name,status from gv$instance;

INSTANCE_NAME STATUS ---------------- ------------ prod1 OPEN prod2 OPEN

17:45:40 SYS@ prod1>select userenv('LANGUAGE') FROM DUAL;

USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.ZHS16GBK

2、建立测试环境

单实例：

17:38:26 SYS@ test1>create tablespace test01

17:41:40 2 datafile '/dsk1/oradata/test1/test01.dbf' size 100m;

Tablespace created.

17:43:49 SYS@ test1>create user test1 identified by test1

17:44:00 2 default tablespace test01

17:44:00 3 temporary tablespace tmpgp1

17:44:00 4 quota unlimited on test01

17:44:00 5 account unlock;

17:44:00 SYS@ test1>grant connect ,resource to test1;

Grant succeeded.

17:50:34 SYS@ test1>conn test1/test1

Connected.

17:50:41 TEST1@ test1>create table testtb1 tablespace test01 as select * from scott.emp;

Table created.

Elapsed: 00:00:00.25

17:50:52 TEST1@ test1>create index test_empno_ind on testtb1(empno) tablespace indx;

Index created.

Elapsed: 00:00:00.05

如果做表空间传输，需要对表空间做自包含检测：

17:51:15 SYS@ test1>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('test01', TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:01:04.07

17:52:25 SYS@ test1>SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

Elapsed: 00:00:00.05

创建导出传输目录：

17:52:43 SYS@ test1>create directory exp_dir as '/home/oracle/exp';

Directory created.

Elapsed: 00:00:00.07

17:53:24 SYS@ test1>grant read,write on directory exp_dir to test1;

Grant succeeded.

Elapsed: 00:00:00.06

17:53:39 SYS@ test1>!mkdir ~/exp

导出schema：

[oracle@rh6 exp]$ expdp test1/test1 directory=exp_dir dumpfile=test.dmp logfile=test.log schemas=test1

Export: Release 11.2.0.1.0 - Production on Thu Jul 10 17:59:05 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "TEST1"."SYS_EXPORT_SCHEMA_02": test1/******** directory=exp_dir dumpfile=test.dmp logfile=test.log schemas=test1 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB 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 "TEST1"."SYS_EXPORT_SCHEMA_01" 139.4 KB 1073 rows . . exported "TEST1"."TESTTB1" 8.570 KB 14 rows Master table "TEST1"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST1.SYS_EXPORT_SCHEMA_02 is: /home/oracle/exp/test.dmp Job "TEST1"."SYS_EXPORT_SCHEMA_02" successfully completed at 17:59:28

3、在RAC环境下导入数据：

从单实例传输dump文件到RAC环境下：