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 [email protected] 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 [email protected] test1>select userenv('LANGUAGE') FROM DUAL;
1
2
3
|
USERENV( 'LANGUAGE' )
---------------------------------------------------- AMERICAN_AMERICA.ZHS16GBK |
RAC 环境:
17:46:03 [email protected] 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 [email protected] prod1>select instance_name,status from gv$instance;
1
2
3
4
|
INSTANCE_NAME STATUS ---------------- ------------ prod1 OPEN prod2 OPEN |
17:45:40 [email protected] prod1>select userenv('LANGUAGE') FROM DUAL;
1
2
3
|
USERENV( 'LANGUAGE' )
---------------------------------------------------- AMERICAN_AMERICA.ZHS16GBK |
2、建立测试环境
单实例:
17:38:26 [email protected] test1>create tablespace test01
17:41:40 2 datafile '/dsk1/oradata/test1/test01.dbf' size 100m;
Tablespace created.
17:43:49 [email protected] 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 [email protected] test1>grant connect ,resource to test1;
Grant succeeded.
17:50:34 [email protected] test1>conn test1/test1
Connected.
17:50:41 [email protected] test1>create table testtb1 tablespace test01 as select * from scott.emp;
Table created.
Elapsed: 00:00:00.25
17:50:52 [email protected] test1>create index test_empno_ind on testtb1(empno) tablespace indx;
Index created.
Elapsed: 00:00:00.05
如果做表空间传输,需要对表空间做自包含检测:
17:51:15 [email protected] test1>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('test01', TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:01:04.07
17:52:25 [email protected] test1>SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
Elapsed: 00:00:00.05
创建导出传输目录:
17:52:43 [email protected] test1>create directory exp_dir as '/home/oracle/exp';
Directory created.
Elapsed: 00:00:00.07
17:53:24 [email protected] test1>grant read,write on directory exp_dir to test1;
Grant succeeded.
Elapsed: 00:00:00.06
17:53:39 [email protected] test1>!mkdir ~/exp
导出schema:
[[email protected] 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环境下:
[[email protected] 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.
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
|
Tablespace created. Elapsed: 00 : 00 : 06.27
Tablespace created. Elapsed: 00 : 00 : 05.81
18 : 19 : 36 [email protected] 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 : 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
Grant succeeded. |
建立数据导入目录:
1
2
3
4
5
6
7
|
Directory created. Grant succeeded. Elapsed: 00 : 00 : 00.16
[[email protected] ~]# mkdir -p /u01/exp [[email protected] ~]# chown -R oracle:dba /u01/exp |
导入数据:
[[email protected] ~]# su - oracle
[[email protected] ~]$ cd /u01/exp
[[email protected] exp]$ ls
test.dmp
[[email protected] 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
|
Connected. TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- SYS_EXPORT_SCHEMA_01 TABLE TESTTB1 TABLE Elapsed: 00 : 00 : 00.08
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
Connected. 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):
[[email protected] ~]$ 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):
[[email protected] ~]$ cd exp
[[email protected] 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 [email protected] 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 [email protected] test1>alter system set shared_pool_size=200m ;
System altered.
Elapsed: 00:00:00.65
调整后重新导出成功:
[[email protected] 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
|
@至此,数据迁移完成!