首頁 >資料庫 >mysql教程 >单实例数据库迁移到RAC环境

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

WBOY
WBOY原創
2016-06-07 17:10:001349瀏覽

从本节开始,将介绍下如何将单实例数据库迁移到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导出的相关文件到目标库上,同时在目标库上创建相应的用户和表空间以及目录对象等

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn