单实例数据库迁移到rac环境(二)

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介:

在上一节中,介绍了使用expdp/impdp方式迁移单实例数据库至rac环境,本节中将简要的介绍下,使用传输表空间方式将单实例数据库的表空间迁移至rac环境下,同样需要借助expdp/impdp数据泵实现,但同上一节所演示的结果相比,在大数据量情况下,使用传输表空间方式迁移,可以节约许多时间,但前途是数据库的字符集要一致,且需要将准备迁移的表空间进行只读操作!

环境介绍:
数据库的版本均为10.2.0.5,数据库字符集编码为utf8
操作系统的版本单实例数据库(源库)为rhel5.4 64 bit
rac(目标数据库)为ceontos4.8 64bit

一:在源库上创建表空间,用户,建表,插入数据,建索引以及目录对象等

SQL> create tablespace exp_rac1 datafile
  2  '/u01/app/oracle/oradata/orcl/exp_rac1_01.dbf' size 300M
  3  autoextend  on next 10M maxsize unlimited
  4* extent management local;
Tablespace created.

SQL> create tablespace exp_rac1_index datafile
  2  '/u01/app/oracle/oradata/orcl/exp_rac1_index_01.dbf' size 300M
  3  autoextend  on next 10M maxsize unlimited
  4* extent management local;
Tablespace created.

SQL> create user test2 identified by oracle
  2  default tablespace exp_rac1
  3  temporary tablespace temp
  4  quota unlimited on  exp_rac1
  5* account unlock;
User created.

SQL> grant connect,resource to test2;
Grant succeeded.

SQL> create table test2.source2 as select * from dba_source;
Table created.

SQL> insert into test2.source2 select * from test2.source2;
295491 rows created.

SQL> /
590982 rows created.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats('TEST2','SOURCE2');
PL/SQL procedure successfully completed.

SQL> select count(*) from test2.source2;

  COUNT(*)
----------
   1181964

SQL> select sum(bytes/(1024*1024)) MB from dba_extents
  2  where segment_name='SOURCE2'
  3* and owner='TEST2';

        MB
----------
       208

SQL> create index test2.i_source2
  2  on test2.source2(type)
  3* tablespace exp_rac1_index;
Index created.

SQL>  select table_name,tablespace_name from dba_indexes
  2* where owner='TEST2' and index_name='I_SOURCE2'

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SOURCE2                        EXP_RAC1_INDEX

SQL> select sum(bytes/(1024*1024)) MB from dba_extents
  2  where segment_name='I_SOURCE2'
  3* and owner='TEST2'

        MB
----------
        28

SQL> create directory expdp_dir2 as '/home/oracle/expdp_dir2';
Directory created.

SQL> grant read,write on directory expdp_dir2 to test2;
Grant succeeded.

SQL> !mkdir -p /home/oracle/expdp_dir2

二:将需要迁移的表空间只读,并拷贝表空间数据文件,如果在asm环境下,可以使用rman操作

SQL> alter tablespace exp_rac1 read only;
Tablespace altered.

SQL> alter tablespace exp_rac1_index read only;
Tablespace altered.

[oracle@server49 ~]$ cd /home/oracle/expdp_dir2/
[oracle@server49 expdp_dir2]$ cp /u01/app/oracle/oradata/orcl/exp_rac1_01.dbf ./
[oracle@server49 expdp_dir2]$ cp /u01/app/oracle/oradata/orcl/exp_rac1_index_01.dbf ./

三:使用expdp命令导出传输表空间的元数据并复制到目标数据库上(rac环境)

[oracle@server49 ~]$ expdp \'sys/123456 as sysdba\' directory=expdp_dir2 dumpfile=source2.dmp

logfile=source2.log transport_tablespaces=exp_rac1,exp_rac1_index

Export: Release 10.2.0.5.0 - 64bit Production on Sunday, 01 January, 2012 22:44:51

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 "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "sys/******** AS SYSDBA" directory=expdp_dir2

dumpfile=source2.dmp logfile=source2.log transport_tablespaces=exp_rac1,exp_rac1_index 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/expdp_dir2/source2.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 22:45:12

[oracle@server49 ~]$ ls /home/oracle/expdp_dir2/
exp_rac1_01.dbf  exp_rac1_index_01.dbf  source2.dmp  source2.log

四:在目标数据库上创建用户和目录对象,并利用rman将表空间的数据文件导入到asm实例上

[oracle@rac1 ~]$ sqlplus sys/123456@racdb1 as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Jan 1 23:13:17 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> create user test2 identified by oracle account unlock;
User created.

SQL> grant connect,resource to test2;
Grant succeeded.

SQL> create directory expdp_dir2 as '/home/oracle/expdp_dir2';
Directory created.

SQL> grant read,write on directory expdp_dir2 to test2;
Grant succeeded.

RMAN> convert datafile '/home/oracle/expdp_dir2/exp_rac1_01.dbf' format '+DATA/racdb/datafile/exp_rac1_01.dbf';

Starting backup at 2012-01-01 23:41:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=126 instance=racdb1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/expdp_dir2/exp_rac1_01.dbf
converted datafile=+DATA/racdb/datafile/exp_rac1_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:24
Finished backup at 2012-01-01 23:42:14

RMAN> convert datafile '/home/oracle/expdp_dir2/exp_rac1_index_01.dbf' format

'+DATA/racdb/datafile/exp_rac1_index_01.dbf';

Starting backup at 2012-01-01 23:44:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/expdp_dir2/exp_rac1_index_01.dbf
converted datafile=+DATA/racdb/datafile/exp_rac1_index_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:27
Finished backup at 2012-01-01 23:44:29

ASMCMD> pwd
+DATA/racdb/datafile
ASMCMD> ls
EXAMPLE.264.769205649
EXP_RAC1.301.771464511
EXP_RAC1_INDEX.297.771464643
LOCAL_ARCH_TEST.312.770588847
RAC_EXP.309.771454375
RAC_EXP_INDEX.305.771454413
SYSAUX.257.770222575
SYSTEM.256.770588849
UNDOTBS1.258.769205541
UNDOTBS2.265.769205765
USERS.259.769960507
exp_rac1_01.dbf
exp_rac1_index_01.dbf

五:利用impdp导入传输表空间的元数据

[oracle@rac1 ~]$ impdp \'sys/123456 as sysdba\'  directory=expdp_dir2 dumpfile=source2.dmp

transport_datafiles='+DATA/racdb/datafile/exp_rac1_01.dbf','+DATA/racdb/datafile/exp_rac1_index_01.dbf'

Import: Release 10.2.0.5.0 - 64bit Production on Monday, 02 January, 2012 0:08:33
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 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "sys/******** AS SYSDBA" directory=expdp_dir2

dumpfile=source2.dmp transport_datafiles=+DATA/racdb/datafile/exp_rac1_01.dbf,

+DATA/racdb/datafile/exp_rac1_index_01.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 00:08:52

六:测试

[oracle@rac1 ~]$ sqlplus sys/123456@racdb as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 2 00:09:20 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 test2.source2;

  COUNT(*)
----------
   1181964

SQL> col table_name format a20
SQL> col tablespace_name format a20
SQL> select table_name,tablespace_name from dba_indexes
  2  where owner='TEST2' and index_name='I_SOURCE2';

TABLE_NAME           TABLESPACE_NAME
-------------------- --------------------
SOURCE2              EXP_RAC1_INDEX

SQL> select sum(bytes/(1024*1024)) MB from dba_extents
  2  where segment_name='I_SOURCE2'
  3* and owner='TEST2'

        MB
----------
        28

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.77022257 SYSAUX
+DATA/racdb/datafile/undotbs1.258.769205 UNDOTBS1
+DATA/racdb/datafile/system.256.77058884 SYSTEM
+DATA/racdb/datafile/example.264.7692056 EXAMPLE
+DATA/racdb/datafile/undotbs2.265.769205 UNDOTBS2
+DATA/racdb/datafile/local_arch_test.312 LOCAL_ARCH_TEST
+DATA/racdb/datafile/rac_exp.309.7714543 EXP_RAC
+DATA/racdb/datafile/rac_exp_index.305.7 EXP_RAC_INDEX
+DATA/racdb/datafile/exp_rac1_index_01.d EXP_RAC1_INDEX
+DATA/racdb/datafile/exp_rac1_01.dbf     EXP_RAC1
11 rows selected.

本文转自斩月博客51CTO博客,原文链接http://blog.51cto.com/ylw6006/757707如需转载请自行联系原作者


ylw6006

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
28天前
|
SQL 关系型数据库 MySQL
SQL命令行退出操作指南:轻松掌握不同数据库环境下的退出技巧
在数据库管理与开发过程中,经常需要通过SQL命令行工具(如MySQL的mysql客户端、PostgreSQL的psql、SQL Server的sqlcmd等)与数据库进行交互
191 59
|
3月前
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库,一步就能脱胎换骨
阿里云瑶池数据库来开课啦!自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定!点击阅读原文完成实验就可获得一本日历哦~
|
3月前
|
关系型数据库 MySQL 数据库
RDS MySQL灾备服务协同解决方案构建问题之数据库备份数据的云上云下迁移如何解决
RDS MySQL灾备服务协同解决方案构建问题之数据库备份数据的云上云下迁移如何解决
|
12天前
|
算法 大数据 数据库
云计算与大数据平台的数据库迁移与同步
本文详细介绍了云计算与大数据平台的数据库迁移与同步的核心概念、算法原理、具体操作步骤、数学模型公式、代码实例及未来发展趋势与挑战。涵盖全量与增量迁移、一致性与异步复制等内容,旨在帮助读者全面了解并应对相关技术挑战。
20 3
|
2月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
316 15
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
30天前
|
应用服务中间件 PHP Apache
PbootCMS提示错误信息“未检测到您服务器环境的sqlite3数据库扩展...”
PbootCMS提示错误信息“未检测到您服务器环境的sqlite3数据库扩展...”
|
23天前
|
安全 Linux 数据库连接
CentOS 7环境下DM8数据库的安装与配置
【10月更文挑战第16天】本文介绍了在 CentOS 7 环境下安装与配置达梦数据库(DM8)的详细步骤,包括安装前准备、创建安装用户、上传安装文件、解压并运行安装程序、初始化数据库实例、配置环境变量、启动数据库服务、配置数据库连接和参数、备份与恢复、以及安装后的安全设置、性能优化和定期维护等内容。通过这些步骤,可以顺利完成 DM8 的安装与配置。
147 0
|
29天前
|
数据可视化 关系型数据库 MySQL
【IDEA】配置mysql环境并创建mysql数据库
【IDEA】配置mysql环境并创建mysql数据库
67 0
|
2月前
|
SQL Java 数据库连接
数据库迁移不再难:Flyway 与 Liquibase 大比拼,哪个才是你的真命天子?
【9月更文挑战第3天】数据库迁移在软件开发中至关重要,尤其在使用 ORM 框架如 Hibernate 时。为确保部署时能顺利应用最新的数据库变更,开发者常使用自动化工具。Flyway 和 Liquibase 是当前流行的两种选择,均能有效管理数据库版本控制。Flyway 采用 SQL 脚本表示变更,简单易用;Liquibase 支持多种脚本格式,功能更强大,适合复杂项目。本文将对比这两种工具的特点,并通过示例展示各自的优缺点,帮助开发者根据项目需求做出合适的选择。
379 1
|
3月前
|
Linux 数据库 数据安全/隐私保护