系统环境：
操作系统：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;
|
1
2
3
4
5
6
7
8
|
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;
|
1
2
3
|
USERENV(
'LANGUAGE'
)
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
|
RAC 环境：
17:46:03 SYS@ prod1>SELECT * FROM V$VERSION;
|
1
2
3
4
5
6
7
8
|
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;
|
1
2
3
4
|
INSTANCE_NAME STATUS
---------------- ------------
prod1 OPEN
prod2 OPEN
|
17:45:40 SYS@ prod1>select userenv('LANGUAGE') FROM DUAL;
|
1
2
3
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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环境下：
[oracle@rh6 exp]$ scp *.dmp 192.168.8.21:/u01/exp
|
1
2
3
4
5
6
|
The authenticity of host
'192.168.8.21 (192.168.8.21)'
can't be established.
RSA key fingerprint is
39
:
04
:
88
:3b:
54
:
34
:3c:
34
:d2:df:
74
:
37
:fe:5f:
92
:2d.
Are you sure you want to
continue
connecting (yes/no)? yes
Warning: Permanently added
'192.168.8.21'
(RSA) to the list of known hosts.
oracle@
192.168.
8.21
's password:
test.dmp
100
% 428KB
428.
0KB/s
00
:
00
|
在RAC下建立表空间和Schema：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
18
:
16
:
28
SYS@ prod1>create tablespace test01;
Tablespace created.
Elapsed:
00
:
00
:
06.27
18
:
19
:
19
SYS@ prod1>create tablespace indx;
Tablespace created.
Elapsed:
00
:
00
:
05.81
18
:
19
:
36
SYS@ prod1>select file_name,file_id,tablespace_name,bytes/
1024
/
1024
from
dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/
1024
/
1024
-------------------------------------------------- ---------- ---------------------------
+DG1/prod/datafile/users
.259.852387481
4
USERS
5
+DG1/prod/datafile/undotbs1
.258.852292707
3
UNDOTBS1
105
+DG1/prod/datafile/sysaux
.257.852292707
2
SYSAUX
630
+DG1/prod/datafile/system
.256.852292703
1
SYSTEM
690
+DG1/prod/datafile/example
.264.852292891
5
EXAMPLE
103.125
+DG1/prod/datafile/undotbs2
.265.852293259
6
UNDOTBS2
50
+DG1/prod/datafile/tbs1
.269.852376681
7
TBS1
100
+DG1/prod/datafile/test01
.273.852574753
8
TEST01
100
+DG1/prod/datafile/indx
.274.852574771
9
INDX
100
9
rows selected.
|
|
1
2
3
4
5
6
7
8
9
10
|
18
:
14
:
34
SYS@ prod1>create user test1 identified by test1
18
:
14
:
49
2
default tablespace users
18
:
14
:
49
3
temporary tablespace temp
18
:
14
:
49
4
quota unlimited
on
users
18
:
14
:
49
5
account unlock;
User created.
Elapsed:
00
:
00
:
00.15
18
:
14
:
50
SYS@ prod1>
18
:
14
:
50
SYS@ prod1>grant connect,resource to test1;
Grant succeeded.
|
建立数据导入目录：
|
1
2
3
4
5
6
7
|
18
:
15
:
31
SYS@ prod1>create directory imp_dir
as
'/u01/exp'
;
Directory created.
18
:
16
:
08
SYS@ prod1>grant read,write
on
directory imp_dir to test1;
Grant succeeded.
Elapsed:
00
:
00
:
00.16
[root@node1 ~]# mkdir -p /u01/exp
[root@node1 ~]# chown -R oracle:dba /u01/exp
|
导入数据：
[root@node1 ~]# su - oracle
[oracle@node1 ~]$ cd /u01/exp
[oracle@node1 exp]$ ls
test.dmp
[oracle@node1 exp]$ impdp test1/test1 directory=imp_dir dumpfile=test.dmp logfile=test.log schemas=test1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
Import: Release
11.2.
0.1.
0
- Production
on
Thu Jul
10
18
:
21
:
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
- Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining
and
Real Application Testing options
Master table
"TEST1"
.
"SYS_IMPORT_SCHEMA_01"
successfully loaded/unloaded
Starting
"TEST1"
.
"SYS_IMPORT_SCHEMA_01"
: test1/******** directory=imp_dir dumpfile=test.dmp logfile=test.log schemas=test1
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
"TEST1"
.
"SYS_EXPORT_SCHEMA_01"
139.4
KB
1073
rows
. . imported
"TEST1"
.
"TESTTB1"
8.570
KB
14
rows
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
Job
"TEST1"
.
"SYS_IMPORT_SCHEMA_01"
successfully completed
at
18
:
21
:
42
|
数据导入成功！
4、验证：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
18
:
19
:
45
SYS@ prod1>conn test1/test1
Connected.
18
:
22
:
20
TEST1@ prod1>select *
from
tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SYS_EXPORT_SCHEMA_01 TABLE
TESTTB1 TABLE
Elapsed:
00
:
00
:
00.08
18
:
22
:
24
TEST1@ prod1>select *
from
testtb1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7521
WARD SALESMAN
7698
22
-FEB
-81
1250
500
30
7566
JONES MANAGER
7839
02
-APR
-81
2975
20
7654
MARTIN SALESMAN
7698
28
-SEP
-81
1250
1400
30
7698
BLAKE MANAGER
7839
01
-MAY
-81
2850
30
7782
CLARK MANAGER
7839
09
-JUN
-81
2450
10
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
40
7839
KING PRESIDENT
17
-NOV
-81
5000
10
7844
TURNER SALESMAN
7698
08
-SEP
-81
1500
0
30
7876
ADAMS CLERK
7788
23
-MAY
-87
1100
20
7900
JAMES CLERK
7698
03
-DEC
-81
950
30
7902
FORD ANALYST
7566
03
-DEC
-81
3000
20
7934
MILLER CLERK
7782
23
-JAN
-82
1300
10
14
rows selected.
Elapsed:
00
:
00
:
00.02
18
:
22
:
34
TEST1@ prod1>
17
:
47
:
08
SYS@ prod2>conn test1/test1
Connected.
18
:
22
:
54
TEST1@ prod2>select *
from
testtb1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7521
WARD SALESMAN
7698
22
-FEB
-81
1250
500
30
7566
JONES MANAGER
7839
02
-APR
-81
2975
20
7654
MARTIN SALESMAN
7698
28
-SEP
-81
1250
1400
30
7698
BLAKE MANAGER
7839
01
-MAY
-81
2850
30
7782
CLARK MANAGER
7839
09
-JUN
-81
2450
10
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
40
7839
KING PRESIDENT
17
-NOV
-81
5000
10
7844
TURNER SALESMAN
7698
08
-SEP
-81
1500
0
30
7876
ADAMS CLERK
7788
23
-MAY
-87
1100
20
7900
JAMES CLERK
7698
03
-DEC
-81
950
30
7902
FORD ANALYST
7566
03
-DEC
-81
3000
20
7934
MILLER CLERK
7782
23
-JAN
-82
1300
10
14
rows selected.
Elapsed:
00
:
00
:
00.31
|
数据导入错误案例：
错误（1）：
[oracle@rh6 ~]$ expdp test1/test1 directory=exp_dir dumpfile=~/exp/test.dmp logfile=~/exp/test.log schemas=test1
Export: Release 11.2.0.1.0 - Production on Thu Jul 10 17:55:21 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
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39088: file name cannot contain a path specification
在dumpfile和logfile指定的参数不能指定路径！
错误（2）：
[oracle@rh6 ~]$ cd exp
[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:56:19 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_01": 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: 64 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
UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT job_id FROM v$datapum...","SQLA","tmp")
ORA-06512: at "SYS.KUPV$FT_INT", line 2904
ORA-06512: at "SYS.KUPC$QUE_INT", line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1
出现了ORA-04031错误，应该和share pool 有关系！
查看系统共享池：
17:57:59 SYS@ test1>show parameter shared
|
1
2
3
4
5
6
7
8
9
|
NAME TYPE VALUE
------------------------------------ -------------------------------- -------------------
hi_shared_memory_address integer
0
max_shared_servers integer
shared_memory_address integer
0
shared_pool_reserved_size big integer 12M
shared_pool_size big integer 112M
shared_server_sessions integer
shared_servers integer
0
|
调整share pool的size：
17:58:01 SYS@ test1>alter system set shared_pool_size=200m ;
System altered.
Elapsed: 00:00:00.65
调整后重新导出成功：
[oracle@rh6 exp]$ expdp test1/test1 directory=exp_dir dumpfile=test.dmp logfile=test.log schemas=test1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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
|
@至此，数据迁移完成！
