單一實例資料庫遷移到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 - Production SQL> 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.dbf SQL> 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(*) ----------    2363928 SQL> 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.dbf SQL> 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=test1 Export: 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 options SQL> 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_TEST SQL> 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 options SQL> select count(*) from test1.source;   COUNT(*) ----------    2363928 SQL> 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

1 2 3 4 5 下一頁