单实例数据库迁移到RAC环境

从本节开始，将介绍下如何将单实例数据库迁移到rac环境。在生产环境中，随着业务和数据量的加大，这种需求和场景不可避免，一般来讲主要由以下四种方法实现迁移过程！
1：使用expdp/impdp数据泵导出导入，或者使用传统的exp/imp导入导出，后者效率低下；
2: 使用在线表空间迁移技术快速导出导入，前提是数据库的字符集要一致；
3：使用rman的备份进行异机恢复
4: 对单实例数据库构建基于rac的物理备库，进而切换备库为主库，这是生产环境中最为推荐的做法本节中介绍使用expdp/impdp数据泵导出导入的方式实现迁移！环境介绍：
数据库的版本均为10.2.0.5
操作系统的版本单实例数据库（源库）为rhel5.4 64 bit
rac（目标数据库）为ceontos4.8 64bit一：查看源库的版本和表空间情况，同时在源库上建新的表空间和用户，插入数据，建立索引，创建目录对象，使用expdp到出用户的schema等

SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - ProductionSQL> show parameter compat;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.5.0
plsql_v2_compatibility               boolean     FALSE
SQL> select tablespace_name,file_name from dba_data_files;TABLESPACE_NAME      FILE_NAME
-------------------- --------------------------------------------------
USERS                /u01/app/oracle/oradata/orcl/users01.dbf
SYSAUX               /u01/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM               /u01/app/oracle/oradata/orcl/system01.dbf
EXAMPLE              /u01/app/oracle/oradata/orcl/example01.dbfSQL> create tablespace exp_rac datafile
2 "/u01/app/oracle/oradata/orcl/exp_rac01.dbf" size 300M
3 autoextend on next 10M maxsize unlimited
4* extent management local
Tablespace created.SQL> create tablespace exp_rac_index datafile
2 "/u01/app/oracle/oradata/orcl/exp_rac_index01.dbf" size 300M
3 autoextend on next 10M maxsize unlimited
4* extent management local
Tablespace created.SQL> select tablespace_name,file_name from dba_data_files;TABLESPACE_NAME      FILE_NAME
-------------------- --------------------------------------------------
USERS                /u01/app/oracle/oradata/orcl/users01.dbf
SYSAUX               /u01/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM               /u01/app/oracle/oradata/orcl/system01.dbf
EXAMPLE              /u01/app/oracle/oradata/orcl/example01.dbf
EXP_RAC              /u01/app/oracle/oradata/orcl/exp_rac01.dbf
EXP_RAC_INDEX        /u01/app/oracle/oradata/orcl/exp_rac_index01.dbf
SQL> create user test1 identified by oracle
2 default tablespace exp_rac
3 temporary tablespace temp
4 quota unlimited on exp_rac
5* account unlock;
User created.SQL> grant connect,resource to test1;
Grant succeeded.SQL> create table test1.source as select * from dba_source;
Table created.SQL> insert into test1.source select * from test1.source;
295491 rows created.SQL> /
590982 rows created.SQL> /
1181964 rows created.SQL> commit;
Commit complete.SQL> analyze table test1.source compute statistics;
Table analyzed.SQL> select count（*） from test1.source; COUNT（*）
----------
   2363928SQL> select sum（bytes/（1024*1024）） MB from dba_extents
2 where segment_name="SOURCE"
3 and owner="TEST1";        MB
----------
       408[oracle@server49 orcl]$ ll -h exp_rac01.dbf
-rw-r----- 1 oracle oinstall 411M Jan 1 19:06 exp_rac01.dbfSQL> create index test1.i_source
2 on test1.source（type）
3 tablespace exp_rac_index;
Index created.SQL> select table_name,tablespace_name from dba_indexes
2 where owner="TEST1" and index_name="I_SOURCE";TABLE_NAME                     TABLESPACE_NAME
------------------------------ --------------------
SOURCE                         EXP_RAC_INDEX
SQL> create directory expdp_dir as "/home/oracle/expdp_dir";
Directory created.SQL> grant read,write on directory expdp_dir to test1;
Grant succeeded.SQL> ！mkdir -p /home/oracle/expdp_dir[oracle@server49 ~]$ expdp test1/oracle directory=expdp_dir dumpfile=source.dmp logfile=source.log schemas=test1Export: Release 10.2.0.5.0 - 64bit Production on Sunday, 01 January, 2012 19:38:30
Copyright （c） 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST1"."SYS_EXPORT_SCHEMA_01": test1/******** directory=expdp_dir dumpfile=source.dmp logfile=source.log schemas=test1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 408 MB
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST1"."SOURCE"                            280.8 MB 2363928 rows
Master table "TEST1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST1.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/expdp_dir/source.dmp
Job "TEST1"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:39:03

二：复制impdp导出的相关文件到目标库上,同时在目标库上创建相应的用户和表空间以及目录对象等

[oracle@rac1 ~]$ sqlplus sys/123456@racdb as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Jan 1 20:28:52 2012
Copyright （c） 1982, 2010, Oracle. All Rights Reserved.Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing optionsSQL> select file_name,tablespace_name from dba_data_files;FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------
+DATA/racdb/datafile/users.259.769960507           USERS
+DATA/racdb/datafile/sysaux.257.770222575          SYSAUX
+DATA/racdb/datafile/undotbs1.258.769205541        UNDOTBS1
+DATA/racdb/datafile/system.256.770588849          SYSTEM
+DATA/racdb/datafile/example.264.769205649         EXAMPLE
+DATA/racdb/datafile/undotbs2.265.769205765        UNDOTBS2
+DATA/racdb/datafile/local_arch_test.312.770588847 LOCAL_ARCH_TESTSQL> create user test1 identified by oracle
2 default tablespace users
3 temporary tablespace temp
4 account unlock;
User created.SQL> grant connect,resource to test1;
Grant succeeded.SQL> create directory expdp_dir as "/home/oracle/expdp_dir";
Directory created.SQL> grant read,write on directory expdp_dir to test1;
Grant succeeded.SQL> create tablespace exp_rac datafile size 500M;
Tablespace created.SQL> create tablespace exp_rac_index datafile size 500M;
Tablespace created.

三：在目标数据库上使用impdp导入数据

[oracle@rac1 ~]$ impdp test1/oracle directory=expdp_dir dumpfile=source.dmp logfile=source.log schemas=test1
Import: Release 10.2.0.5.0 - 64bit Production on Sunday, 01 January, 2012 21:00:26
Copyright （c） 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, 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=expdp_dir dumpfile=source.dmp logfile=source.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"."SOURCE" 280.8 MB 2363928 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST1"."SYS_IMPORT_SCHEMA_01" successfully completed at 21:07:02

四：测试结果

[oracle@rac1 ~]$ sqlplus sys/123456@racdb as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Jan 1 21:08:18 2012
Copyright （c） 1982, 2010, Oracle. All Rights Reserved.Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing optionsSQL> select count（*） from test1.source; COUNT（*）
----------
   2363928SQL> col table_name format a20
SQL> col tablespace_name format a20
SQL> select table_name,tablespace_name from dba_indexes
2 where owner="TEST1" and index_name="I_SOURCE";TABLE_NAME           TABLESPACE_NAME
-------------------- --------------------
SOURCE               EXP_RAC_INDEX
SQL> select sum（bytes/（1024*1024）） MB from dba_extents
2 where segment_name="I_SOURCE"
3* and owner="TEST1"      MB
----------
        56

Ruby在Linux下连接MySQL数据库使用RAC和Data Guard构建MAA架构相关资讯 Oracle基础教程

Oracle块编程返回结果集详解（11/10/2013 10:45:58）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）
Oracle基础教程知识点总结（06/18/2013 07:43:32）

Oracle基础教程之tkprof程序详解（10/22/2013 11:49:50）
Oracle基础教程之sqlplus汉字乱码（07/18/2013 16:30:00）
Oracle 管理之 Linux 网络基础（02/16/2013 18:37:35）

本文评论查看全部评论（0）